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 ,st.dbid ,st.objectid ,qs.total_worker_time ,qs.last_worker_time ,qp.query_plan 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.
total_worker_timeis of course cumulative whereas profiler shows figures for individual executions. Maybe these are just executed very frequently - Martin Smith 2012-04-04 19:04
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.