|
Home > Archive > MS SQL Server > March 2006 > How to prevent frequent reindexing/updating stats?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How to prevent frequent reindexing/updating stats?
|
|
|
| Hi -
In working the performance issues on our database, sometimes we find that
doing a rebuild of the indexes and updating statistics is the only thing we
can do to boost performance. We've trying to performance tune everything ...
but sometimes the only thing that works is the rebuild. We've scheduled the
rebuild to occur every morning.
Yet, sometimes the rebuild/updating stats does not work. Any ideas on really
eliminating the root cause causing the performance degradation? We do
regularly get deadlocks - the cause of which I'm investigating. My DBA
believes that the deadlocks are causing performance degradation - but I
believe it could be the other way around - the slowness of the database
could cause the locks to be held longer - thereby causing the deadlocks. And
I'm trying to figure out why the indexes need to be rebuilt so often ...
Any suggestions as to avenues I could explore? Thanks in advance.
Steph
| |
| Robert Klemme 2006-03-05, 8:24 pm |
| Steph wrote:
> Hi -
>
> In working the performance issues on our database, sometimes we find
> that doing a rebuild of the indexes and updating statistics is the
> only thing we can do to boost performance. We've trying to
> performance tune everything ...
What exactly does that mean? What did you measure? What did you tweak?
> but sometimes the only thing that
> works is the rebuild. We've scheduled the rebuild to occur every
> morning.
>
> Yet, sometimes the rebuild/updating stats does not work. Any ideas on
> really eliminating the root cause causing the performance
> degradation?
Did you investiagate with the Profiler and SQL Server's PerfMon events?
If not, that's the first thing I'd do before considering a solution (index
rebuild).
> We do regularly get deadlocks - the cause of which I'm
> investigating. My DBA believes that the deadlocks are causing
> performance degradation - but I believe it could be the other way
> around - the slowness of the database could cause the locks to be
> held longer - thereby causing the deadlocks. And I'm trying to figure
> out why the indexes need to be rebuilt so often ...
Even if slowness of the engine was increasing the likelyhood of deadlocks,
the fact that they are there strongly indicates that there is an
application flaw. You cannot rely on deadlock prevention by lucky timing
so IMHO you should definitively go after these deadlocks. Sometimes
changing a transaction's isolation level helps but other measures are
preferrable.
Kind regards
robert
|
|
|
|
|