Fixed: SQL Server Query Performance Issue

Issue Description

We were working on a SQL Server query performance issue where customer complained of queries being slow. There are so many things that we look upon while investigating SQL Server query slowness issues. Most of the things like indexes, statistics, parallelism configuration, memory settings, disk IOPS & throughput etc. were fine. However, we found that plans were not being reused causing us to look at parametrizing SQL server Queries. In this guide we will show the steps we took to find the problem and a fix for the issue.

Investigation

We looked at SQL Server Activity Monitor and went through Recent Expensive Queries Section.

From above section, we found that few of the queries on a database were showing very high number of plan count. Ideally once a query builds an optimal plan it should reuse it. If number of plan count for a query is huge and more or less equal to execution count of query then it indicates that for each execution a new plan is being built. This can result in slow performance as SQL server has to build optimal plan every time problematic query is up for execution. A large number may indicate a need for explicit parameterization.

Now, we looked at concerned query and it was in below format:

SELECT Column1 FROM Table
WHERE (ID=  123) AND ( Flag= 'Y')

Although query was similar each time, application itself was sending queries in above format with fixed values to columns in where clause.

So let suppose we have below four queries coming from application. All these are similar but SQL will make separate plan for each causing plan count huge leading to query slowness.

-- Query 1

SELECT Column1 FROM Table
WHERE (ID=  124) AND ( Flag= 'Y')

-- Query 2

SELECT Column1 FROM Table
WHERE (ID=  125) AND ( Flag= 'N')

-- Query 3

SELECT Column1 FROM Table
WHERE (ID=  126) AND ( Flag= 'Y')

-- Query 4

SELECT Column1 FROM Table
WHERE (ID=  127) AND ( Flag= 'N')

Solution:

1. Short Term : Set Parameterized option in database as forced and monitor performance for few days. This is to be used only in case below long term solution is not possible. It has its side effects.
2. Long term : Work with developers so that application is parameterizing SQL server queries as below so that SQL server treat all the queries same and duplicate plans are not generated

DECLARE @ID int = 123
DECLARE Flag varchar(1) ='Y'
SELECT Column1 FROM Table
WHERE (ID=  @ID) AND ( Flag= @Flag)

Customer followed long term path of parameterizing SQL server queries and it fixed query slowness issue. If you wish to check other performance troubleshooting articles then refer below:

Fixing high CPU issues caused by deadlocks

Handling SQL Server High CPU Issue