January 20, 2021 at 5:23 pm
Hi all,
I have a table where I have 3 indexes (User Table):
id_user CLUSTERED
last_name NONCLUSTERED
firt_name NONCLUSTERED
date_deleted
group_id
SSMS proposed me to create a composite NC index on Date_deleted, group_id, last_name, firt_name.
Does it mean that I can remove indexes on last_name and first_name and create the index that I wrote above?
Or the order of columns is important?
January 20, 2021 at 5:49 pm
Order is important. To be used, the first column(s) should match the columns in the predicate(s) of the query.
Analogy: A phonebook is fairly useless for finding a person if you don't know their last name -- you just have to scan every entry (granted phone books are of decreasing value given the migration from land lines to mobile phones).
If a query uses last_name & first_name but not date_deleted in predicate, it will not use that proposed index. But if your query uses all the columns in the proposed query, and no queries that use last_name or first_name only (or you can afford the performance hit for those queries), then you can probably drop the indexes on last_name & first_name.
January 20, 2021 at 7:03 pm
SSMS is not great about how best to define indexes.
Would you be able to post the query and the DDL for the table, including the indexes?
And please confirm, are you actually on SQL 2016+?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 20, 2021 at 7:34 pm
SSMS proposed me to create a composite NC index on Date_deleted, group_id, last_name, firt_name. Does it mean that I can remove indexes on last_name and first_name and create the index that I wrote above? Or the order of columns is important?
It wouldn't replace any of the existing indexes. It would replace an index, if you had one, on date_deleted. It might also replace the indexes if they are used in a query that specifies all of the columns in the new proposed index. There is a chance that some of the indexes you have are not being used in which case you would need to check the index usage statistics.
January 20, 2021 at 9:04 pm
The biggest problem is that I have no specific query. I have several applications that use a database. When I try to find queries using a profiler, I get hundreds of thousands of different queries in a matter of minutes.
I focused on the statistics of the indexes using. For example,
LastName (NC index) has more than 8 million seeks, any scan, and a few updates.
FirstName (NC index) has only 7 scans and anything else.
SSMS actually offered a lot of indexes, but I want to create the index (I wrote about above) because it uses more than 10 million seeks.
When I see a few of seeks less than 1000 I don't want to create other indexes.
And I was wondering If I can remove the index on the LastName when I will create this index (composite). Now I understand that this should not be done because order matters
January 20, 2021 at 9:05 pm
I focused on the statistics of the indexes using. For example,
LastName (NC index) has more than 8 million seeks, any scan, and a few updates.
FirstName (NC index) has only 7 scans and anything else.
SSMS actually offered a lot of indexes, but I want to create the index (I wrote about above) because it uses more than 10 million seeks.
When I see a few of seeks less than 1000 I don't want to create other indexes.
And I was wondering If I can remove the index on the LastName when I will create this index (composite). Now I understand that this should not be done because order matters
January 21, 2021 at 1:33 pm
Just remember, the first column is the column used to create the histogram in statistics. The histogram is one of the leading drivers for the optimizer to make determinations on row counts. So, let's say FirstName, is included in the index. The index will be more selective overall because it's a compound index. However, any searches that are only filtering on FirstName will be very unlikely to use that index. The optimizer can't tell how many rows match the value 'Grant' or 'Christi'.
Having indexes with different leading edges, first columns, can be extremely useful. For example, you have two queries, one that filters on FirstName and LastName, another that filters only on LastName. It wouldn't be crazy to have an index with the key values, FirstName & LastName in that order, and another index with the key value LastName with an INCLUDE of FirstName. For different executions of your two queries, you'll either see each index used selectively, or, you may see (this is somewhat rare) and index join between the two to satisfy your query.
Determining good indexes is all a merry dance.
"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
January 21, 2021 at 6:01 pm
Thanks, Grant.
I would like to ask also when I have only one index (a clustered index ) on the table and this index has a lot of seeks and scans, does it mean that because we have a lot of scans (scans entire table) and we need an additional non-clustered index?
January 21, 2021 at 6:17 pm
Hard to say without details.
It depends.
It could be that you simply have queries running against the table that are filtering on columns where a non-clustered index could help. But, it could also be that your code is written in such a way, for example "...LIKE '%Something'...", that the index, even though it is part of the filtering criteria, simply can't be used or used well. Also, it could be that your statistics are out of date causing the optimizer to think there are fewer (or more) rows in the table, leading to a scan. There are a bunch of other possible reasons.
However, that is one possibility. A nonclustered index on the right column(s) with any necessary INCLUDE columns could make a huge difference. Then again, it might not.
"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
January 21, 2021 at 7:38 pm
SQL provide missing index info that you can use to help determine whether you need other index(es) or not, or even whether you need to change the clustered index to be other column(s).
However, as with SSMS's recommendations, take them with a pound of salt; i.e., much more than a grain of salt.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply