Profile: Colin Smith
I ran into a SQL Server that was performing poorly and when I looked at it the only metric that looked like it was causing an issue was the CPU at about 95% avg. I Ran the below query to check and see what my top CPU consumers on the system were. It also showed me that they were executed a lot. About 200 times per minute each.
select top 10
count(*) as ‘#_statements’,
qt.dbid, qt.objectid, qs.sql_handle,
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text
(qs.sql_handle) as qt
group by qt.dbid,qt.objectid,
order by sum(qs.total_worker_time)
This Query showed me two queries that were causing a the CPU to be running so high. I took a look at them and they were very simple queries. one was a simple select with a where clause and the second was an update of a single row in the same table that the select query was hitting. I grabbed plans for both and took a look. Both were doing Clustered Index Scans. So that is really a table scan. The table is not huge at about 200K rows. But since both queries could be covered by the same index and the table did not have any other non clustered indexes, I thought it was worth a shot.
I created the one index on one column of the table and immediately the CPU dropped off a cliff to about 30 – 40% and not only that but I also saw the batch reqs per second jump from around 20 to almost 1000. I then looked at the new plans and both were doing index seeks now so they were much more efficient and CPU use for both dropped to next to nothing as well as the exec times dropped from around 200ms to less then 5ms. Now the queries are executing over 10K times per minute and the server is in much better shape. The application team that uses this database said that the app went from processing 1 record every 1 to 2 minutes to one record every 2-3 seconds. Now that was some big bang for the buck. So I hope that when the application support team takes this to the application vendor that they will take a look at indexing strategy and implement this index and perhaps some others that will help that I just have not come across yet.