April 13, 2011 at 3:50 am
Greetings:
Is there a way to test or check that an index that you have set up is working?
I have a table in which I have specified that the first two columns be a UNIQUE and CLUSTERED index for that table. How can I then tell that this policy has been carried out? When I examime the table using the Server Management Studio, I don't see anything different that tells me that this new index has "kicked in".
Thank you
April 13, 2011 at 4:15 am
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
Will show if if there is any index usage.
run your query and look at the explain plan. That will tell you if your query is using the index and how it is being (or not) used.
If not, compare the where clause of the query and the indexed columns.
April 13, 2011 at 5:49 am
If you didn't get errors when you created the index, it's there, and "kicked in." Now, whether or not it's getting used, well, there's used and there's used. As was stated, you can check the index stats to see if it's being referenced, but the mechanism to check to see that you are using the index well is to look at the execution plan. Best is when you see index seeks. An index scan is an indication of use, but not necessarily good use of that index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply