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 <[email protected]> wrote: > At 09:43 2018-10-09, "Paul H. Tarver" <[email protected]> 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 not > correct. 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] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/cajidmylezp02+c4pzxotasemkb5_-tvztwygximiqkxzmmg...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
