Prev: sys.dm_db_index_operational_stats
Next: Large table + index maintenance = headaches
From: P KLINE on 4 May 2010 15:53
We have a few servers where the backup history (backupset) is a couple
of hundred thousand unindexed rows and would like to trim them down.
What I want to know is if how the sp goes about it's business when the
table is not indexed. The last thing I want to do is tie up msdb for
any length of time while the sp passes through the entire length of
it's tables selecting deletion victimm but I guess thats how it does
what it does.

People here are leary of (afraid of)adding indexes to the table to
assist the sp in it's work but I'm not sure why.

Any light on the subject would be greatly appreciated.
From: Andrew J. Kelly on 4 May 2010 19:29
It will take a while if this is 2005 SP1 or older since there are no indexes
on the tables. However you can as you suggest add some easily enough. Have a
look here:
http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx
But if you don't add them delete only about a week at a time starting from
the oldest week and work your way up to avoid locking the tables for a long
time.


--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"P KLINE" <srphilipkline(a)gmail.com> wrote in message
news:2cu0u516a7qnrmr6is5ig0d8grn7qaq65n(a)4ax.com...
> We have a few servers where the backup history (backupset) is a couple
> of hundred thousand unindexed rows and would like to trim them down.
> What I want to know is if how the sp goes about it's business when the
> table is not indexed. The last thing I want to do is tie up msdb for
> any length of time while the sp passes through the entire length of
> it's tables selecting deletion victimm but I guess thats how it does
> what it does.
>
> People here are leary of (afraid of)adding indexes to the table to
> assist the sp in it's work but I'm not sure why.
>
> Any light on the subject would be greatly appreciated.

 | 
Pages: 1
Prev: sys.dm_db_index_operational_stats
Next: Large table + index maintenance = headaches