The Multifunctioning DBA

Oct 2 2011   3:23PM GMT

Just a little Index



Posted by: Colin Smith
Tags:
Indexing
Performance
Tuning
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
sum(qs.total_worker_time) as
total_cpu_time,
sum(qs.execution_count) as
total_execution_count,
count(*) as ‘#_statements’,
qt.dbid, qt.objectid, qs.sql_handle,
qt.
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,
qs.sql_handle,qt.
order by sum(qs.total_worker_time)
desc,qs.sql_handle

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.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: