May 16, 2013 at 5:48 am
What I'm looking to do is capture the queries that are being fired against a particular DB, to help work out what indexes are being used, what might help them work better (user always selects a couple columns, always with a where = one particular column? Maybe include the other columns in the index!)
Currently, here's what I'm capturing:
Event 13 - SQL:Batch Starting
Columns:
1 - TextData (which should show the T-SQL?)
14 - StartTime
35 - DatabaseName
12 - SPID
I'm filtering the trace (sp_trace_setfilter) to only show the DB I'm currently interested in.
Where I'm getting confused is, either the users haven't touched the DB all week, or I'm not capturing their queries. I know I'm capturing data, as when I ran a sp_estimate_data_compression_savings against the DB, I found it in the trace. I know it's not rolled over (it's still on the first file, and it's configured to go to 100MB before rolling over to a new file)
I'm going to contact the POC / DEV for this DB, ask him if the users have been hitting it, but I'd feel really silly if my trace is broken or not capturing the right data...
You know, a question would probably help...
Basically, should the trace I defined above capture the information I want?
Thanks,
Jason
May 17, 2013 at 11:23 am
I've seen issues with filtering on database name in Trace. My suggestion, switch to database id if you can. If not, the better solution, switch to extended events. They are so much safer to run on your production server. Did you know, even though you're filtering trace data, filters are applied after the trace event is collected and stored in memory, not before. Not so with extended events.
"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
May 17, 2013 at 11:47 am
Yes, I can re-jigger the trace to use the DB ID.
I'll also look into setting this up with Extended Events, instead.
Thanks Grant!
Jason
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply