I have 2 identical databases (one test and one to be live soon) in
different installations. Both have an identical table with
approximately 4.5 million rows and a row width of 339 characters. Both
tables have the same primary key constraint and clustered and
non-clustered indexes. Both tables have the same data (one was loaded
from the other and statistics are up to date.

My problem is that when running a select like:

select *
from tablename
where keycol = 'value'

in the test database the response is very fast and the QEP shows that
SQL Server is doing a 'clustered index seek' and the I/O cost is 0.003
and the CPU cost is 0.00008. In the live database the QEP shows that
SQL Server is doing an 'index scan' and the I/O cost is 9.4 and the CPU
cost is 2.5.

Whilst I'm not sure what these cost units are I can see that the 2 QEPs
have very different costs and the QEP is very different. Interestingly,
running the following query in both databases:

select count(*)
from tablename

leads to QEPs that are identical to the previous QEP in the live
database.

I've run sp_dboptions for both databases and they are the same.

Whilst these databases are in different installations and on different
machines, the OS and SQL Server software are identical and shouldn't
lead to a different QEP. It looks to me that SQL Server is just
ignoring the statistics and ignoring the fact that there is a primary
key constraint on the column in the where clause.

Has anyone any ideas how to investigate this further or solve the
problem ?

Thanks in advance.

Laurence Breeze
The Open University

[posted and mailed, please reply in news]

Laurence Breeze (laurence_breeze@yahoo.co.uk) writes:
[quoted text, click to view]

If you had had an Index Seek in once case, and a Clustered Index Scan,
I would have discussed table scans contra bookmark lookups.

But if you have Clustered Index Seek in once case, and Index Scan
in the other, I get a feeling that the clustered index is missing
in the live database.

If this guess is incorrect, can you post the CREATE TABLE statement for the
table, and the output from sp_helpindex on both servers for the table? Of
course, I would like to see the actual query as well.

[quoted text, click to view]

That query should be carried out your most narrow (in terms of key
length) non-clustered index.




--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

Hi,

I don't have the original create script. I've extracted a script for
the create table and create index statements from SQLServer using Query
Analyser for both databases, see below:

CREATE TABLE [LTS_leu_table] (
[PersId] [varchar] (14) COLLATE Latin1_General_CI_AS NOT NULL ,
[OUcu] [varchar] (14) COLLATE Latin1_General_CI_AS NULL ,
[Title] [varchar] (40) COLLATE Latin1_General_CI_AS NULL ,
[Name] [varchar] (64) COLLATE Latin1_General_CI_AS NULL ,
[Address] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Country] [varchar] (12) COLLATE Latin1_General_CI_AS NULL ,
[Region] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[DayPhone] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[EvePhone] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[Email] [varchar] (60) COLLATE Latin1_General_CI_AS NULL ,
[Fax] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[DoB] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[MasterStatus] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[UserType] [int] NULL ,
PRIMARY KEY CLUSTERED
(
[PersId]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [PersId_ind] ON [dbo].[LTS_leu_table]([PersId])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [OUcu_ind] ON [dbo].[LTS_leu_table]([OUcu]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

The only difference between the scripts for the 2 databases is that the
script for the test database has fillfactors set whilst the script for
the live database doesn't.

My original query was:

select * from lts_leu_table where persid = '12345678'

The QEP shows an index scan using the PersId_ind index with a bookmark
lookup.

The following query:

select count(*) from lts_leu_table

has a QEP which also shows an index scan using the PersID_ind index and
has the same costs as the query above.


Since my original post I've tried dropping the indexes and recreating
them with no change to the QEPs.

The results of running sp_helpindex on the table are:

index_name index_description
index_keys
---------------------------
--------------------------------------------------- -----------
OUcu_ind nonclustered located on PRIMARY
OUcu
PersId_ind nonclustered, unique located on PRIMARY
PersId
PK__LTS_leu_new__4C214075 clustered, unique, primary key located on
PRIMARY PersId

I have also tried running a similar query to my original one that I
would expect to use the other non-clustered index, i.e:

select * from lts_leu_table where oucu = 'ilb3'

The QEP for this query also shows an index scan, this time using the
OUcu_ind index with a bookmark lookup.

It seems very odd that the appropriate index is used in both cases but
not in the way I would have expected.

Laurence Breeze



[quoted text, click to view]