[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • 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?

Thanks,
0
rocky_lotus_newbie
Asked:
rocky_lotus_newbie
3 Solutions
 
BawerCommented:
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.
0
 
sventhanCommented:
Is your statistics up to date? Are you collecting the statistics at regular interval?
0
 
rocky_lotus_newbieAuthor Commented:
Yes, we have auto update stats ON  and also a nightly task to update stats.

Thanks,
0
 
TempDBACommented:
rocky,
     Check for the most CPU consuming statements and objects and try tuning them.
0
 
rocky_lotus_newbieAuthor Commented:
Thanks everyone for your inputs
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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