{"id":629,"date":"2011-01-16T13:07:00","date_gmt":"2011-01-16T13:07:00","guid":{"rendered":"\/blogs\/paul\/post\/Adventures-in-query-tuning-non-seekable-WHERE-clause-expressions.aspx"},"modified":"2017-07-12T13:10:42","modified_gmt":"2017-07-12T20:10:42","slug":"adventures-in-query-tuning-non-seekable-where-clause-expressions","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/adventures-in-query-tuning-non-seekable-where-clause-expressions\/","title":{"rendered":"Adventures in query tuning: non-seekable WHERE clause expressions"},"content":{"rendered":"

I’ve been doing a lot of performance tuning work over the last couple of months and this weekend found something that’s very pervasive out in the wild. Kimberly was helping me optimize a gnarly query plan and spotted something in the code I hadn’t noticed that was causing an index scan instead of an index seek. Even though the query plan was using a covering nonclustered index, the way the code was written was causing the index to be scanned.<\/p>\n

I’ll set up a test to show you what I mean.<\/p>\n

First, create an\u00a0example sales tracking\u00a0table and populate it (took 3m45s on my laptop):<\/p>\n

\r\n-- Create example table\r\nCREATE TABLE [BigTableLotsOfColumns] (\r\n    [SalesID] BIGINT IDENTITY,\r\n    [SalesDate] DATETIME,\r\n    [Descr] VARCHAR (100),\r\n    [CustomerID] INT,\r\n    [ProductID] INT,\r\n    [ModifyDate] DATETIME DEFAULT NULL,\r\n    [PayDate] DATETIME DEFAULT NULL,\r\n    [Quantity] INT,\r\n    [Price] DECIMAL (6,2),\r\n    [Discount] INT DEFAULT NULL,\r\n    [WarehouseID] INT,\r\n    [PickerID] INT,\r\n    [ShipperID] INT);\r\nGO\r\n\r\n-- Populate table\r\nSET NOCOUNT ON;\r\n\r\nDECLARE @Date DATETIME;\r\nDECLARE @Loop INT;\r\n\r\nSET @Loop = 1;\r\nSET @Date = '01\/01\/2010';\r\n\r\n-- Insert 1500 sales per day\r\nWHILE @Loop &lt; 547600\r\nBEGIN\r\n    INSERT INTO [BigTableLotsOfColumns] (\r\n        [SalesDate], [Descr], [CustomerID], [ProductID], [Quantity],\r\n        [Price], [WarehouseID], [PickerID], [ShipperID])\r\n     VALUES (\r\n        @Date,\r\n        'A nice order from someone',\r\n        CONVERT (INT, (RAND () * 1000)),\r\n        CONVERT (INT, (RAND () * 1000)),\r\n        CONVERT (INT, (RAND () * 10)),\r\n        ROUND (RAND () * 1000, 2),\r\n        1,\r\n        CONVERT (INT, (RAND () * 4)),\r\n        CONVERT (INT, (RAND () * 4)));\r\n\r\n    IF @Loop % 1500 = 0 SET @Date = @Date + 1;\r\n\r\n    SET @Loop = @Loop + 1;\r\nEND\r\nGO\r\n\r\n-- And a clustered index\r\nCREATE CLUSTERED INDEX [IX_BigTableLotsOfColumns_Clustered] ON [BigTableLotsOfColumns] ([SalesID]);\r\nGO\r\n<\/pre>\n
Now I’ve got a simple stored procedure to find the total sales amount for any particular date.<\/p>\n
\r\nCREATE PROCEDURE [TotalSalesForDate] (@TargetDate DATETIME)\r\nAS\r\n    SELECT\r\n        SUM ([Quantity] * [Price])\r\n    FROM\r\n        [BigTableLotsOfColumns]\r\n    WHERE DATEDIFF (DAY, [SalesDate], @TargetDate) = 0\r\n    OPTION (MAXDOP 1);\r\nGO\r\n<\/pre>\n
The idea is that the stored procedure will pick up all sales for a particular date, no matter what time of that day the sale occurred. I’ve got OPTION (MAXDOP 1)<\/em> to simulate a system that’s been tuned for OLTP queries (and to make the query plans fit better in my blog post :-)<\/p>\n
If I run the stored procedure for, say, December 26th 2010, the query plan it uses is as follows:<\/p>\n
\r\nEXEC TotalSalesForDate '12\/26\/2010';\r\nGO\r\n<\/pre>\n
\"\"<\/p>\n
And it uses the following CPU and I\/O (you can get these by using SET STATISTICS IO ON<\/em> and SET STATISTICS TIME ON<\/em>, but be careful – the can generate lots of output for big procs):<\/p>\n
\r\nTable 'BigTableLotsOfColumns'. Scan count 1, logical reads 7229, physical reads 0, ...\r\n\r\nSQL Server Execution Times:\r\nCPU time = 125 ms,\u00a0 elapsed time = 186 ms.\r\n<\/pre>\n
I ran the proc twice; once to get it\u00a0to compile, and the second time to factor out the compile time.<\/p>\n
Pretty obvious that I can speed this up by creating a covering nonclustered index, and in fact you can see the query processor telling me that with the missing index suggestion. I create the covering nonclustered index:<\/p>\n
\r\nCREATE NONCLUSTERED INDEX [IX_BigTableLotsOfColumns_Date] ON\r\n    [BigTableLotsOfColumns] ([SalesDate]) INCLUDE ([Quantity], [Price]);\r\n<\/pre>\n
Now when I run the proc, I get a better query plan:<\/p>\n
\"\"<\/p>\n
And the following CPU and IO:<\/p>\n
\r\nTable 'BigTableLotsOfColumns'. Scan count 1, logical reads 2113, physical reads 0,\u00a0...\r\n\r\nSQL Server Execution Times:\r\nCPU time = 78 ms,\u00a0 elapsed time = 115 ms.\r\n<\/pre>\n
Much better, as I’d expect. But look at how the nonclustered index is being used: it’s being scanned when instead there should be a seek. It’s still doing more than 2,000 logical reads because it’s scanning the whole index.<\/p>\n
The reason for this is that the DATEDIFF<\/em> in the proc is forcing every SalesDate<\/em> column value to be evaluated, hence the scan. If I know that the proc only ever is called with a date without a time portion, I can do some rearranging of the logic.<\/p>\n
The updated proc is below. Notice that there’s no reason to scan any more as the WHERE<\/em> clause doesn’t require every column value to passed through the DATEDIFF<\/em>:<\/p>\n
\r\nCREATE PROCEDURE [TotalSalesForDate] (@TargetDate DATETIME)\r\nAS\r\n    SELECT\r\n        SUM ([Quantity] * [Price])\r\n    FROM\r\n        [BigTableLotsOfColumns]\r\n    WHERE [SalesDate] >= @TargetDate AND [SalesDate] < DATEADD (DAY, 1, @TargetDate)\r\n        OPTION (MAXDOP 1);\r\nGO\r\n<\/pre>\n
And when I run it I get the best plan:<\/p>\n
\"\"<\/p>\n
And the following CPU and I\/O:<\/p>\n
\r\nTable 'BigTableLotsOfColumns'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0,\u00a0...\r\n\r\nSQL Server Execution Times:\r\nCPU time = 0 ms,\u00a0 elapsed time = 64 ms.\r\n<\/pre>\n
Wow! 200x fewer logical I\/Os and not even enough CPU time to register as 1ms.<\/p>\n
Summary: although you may have nonclustered indexes that are being used, make sure that they’re being used for seeks as you expect. For small queries that are executed hundreds of times a second, the difference between seeking and scanning can be huge in overall performance.<\/p>\n","protected":false},"excerpt":{"rendered":"
I’ve been doing a lot of performance tuning work over the last couple of months and this weekend found something that’s very pervasive out in the wild. Kimberly was helping me optimize a gnarly query plan and spotted something in the code I hadn’t noticed that was causing an index scan instead of an index […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66,73],"tags":[],"class_list":["post-629","post","type-post","status-publish","format-standard","hentry","category-performance-tuning","category-query-tuning"],"yoast_head":"\nAdventures in query tuning: non-seekable WHERE clause expressions - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/adventures-in-query-tuning-non-seekable-where-clause-expressions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Adventures in query tuning: non-seekable WHERE clause expressions - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"I’ve been doing a lot of performance tuning work over the last couple of months and this weekend found something that’s very pervasive out in the wild. Kimberly was helping me optimize a gnarly query plan and spotted something in the code I hadn’t noticed that was causing an index scan instead of an index […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/adventures-in-query-tuning-non-seekable-where-clause-expressions\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2011-01-16T13:07:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-07-12T20:10:42+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/adventures-in-query-tuning-non-seekable-where-clause-expressions\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/adventures-in-query-tuning-non-seekable-where-clause-expressions\/\",\"name\":\"Adventures in query tuning: non-seekable WHERE clause expressions - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2011-01-16T13:07:00+00:00\",\"dateModified\":\"2017-07-12T20:10:42+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/adventures-in-query-tuning-non-seekable-where-clause-expressions\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/adventures-in-query-tuning-non-seekable-where-clause-expressions\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/adventures-in-query-tuning-non-seekable-where-clause-expressions\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Adventures in query tuning: non-seekable WHERE clause expressions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Adventures in query tuning: non-seekable WHERE clause expressions - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/adventures-in-query-tuning-non-seekable-where-clause-expressions\/","og_locale":"en_US","og_type":"article","og_title":"Adventures in query tuning: non-seekable WHERE clause expressions - Paul S. Randal","og_description":"I’ve been doing a lot of performance tuning work over the last couple of months and this weekend found something that’s very pervasive out in the wild. Kimberly was helping me optimize a gnarly query plan and spotted something in the code I hadn’t noticed that was causing an index scan instead of an index […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/adventures-in-query-tuning-non-seekable-where-clause-expressions\/","og_site_name":"Paul S. Randal","article_published_time":"2011-01-16T13:07:00+00:00","article_modified_time":"2017-07-12T20:10:42+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/adventures-in-query-tuning-non-seekable-where-clause-expressions\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/adventures-in-query-tuning-non-seekable-where-clause-expressions\/","name":"Adventures in query tuning: non-seekable WHERE clause expressions - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2011-01-16T13:07:00+00:00","dateModified":"2017-07-12T20:10:42+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/adventures-in-query-tuning-non-seekable-where-clause-expressions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/adventures-in-query-tuning-non-seekable-where-clause-expressions\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/adventures-in-query-tuning-non-seekable-where-clause-expressions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Adventures in query tuning: non-seekable WHERE clause expressions"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/629","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=629"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/629\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=629"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=629"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=629"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}