SQL Server high CPU and I/O activity database tuning

Question

Our application tends to be running very slow recently. On debugging and tracing found out that the process is showing high cpu cycles and SQL Server shows high I/O activity. Can you please guide as to how it can be optimised?

The application is now about an year old and the database file sizes are not very big or anything. The database is set to auto shrink. Its running on win2003, SQL Server 2005 and the application is a web application coded in c# i.e vs2005

Solution 1

Run SQL Profiler on your database for a while to see if the "slowness" is due to any problem queries. Then you can analyize these queries in order to run any indexes or statistics to increase performance.

As the comment suggests though, auto shrink can result in a very fragmented database. The database will generally grow as it needs to and its usually best not to worry about how big it wants to be. As long as you perform regular transaction log backups then you're better off letting it grow. You might need to ask yourself is performance is more important than having to buy new.more disks.

You can also run some maintenance plans against the database to rebuild the indexes and statistics. This might sort things out in the short term.

Solution 2

1. defrag your harddisk (or at least the mdf/ldf) files.
2. put the ldf file on a separate harddisk than mdf, if possible
3. use the the profiling tool from SQL 2005; it will tell you which requests last most; then use the "show execution plan" tool to see the steps of execution; maybe you will get a hint on what indexes should be added; for example, full table scan should be avoided for large tables.

Solution 3

Next to looking at performance issues with queries, I would also check whether the DB and the tables within the DB are not to much fragmented.

You can issue the DBCC showcontig statement to check this. If it shows that the tables are heavily fragmented, you should consider creating a maintenance plan that is regularly executed. In that maintenance plan, you should specify that indexes should be rebuild. By doing so, the tables will be defragged.

Solution 4

I know this thread has been quite for some time but I thought I'd add my 2 bits in. We were having problems on our sql server 2005 production db where cpu was regularly running at 80 to 100% constantly all day long. We tried running traces, evaluating jobs, defragging, freeing disk space, everyting we could think of, but nothing really helped. In the end we found a post on a blog site (I'm afraid we don't remember which one) which recommened using Sql Server's missing indexes functionality.

As it turns out SQL Server 2005 and later all have this feature; SQL Server is constantly evaluating and recording recommended indexes that it thinks will help speed performance. We ran the query below and implemented the top 130 indexes (the ones showing the biggest potential gains). Our overall db cpu performance is now down to 30% to 40% during the busiest times of the day and users across the board are telling us that their apps are much more responsive.

A few caveats. We are not DBAs so add indexes at your own discretion. Also adding too many indexes to any one table can be detrimental to performance - so be aware of what indexes you are adding and always be on the lookout for index overload.

SELECT mid.database_id, 
       db.name,
       migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure, 
       'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']' + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, 
       migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig 
INNER JOIN sys.dm_db_missing_index_group_stats migs
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
    ON mig.index_handle = mid.index_handle
INNER JOIN sys.databases db
    ON mid.database_id = db.database_id
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

View more solutions

Comments

[zapping]

Actually a job is scheduled to run twice a day to do the db backup to a separate disk. That was why it was set to auto shrink.

[zapping]

The application is hosted on a dedicated server and there is lots of disk space. The database size is under 2gb. It was tending to grow about 6-7 months back. that was when the auto shrink was setup. But now the db growth is pretty much less.

[MartW]

The options in SSMS for displaying the Execution Plan and statistics are at least as good.

[Dave Markle]

+1: Auto shrink is, 99% of the time, a bad idea. Certainly in production.

[João Paladini]

If your backups are so setup and correct, then NO Autoshrink should ever have been needed. If they keep growing significantly after 24 hours, then you have something set wrong in your backups, etc.

[Andrew]

Instead of running profiler for a period of time, I would just pull the query cache via the DMV's straight away, it will be a faster turnaround - but is 2k5 or 2k8 only, 2k would still need profiler. Before that, I think I'd also check perfmon to check for key indicators, page life expectency etc

[Nick Kavadias]

+1 If autoshink has been turned on then your sql server data/log files will be fragmented to hell. Good idea to perform a defrag