July 10, 2018 at 9:47 am
We have an issue with one of our old legacy system. After upgrading hardware to this system (SQL 2000), same query which was running fine earlier (index seek) and now for some items it's doing index seek and for some of the records, it's doing clustered index scan. We tried updating stats, rebuild the indexes but still we have the same issue.
Any urgent help would be greatly appreciated.
July 10, 2018 at 10:04 am
EasyBoy - Tuesday, July 10, 2018 9:47 AMWe have an issue with one of our old legacy system. After upgrading hardware to this system (SQL 2000), same query which was running fine earlier (index seek) and now for some items it's doing index seek and for some of the records, it's doing clustered index scan. We tried updating stats, rebuild the indexes but still we have the same issue.
Any urgent help would be greatly appreciated.
Rollback to the original hardware?
Not enough information to provide much help. It would help if you could upload the actual execution plans for the two instances as *.sqlplan files.
July 10, 2018 at 10:44 am
So I guess it's doing a non-clustered index seek? When you go that route, you have to be careful of the "tipping point", where SQL falls back to scanning the table instead.
You really should review the clustered index on the table to determine if it is the best clustered index for that specific table. Hint: If your clustering is an identity, it's probably not the best clus index for that table.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 10, 2018 at 10:52 am
ScottPletcher - Tuesday, July 10, 2018 10:44 AMSo I guess it's doing a non-clustered index seek? When you go that route, you have to be careful of the "tipping point", where SQL falls back to scanning the table instead.You really should review the clustered index on the table to determine if it is the best clustered index for that specific table. Hint: If your clustering is an identity, it's probably not the best clus index for that table.
But the same query was doing index seek on old physical box. Not sure, why it's doing clustered index scan?
We have updated statistics on tables associated with this query as well as REINDEX all indexes.
July 10, 2018 at 11:04 am
EasyBoy - Tuesday, July 10, 2018 10:52 AMScottPletcher - Tuesday, July 10, 2018 10:44 AMSo I guess it's doing a non-clustered index seek? When you go that route, you have to be careful of the "tipping point", where SQL falls back to scanning the table instead.You really should review the clustered index on the table to determine if it is the best clustered index for that specific table. Hint: If your clustering is an identity, it's probably not the best clus index for that table.
But the same query was doing index seek on old physical box. Not sure, why it's doing clustered index scan?
We have updated statistics on tables associated with this query as well as REINDEX all indexes.
Again, you've most likely hit the "tipping point", which you can't really determine in advance. The potential for this is there all the time unless you are reading an extremely limited, in most cases, number of rows from a non-clus index. That's why it's so vital to get the best clus index, particularly if you are selecting a contiguous, or nearly contiguous, range of rows or ranges of rows.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 10, 2018 at 12:39 pm
Still don't have enough information to really help. We can't see what you see.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply