HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
indexfullwithqueryselecttextslownonclusteredandeven

Problem

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%


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 ex

Solution

The double-wild card action will not do you any favors ([varchar9] LIKE '%a%'). Because you're searching in the middle of the string, SQL Server is going to scan every row, and no index will help matters. It's expensive, see Brent Ozar's post (https://www.brentozar.com/archive/2016/10/searching-strings-sql-server-expensive).

FETCH NEXT logic is necessary? It's an excellent way to bog down your resources, particularly with an xml column in tow. To read about the overhead see Figure 7.2 here (https://use-the-index-luke.com/sql/partial-results/fetch-next-page)

Aaron Bertrand writes about a great technique that minimizes the extra overhead, if you really need to do paging in sql server, I have used this technique to much applause and success. Basically, collect the PRIMARY KEYS and use that to collect the xml and all the other columns you'll need. you will be slinging around a lot less baggage.
https://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch

Context

StackExchange Database Administrators Q#162804, answer score: 2

Revisions (0)

No revisions yet.