SQL Server Tuning method for a drop in throughput

05 November,2013 by Jack Vamvas

Question: I  would like a simple method to approach SQL Server tuning in a situation of a drop in throughput. There is large amounts of information available, but I am looking for a method which I can use repeatedly.

 Answer: You are right , there is a great deal of information and methods related to SQL Server Tuning. Quite often a DBA has spent large amounts of time architecting a system , such as the OS configuration , storage , SQL Server configuration and various SQL Server database files configuration checklist  items. But after some period of time , where real world applications have modified the data , maintenance is applied ,  and queries have changed , it is possible the  query response time has slowed and users are complaining.

 This may not be just the queries have changed , but it could also be the workload has changed. In that case, it is a different type of question and a different type of SQL performance problem  i.e capacity. To progress to a satisfactory conclusion – it is important to identify the type of problem – more on SQL Performance tuning - Asking the right question.

In the SQL performance problem of a drop in throughput  , where you believe it is not a capacity issue – these 6 steps are suitable and can give you enough information to identify and fix the bottleneck

1.Identify waits at the SQL Server instance level.

2.Collate waits with queues.

3.Decide on an action plan.

4.Focus on  the database file level.

5.Focus on the process level.

6.Identify slow queries and tune

Read More

SQL Server – How to troubleshoot a slow running query

SQL Server – Find high impact queries with sys.dm_exec_query_stats

SQL Server – Write everything down during problem troubleshooting

How to report a SQL Server performance problem

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server Tuning method for a drop in throughput

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer