Gene, you may have the point or missed it totally, not sure. The number of indexes you maintain is a performance hit when you have a lot of data in a table and your index is SELDOM used. The more complex the index is for a use at EOM operation may impact the daily CRUD operations of that table for all the other days of the week. Here are identical clustered and non clustered indexes
CREATE UNIQUE CLUSTERED INDEX [Itwhinh4331a] ON [dbo].[twhinh433] ( [t_shpm] ASC, [t_pono] ASC, [t_boml] ASC, [t_dssq] ASC, [t_serl] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
Here you have 5 columns to define unique in one of my LOTS consumption tables. This is only one index and the Shipment number will direct the natural order for the output of this index.
The article didn't go into the basics of index NAZI techniques because Brent sells a class for learning just that at the very bottom of the blog. :)
When you get into actual index tuning to deliver top performance the little things are what get adjusted to give you that big payoff in the end. When you are doing EOM operations and truncate the working tables to pull in last months data, having fewer indexes will speed things up. The generation of all the leaves that are required to grow, break as they expand while the table takes in 100,000 rows of data slows down the process. The worst ones are nvarchar data for a name or address. I have found that it is better to drop those indexes on the front end and generate them after the data is inserted.
YMMV
On Tue, Oct 9, 2018 at 9:15 PM Gene Wirchenko genew@telus.net wrote:
At 09:43 2018-10-09, "Paul H. Tarver" paul@tpcqpc.com wrote:
Good article. I have to admit sometimes I cheat and have more than five indexes with one column each when I'm optimizing Rushmore on temporary cursors, but otherwise, this is really good advice.
This is stupid advice. An arbitrary number of indexes is notcorrect. Instead, determine how many indexes are required and create that many.
Keeping it simple, keeps it fast.
But it might not meet the needs of the users.[snip]
Sincerely,
Gene Wirchenko
[excessive quoting removed by server]