Is their a monitor counter or profiler trace that might tell me when an
index is being restructured during heavy write times?
--
Message posted via http://www.sqlmonster.comWhat do you mean by "restructured"? There are PerfMon counters for page
splits (AccessMethods:PageSplits/sec), is that what you're referring to?
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:b51b69db39ae463fb5aeaab744865dc4@.SQLMonster.com...
> Is their a monitor counter or profiler trace that might tell me when an
> index is being restructured during heavy write times?
> --
> Message posted via http://www.sqlmonster.com|||Yes, I believe so.
I have some application timeouts that occasionally happen and have ruled
out locking, blocking, deadlocks, log growth, data growth, cpu, and memory.
This table is heavy on the writes in comparison to the reads, with a
clustered primary key index with a fillfactor of 90%, and so I suspect the
index might be reaching a point where it is, for lack of a better term
"restructuring" due to running out of free space.
--
Message posted via http://www.sqlmonster.com|||Have you looked at the checkpoints? If you are write intensive when the db
issues a checkpoint it will attempt to write a bunch of dirty pages to disk.
If your drives can not handle the sudden massive requests the other users
will wait in the disk queue until the checkpoint is done. You never
mentioned Disks in your list. Check out the various disk related counters
and the Checkpoint pages per sec in perfmon and see if these correlate tot
he timeouts.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:e0defe3ebbe3418787f6050043594063@.SQLMonster.com...
> Yes, I believe so.
> I have some application timeouts that occasionally happen and have ruled
> out locking, blocking, deadlocks, log growth, data growth, cpu, and
> memory.
> This table is heavy on the writes in comparison to the reads, with a
> clustered primary key index with a fillfactor of 90%, and so I suspect the
> index might be reaching a point where it is, for lack of a better term
> "restructuring" due to running out of free space.
> --
> Message posted via http://www.sqlmonster.com
Showing posts with label monitor. Show all posts
Showing posts with label monitor. Show all posts
Wednesday, March 28, 2012
Index restructuring
Is their a monitor counter or profiler trace that might tell me when an
index is being restructured during heavy write times?
Message posted via http://www.sqlmonster.com
What do you mean by "restructured"? There are PerfMon counters for page
splits (AccessMethods:PageSplits/sec), is that what you're referring to?
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:b51b69db39ae463fb5aeaab744865dc4@.SQLMonster.c om...
> Is their a monitor counter or profiler trace that might tell me when an
> index is being restructured during heavy write times?
> --
> Message posted via http://www.sqlmonster.com
|||Yes, I believe so.
I have some application timeouts that occasionally happen and have ruled
out locking, blocking, deadlocks, log growth, data growth, cpu, and memory.
This table is heavy on the writes in comparison to the reads, with a
clustered primary key index with a fillfactor of 90%, and so I suspect the
index might be reaching a point where it is, for lack of a better term
"restructuring" due to running out of free space.
Message posted via http://www.sqlmonster.com
|||Have you looked at the checkpoints? If you are write intensive when the db
issues a checkpoint it will attempt to write a bunch of dirty pages to disk.
If your drives can not handle the sudden massive requests the other users
will wait in the disk queue until the checkpoint is done. You never
mentioned Disks in your list. Check out the various disk related counters
and the Checkpoint pages per sec in perfmon and see if these correlate tot
he timeouts.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:e0defe3ebbe3418787f6050043594063@.SQLMonster.c om...
> Yes, I believe so.
> I have some application timeouts that occasionally happen and have ruled
> out locking, blocking, deadlocks, log growth, data growth, cpu, and
> memory.
> This table is heavy on the writes in comparison to the reads, with a
> clustered primary key index with a fillfactor of 90%, and so I suspect the
> index might be reaching a point where it is, for lack of a better term
> "restructuring" due to running out of free space.
> --
> Message posted via http://www.sqlmonster.com
index is being restructured during heavy write times?
Message posted via http://www.sqlmonster.com
What do you mean by "restructured"? There are PerfMon counters for page
splits (AccessMethods:PageSplits/sec), is that what you're referring to?
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:b51b69db39ae463fb5aeaab744865dc4@.SQLMonster.c om...
> Is their a monitor counter or profiler trace that might tell me when an
> index is being restructured during heavy write times?
> --
> Message posted via http://www.sqlmonster.com
|||Yes, I believe so.
I have some application timeouts that occasionally happen and have ruled
out locking, blocking, deadlocks, log growth, data growth, cpu, and memory.
This table is heavy on the writes in comparison to the reads, with a
clustered primary key index with a fillfactor of 90%, and so I suspect the
index might be reaching a point where it is, for lack of a better term
"restructuring" due to running out of free space.
Message posted via http://www.sqlmonster.com
|||Have you looked at the checkpoints? If you are write intensive when the db
issues a checkpoint it will attempt to write a bunch of dirty pages to disk.
If your drives can not handle the sudden massive requests the other users
will wait in the disk queue until the checkpoint is done. You never
mentioned Disks in your list. Check out the various disk related counters
and the Checkpoint pages per sec in perfmon and see if these correlate tot
he timeouts.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:e0defe3ebbe3418787f6050043594063@.SQLMonster.c om...
> Yes, I believe so.
> I have some application timeouts that occasionally happen and have ruled
> out locking, blocking, deadlocks, log growth, data growth, cpu, and
> memory.
> This table is heavy on the writes in comparison to the reads, with a
> clustered primary key index with a fillfactor of 90%, and so I suspect the
> index might be reaching a point where it is, for lack of a better term
> "restructuring" due to running out of free space.
> --
> Message posted via http://www.sqlmonster.com
Index restructuring
Is their a monitor counter or profiler trace that might tell me when an
index is being restructured during heavy write times?
Message posted via http://www.droptable.comWhat do you mean by "restructured"? There are PerfMon counters for page
splits (AccessMethods:PageSplits/sec), is that what you're referring to?
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:b51b69db39ae463fb5aeaab744865dc4@.SQ
droptable.com...
> Is their a monitor counter or profiler trace that might tell me when an
> index is being restructured during heavy write times?
> --
> Message posted via http://www.droptable.com|||Yes, I believe so.
I have some application timeouts that occasionally happen and have ruled
out locking, blocking, deadlocks, log growth, data growth, cpu, and memory.
This table is heavy on the writes in comparison to the reads, with a
clustered primary key index with a fillfactor of 90%, and so I suspect the
index might be reaching a point where it is, for lack of a better term
"restructuring" due to running out of free space.
Message posted via http://www.droptable.com|||Have you looked at the checkpoints? If you are write intensive when the db
issues a checkpoint it will attempt to write a bunch of dirty pages to disk.
If your drives can not handle the sudden massive requests the other users
will wait in the disk queue until the checkpoint is done. You never
mentioned Disks in your list. Check out the various disk related counters
and the Checkpoint pages per sec in perfmon and see if these correlate tot
he timeouts.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:e0defe3ebbe3418787f6050043594063@.SQ
droptable.com...
> Yes, I believe so.
> I have some application timeouts that occasionally happen and have ruled
> out locking, blocking, deadlocks, log growth, data growth, cpu, and
> memory.
> This table is heavy on the writes in comparison to the reads, with a
> clustered primary key index with a fillfactor of 90%, and so I suspect the
> index might be reaching a point where it is, for lack of a better term
> "restructuring" due to running out of free space.
> --
> Message posted via http://www.droptable.comsql
index is being restructured during heavy write times?
Message posted via http://www.droptable.comWhat do you mean by "restructured"? There are PerfMon counters for page
splits (AccessMethods:PageSplits/sec), is that what you're referring to?
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:b51b69db39ae463fb5aeaab744865dc4@.SQ
droptable.com...
> Is their a monitor counter or profiler trace that might tell me when an
> index is being restructured during heavy write times?
> --
> Message posted via http://www.droptable.com|||Yes, I believe so.
I have some application timeouts that occasionally happen and have ruled
out locking, blocking, deadlocks, log growth, data growth, cpu, and memory.
This table is heavy on the writes in comparison to the reads, with a
clustered primary key index with a fillfactor of 90%, and so I suspect the
index might be reaching a point where it is, for lack of a better term
"restructuring" due to running out of free space.
Message posted via http://www.droptable.com|||Have you looked at the checkpoints? If you are write intensive when the db
issues a checkpoint it will attempt to write a bunch of dirty pages to disk.
If your drives can not handle the sudden massive requests the other users
will wait in the disk queue until the checkpoint is done. You never
mentioned Disks in your list. Check out the various disk related counters
and the Checkpoint pages per sec in perfmon and see if these correlate tot
he timeouts.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:e0defe3ebbe3418787f6050043594063@.SQ
droptable.com...
> Yes, I believe so.
> I have some application timeouts that occasionally happen and have ruled
> out locking, blocking, deadlocks, log growth, data growth, cpu, and
> memory.
> This table is heavy on the writes in comparison to the reads, with a
> clustered primary key index with a fillfactor of 90%, and so I suspect the
> index might be reaching a point where it is, for lack of a better term
> "restructuring" due to running out of free space.
> --
> Message posted via http://www.droptable.comsql
Friday, March 23, 2012
Index Performance Monitor Counter
I have a SQL Server 2000 database that I have clean up duplicates indexes
and numerous other unneeded indexes.
What are good performance monitor counters to show the database running
better
without these indexes?Joe K. wrote:
> I have a SQL Server 2000 database that I have clean up duplicates
> indexes and numerous other unneeded indexes.
> What are good performance monitor counters to show the database
> running better
> without these indexes?
Record a profiler trace of typical traffic before and after the change.
That way you can also look at ITW's suggestions for indexes to drop /
created.
Kind regards
robertsql
and numerous other unneeded indexes.
What are good performance monitor counters to show the database running
better
without these indexes?Joe K. wrote:
> I have a SQL Server 2000 database that I have clean up duplicates
> indexes and numerous other unneeded indexes.
> What are good performance monitor counters to show the database
> running better
> without these indexes?
Record a profiler trace of typical traffic before and after the change.
That way you can also look at ITW's suggestions for indexes to drop /
created.
Kind regards
robertsql
Index Performance Monitor Counter
I have a SQL Server 2000 database that I have clean up duplicates indexes
and numerous other unneeded indexes.
What are good performance monitor counters to show the database running
better
without these indexes?
Joe K. wrote:
> I have a SQL Server 2000 database that I have clean up duplicates
> indexes and numerous other unneeded indexes.
> What are good performance monitor counters to show the database
> running better
> without these indexes?
Record a profiler trace of typical traffic before and after the change.
That way you can also look at ITW's suggestions for indexes to drop /
created.
Kind regards
robert
and numerous other unneeded indexes.
What are good performance monitor counters to show the database running
better
without these indexes?
Joe K. wrote:
> I have a SQL Server 2000 database that I have clean up duplicates
> indexes and numerous other unneeded indexes.
> What are good performance monitor counters to show the database
> running better
> without these indexes?
Record a profiler trace of typical traffic before and after the change.
That way you can also look at ITW's suggestions for indexes to drop /
created.
Kind regards
robert
Wednesday, March 21, 2012
Index Performance Monitor Counter
I have a SQL Server 2000 database that I have clean up duplicates indexes
and numerous other unneeded indexes.
What are good performance monitor counters to show the database running
better
without these indexes?Joe K. wrote:
> I have a SQL Server 2000 database that I have clean up duplicates
> indexes and numerous other unneeded indexes.
> What are good performance monitor counters to show the database
> running better
> without these indexes?
Record a profiler trace of typical traffic before and after the change.
That way you can also look at ITW's suggestions for indexes to drop /
created.
Kind regards
robert
and numerous other unneeded indexes.
What are good performance monitor counters to show the database running
better
without these indexes?Joe K. wrote:
> I have a SQL Server 2000 database that I have clean up duplicates
> indexes and numerous other unneeded indexes.
> What are good performance monitor counters to show the database
> running better
> without these indexes?
Record a profiler trace of typical traffic before and after the change.
That way you can also look at ITW's suggestions for indexes to drop /
created.
Kind regards
robert
Monday, March 19, 2012
Index Monitoring
I monitor the index fragmentation through dbcc showcontig command , i get result as
Pages Scanned
Extents Scanned
Extent Switches
Avg. Pages per Extent
Scan Density [Best Count:Actual Count]
Logical Scan Fragmentation
Extent Scan Fragmentation
Avg. Bytes Free per Page
Avg. Page Density (full).
Pages Scanned
Extents Scanned
Extent Switches
Avg. Pages per Extent
Scan Density [Best Count:Actual Count]
Logical Scan Fragmentation
Extent Scan Fragmentation
Avg. Bytes Free per Page
Avg. Page Density (full).
But i want to know can we get value of 'extent switches' and logical scan fragmentation' through system tables , because we know that all database details are updated in system tables.As you have posted a question in the article section it ts being moved to SQL Server Forum.
Subscribe to:
Posts (Atom)