Re: query performance help needed

Inna (mednyk@xxxxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... What you said was what I first planned to say, but then I read the Books Online which implies that the batch is indeed reused, so I held back from saying that. ... the query optimizer will probably match the Transact-SQL statement in the second execution with the execution plan generated for the first execution. ... SQL Server does not have to compile the second statement." ...
    (microsoft.public.sqlserver.tools)
  • Re: Interpreting the execution plan results
    ... > On Query Analyzer.. ... > I can figure out a little what the information on the execution plan means ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.tools)
  • Re: Analysing Execution plan
    ... Books Online for SQL Server SP3 at ... "Ramnadh" wrote in message ... > understand the Query execution plan properly. ...
    (microsoft.public.sqlserver.programming)
  • Re: Multicolumn correlated subquery?
    ... they had no effect on the execution plan or performance. ... situations where such "meaningless" permuation affected the query plan, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Weird speed problem
    ... Apparently, there is a subtle, but important difference. ... Would it be posible for you to post the query plans? ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)