p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: Professional SQL Server 2008 Internals and Troubleshooting (http://p2p.wrox.com/forumdisplay.php?f=544)
-   -   Server-Side SQL Trace (http://p2p.wrox.com/showthread.php?t=82414)

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

Server-Side SQL Trace
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.


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:


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.


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.

All times are GMT -4. The time now is 04:09 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.