[Solved] TSQL: Different execution due to parallelism

EverSQL Database Performance Knowledge Base

TSQL: Different execution due to parallelism

One of my query is performing slow for small records but for large records its working as expected. Foe example:

Query #1

FROM vwServwerHealthReport 
WHERE startDate >= '20/02/2018' 
  AND startDate <= '06/03/2018' 

-- fetching more than 6000 records in 0:02 seconds

Query #2

FROM vwServwerHealthReport 
WHERE startDate >= '02/03/2018' 
  AND startDate <= '06/03/2018' 

-- fetching approx 800 records in 02:05 seconds

I have checked execution plans as well and found that the execution plans of both the queries is different and the slow running query is using parallelism operator a lot but the fast query is not using any parallelism operation.

Initially I was assuming this to be a parameter sniffing problem and implemented "Recompile, Optimize for" hints but have not got the improvement.

Please let me know why parallelism is being used by slow running query even for small records and how can i resolve this issue.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `vwServwerHealthReport` ADD INDEX `vwservwerhealthrep_idx_startdate` (`startDate`);
The optimized query:
        vwServwerHealthReport.startDate >= '20/02/2018' 
        AND vwServwerHealthReport.startDate <= '06/03/2018'

Related Articles

* original question posted on StackOverflow here.