April 17, 2017 at 10:41 am
Eric M Russell - Friday, April 14, 2017 4:40 PMSQLRNNR - Friday, April 14, 2017 3:45 PMEric M Russell - Friday, April 14, 2017 10:03 AMWhat you don't want to do is add too many indexes upfront which won't be used.You mean 999 indexes is not a goal for every table? :Whistling:
For most tables, 3 or 4 is too many.
Agreed
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 17, 2017 at 3:47 pm
SQLRNNR - Monday, April 17, 2017 10:41 AMEric M Russell - Friday, April 14, 2017 4:40 PMSQLRNNR - Friday, April 14, 2017 3:45 PMEric M Russell - Friday, April 14, 2017 10:03 AMWhat you don't want to do is add too many indexes upfront which won't be used.You mean 999 indexes is not a goal for every table? :Whistling:
For most tables, 3 or 4 is too many.
Agreed
Heh... gee... you mean that my 4 column table doesn't need 16 indexes to cover every eventuality? 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2017 at 1:36 pm
Jeff Moden - Monday, April 17, 2017 3:47 PMSQLRNNR - Monday, April 17, 2017 10:41 AMEric M Russell - Friday, April 14, 2017 4:40 PMSQLRNNR - Friday, April 14, 2017 3:45 PMEric M Russell - Friday, April 14, 2017 10:03 AMWhat you don't want to do is add too many indexes upfront which won't be used.You mean 999 indexes is not a goal for every table? :Whistling:
For most tables, 3 or 4 is too many.
Agreed
Heh... gee... you mean that my 4 column table doesn't need 16 indexes to cover every eventuality? 😛
You better create some indexes to help improve the write operations too.
No joke - I had a client that had numerous indexes created for write performance improvement. :crazy:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 18, 2017 at 1:50 pm
SQLRNNR - Tuesday, April 18, 2017 1:36 PMJeff Moden - Monday, April 17, 2017 3:47 PMHeh... gee... you mean that my 4 column table doesn't need 16 indexes to cover every eventuality? 😛
You better create some indexes to help improve the write operations too.
No joke - I had a client that had numerous indexes created for write performance improvement. :crazy:
If the writes are updates or deletes that have to locate the rows they're going to operate on, that can actually work.
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
April 18, 2017 at 2:30 pm
GilaMonster - Tuesday, April 18, 2017 1:50 PMSQLRNNR - Tuesday, April 18, 2017 1:36 PMJeff Moden - Monday, April 17, 2017 3:47 PMHeh... gee... you mean that my 4 column table doesn't need 16 indexes to cover every eventuality? 😛
You better create some indexes to help improve the write operations too.
No joke - I had a client that had numerous indexes created for write performance improvement. :crazy:
If the writes are updates or deletes that have to locate the rows they're going to operate on, that can actually work.
Yes the row location, it should help with that "read" aspect. The rest of the story is there was always a duplicate index for the read performance too. Removing the "write" indexes helped improve performance many times over. Not to mention the additional gains in free space in the database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 18, 2017 at 2:36 pm
SQLRNNR - Tuesday, April 18, 2017 2:30 PMThe rest of the story is there was always a duplicate index for the read performance too.
Headdesk
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
April 19, 2017 at 7:02 am
SQLRNNR - Tuesday, April 18, 2017 1:36 PMJeff Moden - Monday, April 17, 2017 3:47 PMSQLRNNR - Monday, April 17, 2017 10:41 AMEric M Russell - Friday, April 14, 2017 4:40 PMSQLRNNR - Friday, April 14, 2017 3:45 PMEric M Russell - Friday, April 14, 2017 10:03 AMWhat you don't want to do is add too many indexes upfront which won't be used.You mean 999 indexes is not a goal for every table? :Whistling:
For most tables, 3 or 4 is too many.
Agreed
Heh... gee... you mean that my 4 column table doesn't need 16 indexes to cover every eventuality? 😛
You better create some indexes to help improve the write operations too.
No joke - I had a client that had numerous indexes created for write performance improvement. :crazy:
Too bad unused indexes don't eventually drop off automatically the same way that execution plans do.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 19, 2017 at 7:49 am
Eric M Russell - Wednesday, April 19, 2017 7:02 AMSQLRNNR - Tuesday, April 18, 2017 1:36 PMJeff Moden - Monday, April 17, 2017 3:47 PMSQLRNNR - Monday, April 17, 2017 10:41 AMEric M Russell - Friday, April 14, 2017 4:40 PMSQLRNNR - Friday, April 14, 2017 3:45 PMEric M Russell - Friday, April 14, 2017 10:03 AMWhat you don't want to do is add too many indexes upfront which won't be used.You mean 999 indexes is not a goal for every table? :Whistling:
For most tables, 3 or 4 is too many.
Agreed
Heh... gee... you mean that my 4 column table doesn't need 16 indexes to cover every eventuality? 😛
You better create some indexes to help improve the write operations too.
No joke - I had a client that had numerous indexes created for write performance improvement. :crazy:
Too bad unused indexes don't eventually drop off automatically the same way that execution plans do.
I'm actually very happy that they don't. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 19, 2017 at 8:16 am
Jeff Moden - Wednesday, April 19, 2017 7:49 AMEric M Russell - Wednesday, April 19, 2017 7:02 AMToo bad unused indexes don't eventually drop off automatically the same way that execution plans do.I'm actually very happy that they don't. 😉
Every feature I suggest as potentially useful gets added in the next release, but I'm sure it will be optional, and they won't make it the default option.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply