FTI, Searching and other Filters

We have this table...
CREATE TABLE [dbo].[Document](
[DocumentID] [int] IDENTITY(1,1) NOT NULL,
[HumanResourceID] [int] NOT NULL,
[Name] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[ContentType] [nchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Content] [image] NOT NULL,
[DateEntered] [datetime] NOT NULL,
[DateModified] [datetime] NULL,
[Version] [timestamp] NOT NULL,
[EmployeeID] [int] NULL,
CONSTRAINT [Resume_PK] PRIMARY KEY CLUSTERED
(
[ResumeID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Content contains the bits that make up either Word or RTF documents.
We have FTI defined on Content / ContentType / DocumentID. Generally FT
searches are working.
The table contains over 130k documents.
Through our application we are limiting their searches to the top 1500 rank
of any FT search. (So as not to over-burden our server.)
This works when the want to search the table for documents within the entire
company.
But what they would really like is the top 1500 rank for documents within
their office.
Is there a way to partition the the table by an OfficeID with some what to
pre-filter so the FT search is only looking at Documents from one or more
OfficeIDs?
TIA - Kyle!
I think I found my answer...although a lot of work.
Remove the FTI from the table.
Add an OfficeID column to the table and populate it.
Partition the table by the OfficeID column.
Create an Indexed View for each OfficeID
(Open a new office, the add a new OfficeID and a new Indexed View for that
OfficeID.)
(Close an existing Office, migrate the documents to a different office, drop
the FTI for that View and drop that View)
Add a FTI to each of the Indexed Views.
Mod the application so it knows what how to FT search one or more Indexed
Views and combine the results from multiple views if needed.
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:OitVbpuHHHA.1468@.TK2MSFTNGP04.phx.gbl...
> We have this table...
> CREATE TABLE [dbo].[Document](
> [DocumentID] [int] IDENTITY(1,1) NOT NULL,
> [HumanResourceID] [int] NOT NULL,
> [Name] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [Description] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> [ContentType] [nchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [Content] [image] NOT NULL,
> [DateEntered] [datetime] NOT NULL,
> [DateModified] [datetime] NULL,
> [Version] [timestamp] NOT NULL,
> [EmployeeID] [int] NULL,
> CONSTRAINT [Resume_PK] PRIMARY KEY CLUSTERED
> (
> [ResumeID] ASC
> )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON
> [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> Content contains the bits that make up either Word or RTF documents.
> We have FTI defined on Content / ContentType / DocumentID. Generally FT
> searches are working.
> The table contains over 130k documents.
> Through our application we are limiting their searches to the top 1500
> rank of any FT search. (So as not to over-burden our server.)
> This works when the want to search the table for documents within the
> entire company.
> But what they would really like is the top 1500 rank for documents within
> their office.
> Is there a way to partition the the table by an OfficeID with some what to
> pre-filter so the FT search is only looking at Documents from one or more
> OfficeIDs?
> TIA - Kyle!
>
|||Hello Kyle,
The other option is to add the office ID to the content (If the content was
editable i.e. text/html)
Then use a query like containstable(document, content,'OFFICE2345 AND "SQL
SERVER DBA"')
If the content is editabel this is by far the more manageable, and scalable.
We did the index view thing and it is just not a neat solution. The token
thing is much easier.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> I think I found my answer...although a lot of work.
> Remove the FTI from the table.
> Add an OfficeID column to the table and populate it.
> Partition the table by the OfficeID column.
> Create an Indexed View for each OfficeID
> (Open a new office, the add a new OfficeID and a new Indexed View for
> that
> OfficeID.)
> (Close an existing Office, migrate the documents to a different
> office, drop
> the FTI for that View and drop that View)
> Add a FTI to each of the Indexed Views.
> Mod the application so it knows what how to FT search one or more
> Indexed Views and combine the results from multiple views if needed.
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in
> message news:OitVbpuHHHA.1468@.TK2MSFTNGP04.phx.gbl...
|||Um, maybe I'm missing something here, but since you are using the rank
I'm assuming you are using CONTAINSTABLE or FREETEXTTABLE and joining
back to Document. I'm also assuming you are using a "TOP 1500" and an
"ORDER BY Rank" in your query. Could you then just add "AND OfficeID =
'USEROFFICEID'" to your WHERE clause after adding the OfficeID field to
the table?
On Dec 14, 7:32 pm, Simon Sabin <SimonSa...@.noemail.noemail> wrote:[vbcol=seagreen]
> Hello Kyle,
> The other option is to add the office ID to the content (If the content was
> editable i.e. text/html)
> Then use a query like containstable(document, content,'OFFICE2345 AND "SQL
> SERVER DBA"')
> If the content is editabel this is by far the more manageable, and scalable.
> We did the index view thing and it is just not a neat solution. The token
> thing is much easier.
> Simon Sabin
> SQL Server MVPhttp://sqlblogcasts.com/blogs/simons
>
>
>
>
>
>
>
>
>
|||Looks like I'm stuck.
In order to create a Full Text Index on my view, the view has to have a
unique index.
SQL 2005 doesn't allow you to create a index on a view if the view contains
an text, ntext, image or xml columns.
And mine does because we're storint the resume in an image column and the
resume is what we're after.
We can't add any special tokens, the content is an image field.
Any other ideas?
"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:62959f1a36ede8c8edf7abdbb584@.msnews.microsoft .com...
> Hello Kyle,
> The other option is to add the office ID to the content (If the content
> was editable i.e. text/html)
> Then use a query like containstable(document, content,'OFFICE2345 AND "SQL
> SERVER DBA"')
> If the content is editabel this is by far the more manageable, and
> scalable.
> We did the index view thing and it is just not a neat solution. The token
> thing is much easier.
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>
|||Kyle,
a) you can create an index on a view if you use VARCHAR(MAX) or
VARBINARY(MAX) instead of TEXT or IMAGE (which are deprecated in sql 2005).
b) AFAIK, those "tokens" can be other columns from the same view/table that
are also indexed in that FT catalog. You just specify CONTAINS(*,... Instead
of CONTAINS(MyClobColumn,...
it should work.
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:%23vIoXwFJHHA.536@.TK2MSFTNGP02.phx.gbl...
> Looks like I'm stuck.
> In order to create a Full Text Index on my view, the view has to have a
> unique index.
> SQL 2005 doesn't allow you to create a index on a view if the view
> contains an text, ntext, image or xml columns.
> And mine does because we're storint the resume in an image column and the
> resume is what we're after.
> We can't add any special tokens, the content is an image field.
> Any other ideas?
>
> "Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
> news:62959f1a36ede8c8edf7abdbb584@.msnews.microsoft .com...
>
|||I will have to try changing the type over to VARBINARY(MAX). That may work.
It mght also be easier to FTI a second column since that will require less
program changes.
Thanks
"Lakusha" <Lakusha@.excite.com> wrote in message
news:uWBdE0KJHHA.3552@.TK2MSFTNGP03.phx.gbl...
> Kyle,
> a) you can create an index on a view if you use VARCHAR(MAX) or
> VARBINARY(MAX) instead of TEXT or IMAGE (which are deprecated in sql
> 2005).
> b) AFAIK, those "tokens" can be other columns from the same view/table
> that are also indexed in that FT catalog. You just specify CONTAINS(*,...
> Instead of CONTAINS(MyClobColumn,...
> it should work.
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:%23vIoXwFJHHA.536@.TK2MSFTNGP02.phx.gbl...
>
|||I added Office nvchar(8) to my table and populated int
select count(DocumentID) from Document where contains(*, 'Office01 and
cobol')
"Lakusha" <Lakusha@.excite.com> wrote in message
news:uWBdE0KJHHA.3552@.TK2MSFTNGP03.phx.gbl...
> Kyle,
> a) you can create an index on a view if you use VARCHAR(MAX) or
> VARBINARY(MAX) instead of TEXT or IMAGE (which are deprecated in sql
> 2005).
> b) AFAIK, those "tokens" can be other columns from the same view/table
> that are also indexed in that FT catalog. You just specify CONTAINS(*,...
> Instead of CONTAINS(MyClobColumn,...
> it should work.
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:%23vIoXwFJHHA.536@.TK2MSFTNGP02.phx.gbl...
>
|||I added a Office nvchar(8) column and populated it.
No matter what I specify for a search condition, it doesn't return what I'm
after.
For a test I tried
select count(DocumentID) from Document where contains(*, 'Office02 and
cobol')
'Office02' is in the new column, 'cobol' is in the image column.
If I search for them seperately there is overlap so the data is correct.
Ideas?
"Lakusha" <Lakusha@.excite.com> wrote in message
news:uWBdE0KJHHA.3552@.TK2MSFTNGP03.phx.gbl...
> Kyle,
> a) you can create an index on a view if you use VARCHAR(MAX) or
> VARBINARY(MAX) instead of TEXT or IMAGE (which are deprecated in sql
> 2005).
> b) AFAIK, those "tokens" can be other columns from the same view/table
> that are also indexed in that FT catalog. You just specify CONTAINS(*,...
> Instead of CONTAINS(MyClobColumn,...
> it should work.
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:%23vIoXwFJHHA.536@.TK2MSFTNGP02.phx.gbl...
>
|||To be clear the 'Office' column will only ever hold an office identifier
liket 'Office01' or 'Office13'.
The Content column has the interedting data.
We want to use containstable to give us the top n by rank DocumentIDs for a
particular office.
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uM6mqsRKHHA.1468@.TK2MSFTNGP04.phx.gbl...
>I added a Office nvchar(8) column and populated it.
> No matter what I specify for a search condition, it doesn't return what
> I'm after.
> For a test I tried
> select count(DocumentID) from Document where contains(*, 'Office02 and
> cobol')
> 'Office02' is in the new column, 'cobol' is in the image column.
> If I search for them seperately there is overlap so the data is correct.
> Ideas?
> "Lakusha" <Lakusha@.excite.com> wrote in message
> news:uWBdE0KJHHA.3552@.TK2MSFTNGP03.phx.gbl...
>

from nvarchar to ntext

I would like to change my field type from nvarchar to ntext.
Is it possible? Are there any restriction for ntext?
I have stored procedues and views using the filed.
Are there any impact for this changes?
Any information is great appreciated,Hi
Depending on what you are doing, you may need to change to code to use
WRITETEXT and UPDATETEXT See books online for more information about using
these functions.
You can change the column using the ALTER TABLE ... ALTER COLUMN...
statement.
John
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:CFD8641B-7177-4D62-B608-9BA21A75F64F@.microsoft.com...
>I would like to change my field type from nvarchar to ntext.
> Is it possible? Are there any restriction for ntext?
> I have stored procedues and views using the filed.
> Are there any impact for this changes?
> Any information is great appreciated,
>

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 insert
s
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 chang
e
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:

> 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. Fo
r
> 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...
>
>|||> 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 chang
e
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:

> 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. Fo
r
> 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...
>
>|||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/defaul...kb;en-us;169960
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
AMB
"Alex" wrote:
[vbcol=seagreen]
> 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, becau
se
> I have few rows per page. And that's the reason that I thoght about to cha
nge
> 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:
>|||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/defaul...kb;en-us;169960
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
AMB
"Alex" wrote:
[vbcol=seagreen]
> 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, becau
se
> I have few rows per page. And that's the reason that I thoght about to cha
nge
> 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:
>|||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...[vbcol=seagreen]
> 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:
>|||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...[vbcol=seagreen]
> 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:
>

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
>> >
>>