Prev: Taking database offline
Next: Clear connection history Answer
From: MichaelH on 15 Dec 2009 08:40
We have a procedure that collects fragmentation data before re-
indexing a production database.

On two separate occasions, when executing
'sys.dm_db_index_physical_stats', users have reported timeout issues.

The mode is 'LIMITED'

Thanks in advance.
From: TheSQLGuru on 15 Dec 2009 13:08
You need to do more investigation.

1) was there blocking that caused the timeouts?

2) do a fileIO and waitstats analysis during execution (and when it is NOT
executing for comparison) to see where the actual problem is if it is not
blocking.

3) do you get timeouts when this is not running?


--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"MichaelH" <bmiguelh(a)gmail.com> wrote in message
news:123da4c4-3cb1-474d-bd25-8cfb113e7d40(a)r24g2000yqd.googlegroups.com...
> We have a procedure that collects fragmentation data before re-
> indexing a production database.
>
> On two separate occasions, when executing
> 'sys.dm_db_index_physical_stats', users have reported timeout issues.
>
> The mode is 'LIMITED'
>
> Thanks in advance.


From: Andrew J. Kelly on 15 Dec 2009 16:09
Most likely this is due to a poor I/O subsystem configuration since this can
do a lot of physical I/O on larger tables or indexes even in Limited mode.
But you need to watch your perfmon counters and things like file & wait
stats to see what is holding things up.

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"MichaelH" <bmiguelh(a)gmail.com> wrote in message
news:123da4c4-3cb1-474d-bd25-8cfb113e7d40(a)r24g2000yqd.googlegroups.com...
> We have a procedure that collects fragmentation data before re-
> indexing a production database.
>
> On two separate occasions, when executing
> 'sys.dm_db_index_physical_stats', users have reported timeout issues.
>
> The mode is 'LIMITED'
>
> Thanks in advance.

From: Uri Dimant on 16 Dec 2009 04:20
Hi
As alternative see Ola's blog
http://ola.hallengren.com/




"MichaelH" <bmiguelh(a)gmail.com> wrote in message
news:123da4c4-3cb1-474d-bd25-8cfb113e7d40(a)r24g2000yqd.googlegroups.com...
> We have a procedure that collects fragmentation data before re-
> indexing a production database.
>
> On two separate occasions, when executing
> 'sys.dm_db_index_physical_stats', users have reported timeout issues.
>
> The mode is 'LIMITED'
>
> Thanks in advance.


 | 
Pages: 1
Prev: Taking database offline
Next: Clear connection history Answer