Sql Server Programming :: Performance


	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 - Hide quoted text - "Yan" <y@rediffmail.com> wrote in message news:utfkEfeoHHA.1000@TK2MSFTNGP05.phx.gbl... > 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 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... - Hide quoted text - > 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 > "Yan" <y@rediffmail.com> wrote in message > news:utfkEfeoHHA.1000@TK2MSFTNGP05.phx.gbl... >> 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 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... - Hide quoted text - >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... >> 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 >> "Yan" <y@rediffmail.com> wrote in message >> news:utfkEfeoHHA.1000@TK2MSFTNGP05.phx.gbl... >>> 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 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 - Hide quoted text - "yan" <yan@rediffmail.com> wrote in message news:etM4exvoHHA.3484@TK2MSFTNGP04.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... >>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... >>> 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 >>> "Yan" <y@rediffmail.com> wrote in message news:utfkEfeoHHA.1000@TK2MSFTNGP05.phx.gbl... >>>> 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 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... - Hide quoted text - >> 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 > "yan" <yan@rediffmail.com> wrote in message > news:etM4exvoHHA.3484@TK2MSFTNGP04.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... >>>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... >>>> 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 >>>> "Yan" <y@rediffmail.com> wrote in message >>>> news:utfkEfeoHHA.1000@TK2MSFTNGP05.phx.gbl... >>>>> 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 > 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 - Hide quoted text - "Yan" <y@rediffmail.com> wrote in message news:%239j$uz4oHHA.588@TK2MSFTNGP06.phx.gbl... > 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... >>> 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 >> "yan" <yan@rediffmail.com> wrote in message news:etM4exvoHHA.3484@TK2MSFTNGP04.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... >>>>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... >>>>> 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 >>>>> "Yan" <y@rediffmail.com> wrote in message news:utfkEfeoHHA.1000@TK2MSFTNGP05.phx.gbl... >>>>>> 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