November 23, 2010 at 9:39 am
I have a query and I'm trying to improve its execution speed. The Estimated Execution Plan is suggesting an index to improve the speed. It is suggesting an index on table A which is a partitioned table. Table A has a structure like
CREATE TABLE A
(ThisID uniqueidentifier NOT NULL,
YearMonth int NOT NULL,
SaleAmount smallmoney NOT NULL,
StandardOrder bit NOT NULL,
PurchaseDate Date NOT NULL,
CONSTRAINT PK_A_ThisID_YearMonth PRIMARY KEY CLUSTERED (ThisID, YearMonth))
The Estimated Execution Plan is suggesting a key of
CREATE NONCLUSTERED INDEX IX_A_SO_PDate ON A (StandardOrder, PurchaseDate) INCLUDE (ThisID, YearMonth)
I have a couple of questions about this. First, does putting an index on a bit column really make sense? Second, I thought the PK (or the clustered index) on a table was automatically included in any subsequent indexes created on the table. If that's true, why would I specify the PK columns in the INCLUDE part of the new index?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 23, 2010 at 10:09 am
Short answer: Because the missing index functionality is rather limited (read stupid) and lists EXACTLY the index that it thinks it wants.
Longer answer: The columns will be there whether you specify them or not and will only be there once regardless
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 23, 2010 at 10:16 am
GilaMonster (11/23/2010)
Short answer: Because the missing index functionality is rather limited (read stupid) and lists EXACTLY the index that it thinks it wants.Longer answer: The columns will be there whether you specify them or not and will only be there once regardless
Excellent.
Response to Short Answer: Oh good, I thought I was missing something.
Response to Long Answer: That's what I thought from reading the Books Online entry, that the columns would only be there once either way, but it is good to have it confirmed.
Thanks!
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 23, 2010 at 10:17 am
An index on a bit field, probably not. But this is not an index on a bit field. It's a composite index on a bit and a date with include columns. You'll likely find that this index covers the particular query that you were running.
Do you want it? It depends.
😀
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 23, 2010 at 10:20 am
Stefan Krzywicki (11/23/2010)
Response to Long Answer: That's what I thought from reading the Books Online entry, that the columns would only be there once either way, but it is good to have it confirmed.
I really need to write a blog post on this. Must be the 5th time this has come up this month.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 23, 2010 at 10:27 am
GilaMonster (11/23/2010)
An index on a bit field, probably not. But this is not an index on a bit field. It's a composite index on a bit and a date with include columns. You'll likely find that this index covers the particular query that you were running.Do you want it? It depends.
😀
Yeah, I don't think I do. There are other ways to improve performance that I've already done and some more that I can try that'll be more generally applicable before I'd have to resort to this one.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 23, 2010 at 10:28 am
GilaMonster (11/23/2010)
Stefan Krzywicki (11/23/2010)
Response to Long Answer: That's what I thought from reading the Books Online entry, that the columns would only be there once either way, but it is good to have it confirmed.I really need to write a blog post on this. Must be the 5th time this has come up this month.
That's really the only reason I thought to look in BOL before asking for confirmaiton here. I was pretty sure I'd seen it mentioned somewhere here, but couldn't remember where.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 23, 2010 at 10:29 am
Don't discount indexes with a bit as a leading column. Especially if lots of queries include that bit column in the where clause. I wouldn't suggest you index it alone, but as part of a composite index if can be very, very useful.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 23, 2010 at 10:42 am
GilaMonster (11/23/2010)
Don't discount indexes with a bit as a leading column. Especially if lots of queries include that bit column in the where clause. I wouldn't suggest you index it alone, but as part of a composite index if can be very, very useful.
Good to know and I'll keep it in mind, but that's not why I'm discounting it. I'm not going to use it just yet because so far that bit column is only in a very small % of queries and because I've already gotten a 90% improvement in execution time by adding indexes to the temp tables in the query. Plus, I have another index or two that should be more widely applicable that might give a performance increase on this query as well.
If this column starts turning up more, I'll consider creating the index especially since I now have this recommendation.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply