How to Predict SQL Server Performance

15 May,2014 by Jack Vamvas

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


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 How to Predict SQL Server Performance


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