January 24, 2008 at 10:03 pm
Is it possible to create a trace template for profiler (sql2k sp3) to watch only the started (but not finished yet) long running queries (let's say having exec time >1s) ? The servers are being kicked by all kind of queries, but most of them finish fast - so I need a trick to remove (or filter out) the "fast & terminated queries" from that list
And the second one: how could I make "Show Plan Statistics " event show up something useful? (that long hex code doesn't say too much, at least to me...). Is there any other column to be selected? (note : "Show execution plan" event is shown up )
January 25, 2008 at 6:05 am
Sure. Trace the events SP:stmt_Completed and T-SQL:Stmt_completed (if you want to see individual statements in a proc/batch) or SP:completed and T-SQL:Batch_Completed
You can then filter for duration >1000 (1000 ms) to get just the long running queries. The starting events don't (for obvious reasons) have a duration.
Never used the Show plan stats, so I have no idea what it shows. What are you trying to get?
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 25, 2008 at 8:42 am
I'm trying to get the exec plan in xml format (remember, I'm on sql2k), times , nb of reads and so on (this is the first time trying this) ...
And for the other question, I'm trying to get queries that are running (not finished yet) and blocking others without the millions of fast running queries
January 25, 2008 at 8:53 am
virgilash (1/25/2008)
I'm trying to get the exec plan in xml format
Not on SQL 2000. The exec plan in xml is a 2005 specific feature (specific to the 2005 server, not tools) Check management studio. You'll see that you can only save an exec plan if you're connected to a 2005 server.
As for reads, cpu, writes, the *_completed events will give you that.
For blocking queries, you're probably better using query analyser and a script to check for blocking. Profiler's great for watching events as they happen, but it won't (easily) tell you what queries are still running.
Profiler can give you a blocked process event. From the 2005 BoL
The Blocked Process Report event class indicates that a task has been blocked for more than a specified amount of time. This event class does not include system tasks or tasks that are waiting on non deadlock-detectable resources.
You could theoretically use a combination of *_started and *_completed events and watch for queries that have started and not completed, but it will be a nightmare, because you can't filter the starting events on duration.
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 25, 2008 at 11:10 am
1st: sorry, hierachical text would be a better explanation - I got it already but I read that I could get statistics info too with the other event...
2nd: I know .... :-((
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply