August 26, 2015 at 7:04 am
Hi guys
Just a general query in regards the index optimizer selects at run time of a query.
I have a table with a clustered index, a Unique non clustered constraint and a non-clustered index, all are multi column with a different combination of columns used.
When I run a particular query that uses a field that is in both the non-clustered constraint and non-clustered index, optimizer uses the constraint instead of the non-clustered index.
I'm just wondering if this is random or is there a hierarchy that optimizer will always abide by depending on the location of the column in either the constraint or non clustered index - i.e. the column used in the where clause is the leading column in the constraint but is second in the non clustered index
ALTER TABLE [dbo].[Test] ADD CONSTRAINT [UNQ_Test_01] UNIQUE NONCLUSTERED
(
[AccNo] ASC,
[CaseNo] ASC,
[IssueNo] ASC
)
GO
CREATE NONCLUSTERED INDEX [TestIndex] ON [dbo].[Test]
(
[AccNo] ASC,
[CaseNo] ASC
)
GO
SELECT
AccNo
, CaseNo
, IssueNo
FROM Test
WHERE CaseNo IS NULL
August 26, 2015 at 7:24 am
From the definitions you posted, CaseNo is second column in both of those indexes. Since CaseNo is the second column, neither of those indexes are seekable, so it will have to do an index scan.
It's using the constraint because the nonclustered index is not covering (doesn't have IssueNo in it). Also, if those are the definitions, then that nonclustered index is completely redundant and a waste of space.
A unique constraint is just a unique index by another name
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
August 26, 2015 at 8:24 am
Hi Gail
The select in use is a select of a view of the table in question using different columns in the where clause - the view holds the where clause (CaseNo in the example mentioned above).
But what you mentioned in the previous post makes sense, columns in the view are not included in the NC index, hence the reason why optimizer will use the constraint instead. The NC index does look redundant alright, the indexes used for the most part look like they were set up for the sake of it.
It is performing an index seek though oddly enough.
We had the stat's on the table updated which improved performance, it's still using the constraint in the query plan but there seemed to be a huge change in performance for the end users for what looks like a very straightforward select (end users went from taking 2 minutes to 2 seconds after the stat's updated). Table holds 20k rows, 18 columns wide, very few columns with NULL values.
There are some redundant indexes on the table but they weren't using up much space or used by the optimizer too frequently according to sys.dm_db_index_usage_stats
It doesn't make much sense for such a change in performance as the indexes are rebuilt and stats updated on a nightly basis. I spotted a lot of index fragmentation though, my guess is that this is due to how data is being inserted into the table.
In any case, I created a new index with the correct leading column in our dev environment, optimizer is using that now and it would appear to be running fine there (although it seemed to run fine in our dev environment to begin with, go figure)
August 26, 2015 at 11:19 am
mitzyturbo (8/26/2015)
It doesn't make much sense for such a change in performance as the indexes are rebuilt and stats updated on a nightly basis.
After an index is rebuilt, you should not then update the stats. The index rebuild has given you the equivalent of stats updated with a FULLSCAN. An explicit stats update after that might give you less accurate stats by using only a sampling of rows.
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".
August 26, 2015 at 11:46 am
mitzyturbo (8/26/2015)
It is performing an index seek though oddly enough.
With the query you posted and the two indexes you posted, there is no way that SQL can perform an index seek. Either the query's more complex than you said, or the index definitions different to the real scenario.
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
August 27, 2015 at 2:31 am
You're correct Gail (I'd say you get that a lot :-)), after I reviewed the code compared to the stuff I posted, I didn't state the select in the same way that it actually is.
Would have loved to have just copied and pasted but I'm sure you understand why I can't
Chalk this one up to me not putting some test code together properly.
Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply