sp execute

[nirmalsp]

Hi

There are lots of Stored procedures running using cursors
in my sql server db's
now when ever a sp executes server allocates up to 100% CPU usage at that time.
due to this other users who use to view reports & run queries fails giving a time out.

what could be the cause.
all are welcome with ideas.

thanks

 

[tlbroadbent]

Cursors are probably the cause of the problems. They are extremely inefficient and can be processor intensive. We've had the experience of one poorly designed SP with cursors taking down a SQL Server with four 500 mhz processors and 2 GB of RAM. Almost always, poor application design is the cause of SQL Performance problems. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com