Index usage in a raw table

Question

Hello guys

I have a problem that SQLServer 2008 Express Edition does not use the indexes that I defined and I don't understand why!

I have this test table:

 

CREATE TABLE [dbo].[tblTest](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Text1] [char](16) NOT NULL,

[Text2] [char](16) NOT NULL,

[Text3] [char](16) NOT NULL,

[IntValue1] [int] NOT NULL,

[IntValue2] [int] NOT NULL,

[IntValue3] [decimal](18, 0) NOT NULL,

[Text4] [varchar](255) NOT NULL,

 CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED 

(

[Id] 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 simple indexes in the fields Text1, Text2 and Text3 NOT UNIQUE

To populate the table I have this small script:

DECLARE @nCount  INT

DECLARE @nCount1  INT

DECLARE @nCount2 INT

DECLARE @Text1 CHAR(10)

DECLARE @Text2 CHAR(10)

DECLARE @Text3 CHAR(10)

 

SET @nCount = 1

SET @nCount1 = 1

SET @nCount2= 1

WHILE (@nCount <= 1000)

BEGIN

if (@nCount % 10 = 0) SET @nCount1 = @nCount1+1

if (@nCount % 100 = 0) SET @nCount2 = @nCount2+1

SET @Text1 = 'Text1' + (SELECT RIGHT('000000' + CAST(@nCount AS VARCHAR(5)), 5))

SET @Text2 = 'Text2' + (SELECT RIGHT('000000' + CAST(@nCount1 AS VARCHAR(5)), 5))

SET @Text3 = 'Text3' + (SELECT RIGHT('000000' + CAST(@nCount2 AS VARCHAR(5)), 5))

INSERT INTO dbo.tblTest (Text1, Text2, Text3, IntValue1, IntValue2, IntValue3, Text4) VALUES (@Text1, @Text2, @Text3, @nCount, @nCount1, @nCount2, 'Text4')

SET @nCount = @nCount +1

END

 

with this I got 1000 rows on the table. After I have tree simple queries:

SELECT Id, Text2, Text1, Text3, IntValue1

FROM tblTest

WHERE Text1 = 'Text100500'

ORDER BY Text1

 

SELECT Id, Text2, Text1, IntValue1

FROM tblTest

WHERE Text2 = 'Text200050'

ORDER BY Text2

 

SELECT Id, Text2, Text1, Text3, IntValue1

FROM tblTest

WHERE Text3 = 'Text300005'

ORDER BY Text3

 

I run the queries and compare the Execution Plan and something it's not right. The first query use a Index Seek [NonCluestered] operation for the index created for the field Text1 and it's ok ... this is the expected behavior.  The other two queries use a Clustered Index Scan operation defined for the Primary Key.

The execution time of this two queries are much more when the index it's used (I measure the time in milliseconds with a .NET application) and for this two queries use the index I add the line: WITH (INDEX=IdxText2) and WITH (INDEX=IdxText3) to the queries and now the Execution Plan looks good.

What's it's wrong in my approach? How the indexes should be created for the SQL Server use them?

 

Regards
Paulo Aboim Pinto
Odivelas - Portugal

---

Paulo Aboim Pinto

Answer 1

If you see the data, column TEXT1 has unique records where as TEXT2 & TEXT3 has duplicate records.

In the case of first query (filter by TEXT1), sql has to fetch only one record. Since an index is already defined it uses the index and then does a look up to the clustered index to get other columns.

In the case of other two queries, sql has to fetch more records. If it uses non-clusetered index,then it has to do clustered index look up for each and every row. Since the table is small, sql might have felt that clustered index scan is better than  non-clustered index +  clustered index lookup for each row.

See the query execution plan for the below queries (put these queries in separate window and see exection plan)

SELECT Id, Text2, Text1, Text3, IntValue1

FROM tblTest 

WHERE Text3 = 'Text300005'

ORDER BY Text3


SELECT Id, Text2, Text1, Text3, IntValue1

FROM tblTest WITH (index = idx3)

WHERE Text3 = 'Text300005'

ORDER BY Text3

the "Query Cost" for the  first one is 18% where as for the second one it is 82%.

One option is to create covering index or include columns in the index. But index creation targetting for particualr query should only be done after evaluating some aspects like how critical the query, how frequently it is used and what will be the impact if this index is added.

 

Answer 2

Suresh is right. Consider that if the query (based on the parameters, WHERE clause and other focusing expressions in the SQL) spans more than 20% of the table pages (not rows), the query optimizer will revert to (usually more efficient) table scan.

hth

---

__________________________________________________________________
William Vaughn
Mentor, Consultant, Trainer, MVP
http://betav.com
http://betav.com/blog/billva
http://www.hitchhikerguides.net

“Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

Please click the Mark as Answer button if a post solves your problem!

Answer 3

As per me there  are two conditions when one can land up in such situation:

1. As specified by suresh if the scan is better than seek the compiler will use scan rather than seek

solution:

• try out creating coveting index
• create index indx4 on [tblTest]([Text3],Id, Text2, Text1, IntValue1)
• that will reduce the cost of 3rd query to 17%.

drawbacks :

• Takes more space
• insert/update/delete may be hampered

2. there might be sorted seek problem

http://blogs.msdn.com/queryoptteam/archive/2006/04/12/575241.aspx

solution:

use of index hint as you have used is good method.