Server-Side SQL Trace

Dirk.Hondong · January 28th, 2011, 04:07 AM

Hello all,

I tried to analyse a process ouf our merchandise management system with the help of the modified PerfStatsScripts which are provided through this site.
I used the SQLDiagPerfStats_SQLNexus.XML. I know that a lot of data is gathered then.
My problem is that the process (or better the app initiating the process) I want to analyse times out when SQL:StmtStarting and SQL:StmtCompleted are gathered as well.
But I am interested in these data among the other.
So, how can I tweek the SQL Trace (or maybe provide additional Ressources to the VM hosting the SQL Server) to get all necessary information for SQLNexus to analyse. I need to take a look at Unique Statements Report. Unique Batches do not provide enough inofrmation in this case.

Regards
Dirk

Brent Ozar · January 28th, 2011, 07:51 AM

If I understand your post right, you're trying to analyze timeouts? You said "times out", but I think that's what you meant. SQL Server doesn't know about timeouts because they happen on the client side - the app waits too long and gives up.

Ken Henderson has a good post about how timeouts work:

http://blogs.msdn.com/b/khen1234/arc...20/483015.aspx

Dirk.Hondong · January 28th, 2011, 08:23 AM

Hi Brent,

I think I have to improve my English a little bit. Sorry that my words caused some confusion.

I wanted to analyse one business process of our management merchandise system which timed out when I ran a trace with SQL:STMTStarting / STMTComplete & SP:STMTStarting & SP:STMTComplete.

I had the chance to give my test machine more resources (e.g. added two more virt. CPUs and 4 GB additional RAM).
Now ChristianÂ´s custom SQLDiagPerfStats_SQLNexus.XML for sqldiag does not let the process time out any more and I can take a more detailed look at it.
I just thought that thereÂ´s maybe another way instead of adding more ressources.

But thank you for the reply anyway. I didnÂ´t expect such a quick response.

Regards
Dirk

Brent Ozar · January 28th, 2011, 08:30 AM

Ah, okay. Yes, SQLdiag and tracing can add more load than a slow server can handle. You might check into Extended Events, which lets you monitor with less overhead. It requires more work to implement, though.