August 7, 2015 at 8:02 am
Hello All,
I am currently trying to tune a query and please find the details below
Table A
Clustered Index:Input_DT
Table B
Clustered Index:End_dt
Both Table A and Table B have the same structures except for the indexes.
Query which is currently causing the performance issue has the below where condition and there are a number of columns which are used in select part.
WHERE INPUT_DT<=@DT AND END_DT>=@DT
Observations:
Query with Clustered index End_dt seems to be performing better.Please see stats below
For Table B
Scan count 97, logical reads 392044, physical reads 12, read-ahead reads 388834, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Actual Execution Plan:
Clustered index seek
Estimated I/O cost:905.92
Actual Number of rows:156000
For Table A
Scan count 97, logical reads 12176735, physical reads 43, read-ahead reads 12080567, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0
Actual Execution Plan:
Clustered index seek
Estimated I/O cost:8994.42
Actual Number of rows:156400
Can anyone please guide on what is causing the performance to degrade when Table A is used as the query plan seems to be similar in both the cases and there is only slight variation in the indexes.
August 7, 2015 at 8:57 am
Without seeing the execution plans, I'm just guessing.
First guess, these are different columns with different data and different data distributions. That's likely to result in differences in behavior. Another guess, the statistics are out of date for one or both of the indexes.
Hard to guess at more without seeing the full query and the execution plan.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 7, 2015 at 10:42 am
Data distribution, statistics staleness, bad cached plan, just plain a lot of data hit/returned, etc.
I would almost ALWAYS run such queries with OPTION (RECOMPILE) to ensure you get best possible stats out of the filter(s).
Speaking of which, what are the estimated and actual row counts from each?
To really help you we need table and index defs and actual query plans.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply