15 May,2014 by Tom Collins
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
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: |