July 3, 2008 at 8:07 am
I have read through articles on best practices when selecting indexes for a table, I would not say I am confused, but I think an example would help sum things up. So as an example, the following query performed 105,881 reads.
exec sp_executesql N'SELECT A1.HISTORYID, A2.TEXT A2_TEXT, A1.COMPLETEDDATE, A1.USERID, A1.CONTACTNAME, A1.RESULT, A1.DESCRIPTION,A2.ITEMID FROM HISTORY A1 INNER JOIN PICKLIST A2 ON (A1.TYPE=A2.ID) WHERE ((A1.TYPE<>262162) OR (A1.USERID=''U6UJ9A000037'')) AND (A1.OPPORTUNITYID = @P1)
AND (A1.TYPE <> 262156) Order By A1.COMPLETEDDATE Desc
', N'@P1 varchar(12)', 'O6UJ9A003YZR'
The current indexes consist of:
HISTORY
nonclustered located on PRIMARYCONTACTID, COMPLETEDDATE, HISTORYID, TYPE, DESCRIPTION, USERID, USERNAME, RESULT, NOTESHISTORY2
clustered located on PRIMARYUSERID, TYPE, STARTDATEPK_HISTORY
nonclustered, unique, primary key located on PRIMARYHISTORYIDXIE1HISTORY
nonclustered located on PRIMARYACCOUNTIDXIE2HISTORY
nonclustered located on PRIMARYACTIVITYIDXIE3HISTORY
nonclustered located on PRIMARYCONTACTIDXIE5HISTORY
nonclustered located on PRIMARYPROCESSIDXIE6HISTORY
nonclustered located on PRIMARYSTARTDATEXIE7HISTORY
nonclustered located on PRIMARYUSERID, TYPEXPKHISTORY
nonclustered, unique located on PRIMARYHISTORYID
PICKLIST
nonclustered located on PRIMARYTEXTXIE2PICKLIST
clustered located on PRIMARYPICKLISTID, ID, TEXTXPKPICKLIST
nonclustered, unique located on PRIMARYITEMID
If I only take this query into consideration, I would think I would need an index on HISTORY.TYPE and another on PICKLIST.ID because they are used in a join. HISTORY.USERID and HISTORY.OPPORTUNITYID because they are used in the where clause. Another index would be needed on HISTORY.COMPLETEDDATE because it is being used in an order by clause.
Where I become a little confused is when to have a composite index, which columns should be included, and why? Also, how do the columns in the select statement play into the index selection process? Any suggestions would be greatly appreciated.
July 3, 2008 at 10:25 am
I would also be up for any generalized tips when creating indexes.
July 3, 2008 at 10:43 am
Where the columns in the select list come into index selection relates to covered indexes. For example, you have an index on an Employee table using EmpID, EmpLastName, EmpFirstName. In several queries you only need the EmpLastName and EmpFirstName from the Employee table and you link to the table (index) using EmpID. SQL Server will use this index and not access the actual data from table, as it is available in the index.
SQL Server 2000/2005/2008 limits the size of the indexed columns to 900 bytes, but SQL Server 2005/2008 allow for including additional columns in the index. This can be a blessing and a curse in SQL Server 2005/2008 as the DTA tool (if you use it) will sometimes suggest an index with 30 or more included columns. You have to be careful when creating covered indexes, as you add overhead to insert/update/delete processes, so you have to balance their need with performance requirements.
😎
July 3, 2008 at 11:07 am
I would think I would need an index on HISTORY.TYPE and another on PICKLIST.ID because they are used in a join.
Assumming that a nested loop will be used and HISTORY is the driving table, only an index on PICKLIST.ID is needed.
HISTORY.USERID and HISTORY.OPPORTUNITYID because they are used in the where clause.
As the HISTORY.OPPORTUNITYID has a "where equal", this should be the first column in the index. Depending on how many rows for the same OPPORTUNITYID, adding additional columns may or may not be usefull but would have a negative impact on insert, update and delete performance.
Another index would be needed on HISTORY.COMPLETEDDATE because it is being used in an order by clause.
Actually as such an index would not be used to access the rows (which is by OPPORTUNITYID), such an index would not be used and sorting would occur.
SQL = Scarcely Qualifies as a Language
July 3, 2008 at 12:10 pm
As far as the columns in the join, I would only need to included the column from the table searching for the match, not the originating table?
I see what you are saying about making OPPORTUNITYID first in the index because that should create a small enough subset to search through for the remaining items in the where clause. If OPPORTUNITYID returned a large subset, I could include another column to decrease the size.
I am not completely sure of covering indexes. I would assume that it is bad practice to include all columns in a covering index that are being selected. That being said, how does one best gauge which columns to include in a covering index, and which to leave out?
Lastly, would a separate index need to be created for each of these functions? A nonclustered index for the column used in the join. A nonclustered index on OPPORTUNITYID for the where clause. Another nonclustered index for the columns of the covering index.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply