March 5, 2017 at 5:34 am
Hi Experts,
Have a select query which is selecting 40 columns .Created index with key columns in where clause and added those in select(ie:40 columns) as included column. Is this design wrong,will this affect the write performance ?
Thanks in Advance for the help.
March 5, 2017 at 12:05 pm
VastSQL - Sunday, March 5, 2017 5:34 AMHi Experts,Have a select query which is selecting 40 columns .Created index with key columns in where clause and added those in select(ie:40 columns) as included column. Is this design wrong,will this affect the write performance ?
Thanks in Advance for the help.
Unless it's the clustered index, which auto-magically includes all of the columns then, yes, the design is terrible and, yes, it will impact write performance. You have to remember that indexes are a duplication of data. If you duplicate all of the columns of the table, then it will take twice as long to do an insert.
Now, don't just charge of to change the clustered index. All of the key columns of the clustered index become a part of the B-TREE and leaf level of non-unique non-clustered indexes.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2017 at 12:10 pm
The write speed is probably the least of the problems (unless you've got 10+ indexes). You've just duplicated that table, meaning it's taking up twice the space, twice the memory, twice the network bandwidth, backup are larger, maintenance takes longer, etc.
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
March 5, 2017 at 10:32 pm
Jeff Moden - Sunday, March 5, 2017 12:05 PMVastSQL - Sunday, March 5, 2017 5:34 AMHi Experts,Have a select query which is selecting 40 columns .Created index with key columns in where clause and added those in select(ie:40 columns) as included column. Is this design wrong,will this affect the write performance ?
Thanks in Advance for the help.
Unless it's the clustered index, which auto-magically includes all of the columns then, yes, the design is terrible and, yes, it will impact write performance. You have to remember that indexes are a duplication of data. If you duplicate all of the columns of the table, then it will take twice as long to do an insert.
Now, don't just charge of to change the clustered index. All of the key columns of the clustered index become a part of the B-TREE and leaf level of non-unique non-clustered indexes.
Included columns are for Non-Clustered indexes
March 5, 2017 at 10:33 pm
GilaMonster - Sunday, March 5, 2017 12:10 PMThe write speed is probably the least of the problems (unless you've got 10+ indexes). You've just duplicated that table, meaning it's taking up twice the space, twice the memory, twice the network bandwidth, backup are larger, maintenance takes longer, etc.
Thanks Gail,
We have done this to avoid the scan and clustered index lookup? We will remove this for now and observe.
March 6, 2017 at 3:26 am
So, a key lookup operation is added cost. However, there is not reason to automatically attempt to remove that cost unless you determine that in fact that key lookup operation is the cause of performance headaches. Do you have measures that suggest you need to make that change? If so, it's possible (not likely, and very rare, and no, most people are not the exception that they think they are) that doing what you did with the index is worth all the overhead that Gail lists. However, you have to have very good measures to ensure that all that added overhead is offset by benefits. If you can't prove it absolutely, mathematically, don't do it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2017 at 8:03 am
VastSQL - Sunday, March 5, 2017 10:32 PMJeff Moden - Sunday, March 5, 2017 12:05 PMVastSQL - Sunday, March 5, 2017 5:34 AMHi Experts,Have a select query which is selecting 40 columns .Created index with key columns in where clause and added those in select(ie:40 columns) as included column. Is this design wrong,will this affect the write performance ?
Thanks in Advance for the help.
Unless it's the clustered index, which auto-magically includes all of the columns then, yes, the design is terrible and, yes, it will impact write performance. You have to remember that indexes are a duplication of data. If you duplicate all of the columns of the table, then it will take twice as long to do an insert.
Now, don't just charge of to change the clustered index. All of the key columns of the clustered index become a part of the B-TREE and leaf level of non-unique non-clustered indexes.
Included columns are for Non-Clustered indexes
Yep. I know that and said nothing to the contrary. The reason why you can't INCLUDE columns in a Clustered Index is because all of them are already included in the CI.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2017 at 12:41 pm
VastSQL - Sunday, March 5, 2017 5:34 AMHi Experts,Have a select query which is selecting 40 columns .Created index with key columns in where clause and added those in select(ie:40 columns) as included column. Is this design wrong,will this affect the write performance ?
Thanks in Advance for the help.
Never mind all of the "bad design" answers you've already received (all excellent): it's not even possible: the stated limit is 16 columns max or 900 bytes (whichever is smaller). There's also a list of data types that can't be included etc....
https://msdn.microsoft.com/en-us/library/ms190806.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 6, 2017 at 1:07 pm
That's the limit for the index key. Include columns can be many more than 16, can be just about any size.
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
March 6, 2017 at 5:38 pm
And that's what I get for answering too fast. Correct as always, Gail - thanks!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply