Stored Procedure uses 100% CPU

[crystalreporting]

We have a stored procedure that, when run, takes the CPU usage up to 100%, and holds it at 100% until it is cancelled or completes. This affects other services (network etc.) on the SQL server. Other stored procedures DO NOT have thsi effect so I suspect the stored procedure is not written as well as it could be. In any event, there should (must?) be some way to prevent SQL from 'stealing' every resource on the server? Any ideas?

Peter Shirley

http://www.linkcorp.com

 

[Krickles]

Instead of trying to limit the resources (which is an option), why not correct the sproc? It sounds like you have a Cartesian product.

Start troubleshooting by looking at the joins and by examining the execution path in Query Analyzer. If you haven't looked at the execution path before, do the following:

Open QA --> Load sproc in main window --> CTRL + L

The result should be a graphical representation of your execution path in the bottom window. You will then be able to see which section of the sproc is taking the majority of the resources.

Good luck.

Krickles | 1.6180

 

[crystalreporting]

Thanks - and obviously the stored procedure needs reworked, as our other stored procedures DO NOT peg the resource usage. I guess I was more interested in establishing how we can prevent the CPU from being pegged if and when we write an inefficient stored procedure. If there's no way to control this, then we wont know a stored procedure is written poorly until the CPU hits 100% and everybody else on the server stops working...

Peter Shirley

http://www.linkcorp.com

 

[MDXer]

If there's no way to control this, then we wont know a stored procedure is written poorly until the CPU hits 100% and everybody else on the server stops working

If you analyze your queries and use the execution plans you will know what if the query has issues. Items like Table Scans Index Scans Hash matches are all costly operations and easly identified by looking at the execution plan. It is probably one of the best and least used tools in SQL2K.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Krickles]

You can control the amount of resources an instance of SQL Server claims in SQL Server Properties:

EM --> Tools --> SQL Server Configuration Properties

As far as I know you can't do this per sproc.

I maintain, if the problem is with the sproc, repair it before you move forward.

Paradigm: If you are in your car, turn on the A/C, and then the car stops moving until the A/C cools off the entire car; would you develop a way to limit the resources of the A/C or would you have the A/C repaired?

Improving the performance of the sproc will help the overall performance of the server and all dependencies.

Krickles | 1.6180

 

[crystalreporting]

Bad example - stored procedures aren't provided by the manufacturer (Microsoft), A/C is (by the vehicle manufacturer) and it cannot be customized by the driver.

To use your example - you are in a car, and it has a navigation system that allows you to tailor the display to suit your needs. However, if you tailor the display in certain ways ONLY, not only does the navigation system stop, but the entire car shuts down (and prevents anyone else who happens to be riding in the car from using any of the other features i.e. stereo stops, A/C stops, electric windows stop). Damn right I'd want a way to limit the resources of the navigation system, and I'd be heading to the dealership pretty quickly!

I'll check the resources under SQL Server Configuration Properties as you describe - thanks.

Peter Shirley

http://www.linkcorp.com

 

[Krickles]

Actually, you can purchase a 3rd party A/C and have it installed...aftermarket car parts. Sorry you didn't care for the example. Obviously my point about repairing the sproc isn't the answer you wanted.

Krickles | 1.6180

 

[crystalreporting]

I love it when people get defensive - I appreciate all advice and if you read my second post you'll see I already acknowledged that the stored procedure needs to be rewritten. If you re-read my last post you'll see the comparison I made was that A/C systems can't be 'customized by the driver' - doesn't matter if it's OEM A/C or aftermarket. In either case, comparing SQL with a car isn't helping.

I looked at the server properties in EM, and I can see where you can limit the CPU to a single processor - however the machine in our situation is only a single processor, can I still prevent SQL from using 100% of the CPU?

Peter Shirley

http://www.linkcorp.com

 

[MDXer]

no you can't limit the amount of a cpu SQL will take everything it is allowed to. Again the best method is to invest the time and optimize the query. Look at the execution plan for page scans which usually will send CPU through the roof. you may also want to look at you DOP setting to make sure that is not the issue.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[hmckillop]

The best practice would be to test your stored procedures on a similiar environment with same data loads before production release.
Each proc should be analysed throughly and there are numerous tools out there to help - i personally like SQL Coefficient. If you have some form of load tool aswell it helps with the performance testing.

We have an online banking system which can cope with thousands of active concurrent users, but prior to pre-release testing we would have struggled to have more than a few on at the same time. Test, analyse and test some more.



"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[camy123]

You really need to look at the store procedure and optimize it...i have come across this many times and have re written querys for clients which normally use to take 24 hours and minmized them to 8 mins. so its all how you write the query.....if your not willing to this then cluster your servers/