Sql-server – SELECT query slow even with nonclustered Index and a full-text index

sql serversql server 2014stored-procedurest-sql

I am attempting to query between 0 and 65,000 rows from a table.

The server is using Microsoft SQL Server 2014, and I have no way to change the hardware on the server.

Schema

[Id] (PK)        INT      
[varchar1]  VARCHAR(4)     Normal Cardinality
[varchar2]  VARCHAR(250)   Normal Cardinality
[varchar3]  VARCHAR(250)   Normal Cardinality
[varchar4]  VARCHAR(100)   Normal Cardinality
[date1]     DATETIME       High Cardinality
[varchar5]  VARCHAR(100)   Low Cardinality
[varchar6]  VARCHAR(1000)  Normal Cardinality
[varchar7]  VARCHAR(100)   Normal Cardinality
[varchar8]  VARCHAR(20)    Normal Cardinality
[varchar9]  VARCHAR(100)   High Cardinality
[xml1]      XML            Low Cardinality

Query

The following query is part of a stored procedure (the rest of which is irrelevant since it has trivial impact on the stored procedure's performance). The column names have been replaced with the column type and a number:

SELECT   [varchar1]
       , [varchar4]
       , [date1]
       , [varchar5]
       , [varchar6]
       , [varchar7]
       , [varchar8]
       , [varchar9]
       , [xml1]
FROM [database].[dbo].[table] WITH (NOLOCK)
WHERE [varchar1] = '0'
AND   ([date1] >='2014-1-1' AND [date1] <= '2017-1-1')
AND   [varchar8] = 'someText'
AND   [varchar9] LIKE '%a%'
ORDER BY [varchar1] ASC, [date1] DESC
OFFSET 0 ROWS
FETCH NEXT 65000 ROWS ONLY

Execution Plan

Execution Plan XML: https://gist.github.com/BlackyWolf/046856518065bfe5293cad78f73340e9

But the information it gave so far is:

Query1: Query cost (relative to the batch): 100%
                               Index Seek [NonClustered]
 SELECT            Top          [Table].[i_table_index]
Cost: 0%         Cost: 4%              Cost: 96%

Execution Plan Statement

I am not using a PK in this search. This query has the following durations depending on the columns removed:

All columns in select statement =   18s-27s
Without [xml1] =                     8s-11s
Without [xml1] and [varchar4] =      4s-6s

The total execution from Web to DB and back needs to be within 10s. Preferably this query needs to be within 4s.

Return Size (MB)

I am looking to get anywhere between 290-310 MB worth of data back, with a margin of error being 20 MB, for 65,000 records total.

Index

There is a clustered index on the Primary Key.

There is a non-clustered index defined as:

CREATE NONCLUSTERED INDEX [i_table_index]
ON dbo.[table] ([varchar1], [date1] DESC, [varchar8], [varchar9])
INCLUDE ([varchar4], [varchar7], [xml1]);

Unfortunately I can't really remove [xml1] or [varchar4] or I would. The index didn't seem to help much, to be honest, even though the execution plan showed it was using it.

My SQL Experience is limited to what I do with C#. I appreciate any help or guidance offered (even links), and if you need more information please let me know, I'll do my best to get it.

Best Answer

Each of the elements in the where clause are using functions. When functions are used sql cannot determine what index to use. It cannot determine what fields are in the where clause causing a table scan.

  1. Converge the date between to date => and date =<
  2. Is the database really case sensitive? Can you get rid of the lower()
  3. Can you turn the contains into a patindex
Related Question