• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

SQL Server Indexing - Disk IO - CPU

Hi Experts,

I have worked on a very huge database system that does not have proper indexing. (SQL Server 2008 Standard). After putting indexes in place, I see that the Avg Disk Queue length reduced to 6 from 16 which is more than 200% improvement. However, avg cpu is down only by 13% from 65% to 52%. I have run perfmon traces for 2 days before and after the changes to note these values. There are no changes done to code (still the same bad coding)

I want to understand why cpu usage did not reduce to a large extent, any thoughts?

3 Solutions
indexing in any db system use the CPU at max (db with no indexing will use CPU less) , however the CPU usage can be reduced if the indexing is properly implemented on different tables. I personally have handled many db system where huge tables are indexes which are not even used on regular basis even some table not at all. Some databases are upgraded and table structures are changed whereas old tables are then indexed which are not even required.
Is your statistics up to date? Are you collecting the statistics at regular interval?
rocky_lotus_newbieAuthor Commented:
Yes, we have auto update stats ON  and also a nightly task to update stats.

     Check for the most CPU consuming statements and objects and try tuning them.
rocky_lotus_newbieAuthor Commented:
Thanks everyone for your inputs
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now