April 29, 2009 at 10:57 am
First off I am new to db adminstration and I am still trying to find my way around the dba world.My manager has asked me to come up with some perfomace counters that will give hime the following:
1)the longest running query.
2)Time taken by this query.
3)Memmory being used by the query.
I am able to view all this info in the activity monitor by using sql 2008 but I need to produce some numbers that will give this information .Is there a script that will give all this info?
I used select * From sys.dm_os_wait_stats order by wait_time_ms desc to give some wait stats but i need more specific info as I have described above.
Can any of you guys out there help me?
thanks
April 30, 2009 at 6:36 am
One of two ways you can go about that. First option, run server-side traces to capture events over time. You can set those up through Profiler. You can then aggregate them, total them, etc.. Next option is to query the information of queries currently in cache. There's a great DMV sys.dm_exec_query_stats that shows aggregate run time etc.
Either one of these, or both, should provide you with the information you need. More information on both is available in BOL or various articles here at SSC & elsewhere around the web.
"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
April 30, 2009 at 10:04 am
Thank you grant for the info.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply