Performance error with / without parameters

Question

I have some performance issues.

I have a table with approximately 2 million rows.

CREATE TABLE [dbo].[M8]( [M8_ID] [int] IDENTITY(1,1) NOT NULL, [APPLIC] [char](8) NOT NULL, [NIVALERTE] [numeric](1, 0) NOT NULL, [LOGDH] [datetime2](7) NULL, [USERX] [char](20) NOT NULL, [TACHE] [char](3) NOT NULL, [PRG] [char](32) NOT NULL, [DOS] [numeric](3, 0) NOT NULL, [ERRNUM] [numeric](5, 0) NOT NULL, [LOGTXT] [char](200) NOT NULL) 

I read them with C # and ADO.NET

In the management studio (SQL Server 2008 R2) with this query:

 SELECT M8.M8_ID, M8.APPLIC, M8.NIVALERTE, M8.LOGDH, M8.USERX, M8.TACHE, M8.PRG, M8.DOS, M8.ERRNUM, M8.LOGTXT FROM M8 AS M8 WITH(NOLOCK) WHERE ((M8.APPLIC LIKE 'DAV' ) ) ORDER BY M8.LOGDH DESC, M8.M8_ID ASC OPTION (FAST 1) 

The first lines will take about 1 minute.

But, with

 DECLARE @APPLIC_ZOOMAPRESCLE_ZOOM_LIKE_APPLIC_WHERE_0 as char(8) = 'DAV' SELECT M8.M8_ID, M8.APPLIC, M8.NIVALERTE, M8.LOGDH, M8.USERX, M8.TACHE, M8.PRG, M8.DOS, M8.ERRNUM, M8.LOGTXT FROM M8 AS M8 WITH(NOLOCK) WHERE ((M8.APPLIC LIKE @APPLIC_ZOOMAPRESCLE_ZOOM_LIKE_APPLIC_WHERE_0 ) ) ORDER BY M8.LOGDH DESC, M8.M8_ID ASC OPTION(FAST 1) 

I get the first lines after 4 seconds.

PS: I know, I don’t have% in similar.

Edit: Here are the execution plans https://www.dropbox.com/sh/jgai5f9txbs84x6/EP5_hj8DNv

Answer 1

Your table has 1,517,820 rows. Of these, almost one third (476,672) contain the DAV value (or, more precisely, the DAV value, since it has the CHAR(8) data type CHAR(8) , therefore it is supplemented with trailing spaces.

In a LIKE comparison of match_expression spaces in match_expression does not matter (although they are significant for the pattern itself).

Therefore, the expression WHERE APPLIC LIKE 'DAV' really matches WHERE APPLIC LIKE 'DAV' lines. However, none of the implementation plans evaluate this. Although a faster plan (with variables) is three orders of magnitude closer.

 +-----------------------+-----------+-----------+ | | Slow Plan | Fast Plan | +-----------------------+-----------+-----------+ | Estimated # Rows | 32 | 47,343 | | Memory Grant | 1 MB | 333 MB | | Degree of Parallelism | 1 | 4 | +-----------------------+-----------+-----------+ 

For a plan with variables, since SQL Server does not intercept variables (without the OPTION (RECOMPILE) hint), it goes back to assuming how many rows will match the predicate and comes up with an estimate that is about 3.1% of the table will qualify.

Quick plan

A plan with a literal meaning should have much better grades. The screenshot you provided with DBCC SHOW_STATISTICS (after adding another million rows) shows that DAV definitely there

Unfortunately, it seems that although the final space in the column values ​​is not significant for the query result, their presence really messed up the power estimates ( Reported as bug here and is currently claimed to be fixed in the next version). As a result of this problem, he will calculate that only a few rows will be returned and the next plan will be presented.

Slow plan

In addition to performing half a million key searches due to poor power ratings, the memory grant size is probably not adequate for the size of the sorted data, which leads to spills up to tempdb .

There are many problems that you can consider if you can change the query scheme or table.

• Using = instead of LIKE
• Change WHERE to LIKE CAST('DAV' AS CHAR(8))
• Changing the column data type to VARCHAR(8) (and ensuring that all stored values ​​are saved).
• Drop the current requested index ( Index_A ). You did not specify its definition, but if it is an index of a column with several separate values, its presence may be more of an obstacle than help (depending on the workload of your request).
• Adding a coverage index with the APPLIC key column (and possibly LOGDH DESC, M8_ID ASC to avoid sorting), and the rest of the reference columns are INCLUDED .

Answer 2

Perhaps these two questions will help you better understand the performance issues associated with the LIKE and = operators:

• Like vs = operator , it refers to the second
• Equals (=) vs LIKE