Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Performance


Hi,

SQL Server 2005 sp1 + coumlative HF

The problem we face is a stored procedure that consists of one complicated
SELECT that runs in different durations on different servers.

On the Publisher srv the sp takes 30 sec to complete due to an inefficient
execution plan
while on 2 Subscribers of a Transactional Replication the sp completes in 3
seconds due to an efficienmt plann that uses only indexes and no tables
scans at all.

The data and indexes are the same in all servers.
Wew have auto update stats on + the selectivity of the columns involved in
the WHERE clause is very good so there should not be a reason for the
Optimizer to create a plan which preffers Clustered Index Scams.

Any idea would be appreciated.

Thanks,
Yaniv

Perhaps this is a candidate for manually updating statistics using FULLSCAN?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

I updated stats manuall before this post, I think i used FULLSCAN but I will
verify this again and post here the results.
----------------

"Tibor Karaszi" <tibor_please.no.email_kara@hotmail.nomail.com> wrote in
message news:OSP6MDgoHHA.2596@TK2MSFTNGP06.phx.gbl...

UPDATE STATISTICS WITH FULLSCAN resolved the problem. So now the sp runs the
same in all enviroments.
We now face another issue with this sp; When the sp is run from SSMS it uses
a good plan which has little IO and therefore a duration smaller than a sec.
However when the same sp is run from the application (aspx) it takes 7 sec
to complete. When traced in Profiler the RPC Completed event shows that the
long duration is a result of 6,000,000 logical reads.
I run DBCC FREEPROCASH but the situation remains.

What could possible cause the sp to use a different plan when it is executed
from different applications; SSMS and IE?
---------------

"yan" <yan@rediffmail.com> wrote in message

news:en2RJNjoHHA.4196@TK2MSFTNGP06.phx.gbl...

Hi,

SQL Server 2005 sp1 + coumlative HF.

We face the following problem: when a stored procedure is run from SSMS it
uses a good plan which has little IO and therefore a duration smaller than a
sec. However when the same stored procedure is run from the application
(aspx) it takes 7 sec to complete. When traced in Profiler the RPC Completed
event shows that the long duration is a result of 6,000,000 logical reads.
I run DBCC FREEPROCASH to cause the sp to recompile but the situation
remains.

What could possible cause the sp to use a different plan when it is executed
from different applications; SSMS and IE?

Thanks,
Yaniv

-----------------------------------------------Reply-----------------------------------------------

> What could possible cause the sp to use a different plan when it is executed from different
> applications; SSMS and IE?

One possible cause is that you might not have the same settings for your SET parameters, resulting
in a potentially different semantics of the query, hence a different execution plan...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Tibor,
You were correct. When I queried the dm views I found there were 2 plans for
this sp. I cleaned the proc cash, run the sp from SSMS and it resoved the
problem. Now I have a single plan that works well.

However, before sending the post to the group i also freed the proc cash but
it did not help so I assumed it is not a case of plans. What I suspect now
and this I will test in another enviroment is that if I free the proc cash
and the first exec of the sp is via the app then the result may be a bad
plan cashed.

If this is the case it is not good because follwoing an iis reset we will be
in problem again. Do you think a Plan Guide may suitable for this situation?
-----------

"Tibor Karaszi" <tibor_please.no.email_kara@hotmail.nomail.com> wrote in
message news:OYiPQk3oHHA.4188@TK2MSFTNGP02.phx.gbl...

> You were correct. When I queried the dm views I found there were 2 plans for this sp.

You should only have several plans for the proc when there *are* differences. So, I'd start by
checking for those two plans what the differences are. SET parameters, user, or something else. See
the WP on plan caching (http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx), and also
use DM views to investigate what settings differ for the two plans.

I consider plan guides as more of a last resort, so make sure you understand *why* you get different
plans.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc