Performance and sys.dm_db_index_physical_stats
in [Server Configuration]
|
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 |