Posted by Erland Sommarskog on 11/20/07 22:28

Inna (mednyk@hotmail.com) writes:
> The stored procedure in all databases optimised well, all fields are
> indexed.

Just because you index all columns, does not mean that you have the
right indexes. You may need multi-column indexes.

> So I was thinking since this is development server , we don't
> rebuild indexes and if we go on production maybe it will be better...

Indeed, fragmentation may be a reason for the differences you are seeing.
Fragmenation certainly does not help when you need to read data from
disk.

> There is another trick I was thinking about, which I am not sure is
> good and possible to do, maybe I should create a job,
> which checks if the execution plan is there (this I am not sure I can
> do)

Whether the execution plan is there or not has little do with it.
What matters is if the data is in cache, you need to read from
disk. Now, here is an important thing: if you query requires
tables to be scanned, that means more pages to be read, and that
means that there will be more crowded in the cache.

It's very clear that you need to analyse your query plans, and see if
you can improve indexing.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]