Well, It is slightly more advanced that that ;-) Michael, your first apprach is to found out the size of your tables - this includes rows and indexes. You should then fire up QA and then run sp_lock/sp_who/sp_who2 ad-hoc at slow times but you should also run a SQL Trace to find out long running Queries etc. Your best bet to this is via SQL Profiler.
As for the best best book - simply get into SQL Books online (make sure you get the updated specs from MS). And get your info from MSDN. Memory is really not an issue with SQL Server unless you are running a crappy spec. What spec is your Database? Is it a multi-processor box? Are you running lots of complex queries (correlated subqueries, computed columns etc, dynamic views..)? Are you running Stored Procedures? Are they running (god forbid :- ) lots of dynamic SQL blocks? Are you experiencing degredation at the moment? If you are then SQL Profiler will tell you what SP/Query block is causing high I/O hits and memory consumption (possibly leading to locks and even worse blocking). You may also want to consider if your database is over indexed as that can cause problems. Let me know if I can be any more help in this area. N Quoting "Justin D. Scott" <[EMAIL PROTECTED]>: > > I'd like to review what DBs, Tables, Queries and Stored > > procedures are using the most resources (mainly ram). > > I know that you can use the SQL profiler to see > > operations and such, but I'd like to know what in my > > SQL server is 'holding onto' memory, what's using the > > most, where I can tighten up, etc. > > My understanding is that when SQL Server runs a query, it will try to keep > everything involved with that query in memory up to the maximum you have set > in the server properties. If something isn't accessed for a while, it will > eventually get dropped out if/when something new requires that memory > instead. > > It's been a while since I had to mess with the innards of SQL Server, so I > could be way off. I personally have never seen a tool that would allow you > to "inspect" what SQL was holding in memory at any given time. > > > ----------------------------------- > Justin D. Scott > Vice President > Sceiron Interactive, Inc. > www.sceiron.com > > [EMAIL PROTECTED] > 941.378.5341 - office > 941.320.2402 - mobile > 877.678.6011 - facsimile > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2161 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
