August 13, 2009 at 2:38 am
How we can know activity on a database. ( This is doubt i got when colleague showed his database server and ask me to find what is going on..) Is there any way to find what database is very much active and what are the operation going and all...
Can you suggest me other than SQL server profiler idea ?
Thanks.
August 13, 2009 at 6:50 am
What's wrong with using a server side trace?
But, you can also query the dynamic management views (dmv), such as sys.dm_exec_requests to see which database is getting a lot of activity. You can also see aggregate information about the queries that are currently in cache using the dmv sys.dm_exec_query_stats. It will show you how many times a particular query has been called, the amount of time it ran on average, the max & min times, etc. Very useful stuff.
If all this is too granular, you can look at Performance Monitor and observer the wait states and queues for I/O, CPU and Memory. That will tell you where the most load is being placed on the system and what is causing the slow down. From there you can drill into which queries are being run by which process, again by going back to the DMV's.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 14, 2009 at 1:10 am
Thanks for the suggestion.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply