Optimising SQL Server Queries: A Performance Tuning Success Story

We recently had an SQL Server customer who reported intermittent poor performance with an SQL query, similar to the statement below. The SQL query referenced a view, and that view (View1) performed a remote query to a linked server:

SELECT Col1, Col2, Col3,

(SELECT COUNT(Val) FROM View1 WITH (NOLOCK) WHERE (View1.Cat = Table2.Cat)) AS Col4

FROM Table1 WITH (NOLOCK)

INNER JOIN Table2 WITH (NOLOCK) ON Table1.Ref = Table2.Ref

WHERE Table1.Col2 = '0000000617'

AND cat = '0000000044'

We reviewed the execution plan and found that the nested loop operator performing the join processed 34 rows from the outer statement and 12,000 rows from the inner statement, the product of which processed over 400,000 rows.

When the result of the outer statement scaled up to 500 rows, the product was almost 6 million rows – all being pulled across the network from the remote linked server.

In the case of the customer’s query, creating a local temporary table like the example below and extracting the remote data from the linked server only once led to a 97% reduction in reads and a decrease in execution time from 8s to < 1s.

CREATE TABLE #TempTable1 (

val nchar(10),

cat nchar(10))

INSERT INTO #TempTable1 (val, cat)

(SELECT val, cat

       FROM View1 WITH (NOLOCK))

SELECT Col1, Col2, Col3,

(SELECT COUNT(Val) FROM #TempTable1 WITH (NOLOCK) WHERE (#TempTable1.Cat = Table2.Cat)) AS Col4

FROM Table1 WITH (NOLOCK)

INNER JOIN Table2 WITH (NOLOCK) ON Table1.Ref = Table2.Ref

WHERE Table1.Col2 = '0000000617'

AND cat = '0000000044'

Furthermore, we found that the use of literal values rather than parameterisation in the where clauses resulted in multiple execution plans, adding bloat to the plan cache and potentially preventing the re-use of a good plan:

This could be improved by introducing parameterisation to the where clauses as follows:

DECLARE @Col2 nchar(10),

@cat nchar(10)

CREATE TABLE #TempTable1 (

val nchar(10),

cat nchar(10))

INSERT INTO #TempTable1 (val, cat)

(SELECT val, cat

       FROM View1 WITH (NOLOCK))

SELECT Col1, Col2, Col3,

(SELECT COUNT(Val) FROM #TempTable1 WITH (NOLOCK) WHERE (#TempTable1.Cat = Table2.Cat)) AS Col4

FROM Table1 WITH (NOLOCK)

INNER JOIN Table2 WITH (NOLOCK) ON Table1.Ref = Table2.Ref

WHERE Table1.Col2 = @Col2

AND cat = @cat

If this sounds like something you could benefit from, we’re here to help. As a trusted Microsoft Partner, DSP’s pedigree in delivering SQL Server consultancy is well established. Whatever you need help with, we’re the consultancy for you.

Our team of expert SQL Server consultants can give you the guidance and assistance you need to optimise your environment.

We offer a complete range of SQL Server consultancy services, including SQL Server health checks to highlight any points of weakness, proactive managed services, disaster recovery and security, right up to preparing for and migrating to the Cloud, plus database design and development to get it right the first time.