poor index performance – mssql 2005 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

poor index performance – mssql 2005

Hey guys, Having some trouble with indexes on sql server 2005. I’ll explain it with a simplified example.
I have a customers table, and a sp to list customers :
create table Customers(
CusID int not null,
Name varchar(50) null,
Surname varchar(50) null,
CusNo int not null,
Deleted bit not null
)
create proc spCusLs (
@CusID int = null,
@Name varchar(50) = null,
@Surname varchar(50) = null,
@CusNo int = null
)
as select
CusID,
Name,
Surname,
CusNo
from
Customers
where
Deleted = 0
and CusID <> 1000
and (@CusID is null or CusID = @CusID)
and (@CusNo is null or CusNo = @CusNo)
and (@Name is null or Name like @Name)
and (@Surname is null or Surname like @Surname)
order by
Name,
Surname
create nonclustered index ix_customers_name on customers ([name] asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary create nonclustered index ix_customers_surname on customers (surname asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary create nonclustered index ix_customers_cusno on customers (cusno asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary I’ve recently noticed that some tables, including ‘Customers’ don’t have indexes except primary keys. And I have added indexes to "name", "surname" and "cusno" columns. This has dropped the number of IO reads. But the strange thing is; one time it works with name / surname searches like (‘joh%’ ‘%’) but when CusNo is included, it does a full scan. And vice versa when the SP is recompiled using ‘alter’, works ok with CusNo, but not with name/surname. Recompile it, and it’s reversed again. When run as a single query, the execution plan looks different. What’s happening? Perhaps something to do with statistics? This doesn’t have a big payload on the server, but there are some other procs suffering from this on heavy queries, making server performance worse than before…
In most cases, when a WHERE clause has "<>" or "is null" or "or", or "like", or if you have any columns that are not highly selective (like your "deleted" colum, like yours does, the query optimizer is unable to use an index and must full table scan. So, in other words, there is a lot in this query to product problems, preventing any current indexes from being used. Also, it appears that you have no clustered index, unless I am missing seeing it. Consider creating a clustered index on the CusNo. Then if you search for CusNo (which should be unique), then the clustered index can be used for a quick index seek, and the other data needs for the rest of the WHERE clause will be immediately availably for very quick analysis, and the query should speed greatly. ——————————–
Brad M. McGehee, SQL Server MVP
http://www.sqlbrad.com
what is comes down to is:
you can write one query that does everything (very) poorly or one query that does one thing very well, ie, separate queries for separate functions

You may also want to have a look at http://sommarskog.se/dyn-search.html Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

]]>