Find the Root cause of a SQL Server Performance downgrade

04 September,2014 by Jack Vamvas

Finding the root cause of a SQL Server performance downgrade requires a wide understanding of infrastructure . A common user request  is to analyse a sudden performance downgrade.  DBAs are  very busy – sometimes a quick fix is applied . Although it gets the user off your back, long-term it’s not a great tactic , as the problem normally reappears!

There are different reasons for performance downgrade. From network through to user input.  Asking the right question can be useful for information gathering .  Unless users are experienced in troubleshooting , the requests can be vague , little detail. Spending some time to ask questions .

99.9% of all SQL Server performance issues exist in one or more of these categories.  

Higher Workload

Statistics Distribution  

Maintenance Plans Issues

Resource Shortage e.g memory,CPU, drivers

Infrastructure Issues  e.g SAN, network, Measuring SAN Performance with Avg Disk Transfer and disk bytes per second

User Error

Incorrect Configuration

Concurrent Users

Change request impact

Data Growth - Planned and Unplanned

A good quality DBA spends adequate time planning server deployments to minimise the outages. Capacity planning , stress testing , standardisation, maintenance and monitoring are part of the DBA tactics to maintain uptime.  A deeper understanding of the systems assists in drilling into the root cause

Read More

SQL Server – How to troubleshoot query timeouts

Storage Checklist FAQ - SQL Server DBA

SQL Server Performance Checklist - SQL Server DBA

TempDB performance and strategy checklist - SQL Server DBA

 


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


Share:

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment on Find the Root cause of a SQL Server Performance downgrade


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