How to Predict SQL Server Performance

Question: Is it possible to predict SQL Server performance ? It seems many of the techniques and tools are based on a reaction to a drop in sql performance. For example – users begin to complain a datawarehouse report is slowing down or timeouts appear in applications.

The DBA is called out , commits some performance analysis, identifies the bottleneck,adjusts something , and everyone is happy

How do I think about this problem and develop a mindset of proactive SQL Server performance monitoring  and what are the broad categories to consider?

Answer:  Good question but it’s a wide question with a number of different aspects. How do you set up a framework to track, measure and foresee sql server performance issues?

First , SQL Servers are hosted in large hosting environments – with backups, storage migrations ,variable network traffic, code changes , data changes. All these contribute to response time.

Second, architecture – a DBA can make a number of adjustments but if the system is not designed to scale – then you have to discuss platform changes as workload increases

Third, is it an increased workload  issue or response time issue?  Although both result  in  slower response for the end-user, understanding the difference influences the action to fix. Read more SQL Performance tuning - Asking the right question

Back to the question. The two main categories of SQL Server problems I’d want to monitor/track and predict are:

1)      SQL statements  degrading in response time and resource consumption. If they are not tuned , they will cause a system overload  resulting in SLA not met

2)      When demand on a database exceeds the configured capacity database bottlenecks occur. Examples include –IO degradation , lock contention etc

Architecture, Maintenance and monitoring  need to work together .

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