Why did Sql Server choose to search the index instead of
scan him?This is a combination of two factors:There is an index in which the pole is needed to search
Key and leaduse as a search template LIKE Scale
by variableIf you're sure you won't need an index search, you'll be instructed. FORCESCAN - one of the possible solutions, but in this case, perhaps not the best. The thing is, there's an operator in the request plan. Sort♪ And if, as you write, operator. Nested Loops You.Actual 16,000, expected 400,000.(the booster has overestimated the number of lines), it is likely that overrepresentation is available for the request. Try adding OPTION (RECOMPILE) If requested, then the optimist will be able to use scanning, plus evaluation (and remembrance) may be more appropriate.Either if the existing index is not used for other purposes, it should be replaced at allCREATE INDEX IX_Folder_Storage_id ON [Folder] ([Storage_id])
INCLUDE ([paths], [Name]);
Then the optimizer will use the index scan, as you wish, plus the request plan will miss the sorting.Below are some details.We'll take the original request the most interesting.DECLARE @pattern nvarchar(255) = '815';
SELECT [Name]
FROM [Folder]
WHERE [Name] LIKE '%' + @pattern + '%';
The effective plan of such a request is as follows: |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1007], [Expr1008], [Expr1009]))
|--Compute Scalar(DEFINE:([Expr1007]=LikeRangeStart(N'%'+[@pattern]+N'%'),
| | [Expr1008]=LikeRangeEnd(N'%'+[@pattern]+N'%'),
| | [Expr1009]=LikeRangeInfo(N'%'+[@pattern]+N'%')))
| |--Constant Scan
|
|--Index Seek(OBJECT:([Simple].[dbo].[Folder].[IX_Folder_Name])
SEEK:([Simple].[dbo].[Folder].[Name] > [Expr1007]
AND [Simple].[dbo].[Folder].[Name] < [Expr1008])
WHERE:([Simple].[dbo].[Folder].[Name] like N'%'+[@pattern]+N'%')
ORDERED FORWARD)
I mean. SqlServer is searching for it in an area that is dynamically defined by internal functions LikeRangeStart and LikeRangeEnd♪ Searching in the key range index is in fact partial scanning (often speaking) partial scan or range scan)Such a template is special. Operators Constan Scan♪ Compute Scalar and Nested Loops are additional and added during the post-optimization phase (i.e. post-optimization rewrite) This template (and others like him) is well described. https://www.sql.kiwi/2012/01/dynamic-seeks-and-hidden-implicit-conversions.html ♪ What could be found in the article on the reference would, in the case in question, add the following.When using local variables, the optimist may use simplified estimates of the selectivity of the predictors (see). https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd535534(v=sql.100)#avoid-use-of-local-variables-in-queries section Avoid use of local variables in queries)For example, on the test tableCREATE TABLE [Folder] ([Name] nvarchar(260) NOT NULL, [Filler] binary(400));
containing 100,000 linesWITH Nums(N) AS (
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.all_columns a, sys.all_columns b
)
INSERT INTO [Folder] WITH (TABLOCKX) ([Name])
SELECT
REPLICATE(CONVERT(nvarchar(200), CAST(N AS binary(4)), 2), 25)
FROM Nums;
with pole index NameCREATE INDEX IX_Folder_Name ON [Folder] ([Name]);
The request returned to me the following metrics and productivity assessments: The distance between the estimated number of lines with a valid order was more than two.If you see (through the 2363 flag) the source of this assessment, you can seePlan for computation:
CSelCalcFixedFilter (0.09)
Selectivity: 0.09
Stats collection generated:
CStCollFilter(ID=2, CARD=9000)
CStCollBaseTable(ID=1, CARD=100000 TBL: Folder)
That a calculator is used to calculate selectivity CSelCalcFixedFilter (i.e., the evaluation of selectivity in this case is constant). Use of tabular clues FORCESCAN The estimation method is not affected, leaving the selectiveness of constant 9 per cent of the total number of lines in the table.Another negative point is that the search operator in the index does not appear to be parallel in this template. If you get the optimist to generate a parallel plan, it's a pretty useless design. Well, if it were for the operator. Index Seek in this regard, which could have been parallel, the benefits would be questionable, as the parallel to the inner (internal)inner) to the Nested Loops There are certain problems. https://www.sql.kiwi/2011/12/forcing-a-parallel-query-execution-plan.html section A Note about Parallel Nested Loops)We'll take the same request now, but it won't be with the variable, but with the literary:SELECT [Name]
FROM [Folder]
WHERE [Name] LIKE '%815%';
The effective request plan was: |--Index Scan(OBJECT:([Simple].[dbo].[Folder].[IX_Folder_Name]),
WHERE:([Simple].[dbo].[Folder].[Name] like N'%815%'))
I don't have a parallel, because there's not much in the table, but in this case, everything is primitive and there's no obstacle to parallelism. Interesting, however, is the estimated number of lines Compared to the actual number, this is the same order.If you see the source of this assessment, you can seePlan for computation:
CSelCalcTrieBased
Column: QCOL: [Simple].[dbo].[Folder].Name
Selectivity: 0.000107411
Stats collection generated:
CStCollFilter(ID=2, CARD=10.7411)
CStCollBaseTable(ID=1, CARD=100000 TBL: Folder)
It's not fixed, it's calculated with help. CSelCalcTrieBased (selective calculator) And an estimate for the prediction of the variable was similar but still somewhat different, which is expected, that is. Cardinality Estimator In SqlServer 2014, changes have occurred (the difference may even be in the minor versions of one release).The track of 2363 appeared only in SqlServer 2014, so SqlServer 2008 does not allow for the same easy view of the sources of selectiveness estimates. Some information, however, can be extracted with the help of the sweet.Based on the set of calls, SqlServer 2008 to assess the selectivity of the filter LIKE Prefix trees are also used with literal lithorolCTrieInMem(call-stack)
sqlservr!OptimizerUtil::ProbLikeGuess
sqlservr!CTrieInMemCore::UlCountLikeStrings
sqlservr!CTrieInMemCore::FFilterLike+0x2af
sqlservr!CTrieInMem::FFilterLike+0x2e
sqlservr!CInMemHistogram::FFilterLike+0x5d
sqlservr!CScaOp_Intrinsic::FCalcSelectivity+0x47c0
sqlservr!CalculateFilter+0x72
sqlservr!CSelContext::CalculateSelectivity+0x37e
sqlservr!GroupCard+0x736
sqlservr!CLogOp_Select::DeriveCardinality+0x587
...
a to assess the selectivity of the filter LIKE with variables, some assumption is used (ProbLikeGuess)(call-stack)
sqlservr!OptimizerUtil::ProbLikeGuess
sqlservr!CScaOp_Intrinsic::FCalcSelectivity+0x6023
sqlservr!CalculateFilter+0x72
sqlservr!CSelContext::CalculateSelectivity+0x37e
sqlservr!GroupCard+0x736
sqlservr!CLogOp_Select::DeriveCardinality+0x587
...
apparently based on the density vector(call-tree)
sqlservr!CLogOp_Select::DeriveCardinality+0x587
sqlservr!GroupCard+0x736
sqlservr!CSelContext::CalculateSelectivity+0x37e
sqlservr!CalculateFilter+0x72
sqlservr!CScaOp_Intrinsic::FCalcSelectivity+0x34b
sqlservr!CScaOp_Identifier::FCalcSelectivity+0x44a
sqlservr!CInMemHistogram::CardGetStepTotal
sqlservr!CScaOp_Identifier::FCalcSelectivity+0x47a
sqlservr!CDensityGroup::FFindDensityByPvr
...
Resume the above. Inadequate assessments can be the cause of the non-optimal request plan, its slow implementation and/or overresourcing. If the demand ' s productivity is unsatisfactory, and the reason for low productivity is precisely the inadequacy of the estimates, then (in general case) it is possible to try to correct this in the following ways:Update statistics on the tables participating in the requestAdd to request OPTION (RECOMPILE) (on heavy requests
is the most commonly acceptable compromise)materialize part of the request in #- Table.
