Stored procedures eating CPU SQL Server 2005

Go To


I am trying to resolve a 100% cpu usage by the SQL Server process on the database server. While investigating, I have now come across the fact that the stored procedures are taking the most worker time.

For the following query of dmv's to find queries taking highest time,

SELECT TOP 20 st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC

most of them are stored procedures. The weird thing is that all these stored procedures are querying different tables. And yet they are the top of taking the most worker time, even though when I look at the Profiler for queries with top CPU, Reads, Duration, the stored procedures don't figure at the top there.

Why could this be happening?

==Edit== The application actually uses adhoc queries more than stored procedures. Some of these procedures are to be migrated to using adhoc queries. The thing is that these procedures are not called as often as some of the other queries, which are cpu intensive, and are called very frequently. Also, it does strike me odd that a stored procedure with which does a simple select a,b,c from tbl where id=@id would have a higher total worker time than a query which has mulitple joins, user defined functions in the where clause, a sort and a row_number over and while the simple one queries a table with 20000 records, the complex query is on a table with over 200,000 records.

2012-04-04 18:54
by shashi
All this says to me is that your applications are coded to access the database using stored procedures and not ad-hoc queries. This isn't a bad thing. There are so many other factors which could contribute to your server CPU being pegged. Not enough power to meet demand? Is this all the time or during peak usage? Indexing appropriately? Etc etc etc.. - Yuck 2012-04-04 19:02
total_worker_time is of course cumulative whereas profiler shows figures for individual executions. Maybe these are just executed very frequently - Martin Smith 2012-04-04 19:04
@yuck added some more info. I understand that there could be a lot of reasons why cpu usage is at 100%, but it is at 100 even during low loads. Have been tuning/defraging indexes etc - shashi 2012-04-04 20:12
@martinsmith yes, you are right about that. the sql server was restarted and I noticed that initially, the stored procs were not the top candidates but with time they started coming in the top 20 - shashi 2012-04-04 22:51


It depends what are you doing in that stored procedure, how you are tuning your tables, indexes, etc.

For example if you create a stored procedure using loops like cursor you will have at maximum your cpu usage. If you don't setup your indexes and you are using select using different joins,etc. You'll have your cpu overloaded.

It is accordingly of how you use your database.

Some tips:

  1. I recommend create stored procedure most of the time.
  2. When you create your stored procedures, run with execution plan to get some suggestions.
  3. If the tables have a lot of records (more than 1 or 2 millions), think about create index views.
  4. Do update or delete only when is necessary, sometimes is better only insert records and run a job daily or weekly to update or remove the records that you don't need. (it depends of the case)
2012-04-04 19:16
by Victor Capeluto