SQL Server Indexing - Disk IO - CPU

Posted on 2011-05-12
Last Modified: 2012-05-11
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?

Question by:rocky_lotus_newbie
    LVL 10

    Accepted Solution

    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.
    LVL 18

    Assisted Solution

    Is your statistics up to date? Are you collecting the statistics at regular interval?
    LVL 4

    Author Comment

    Yes, we have auto update stats ON  and also a nightly task to update stats.

    LVL 25

    Assisted Solution

         Check for the most CPU consuming statements and objects and try tuning them.
    LVL 4

    Author Closing Comment

    Thanks everyone for your inputs

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now