SQL Server Performance

Troubleshooting high CPU utilization

Discussion in 'T-SQL Performance Tuning for Developers' started by v1rtu0s1ty, Apr 10, 2004.

  1. v1rtu0s1ty New Member

    Hey guys,

    We're having a problem with a client-webserver-dbserver(3tier) configuration. I was able to pinpoint where the problem is. I found out that the CPU utilization on our 8 processor db server with 4 gig of RAM is reaching 90% and a very high Processor Queue Lenght value. I sorted the processes and found out that sqlserver was eating up the whole CPU. Problem is, I'm not a db guru but I can follow intructions. From my guess, it could be a cause of adhoc queries. I know that I can use Profiler but I don't know what to filter to really pin down the problem.

    Can someone help me on what to do to find out the root cause of our issues?

    Thanks.

    V1rt
  2. v1rtu0s1ty New Member

    I forgot, we are running Microsoft SQL 2000 server with all the latest patches.
  3. Luis Martin Moderator

    Run profiler with following events:<br />RPC:Completed <br />RPC<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtCompleted<br />Query: Batch Completed.<br /><br />Choise al least these columns: Duration, Reads, Writes,CPU.<br /><br />Check also, in SQL Server Properties, how many CPU are used by paralellism.<br /><br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com
  4. v1rtu0s1ty New Member

    Hi,<br /><br />I did not see "RPC<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtCompleted" and "Query:Batch Completed". I visited all the Available event classes one by one. However, I saw "SP<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtCompleted" under Stored Procedures and I also saw "SQL:Batch Completed" under TSQL. I also saw another SQL<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtCompleted in TSQL.<br /><br />Please verify.<br /><br />Thank you very much in advance.<br /><br />V1rt
  5. v1rtu0s1ty New Member

    And another question, what is the unit used in the CPU column? Is it percentage?

    Thanks again.

    V1rt
  6. v1rtu0s1ty New Member

    Ok guys, I just did a simple select. Looks like the CPU column is not percentage. It's because, I got a value of 180. So what unit is CPU column?<br /><br />Thanks. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />V1rt
  7. v1rtu0s1ty New Member

    Hey guys, sorry for the multiple post. Just found out that the CPU column is the CPU time in milleseconds. My question now is, how will we know the the query is CPU intensive such as eating 80% of the CPU?

    Thanks.

    V1rt
  8. derrickleggett New Member

    You don't. The CPU is millisecond of CPU time used by that one process. Anything very high at all should be looked at. If you have over 1000 milliseconds, that's 1 second of CPU time unavailable for any other proceess. You can see how this would starve the CPU if it runs hundreds or thousands of times a day.<br /><br />If you do everything in stored procedures, you just need to look at:<br />Stored Procedures:<br />&gt;RPC:Completed<br />&gt;SP<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtCompleted<br /><br />If you do not use stored procedures, you should start converting everything to stored procedures. In the meantime, monitor:<br />TSQL:<br />&gt;SQL:BatchCompleted<br /><br />Another thing to look for is:<br />StoredProcedures:<br />&gt;SP:Recompile<br />Scans:<br />&gt;Scan<img src='/community/emoticons/emotion-7.gif' alt=':S' />tarted (You need to find out if it's table scans)<br /><br />and cursors in any of your code.<br /><br />All of the above can take up large amounts of CPU, especially cursors. If you have any cursors at all, you might be able to see a significant improvement in your server by getting rid of them.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  9. toshtosh New Member

    Just to add to that...<br /><br />Switching to stored procedures and removing cursors would not be my first step as those are second-level optimizations. You can have a system without SPs and with cursors that runs fine.<br /><br />95% of the speedup will come from properly indexing and rewriting the queries so that they use the indexes. Look at Scan<img src='/community/emoticons/emotion-7.gif' alt=':S' />tarted as derrick says and also order your trace by the 'Reads' column and start looking at the heaviest queries.<br /><br />Cheers
  10. v1rtu0s1ty New Member

    Good morning guys,<br /><br />&gt; From derrickleggett<br />&gt; You don't. The CPU is millisecond of CPU time used by that one process. Anything very high at all should be looked at. If you have over 1000 milliseconds, that's 1 second of CPU time unavailable for any other proceess. You can see how this would starve the CPU if it runs hundreds or thousands of times a day.<br /><br />I am still trying to understand how the values relate to each other. I did a trace again last night on our application. Assuming, the CPU column is 4339(4.3 secs), the DURATION column is 18437(18.4 secs), and the READS column is 20000(20 secs), how can I tell that the query isn't efficient? I just got confused with the 1 second you showed in your reply. As an added info, we have about 700 to 800 user of this app.<br /><br />So for an 8 cpu, 4gig mssql production machine with 700-800 users(150-200 concurrent), what values in CPU, DURATION and READ should be optimal or should not exceed?<br /><br />&gt; From toshtosh<br />&gt; 95% of the speedup will come from properly indexing and rewriting the queries so that they use the indexes. Look at Scan<img src='/community/emoticons/emotion-7.gif' alt=':S' />tarted as derrick says and also order your trace by the 'Reads' column and start looking at the heaviest queries.<br /><br />For READs column, when can you say that a query is not doing well? What value?<br /><br />And guys, I found this link:<br /<a target="_blank" href=http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/mon_perf_33.htm>http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/mon_perf_33.htm</a><br /><br />Here is a snipped of the "Analyzing Data to Find Hotspots" section:<br /><br />SELECT TextData, Duration, CPU<br />FROM trace_table_name<br />WHERE EventClass = 12 -- SQL:BatchCompleted events<br />AND CPU &lt; (.4 * Duration)<br /><br />Can you explain to me the formula or the second condition that they used and how it can be useful for finding problems? Let say we have 4000 in CPU and 4500 in DURATION, that means 4 secs and 4.5 secs. So DURATION of 4.5 x .4 will give us 1.8 secs. So how can that statement be useful?<br /><br />Thanks for all the help. I really appreciate it.<br /><br />V1rt<br /><br /><br /><br />
  11. Luis Martin Moderator

    V1rt:

    1) When you are monitoring CPU using Performance Monitor you have severals options:
    a) %Total CPU
    B) %CPU used by each CPU.
    C) %CPU used by one process.

    In C is normal to have 180% because you have 2 or more CPU´s.

    2) In may first post I don't have any documentation, what I mean is what Derrick said, about events.
    3) If you run Profiler with those events you will find witch query is using more CPU and have a long duration. May be this query use a lot o reads, and/or writes.
    4) After you find it, you can use Query Analizer with that query a see execution plan to see if this query need some index.

    Also how many CPU are used to paralellism?





    Luis Martin
    Moderator
    SQL-Server-Performance.com
  12. v1rtu0s1ty New Member

    > Also how many CPU are used to paralellism?

    This is the one that I don't know where to get it. Can you tell what utility to use and what part of the screen is it?

    Thanks.

    V1rt
  13. Luis Martin Moderator

    Use Enterprise Manager, SQL Server Properties, Processor.
    In this tab you will find paralellism.
    Also in memory tab, check if memory is configurated dynamically.


    Luis Martin
    Moderator
    SQL-Server-Performance.com
  14. v1rtu0s1ty New Member

    In General Tab, it says we have 8 processors.
    In Processor tab, Use all available is selected in parallelism.
    But above it, only 4 CPUs are checked.

    Thanks.

    V1rt
  15. Luis Martin Moderator

    Ok, begin with Profiler to find queries with hight CPU, duration and Reads.
    Once you find it, cut from profiler and paste into SQL Analyzer, see execution plan to find:
    1) If there is any paralellism.
    2) Cost of each step.
    3) Full Scan over tables (tha's means no index are used).
    4) If you see Hight cost in any step (forget total cost) and/or Full scan, run Index Tuning Wizard, in SQL Analyzer. At end you will find ( or not) suggestions about indexs to improve performance.

    After all that and deppending on results we will look paralellism problem.


    Luis Martin
    Moderator
    SQL-Server-Performance.com
  16. derrickleggett New Member

    In General Tab, it says we have 8 processors.
    In Processor tab, Use all available is selected in parallelism.
    But above it, only 4 CPUs are checked.


    What version of SQL Server are you using? Are you saying that you only have 0-3 checked as processors to use by SQL Server? If this is the case, you need to take care of this if it's a dedicated SQL Server box, or you are just wasting the box.

    the READS column is 20000(20 secs),

    The READS are actual reads. READS and WRITES are in actual reads and writes against the system. DURATION and CPU are milliseconds of use.


    Switching to stored procedures and removing cursors would not be my first step as those are second-level optimizations. You can have a system without SPs and with cursors that runs fine.

    If you are running 700 connections and 300-500 t/sec, this statement is not true at all. Cursors can easily kill this type of system. And having this size of system without SPs would be incredibly inefficient from a cache usage, execution plan, and recompile perspective.

    Your system sounds like it's almost an exact match of the system I use every day. We have the same averages on connections, average 300-500 t/sec throughout the day with peaks at 1000.

    MeanOldDBA
    derrickleggett@hotmail.com

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

    Problem is, I have no idea if the store procedure is updating a table. If it is, then I cannot do that. Are you saying that I should only do it on queries that are doing reads? And am I correct that I will be able to verify by looking into the READ column on the profiler output?

    Thanks.

    V1rt
  18. derrickleggett New Member

    Problem is, I have no idea if the store procedure is updating a table. If it is, then I cannot do that. Are you saying that I should only do it on queries that are doing reads?

    I'm confused about this piece? What can you not do??? You want to be monitoring writes also.

    And am I correct that I will be able to verify by looking into the READ column on the profiler output?

    You will only be able to verify reads from here. You will still need to look at writes as well.

    MeanOldDBA
    derrickleggett@hotmail.com

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

    &gt;FROM derrickleggett:<br />&gt;What version of SQL Server are you using? Are you saying that you only have 0-3 checked as &gt<img src='/community/emoticons/emotion-4.gif' alt=';p' />rocessors to use by SQL Server? If this is the case, you need to take care of this if it's a &gt;dedicated SQL Server box, or you are just wasting the box.<br /><br />Yes, it is a dedicated MsSQL 2000 server. Are you saying that if it is a dedicated box and we have 8 processor and only 4 are checked, then I am wasthing the box. Am I correct?<br /><br />What could be the reason why the IBM support did it that way? It's just that until now, they can't find the issue. So, I want to do it myself and present to the management. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />I learned a lot from this threads btw.<br /><br />Thanks in advance.<br /><br />V1rt<br /><br />
  20. v1rtu0s1ty New Member

    I forgot to say derrick that only 0-3 are checked.
  21. v1rtu0s1ty New Member

    quote:Originally posted by LuisMartin

    Ok, begin with Profiler to find queries with hight CPU, duration and Reads.

    What values can be considered high for CPU, DURATION and READs?


    quote:
    Once you find it, cut from profiler and paste into SQL Analyzer, see execution plan to find:
    1) If there is any paralellism.
    2) Cost of each step.
    3) Full Scan over tables (tha's means no index are used).
    4) If you see Hight cost in any step (forget total cost) and/or Full scan, run Index Tuning Wizard, in SQL Analyzer. At end you will find ( or not) suggestions about indexs to improve performance.


    Ok, will try later.

    V1rt
  22. v1rtu0s1ty New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by derrickleggett</i><br /><br /><i>Problem is, I have no idea if the store procedure is updating a table. If it is, then I cannot do that. Are you saying that I should only do it on queries that are doing reads? </i><br /><br />I'm confused about this piece? What can you not do??? You want to be monitoring writes also.<br /><br /><i>And am I correct that I will be able to verify by looking into the READ column on the profiler output?</i><br /><br />You will only be able to verify reads from here. You will still need to look at writes as well.<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />Sorry derrick if I was confusing. I was worried that if I copy+paste the sp or statement from Profiler that is WRITE, I might update our production table. Forgive me for I have never used Show Execution Plan yet. It's gonna be my first time later. <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  23. toshtosh New Member

    Again, I think moving things to Stored Procedures is a last level optimization (but if you're designing something from scratch, might as well start with SPs). For instance, virtuosity mentions that he has a query consuming 20,000 reads!!! That's a lot of reads, most likely because the query is requiring scans. But of course, I agree that any system where the number of queries is overwhelming will benefit from SPs, no doubt.

    Anyway, to answer your question virtuosity, it's hard to determine a 'level' above which a number of 'Reads' is considered high, but anything over a couple hundred reads I usually try to cut down. In your case, 20000 is very high.

    As suggested by others, I would copy/paste that query into query analyzer, run it and look at the execution plan. Where to go from there is actually not easy to answer. It takes some learning to be able to analyze an execution plan and come up with a better query or change in indexes. You can try using the index tuning wizard (available in query analyzer) and let it make the decisions for you.

    Your server seems pretty heavy duty, is there no one else responsible in maintaining it that has experience that can help you?
  24. Luis Martin Moderator

    Well, Tosth, exactly words nothing more, nothing less.
    Like I said before, begin with something and then all of us will help you more easy.


    Luis Martin
    Moderator
    SQL-Server-Performance.com
  25. gaurav_bindlish New Member

    Okay I think my fello peers have contributed a lot but I would like to add my 2 cents....

    If the machine is having high CPU utilization, that does not mean that there is a performance issue but there can be one soon if the workload on the server increases. Howecer it may be the case that you are already seeing some performance problems as it is evident from your posts.

    Now before looking at the CPU column, I would rather look at the duration column, as I assume that if a query is having high utilization / high IO rate, it is bound to run relatively slower than rest of the queries. However if I know that certain set of queries run morre frequently than others, I would focus on them. But this is generally not the case (at least I am not so lucky).

    Once the slow running queries have been identified, I would try to understand as to what is happening inside the query. Frankly speaking, I don't rely on the CPU and IO column in the profiler if I am profiling on a production system as there are so many events happeneing in the system, the probability of error is so high. So I would take the query along with the parameters and try to exxcute it in Query analyzer to understand the execution plan.

    The execution plan and the statistics will guide me to the next steps in the tuning of the procedure.

    HTH.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  26. v1rtu0s1ty New Member

    Ok guys, I will start my profiler now and hopefully be able to extract some good values. Then, I will let you know about it.

    By the way, assuming I found an SP that updates a table and that it is taking for than 1.5 minutes. I don't want to paste this query in Query Analyzer since it might destroy the production data. Am I correct that I really should not do it?

    Thanks for all the input.

    V1rt
  27. Luis Martin Moderator

    If you paste into SQL Analyzer the idea is to run execution plan, no to run the query.
    Execution Plan, see what's goin on if you run the query, is a simulation.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  28. v1rtu0s1ty New Member

    Hey guys,

    I did a profiler trace of another application(not the production I am referring too).

    Here is a screenshot of execution plan:
    http://restricted.dyndns.org/target/result.png

    Can someone explain what the execution plan output mean? Most of them were 0% Cost, but Bookmark Lookup is 99%. It took more than 7 minutes before MsSQL returned the result.

    With your explanation, I will have more understanding what's going on.

    Thanks.

    Neil
  29. Luis Martin Moderator

    In your link I can only see part of all execution plan.
    I suggest to look articles in our forum, some of then explain in detail what each step means.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  30. derrickleggett New Member

    A bookmark lookup is caused when you don't have a covering index. Since this bookmark lookup is such a large part of the overall cost, you really don't need to post anything else.

    Basically, if you look at the origin of this arrow, you will see that it's looking at an index that doesn't contain all the values used from this table in your FROM and WHERE clauses. Because of this, it's finding the records it can in your existing index and "bookmarking" the position. It is then reading the other rows with what would equate to a table scan and matching them up to the positions in the bookmark lookup.

    If you can afford the extra writes an index would create, add a covering index that has all columns for this table used in the FROM and WHERE clauses. You will then see this BOOKMARK LOOKUP disappear and be replaced by a NESTED LOOP join.

    This will take your total cost down dramatically.

    In the convering index, you will want to have the columns first in the index that would reduce the recordset the fastes. In otherwords, have the index ordered from most selective to least. This will allow the pages of your index to be smaller that represent the outer pages of your index.

    Make sense?

    MeanOldDBA
    derrickleggett@hotmail.com

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

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by gaurav_bindlish</i><br /><br />Okay I think my fello peers have contributed a lot but I would like to add my 2 cents....<br /><br />If the machine is having high CPU utilization, that does not mean that there is a performance issue but there can be one soon if the workload on the server increases. Howecer it may be the case that you are already seeing some performance problems as it is evident from your posts.<br /><br />Now before looking at the CPU column, I would rather look at the duration column, as I assume that if a query is having high utilization / high IO rate, it is bound to run relatively slower than rest of the queries. However if I know that certain set of queries run morre frequently than others, I would focus on them. But this is generally not the case (at least I am not so lucky).<br /><br />Once the slow running queries have been identified, I would try to understand as to what is happening inside the query. Frankly speaking, I don't rely on the CPU and IO column in the profiler if I am profiling on a production system as there are so many events happeneing in the system, the probability of error is so high. So I would take the query along with the parameters and try to exxcute it in Query analyzer to understand the execution plan. <br /><br />The execution plan and the statistics will guide me to the next steps in the tuning of the procedure.<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />I tried tracing from 7 to 12 this morning but the only longest running query I found out was just 27 secs. <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br /><br />Anyways, I found a link guys.<a target="_blank" href=http://vyaskn.tripod.com/analyzing_profiler_output.htm>http://vyaskn.tripod.com/analyzing_profiler_output.htm</a><br />What can you say about his trace defintion?<br /><br />Also, I really want to learn heavily on this area. What books should I buy?<br /><br />Thanks again guys.<br /><br />V1rt
  32. satya Moderator

  33. Luis Martin Moderator

    Indeed.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  34. v1rtu0s1ty New Member

    I just ordered:
    Inside SQL Server 2000 by Kalen Delaneay

    What about this book? Someone reviewed this book and he actually wants to call it as MsSQL bible.

    Title: Microsoft SQL Server 2000 Performance Tuning Technical Reference
    Authors: Edward Whalen, Marcilina Garcia, Steve Adrien Deluca, Michael Dean Thompson, Jamie Reding
    Publisher: Microsoft Press

    Thanks guys.

    V1rt
  35. toshtosh New Member

    I liked 'SQL Server Query - Performance Tuning Distilled' by Sajal Dam.

    Yes, I know, stupid title.

  36. v1rtu0s1ty New Member

    quote:Originally posted by toshtosh

    I liked 'SQL Server Query - Performance Tuning Distilled' by Sajal Dam.

    Yes, I know, stupid title.

    toshtosh,

    But have you read Microsoft SQL Server 2000 Performance Tuning Technical Reference?

    Thanks.

    V1rt
  37. satya Moderator

    V1rt

    My oversight about missing the reference of this good book, as it covers the basics/tuning/planing etc on the terms of Performance Tuning.

    Both books have valuable information about SQL server.

    quote:Originally posted by v1rtu0s1ty

    I just ordered:
    Inside SQL Server 2000 by Kalen Delaneay

    What about this book? Someone reviewed this book and he actually wants to call it as MsSQL bible.

    Title: Microsoft SQL Server 2000 Performance Tuning Technical Reference
    Authors: Edward Whalen, Marcilina Garcia, Steve Adrien Deluca, Michael Dean Thompson, Jamie Reding
    Publisher: Microsoft Press

    Thanks guys.

    V1rt

    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.
  38. FrankKalis Moderator

    quote:Originally posted by v1rtu0s1ty

    I just ordered:
    Inside SQL Server 2000 by Kalen Delaneay

    What about this book? Someone reviewed this book and he actually wants to call it as MsSQL bible.

    Title: Microsoft SQL Server 2000 Performance Tuning Technical Reference
    Authors: Edward Whalen, Marcilina Garcia, Steve Adrien Deluca, Michael Dean Thompson, Jamie Reding
    Publisher: Microsoft Press

    Thanks guys.

    V1rt
    I agree with satya and Luis!
    Inside SQL Server by Kalen Delaney is IMHO the only decent book from MS Press. And the only one on SQL Server that's entirely worth reading.

    I don't know the *Performance Tuning* book you've mentioned, but I did a review of *SQL Server 2000 Administrators' Companion*, which is mostly written by the same authors
    http://www.sqlservercentral.com/col...eviewofsqlserver2000administratorscompani.asp and found that book to be not suited for advanced users. So I suggest to take a really close look in a bookstore before you buy the *Performance Tuning* book.


    Frank
    http://www.insidesql.de
    http://www.familienzirkus.de
  39. toshtosh New Member

    Haven't read either of the two books mentionned. Might give em a try. Just throwing in another option with the book I mentionned!
  40. derrickleggett New Member

    quote:Originally posted by v1rtu0s1ty


    quote:Originally posted by toshtosh

    I liked 'SQL Server Query - Performance Tuning Distilled' by Sajal Dam.

    Yes, I know, stupid title.

    toshtosh,

    But have you read Microsoft SQL Server 2000 Performance Tuning Technical Reference?

    Thanks.

    V1rt

    I have this book setting on my desk and use it all the time. It's right beside my "Inside SQL Server" book. It's an invaluable reference for performance tuning.

    MeanOldDBA
    derrickleggett@hotmail.com

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

    I would like to thank everyone who contributed their ideas. All the replies were really helpful. I would like to let everyone know that I have also decided to pursue with MSSQL certification <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Thank you very much again.<br /><br />V1rt
  42. v1rtu0s1ty New Member

    Hey guys,

    Quick question. When blocking on the database server happens, does it up CPU?

    Thanks.

    V1rt
  43. Luis Martin Moderator

    Not neccesary. You can have a query taking all CPU and blocking others (some page or table) or one query with litle CPU and high disk blocking others too.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  44. gaurav_bindlish New Member

    I second to what has been suggested. Blocking doesn not mean that the CPU usage will be high. There may be a case where the query which is blocking the other query is waiting on some disk IO or network request. In this case you'll see very little CPU utilization.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  45. v1rtu0s1ty New Member

    Yeah, you know what, we've been having a very hard time figuring out the root cause of our slow performance problem. Today, the business called us again at 10am. They said that our JAX and FLO sites were experiencing slow performance. We analyzed each device/host that is part of the application system. No issues on the network part. While the person was experiencing issue, we did a ping test and reply was average 56ms. We coordinated with the load balancer guys and told us that the BigIP(loadbalancer) is performing well. I also had a job running on all our webservers monitoring CPU utilization and Processor Queue Length, and all of them were in 10% to 15% utilization. And lastly for the db servers, there were very minimal spikes on the CPU utilization but it happened at 12pm and it didn't even lasted for 1 minute. This is the reason why I asked that kind of question. It's making us nuts!!! <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />You know what, I haven't really monitored the I/O yet. Although, it's a SAN attached.<br /><br />LuisMartin, you mentioned about high disk blocking. What will you recommend me now to monitor?<br /><br />Thanks guy. You've been very helpful.<br /><br />V1rt
  46. Luis Martin Moderator

    Performance Monitor, Physical disk, Avg. Disk Queue Length.
    Depending on you RAID, but suppose 1 disk, then values > 2 for 5 o 10 minuts is a bad value. If that is what happends, you have to trace with profiler and find queries or sp to optimize.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  47. satya Moderator

  48. v1rtu0s1ty New Member

    Hi satya,

    I am following the steps in this link on my non-production mssql box:
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;271509

    This is what I got when I pasted the whole stored procedure(sp_blocker_pss80) to QA.

    Server: Msg 207, Level 16, State 3, Procedure sp_blocker_pss80, Line 34
    Invalid column name 'sql_handle'.
    Server: Msg 207, Level 16, State 1, Procedure sp_blocker_pss80, Line 34
    Invalid column name 'stmt_start'.
    Server: Msg 207, Level 16, State 1, Procedure sp_blocker_pss80, Line 34
    Invalid column name 'stmt_end'.
    Server: Msg 207, Level 16, State 1, Procedure sp_blocker_pss80, Line 123
    Invalid column name 'sql_handle'.
    Server: Msg 207, Level 16, State 1, Procedure sp_blocker_pss80, Line 123
    Invalid column name 'stmt_start'.
    Server: Msg 207, Level 16, State 1, Procedure sp_blocker_pss80, Line 123
    Invalid column name 'stmt_end'.

    And also, I have another question on step 2 of that link. Actually, this is the script written in step 2.

    WHILE 1=1
    BEGIN
    EXEC master.dbo.sp_blocker_pss80
    -- Or for fast mode
    -- EXEC master.dbo.sp_blocker_pss80 @fast=1
    -- Or for latch mode
    -- EXEC master.dbo.sp_blocker_pss80 @latch=1
    WAITFOR DELAY '00:00:15'
    END
    GO

    On the URL I included above, it doesn't tell how the loop will end. So how does it end? Does it also say on the link that it can be run safely/continuously on a production MSSQL server say from 8am to 5pm?

    Thanks guys.

    V1rt
  49. satya Moderator

    For error 207 check under SYSPROCESSES table whether these columns exists or not, must be present.
    If not ensure to maintain on latest service pack on SQL Server.

    Then coming to schedule the script,
    If you want to run the SP in Fast mode then uncomment EXEC master.dbo.sp_blocker_pss80 @fast=1.
    Otherwise using Latch mode then uncomment EXEC master.dbo.sp_blocker_pss80 @latch=1 section of the code on QA.


    I suggest you to run thorougly on the test server and get used the script and results. Then you deploy on the production server and follow as suggested in the KBA This output is very useful when combined with a Microsoft Windows NT Performance Monitor log and a SQL Profiler log, so creating both at the same time is suggested.

    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.
  50. v1rtu0s1ty New Member

    Aha, I don't have any mssql service pack installed on my test box. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />That's why, I am missing that field.<br /><br />So, the KBA I've shown will helped me pinpoint the root cause of our application problem?<br /><br />Thanks Satya.<br /><br />V1rt
  51. satya Moderator

    Yes, basically the blocker script helps to findout the blocking during the application processing the statements against the database.

    Also as suggested take help of PERFMON to assess the hardware and SQL counters and PROFILER to track down the culprit statements, that contribute the blocking on the database.

    If the application is written IN-HOUSE then its easy to fine tune the code on the application, if not then you may have hard-time to prove on the third party application. In this case take pre-cautionary measures and allow window to perform DBCC checks and other db maintenance tasks to fine tune the database performance.


    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.
  52. v1rtu0s1ty New Member

    This scripts that I will be using is harmless right? It doesn't update any table right?

    Thanks.

    V1rt
  53. v1rtu0s1ty New Member

    Hey Satya,

    After installing service pack 3, I tried installing the stored procedure. There was a minor error telling me that the sp already existed. Looks like SP3 added it. Anyways, since I don't have any heavy application on my test sql server, can you or someone share a sample log/report of this sp that shows an affected slow performing application? If so, can you also highlight the item w/c will show that it is affecting the system? With this, I will be able to easily learn how to analyze a report or log.

    Thanks.

    V1rt

  54. satya Moderator

  55. v1rtu0s1ty New Member

    Hey guys,

    What can you say about long periods of high Processor Queue Lenght but below 30% CPU utilization?
    What does it mean? Is the machine still burdened?

    Thanks.

    V1rt
  56. satya Moderator

    http://www.sql-server-performance.com/q&a2.asp for information.

    You should monitor perfomance for a while, see what processes cause high memory / processor load.
    There are a lot of legacy applications which could cause this behavior.


    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.
  57. v1rtu0s1ty New Member

    quote:Originally posted by satya

    http://www.sql-server-performance.com/q&a2.asp for information.

    You should monitor perfomance for a while, see what processes cause high memory / processor load.
    There are a lot of legacy applications which could cause this behavior.


    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.

    On the Task Manager, it's the sqlserver. I sorted it by CPU column then all the processes under sqlserver is 0 while him is dancing 80%-90%.

    Going back to my question, if cpu is below 30% but processor queue lenght is very high for a sustained period of time, can we say that the server is having a performance issue?

    Thanks satya.

    V1rt
  58. gaurav_bindlish New Member

    I am not sure if this has been discussed before, did you observe any locking during the periods when CPU queue length is high? What is the recovery mode for the database. How many users typically connect to the server and are they present on slow networks or 100 Mbps lines? What is the database size and Txn log apsce for database under consideration? What is their growth increment set to? Are there any jobs schedules on the same which execute during the day. Do you reindex the database regularly or else, what is the maintainence activity for the database?

    I know these are lotof questions but I think having them answered will help us better.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  59. Luis Martin Moderator

    CPU 30% with queue length > 2 for a long period is no good, but I suggest to response Gaurav questions first to evaluate all picture.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  60. satya Moderator

    I would suggest to take help of PERFMON(SYSMON) to evaluate the counters, not just the task manager.

    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.
  61. biged123456 New Member


    We also like the Kalen Delaney publication.

    I like to equate tuning SQL to tuning a slow car. If you are trying to figure out why a car won't go fast and you are looking at the motor oil (queries) because the engine is running hot (CPU high), but haven't looked at the rest of the components, you may have missed that all 4 tires are flat. A good article on how to check all the components is available athttp://www.sql-server-performance.com/sg_sql_server_performance_article.asp. I would suggest this before looking at individual queries.

    About half of the CPU problems I have seen on SQL servers were really related to disk problems (improper disk configuration, disk queuing, index problems causing increase disk activity, low memory causing disk paging, etc)
  62. v1rtu0s1ty New Member

    Thanks biged123456. That link is very useful. I learned to use Profiler but I want to learn a different approach. Can someone here tell me the procedure on how to do server side tracing? What machine is it done, on a client?

    Thanks in advance.

    V1rt
  63. v1rtu0s1ty New Member

    Hey guys,

    I was playing with perfmon and happen to see SQLServer:Locks Performance object. Is this good enough as a 20000 feet view to monitor db locks as to the ones found by Profiler?

    Thanks.

    V1rt
  64. gaurav_bindlish New Member

  65. v1rtu0s1ty New Member

    Hi gaurav,

    I am watching the webcast now. I would like to monitor SQLServer:Locks but what counters would be very beneficial for troubleshooting?

    Thanks.

    V1rt
  66. gaurav_bindlish New Member

    Generally speaking, I would look at the Locks counter only to see if there is a locking problem in the system. If there is, I would investigate the locks using the profiler script mentioned in the above articles. Perfmon gives and overview but to drill down you need to profile the application.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  67. v1rtu0s1ty New Member

    I am watching webcast and I'm on the sqldiag part now. Is this tool destructive? Does it change anything on the system tables on an MSSQL server 2000 machine?
  68. v1rtu0s1ty New Member

  69. Luis Martin Moderator

    If you have SQL 2000, I don't see any problem to run this script

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  70. v1rtu0s1ty New Member

    Luis,

    Ok. I also saw on the webcast presentation which added dbcc traceon(3604) prior to the loop. What is the difference if I don't execute that dbcc command?

    Thanks.

    V1rt
  71. satya Moderator

    SQLDiag is an useful tool to capture all the details on the server side when any issue arises, if you're dealing with MS Support the first report they would ask is SQLDiag report only.

    Blocker script is a valueable script to get to know any blocking on the server, useful but test it before depoying on the production.
    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.
  72. v1rtu0s1ty New Member

    Hey guys,

    Going back to the blocker script and on the first webcast.
    I was looking at this table:
    ------+-------------------
    Trace | Flag Explanation
    ------+-------------------
    3604 |Directs trace output to the client, rather than to the error log.
    ------+----------------------------

    On the this link,http://support.microsoft.com/default.aspx?scid=kb;EN-US;271509
    it does not mention dbcc traceon(3604)

    While on the webcast presentation, dbcc traceon(3604) was added before the loop.

    My question now is, if I turn it on, will it affect other people that are also doing trace?

    Thanks.

    V1rt

  73. gaurav_bindlish New Member

    I believe SQL Traces have global impact and are not set for a user connection.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  74. satya Moderator

    Always test the trace before implementing on the production, as referred bY Gaurav its an impact process.


    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.
  75. Luis Martin Moderator

    I suggest to begin with new post about trace, now is fare away from high CPU utilization. Just a though.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  76. satya Moderator

    I believe this is one of the longest thread with valuable suggestions by the peers in the forum.
    Hope the originator is get thru the information required.

    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.
  77. v1rtu0s1ty New Member

    Hey everyone,<br /><br />YES!!! All the inputs were very helpful. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />I did learn a lot from this thread. Tonight, I will copy+paste this thread and put it to my notes.<br /><br />Thanks again.<br /><br />V1rt
  78. gaurav_bindlish New Member

    I wonder how our life would have been if COPY and PASTE functionalities were not "supported" [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Gaurav<br /><i>Moderator<br /><font size="1">Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard</i></font id="size1"><br /><font size="1">The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.</font id="size1">
  79. Luis Martin Moderator

    In that case I give up[<img src='/community/emoticons/emotion-3.gif' alt=':eek:' />)].<br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br />The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Share This Page