November 16, 2012 at 4:54 am
Hi All
I've run a query to obtain missing index details (from Brent Ozar) and I've found two that are highly recommended. Looking at the other indexes for that table there are two others. I'm wondering of the possibility of merging so there is less maintainance during updates as this table is read and updated lots. The cluseterd index of the table lies on TimeCostID, the table has 4459195. The older data is rarely requested gernerally only things from the last few months or occassionaly financial year.
The only other index (bar the clusterd on PK) is
CREATE NONCLUSTERED INDEX [ix_ClientID_TimeOut]
ON mytable
([ClientID], [TimeOut] )
Now although the reads per write are low, it is heavily used (been inplace a couple of days now).
reads: 27825
writes: 65249
rows: 4459384
reads_per_write: 0.426
Here's the results as create statements (with some names changed), I've included the impact number, 1'000'000 is apparently high:
--895324.1726
CREATE NONCLUSTERED INDEX ix_AdminID_CaseNo
ON mytable ( [AdminID],[Case No] )
INCLUDE ([ProductID], [ClientID], [TimeIn]);
--855309.8385
CREATE NONCLUSTERED INDEX ix_ProductID_AdminID
ON mytable ( [ProductID], [AdminID] )
INCLUDE ([ClientID], [Case No], [TimeIn]);
--42882.34599
CREATE NONCLUSTERED INDEX ix_ProductID_TimeIn
ON mytable( [ProductID], [TimeIn] )
INCLUDE ([Case No], [TimeOut], [AdminID]);
--3602.017077
CREATE NONCLUSTERED INDEX ix_TimeOut_TimeIn
ON mytable ( [TimeOut],[TimeIn] ) ;
Any advice on which of these can be merged would be most helpful - enlightening in fact.
Many thanks in advance!
November 16, 2012 at 5:07 am
None of those can be merged, they're all distinct indexes.
Whether they're actually required or not you will have to test to determine. Don't accept the index suggestions as requirements, they're suggestions.
While you could potentially merge these two, you can't create a single index that will be as effective as the two separate ones. Again, whether a merged one will be effective enough or not, you'll have to test.
CREATE NONCLUSTERED INDEX ix_ProductID_AdminID
ON mytable ( [ProductID], [AdminID] )
INCLUDE ([ClientID], [Case No], [TimeIn]);
CREATE NONCLUSTERED INDEX ix_ProductID_TimeIn
ON mytable( [ProductID], [TimeIn] )
INCLUDE ([Case No], [TimeOut], [AdminID]);
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 16, 2012 at 5:14 am
Thank you for the prompt reply. If I were to do that, just so I understand for the future, which order would I put the second two columns in (as they will both be sorted so I assume the order is relevent)?
Thanks again.
Actually, I guess put it in the order of the recommendation, AdminID then TimeIn?
November 16, 2012 at 5:27 am
MartJ (11/16/2012)
Thank you for the prompt reply. If I were to do that, just so I understand for the future, which order would I put the second two columns in (as they will both be sorted so I assume the order is relevent)?
The order is relevant and it depends on the queries that will use the index. No way to say without looking at that.
Personally, I'd probably go for 2 indexes, or significant testing to ensure that merging the two into a not-quite-suitable index is acceptable for performance.
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 16, 2012 at 5:38 am
Ok Gail I'll take a look at the application and see if things can be improved/altered there.
Thanks again, very much appreciated!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply