| Author |
Topic |
|
rubs_65
Posting Yak Master
144 Posts |
 Posted - 2005-11-17 : 15:51:04
|
| Hi,We had the big table containing about 600M records and the following query used to take long time (this is subset of the query but this part takes the most time and is part of this question/discussion): Select top 1001 col3, col1, col4, col5 From tab1 au --with (index (idx_tab1)) Where au.col6 = 44431 and au.col7 = 857669662 and au.col8 is null This table have clustered index on col1 and non-clustered index on (col6, col7, col3).Since this col6 = 44431 hold about 120M records in this table so whenever we run the above query with this col6 as predicate, SQL Server always do clustered index san and takes about 4 min but we apply the non-clustered index in the query and time reduces to 3 sec. So the bottom line is due to the skewed statistics SQL Server feels that index scan will be better that doing the combination of index seek and bookmark lookup and forgets about that we specified the TOP 1001 and it will be better to do index seek than clustered scan.So far this was working and now we have deployed the partition view in the database and partition this table on (col7, col1) and now the data is spread out on different partitions that all are on different databases. Now since we are running this query against view so we can’t apply index hints so we are wondering what are the other ways we can force optimizer to use non-clustered seek or come up with better execution plan by seeing that TOP 1001 hint is specified.Isn't it the kind of a bug that SQL Server optimizer don't always use the index seek when TOP clause is specified with small number of rows to be returned?Thanks--Harvinder |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-18 : 01:58:26
|
| "Isn't it the kind of a bug that SQL Server optimizer don't always use the index seek when TOP clause is specified with small number of rows to be returned?"Its a good point!Do you UPDATE STATISTICS using FULL SCAN option? Might that cause SQL Server to decide that au.col6 = 44431 and au.col7 = 857669662is a good candidate for index on (col6, col7, col3) ?What is the COUNT(*) for au.col6 = 44431 and au.col7 = 857669662 - small enough that the composite index should be chosen as sufficiently selective?Kristen |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-11-18 : 05:50:46
|
| Maybe try to get this in front of Paul Randal...a member here, who works for Microsoft. A solution may be advanced or a workaround may be available. |
|
|
|
rubs_65
Posting Yak Master
144 Posts |
Posted - 2005-11-18 : 10:11:13
|
SQL Server is not using the index for this values of col6=44431 even if i update the statustics with 30 percent or full scna.It only uses the indexes when i delete the statistics on this column in which case SQL Server have no idea about selectivity of column.quote:
Originally posted by Kristen
"Isn't it the kind of a bug that SQL Server optimizer don't always use the index seek when TOP clause is specified with small number of rows to be returned?"Its a good point!Do you UPDATE STATISTICS using FULL SCAN option? Might that cause SQL Server to decide that au.col6 = 44431 and au.col7 = 857669662is a good candidate for index on (col6, col7, col3) ?What is the COUNT(*) for au.col6 = 44431 and au.col7 = 857669662 - small enough that the composite index should be chosen as sufficiently selective?Kristen
|
|
|
|
rubs_65
Posting Yak Master
144 Posts |
Posted - 2005-11-18 : 10:13:04
|
How to get his email address or what is the process to contact him? (I verified that in SQL Server 2005 this is fixed as optimizer is smart enough to recognize that we are looking for just top 1001 rows)quote:
Originally posted by AndrewMurphy
Maybe try to get this in front of Paul Randal...a member here, who works for Microsoft. A solution may be advanced or a workaround may be available.
|
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-18 : 11:56:41
|
| "SQL Server is not using the index for this values of col6=44431"So also includingAND au.col7 = 857669662too doesn't cause SQL to use the index? Or were you only trying on that column?Is au.col7 more selective, and could you make the index (col7, col6, col3) instead?I'm only really talking out loud here in case it sparks an idea!Kristen |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-11-18 : 12:11:46
|
| "....How to get his email address "...in the section to view member profiles, you can get SQLTeam.com to send him an email....If he likes you, he'll respond. The method used hides the email addresses to prevent spammers |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-11-19 : 18:07:17
|
| I'll ask my buddy who runs the optimizer dev team.Paul RandalLead Program Manager, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-11-19 : 19:31:02
|
quote:
Originally posted by paulrandal
I'll ask my buddy who runs the optimizer dev team.
Answer from him:There is no way to force a seek using hints. In 2005, you can take a snapshot of your plan and use the USE PLAN hint to get back to that plan. However, this works for the same physical schema. Non-selective leading keys are tricky. However, the multi-column stats created by the index should help... Is it possible to create the index differently? col7 first, perhaps? Absent this, we'd need to see the database or a stats-only database.Paul RandalLead Program Manager, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
rubs_65
Posting Yak Master
144 Posts |
Posted - 2005-11-21 : 11:51:38
|
| Col6 is the most selective column in the index and is the leading column. I have the clone of this database (created using microsoft clone utility) and i was able to reproduce the issue on this database. How and Where should I upload this database.Thanks--Harvinder |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-11-21 : 17:50:11
|
quote:
Originally posted by rubs_65
Col6 is the most selective column in the index and is the leading column. I have the clone of this database (created using microsoft clone utility) and i was able to reproduce the issue on this database. How and Where should I upload this database.Thanks--Harvinder
How big is it zipped? If its only a few meg, email it to me, otherwise stick it on a website and send me the link.Paul RandalLead Program Manager, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
rubs_65
Posting Yak Master
144 Posts |
Posted - 2005-11-28 : 13:52:35
|
| Sorry for the late reply. We had the incident open with the microsoft on the similar issue and sent the clone database so you may be able to get the data from the Case SRX050516605598Thanks--Harvinder |
|
|
|
|
opt,imizer using bad plan so need alternatives