January 23, 2009 at 1:23 pm
I guess most likely some DMV captures that, but I could not find any. Does anyone know how?
Thanks!
Kathleen
January 23, 2009 at 1:29 pm
The most frequent what? Stored proc?
I use server traces to get that kind of data.
- 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
January 23, 2009 at 1:47 pm
Either dynamic queries or stored procedures. Basically I would like to know what queries/procedures got run most on our production server. Using profiler for an extended period of time is tough, because I've run into issues before where profiler killed the server, probably because we have a lot of contesion on the server.
January 23, 2009 at 1:52 pm
Where were you running profiler from?
If you store it in a trc file running from a remote box you and only capture TSQL_SPs you should be ok.
Get it into a table that you can work on.
SELECT * INTO trace1108 FROM ::fn_trace_gettable('C:\MyTrace.trc', default)
Then you can do a count from there.
more info here
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
January 23, 2009 at 1:56 pm
Don't run profiler for the traces. Create them separately. Search for "trace" in Books Online, and it will come up with "Introducing SQL Trace". Specifically, you're going to want to look at sp_trace_create. There's also a function called sys.fn_trace_gettable that you can use to query the trace once you've created it.
I definitely advise tracing to a file, not a table. Much less impact on the server.
Using these trace procs to create a trace in a file has very little impact on server performance, and can be quite useful for lots of things.
- 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
January 23, 2009 at 2:00 pm
I ran profiler, but only captured 'sql:batch completed', I've tried locally as well as remotely, I ran into issues in either case. I hope running trace as a background job is much less intrusive.
Thanks for your help!
January 23, 2009 at 2:13 pm
You can run a trace, but it's so... 20th century! 🙂
Use this query to capture your most frequently-run SQL statements (modify per your requirements):
SELECT TOP 10 *
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY qs.execution_count desc;
You will probably need to run this regularly - say once a day - and store the results in a table for analysis over a period of time. There is a risk that the data from the DMV dm_exec_query_stats is regularly flushed from memory if there is memory pressure in your system.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 23, 2009 at 2:14 pm
KATHLEEN Y ZHANG (1/23/2009)
I hope running trace as a background job is much less intrusive.
It is. Grant explained why - http://scarydba.wordpress.com/2008/12/18/profiler-research/
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
January 23, 2009 at 2:23 pm
Marios Philippopoulos (1/23/2009)
There is a risk that the data from the DMV dm_exec_query_stats is regularly flushed from memory if there is memory pressure in your system.
There's a couple other limitations.
Queries that are not cached won't appear in that DMV (obviously, it's the cached plans DMV afterall).
That includes DDL statements (that only have one way of been executed) and any statement that will recompile on every execution, either because the proc is marked WITH RECOMPILE or the query is marked OPTION (RECOMPILE)
The second thing is that a proc with an unstable execution plan (say something that creates a temp table or reads from a very fast changing table) will be getting recompiled frequently and as such, will have much lower usage stats that might be expected.
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
January 23, 2009 at 2:36 pm
All of which are why I suggest a trace.
And don't worry too much about a server-side trace (the kind I'm talking about) taking a lot of server resources and slowing things down. I've done it on dozens of servers, and no end-user has ever yet noticed the difference in server speed.
- 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
January 23, 2009 at 2:47 pm
Thanks all for your input! Have a nice weekend!
January 23, 2009 at 8:25 pm
You're welcome.
- 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
January 24, 2009 at 5:50 am
GilaMonster (1/23/2009)
Marios Philippopoulos (1/23/2009)
There is a risk that the data from the DMV dm_exec_query_stats is regularly flushed from memory if there is memory pressure in your system.There's a couple other limitations.
Queries that are not cached won't appear in that DMV (obviously, it's the cached plans DMV afterall).
That includes DDL statements (that only have one way of been executed) and any statement that will recompile on every execution, either because the proc is marked WITH RECOMPILE or the query is marked OPTION (RECOMPILE)
The second thing is that a proc with an unstable execution plan (say something that creates a temp table or reads from a very fast changing table) will be getting recompiled frequently and as such, will have much lower usage stats that might be expected.
Thanks, these are very good points I hadn't thought of. I will keep them in mind.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 24, 2009 at 5:56 am
GSquared (1/23/2009)
All of which are why I suggest a trace.And don't worry too much about a server-side trace (the kind I'm talking about) taking a lot of server resources and slowing things down. I've done it on dozens of servers, and no end-user has ever yet noticed the difference in server speed.
Yes, a trace is still the best solution after all. I hadn't realized the limitations of the DMV option. Hopefully, DMVs will be able to capture the remaining info in future versions of SQL Server...
There is value in the idea of being able to capture historic info with a single query *after the fact*, as opposed to running a trace in the hope the problematic condition will re-occur in an expected time interval.
I guess that's why the DMVs appeal to me more, but they are not there yet.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 24, 2009 at 9:12 am
Marios Philippopoulos (1/24/2009)
Hopefully, DMVs will be able to capture the remaining info in future versions of SQL Server...
I doubt they'll ever replace SQL trace because the DMVs, like extended events, are not designed to replace SQL trace. They expose different information in different ways. For me, it's not trace OR DMVs, it's trace AND DMSs where the information that together they provide is far more than the sum of the parts
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 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply