StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POGetting rid of full index scan
text
Body
copied!<p>The following query performs badly because of a full non-clustered index scan of 6.5 million records in P4FileReleases followed by a hash join. I'm looking for possible reasons the optimizer picks a scan over a seek.</p> <pre><code>SELECT p4f.FileReleaseID FROM P4FileReleases p4f INNER JOIN AnalyzedFileView af ON p4f.FileRelease = (af.path+'#'+cast(af.revision as varchar)) WHERE (af.tracked_change_id = 1) </code></pre> <p>From what I can tell, I see no reason for the optimizer to pick a scan of P4FileReleases. The WHERE clause limits the size of the right dataset to about 1K of records and the optimizer should know it (see the histogram below). </p> <p>If fact, if I take the view data and throw it into a heap table (same structure as the indexed view), then the query is performed with an index seek on the larger table and an inner join loop instead of a hash join (and the total cost drops from 145 to around 1).</p> <p>Any ideas on what might be throwing the optimizer off?</p> <p><strong>Details.</strong> Sql Server 2008 (v. 10.0.2757.0).</p> <p><strong>P4FileReleases table</strong> Holds 6.5 million records</p> <pre><code>CREATE TABLE [dbo].[P4FileReleases]( [FileReleaseID] [int] IDENTITY(1,1) NOT NULL, [FileRelease] [varchar](254) NOT NULL, -- 5 more fields CONSTRAINT [CIX_P4FileReleases_FileReleaseID_PK] PRIMARY KEY CLUSTERED ( [FileReleaseID] ASC ), CONSTRAINT [NCIX_P4FileReleases_FileRelease] UNIQUE NONCLUSTERED ( [FileRelease] ASC ) </code></pre> <p><strong>AnalyzedFileView</strong> is an indexed view with statistics enabled and up-to-date. </p> <p>It has four columns: </p> <pre><code> key int (int, PK) - clustered index tracked_change_id (int, FK) - non-unique, non-clustered index (covering 'path', 'revision') path (nvarchar(1024), null) revision (smallint, null) </code></pre> <p>tracked_change_id histogram:</p> <pre><code>1 0 1222 0 1 4 0 787 0 1 8 0 2754 0 1 12 0 254 0 1 13 0 34 0 1 </code></pre> <p><strong>Query Plan</strong></p> <pre><code> |--Parallelism(Gather Streams) |--Hash Match(Inner Join, HASH:([Expr1011])=([Expr1010]), RESIDUAL:([Expr1010]=[Expr1011])) |--Bitmap(HASH:([Expr1011]), DEFINE:([Bitmap1015])) | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1011])) | |--Compute Scalar(DEFINE:([Expr1011]=([qpsitools].[dbo].[analyzed_file_view].[path]+N'#')+CONVERT_IMPLICIT(nvarchar(30),CONVERT(varchar(30),[qpsitools].[dbo].[analyzed_file_view].[revision],0),0))) | |--Index Seek(OBJECT:([qpsitools].[dbo].[analyzed_file_view].[tracked_change_id]), SEEK:([qpsitools].[dbo].[analyzed_file_view].[tracked_change_id]=(1)) ORDERED FORWARD) |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1010]), WHERE:(PROBE([Bitmap1015],[Expr1010]))) |--Compute Scalar(DEFINE:([Expr1010]=CONVERT_IMPLICIT(nvarchar(254),[Blueprint].[dbo].[P4FileReleases].[FileRelease] as [p4f].[FileRelease],0))) |--Index Scan(OBJECT:([Blueprint].[dbo].[P4FileReleases].[NCIX_P4FileReleases_FileRelease] AS [p4f])) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload