Degradation of performance with time

I have OLTP database, 24x5. The database used by application. The application
have new version like every half a year. During deployment of new version,
old database renamed, creating new database with same structure and move
data from old database to new with DTS. After deployment of new version I see
performance improvements for like 2-3 weeks. Next, after 2-3 monthes, it
return back to not so good state. I guess it may be caused by fragmentation?
Every week runs dbcc dbreindex, to defragment database. Database file and
log file are on separate LUNs, each RAID10. Storage device - EMC CX300. No
any other files on same drive where database files are located. Most of
tables have clustered indices, but not all tables have it - our benchmark
tests showed what for some tables better to not have clustered index. SQL2k
SP3.
Why is such performance degradation? What can be done to fix it?
What about statistics, are those kept up to date?
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Hi
Your description seems to follow the sort of pattern associated with
fragmented indexes, but as you rebuild them this should not happen and you
should see an improvement after the rebuild that decays over time. Try using
DBCC SHOWCONTIG to monitor the index fragmentation, you may also want to
update the statistics more often. Check the query plans to see if they differ
when the performance is slower.
You will need to know how your LUNs map to physical discs to determine if
there is any contention, but this should not follow the performance patterns
you have described. Check out physical disc fragmentation and defragment if
necessary.
You may want to also look at the blocker script
http://support.microsoft.com/kb/271509 and monitor performance with perfmon
and compare the results when it is working well against when it isn't.
You don't say if your system databases or tempdb are located on the same
discs, if they are you may want to consider moving them. That may not solve
your problem but it would be good practices.
John
"andsm" wrote:

> I have OLTP database, 24x5. The database used by application. The application
> have new version like every half a year. During deployment of new version,
> old database renamed, creating new database with same structure and move
> data from old database to new with DTS. After deployment of new version I see
> performance improvements for like 2-3 weeks. Next, after 2-3 monthes, it
> return back to not so good state. I guess it may be caused by fragmentation?
> Every week runs dbcc dbreindex, to defragment database. Database file and
> log file are on separate LUNs, each RAID10. Storage device - EMC CX300. No
> any other files on same drive where database files are located. Most of
> tables have clustered indices, but not all tables have it - our benchmark
> tests showed what for some tables better to not have clustered index. SQL2k
> SP3.
> Why is such performance degradation? What can be done to fix it?
|||I'd look into specificallhy what is going slow. Are there specific
tranactions that you run frequently that become slow?
Can you reduce the number of indexes on the biggest tables?