Find the Root cause of a SQL Server Performance downgrade

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