Performance Tuning

Fixed: SQL Server Query Performance Issue

by

Troubleshooting SQL Server query slowness issue. Query optimization through parameterization. Fixing duplicate plan issue.

Guide to handle high CPU caused by deadlocks

by

We were working for a customer who was facing below issues on MS SQL Database server: Initial Approach: Issue Identification: Above things helped very little so we started further investigation of deadlocks and execution plans of high CPU queries. We identified below issues: 5. Winner query acquired key lock on entire non clustered index due … Read more

SQL Server Performance Tuning : Ways to do Index Optimization

by

In our last post we discussed an approach on what we should do to find whether there is a memory issue or a way to identify if there is a memory crunch which is causing bad SQL performance. However, not always the thing we see on the surface is the root cause. Though there may … Read more

SQL Server performance tuning – how to look for memory issue

by

We in the first post saw how capturing and analyzing wait statistics can help a SQL DBA to look for potential performance issue. In the second post we looked at some of the example and potential scenario of wait stats which were similar to a production environment. In this post, we will see how we … Read more

Low PLE but high Buffer Cache Hit Ratio

by

Recently I worked on an interesting scenario where an incident was reported for very low PLE value. Very low PLE value indicates memory pressure on SQL server and should be fixed to ensure good SQL server performance. First lets check the definitions of PLE and BCHR as per Microsoft BOL: Page Life Expectancy: Indicates the … Read more

SQL Server Performance tuning approach – deduce information from wait statistics data

by

In our last post, we started a journey for SQL Server DBAs so that they can establish an approach for performance tuning issue. We went through some of the basic wait types are and shared links for downloading queries to gather those. Now that you have gathered total wait statistics from a SQL Server instance, … Read more

SQL Server Performance tuning approach – Wait statistics

by

As a SQL Server DBA one of the most important part of our job is to do performance tuning. But, as soon as some says performance tuning, most DBAs are clueless on how to start troubleshooting. There are many articles, blogs which focuses on key aspects of performance tuning. However, in this series, we will … Read more