Preformance counter that logs contents of a querey 1

[DriveByCheckmate]

Hi everyone,

I'm having a disk queue length issue and I'm trying to figure out what exactly is causing it.

What I'd like to do is setup a performance counter so that when disk queue goes over a certain point it will start logging the SQL queries that are being executed.

Anyone have any feedback on how that could be done or maybe have a better idea? Thanks!

FYI - we're using Microsoft SQL Express 2005.

 

[JayKusch]

What you could do is set up Permon to monitor and alert on your DQLs so that you have a "time" that the metric was exceeded.

Then, set up a Profiler trace to capture the TextData value of the SPIDs that are executing on your SQL Server instance.

Save the trace to a table preferable.

When Permon alerts you, you can then drill into the table with the Profiler trace records and find the records that are within the time range you are looking for.

A 3rd party tool like Quest Spotlight On SQL Server is a huge help in getting to this information from one management point.

Thanks

J. Kusch

 

[DriveByCheckmate]

I'm going to give that a try, thanks J!