SQL SERVER Query tuning

Recently I encountered an interesting issue with the SQL SERVER query engine. I had received a high CPU alert from the SQL SERVER 2008 server. I logged in and looked at the query plan cache to see which queries were causing load on the server. The query below quickly caught my attention. Each time the query below ran it was performing 207,651 logical reads on the server. I thought this was interesting in that the query seemed to be very selective with the WHERE clause placed on it. In addition, I noticed that there were extra parentheses around the filters in the WHERE clause. Upon removing these parentheses the query went from 207,651 logical reads to 4.

Original query:

set statistics io on
SELECT table1.brcd, table2.Pkt_x, table2.PKT_NBR 
FROM table1 WITH(NOLOCK) INNER JOIN table2 WITH(NOLOCK) ON table1.PKT_NB = table2.PKT_NB 
WHERE (((table2.Pkt_x)=1) AND ((table2.PKT_NBR)=5630));
(1 row(s) affected)
Table 'table2'. Scan count 9, logical reads 207651, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table1'. Scan count 1, logical reads 4, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I gathered the following information and forwarded it to the customer. They replied saying that the query was generated by an Microsoft Access database and they had refactored the query, removing the extra parentheses.

Refactored query:

SELECT table1.brcd, table2.Pkt_x, table2.PKT_NBR
FROM table1 WITH(NOLOCK) INNER JOIN table2 WITH(NOLOCK) ON table1.PKT_NB = table2.PKT_NB
WHERE table2.Pkt_x=1 and table2.PKT_NBR='5630'
Table 'table1'. Scan count 1, logical reads 4, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'table2'. Scan count 1, logical reads 4, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s