04 September,2014 by Tom Collins
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
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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |