High CPU Usage SQL Server (One Bad Query)

My colleague reported to me that one of our database server is reporting consistent high CPU usage so I looked at it I noticed CPU was at 100% from last one week when I contacted the application owner and I foundthat they implemented a new feature that polls the database for every second to ensure the data collection process is running properly as it was necessary to ensure that we are under compliance in terms of reporting and auditing. So I ran a query to pull the queries with high cpu utilization with execution count. I certainly noticed a query running more often with high cpu usage.

 

Exec stats

I know that above highlighted query is causing the high cpu usage, next I looked at query stats and noticed this query is running twice every second, so I looked at the plan

Select top 1 col1 from table order by 1

Table is clustered and col1 is not part of clustered index and does not have an index. simple enough SQL server decides to do Clustered index scan and sorts(fully blocking) col1 and selects 1 row with no predicate SQL server doesn’t think its missing an index.

PLan 1

PLan 1 properties .jpg
SQL Server Execution Times: with out non clustered index
CPU time = 2296 ms, elapsed time = 658 ms.

So I created a non clustered index on col1 in desc on the table

plan 2.jpg
SQL Server Execution Times: with non clustered index
CPU time = 0 ms, elapsed time = 0 ms.

Cpu Usage dramatically reduced

Cpu usage.jpg

Bottom line:

Its very important to understand no matter how much physical resources you might have on a  server its very important understand that one bad query can literally bring the server down to it knees.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s