December 23, 2009 at 2:03 am
Hi all,
In order to monitor performance, I've got a trace set up to run on the 1st Tuesday of each month, which records all queries taking more than 2 seconds to execute, between 0800 and 1330. We're generally getting approximately 30 queries satisfying this criterion each time the trace is run. Looking at the queries, I'm happy with this (no need to go into the detail of why here).
Our customer (bless him) now would like to know how these statistics stack up against the total number of queries run during this period.
Is there a way of capturing this information?
One way I can think of is running a second trace (or changing the parameters of the existing one) to trace all queries, and then performing some analysis on the resulting data (either in Excel or a separate analysis database).
However, is it possible to get this info from some of the 2005 management views, or with some clever data gathering stored procedure?
Of course, a second trace or data gathering stored proc will affect performance...
Thanks,
James
December 23, 2009 at 8:59 am
Start by looking at sys.dm_exec_query_stats. You'll need to use sys.dm_exec_sql_text to see the query being executed. This information is since last server restart.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 23, 2009 at 9:05 am
Jack Corbett (12/23/2009)
Start by looking at sys.dm_exec_query_stats. You'll need to use sys.dm_exec_sql_text to see the query being executed. This information is since last server restart.
Sorry to jump on this one Jack, but sys.dm_exec_query_stats is only for queries that are in cache, not since the last restart. If it ages out of cache, the information goes away.
To get this information out of the DMV's, you'd need to query it somewhat frequently, say, once an hour, with the understanding that there's a chance, depending on the volatility of your the information in your cache, that you're missing some data.
To get a 100% perfect measure, you'd need to set up a trace and run it for a month. As long as you manage the output of the trace carefully and you only capture a minimal set of events on the server, running it for a month won't cause performance problems, so I'd probably go that route.
"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
December 23, 2009 at 9:07 am
The system views (DMVs) won't give you data particular to that time period. They will give you a lot of very valuable data, but it's since last service start, not for a particular time-period (so far as I know).
A server-side trace will have very little impact on server performance. Are you running traces by setting them up through sp_trace_create (and related procs), or are you running them through Profiler?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 23, 2009 at 9:21 am
Thanks, everyone, for the pointers.
You've confirmed my thoughts that the DMVs won't give enough/the right information for what I want.
I'm running my trace using sp_trace_create, which I believe is the most efficient mechanism. I think I'll create a separate trace, running for the same time period, capturing lightweight information on all queries being run. I can keep my existing trace to highlight those queries taking more than N seconds, with more information (for example the SQL text).
James
December 23, 2009 at 11:02 am
Grant Fritchey (12/23/2009)
Jack Corbett (12/23/2009)
Start by looking at sys.dm_exec_query_stats. You'll need to use sys.dm_exec_sql_text to see the query being executed. This information is since last server restart.Sorry to jump on this one Jack, but sys.dm_exec_query_stats is only for queries that are in cache, not since the last restart. If it ages out of cache, the information goes away.
Plus certain queries will never be in cache at all and others will only show single execution statistics no matter how many times they've run.
See - http://sqlinthewild.co.za/index.php/2009/07/10/why-the-dmvs-are-not-a-replacement-for-sql-trace/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 23, 2009 at 11:05 am
James Lavery (12/23/2009)
I'm running my trace using sp_trace_create, which I believe is the most efficient mechanism. I think I'll create a separate trace, running for the same time period, capturing lightweight information on all queries being run. I can keep my existing trace to highlight those queries taking more than N seconds, with more information (for example the SQL text).
What events are you currently capturing in your existing trace?
Personally, I'd say just create one trace (unless you know it will degrade performance) and then when you do the analysis, you can filter out the queries that you're not interested in. I say this because it's not always the long-running queries that are the performance problems.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 23, 2009 at 11:12 am
Grant Fritchey (12/23/2009)
Jack Corbett (12/23/2009)
Start by looking at sys.dm_exec_query_stats. You'll need to use sys.dm_exec_sql_text to see the query being executed. This information is since last server restart.Sorry to jump on this one Jack, but sys.dm_exec_query_stats is only for queries that are in cache, not since the last restart. If it ages out of cache, the information goes away.
To get this information out of the DMV's, you'd need to query it somewhat frequently, say, once an hour, with the understanding that there's a chance, depending on the volatility of your the information in your cache, that you're missing some data.
To get a 100% perfect measure, you'd need to set up a trace and run it for a month. As long as you manage the output of the trace carefully and you only capture a minimal set of events on the server, running it for a month won't cause performance problems, so I'd probably go that route.
No problem, jump away. I'd rather have you do that than have my incorrect advice stay out there. I get to learn something new as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 23, 2009 at 11:30 am
Jack Corbett (12/23/2009)
Grant Fritchey (12/23/2009)
Jack Corbett (12/23/2009)
Start by looking at sys.dm_exec_query_stats. You'll need to use sys.dm_exec_sql_text to see the query being executed. This information is since last server restart.Sorry to jump on this one Jack, but sys.dm_exec_query_stats is only for queries that are in cache, not since the last restart. If it ages out of cache, the information goes away.
To get this information out of the DMV's, you'd need to query it somewhat frequently, say, once an hour, with the understanding that there's a chance, depending on the volatility of your the information in your cache, that you're missing some data.
To get a 100% perfect measure, you'd need to set up a trace and run it for a month. As long as you manage the output of the trace carefully and you only capture a minimal set of events on the server, running it for a month won't cause performance problems, so I'd probably go that route.
No problem, jump away. I'd rather have you do that than have my incorrect advice stay out there. I get to learn something new as well.
Oh, I'm going to jump, I'm just trying to land as softly as possible.
Seriously though, I love learning stuff when I get something wrong, but I hate it when people are rude about the corrections. Maybe I'm over-compensating.
"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
December 23, 2009 at 11:36 am
GilaMonster (12/23/2009)
Grant Fritchey (12/23/2009)
Jack Corbett (12/23/2009)
Start by looking at sys.dm_exec_query_stats. You'll need to use sys.dm_exec_sql_text to see the query being executed. This information is since last server restart.Sorry to jump on this one Jack, but sys.dm_exec_query_stats is only for queries that are in cache, not since the last restart. If it ages out of cache, the information goes away.
Plus certain queries will never be in cache at all and others will only show single execution statistics no matter how many times they've run.
See - http://sqlinthewild.co.za/index.php/2009/07/10/why-the-dmvs-are-not-a-replacement-for-sql-trace/
Ooooh! I didn't even think about what would happen with temp tables & the cache. Bloody excellent post.
"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
December 24, 2009 at 2:30 am
Thanks - good point to just keep one trace. If I keep the output lightweight then there shouldn't be too much load on the server.
Thanks for the links - very useful, especially the mods to the trace invocation SQL to timestamp the output file and limit the trace time from the outset. Obvious once you see it!
Currently performance isn't a problem. However, the userbase is scheduled to grow, and so my plan is to run this trace regularly (same time period on same day of week) so that I can baseline the query profile and also see trends in usage of the system (at the 'how many queries are being fired at the database' level).
December 24, 2009 at 5:45 am
I suggest (as I did in the article), half an hour to an hour, once a week. Longer than that and it gets cumbersome to analyse.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply