Clustered Index Seek

I am trying desperately to ensure that my queries use
Index Seeks or Clustered Index Seeks and NOT clustered
index scans but I am having difficulty.
I have a combination Primary Key on 3 fields...therefore
the Clustered Index is also on these 3 fields. I have
ordered the fields so that the most uniquely occuring
field is first in the index.
If I join 2 tables on these 3 fields, I still get scans
and not seeks when I look at the Estimated Execution Plan
for this table with the correctly ordered index.
Does anyone know how to ensure your queries use Seeks and
not Scans? Thanks!Mark
http://www.sql-server-performance.com/clustered_indexes.asp
"Mark" <mark_nilsen@.gfc-consulting.com> wrote in message
news:096201c38e3c$4e594100$a301280a@.phx.gbl...
> I am trying desperately to ensure that my queries use
> Index Seeks or Clustered Index Seeks and NOT clustered
> index scans but I am having difficulty.
> I have a combination Primary Key on 3 fields...therefore
> the Clustered Index is also on these 3 fields. I have
> ordered the fields so that the most uniquely occuring
> field is first in the index.
> If I join 2 tables on these 3 fields, I still get scans
> and not seeks when I look at the Estimated Execution Plan
> for this table with the correctly ordered index.
> Does anyone know how to ensure your queries use Seeks and
> not Scans? Thanks!
>|||In order to seek the query must be highly selective . That is it must return
a very small percentage of the records, Perhaps 3% or less. While there are
many other factors which affect the use of indexes, selectivity is often the
thing that causes sql to scan or use the clustered inx instead of a
non-clust.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark" <mark_nilsen@.gfc-consulting.com> wrote in message
news:096201c38e3c$4e594100$a301280a@.phx.gbl...
> I am trying desperately to ensure that my queries use
> Index Seeks or Clustered Index Seeks and NOT clustered
> index scans but I am having difficulty.
> I have a combination Primary Key on 3 fields...therefore
> the Clustered Index is also on these 3 fields. I have
> ordered the fields so that the most uniquely occuring
> field is first in the index.
> If I join 2 tables on these 3 fields, I still get scans
> and not seeks when I look at the Estimated Execution Plan
> for this table with the correctly ordered index.
> Does anyone know how to ensure your queries use Seeks and
> not Scans? Thanks!
>|||It will be very difficult for people to help with an issue like this unless
you're able to put together a repeatable test that includes DDL and a script
to load test data that will repro the issue you're seeing...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Mark" <mark_nilsen@.gfc-consulting.com> wrote in message
news:096201c38e3c$4e594100$a301280a@.phx.gbl...
> I am trying desperately to ensure that my queries use
> Index Seeks or Clustered Index Seeks and NOT clustered
> index scans but I am having difficulty.
> I have a combination Primary Key on 3 fields...therefore
> the Clustered Index is also on these 3 fields. I have
> ordered the fields so that the most uniquely occuring
> field is first in the index.
> If I join 2 tables on these 3 fields, I still get scans
> and not seeks when I look at the Estimated Execution Plan
> for this table with the correctly ordered index.
> Does anyone know how to ensure your queries use Seeks and
> not Scans? Thanks!
>