Poor performance and statistics  
Author Message
Moset





PostPosted: Mon Feb 05 18:21:00 CST 2007 Top

SQL Server Developer >> Poor performance and statistics

I have a process that runs throughout the day. For the past month we have
been fighting performance issues with this process. It normally runs for
30-45 seconds but then all of a sudden it will start taking 5 minutes to
complete. The server is idle with no queue waits. One I run Update
Statistics With FULLSCAN everything returns back to the normal 30-45 second
runtime. I run this command for all tables in the database on a nightly
basis. I have also been running a SQL Profiler trace and capturing all Auto
Stats events. The results show that none of the tables/indexes that are used
by this process never have auto statistics generated for them so they should
still use the same statistics that were created during the nightly run for
the query plan.

My question is what else, if anything, other than statistics could cause a
different query plan to be used? What am I missing in trying to figure out
what is causing this problem?

Thanks in advance for your help.

SQL Server126  
 
 
MikeMiller





PostPosted: Mon Feb 05 18:21:00 CST 2007 Top

SQL Server Developer >> Poor performance and statistics Does the size of the table change dramatically throughout the day? For
example, does each run of the sp add rows that are cleaned up at the end of
the day? If so, the query plan will be based on the statistics as they were
when update statistics was run.

That's really the only thing I can think of without looking at the query and
the table DDL.




> I have a process that runs throughout the day. For the past month we have
> been fighting performance issues with this process. It normally runs for
> 30-45 seconds but then all of a sudden it will start taking 5 minutes to
> complete. The server is idle with no queue waits. One I run Update
> Statistics With FULLSCAN everything returns back to the normal 30-45 second
> runtime. I run this command for all tables in the database on a nightly
> basis. I have also been running a SQL Profiler trace and capturing all Auto
> Stats events. The results show that none of the tables/indexes that are used
> by this process never have auto statistics generated for them so they should
> still use the same statistics that were created during the nightly run for
> the query plan.
>
> My question is what else, if anything, other than statistics could cause a
> different query plan to be used? What am I missing in trying to figure out
> what is causing this problem?
>
> Thanks in advance for your help.
 
 
Andrew





PostPosted: Mon Feb 05 18:59:43 CST 2007 Top

SQL Server Developer >> Poor performance and statistics Sounds like a victim of parameter sniffing. See: http://tinyurl.com/l59s

My guess is there is something other than stats changing that force this
plan to be recompiled (or compiled if the plan is pushed out of the proc
cache) at times throughout the day. You probably have some values that are
atypical but get used periodically when calling this process that lend
better to a scan. But then when you run it the next time and a seek is the
better choice it will use the scan plan instead or visa versa. I don't know
if you are using 2000 or 2005 but here are some links that you should have a
look at.

http://support.microsoft.com/kb/243588

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
--
Andrew J. Kelly SQL MVP



>I have a process that runs throughout the day. For the past month we have
> been fighting performance issues with this process. It normally runs for
> 30-45 seconds but then all of a sudden it will start taking 5 minutes to
> complete. The server is idle with no queue waits. One I run Update
> Statistics With FULLSCAN everything returns back to the normal 30-45
> second
> runtime. I run this command for all tables in the database on a nightly
> basis. I have also been running a SQL Profiler trace and capturing all
> Auto
> Stats events. The results show that none of the tables/indexes that are
> used
> by this process never have auto statistics generated for them so they
> should
> still use the same statistics that were created during the nightly run for
> the query plan.
>
> My question is what else, if anything, other than statistics could cause a
> different query plan to be used? What am I missing in trying to figure
> out
> what is causing this problem?
>
> Thanks in advance for your help.


 
 
Corey





PostPosted: Wed Feb 07 08:38:00 CST 2007 Top

SQL Server Developer >> Poor performance and statistics Thanks for the input. This is very likely what is occurring. I will review
the links that you provided and work on the problem further.

Thanks again.

Corey



> Sounds like a victim of parameter sniffing. See: http://tinyurl.com/l59s
>
> My guess is there is something other than stats changing that force this
> plan to be recompiled (or compiled if the plan is pushed out of the proc
> cache) at times throughout the day. You probably have some values that are
> atypical but get used periodically when calling this process that lend
> better to a scan. But then when you run it the next time and a seek is the
> better choice it will use the scan plan instead or visa versa. I don't know
> if you are using 2000 or 2005 but here are some links that you should have a
> look at.
>
> http://support.microsoft.com/kb/243588
>
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> --
> Andrew J. Kelly SQL MVP
>


> >I have a process that runs throughout the day. For the past month we have
> > been fighting performance issues with this process. It normally runs for
> > 30-45 seconds but then all of a sudden it will start taking 5 minutes to
> > complete. The server is idle with no queue waits. One I run Update
> > Statistics With FULLSCAN everything returns back to the normal 30-45
> > second
> > runtime. I run this command for all tables in the database on a nightly
> > basis. I have also been running a SQL Profiler trace and capturing all
> > Auto
> > Stats events. The results show that none of the tables/indexes that are
> > used
> > by this process never have auto statistics generated for them so they
> > should
> > still use the same statistics that were created during the nightly run for
> > the query plan.
> >
> > My question is what else, if anything, other than statistics could cause a
> > different query plan to be used? What am I missing in trying to figure
> > out
> > what is causing this problem?
> >
> > Thanks in advance for your help.
>
>
>