dbcc freeproccache only solution??

Recently upgranded to SQL2005 sp1
Daily we have times of very poor performance. We tracked down to full table
scans occuring, indexes not used.
Perform a dbcc freeproccache to solve. Any idea why this is occurring?
Happening more often since upgrade - did notice same issue when on sql2000...
Any ideas?
tia
chris
On Fri, 11 May 2007 15:55:00 -0700, Chris
<Chris@.discussions.microsoft.com> wrote:

>Recently upgranded to SQL2005 sp1
>Daily we have times of very poor performance. We tracked down to full table
>scans occuring, indexes not used.
>Perform a dbcc freeproccache to solve. Any idea why this is occurring?
>Happening more often since upgrade - did notice same issue when on sql2000...
>Any ideas?
Many ideas.
But they all start with isolating the problem code, the DDL, and the
stats involved.
J.
|||Sounds like parameter sniffing issues. Did you update the stats after the
upgrade? Have a look at these:
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E65671C4-696D-44BC-AB87-BD3103AB5298@.microsoft.com...
> Recently upgranded to SQL2005 sp1
> Daily we have times of very poor performance. We tracked down to full
> table
> scans occuring, indexes not used.
> Perform a dbcc freeproccache to solve. Any idea why this is occurring?
> Happening more often since upgrade - did notice same issue when on
> sql2000...
> Any ideas?
> tia
> chris
|||"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E65671C4-696D-44BC-AB87-BD3103AB5298@.microsoft.com...
> Recently upgranded to SQL2005 sp1
> Daily we have times of very poor performance. We tracked down to full
> table
> scans occuring, indexes not used.
> Perform a dbcc freeproccache to solve. Any idea why this is occurring?
> Happening more often since upgrade - did notice same issue when on
> sql2000...
> Any ideas?
> tia
> chris
My guess is that most likely your stored proc query plans are becoming
invalid.
A simple test would be to find one called a lot and do a recompile on it and
see if the performance gets better.
If it does, there's a few things to loook at.
Are you doing a lot of updates/inserts/deletes that invalidate the stats for
your indices? If so, you can update stats more often (among other things).
Also lok for stuff like a "IF @.FOO then X else Y" Typically the first time
the procedure is called, a particular path is followed (say @.FOO Is true,
then X is followed.) This is the query plan that will be cashed. If later
the call has @.FOO is false and Y is followed the query plan will be invalid.
So that's something else to look at.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html