SQL Server Performance

profiler

Discussion in 'Performance Tuning for DBAs' started by v1rtu0s1ty, Sep 30, 2004.

  1. v1rtu0s1ty New Member

    Can profiler cause performance issues? profiler was ran to capture currently happening blocks!

    Thanks

    V1rt
  2. Luis Martin Moderator

    Depends of how many columns do yo add in profiler, and duration.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  3. v1rtu0s1ty New Member

    I added those found at this link;<a target="_blank" href=http://support.microsoft.com/default.aspx?scid=kb;en-us;q224453>http://support.microsoft.com/default.aspx?scid=kb;en-us;q224453</a><br /><br />I added RPC<img src='/community/emoticons/emotion-7.gif' alt=':S' />tarting, SQL:BatchStarting, SP: StmtStarting, Exception and RPC:Completed<br /><br />What do you think about it?<br /><br />
  4. Luis Martin Moderator

    The problem, in performance terms, is not events (RPC,SQL,etc).
    What you choose is very good election to trace.
    You have to care about columns (i.e: Application Name, LoginName, SPID, Duration, etc).
    My recomendations is: choose only neccesary columns and filter application name (i.e: not = Sql Alert - Engine) and filter Duration to some usefull value (i.e: grather than 100 milisecons)



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  5. v1rtu0s1ty New Member

    what if it only left 350mb of freespace in drive c? it's because profiler creates a .tmp file. Will it affect performance of the system?
  6. Luis Martin Moderator

    To minimaze performance issue is recomendable to run profiler in workstation.
    No one of my clients compleint when I run profiler 6 hours from my laptop.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  7. derrickleggett New Member

    There are three overheads associated with SQL Server Profiler:

    1. The overhead of the trace itself. This is not very high. If you run the Profiler from a machine other than the server (and have good bandwidth), you will still see an extremely small hit in performance from capturing the trace events.

    2. The overhead of Profiler application. This is the heaviest hit generally. The Profiler applicaiton is not very efficient. In earlier verisons of SQL Server it was also susceptible to memory leaks. It's generally not recommended to run Profiler on a production server if you can avoid it.

    3. The overhead of the trace files. Profiler creates trace files, then "streams" the file to the GUI, a database table, etc. This incurs extra writes to disk as the traces are processes. This of course involves memory and disk IO overhead.

    I agree with Luis. I have a machine setup that does nothing but run profiler, capture perfmon files, and run reports against them. We see no visible impact on production servers by doing it this way. We have a good backplane and network connections though to support this.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  8. v1rtu0s1ty New Member

    I will follow all your advice. I have a question though on the READ column from my trace. Value that I am getting related to sp_cursor averaging 45. I also see some at 6000, 1074, 6578, and 827. What does this value mean?

    I saw tons of cursor in profiler. I have read a lot about cursors and a lot of people are recommending not to use it. Can those thousands of continuous cursors that I saw is contributing to the bad performance of the application?

    Thanks guys...

  9. derrickleggett New Member

    Absolutely. Cursors are one of the worst things you can use in SQL Server. The reads will vary by the what type of cursor and what the cursor is actually doing. You need to track down what has the cursor and see if you can change it to a set-based statement. If you can, you can make big improvements on your overall server performance.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  10. v1rtu0s1ty New Member

    I am having a hard time deciphering the trace.

    I have read athttp://www.winnetmag.com/Article/ArticleID/7046/7046.html that if I see tons of sp_cursorfetch, the application is doing server-side API cursor.

    What should I look for in that trace? What should I do to easily understand that trace?

    Any help will be greatly appreciated!

    Looks like i'm about to find out the culprit! Although, I have the CPU graph shown at 90%

    Thanks guys.....

  11. v1rtu0s1ty New Member

    I mean, what should I look for to find the culprit query?
  12. Luis Martin Moderator

    I'll look for long duration with big reads/writes.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  13. v1rtu0s1ty New Member

    is value of 1000 a big READ?
  14. v1rtu0s1ty New Member

    or probably, what value is considered acceptable? What type of UNIT is in the READ column?
  15. Luis Martin Moderator

    At home, I don't remember now (I think rows). But, 1000 is not big at all.
    In my customers, reads big is more than 100000, of course 1000 is not big if write is less than that.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  16. v1rtu0s1ty New Member

    I saw another one, READS=2097, DURATION=843 and WRITES=0. If this is low, what then can be contributing to the poor performance? I have verified our network, and response time durin those times were really good.
  17. Luis Martin Moderator

    All depends. I can tell about my experience and customers.
    Duration 843 is less than 1 second. Reads and Writes are nothing.
    May be you have same problem than me.
    Is a 3rd party software, because if yes, I have huge problems with compiled code like:

    sp_execute 1,5,7

    with a lot of reads, and high duration. I can't work with that. Only I can do is: look customer screen and image what is important, may be a field date or something.

    Other posibility is Store Procedures.
    Some times sp take long time to do his job, because was writen by and Alien guy.
    In my case, I´m not able to modify code, so I try to split sp and analyze each query to try to optimize with indexs.

    Something like this is your case?

    Last (for today, is late at home and my wife want to dinner) did you have last Service Pack(OS and SQL) and last MDAC in your workstations?. (See you tomorrow).


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  18. v1rtu0s1ty New Member

    I don't see sp_execute but I see

    exec sp_cursorfetch 180370068, 16, 11, 1
    exec sp_cursorfetch 180388642, 16, 10, 1
    exec sp_cursorfetch 180370068, 16, 13, 1
    exec sp_cursorfetch 180388642, 16, 14, 1

    I see thousands of this ...and yes, it is a 3rd party application
    and also yes, we have the latest service pack

    thanks Luis...

  19. v1rtu0s1ty New Member

    I found the culprit ..... <img src='/community/emoticons/emotion-2.gif' alt=':D' />, a query that was eating up cpu and was running for more than 1 hour
  20. v1rtu0s1ty New Member

    Hey guys,<br /><br />I dumped my profiler trace to a table in separate sql server. And I executed;<br /><br />SELECT Starttime,SPID,Duration,Reads,Writes,CPU, Textdata,ApplicationName,LoginName,hostname FROM mytrace WHERE Duration IN <br />(SELECT DISTINCT TOP 20 Duration <br />FROM mytrace ORDER BY Duration DESC) order by duration desc<br /><br />I was shocked with the result I saw. There were SPIDs that have been running for more than 1.5 hours(Duration columns). Unfortunately, the TextData column were NULL. ApplicationName were NULL.<br /><br />Below are the values from the READs column whose Textdata columns are NULL<br /><br />1. 776398<br />2. 3377970<br />3. 82857<br />4. 747537<br />5. 747572<br />6. 40997<br /><br />And the CPU values for those SPIDS are<br /><br />1. 7515<br />2. 50746<br />3. 5125<br />4. 54937<br />5. 60000<br />6. 1610<br /><br />Should I disregard this result? Or is it something that can be considered as the culprit?<br />And also, how do I convert the CPU value? Coz I am used to seeing CPU utilization in percentage(%) such us in 90% cpu utilization. What about the READs? What are those very high numbers? How should they be interpreted?<br /><br />Any help will be greatly appreciated....<br /><br />Thanks guys...<br /><br />Oh btw, I also have other rows that shows queries that have long duration such as 90000. I think, this is in milliseconds. So that'd be 90 seconds. That's not good. <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  21. satya Moderator

    How you capture the profiler trace using GUI tool or with statements?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  22. v1rtu0s1ty New Member

  23. v1rtu0s1ty New Member

    And also, just want to let you know that I can see the corresponding user account being used by the client
  24. Twan New Member

    make sure you ignore spids < 50 which are system spids

    Cheers
    Twan
  25. v1rtu0s1ty New Member

    but is the explanation regarding long duration from the webserver but doesn't have data in textdata column?

    thanks...
  26. satya Moderator

  27. skasam New Member

    Regarding your questions below:<br /><br />Should I disregard this result? <br />&gt; No. It really depends on what eventclass this duration and cpu is for.<br />&gt; For example, if this is for Logout (where textdata is NULL) then you should be OK. You &gt; can neglect it.<br />&gt; But if it some other intersting event, you should watch out for it.<br /><br />Or is it something that can be considered as the culprit?<br />&gt; See above comment<br /><br />And also, how do I convert the CPU value? Coz I am used to seeing CPU utilization in percentage(%) such us in 90% cpu utilization.<br />&gt; If you really want any meaningful data, you should correlate the Performance Monitor &gt; &gt; data you capture to thge profiler trace you capture at the same time and then start &gt; &gt; corelating it. So when you see 90% CPU usage in Perfmon, you drill into the trace file &gt; to see at that time what events took the most CPU and duration and toubleshot from &gt; &gt; &gt; that point of time.<br /><br />What about the READs? What are those very high numbers? How should they be interpreted?<br />&gt; READs is Page IOs. Each Read is an 8K data read into memory. See the first comment for &gt; this too.<br /><br /><br />Thanks,<br />-- Sri<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by v1rtu0s1ty</i><br /><br />Hey guys,<br /><br />I dumped my profiler trace to a table in separate sql server. And I executed;<br /><br />SELECT Starttime,SPID,Duration,Reads,Writes,CPU, Textdata,ApplicationName,LoginName,hostname FROM mytrace WHERE Duration IN <br />(SELECT DISTINCT TOP 20 Duration <br />FROM mytrace ORDER BY Duration DESC) order by duration desc<br /><br />I was shocked with the result I saw. There were SPIDs that have been running for more than 1.5 hours(Duration columns). Unfortunately, the TextData column were NULL. ApplicationName were NULL.<br /><br />Below are the values from the READs column whose Textdata columns are NULL<br /><br />1. 776398<br />2. 3377970<br />3. 82857<br />4. 747537<br />5. 747572<br />6. 40997<br /><br />And the CPU values for those SPIDS are<br /><br />1. 7515<br />2. 50746<br />3. 5125<br />4. 54937<br />5. 60000<br />6. 1610<br /><br />Should I disregard this result? Or is it something that can be considered as the culprit?<br />And also, how do I convert the CPU value? Coz I am used to seeing CPU utilization in percentage(%) such us in 90% cpu utilization. What about the READs? What are those very high numbers? How should they be interpreted?<br /><br />Any help will be greatly appreciated....<br /><br />Thanks guys...<br /><br />Oh btw, I also have other rows that shows queries that have long duration such as 90000. I think, this is in milliseconds. So that'd be 90 seconds. That's not good. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Thanks,<br />-- Sri

Share This Page