January 18, 2010 at 11:54 pm
I have a table
CREATE TABLE [dbo].[TESTINDEX](
[KEY1] [int] NOT NULL,
[KEY2] [int] NOT NULL,
[VAL1] [int] NULL,
[VAL2] [int] NULL,
[VAL3] [int] NULL,
CONSTRAINT [PK_TESTINDEX] PRIMARY KEY CLUSTERED
(
[KEY1] ASC,
[KEY2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
with sample data:
DECLARE @intFlag INT
DECLARE @intFlag1 INT
DECLARE @intFlag2 INT
DECLARE @Cnt int
SET @Cnt = 0
SET @intFlag = 1
begin tran;
WHILE (@intFlag <=10000)
BEGIN
SET @intFlag1 = 1
WHILE (@intFlag1 <=100)
BEGIN
insert into
TESTINDEX(
KEY1
,KEY2
,VAL1
,VAL2
,VAL3
)
values(
@intFlag
,@intFlag1
,ROUND(((100 - 1 -1) * RAND() + 1), 0)
,ROUND(((1000 - 1 -1) * RAND() + 1), 0)
,ROUND(((10000 - 1 -1) * RAND() + 1), 0)
);
SET @intFlag1 = @intFlag1 + 1
END
SET @intFlag = @intFlag + 1
SET @Cnt = @Cnt +1
IF @Cnt >=100
BEGIN
commit;
SET @Cnt = 0
begin tran;
END
END
commit;
With SQL:
SELECT
KEY1,KEY2,VAL1,VAL3
FROM TESTINDEX
WHERE
KEY2=1
AND
VAL1=1
When I run Database Index Tunring Wizard, it give a index:
CREATE NONCLUSTERED INDEX [temp] ON [dbo].[TESTINDEX]
(
[VAL1] ASC,
[KEY2] ASC
)
INCLUDE ( [KEY1],
[VAL3])
With above index, the performance increase 99%.
But i use this index
CREATE NONCLUSTERED INDEX [temp] ON [dbo].[TESTINDEX]
(
[VAL1] ASC,
[KEY2] ASC
)
The performance increase about 50%
Please resolve for me: Why SQL do that?
January 19, 2010 at 12:37 am
The difference I see is that your First Index is using an Index seek. The include columns are pulling in the data for the full query.
The second index is performing an Index seek and a key lookup back to the clustered index.
However, as far as duration goes - both queries (once against each index) are taking about 52ms for me to execute.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 19, 2010 at 2:26 am
Your first index is covering. That means that all SQL has to do to completely satisfy the query is to do an index seek.
The second index is not covering. To satisfy that query, SQL has to do an index seek to identify the rows. then do bookmare/key lookups to the cluster/heap to get the missing columns. That's why it's slower.
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
January 20, 2010 at 11:24 pm
Yes, it's an Index Covering issue. Having the following index should also suffice your query and will not have any key/bookmark lookup and just an index seek since columns KEY1 and KEY1 already have clustered index defined on them.
CREATE NONCLUSTERED INDEX [temp] ON [dbo].[TESTINDEX]
(
[VAL1] ASC
)
INCLUDE([VAL3])
January 21, 2010 at 1:27 am
bdba (1/20/2010)
... since columns KEY1 and KEY1 already have clustered index defined on them.
Yes, but....
While it is true that the clustered index keys are part of all nonclustered indexes, I strongly recommend explicitly adding them to the key if they are needed. Otherwise you have no guarantee where they are in the key and it's even possible, depending on the index, that they are included columns only.
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
January 23, 2010 at 1:54 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply