SQL Server Performance Forum – Threads Archive

Troubleshooting high CPU utilization

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

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

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

Hi, 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. Please verify. Thank you very much in advance. V1rt

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

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? Thanks. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> V1rt

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

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. If you do everything in stored procedures, you just need to look at: Stored Procedures: >RPC:Completed >SP<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />tmtCompleted If you do not use stored procedures, you should start converting everything to stored procedures. In the meantime, monitor: TSQL: >SQL:BatchCompleted Another thing to look for is: StoredProcedures: >SP:Recompile Scans: >Scan<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />tarted (You need to find out if it’s table scans) and cursors in any of your code. 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. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

Just to add to that… 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. 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. Cheers

Good morning guys, > From derrickleggett > 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. 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. 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? > From toshtosh > 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. For READs column, when can you say that a query is not doing well? What value? And guys, I found this link: http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/mon_perf_33.htm</a> Here is a snipped of the "Analyzing Data to Find Hotspots" section: SELECT TextData, Duration, CPU FROM trace_table_name WHERE EventClass = 12 — SQL:BatchCompleted events AND CPU < (.4 * Duration) 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? Thanks for all the help. I really appreciate it. V1rt

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

> 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

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

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

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

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
[email protected] When life gives you a lemon, fire the DBA.

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

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
[email protected] When life gives you a lemon, fire the DBA.

>FROM derrickleggett: >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 >dedicated SQL Server box, or you are just wasting the box. 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? 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=’

‘ /> I learned a lot from this threads btw. Thanks in advance. V1rt

I forgot to say derrick that only 0-3 are checked.

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

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by derrickleggett 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. <hr height="1" noshade id="quote"></blockquote id="quote"> 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=’

‘ />

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?

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

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.

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

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.

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

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.

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
[email protected] When life gives you a lemon, fire the DBA.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by gaurav_bindlish 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. <hr height="1" noshade id="quote"></blockquote id="quote"> 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=’

‘ /> 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> What can you say about his trace defintion? Also, I really want to learn heavily on this area. What books should I buy? Thanks again guys. V1rt

Inside SQL Server 2000 by Kalen Delaneay. 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.

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.

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 liked ‘SQL Server Query – Performance Tuning Distilled’ by Sajal Dam. Yes, I know, stupid title.

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

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.

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

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

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
[email protected] When life gives you a lemon, fire the DBA.

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=’

‘ /> Thank you very much again. V1rt

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

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.

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.

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=’

‘ /> You know what, I haven’t really monitored the I/O yet. Although, it’s a SAN attached. LuisMartin, you mentioned about high disk blocking. What will you recommend me now to monitor? Thanks guy. You’ve been very helpful. V1rt

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.

Handful of articles to troubleshoot the performance on Application and H/w side too :
http://support.microsoft.com/default.aspx?scid=kb;en-us;224587
http://support.microsoft.com/default.aspx?scid=kb;en-us;298475
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp 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.

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

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.

Aha, I don’t have any mssql service pack installed on my test box. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> That’s why, I am missing that field. So, the KBA I’ve shown will helped me pinpoint the root cause of our application problem? Thanks Satya. V1rt

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.

This scripts that I will be using is harmless right? It doesn’t update any table right? Thanks. V1rt

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

Yes no system tables are affected with the script.
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.

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

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.

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

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.

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.

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.

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)

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

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

See if this can answer your questions…. http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc082401/wcblurb082401.asp
http://support.microsoft.com/default.aspx?scid=kb;en-us;283790
http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc051800/wcblurb051800.asp
http://support.microsoft.com/default.aspx?scid=kb;en-us;283784
http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc111400/wcblurb111400.asp
http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc011502/wcblurb011502.asp 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.

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

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.

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?

Hey guys, sorry for multiple post. Is it safe to create blocker script found on this link?
http://support.microsoft.com/default.aspx?scid=kb;EN-US;271509 Thanks. V1rt

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.

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

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.

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

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.

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.

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.

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.

Hey everyone, YES!!! All the inputs were very helpful. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> I did learn a lot from this thread. Tonight, I will copy+paste this thread and put it to my notes. Thanks again. V1rt

I wonder how our life would have been if COPY and PASTE functionalities were not "supported" [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] 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.

In that case I give up[<img src=’/community/emoticons/emotion-3.gif’ alt=’ :eek:

‘ />)]. 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.

]]>