I am trying to figure out how I can index this query below. The table is 155 columns. It has a clustered index on the column which is not in the query. The query is dynamically created from out of the box application and no way I cant tune it except adding index. I could not find an index that will optimize the query. The table has 511919 rows.Now, it is clustered index scan OR non clustered index Scan (with covered index)
SQL Server version : 2016 SP2, We have 22 core and 24 gbs on the server. I can post a query plan.
Declare @P0 VARCHAR(8000) ,@P1 VARCHAR(8000) ,@P2 VARCHAR(8000) ,@P3 VARCHAR(8000) ,@P4 INT ,@P5 VARCHAR(8000) ,@P6 VARCHAR(8000) ,@P7 VARCHAR(8000) ,@P8 INT ,@P9 VARCHAR(8000) Set @P0 = 'Cancelled' Set @P1 ='20190321' Set @P2 ='20190321' Set @P3 ='20190321' Set @P5 ='20190321' Set @P6 ='20190321' Set @P7 ='20190321' Set @P4 =1 Set @P8 =0 Set @P9='Service' SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16, Col17, Col18, Col19, Col20, Col21, Col22, Col23, Col24, Col25, Col26, Col27, Col28, Col29, Col30, Col31, Col32, Col33, Col34, Col35, Col36, Col37 FROM Table1 WHERE ( Col18 IS NOT NULL AND Col75 <> @P0 AND DATEDIFF(dd, Col146, CAST(SUBSTRING(@P1, 1, 4) + '-' + SUBSTRING(@P2, 5, 2) + '-' + SUBSTRING(@P3, 7, 2) + 'T00:00:00.000' AS DATETIME)) <= @P4 AND DATEDIFF(dd, Col146, CAST(SUBSTRING(@P5, 1, 4) + '-' + SUBSTRING(@P6, 5, 2) + '-' + SUBSTRING(@P7, 7, 2) + 'T00:00:00.000' AS DATETIME)) >= @P8 ) AND Col39 = @P9 ORDER BY 18 ASC ,31 DESC ,30 ASC
This is a covered index. It is used as non clustered index Scan.
/****** Object: Index [NC_OBJ_DID_SQ_CoveredIndex2] Script Date: 3/21/2019 1:30:31 PM ******/ CREATE NONCLUSTERED INDEX NC1 ON Table1 ( TableCol39 ASC, TableCol6 ASC, TableCol75 ASC, TableCol18 ASC ) INCLUDE ( TableCol1, TableCol2, TableCol3, TableCol4, TableCol5, TableCol6, TableCol7, TableCol8, TableCol9, TableCol10, TableCol11, TableCol12, TableCol13, TableCol14, TableCol15, TableCol17, TableCol19, TableCol20, TableCol21, TableCol22, TableCol23, TableCol24, TableCol25, TableCol26, TableCol27, TableCol28, TableCol29, TableCol30, TableCol31, TableCol32, TableCol33, TableCol34, TableCol35, TableCol36, TableCol37, TablCol146 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
Thanks a bunch!