Fragmentation with large varchar column

Hi all,
I have a interesting situation with a table with a nvarchar column that
contains a xml string.
The average size of this field is about 2100 bytes. This means that I will
have few records per page. The primary key (with a clustered index) is in a
field not sequential.
In this way almost in all inserts I will have a page split and the
fragmentation grows dramatically.
Of course I tried to change the clustered index to a sequential field (a
datetime with a getdate() as default).
This avoid the fragmentation, but in my case causes many deadlocks (the
application has a high level of concurrency), because the I force all inserts
to be in the last page.
We want to solve this problem with a minimum impact to the application and
to avoid that the high speed of fragmentation (the system is 24X7 and the
rebuild index has a high cost).
One idea is to change the datatype of this field to text.
In my opinion this would get better the fragmentation question, but I don't
know if the reading cost of this field would be problematic. This field is
always inserted (not updated) and is read totaly (without like or comparison
clauses).
Could you give some sugesstions or tips?
Thank you very much
Alexandre Calderaro
MSDBA
Avanade Italy> Of course I tried to change the clustered index to a sequential field (a
> datetime with a getdate() as default).
> This avoid the fragmentation, but in my case causes many deadlocks (the
> application has a high level of concurrency), because the I force all
> inserts
> to be in the last page.
The deadlocks are probably not due to the hotspot at the end of the table.
It may be that the indexing change introduced scans and this increased
deadlock likelihood. Did you recreate the primary key as non-clustered?
Fragmentation is only one piece of the performance puzzle. A clustered
index on an increasing value like datetime or IDENTITY is good for insert
performance and may also be good for scans/joins on the clustered key.
However, you need to consider the overall mix of queries to determine the
best indexing strategy, especially in a highly transactional environment.
It may be that the PK is the best choice for the clustered index, even at
the cost of fragmentation.
BTW, you can use DBCC INDEXDEFRAG to defragment in a 24x7 environment. For
SQL 2000, there is a post-SP4 hotfix to address INDEXDEFRAG locking issues.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:22D85878-C15E-4F79-9D17-41A72CE609B3@.microsoft.com...
> Hi all,
> I have a interesting situation with a table with a nvarchar column that
> contains a xml string.
> The average size of this field is about 2100 bytes. This means that I will
> have few records per page. The primary key (with a clustered index) is in
> a
> field not sequential.
> In this way almost in all inserts I will have a page split and the
> fragmentation grows dramatically.
> Of course I tried to change the clustered index to a sequential field (a
> datetime with a getdate() as default).
> This avoid the fragmentation, but in my case causes many deadlocks (the
> application has a high level of concurrency), because the I force all
> inserts
> to be in the last page.
> We want to solve this problem with a minimum impact to the application and
> to avoid that the high speed of fragmentation (the system is 24X7 and the
> rebuild index has a high cost).
> One idea is to change the datatype of this field to text.
> In my opinion this would get better the fragmentation question, but I
> don't
> know if the reading cost of this field would be problematic. This field is
> always inserted (not updated) and is read totaly (without like or
> comparison
> clauses).
> Could you give some sugesstions or tips?
> Thank you very much
> Alexandre Calderaro
> MSDBA
> Avanade Italy
>|||Thanks Dan,
I recreated the PK to a non-clustered index before change the clustered
index to a sequential field.
I was almost secure that the deadlock problem could be the hostspot, because
I have few rows per page. And that's the reason that I thoght about to change
the datatype of the varchar field to text. Do you think that this could be
helpful?
We use IndexDefrag, but we have more than one file and this operation
doesn't migrates data between files. A consideration would be to have just
one file.
I have to control if our client installed this fix that you have mentioned.
Thanks again!
Alexandre
"Dan Guzman" wrote:
> > Of course I tried to change the clustered index to a sequential field (a
> > datetime with a getdate() as default).
> > This avoid the fragmentation, but in my case causes many deadlocks (the
> > application has a high level of concurrency), because the I force all
> > inserts
> > to be in the last page.
> The deadlocks are probably not due to the hotspot at the end of the table.
> It may be that the indexing change introduced scans and this increased
> deadlock likelihood. Did you recreate the primary key as non-clustered?
> Fragmentation is only one piece of the performance puzzle. A clustered
> index on an increasing value like datetime or IDENTITY is good for insert
> performance and may also be good for scans/joins on the clustered key.
> However, you need to consider the overall mix of queries to determine the
> best indexing strategy, especially in a highly transactional environment.
> It may be that the PK is the best choice for the clustered index, even at
> the cost of fragmentation.
> BTW, you can use DBCC INDEXDEFRAG to defragment in a 24x7 environment. For
> SQL 2000, there is a post-SP4 hotfix to address INDEXDEFRAG locking issues.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alex" <Alex@.discussions.microsoft.com> wrote in message
> news:22D85878-C15E-4F79-9D17-41A72CE609B3@.microsoft.com...
> > Hi all,
> >
> > I have a interesting situation with a table with a nvarchar column that
> > contains a xml string.
> > The average size of this field is about 2100 bytes. This means that I will
> > have few records per page. The primary key (with a clustered index) is in
> > a
> > field not sequential.
> > In this way almost in all inserts I will have a page split and the
> > fragmentation grows dramatically.
> > Of course I tried to change the clustered index to a sequential field (a
> > datetime with a getdate() as default).
> > This avoid the fragmentation, but in my case causes many deadlocks (the
> > application has a high level of concurrency), because the I force all
> > inserts
> > to be in the last page.
> > We want to solve this problem with a minimum impact to the application and
> > to avoid that the high speed of fragmentation (the system is 24X7 and the
> > rebuild index has a high cost).
> > One idea is to change the datatype of this field to text.
> > In my opinion this would get better the fragmentation question, but I
> > don't
> > know if the reading cost of this field would be problematic. This field is
> > always inserted (not updated) and is read totaly (without like or
> > comparison
> > clauses).
> > Could you give some sugesstions or tips?
> > Thank you very much
> >
> > Alexandre Calderaro
> > MSDBA
> > Avanade Italy
> >
>
>|||Alex,
Have you identified the objects/processes involved in the deadlocks?
May be the deadlocks are related to the way you are accessing the tables.
INF: Analyzing and Avoiding Deadlocks in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;169960
Tracing Deadlocks
http://www.sqlservercentral.com/columnists/skumar/tracingdeadlocks.asp
AMB
"Alex" wrote:
> Thanks Dan,
> I recreated the PK to a non-clustered index before change the clustered
> index to a sequential field.
> I was almost secure that the deadlock problem could be the hostspot, because
> I have few rows per page. And that's the reason that I thoght about to change
> the datatype of the varchar field to text. Do you think that this could be
> helpful?
> We use IndexDefrag, but we have more than one file and this operation
> doesn't migrates data between files. A consideration would be to have just
> one file.
> I have to control if our client installed this fix that you have mentioned.
> Thanks again!
> Alexandre
> "Dan Guzman" wrote:
> > > Of course I tried to change the clustered index to a sequential field (a
> > > datetime with a getdate() as default).
> > > This avoid the fragmentation, but in my case causes many deadlocks (the
> > > application has a high level of concurrency), because the I force all
> > > inserts
> > > to be in the last page.
> >
> > The deadlocks are probably not due to the hotspot at the end of the table.
> > It may be that the indexing change introduced scans and this increased
> > deadlock likelihood. Did you recreate the primary key as non-clustered?
> >
> > Fragmentation is only one piece of the performance puzzle. A clustered
> > index on an increasing value like datetime or IDENTITY is good for insert
> > performance and may also be good for scans/joins on the clustered key.
> > However, you need to consider the overall mix of queries to determine the
> > best indexing strategy, especially in a highly transactional environment.
> > It may be that the PK is the best choice for the clustered index, even at
> > the cost of fragmentation.
> >
> > BTW, you can use DBCC INDEXDEFRAG to defragment in a 24x7 environment. For
> > SQL 2000, there is a post-SP4 hotfix to address INDEXDEFRAG locking issues.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Alex" <Alex@.discussions.microsoft.com> wrote in message
> > news:22D85878-C15E-4F79-9D17-41A72CE609B3@.microsoft.com...
> > > Hi all,
> > >
> > > I have a interesting situation with a table with a nvarchar column that
> > > contains a xml string.
> > > The average size of this field is about 2100 bytes. This means that I will
> > > have few records per page. The primary key (with a clustered index) is in
> > > a
> > > field not sequential.
> > > In this way almost in all inserts I will have a page split and the
> > > fragmentation grows dramatically.
> > > Of course I tried to change the clustered index to a sequential field (a
> > > datetime with a getdate() as default).
> > > This avoid the fragmentation, but in my case causes many deadlocks (the
> > > application has a high level of concurrency), because the I force all
> > > inserts
> > > to be in the last page.
> > > We want to solve this problem with a minimum impact to the application and
> > > to avoid that the high speed of fragmentation (the system is 24X7 and the
> > > rebuild index has a high cost).
> > > One idea is to change the datatype of this field to text.
> > > In my opinion this would get better the fragmentation question, but I
> > > don't
> > > know if the reading cost of this field would be problematic. This field is
> > > always inserted (not updated) and is read totaly (without like or
> > > comparison
> > > clauses).
> > > Could you give some sugesstions or tips?
> > > Thank you very much
> > >
> > > Alexandre Calderaro
> > > MSDBA
> > > Avanade Italy
> > >
> >
> >
> >|||The links Alejandro posted can help identify the problem queries and
deadlocking resource. Take a look at the execution plans of the queries
involved in the deadlock as this might help identify the reason for the
resource contention.
Changing varchar to text (or nvarchar to ntext) will certainly improve
density. However, queries that reference the column will incur an
additional i/o. It depends on your workload mix whether or not this is the
right thing to do.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:FD074D4F-879A-4BDD-892C-F7BB455A97C3@.microsoft.com...
> Thanks Dan,
> I recreated the PK to a non-clustered index before change the clustered
> index to a sequential field.
> I was almost secure that the deadlock problem could be the hostspot,
> because
> I have few rows per page. And that's the reason that I thoght about to
> change
> the datatype of the varchar field to text. Do you think that this could be
> helpful?
> We use IndexDefrag, but we have more than one file and this operation
> doesn't migrates data between files. A consideration would be to have just
> one file.
> I have to control if our client installed this fix that you have
> mentioned.
> Thanks again!
> Alexandre
> "Dan Guzman" wrote:
>> > Of course I tried to change the clustered index to a sequential field
>> > (a
>> > datetime with a getdate() as default).
>> > This avoid the fragmentation, but in my case causes many deadlocks (the
>> > application has a high level of concurrency), because the I force all
>> > inserts
>> > to be in the last page.
>> The deadlocks are probably not due to the hotspot at the end of the
>> table.
>> It may be that the indexing change introduced scans and this increased
>> deadlock likelihood. Did you recreate the primary key as non-clustered?
>> Fragmentation is only one piece of the performance puzzle. A clustered
>> index on an increasing value like datetime or IDENTITY is good for insert
>> performance and may also be good for scans/joins on the clustered key.
>> However, you need to consider the overall mix of queries to determine the
>> best indexing strategy, especially in a highly transactional environment.
>> It may be that the PK is the best choice for the clustered index, even at
>> the cost of fragmentation.
>> BTW, you can use DBCC INDEXDEFRAG to defragment in a 24x7 environment.
>> For
>> SQL 2000, there is a post-SP4 hotfix to address INDEXDEFRAG locking
>> issues.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Alex" <Alex@.discussions.microsoft.com> wrote in message
>> news:22D85878-C15E-4F79-9D17-41A72CE609B3@.microsoft.com...
>> > Hi all,
>> >
>> > I have a interesting situation with a table with a nvarchar column that
>> > contains a xml string.
>> > The average size of this field is about 2100 bytes. This means that I
>> > will
>> > have few records per page. The primary key (with a clustered index) is
>> > in
>> > a
>> > field not sequential.
>> > In this way almost in all inserts I will have a page split and the
>> > fragmentation grows dramatically.
>> > Of course I tried to change the clustered index to a sequential field
>> > (a
>> > datetime with a getdate() as default).
>> > This avoid the fragmentation, but in my case causes many deadlocks (the
>> > application has a high level of concurrency), because the I force all
>> > inserts
>> > to be in the last page.
>> > We want to solve this problem with a minimum impact to the application
>> > and
>> > to avoid that the high speed of fragmentation (the system is 24X7 and
>> > the
>> > rebuild index has a high cost).
>> > One idea is to change the datatype of this field to text.
>> > In my opinion this would get better the fragmentation question, but I
>> > don't
>> > know if the reading cost of this field would be problematic. This field
>> > is
>> > always inserted (not updated) and is read totaly (without like or
>> > comparison
>> > clauses).
>> > Could you give some sugesstions or tips?
>> > Thank you very much
>> >
>> > Alexandre Calderaro
>> > MSDBA
>> > Avanade Italy
>> >
>>