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 write. Show all posts
Showing posts with label write. 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 preference in Write intensive envt.
Hi,
Please let me know the index that needs to be created in write intensive
environments and why?
Thanks in advance
Manu
Manu,
For maximum performance, a unique clustered index (commonly a primary key)
on an incrementing value (commonly idenitity) is probably your best bet.
Eliminate any other indexes on the write intensive tables.
When a clustered index is used along with an incrementing value, the
organization of data pages is highly efficient as new records are inserted.
Fragmentation is minimized. Non-clustered indexes on the same data will
result in fragmentation requiring re-indexing to optimize performance.
From there, you can examine partitioning.
-- Bill
"manu" <manu@.discussions.microsoft.com> wrote in message
news:88E41056-2044-4DB0-BE5F-9892879E9CC6@.microsoft.com...
> Hi,
> Please let me know the index that needs to be created in write intensive
> environments and why?
> Thanks in advance
> Manu
|||On Mon, 8 Jan 2007 14:43:00 -0800, manu
<manu@.discussions.microsoft.com> wrote:
>Please let me know the index that needs to be created in write intensive
>environments and why?
When you say write-intensive, do you mean Insert or Update or both?
Answers can be quite different, depending.
J.
|||Actually it is more based on inserting new records. It would be better if u
can recommend for both the situations:-
1. Insert only
2. Insert and Update both
Thx. for ur reply..
Manu
"JXStern" wrote:
> On Mon, 8 Jan 2007 14:43:00 -0800, manu
> <manu@.discussions.microsoft.com> wrote:
>
> When you say write-intensive, do you mean Insert or Update or both?
> Answers can be quite different, depending.
> J.
>
Please let me know the index that needs to be created in write intensive
environments and why?
Thanks in advance
Manu
Manu,
For maximum performance, a unique clustered index (commonly a primary key)
on an incrementing value (commonly idenitity) is probably your best bet.
Eliminate any other indexes on the write intensive tables.
When a clustered index is used along with an incrementing value, the
organization of data pages is highly efficient as new records are inserted.
Fragmentation is minimized. Non-clustered indexes on the same data will
result in fragmentation requiring re-indexing to optimize performance.
From there, you can examine partitioning.
-- Bill
"manu" <manu@.discussions.microsoft.com> wrote in message
news:88E41056-2044-4DB0-BE5F-9892879E9CC6@.microsoft.com...
> Hi,
> Please let me know the index that needs to be created in write intensive
> environments and why?
> Thanks in advance
> Manu
|||On Mon, 8 Jan 2007 14:43:00 -0800, manu
<manu@.discussions.microsoft.com> wrote:
>Please let me know the index that needs to be created in write intensive
>environments and why?
When you say write-intensive, do you mean Insert or Update or both?
Answers can be quite different, depending.
J.
|||Actually it is more based on inserting new records. It would be better if u
can recommend for both the situations:-
1. Insert only
2. Insert and Update both
Thx. for ur reply..
Manu
"JXStern" wrote:
> On Mon, 8 Jan 2007 14:43:00 -0800, manu
> <manu@.discussions.microsoft.com> wrote:
>
> When you say write-intensive, do you mean Insert or Update or both?
> Answers can be quite different, depending.
> J.
>
Index preference in Write intensive envt.
Hi,
Please let me know the index that needs to be created in write intensive
environments and why?
Thanks in advance
ManuManu,
For maximum performance, a unique clustered index (commonly a primary key)
on an incrementing value (commonly idenitity) is probably your best bet.
Eliminate any other indexes on the write intensive tables.
When a clustered index is used along with an incrementing value, the
organization of data pages is highly efficient as new records are inserted.
Fragmentation is minimized. Non-clustered indexes on the same data will
result in fragmentation requiring re-indexing to optimize performance.
From there, you can examine partitioning.
-- Bill
"manu" <manu@.discussions.microsoft.com> wrote in message
news:88E41056-2044-4DB0-BE5F-9892879E9CC6@.microsoft.com...
> Hi,
> Please let me know the index that needs to be created in write intensive
> environments and why?
> Thanks in advance
> Manu|||On Mon, 8 Jan 2007 14:43:00 -0800, manu
<manu@.discussions.microsoft.com> wrote:
>Please let me know the index that needs to be created in write intensive
>environments and why?
When you say write-intensive, do you mean Insert or Update or both?
Answers can be quite different, depending.
J.|||Actually it is more based on inserting new records. It would be better if u
can recommend for both the situations:-
1. Insert only
2. Insert and Update both
Thx. for ur reply..
Manu
"JXStern" wrote:
> On Mon, 8 Jan 2007 14:43:00 -0800, manu
> <manu@.discussions.microsoft.com> wrote:
>
> When you say write-intensive, do you mean Insert or Update or both?
> Answers can be quite different, depending.
> J.
>
Please let me know the index that needs to be created in write intensive
environments and why?
Thanks in advance
ManuManu,
For maximum performance, a unique clustered index (commonly a primary key)
on an incrementing value (commonly idenitity) is probably your best bet.
Eliminate any other indexes on the write intensive tables.
When a clustered index is used along with an incrementing value, the
organization of data pages is highly efficient as new records are inserted.
Fragmentation is minimized. Non-clustered indexes on the same data will
result in fragmentation requiring re-indexing to optimize performance.
From there, you can examine partitioning.
-- Bill
"manu" <manu@.discussions.microsoft.com> wrote in message
news:88E41056-2044-4DB0-BE5F-9892879E9CC6@.microsoft.com...
> Hi,
> Please let me know the index that needs to be created in write intensive
> environments and why?
> Thanks in advance
> Manu|||On Mon, 8 Jan 2007 14:43:00 -0800, manu
<manu@.discussions.microsoft.com> wrote:
>Please let me know the index that needs to be created in write intensive
>environments and why?
When you say write-intensive, do you mean Insert or Update or both?
Answers can be quite different, depending.
J.|||Actually it is more based on inserting new records. It would be better if u
can recommend for both the situations:-
1. Insert only
2. Insert and Update both
Thx. for ur reply..
Manu
"JXStern" wrote:
> On Mon, 8 Jan 2007 14:43:00 -0800, manu
> <manu@.discussions.microsoft.com> wrote:
>
> When you say write-intensive, do you mean Insert or Update or both?
> Answers can be quite different, depending.
> J.
>
Sunday, February 19, 2012
Index , Database size
Hi all
I need to write a script or simple SQL to run periodically
say every day midnight.
In this,
1) I need to check whether any index in the Database has
been corrupted or need to re-create the index.
2) Provides daily occupancy (in percentage) of [Data +
Log ] file.
Can anyone hep me to give me a poiter to web site or SQL
script to aciehve this.
Thanks ina advance
AnandOne possibility is http://www.dbmaint.com/.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Anand" <gurusanand1@.sifymail.com> wrote in message
news:06e601c35af3$6fe0ebb0$a401280a@.phx.gbl...
> Hi all
> I need to write a script or simple SQL to run periodically
> say every day midnight.
> In this,
> 1) I need to check whether any index in the Database has
> been corrupted or need to re-create the index.
> 2) Provides daily occupancy (in percentage) of [Data +
> Log ] file.
> Can anyone hep me to give me a poiter to web site or SQL
> script to aciehve this.
> Thanks ina advance
> Anand
I need to write a script or simple SQL to run periodically
say every day midnight.
In this,
1) I need to check whether any index in the Database has
been corrupted or need to re-create the index.
2) Provides daily occupancy (in percentage) of [Data +
Log ] file.
Can anyone hep me to give me a poiter to web site or SQL
script to aciehve this.
Thanks ina advance
AnandOne possibility is http://www.dbmaint.com/.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Anand" <gurusanand1@.sifymail.com> wrote in message
news:06e601c35af3$6fe0ebb0$a401280a@.phx.gbl...
> Hi all
> I need to write a script or simple SQL to run periodically
> say every day midnight.
> In this,
> 1) I need to check whether any index in the Database has
> been corrupted or need to re-create the index.
> 2) Provides daily occupancy (in percentage) of [Data +
> Log ] file.
> Can anyone hep me to give me a poiter to web site or SQL
> script to aciehve this.
> Thanks ina advance
> Anand
Subscribe to:
Posts (Atom)