June 25, 2009 at 9:41 am
Without seeing the execution plan I'm going to guess that your IDX_App index is not a covering index. With a nonclustered index unless it is very selective OR covering, the optimizer will not generally use it. Adding the hint didn't help things because even though the nonclustered index helps to identify the rows more efficiently, SQL still has to perform a bookmark lookup against the clustered index to get the rest of the data.
You need to INCLUDE all the other columns that you need in the index. That way SQL has all the data it needs right in one place.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 25, 2009 at 9:44 am
The thing to look for in the execution plan (the one with the index hint) is that there will be a nonclustered index seek and then a bookmark lookup to the clustered index. If you look at the properties of that lookup, you'll see an "output list" this is the list of columns that SQL needs to lookup and get from the clustered index. If all of those columns are put into the nonclustered index, the bookmark lookup goes away and the query speeds up.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 25, 2009 at 11:02 am
SQLSlammer (6/25/2009)
Some jumped up new DBA decided that too many people had sysadmin permissions, so he started revoking permissions..including yours....ring any bells? hehe
You really must be thinking of someone else, for the past ten years ive been carrying the SA password with me in my wallet. Or you going back further than that ?
June 25, 2009 at 3:03 pm
DCPeterson (6/25/2009)
A clustered index is ALWAYS unique.
Was this reply prompted by my post?
If so, I think you might have missed what I was driving at 🙂
If you get a minute, read the blog post I referenced - it provides the context for what I wrote.
Ta.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 28, 2009 at 3:52 pm
Is 14 sec a long time for that query to execute? The original post says it takes 14 sec to return 42000 rows from a query that uses 3 very unselective columns (all of them can have only 2 values 0 or 1 and there is OR in the WHERE clause). Isn't Index Scan an exact thing that SQL server should do? Just curious ...
I tried to run it on sql 2008 (developer edition which is almost like Enterprise) 64 bit with dummy table, used included table definition but populated with dummy data, and it run 3 sec. But it's a fresh machine, fresh db and fresh table, so probably no fragmentation and the data was added in batches so it's not like real ....
How fast are those machines .. etc.. From my experience (not very extensive) SQL 2005 is slower than 2008.
July 1, 2009 at 2:45 am
Update:
Dave Ballantyne, well if you're not Scottish and you didn't work in Hemel Hempsted then it's definitely not you 🙂
I think the slow view is more down to general bad performance accross the server and a bit of me being a numpty. We are getting a new server and refurbishing the old ones (disks configured badly ect) and san attaching the lot.
Thanks for all the replies, they were very helpful regarding indexes and included columns.
Is there some way I can add to peoples reputation, or give them points for their posts?
July 1, 2009 at 3:11 am
SQLSlammer (7/1/2009)
Dave Ballantyne, well if you're not Scottish and you didn't work in Hemel Hempsted then it's definitely not you 🙂
Hemel Hempstead has one of the most amazing roundabouts I have ever seen!
SQLSlammer (7/1/2009)
Is there some way I can add to peoples reputation, or give them points for their posts?
Nope - not on this site, we do it for the thrills 😀
Thanks for the feedback - it's nice when people take the time to do that.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 1, 2009 at 3:50 am
I actually think the roundabout works very in terms of traffic calming, confusing at first but good once you're used to it 🙂
I post answers on another SQL Server forum which is less advanced than this forum (in terms of the questions posted) and I agree it is kinda annoying when you post a massively detailed and helpful response only to get no reply- so you never know whether you were actually helpful or not.
July 1, 2009 at 3:54 am
SQLSlammer (7/1/2009)
Dave Ballantyne, well if you're not Scottish and you didn't work in Hemel Hempsted then it's definitely not you 🙂
No to both counts on that one 🙂 , or is that going to create double negative confusion 😀
July 1, 2009 at 4:02 am
Lol, so there's two Dave Ballantyne DBAs. The Scottish one should surely be your nemesis now.. 😀
You may well have to hunt him down and destroy him whilst screaming "THERE CAN ONLY BE ONE"
July 1, 2009 at 8:11 am
Paul White (6/25/2009)
DCPeterson (6/25/2009)
A clustered index is ALWAYS unique.Was this reply prompted by my post?
If so, I think you might have missed what I was driving at 🙂
If you get a minute, read the blog post I referenced - it provides the context for what I wrote.
Ta.
Paul
Sort of... but more in the way that your post just reminded me that many folks don't really understand the relationship between clustered and nonclustered indexes etc... so I thought I'd take a minute and explain some of the basics. It wasn't meant as a direct reply to your post.
The blog entry you linked to was interesting, but if I read and understood fully, it doesn't change anything I wrote because I was talking about the leaf level of the nonclustered index while the blog post indicates that under certain circumstances the clustered index key may or may not also be stored in the intermediate levels of the nonclustered index.
I still don't think I fully appreciate the implications of what she wrote, so I'll have to noodle on it a bit more.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply