April 28, 2017 at 7:09 pm
I have a simple question that I really should know and pretty embarrassed that I don't .... I've checked online via simple searching and found only 1 part of my question answered.
Firstly, I found out via the following query, what indexs are being used on this table.
SELECT
idx.name,
stats.user_seeks,
stats.user_scans,
stats.user_lookups,
user_updates,
stats.last_user_seek,
stats.last_user_scan,
stats.last_user_lookup,
stats.last_user_update
FROM sys.dm_db_index_usage_stats stats
INNER JOIN sys.indexes idx
ON idx.object_id = stats.object_id
AND idx.index_id = stats.index_id
WHERE stats.database_id = DB_ID('Company') AND stats.object_id = OBJECT_ID('dbo.CompanyHistory')
ORDER BY stats.index_id ASC
I've attached an image of my result set for the above query.
What I'm trying to understand is:
1. Are these indexes being used?
2. If they are being used as I suspect, are they being used effectively?
3. what should my values be in the columns.
a. IX_CompanyHistory_ChangedByPersonID - looks like its not effective right since its all 0.
b. IX_CompanyHistory_HistoryTypeIDTimestamp - should this be revisted as there is more scans then seeks?
Any help would be great.
edit: spelling errors
April 28, 2017 at 10:27 pm
Tava - Friday, April 28, 2017 7:09 PMI have a simple question that I really should know and pretty embarrassed that I don't .... I've checked online via simple searching and found only 1 part of my question answered.
Firstly, I found out via the following query, what indexs are being used on this table.
SELECT
idx.name,
stats.user_seeks,
stats.user_scans,
stats.user_lookups,
user_updates,
stats.last_user_seek,
stats.last_user_scan,
stats.last_user_lookup,
stats.last_user_update
FROM sys.dm_db_index_usage_stats stats
INNER JOIN sys.indexes idx
ON idx.object_id = stats.object_id
AND idx.index_id = stats.index_id
WHERE stats.database_id = DB_ID('Company') AND stats.object_id = OBJECT_ID('dbo.CompanyHistory')
ORDER BY stats.index_id ASC
I've attached an image of my result set for the above query.
What I'm trying to understand is:
1. Are these indexes being used?
2. If they are being used as I suspect, are they being used effectively?
3. what should my values be in the columns.
a. IX_CompanyHistory_ChangedByPersonID - looks like its not effective right since its all 0.
b. IX_CompanyHistory_HistoryTypeIDTimestamp - should this be revisted as there is more scans then seeks?Any help would be great.
edit: spelling errors
If you look at your seeks, scans, you'll see that they are being used. As is normal, some are being used more heavily than others.
The IX_CompanyHistory_ChangedByPersonID has been scanned. If the name tells me what I infer from it (that it's used to cover a foreign key to a Persons table) then the scans could be the result of an update or delete on the Persons table.
As with anything, investigate what's using them and how. You asked about the scan-heavy NCI. I don't know if it's the case, but if you're using a WHERE clause in a column of IX_CompanyHistory_HistoryTypeIDTimestamp and the column isn't the leading column in the NCI, then the result will be a scan because the NCI isn't in the order the query needs. This would be a case where SQL Server uses the NCI to save on reads, but must scan the whole NCI to get the data it needs.
You have a lot of lookups against your PK. If this is also the clustered index, then your queries are using an NCI to find the data it needs and then doing a lookup on the CI to get the rest of the data it needs. It may be the case that you need another column in an NCI to cover your common queries. There's no way for me to know this definitively because I don't know your workload. Again, look into what's using them and how.
I hope this helps.
April 29, 2017 at 12:29 am
Ed Wagner - Friday, April 28, 2017 10:27 PMIf you look at your seeks, scans, you'll see that they are being used. As is normal, some are being used more heavily than others.
The IX_CompanyHistory_ChangedByPersonID has been scanned. If the name tells me what I infer from it (that it's used to cover a foreign key to a Persons table) then the scans could be the result of an update or delete on the Persons table.As with anything, investigate what's using them and how. You asked about the scan-heavy NCI. I don't know if it's the case, but if you're using a WHERE clause in a column of IX_CompanyHistory_HistoryTypeIDTimestamp and the column isn't the leading column in the NCI, then the result will be a scan because the NCI isn't in the order the query needs. This would be a case where SQL Server uses the NCI to save on reads, but must scan the whole NCI to get the data it needs.
You have a lot of lookups against your PK. If this is also the clustered index, then your queries are using an NCI to find the data it needs and then doing a lookup on the CI to get the rest of the data it needs. It may be the case that you need another column in an NCI to cover your common queries. There's no way for me to know this definitively because I don't know your workload. Again, look into what's using them and how.
I hope this helps.
When you say it needs to be the leading column, so if its also uses a Join it needs to be
1. WHERE clause first column
2 JOINS Columns 2 + n
3. Then add the Included Columns (select etc)
You're correct, the PK is also clustered Key - I'll look into this a bit further to see if I can tighten this up.
Thanks for you help. I'll get back to you if I have any further questions.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply