We are implementing FTI on a Linguistic SQL Server Database for performance improvement. The maximum size of database is 20 GB and there are 27 million records in the table on which we are implementing FTI. When We did a small POC with 1.2 million records, the FTI worked fine. But now with 27 million records it' giving the error messages attached in the event logs below.
It looks like the system resources are insufficient for completing the FTI population, but can I pinpoint what is causing problem from the overall system? Can we make some firm conclusions by looking at the attached event logs?
FYI: the free disc space on which the FTI catalogs are kept is 55GB. The SQL Server configuration is 4 processors, 4GB RAM.
Any help in this regard is greatly appreciated. ThanksHi There,
You could always split the process by /8 or /16 different tables and then move them to the new location systematically and not all at one time.
(ISQL/W : Select percentage 25% from blah ...?)
Showing posts with label index. Show all posts
Showing posts with label index. Show all posts
Monday, March 26, 2012
FTI on whole Database
I am new at FULLTEXT INDEX, I wonder if there is a possibility to index the whole DB at once and search it in the same way rather than specifying the Table and the columns?
NO, and I can't imagine why one would consider that as a possibility.
I wouldn't consider searching datetime columns, or money columns, or numeric columns for a text string. So even if it were possible, it would amount to a terrible waste of time/effort searching unnecessary columns. As well as significant resources just to create and maintain indexes on columns that may never need to be searched.
|||
Ok, Here is the scenario, We have this huge DB from a client -about 6k Tables!!!!- I am looking for the column that contains a specific string? I thought of using the FULLTEXT INDEX to get all the instances that contains that strings.
Is there a way to do that? Definitely as you mentioned it will be a terrible waste of time/effort but I don't see doing this otherwise, searching the DB table by table will be worse.
No matter how you cut it, that will be a resource intensive task.
Here is a procedure that I created that would do the search. Hopefully, it will give you a good idea or two. Expect it to take quite some time with 6k tables.
--*******************************************
-- Problem: Locate data in any column, any table
-- Demonstrates:
-- WHILE looping
-- Using Dynamic SQL (sp_executesql)
-- #Temp Table vs. Table Variable
--*******************************************
SET NOCOUNT ON
DECLARE
@.TotalRows int,
@.Counter int,
@.TableName varchar(50),
@.ColumnName varchar(50),
@.FieldValue varchar(250),
@.SQLCommand nvarchar(1000),
@.ValueToFind varchar(100)
-->
SET @.ValueToFind = 'mexico'
USE Northwind -- For demo only, not needed
-->
DECLARE @.MyTable table
( RowID int IDENTITY,
TableName varchar(50),
ColumnName varchar(50)
)
CREATE TABLE #FoundTable
( RowID int IDENTITY,
Tablename varchar(100)
)
INSERT INTO @.MyTable
SELECT
TABLE_NAME,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ( 'char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext' )
SELECT
@.TotalRows = @.@.ROWCOUNT,
@.Counter = 1
WHILE ( @.Counter <= @.TotalRows )
BEGIN
SELECT
@.TableName = TableName,
@.ColumnName = ColumnName
FROM @.MyTable
WHERE RowID = @.Counter
SET @.SQLCommand = 'IF EXISTS ( '+
'SELECT 1 FROM [' + @.TableName + '] ' +
'WHERE [' + @.ColumnName + '] LIKE ''%' + @.ValueToFind + '%'' ' +
' )' +
'INSERT INTO #FoundTable ' +
' SELECT ''[' + @.TableName + ']([' + @.ColumnName + '])'''
EXECUTE sp_executesql @.SQLCommand
SET @.Counter = ( @.Counter + 1 )
END
SELECT * FROM #FoundTable
DROP TABLE #FoundTable
/*
RowID Tablename
-- --
1 Invoices(Salesperson)
2 Employees(LastName)
3 Employees(PhotoPath)
*/
|||Thanks!!!!!!!!!!! It worked, I had some issue with the variables, I was getting errors "Must declare the scalar variable "@.TotalRows". which I think it is the result of the variables scope.
|||Sorry, I had left a [GO] in the script, and it starts a new variable scope.
FTE locks the table
I have a table with Full Text index on it, the table has at the most 300
records.
The database is on the live server and gets hit a lot.
Every morning when i come to work the table is inaccessible, meaning any
query that i issue to this table runs indefinitely, Select, update, Insert
all just hang.
The only way for me to fix this is manually End The Full Text Sql Windows
Task, in task manager. As soon as i end the task it restarts immediately and
everything is working fine after.
Is there a patch for Sql 2005 to fix this, did anyone experience something
like that?
Full-text indexing will cause some locking as it needs to create some level
of consistency with what is in the catalogs and what is in the database. Are
you using change tracking? If not you really should. You also might want to
set your resource usage to something smaller.
One more point, it is possible that other non-optimal procs or ad hoq sql
statements are themselves not tuned and are causing overall performance
problems manifesting itself in FTI locking.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:206A8F61-21C9-475B-9095-C6708D29A09C@.microsoft.com...
>I have a table with Full Text index on it, the table has at the most 300
> records.
> The database is on the live server and gets hit a lot.
> Every morning when i come to work the table is inaccessible, meaning any
> query that i issue to this table runs indefinitely, Select, update, Insert
> all just hang.
> The only way for me to fix this is manually End The Full Text Sql Windows
> Task, in task manager. As soon as i end the task it restarts immediately
> and
> everything is working fine after.
> Is there a patch for Sql 2005 to fix this, did anyone experience something
> like that?
>
sql
records.
The database is on the live server and gets hit a lot.
Every morning when i come to work the table is inaccessible, meaning any
query that i issue to this table runs indefinitely, Select, update, Insert
all just hang.
The only way for me to fix this is manually End The Full Text Sql Windows
Task, in task manager. As soon as i end the task it restarts immediately and
everything is working fine after.
Is there a patch for Sql 2005 to fix this, did anyone experience something
like that?
Full-text indexing will cause some locking as it needs to create some level
of consistency with what is in the catalogs and what is in the database. Are
you using change tracking? If not you really should. You also might want to
set your resource usage to something smaller.
One more point, it is possible that other non-optimal procs or ad hoq sql
statements are themselves not tuned and are causing overall performance
problems manifesting itself in FTI locking.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:206A8F61-21C9-475B-9095-C6708D29A09C@.microsoft.com...
>I have a table with Full Text index on it, the table has at the most 300
> records.
> The database is on the live server and gets hit a lot.
> Every morning when i come to work the table is inaccessible, meaning any
> query that i issue to this table runs indefinitely, Select, update, Insert
> all just hang.
> The only way for me to fix this is manually End The Full Text Sql Windows
> Task, in task manager. As soon as i end the task it restarts immediately
> and
> everything is working fine after.
> Is there a patch for Sql 2005 to fix this, did anyone experience something
> like that?
>
sql
FTE locks the table
I have a table with Full Text index on it, the table has at the most 300
records.
The database is on the live server and gets hit a lot.
Every morning when i come to work the table is inaccessible, meaning any
query that i issue to this table runs indefinitely, Select, update, Insert
all just hang.
The only way for me to fix this is manually End The Full Text Sql Windows
Task, in task manager. As soon as i end the task it restarts immediately and
everything is working fine after.
Is there a patch for Sql 2005 to fix this, did anyone experience something
like that?Full-text indexing will cause some locking as it needs to create some level
of consistency with what is in the catalogs and what is in the database. Are
you using change tracking? If not you really should. You also might want to
set your resource usage to something smaller.
One more point, it is possible that other non-optimal procs or ad hoq sql
statements are themselves not tuned and are causing overall performance
problems manifesting itself in FTI locking.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:206A8F61-21C9-475B-9095-C6708D29A09C@.microsoft.com...
>I have a table with Full Text index on it, the table has at the most 300
> records.
> The database is on the live server and gets hit a lot.
> Every morning when i come to work the table is inaccessible, meaning any
> query that i issue to this table runs indefinitely, Select, update, Insert
> all just hang.
> The only way for me to fix this is manually End The Full Text Sql Windows
> Task, in task manager. As soon as i end the task it restarts immediately
> and
> everything is working fine after.
> Is there a patch for Sql 2005 to fix this, did anyone experience something
> like that?
>
records.
The database is on the live server and gets hit a lot.
Every morning when i come to work the table is inaccessible, meaning any
query that i issue to this table runs indefinitely, Select, update, Insert
all just hang.
The only way for me to fix this is manually End The Full Text Sql Windows
Task, in task manager. As soon as i end the task it restarts immediately and
everything is working fine after.
Is there a patch for Sql 2005 to fix this, did anyone experience something
like that?Full-text indexing will cause some locking as it needs to create some level
of consistency with what is in the catalogs and what is in the database. Are
you using change tracking? If not you really should. You also might want to
set your resource usage to something smaller.
One more point, it is possible that other non-optimal procs or ad hoq sql
statements are themselves not tuned and are causing overall performance
problems manifesting itself in FTI locking.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:206A8F61-21C9-475B-9095-C6708D29A09C@.microsoft.com...
>I have a table with Full Text index on it, the table has at the most 300
> records.
> The database is on the live server and gets hit a lot.
> Every morning when i come to work the table is inaccessible, meaning any
> query that i issue to this table runs indefinitely, Select, update, Insert
> all just hang.
> The only way for me to fix this is manually End The Full Text Sql Windows
> Task, in task manager. As soon as i end the task it restarts immediately
> and
> everything is working fine after.
> Is there a patch for Sql 2005 to fix this, did anyone experience something
> like that?
>
FTE locks the table
I have a table with Full Text index on it, the table has at the most 300
records.
The database is on the live server and gets hit a lot.
Every morning when i come to work the table is inaccessible, meaning any
query that i issue to this table runs indefinitely, Select, update, Insert
all just hang.
The only way for me to fix this is manually End The Full Text Sql Windows
Task, in task manager. As soon as i end the task it restarts immediately and
everything is working fine after.
Is there a patch for Sql 2005 to fix this, did anyone experience something
like that?Full-text indexing will cause some locking as it needs to create some level
of consistency with what is in the catalogs and what is in the database. Are
you using change tracking? If not you really should. You also might want to
set your resource usage to something smaller.
One more point, it is possible that other non-optimal procs or ad hoq sql
statements are themselves not tuned and are causing overall performance
problems manifesting itself in FTI locking.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:206A8F61-21C9-475B-9095-C6708D29A09C@.microsoft.com...
>I have a table with Full Text index on it, the table has at the most 300
> records.
> The database is on the live server and gets hit a lot.
> Every morning when i come to work the table is inaccessible, meaning any
> query that i issue to this table runs indefinitely, Select, update, Insert
> all just hang.
> The only way for me to fix this is manually End The Full Text Sql Windows
> Task, in task manager. As soon as i end the task it restarts immediately
> and
> everything is working fine after.
> Is there a patch for Sql 2005 to fix this, did anyone experience something
> like that?
>
records.
The database is on the live server and gets hit a lot.
Every morning when i come to work the table is inaccessible, meaning any
query that i issue to this table runs indefinitely, Select, update, Insert
all just hang.
The only way for me to fix this is manually End The Full Text Sql Windows
Task, in task manager. As soon as i end the task it restarts immediately and
everything is working fine after.
Is there a patch for Sql 2005 to fix this, did anyone experience something
like that?Full-text indexing will cause some locking as it needs to create some level
of consistency with what is in the catalogs and what is in the database. Are
you using change tracking? If not you really should. You also might want to
set your resource usage to something smaller.
One more point, it is possible that other non-optimal procs or ad hoq sql
statements are themselves not tuned and are causing overall performance
problems manifesting itself in FTI locking.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:206A8F61-21C9-475B-9095-C6708D29A09C@.microsoft.com...
>I have a table with Full Text index on it, the table has at the most 300
> records.
> The database is on the live server and gets hit a lot.
> Every morning when i come to work the table is inaccessible, meaning any
> query that i issue to this table runs indefinitely, Select, update, Insert
> all just hang.
> The only way for me to fix this is manually End The Full Text Sql Windows
> Task, in task manager. As soon as i end the task it restarts immediately
> and
> everything is working fine after.
> Is there a patch for Sql 2005 to fix this, did anyone experience something
> like that?
>
FT Locks the table!
I have a table with Full Text index on it, the table has at the most 300
records.
The database is on the live server and gets hit a lot.
Every morning when i come to work the table is inaccessible, meaning any
query that i issue to this table runs indefinitely, Select, update, Insert
all just hang.
The only way for me to fix this is manually End The Full Text Sql Windows
Task, in task manager. As soon as i end the task it restarts immediately and
everything is working fine after.
Is there a patch for Sql 2005 to fix this, did anyone experience something
like that?
Full-text indexing will cause some locking as it needs to create some level
of consistency with what is in the catalogs and what is in the database. Are
you using change tracking? If not you really should. You also might want to
set your resource usage to something smaller.
One more point, it is possible that other non-optimal procs or ad hoq sql
statements are themselves not tuned and are causing overall performance
problems manifesting itself in FTI locking.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:60169C43-9233-4021-88C2-9C200A4E0462@.microsoft.com...
>I have a table with Full Text index on it, the table has at the most 300
> records.
> The database is on the live server and gets hit a lot.
> Every morning when i come to work the table is inaccessible, meaning any
> query that i issue to this table runs indefinitely, Select, update, Insert
> all just hang.
> The only way for me to fix this is manually End The Full Text Sql Windows
> Task, in task manager. As soon as i end the task it restarts immediately
> and
> everything is working fine after.
> Is there a patch for Sql 2005 to fix this, did anyone experience something
> like that?
>
|||Thank you Hilary.
I checked and i have the change tracking set to Auto.
I cannot find where i can change the resource usage for FT.
Can you help please?
It is Sql 2005.
"Hilary Cotter" wrote:
> Full-text indexing will cause some locking as it needs to create some level
> of consistency with what is in the catalogs and what is in the database. Are
> you using change tracking? If not you really should. You also might want to
> set your resource usage to something smaller.
> One more point, it is possible that other non-optimal procs or ad hoq sql
> statements are themselves not tuned and are causing overall performance
> problems manifesting itself in FTI locking.
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
> news:60169C43-9233-4021-88C2-9C200A4E0462@.microsoft.com...
>
>
|||sp_fulltext_service 'resource usage',2
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:0953B0F8-5E75-482A-A273-DFA94BEAEA73@.microsoft.com...[vbcol=seagreen]
> Thank you Hilary.
> I checked and i have the change tracking set to Auto.
> I cannot find where i can change the resource usage for FT.
> Can you help please?
> It is Sql 2005.
>
> "Hilary Cotter" wrote:
records.
The database is on the live server and gets hit a lot.
Every morning when i come to work the table is inaccessible, meaning any
query that i issue to this table runs indefinitely, Select, update, Insert
all just hang.
The only way for me to fix this is manually End The Full Text Sql Windows
Task, in task manager. As soon as i end the task it restarts immediately and
everything is working fine after.
Is there a patch for Sql 2005 to fix this, did anyone experience something
like that?
Full-text indexing will cause some locking as it needs to create some level
of consistency with what is in the catalogs and what is in the database. Are
you using change tracking? If not you really should. You also might want to
set your resource usage to something smaller.
One more point, it is possible that other non-optimal procs or ad hoq sql
statements are themselves not tuned and are causing overall performance
problems manifesting itself in FTI locking.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:60169C43-9233-4021-88C2-9C200A4E0462@.microsoft.com...
>I have a table with Full Text index on it, the table has at the most 300
> records.
> The database is on the live server and gets hit a lot.
> Every morning when i come to work the table is inaccessible, meaning any
> query that i issue to this table runs indefinitely, Select, update, Insert
> all just hang.
> The only way for me to fix this is manually End The Full Text Sql Windows
> Task, in task manager. As soon as i end the task it restarts immediately
> and
> everything is working fine after.
> Is there a patch for Sql 2005 to fix this, did anyone experience something
> like that?
>
|||Thank you Hilary.
I checked and i have the change tracking set to Auto.
I cannot find where i can change the resource usage for FT.
Can you help please?
It is Sql 2005.
"Hilary Cotter" wrote:
> Full-text indexing will cause some locking as it needs to create some level
> of consistency with what is in the catalogs and what is in the database. Are
> you using change tracking? If not you really should. You also might want to
> set your resource usage to something smaller.
> One more point, it is possible that other non-optimal procs or ad hoq sql
> statements are themselves not tuned and are causing overall performance
> problems manifesting itself in FTI locking.
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
> news:60169C43-9233-4021-88C2-9C200A4E0462@.microsoft.com...
>
>
|||sp_fulltext_service 'resource usage',2
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:0953B0F8-5E75-482A-A273-DFA94BEAEA73@.microsoft.com...[vbcol=seagreen]
> Thank you Hilary.
> I checked and i have the change tracking set to Auto.
> I cannot find where i can change the resource usage for FT.
> Can you help please?
> It is Sql 2005.
>
> "Hilary Cotter" wrote:
FT Index on column with multiple language doesn't work - any ideas
Hi all,
I have a table on SQL Server 2005 with a column (collation
SQL_Latin1_General_CP1_CI_AS) that can contain multiple languages. Some of
the possible languages include English, German, French, Kazakh, Arabic,
Chinese (Simplified and Traditional), Japanese, Korean, Thai, etc.
I tried querying for documents that contains 台灣 (meaning "Taiwan" in
Simplified Chinese), but it is not working as I expected.
I ran the following query:
select * from tblDocuments
where CONTAINS(DocumentTitle, '台灣', LANGUAGE 'Traditional Chinese')
With the English language breaker, I get 0 results.
With the Traditional Chinese language breaker, I get 1 result and it is the
document I was looking for.
I would've expected the LANGUAGE keyword to ignore the default language
breaker and use whatever is specified, but apparently I'm missing
something... maybe table setup or whatnot.
Any ideas would be greatly appreaciated.
Cheers,
Andry
This is correct behavior. When the document is indexed and you are
specifying the English word breaker it interprets the contents of the row to
be English words, unless you are using the xml language tags and specify
that the XML doc is in Chinese and that you are using the XML data type, or
you are using Word and marked that passage containing the character as
Chinese, or the language settings for your Word document are Chinese, or you
are using html and have set the ms.locale to Chinese and set the correct
code page. With Word Docs and HTML docs you must save them in the image or
varbinary data type columns and use a document type column with the
extension the document would have if it was stored in the file system.
If you don't follow the above the Chinese character will be interpreted as a
Unicode sequence which will not be interpreted correctly in your queries.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Andry" <Andry@.discussions.microsoft.com> wrote in message
news:569E9782-47E3-4657-AC68-7787214D3D97@.microsoft.com...
> Hi all,
> I have a table on SQL Server 2005 with a column (collation
> SQL_Latin1_General_CP1_CI_AS) that can contain multiple languages. Some of
> the possible languages include English, German, French, Kazakh, Arabic,
> Chinese (Simplified and Traditional), Japanese, Korean, Thai, etc.
> I tried querying for documents that contains ? (meaning "Taiwan" in
> Simplified Chinese), but it is not working as I expected.
> I ran the following query:
> select * from tblDocuments
> where CONTAINS(DocumentTitle, '?', LANGUAGE 'Traditional Chinese')
> With the English language breaker, I get 0 results.
> With the Traditional Chinese language breaker, I get 1 result and it is
> the
> document I was looking for.
> I would've expected the LANGUAGE keyword to ignore the default language
> breaker and use whatever is specified, but apparently I'm missing
> something... maybe table setup or whatnot.
> Any ideas would be greatly appreaciated.
> Cheers,
> Andry
I have a table on SQL Server 2005 with a column (collation
SQL_Latin1_General_CP1_CI_AS) that can contain multiple languages. Some of
the possible languages include English, German, French, Kazakh, Arabic,
Chinese (Simplified and Traditional), Japanese, Korean, Thai, etc.
I tried querying for documents that contains 台灣 (meaning "Taiwan" in
Simplified Chinese), but it is not working as I expected.
I ran the following query:
select * from tblDocuments
where CONTAINS(DocumentTitle, '台灣', LANGUAGE 'Traditional Chinese')
With the English language breaker, I get 0 results.
With the Traditional Chinese language breaker, I get 1 result and it is the
document I was looking for.
I would've expected the LANGUAGE keyword to ignore the default language
breaker and use whatever is specified, but apparently I'm missing
something... maybe table setup or whatnot.
Any ideas would be greatly appreaciated.
Cheers,
Andry
This is correct behavior. When the document is indexed and you are
specifying the English word breaker it interprets the contents of the row to
be English words, unless you are using the xml language tags and specify
that the XML doc is in Chinese and that you are using the XML data type, or
you are using Word and marked that passage containing the character as
Chinese, or the language settings for your Word document are Chinese, or you
are using html and have set the ms.locale to Chinese and set the correct
code page. With Word Docs and HTML docs you must save them in the image or
varbinary data type columns and use a document type column with the
extension the document would have if it was stored in the file system.
If you don't follow the above the Chinese character will be interpreted as a
Unicode sequence which will not be interpreted correctly in your queries.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Andry" <Andry@.discussions.microsoft.com> wrote in message
news:569E9782-47E3-4657-AC68-7787214D3D97@.microsoft.com...
> Hi all,
> I have a table on SQL Server 2005 with a column (collation
> SQL_Latin1_General_CP1_CI_AS) that can contain multiple languages. Some of
> the possible languages include English, German, French, Kazakh, Arabic,
> Chinese (Simplified and Traditional), Japanese, Korean, Thai, etc.
> I tried querying for documents that contains ? (meaning "Taiwan" in
> Simplified Chinese), but it is not working as I expected.
> I ran the following query:
> select * from tblDocuments
> where CONTAINS(DocumentTitle, '?', LANGUAGE 'Traditional Chinese')
> With the English language breaker, I get 0 results.
> With the Traditional Chinese language breaker, I get 1 result and it is
> the
> document I was looking for.
> I would've expected the LANGUAGE keyword to ignore the default language
> breaker and use whatever is specified, but apparently I'm missing
> something... maybe table setup or whatnot.
> Any ideas would be greatly appreaciated.
> Cheers,
> Andry
FT Index on [German|English] columns. SQL Server 2005
Hi,
sorry for the cryptic topic. I wasn't able to come up with anything
better.
We are currently working on a project thats aims to keep all students
thesis es in a database and enable searches on it via a ASP .NET web
app. The problem we have is that some students write their thesis in
german and some in english. Right now there are more germans texts than
english ones but i guess that will change in the near future. We've
heard it's possible to maintain a fulltext index with both languages at
once but we would have to specify with our queries which index should
be used. While this is ok for entering data, asking the user if he is
going to enter his thesis in english or german, asking someone that is
searching we find it to be troublesome or at least leading to
confusion. Most of the german thesis texts contain a fair number of
english words too (computer science kinda lives from english buzzwords
;-)). So languages might be even mixed inside a text.
Anyone recommendations on how to solve this kinda of problem? Would it
be reasonable to "merge" the englisch and german stopword list and
create a "new language"?
Thanks for your help! All hints/links appreciated.
Kind regards from Germany
Phil
Phil,
In SQL Server 2005 Full-Text Search (FTS) you can store and search on
language-specific words or phrases at query time via CONTAINS or
FREETEXT. So, you can store both languages (English and German are both
supported FTS languages) and use either English or German for the
"Language for Word Breaker" and then use the following CONTAINS
statement (with or without the formsof(inflectional) parameter):
select * from FTSTable where
contains(*,'formsof(inflectional,"english_word")', language 1033) OR
contains(*,'formsof(inflectional,"German_word")',l anguage 1031)
Additionally, on an advance search form, you may want to add a box for
keyword and the associated language, so that the searchers can select
the approprate language for the keyword. Note, the default language is
whatever language you defined for the "Language for Word Breaker".
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
|||Such a project is doomed. German and English generate a large number of
false friends/false conjugates; wander words/worts which are common in both
languages but with different meanings. For instance Gift in English means a
present; in German its means poison.
So what will happen no matter what the language you specify your query to
occur in you will get false hits. The better way to do it is to sniff your
browser settings and check for German as the supported language and then
have the docs stored in different columns/tables/databases. Then a query
would be directed against a language segregated column/table or database.
If your queries are done using the Contains predicate the false conjugates
will be minimized.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Philipp Stader" <pstader@.gmail.com> wrote in message
news:1131651975.271992.103620@.z14g2000cwz.googlegr oups.com...
> Hi,
> sorry for the cryptic topic. I wasn't able to come up with anything
> better.
> We are currently working on a project thats aims to keep all students
> thesis es in a database and enable searches on it via a ASP .NET web
> app. The problem we have is that some students write their thesis in
> german and some in english. Right now there are more germans texts than
> english ones but i guess that will change in the near future. We've
> heard it's possible to maintain a fulltext index with both languages at
> once but we would have to specify with our queries which index should
> be used. While this is ok for entering data, asking the user if he is
> going to enter his thesis in english or german, asking someone that is
> searching we find it to be troublesome or at least leading to
> confusion. Most of the german thesis texts contain a fair number of
> english words too (computer science kinda lives from english buzzwords
> ;-)). So languages might be even mixed inside a text.
> Anyone recommendations on how to solve this kinda of problem? Would it
> be reasonable to "merge" the englisch and german stopword list and
> create a "new language"?
> Thanks for your help! All hints/links appreciated.
> Kind regards from Germany
> Phil
>
|||Thanks for your input! This basicaly means i'll have to tell SQL Server
which language i want it to use? On inserts and searches? Meaning i
have to ask the user if the text he is going to submit is english or
german (or apply some magic to detect the language)?
|||I wish there was a german-english word breaker :-) Detecting browser
language settings won't help us much. People can choose to write their
diploma/master thesis in german or english. Browser language settings
will be german all the way since student can only enter their thesis
from within the university, where all clients are "german". Most user
searching for a thesis will use some acronyms/keywords like XML, C#,
UML etc which are kinda language indepedent. But there are some
technological terms which have a good and commonly used german
expression, usually a longer word combined from substantives so i think
the correct wordbreaker would help a lot if a user search for a
substring of the longer word. Thanks for pointing the problems out.
Seems to me that our goal is kinda not feasable right now :-/
|||You're welcome, Philipp,
Yes, you still need to define the "Language for Word Breaker" (either German
or English) and you only need to do this once when you define the Full-Text
Catalog and the specific FT-enabled column where these languages are stored.
The default will be which ever language you define, and you only need to
specific the non-default language at query time, i.e., when you execute a
CONTAINS or FREETEXT searches.
No, you don't (and cannot) define the language during INSERTS as
syntactically this is not possible with the INSERT statement. The SQL Server
2005 FTS engine (msftesql.exe) will detect and determine the language of the
mixed text in this column.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Philipp Stader" <pstader@.gmail.com> wrote in message
news:1131886568.811593.201700@.o13g2000cwo.googlegr oups.com...
> Thanks for your input! This basicaly means i'll have to tell SQL Server
> which language i want it to use? On inserts and searches? Meaning i
> have to ask the user if the text he is going to submit is english or
> german (or apply some magic to detect the language)?
>
sorry for the cryptic topic. I wasn't able to come up with anything
better.
We are currently working on a project thats aims to keep all students
thesis es in a database and enable searches on it via a ASP .NET web
app. The problem we have is that some students write their thesis in
german and some in english. Right now there are more germans texts than
english ones but i guess that will change in the near future. We've
heard it's possible to maintain a fulltext index with both languages at
once but we would have to specify with our queries which index should
be used. While this is ok for entering data, asking the user if he is
going to enter his thesis in english or german, asking someone that is
searching we find it to be troublesome or at least leading to
confusion. Most of the german thesis texts contain a fair number of
english words too (computer science kinda lives from english buzzwords
;-)). So languages might be even mixed inside a text.
Anyone recommendations on how to solve this kinda of problem? Would it
be reasonable to "merge" the englisch and german stopword list and
create a "new language"?
Thanks for your help! All hints/links appreciated.
Kind regards from Germany
Phil
Phil,
In SQL Server 2005 Full-Text Search (FTS) you can store and search on
language-specific words or phrases at query time via CONTAINS or
FREETEXT. So, you can store both languages (English and German are both
supported FTS languages) and use either English or German for the
"Language for Word Breaker" and then use the following CONTAINS
statement (with or without the formsof(inflectional) parameter):
select * from FTSTable where
contains(*,'formsof(inflectional,"english_word")', language 1033) OR
contains(*,'formsof(inflectional,"German_word")',l anguage 1031)
Additionally, on an advance search form, you may want to add a box for
keyword and the associated language, so that the searchers can select
the approprate language for the keyword. Note, the default language is
whatever language you defined for the "Language for Word Breaker".
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
|||Such a project is doomed. German and English generate a large number of
false friends/false conjugates; wander words/worts which are common in both
languages but with different meanings. For instance Gift in English means a
present; in German its means poison.
So what will happen no matter what the language you specify your query to
occur in you will get false hits. The better way to do it is to sniff your
browser settings and check for German as the supported language and then
have the docs stored in different columns/tables/databases. Then a query
would be directed against a language segregated column/table or database.
If your queries are done using the Contains predicate the false conjugates
will be minimized.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Philipp Stader" <pstader@.gmail.com> wrote in message
news:1131651975.271992.103620@.z14g2000cwz.googlegr oups.com...
> Hi,
> sorry for the cryptic topic. I wasn't able to come up with anything
> better.
> We are currently working on a project thats aims to keep all students
> thesis es in a database and enable searches on it via a ASP .NET web
> app. The problem we have is that some students write their thesis in
> german and some in english. Right now there are more germans texts than
> english ones but i guess that will change in the near future. We've
> heard it's possible to maintain a fulltext index with both languages at
> once but we would have to specify with our queries which index should
> be used. While this is ok for entering data, asking the user if he is
> going to enter his thesis in english or german, asking someone that is
> searching we find it to be troublesome or at least leading to
> confusion. Most of the german thesis texts contain a fair number of
> english words too (computer science kinda lives from english buzzwords
> ;-)). So languages might be even mixed inside a text.
> Anyone recommendations on how to solve this kinda of problem? Would it
> be reasonable to "merge" the englisch and german stopword list and
> create a "new language"?
> Thanks for your help! All hints/links appreciated.
> Kind regards from Germany
> Phil
>
|||Thanks for your input! This basicaly means i'll have to tell SQL Server
which language i want it to use? On inserts and searches? Meaning i
have to ask the user if the text he is going to submit is english or
german (or apply some magic to detect the language)?
|||I wish there was a german-english word breaker :-) Detecting browser
language settings won't help us much. People can choose to write their
diploma/master thesis in german or english. Browser language settings
will be german all the way since student can only enter their thesis
from within the university, where all clients are "german". Most user
searching for a thesis will use some acronyms/keywords like XML, C#,
UML etc which are kinda language indepedent. But there are some
technological terms which have a good and commonly used german
expression, usually a longer word combined from substantives so i think
the correct wordbreaker would help a lot if a user search for a
substring of the longer word. Thanks for pointing the problems out.
Seems to me that our goal is kinda not feasable right now :-/
|||You're welcome, Philipp,
Yes, you still need to define the "Language for Word Breaker" (either German
or English) and you only need to do this once when you define the Full-Text
Catalog and the specific FT-enabled column where these languages are stored.
The default will be which ever language you define, and you only need to
specific the non-default language at query time, i.e., when you execute a
CONTAINS or FREETEXT searches.
No, you don't (and cannot) define the language during INSERTS as
syntactically this is not possible with the INSERT statement. The SQL Server
2005 FTS engine (msftesql.exe) will detect and determine the language of the
mixed text in this column.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Philipp Stader" <pstader@.gmail.com> wrote in message
news:1131886568.811593.201700@.o13g2000cwo.googlegr oups.com...
> Thanks for your input! This basicaly means i'll have to tell SQL Server
> which language i want it to use? On inserts and searches? Meaning i
> have to ask the user if the text he is going to submit is english or
> german (or apply some magic to detect the language)?
>
FT index not function ?
I configure a table with FT index , but when i search the
table , nothing returned , why ?
Windows 2003 EE (Chinese Edition)
SQL Server 2k + SP3 (Chinese Edition)
1. makefile C:\Program Files\Microsoft SQL
Server\MSSQL\FTDATA\SQLServer\Config\noise.chs
2. start the MSSearch service with local system account.
3. connect to SQL Database with isqlw
create table docbase , the type of dcont field is image
and to store my document of word,excel,ppt,htm etc.
4. enable Fulltext of database
sp_fulltext_database 'enable'
5. create catalogs
execute
sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'
execute
sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE
'
execute sp_fulltext_column 'DOCBASE' , 'DCONT' , 'add' ,
0x0804 , 'DOCTYPE'
-- language is Simplified Chinese 0x0804
execute sp_fulltext_table 'DOCBASE' , 'activate'
execute sp_fulltext_catalog 'ft_test','start_full'
EXEC sp_tableoption 'DOCBASE', 'text in row', 'ON'
EXEC sp_fulltext_table 'DOCBASE', 'Start_change_tracking'
EXEC
sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
6. put some docs of type .doc or .xls or .ppt into table
docbase by ASP.net applications
7. select kbid , doctype from docbase where freetext (
dcont , 'ChineseChars')
nothing returned. But i'm sure the content of the docs
include the 'ChineseChars' !
any adea ? thanks !
can you issue this query for me in the database you are Full Text indexing?
select FulltextCatalogProperty(N'test', N'ItemCount')
Also can you look for any messages in your event log from Microsoft Search
with an event ID of 3018 and post the text of this message here?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:02be01c4b804$61ab6920$a501280a@.phx.gbl...
> I configure a table with FT index , but when i search the
> table , nothing returned , why ?
> Windows 2003 EE (Chinese Edition)
> SQL Server 2k + SP3 (Chinese Edition)
> 1. makefile C:\Program Files\Microsoft SQL
> Server\MSSQL\FTDATA\SQLServer\Config\noise.chs
> 2. start the MSSearch service with local system account.
> 3. connect to SQL Database with isqlw
> create table docbase , the type of dcont field is image
> and to store my document of word,excel,ppt,htm etc.
> 4. enable Fulltext of database
> sp_fulltext_database 'enable'
> 5. create catalogs
> execute
> sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'
> execute
> sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE
> '
> execute sp_fulltext_column 'DOCBASE' , 'DCONT' , 'add' ,
> 0x0804 , 'DOCTYPE'
> -- language is Simplified Chinese 0x0804
> execute sp_fulltext_table 'DOCBASE' , 'activate'
> execute sp_fulltext_catalog 'ft_test','start_full'
> EXEC sp_tableoption 'DOCBASE', 'text in row', 'ON'
> EXEC sp_fulltext_table 'DOCBASE', 'Start_change_tracking'
> EXEC
> sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
> 6. put some docs of type .doc or .xls or .ppt into table
> docbase by ASP.net applications
> 7. select kbid , doctype from docbase where freetext (
> dcont , 'ChineseChars')
> nothing returned. But i'm sure the content of the docs
> include the 'ChineseChars' !
> any adea ? thanks !
>
|||Thanks , Cotter
1. > select FulltextCatalogProperty(N'ft_test',
N'ItemCount')
> 2
2. the 3018 message is:
The end of crawl for project <SQLServer SQL0000500005> has
been detected. The
Gatherer successfully processed 6 documents totaling 0K.
It failed to filter 0
documents. URLs could not be reached or were denied access.
3. What's the filter , How to pluggin the filter of
document (word ,excel , pdf etc )into the MS Search
Service ?
>--Original Message--
>can you issue this query for me in the database you are
Full Text indexing?
>select FulltextCatalogProperty(N'test', N'ItemCount')
>Also can you look for any messages in your event log from
Microsoft Search
>with an event ID of 3018 and post the text of this
message here?
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>"rainbow" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:02be01c4b804$61ab6920$a501280a@.phx.gbl...
the[vbcol=seagreen]
sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'[vbcol=seagreen]
sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE[vbcol=seagreen]
sp_fulltext_table 'DOCBASE', 'Start_change_tracking'[vbcol=seagreen]
sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
>
>.
>
|||rainbow,
From the below results, it does appear that at least 5 rows were
successfully FT Indexed. Did you find any other related "Microsoft Search"
or MssCi source event errors? Can I assume that the files () that were
inserted in by your ASP.NET application contained only Chinese characters
and that you issued SQL FTS queries using Chinese characters? If so, and if
the documents also contain English words, could you search on the English
words? There is a know bug ( bug# 356712) with SQL Server 2000, that
"Full-Text: sp_fulltext_column allows you to specify a language on an IMAGE
column, though it has no effect on indexing... DOC" and if you are
searching on the Chinese characters that the FTS query will fail to return
results.
Thanks,
John
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:115701c4ba2c$81213120$a301280a@.phx.gbl...[vbcol=seagreen]
> Thanks , Cotter
> 1. > select FulltextCatalogProperty(N'ft_test',
> N'ItemCount')
> 2. the 3018 message is:
> The end of crawl for project <SQLServer SQL0000500005> has
> been detected. The
> Gatherer successfully processed 6 documents totaling 0K.
> It failed to filter 0
> documents. URLs could not be reached or were denied access.
> 3. What's the filter , How to pluggin the filter of
> document (word ,excel , pdf etc )into the MS Search
> Service ?
>
> Full Text indexing?
> Microsoft Search
> message here?
> message
> the
> sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'
> sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE
> sp_fulltext_table 'DOCBASE', 'Start_change_tracking'
> sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
|||1) ok, it looks like you have indexed at least one row, or perhaps you are
indexing two tables.
2) Is this a recent message for the pubs database?
3) the iFilter for office docs ships with win203 as part of the base
operating system so we know its there.
Can you try to index the authors table and see if you can query this. This
will help to verify that indexing is working currently. I suspect the 3018
is not a recent message. If it is a recent message indexing appears to be
working and your problem could be related to language resources.
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:115701c4ba2c$81213120$a301280a@.phx.gbl...[vbcol=seagreen]
> Thanks , Cotter
> 1. > select FulltextCatalogProperty(N'ft_test',
> N'ItemCount')
> 2. the 3018 message is:
> The end of crawl for project <SQLServer SQL0000500005> has
> been detected. The
> Gatherer successfully processed 6 documents totaling 0K.
> It failed to filter 0
> documents. URLs could not be reached or were denied access.
> 3. What's the filter , How to pluggin the filter of
> document (word ,excel , pdf etc )into the MS Search
> Service ?
>
> Full Text indexing?
> Microsoft Search
> message here?
> message
> the
> sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'
> sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE
> sp_fulltext_table 'DOCBASE', 'Start_change_tracking'
> sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
sql
table , nothing returned , why ?
Windows 2003 EE (Chinese Edition)
SQL Server 2k + SP3 (Chinese Edition)
1. makefile C:\Program Files\Microsoft SQL
Server\MSSQL\FTDATA\SQLServer\Config\noise.chs
2. start the MSSearch service with local system account.
3. connect to SQL Database with isqlw
create table docbase , the type of dcont field is image
and to store my document of word,excel,ppt,htm etc.
4. enable Fulltext of database
sp_fulltext_database 'enable'
5. create catalogs
execute
sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'
execute
sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE
'
execute sp_fulltext_column 'DOCBASE' , 'DCONT' , 'add' ,
0x0804 , 'DOCTYPE'
-- language is Simplified Chinese 0x0804
execute sp_fulltext_table 'DOCBASE' , 'activate'
execute sp_fulltext_catalog 'ft_test','start_full'
EXEC sp_tableoption 'DOCBASE', 'text in row', 'ON'
EXEC sp_fulltext_table 'DOCBASE', 'Start_change_tracking'
EXEC
sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
6. put some docs of type .doc or .xls or .ppt into table
docbase by ASP.net applications
7. select kbid , doctype from docbase where freetext (
dcont , 'ChineseChars')
nothing returned. But i'm sure the content of the docs
include the 'ChineseChars' !
any adea ? thanks !
can you issue this query for me in the database you are Full Text indexing?
select FulltextCatalogProperty(N'test', N'ItemCount')
Also can you look for any messages in your event log from Microsoft Search
with an event ID of 3018 and post the text of this message here?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:02be01c4b804$61ab6920$a501280a@.phx.gbl...
> I configure a table with FT index , but when i search the
> table , nothing returned , why ?
> Windows 2003 EE (Chinese Edition)
> SQL Server 2k + SP3 (Chinese Edition)
> 1. makefile C:\Program Files\Microsoft SQL
> Server\MSSQL\FTDATA\SQLServer\Config\noise.chs
> 2. start the MSSearch service with local system account.
> 3. connect to SQL Database with isqlw
> create table docbase , the type of dcont field is image
> and to store my document of word,excel,ppt,htm etc.
> 4. enable Fulltext of database
> sp_fulltext_database 'enable'
> 5. create catalogs
> execute
> sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'
> execute
> sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE
> '
> execute sp_fulltext_column 'DOCBASE' , 'DCONT' , 'add' ,
> 0x0804 , 'DOCTYPE'
> -- language is Simplified Chinese 0x0804
> execute sp_fulltext_table 'DOCBASE' , 'activate'
> execute sp_fulltext_catalog 'ft_test','start_full'
> EXEC sp_tableoption 'DOCBASE', 'text in row', 'ON'
> EXEC sp_fulltext_table 'DOCBASE', 'Start_change_tracking'
> EXEC
> sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
> 6. put some docs of type .doc or .xls or .ppt into table
> docbase by ASP.net applications
> 7. select kbid , doctype from docbase where freetext (
> dcont , 'ChineseChars')
> nothing returned. But i'm sure the content of the docs
> include the 'ChineseChars' !
> any adea ? thanks !
>
|||Thanks , Cotter
1. > select FulltextCatalogProperty(N'ft_test',
N'ItemCount')
> 2
2. the 3018 message is:
The end of crawl for project <SQLServer SQL0000500005> has
been detected. The
Gatherer successfully processed 6 documents totaling 0K.
It failed to filter 0
documents. URLs could not be reached or were denied access.
3. What's the filter , How to pluggin the filter of
document (word ,excel , pdf etc )into the MS Search
Service ?
>--Original Message--
>can you issue this query for me in the database you are
Full Text indexing?
>select FulltextCatalogProperty(N'test', N'ItemCount')
>Also can you look for any messages in your event log from
Microsoft Search
>with an event ID of 3018 and post the text of this
message here?
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>"rainbow" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:02be01c4b804$61ab6920$a501280a@.phx.gbl...
the[vbcol=seagreen]
sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'[vbcol=seagreen]
sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE[vbcol=seagreen]
sp_fulltext_table 'DOCBASE', 'Start_change_tracking'[vbcol=seagreen]
sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
>
>.
>
|||rainbow,
From the below results, it does appear that at least 5 rows were
successfully FT Indexed. Did you find any other related "Microsoft Search"
or MssCi source event errors? Can I assume that the files () that were
inserted in by your ASP.NET application contained only Chinese characters
and that you issued SQL FTS queries using Chinese characters? If so, and if
the documents also contain English words, could you search on the English
words? There is a know bug ( bug# 356712) with SQL Server 2000, that
"Full-Text: sp_fulltext_column allows you to specify a language on an IMAGE
column, though it has no effect on indexing... DOC" and if you are
searching on the Chinese characters that the FTS query will fail to return
results.
Thanks,
John
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:115701c4ba2c$81213120$a301280a@.phx.gbl...[vbcol=seagreen]
> Thanks , Cotter
> 1. > select FulltextCatalogProperty(N'ft_test',
> N'ItemCount')
> 2. the 3018 message is:
> The end of crawl for project <SQLServer SQL0000500005> has
> been detected. The
> Gatherer successfully processed 6 documents totaling 0K.
> It failed to filter 0
> documents. URLs could not be reached or were denied access.
> 3. What's the filter , How to pluggin the filter of
> document (word ,excel , pdf etc )into the MS Search
> Service ?
>
> Full Text indexing?
> Microsoft Search
> message here?
> message
> the
> sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'
> sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE
> sp_fulltext_table 'DOCBASE', 'Start_change_tracking'
> sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
|||1) ok, it looks like you have indexed at least one row, or perhaps you are
indexing two tables.
2) Is this a recent message for the pubs database?
3) the iFilter for office docs ships with win203 as part of the base
operating system so we know its there.
Can you try to index the authors table and see if you can query this. This
will help to verify that indexing is working currently. I suspect the 3018
is not a recent message. If it is a recent message indexing appears to be
working and your problem could be related to language resources.
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:115701c4ba2c$81213120$a301280a@.phx.gbl...[vbcol=seagreen]
> Thanks , Cotter
> 1. > select FulltextCatalogProperty(N'ft_test',
> N'ItemCount')
> 2. the 3018 message is:
> The end of crawl for project <SQLServer SQL0000500005> has
> been detected. The
> Gatherer successfully processed 6 documents totaling 0K.
> It failed to filter 0
> documents. URLs could not be reached or were denied access.
> 3. What's the filter , How to pluggin the filter of
> document (word ,excel , pdf etc )into the MS Search
> Service ?
>
> Full Text indexing?
> Microsoft Search
> message here?
> message
> the
> sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'
> sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE
> sp_fulltext_table 'DOCBASE', 'Start_change_tracking'
> sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
sql
FT index doesn't auto populate
I have to start by saying I'm new to FTS in SQL Server and could be doing
something stupid. However, everything I've read so far contradicts what I'm
seeing. Here's the issue;
I setup an FTS index in the Adventureworks DB using the Address1 field in
the Address table to query against some sample code I found. I've set up
this index every possible way (auto track, manual track, etc.), but I cannot
get this index to repopulate in realtime consistantly. In other words, if I
go into the table and change a row in the Address table, the index does not
change most of the time. I've seen it change sometimes but I can't find a
pattern for why that happens.
I've used SSMS exclusively to enable FTS on the database and table as well
as defining the index itself. After the change is made to the tables data,
I run the query to search for matches and the results do not reflect the
change I just made. I tried to "force" it by using the SMSS menus and doing
a "Start Incremental Population", nothing happens. I've tried doing a Start
Full Population", and nothing happens UNLESS I have previously set the "Track
Changes" option to OFF during setup. In this one situation I can manually
force the index to rebuild by having Track Changes Automatically turned off
and then executing a "Start Full Population". Of course, this is not a
solution. I need this index to stay current on its own.
What am I doing wrong? Am I missing something fundamental? Is there some
good reading someone can point me to?
Maz
Sorry, I think there's some more info I should mention so no one goes down
the wrong road;
1) I'm using SQl 2005 Standard Edition
2) I've checked teh Crawl Logs and they show nothing around the time I make
the changes. I do see an entry when I do a Full index build. It shows 19000
records indexed with 0 errors.
3) The index seems to be updated when I change the row the first time,
however, if I try to change it again (and put the keyword in the row again)
the index doesn't seem to be updated. This means, that row displays with the
changed word, but, it's ranking still reflects the row before the change.
i.e. the index didn't get updated.
4) The query I'm executing to do the search and rank is as follows;
USE AdventureWorks
GO
SELECT top 100 AddressLine1, KEY_TBL.RANK
FROM Person.Address AS Address INNER JOIN
CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT ("Bay*",
Street WEIGHT(0.9),
View WEIGHT(0.1)
) ' ) AS KEY_TBL
ON Address.AddressID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK desc
GO
Maz
"Spicy Mikey" wrote:
> I have to start by saying I'm new to FTS in SQL Server and could be doing
> something stupid. However, everything I've read so far contradicts what I'm
> seeing. Here's the issue;
> I setup an FTS index in the Adventureworks DB using the Address1 field in
> the Address table to query against some sample code I found. I've set up
> this index every possible way (auto track, manual track, etc.), but I cannot
> get this index to repopulate in realtime consistantly. In other words, if I
> go into the table and change a row in the Address table, the index does not
> change most of the time. I've seen it change sometimes but I can't find a
> pattern for why that happens.
> I've used SSMS exclusively to enable FTS on the database and table as well
> as defining the index itself. After the change is made to the tables data,
> I run the query to search for matches and the results do not reflect the
> change I just made. I tried to "force" it by using the SMSS menus and doing
> a "Start Incremental Population", nothing happens. I've tried doing a Start
> Full Population", and nothing happens UNLESS I have previously set the "Track
> Changes" option to OFF during setup. In this one situation I can manually
> force the index to rebuild by having Track Changes Automatically turned off
> and then executing a "Start Full Population". Of course, this is not a
> solution. I need this index to stay current on its own.
> What am I doing wrong? Am I missing something fundamental? Is there some
> good reading someone can point me to?
>
> --
> Maz
|||Use change tracking. In SQL 2005 SP 1 a population is not automatically set
off after you create your full-text index the way it would occur in RTM. You
need to enable the fulltext index, and you may need to start the full
population. Then change tracking should kick in.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Spicy Mikey" <Maz@.newsgroups.nospam> wrote in message
news:7C90F276-A695-4D0D-9DA4-1FD6E7792A22@.microsoft.com...[vbcol=seagreen]
> Sorry, I think there's some more info I should mention so no one goes down
> the wrong road;
> 1) I'm using SQl 2005 Standard Edition
> 2) I've checked teh Crawl Logs and they show nothing around the time I
> make
> the changes. I do see an entry when I do a Full index build. It shows
> 19000
> records indexed with 0 errors.
> 3) The index seems to be updated when I change the row the first time,
> however, if I try to change it again (and put the keyword in the row
> again)
> the index doesn't seem to be updated. This means, that row displays with
> the
> changed word, but, it's ranking still reflects the row before the change.
> i.e. the index didn't get updated.
> 4) The query I'm executing to do the search and rank is as follows;
> USE AdventureWorks
> GO
> SELECT top 100 AddressLine1, KEY_TBL.RANK
> FROM Person.Address AS Address INNER JOIN
> CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT ("Bay*",
> Street WEIGHT(0.9),
> View WEIGHT(0.1)
> ) ' ) AS KEY_TBL
> ON Address.AddressID = KEY_TBL.[KEY]
> ORDER BY KEY_TBL.RANK desc
> GO
>
> --
> Maz
>
> "Spicy Mikey" wrote:
|||Hi Hilary
Thanks for the quick response. You suggested there are differences in
behaviour between RTM and SP1. I'm using version 9.00.1399.06. I don't
believe I've installed SP1 yet, but, I will make sure I have SP1 installed so
we're all on the same version. Regardless, can you rephrase and elaborate on
your answer? I'm not sure I understand what you are suggesting I do.
You suggest three things;
1) You say to use change tracking. That is one of the scenerio's I tried
and it didn't seem to help. In 2005 we seem to have three options for Change
tracking -- Auto, Manual, and None. I've tried all three. The documentation
says Auto should provide near realtime updates to the index
2) You say to "enable the fulltext index". I do not see an option for that.
I see how I need to enable full text indexing for the database before
creating a FT index. That has been done already
3) You say to kick off the full index population. Not sure what you mean.
The full population kicks off automatically when you first create the index
(with Auto selected). Also, I've tried to manually perform a Full population
index. That doesn't help
One more thing, the wizard is not able to create it's catalog maintenance
job at the end of the wizard steps. The job gets created but I get an error
40 creating the job step that says Named Pipes cannot connect to the server.
It suggests I may not have remote access turned on. However, I have Named
Pipes and TCP access turned on, and, in fact can connect to the database just
fine with the application software using OLEDB. That doesn't seem to be the
problem. Any suggestions on this? Can the two problems be related? This is
a very vanilla installation of SQL on Windows 2003. I haven't changed
anything yet.
Maz
"Hilary Cotter" wrote:
> Use change tracking. In SQL 2005 SP 1 a population is not automatically set
> off after you create your full-text index the way it would occur in RTM. You
> need to enable the fulltext index, and you may need to start the full
> population. Then change tracking should kick in.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Spicy Mikey" <Maz@.newsgroups.nospam> wrote in message
> news:7C90F276-A695-4D0D-9DA4-1FD6E7792A22@.microsoft.com...
>
>
|||Yes there are differences. Essentially when you create a full-text index
through the wizard in SQL 2005 RTM it automatically kicks off change
tracking (if you have accepted the defaults). This involves a
full-population after/during which changes are tracked. As customers
complained to MS about the locking which occurred during the population (at
least this is what I assume), SP1 changes this behavior. Full-text indexes
built through the wizard are not enabled, you must enable them and then
start change tracking - which starts the full-population.
I have attempted to answer your questions inline.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Spicy Mikey" <Maz@.newsgroups.nospam> wrote in message
news:1D9391B9-E15F-40C0-851C-2446876B5D85@.microsoft.com...
> Hi Hilary
> Thanks for the quick response. You suggested there are differences in
> behaviour between RTM and SP1. I'm using version 9.00.1399.06. I don't
> believe I've installed SP1 yet, but, I will make sure I have SP1 installed
> so
> we're all on the same version. Regardless, can you rephrase and elaborate
> on
> your answer? I'm not sure I understand what you are suggesting I do.
> You suggest three things;
> 1) You say to use change tracking. That is one of the scenerio's I tried
> and it didn't seem to help. In 2005 we seem to have three options for
> Change
> tracking -- Auto, Manual, and None. I've tried all three. The
> documentation
> says Auto should provide near realtime updates to the index
Use Auto
> 2) You say to "enable the fulltext index". I do not see an option for
> that.
> I see how I need to enable full text indexing for the database before
> creating a FT index. That has been done already
right click on your table, select Full-Text Index and then see if the option
to Enable Full-Text Index is enabled. If not, it probably is already
enabled.
> 3) You say to kick off the full index population. Not sure what you mean.
> The full population kicks off automatically when you first create the
> index
> (with Auto selected). Also, I've tried to manually perform a Full
> population
> index. That doesn't help
>
Yes, with RTM this was the behavior after creating a full-text index.
> One more thing, the wizard is not able to create it's catalog maintenance
> job at the end of the wizard steps. The job gets created but I get an
> error
> 40 creating the job step that says Named Pipes cannot connect to the
> server.
> It suggests I may not have remote access turned on. However, I have Named
> Pipes and TCP access turned on, and, in fact can connect to the database
> just
> fine with the application software using OLEDB. That doesn't seem to be
> the
> problem. Any suggestions on this? Can the two problems be related? This
> is
> a very vanilla installation of SQL on Windows 2003. I haven't changed
> anything yet.
>
I am not sure why this is. It almost sounds like the account you are
creating the full-text index under does not have rights. Are you logged on
as the sa/administrator.[vbcol=seagreen]
> --
> Maz
>
> "Hilary Cotter" wrote:
|||Thanks again Hilary for sticking with me on this and offering more suggestions.
Unfortunately, nothing works. I have a hard time believing this is due to
my own stupidity, yet, I also have a hard time believing that this FTS
feature flat out doesn't work. For my sake, and anyone else following this
thread, can you do me a favor and do a simple test on your end to see if you
can duplicate this behavior?
1) Load the AdventureWorks database, enable FTS for Auto track, and setup an
FTS index on the Address1 field in the Person.Address table
2) Next, execute this query in SMSS
USE AdventureWorks
GO
SELECT AddressLine1, KEY_TBL.RANK
FROM Person.Address AS Address INNER JOIN
CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT ("Bay*", Street
WEIGHT(0.9), View WEIGHT(0.1)) ' ) AS KEY_TBL
ON Address.AddressID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK desc
GO
4) Note the row that gets returned for "1111Bay Street". Open the table and
change that record in the table to be "1111 X Street", then run that query
again. You should see the record excluded from the query's top results.
That's what we expect. That seems to work fine.
5) Now, add the word "Bay" back into that row and run the query a third
time. I expect to see that row reappear in the top results. However, that's
not what happens. The row is way down the list near the bottom indicating to
me that the index did not get the last change and still has that row indexed
with the "X".
I've done this 10 times. I've eliminated every stupid mistake possible.
Either my expectations are wrong OR Microsoft has a serious bug in this
feature.
If this works for you then I AM stupid and I'll just need to open a ticket
with MS and do some one on one troubleshooting with someone in the SQL team.
Thanks for sticking with me!
Mike
"Hilary Cotter" wrote:
> Yes there are differences. Essentially when you create a full-text index
> through the wizard in SQL 2005 RTM it automatically kicks off change
> tracking (if you have accepted the defaults). This involves a
> full-population after/during which changes are tracked. As customers
> complained to MS about the locking which occurred during the population (at
> least this is what I assume), SP1 changes this behavior. Full-text indexes
> built through the wizard are not enabled, you must enable them and then
> start change tracking - which starts the full-population.
> I have attempted to answer your questions inline.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Spicy Mikey" <Maz@.newsgroups.nospam> wrote in message
> news:1D9391B9-E15F-40C0-851C-2446876B5D85@.microsoft.com...
> Use Auto
>
> right click on your table, select Full-Text Index and then see if the option
> to Enable Full-Text Index is enabled. If not, it probably is already
> enabled.
> Yes, with RTM this was the behavior after creating a full-text index.
> I am not sure why this is. It almost sounds like the account you are
> creating the full-text index under does not have rights. Are you logged on
> as the sa/administrator.
>
>
|||This may sound stupid, but - have you given FTS a couple of seconds to finish
its updates?
ML
http://milambda.blogspot.com/
|||Oh yea, it still hasn't updated, so, I suppose you can say I gave it 3 days
The strange thing is that it only involves adding index words back into the
row. For some reason the index gets updated immediately when I take a key
word away but if I immediately add it back in, the index doesn't reflect that
second change.
I'm not saying it couldn't be something I'm doing wrong, however, assume all
the stupid mistakes have been considered. It's probably something more
complicated then can be addressed on a message board. I'll probably have to
open a ticket with MS. I'll post the answer here for everyone's benefit
Maz
"ML" wrote:
> This may sound stupid, but - have you given FTS a couple of seconds to finish
> its updates?
>
> ML
> --
> http://milambda.blogspot.com/
something stupid. However, everything I've read so far contradicts what I'm
seeing. Here's the issue;
I setup an FTS index in the Adventureworks DB using the Address1 field in
the Address table to query against some sample code I found. I've set up
this index every possible way (auto track, manual track, etc.), but I cannot
get this index to repopulate in realtime consistantly. In other words, if I
go into the table and change a row in the Address table, the index does not
change most of the time. I've seen it change sometimes but I can't find a
pattern for why that happens.
I've used SSMS exclusively to enable FTS on the database and table as well
as defining the index itself. After the change is made to the tables data,
I run the query to search for matches and the results do not reflect the
change I just made. I tried to "force" it by using the SMSS menus and doing
a "Start Incremental Population", nothing happens. I've tried doing a Start
Full Population", and nothing happens UNLESS I have previously set the "Track
Changes" option to OFF during setup. In this one situation I can manually
force the index to rebuild by having Track Changes Automatically turned off
and then executing a "Start Full Population". Of course, this is not a
solution. I need this index to stay current on its own.
What am I doing wrong? Am I missing something fundamental? Is there some
good reading someone can point me to?
Maz
Sorry, I think there's some more info I should mention so no one goes down
the wrong road;
1) I'm using SQl 2005 Standard Edition
2) I've checked teh Crawl Logs and they show nothing around the time I make
the changes. I do see an entry when I do a Full index build. It shows 19000
records indexed with 0 errors.
3) The index seems to be updated when I change the row the first time,
however, if I try to change it again (and put the keyword in the row again)
the index doesn't seem to be updated. This means, that row displays with the
changed word, but, it's ranking still reflects the row before the change.
i.e. the index didn't get updated.
4) The query I'm executing to do the search and rank is as follows;
USE AdventureWorks
GO
SELECT top 100 AddressLine1, KEY_TBL.RANK
FROM Person.Address AS Address INNER JOIN
CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT ("Bay*",
Street WEIGHT(0.9),
View WEIGHT(0.1)
) ' ) AS KEY_TBL
ON Address.AddressID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK desc
GO
Maz
"Spicy Mikey" wrote:
> I have to start by saying I'm new to FTS in SQL Server and could be doing
> something stupid. However, everything I've read so far contradicts what I'm
> seeing. Here's the issue;
> I setup an FTS index in the Adventureworks DB using the Address1 field in
> the Address table to query against some sample code I found. I've set up
> this index every possible way (auto track, manual track, etc.), but I cannot
> get this index to repopulate in realtime consistantly. In other words, if I
> go into the table and change a row in the Address table, the index does not
> change most of the time. I've seen it change sometimes but I can't find a
> pattern for why that happens.
> I've used SSMS exclusively to enable FTS on the database and table as well
> as defining the index itself. After the change is made to the tables data,
> I run the query to search for matches and the results do not reflect the
> change I just made. I tried to "force" it by using the SMSS menus and doing
> a "Start Incremental Population", nothing happens. I've tried doing a Start
> Full Population", and nothing happens UNLESS I have previously set the "Track
> Changes" option to OFF during setup. In this one situation I can manually
> force the index to rebuild by having Track Changes Automatically turned off
> and then executing a "Start Full Population". Of course, this is not a
> solution. I need this index to stay current on its own.
> What am I doing wrong? Am I missing something fundamental? Is there some
> good reading someone can point me to?
>
> --
> Maz
|||Use change tracking. In SQL 2005 SP 1 a population is not automatically set
off after you create your full-text index the way it would occur in RTM. You
need to enable the fulltext index, and you may need to start the full
population. Then change tracking should kick in.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Spicy Mikey" <Maz@.newsgroups.nospam> wrote in message
news:7C90F276-A695-4D0D-9DA4-1FD6E7792A22@.microsoft.com...[vbcol=seagreen]
> Sorry, I think there's some more info I should mention so no one goes down
> the wrong road;
> 1) I'm using SQl 2005 Standard Edition
> 2) I've checked teh Crawl Logs and they show nothing around the time I
> make
> the changes. I do see an entry when I do a Full index build. It shows
> 19000
> records indexed with 0 errors.
> 3) The index seems to be updated when I change the row the first time,
> however, if I try to change it again (and put the keyword in the row
> again)
> the index doesn't seem to be updated. This means, that row displays with
> the
> changed word, but, it's ranking still reflects the row before the change.
> i.e. the index didn't get updated.
> 4) The query I'm executing to do the search and rank is as follows;
> USE AdventureWorks
> GO
> SELECT top 100 AddressLine1, KEY_TBL.RANK
> FROM Person.Address AS Address INNER JOIN
> CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT ("Bay*",
> Street WEIGHT(0.9),
> View WEIGHT(0.1)
> ) ' ) AS KEY_TBL
> ON Address.AddressID = KEY_TBL.[KEY]
> ORDER BY KEY_TBL.RANK desc
> GO
>
> --
> Maz
>
> "Spicy Mikey" wrote:
|||Hi Hilary
Thanks for the quick response. You suggested there are differences in
behaviour between RTM and SP1. I'm using version 9.00.1399.06. I don't
believe I've installed SP1 yet, but, I will make sure I have SP1 installed so
we're all on the same version. Regardless, can you rephrase and elaborate on
your answer? I'm not sure I understand what you are suggesting I do.
You suggest three things;
1) You say to use change tracking. That is one of the scenerio's I tried
and it didn't seem to help. In 2005 we seem to have three options for Change
tracking -- Auto, Manual, and None. I've tried all three. The documentation
says Auto should provide near realtime updates to the index
2) You say to "enable the fulltext index". I do not see an option for that.
I see how I need to enable full text indexing for the database before
creating a FT index. That has been done already
3) You say to kick off the full index population. Not sure what you mean.
The full population kicks off automatically when you first create the index
(with Auto selected). Also, I've tried to manually perform a Full population
index. That doesn't help
One more thing, the wizard is not able to create it's catalog maintenance
job at the end of the wizard steps. The job gets created but I get an error
40 creating the job step that says Named Pipes cannot connect to the server.
It suggests I may not have remote access turned on. However, I have Named
Pipes and TCP access turned on, and, in fact can connect to the database just
fine with the application software using OLEDB. That doesn't seem to be the
problem. Any suggestions on this? Can the two problems be related? This is
a very vanilla installation of SQL on Windows 2003. I haven't changed
anything yet.
Maz
"Hilary Cotter" wrote:
> Use change tracking. In SQL 2005 SP 1 a population is not automatically set
> off after you create your full-text index the way it would occur in RTM. You
> need to enable the fulltext index, and you may need to start the full
> population. Then change tracking should kick in.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Spicy Mikey" <Maz@.newsgroups.nospam> wrote in message
> news:7C90F276-A695-4D0D-9DA4-1FD6E7792A22@.microsoft.com...
>
>
|||Yes there are differences. Essentially when you create a full-text index
through the wizard in SQL 2005 RTM it automatically kicks off change
tracking (if you have accepted the defaults). This involves a
full-population after/during which changes are tracked. As customers
complained to MS about the locking which occurred during the population (at
least this is what I assume), SP1 changes this behavior. Full-text indexes
built through the wizard are not enabled, you must enable them and then
start change tracking - which starts the full-population.
I have attempted to answer your questions inline.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Spicy Mikey" <Maz@.newsgroups.nospam> wrote in message
news:1D9391B9-E15F-40C0-851C-2446876B5D85@.microsoft.com...
> Hi Hilary
> Thanks for the quick response. You suggested there are differences in
> behaviour between RTM and SP1. I'm using version 9.00.1399.06. I don't
> believe I've installed SP1 yet, but, I will make sure I have SP1 installed
> so
> we're all on the same version. Regardless, can you rephrase and elaborate
> on
> your answer? I'm not sure I understand what you are suggesting I do.
> You suggest three things;
> 1) You say to use change tracking. That is one of the scenerio's I tried
> and it didn't seem to help. In 2005 we seem to have three options for
> Change
> tracking -- Auto, Manual, and None. I've tried all three. The
> documentation
> says Auto should provide near realtime updates to the index
Use Auto
> 2) You say to "enable the fulltext index". I do not see an option for
> that.
> I see how I need to enable full text indexing for the database before
> creating a FT index. That has been done already
right click on your table, select Full-Text Index and then see if the option
to Enable Full-Text Index is enabled. If not, it probably is already
enabled.
> 3) You say to kick off the full index population. Not sure what you mean.
> The full population kicks off automatically when you first create the
> index
> (with Auto selected). Also, I've tried to manually perform a Full
> population
> index. That doesn't help
>
Yes, with RTM this was the behavior after creating a full-text index.
> One more thing, the wizard is not able to create it's catalog maintenance
> job at the end of the wizard steps. The job gets created but I get an
> error
> 40 creating the job step that says Named Pipes cannot connect to the
> server.
> It suggests I may not have remote access turned on. However, I have Named
> Pipes and TCP access turned on, and, in fact can connect to the database
> just
> fine with the application software using OLEDB. That doesn't seem to be
> the
> problem. Any suggestions on this? Can the two problems be related? This
> is
> a very vanilla installation of SQL on Windows 2003. I haven't changed
> anything yet.
>
I am not sure why this is. It almost sounds like the account you are
creating the full-text index under does not have rights. Are you logged on
as the sa/administrator.[vbcol=seagreen]
> --
> Maz
>
> "Hilary Cotter" wrote:
|||Thanks again Hilary for sticking with me on this and offering more suggestions.
Unfortunately, nothing works. I have a hard time believing this is due to
my own stupidity, yet, I also have a hard time believing that this FTS
feature flat out doesn't work. For my sake, and anyone else following this
thread, can you do me a favor and do a simple test on your end to see if you
can duplicate this behavior?
1) Load the AdventureWorks database, enable FTS for Auto track, and setup an
FTS index on the Address1 field in the Person.Address table
2) Next, execute this query in SMSS
USE AdventureWorks
GO
SELECT AddressLine1, KEY_TBL.RANK
FROM Person.Address AS Address INNER JOIN
CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT ("Bay*", Street
WEIGHT(0.9), View WEIGHT(0.1)) ' ) AS KEY_TBL
ON Address.AddressID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK desc
GO
4) Note the row that gets returned for "1111Bay Street". Open the table and
change that record in the table to be "1111 X Street", then run that query
again. You should see the record excluded from the query's top results.
That's what we expect. That seems to work fine.
5) Now, add the word "Bay" back into that row and run the query a third
time. I expect to see that row reappear in the top results. However, that's
not what happens. The row is way down the list near the bottom indicating to
me that the index did not get the last change and still has that row indexed
with the "X".
I've done this 10 times. I've eliminated every stupid mistake possible.
Either my expectations are wrong OR Microsoft has a serious bug in this
feature.
If this works for you then I AM stupid and I'll just need to open a ticket
with MS and do some one on one troubleshooting with someone in the SQL team.
Thanks for sticking with me!
Mike
"Hilary Cotter" wrote:
> Yes there are differences. Essentially when you create a full-text index
> through the wizard in SQL 2005 RTM it automatically kicks off change
> tracking (if you have accepted the defaults). This involves a
> full-population after/during which changes are tracked. As customers
> complained to MS about the locking which occurred during the population (at
> least this is what I assume), SP1 changes this behavior. Full-text indexes
> built through the wizard are not enabled, you must enable them and then
> start change tracking - which starts the full-population.
> I have attempted to answer your questions inline.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Spicy Mikey" <Maz@.newsgroups.nospam> wrote in message
> news:1D9391B9-E15F-40C0-851C-2446876B5D85@.microsoft.com...
> Use Auto
>
> right click on your table, select Full-Text Index and then see if the option
> to Enable Full-Text Index is enabled. If not, it probably is already
> enabled.
> Yes, with RTM this was the behavior after creating a full-text index.
> I am not sure why this is. It almost sounds like the account you are
> creating the full-text index under does not have rights. Are you logged on
> as the sa/administrator.
>
>
|||This may sound stupid, but - have you given FTS a couple of seconds to finish
its updates?
ML
http://milambda.blogspot.com/
|||Oh yea, it still hasn't updated, so, I suppose you can say I gave it 3 days
The strange thing is that it only involves adding index words back into the
row. For some reason the index gets updated immediately when I take a key
word away but if I immediately add it back in, the index doesn't reflect that
second change.
I'm not saying it couldn't be something I'm doing wrong, however, assume all
the stupid mistakes have been considered. It's probably something more
complicated then can be addressed on a message board. I'll probably have to
open a ticket with MS. I'll post the answer here for everyone's benefit
Maz
"ML" wrote:
> This may sound stupid, but - have you given FTS a couple of seconds to finish
> its updates?
>
> ML
> --
> http://milambda.blogspot.com/
FT Catalog becomes corrupt after server crashes during incrmental pop.
Has anybody every expirenced this, and if so, is there any way to
repair the index as opposed to Rebuilding the index and then starting
a full population on it. My table that's full-text indexed has over 12
million records in it, and it takes 5+ days to do a full population.
Any ideas/advice would be greatly appreciated.
Regards,
Robert Steel
I've run across it. I have not being able to find a way to do this. in our
case the problem was due to faulty disks IIRC.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Zoidberg" <dizfunksional@.hotmail.com> wrote in message
news:1170176533.145348.285510@.l53g2000cwa.googlegr oups.com...
> Has anybody every expirenced this, and if so, is there any way to
> repair the index as opposed to Rebuilding the index and then starting
> a full population on it. My table that's full-text indexed has over 12
> million records in it, and it takes 5+ days to do a full population.
> Any ideas/advice would be greatly appreciated.
> Regards,
> Robert Steel
>
repair the index as opposed to Rebuilding the index and then starting
a full population on it. My table that's full-text indexed has over 12
million records in it, and it takes 5+ days to do a full population.
Any ideas/advice would be greatly appreciated.
Regards,
Robert Steel
I've run across it. I have not being able to find a way to do this. in our
case the problem was due to faulty disks IIRC.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Zoidberg" <dizfunksional@.hotmail.com> wrote in message
news:1170176533.145348.285510@.l53g2000cwa.googlegr oups.com...
> Has anybody every expirenced this, and if so, is there any way to
> repair the index as opposed to Rebuilding the index and then starting
> a full population on it. My table that's full-text indexed has over 12
> million records in it, and it takes 5+ days to do a full population.
> Any ideas/advice would be greatly appreciated.
> Regards,
> Robert Steel
>
Monday, March 12, 2012
frequent index corruption
Hi All,
I have a table on SQL 2000 (SP4) with about 55k records and was not
having any problems with it for some time. Recently, I am getting
index corruption errors such as:
Could not find the index entry for RID
'3601000001001c0031002d0044004b0058002d0
03800340037003300' in index
page (1:1396706), index ID 3, database <db_name>
I've tried researching on this matter, but there seems to be no clear
explanation on how or why these are happening. I have looked at the
possibility of a hardware problem, but the same set of indexes on two
replication subscribers (from a single publisher) have the corruption.
So it would be quite unlikely that both subscribers, which are
different machines on different locations, would have the same
hardware problem at the same time. The publisher does not have this
problem.
Second, if I drop the corrupt indexes, the errors go away, and if I
recreate them the corruption is consistently present. I did the
dropping and creation a couple of times, and it is very consistent,
which is odd.
I came across KB 822747 (http://support.microsoft.com/kb/822747),
which was quite similar to what I am having since my database is on a
binary collation and the involve indexes have several unicode columns,
but I'm not sure if there is any relationship there. The issue in the
KB was said to be fixed on SP4, but I already got SP4.
DBCC does not return any error, btw.
I hope someone can enlighten me on this or point me to the right
direction.
Thanks,
AramidThis sounds like a case for MS support. I'd run through method#2 in the
stated KB and collect the result. Along with your server info (@.@.version),
it should be a very quick call. You will need a credit to open a case, but
shouldn't be charged if it's indeed a bug.
-oj
"Aramid" <aramid@.hotmail.com> wrote in message
news:ml6d82tca5vf3gr71od5sc8fj5kjp5qtkv@.
4ax.com...
> Hi All,
> I have a table on SQL 2000 (SP4) with about 55k records and was not
> having any problems with it for some time. Recently, I am getting
> index corruption errors such as:
> Could not find the index entry for RID
> '3601000001001c0031002d0044004b0058002d0
03800340037003300' in index
> page (1:1396706), index ID 3, database <db_name>
> I've tried researching on this matter, but there seems to be no clear
> explanation on how or why these are happening. I have looked at the
> possibility of a hardware problem, but the same set of indexes on two
> replication subscribers (from a single publisher) have the corruption.
> So it would be quite unlikely that both subscribers, which are
> different machines on different locations, would have the same
> hardware problem at the same time. The publisher does not have this
> problem.
> Second, if I drop the corrupt indexes, the errors go away, and if I
> recreate them the corruption is consistently present. I did the
> dropping and creation a couple of times, and it is very consistent,
> which is odd.
> I came across KB 822747 (http://support.microsoft.com/kb/822747),
> which was quite similar to what I am having since my database is on a
> binary collation and the involve indexes have several unicode columns,
> but I'm not sure if there is any relationship there. The issue in the
> KB was said to be fixed on SP4, but I already got SP4.
> DBCC does not return any error, btw.
> I hope someone can enlighten me on this or point me to the right
> direction.
> Thanks,
> Aramid
I have a table on SQL 2000 (SP4) with about 55k records and was not
having any problems with it for some time. Recently, I am getting
index corruption errors such as:
Could not find the index entry for RID
'3601000001001c0031002d0044004b0058002d0
03800340037003300' in index
page (1:1396706), index ID 3, database <db_name>
I've tried researching on this matter, but there seems to be no clear
explanation on how or why these are happening. I have looked at the
possibility of a hardware problem, but the same set of indexes on two
replication subscribers (from a single publisher) have the corruption.
So it would be quite unlikely that both subscribers, which are
different machines on different locations, would have the same
hardware problem at the same time. The publisher does not have this
problem.
Second, if I drop the corrupt indexes, the errors go away, and if I
recreate them the corruption is consistently present. I did the
dropping and creation a couple of times, and it is very consistent,
which is odd.
I came across KB 822747 (http://support.microsoft.com/kb/822747),
which was quite similar to what I am having since my database is on a
binary collation and the involve indexes have several unicode columns,
but I'm not sure if there is any relationship there. The issue in the
KB was said to be fixed on SP4, but I already got SP4.
DBCC does not return any error, btw.
I hope someone can enlighten me on this or point me to the right
direction.
Thanks,
AramidThis sounds like a case for MS support. I'd run through method#2 in the
stated KB and collect the result. Along with your server info (@.@.version),
it should be a very quick call. You will need a credit to open a case, but
shouldn't be charged if it's indeed a bug.
-oj
"Aramid" <aramid@.hotmail.com> wrote in message
news:ml6d82tca5vf3gr71od5sc8fj5kjp5qtkv@.
4ax.com...
> Hi All,
> I have a table on SQL 2000 (SP4) with about 55k records and was not
> having any problems with it for some time. Recently, I am getting
> index corruption errors such as:
> Could not find the index entry for RID
> '3601000001001c0031002d0044004b0058002d0
03800340037003300' in index
> page (1:1396706), index ID 3, database <db_name>
> I've tried researching on this matter, but there seems to be no clear
> explanation on how or why these are happening. I have looked at the
> possibility of a hardware problem, but the same set of indexes on two
> replication subscribers (from a single publisher) have the corruption.
> So it would be quite unlikely that both subscribers, which are
> different machines on different locations, would have the same
> hardware problem at the same time. The publisher does not have this
> problem.
> Second, if I drop the corrupt indexes, the errors go away, and if I
> recreate them the corruption is consistently present. I did the
> dropping and creation a couple of times, and it is very consistent,
> which is odd.
> I came across KB 822747 (http://support.microsoft.com/kb/822747),
> which was quite similar to what I am having since my database is on a
> binary collation and the involve indexes have several unicode columns,
> but I'm not sure if there is any relationship there. The issue in the
> KB was said to be fixed on SP4, but I already got SP4.
> DBCC does not return any error, btw.
> I hope someone can enlighten me on this or point me to the right
> direction.
> Thanks,
> Aramid
frequent index corruption
Hi All,
I have a table on SQL 2000 (SP4) with about 55k records and was not
having any problems with it for some time. Recently, I am getting
index corruption errors such as:
Could not find the index entry for RID
'3601000001001c0031002d0044004b0058002d003800340037003300' in index
page (1:1396706), index ID 3, database <db_name>
I've tried researching on this matter, but there seems to be no clear
explanation on how or why these are happening. I have looked at the
possibility of a hardware problem, but the same set of indexes on two
replication subscribers (from a single publisher) have the corruption.
So it would be quite unlikely that both subscribers, which are
different machines on different locations, would have the same
hardware problem at the same time. The publisher does not have this
problem.
Second, if I drop the corrupt indexes, the errors go away, and if I
recreate them the corruption is consistently present. I did the
dropping and creation a couple of times, and it is very consistent,
which is odd.
I came across KB 822747 (http://support.microsoft.com/kb/822747),
which was quite similar to what I am having since my database is on a
binary collation and the involve indexes have several unicode columns,
but I'm not sure if there is any relationship there. The issue in the
KB was said to be fixed on SP4, but I already got SP4.
DBCC does not return any error, btw.
I hope someone can enlighten me on this or point me to the right
direction.
Thanks,
AramidThis sounds like a case for MS support. I'd run through method#2 in the
stated KB and collect the result. Along with your server info (@.@.version),
it should be a very quick call. You will need a credit to open a case, but
shouldn't be charged if it's indeed a bug.
--
-oj
"Aramid" <aramid@.hotmail.com> wrote in message
news:ml6d82tca5vf3gr71od5sc8fj5kjp5qtkv@.4ax.com...
> Hi All,
> I have a table on SQL 2000 (SP4) with about 55k records and was not
> having any problems with it for some time. Recently, I am getting
> index corruption errors such as:
> Could not find the index entry for RID
> '3601000001001c0031002d0044004b0058002d003800340037003300' in index
> page (1:1396706), index ID 3, database <db_name>
> I've tried researching on this matter, but there seems to be no clear
> explanation on how or why these are happening. I have looked at the
> possibility of a hardware problem, but the same set of indexes on two
> replication subscribers (from a single publisher) have the corruption.
> So it would be quite unlikely that both subscribers, which are
> different machines on different locations, would have the same
> hardware problem at the same time. The publisher does not have this
> problem.
> Second, if I drop the corrupt indexes, the errors go away, and if I
> recreate them the corruption is consistently present. I did the
> dropping and creation a couple of times, and it is very consistent,
> which is odd.
> I came across KB 822747 (http://support.microsoft.com/kb/822747),
> which was quite similar to what I am having since my database is on a
> binary collation and the involve indexes have several unicode columns,
> but I'm not sure if there is any relationship there. The issue in the
> KB was said to be fixed on SP4, but I already got SP4.
> DBCC does not return any error, btw.
> I hope someone can enlighten me on this or point me to the right
> direction.
> Thanks,
> Aramid
I have a table on SQL 2000 (SP4) with about 55k records and was not
having any problems with it for some time. Recently, I am getting
index corruption errors such as:
Could not find the index entry for RID
'3601000001001c0031002d0044004b0058002d003800340037003300' in index
page (1:1396706), index ID 3, database <db_name>
I've tried researching on this matter, but there seems to be no clear
explanation on how or why these are happening. I have looked at the
possibility of a hardware problem, but the same set of indexes on two
replication subscribers (from a single publisher) have the corruption.
So it would be quite unlikely that both subscribers, which are
different machines on different locations, would have the same
hardware problem at the same time. The publisher does not have this
problem.
Second, if I drop the corrupt indexes, the errors go away, and if I
recreate them the corruption is consistently present. I did the
dropping and creation a couple of times, and it is very consistent,
which is odd.
I came across KB 822747 (http://support.microsoft.com/kb/822747),
which was quite similar to what I am having since my database is on a
binary collation and the involve indexes have several unicode columns,
but I'm not sure if there is any relationship there. The issue in the
KB was said to be fixed on SP4, but I already got SP4.
DBCC does not return any error, btw.
I hope someone can enlighten me on this or point me to the right
direction.
Thanks,
AramidThis sounds like a case for MS support. I'd run through method#2 in the
stated KB and collect the result. Along with your server info (@.@.version),
it should be a very quick call. You will need a credit to open a case, but
shouldn't be charged if it's indeed a bug.
--
-oj
"Aramid" <aramid@.hotmail.com> wrote in message
news:ml6d82tca5vf3gr71od5sc8fj5kjp5qtkv@.4ax.com...
> Hi All,
> I have a table on SQL 2000 (SP4) with about 55k records and was not
> having any problems with it for some time. Recently, I am getting
> index corruption errors such as:
> Could not find the index entry for RID
> '3601000001001c0031002d0044004b0058002d003800340037003300' in index
> page (1:1396706), index ID 3, database <db_name>
> I've tried researching on this matter, but there seems to be no clear
> explanation on how or why these are happening. I have looked at the
> possibility of a hardware problem, but the same set of indexes on two
> replication subscribers (from a single publisher) have the corruption.
> So it would be quite unlikely that both subscribers, which are
> different machines on different locations, would have the same
> hardware problem at the same time. The publisher does not have this
> problem.
> Second, if I drop the corrupt indexes, the errors go away, and if I
> recreate them the corruption is consistently present. I did the
> dropping and creation a couple of times, and it is very consistent,
> which is odd.
> I came across KB 822747 (http://support.microsoft.com/kb/822747),
> which was quite similar to what I am having since my database is on a
> binary collation and the involve indexes have several unicode columns,
> but I'm not sure if there is any relationship there. The issue in the
> KB was said to be fixed on SP4, but I already got SP4.
> DBCC does not return any error, btw.
> I hope someone can enlighten me on this or point me to the right
> direction.
> Thanks,
> Aramid
Sunday, February 26, 2012
Free space on an index
On SQL 2000, how would you determine:
1. the percent free on an index?
2. the available page size?
Thanks
Message posted via http://www.sqlmonster.com
Indexes don't have "free space" - when pages need to be added to the index,
they are allocated and linked in.
In SQL Server the page size is always the same, 8KB. If you're referring to
the available free space on a particular page, there isn't any documented
way to do this. Perhaps you're talking about FILLFACTOR? You can specify a
FILLFACTOR for index creation that will leave a percentage of space on the
leaf level index pages free, but it is only maintained at index creation
time, not during normal operations. You can use INDEXPROPERTY (<table>,
<index>, 'IndexFillFactor') to get this value...
If I'm misunderstanding your questions, please let me know.
Thanks.
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:44a0ef20a81f45cb9afa95ac557ba389@.SQLMonster.c om...
> On SQL 2000, how would you determine:
> 1. the percent free on an index?
> 2. the available page size?
> Thanks
> --
> Message posted via http://www.sqlmonster.com
|||Okay. Thanks for getting me straight.
What is happening is I have inserts into a table that are timing out, but
updates on the table are proceeding. Is there a possibility of the index
size growing, or new pages being added to the index that may be causing
these insert timeouts?
Message posted via http://www.sqlmonster.com
1. the percent free on an index?
2. the available page size?
Thanks
Message posted via http://www.sqlmonster.com
Indexes don't have "free space" - when pages need to be added to the index,
they are allocated and linked in.
In SQL Server the page size is always the same, 8KB. If you're referring to
the available free space on a particular page, there isn't any documented
way to do this. Perhaps you're talking about FILLFACTOR? You can specify a
FILLFACTOR for index creation that will leave a percentage of space on the
leaf level index pages free, but it is only maintained at index creation
time, not during normal operations. You can use INDEXPROPERTY (<table>,
<index>, 'IndexFillFactor') to get this value...
If I'm misunderstanding your questions, please let me know.
Thanks.
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:44a0ef20a81f45cb9afa95ac557ba389@.SQLMonster.c om...
> On SQL 2000, how would you determine:
> 1. the percent free on an index?
> 2. the available page size?
> Thanks
> --
> Message posted via http://www.sqlmonster.com
|||Okay. Thanks for getting me straight.
What is happening is I have inserts into a table that are timing out, but
updates on the table are proceeding. Is there a possibility of the index
size growing, or new pages being added to the index that may be causing
these insert timeouts?
Message posted via http://www.sqlmonster.com
Free space on an index
On SQL 2000, how would you determine:
1. the percent free on an index?
2. the available page size?
Thanks
--
Message posted via http://www.sqlmonster.comIndexes don't have "free space" - when pages need to be added to the index,
they are allocated and linked in.
In SQL Server the page size is always the same, 8KB. If you're referring to
the available free space on a particular page, there isn't any documented
way to do this. Perhaps you're talking about FILLFACTOR? You can specify a
FILLFACTOR for index creation that will leave a percentage of space on the
leaf level index pages free, but it is only maintained at index creation
time, not during normal operations. You can use INDEXPROPERTY (<table>,
<index>, 'IndexFillFactor') to get this value...
If I'm misunderstanding your questions, please let me know.
Thanks.
--
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:44a0ef20a81f45cb9afa95ac557ba389@.SQLMonster.com...
> On SQL 2000, how would you determine:
> 1. the percent free on an index?
> 2. the available page size?
> Thanks
> --
> Message posted via http://www.sqlmonster.com|||Okay. Thanks for getting me straight.
What is happening is I have inserts into a table that are timing out, but
updates on the table are proceeding. Is there a possibility of the index
size growing, or new pages being added to the index that may be causing
these insert timeouts?
--
Message posted via http://www.sqlmonster.com
1. the percent free on an index?
2. the available page size?
Thanks
--
Message posted via http://www.sqlmonster.comIndexes don't have "free space" - when pages need to be added to the index,
they are allocated and linked in.
In SQL Server the page size is always the same, 8KB. If you're referring to
the available free space on a particular page, there isn't any documented
way to do this. Perhaps you're talking about FILLFACTOR? You can specify a
FILLFACTOR for index creation that will leave a percentage of space on the
leaf level index pages free, but it is only maintained at index creation
time, not during normal operations. You can use INDEXPROPERTY (<table>,
<index>, 'IndexFillFactor') to get this value...
If I'm misunderstanding your questions, please let me know.
Thanks.
--
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:44a0ef20a81f45cb9afa95ac557ba389@.SQLMonster.com...
> On SQL 2000, how would you determine:
> 1. the percent free on an index?
> 2. the available page size?
> Thanks
> --
> Message posted via http://www.sqlmonster.com|||Okay. Thanks for getting me straight.
What is happening is I have inserts into a table that are timing out, but
updates on the table are proceeding. Is there a possibility of the index
size growing, or new pages being added to the index that may be causing
these insert timeouts?
--
Message posted via http://www.sqlmonster.com
Free space on an index
On SQL 2000, how would you determine:
1. the percent free on an index?
2. the available page size?
Thanks
Message posted via http://www.droptable.comIndexes don't have "free space" - when pages need to be added to the index,
they are allocated and linked in.
In SQL Server the page size is always the same, 8KB. If you're referring to
the available free space on a particular page, there isn't any documented
way to do this. Perhaps you're talking about FILLFACTOR? You can specify a
FILLFACTOR for index creation that will leave a percentage of space on the
leaf level index pages free, but it is only maintained at index creation
time, not during normal operations. You can use INDEXPROPERTY (<table>,
<index>, 'IndexFillFactor') to get this value...
If I'm misunderstanding your questions, please let me know.
Thanks.
--
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:44a0ef20a81f45cb9afa95ac557ba389@.SQ
droptable.com...
> On SQL 2000, how would you determine:
> 1. the percent free on an index?
> 2. the available page size?
> Thanks
> --
> Message posted via http://www.droptable.com|||Okay. Thanks for getting me straight.
What is happening is I have inserts into a table that are timing out, but
updates on the table are proceeding. Is there a possibility of the index
size growing, or new pages being added to the index that may be causing
these insert timeouts?
Message posted via http://www.droptable.com
1. the percent free on an index?
2. the available page size?
Thanks
Message posted via http://www.droptable.comIndexes don't have "free space" - when pages need to be added to the index,
they are allocated and linked in.
In SQL Server the page size is always the same, 8KB. If you're referring to
the available free space on a particular page, there isn't any documented
way to do this. Perhaps you're talking about FILLFACTOR? You can specify a
FILLFACTOR for index creation that will leave a percentage of space on the
leaf level index pages free, but it is only maintained at index creation
time, not during normal operations. You can use INDEXPROPERTY (<table>,
<index>, 'IndexFillFactor') to get this value...
If I'm misunderstanding your questions, please let me know.
Thanks.
--
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:44a0ef20a81f45cb9afa95ac557ba389@.SQ
droptable.com...
> On SQL 2000, how would you determine:
> 1. the percent free on an index?
> 2. the available page size?
> Thanks
> --
> Message posted via http://www.droptable.com|||Okay. Thanks for getting me straight.
What is happening is I have inserts into a table that are timing out, but
updates on the table are proceeding. Is there a possibility of the index
size growing, or new pages being added to the index that may be causing
these insert timeouts?
Message posted via http://www.droptable.com
Sunday, February 19, 2012
Fragmented Indexes
I have a query to find all indexes that are fragmented over 10%. The query
also excludes any where the page count is less than 8 and the index size is
smaller than 64k. Immediately after running my maintenance script that
reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
returns quite a few fragmented indexes. I've also insured that all tables
have a clustered index (most of them on an identity column). Why do I still
show fragmented indexes? Is there some reason they all won't defragment?
Any help is greatly appreciated!Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only two
extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation is
calculated, things like extent order etc. As the index gets to some realistic size, you will not be
affected by this "misreport".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>I have a query to find all indexes that are fragmented over 10%. The query
> also excludes any where the page count is less than 8 and the index size is
> smaller than 64k. Immediately after running my maintenance script that
> reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
> returns quite a few fragmented indexes. I've also insured that all tables
> have a clustered index (most of them on an identity column). Why do I still
> show fragmented indexes? Is there some reason they all won't defragment?
> Any help is greatly appreciated!|||Thanks for your quick response! Any chance you have a link to a page that
shows the 1000 pages MS recommends? Any other MS recommendation pages?
"Tibor Karaszi" wrote:
> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only two
> extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation is
> calculated, things like extent order etc. As the index gets to some realistic size, you will not be
> affected by this "misreport".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
> >I have a query to find all indexes that are fragmented over 10%. The query
> > also excludes any where the page count is less than 8 and the index size is
> > smaller than 64k. Immediately after running my maintenance script that
> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
> > returns quite a few fragmented indexes. I've also insured that all tables
> > have a clustered index (most of them on an identity column). Why do I still
> > show fragmented indexes? Is there some reason they all won't defragment?
> > Any help is greatly appreciated!
>|||Below might have to 1000 pages recommendation:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
> Thanks for your quick response! Any chance you have a link to a page that
> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>
> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only
>> two
>> extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation
>> is
>> calculated, things like extent order etc. As the index gets to some realistic size, you will not
>> be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The query
>> > also excludes any where the page count is less than 8 and the index size is
>> > smaller than 64k. Immediately after running my maintenance script that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
>> > returns quite a few fragmented indexes. I've also insured that all tables
>> > have a clustered index (most of them on an identity column). Why do I still
>> > show fragmented indexes? Is there some reason they all won't defragment?
>> > Any help is greatly appreciated!|||Or then again, you might check this paper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
It specifically says:
Generally, you should not be concerned with fragmentation levels of indexes
with less than 1,000 pages. In the tests, indexes containing more than
10,000 pages realized performance gains, with the biggest gains on indexes
with significantly more pages (greater than 50,000 pages).
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Below might have to 1000 pages recommendation:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>> Thanks for your quick response! Any chance you have a link to a page
>> that
>> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>>
>> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000
>> pages). You can, with only two
>> extents, still have reported fragmentation after a rebuild. This is due
>> to the way fragmentation is
>> calculated, things like extent order etc. As the index gets to some
>> realistic size, you will not be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The
>> >query
>> > also excludes any where the page count is less than 8 and the index
>> > size is
>> > smaller than 64k. Immediately after running my maintenance script
>> > that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above
>> > query still
>> > returns quite a few fragmented indexes. I've also insured that all
>> > tables
>> > have a clustered index (most of them on an identity column). Why do I
>> > still
>> > show fragmented indexes? Is there some reason they all won't
>> > defragment?
>> > Any help is greatly appreciated!
>|||Thank you both for your input! I'll change my maintenance script, and I'm
sure you've just saved us several thousand reindexing jobs that aren't
necessary on a weekly basis! Thanks again!
"Kalen Delaney" wrote:
> Or then again, you might check this paper:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> It specifically says:
> Generally, you should not be concerned with fragmentation levels of indexes
> with less than 1,000 pages. In the tests, indexes containing more than
> 10,000 pages realized performance gains, with the biggest gains on indexes
> with significantly more pages (greater than 50,000 pages).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
> > Below might have to 1000 pages recommendation:
> > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Tony" <Tony@.discussions.microsoft.com> wrote in message
> > news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
> >> Thanks for your quick response! Any chance you have a link to a page
> >> that
> >> shows the 1000 pages MS recommends? Any other MS recommendation pages?
> >>
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> Ignore indexes with less than at least 500 pages (MS recommends 1000
> >> pages). You can, with only two
> >> extents, still have reported fragmentation after a rebuild. This is due
> >> to the way fragmentation is
> >> calculated, things like extent order etc. As the index gets to some
> >> realistic size, you will not be
> >> affected by this "misreport".
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> >> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
> >> >I have a query to find all indexes that are fragmented over 10%. The
> >> >query
> >> > also excludes any where the page count is less than 8 and the index
> >> > size is
> >> > smaller than 64k. Immediately after running my maintenance script
> >> > that
> >> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above
> >> > query still
> >> > returns quite a few fragmented indexes. I've also insured that all
> >> > tables
> >> > have a clustered index (most of them on an identity column). Why do I
> >> > still
> >> > show fragmented indexes? Is there some reason they all won't
> >> > defragment?
> >> > Any help is greatly appreciated!
> >>
> >
>
>|||Thanks Kalen. That was, of course, indeed the paper I meant to refer to. I have no idea why I posted
an URL to the I/O basic paper...
:-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%238Hxd5LeIHA.4464@.TK2MSFTNGP02.phx.gbl...
> Or then again, you might check this paper:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> It specifically says:
> Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000
> pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with
> the biggest gains on indexes with significantly more pages (greater than 50,000 pages).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Below might have to 1000 pages recommendation:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>> Thanks for your quick response! Any chance you have a link to a page that
>> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>>
>> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only
>> two
>> extents, still have reported fragmentation after a rebuild. This is due to the way
>> fragmentation is
>> calculated, things like extent order etc. As the index gets to some realistic size, you will
>> not be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The query
>> > also excludes any where the page count is less than 8 and the index size is
>> > smaller than 64k. Immediately after running my maintenance script that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
>> > returns quite a few fragmented indexes. I've also insured that all tables
>> > have a clustered index (most of them on an identity column). Why do I still
>> > show fragmented indexes? Is there some reason they all won't defragment?
>> > Any help is greatly appreciated!
>>
>|||You probably have them all in a list, just like I do, and you grabbed the
wrong one. I just made absolutely sure that I grabbed the right one!
;-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23JZtwKMeIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Thanks Kalen. That was, of course, indeed the paper I meant to refer to. I
> have no idea why I posted an URL to the I/O basic paper...
> :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%238Hxd5LeIHA.4464@.TK2MSFTNGP02.phx.gbl...
>> Or then again, you might check this paper:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> It specifically says:
>> Generally, you should not be concerned with fragmentation levels of
>> indexes with less than 1,000 pages. In the tests, indexes containing more
>> than 10,000 pages realized performance gains, with the biggest gains on
>> indexes with significantly more pages (greater than 50,000 pages).
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Below might have to 1000 pages recommendation:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>> Thanks for your quick response! Any chance you have a link to a page
>> that
>> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>>
>> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000
>> pages). You can, with only two
>> extents, still have reported fragmentation after a rebuild. This is
>> due to the way fragmentation is
>> calculated, things like extent order etc. As the index gets to some
>> realistic size, you will not be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The
>> >query
>> > also excludes any where the page count is less than 8 and the index
>> > size is
>> > smaller than 64k. Immediately after running my maintenance script
>> > that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above
>> > query still
>> > returns quite a few fragmented indexes. I've also insured that all
>> > tables
>> > have a clustered index (most of them on an identity column). Why do
>> > I still
>> > show fragmented indexes? Is there some reason they all won't
>> > defragment?
>> > Any help is greatly appreciated!
>>
>>
>
also excludes any where the page count is less than 8 and the index size is
smaller than 64k. Immediately after running my maintenance script that
reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
returns quite a few fragmented indexes. I've also insured that all tables
have a clustered index (most of them on an identity column). Why do I still
show fragmented indexes? Is there some reason they all won't defragment?
Any help is greatly appreciated!Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only two
extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation is
calculated, things like extent order etc. As the index gets to some realistic size, you will not be
affected by this "misreport".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>I have a query to find all indexes that are fragmented over 10%. The query
> also excludes any where the page count is less than 8 and the index size is
> smaller than 64k. Immediately after running my maintenance script that
> reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
> returns quite a few fragmented indexes. I've also insured that all tables
> have a clustered index (most of them on an identity column). Why do I still
> show fragmented indexes? Is there some reason they all won't defragment?
> Any help is greatly appreciated!|||Thanks for your quick response! Any chance you have a link to a page that
shows the 1000 pages MS recommends? Any other MS recommendation pages?
"Tibor Karaszi" wrote:
> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only two
> extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation is
> calculated, things like extent order etc. As the index gets to some realistic size, you will not be
> affected by this "misreport".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
> >I have a query to find all indexes that are fragmented over 10%. The query
> > also excludes any where the page count is less than 8 and the index size is
> > smaller than 64k. Immediately after running my maintenance script that
> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
> > returns quite a few fragmented indexes. I've also insured that all tables
> > have a clustered index (most of them on an identity column). Why do I still
> > show fragmented indexes? Is there some reason they all won't defragment?
> > Any help is greatly appreciated!
>|||Below might have to 1000 pages recommendation:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
> Thanks for your quick response! Any chance you have a link to a page that
> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>
> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only
>> two
>> extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation
>> is
>> calculated, things like extent order etc. As the index gets to some realistic size, you will not
>> be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The query
>> > also excludes any where the page count is less than 8 and the index size is
>> > smaller than 64k. Immediately after running my maintenance script that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
>> > returns quite a few fragmented indexes. I've also insured that all tables
>> > have a clustered index (most of them on an identity column). Why do I still
>> > show fragmented indexes? Is there some reason they all won't defragment?
>> > Any help is greatly appreciated!|||Or then again, you might check this paper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
It specifically says:
Generally, you should not be concerned with fragmentation levels of indexes
with less than 1,000 pages. In the tests, indexes containing more than
10,000 pages realized performance gains, with the biggest gains on indexes
with significantly more pages (greater than 50,000 pages).
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Below might have to 1000 pages recommendation:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>> Thanks for your quick response! Any chance you have a link to a page
>> that
>> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>>
>> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000
>> pages). You can, with only two
>> extents, still have reported fragmentation after a rebuild. This is due
>> to the way fragmentation is
>> calculated, things like extent order etc. As the index gets to some
>> realistic size, you will not be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The
>> >query
>> > also excludes any where the page count is less than 8 and the index
>> > size is
>> > smaller than 64k. Immediately after running my maintenance script
>> > that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above
>> > query still
>> > returns quite a few fragmented indexes. I've also insured that all
>> > tables
>> > have a clustered index (most of them on an identity column). Why do I
>> > still
>> > show fragmented indexes? Is there some reason they all won't
>> > defragment?
>> > Any help is greatly appreciated!
>|||Thank you both for your input! I'll change my maintenance script, and I'm
sure you've just saved us several thousand reindexing jobs that aren't
necessary on a weekly basis! Thanks again!
"Kalen Delaney" wrote:
> Or then again, you might check this paper:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> It specifically says:
> Generally, you should not be concerned with fragmentation levels of indexes
> with less than 1,000 pages. In the tests, indexes containing more than
> 10,000 pages realized performance gains, with the biggest gains on indexes
> with significantly more pages (greater than 50,000 pages).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
> > Below might have to 1000 pages recommendation:
> > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Tony" <Tony@.discussions.microsoft.com> wrote in message
> > news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
> >> Thanks for your quick response! Any chance you have a link to a page
> >> that
> >> shows the 1000 pages MS recommends? Any other MS recommendation pages?
> >>
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> Ignore indexes with less than at least 500 pages (MS recommends 1000
> >> pages). You can, with only two
> >> extents, still have reported fragmentation after a rebuild. This is due
> >> to the way fragmentation is
> >> calculated, things like extent order etc. As the index gets to some
> >> realistic size, you will not be
> >> affected by this "misreport".
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> >> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
> >> >I have a query to find all indexes that are fragmented over 10%. The
> >> >query
> >> > also excludes any where the page count is less than 8 and the index
> >> > size is
> >> > smaller than 64k. Immediately after running my maintenance script
> >> > that
> >> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above
> >> > query still
> >> > returns quite a few fragmented indexes. I've also insured that all
> >> > tables
> >> > have a clustered index (most of them on an identity column). Why do I
> >> > still
> >> > show fragmented indexes? Is there some reason they all won't
> >> > defragment?
> >> > Any help is greatly appreciated!
> >>
> >
>
>|||Thanks Kalen. That was, of course, indeed the paper I meant to refer to. I have no idea why I posted
an URL to the I/O basic paper...
:-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%238Hxd5LeIHA.4464@.TK2MSFTNGP02.phx.gbl...
> Or then again, you might check this paper:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> It specifically says:
> Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000
> pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with
> the biggest gains on indexes with significantly more pages (greater than 50,000 pages).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Below might have to 1000 pages recommendation:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>> Thanks for your quick response! Any chance you have a link to a page that
>> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>>
>> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only
>> two
>> extents, still have reported fragmentation after a rebuild. This is due to the way
>> fragmentation is
>> calculated, things like extent order etc. As the index gets to some realistic size, you will
>> not be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The query
>> > also excludes any where the page count is less than 8 and the index size is
>> > smaller than 64k. Immediately after running my maintenance script that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
>> > returns quite a few fragmented indexes. I've also insured that all tables
>> > have a clustered index (most of them on an identity column). Why do I still
>> > show fragmented indexes? Is there some reason they all won't defragment?
>> > Any help is greatly appreciated!
>>
>|||You probably have them all in a list, just like I do, and you grabbed the
wrong one. I just made absolutely sure that I grabbed the right one!
;-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23JZtwKMeIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Thanks Kalen. That was, of course, indeed the paper I meant to refer to. I
> have no idea why I posted an URL to the I/O basic paper...
> :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%238Hxd5LeIHA.4464@.TK2MSFTNGP02.phx.gbl...
>> Or then again, you might check this paper:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> It specifically says:
>> Generally, you should not be concerned with fragmentation levels of
>> indexes with less than 1,000 pages. In the tests, indexes containing more
>> than 10,000 pages realized performance gains, with the biggest gains on
>> indexes with significantly more pages (greater than 50,000 pages).
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Below might have to 1000 pages recommendation:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>> Thanks for your quick response! Any chance you have a link to a page
>> that
>> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>>
>> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000
>> pages). You can, with only two
>> extents, still have reported fragmentation after a rebuild. This is
>> due to the way fragmentation is
>> calculated, things like extent order etc. As the index gets to some
>> realistic size, you will not be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The
>> >query
>> > also excludes any where the page count is less than 8 and the index
>> > size is
>> > smaller than 64k. Immediately after running my maintenance script
>> > that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above
>> > query still
>> > returns quite a few fragmented indexes. I've also insured that all
>> > tables
>> > have a clustered index (most of them on an identity column). Why do
>> > I still
>> > show fragmented indexes? Is there some reason they all won't
>> > defragment?
>> > Any help is greatly appreciated!
>>
>>
>
Fragmented Indexes
I have a query to find all indexes that are fragmented over 10%. The query
also excludes any where the page count is less than 8 and the index size is
smaller than 64k. Immediately after running my maintenance script that
reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
returns quite a few fragmented indexes. I've also insured that all tables
have a clustered index (most of them on an identity column). Why do I still
show fragmented indexes? Is there some reason they all won't defragment?
Any help is greatly appreciated!
Thanks for your quick response! Any chance you have a link to a page that
shows the 1000 pages MS recommends? Any other MS recommendation pages?
"Tibor Karaszi" wrote:
> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only two
> extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation is
> calculated, things like extent order etc. As the index gets to some realistic size, you will not be
> affected by this "misreport".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>
|||Or then again, you might check this paper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
It specifically says:
Generally, you should not be concerned with fragmentation levels of indexes
with less than 1,000 pages. In the tests, indexes containing more than
10,000 pages realized performance gains, with the biggest gains on indexes
with significantly more pages (greater than 50,000 pages).
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Below might have to 1000 pages recommendation:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>
|||Thank you both for your input! I'll change my maintenance script, and I'm
sure you've just saved us several thousand reindexing jobs that aren't
necessary on a weekly basis! Thanks again!
"Kalen Delaney" wrote:
> Or then again, you might check this paper:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> It specifically says:
> Generally, you should not be concerned with fragmentation levels of indexes
> with less than 1,000 pages. In the tests, indexes containing more than
> 10,000 pages realized performance gains, with the biggest gains on indexes
> with significantly more pages (greater than 50,000 pages).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
>
>
also excludes any where the page count is less than 8 and the index size is
smaller than 64k. Immediately after running my maintenance script that
reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
returns quite a few fragmented indexes. I've also insured that all tables
have a clustered index (most of them on an identity column). Why do I still
show fragmented indexes? Is there some reason they all won't defragment?
Any help is greatly appreciated!
Thanks for your quick response! Any chance you have a link to a page that
shows the 1000 pages MS recommends? Any other MS recommendation pages?
"Tibor Karaszi" wrote:
> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only two
> extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation is
> calculated, things like extent order etc. As the index gets to some realistic size, you will not be
> affected by this "misreport".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>
|||Or then again, you might check this paper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
It specifically says:
Generally, you should not be concerned with fragmentation levels of indexes
with less than 1,000 pages. In the tests, indexes containing more than
10,000 pages realized performance gains, with the biggest gains on indexes
with significantly more pages (greater than 50,000 pages).
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Below might have to 1000 pages recommendation:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>
|||Thank you both for your input! I'll change my maintenance script, and I'm
sure you've just saved us several thousand reindexing jobs that aren't
necessary on a weekly basis! Thanks again!
"Kalen Delaney" wrote:
> Or then again, you might check this paper:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> It specifically says:
> Generally, you should not be concerned with fragmentation levels of indexes
> with less than 1,000 pages. In the tests, indexes containing more than
> 10,000 pages realized performance gains, with the biggest gains on indexes
> with significantly more pages (greater than 50,000 pages).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
>
>
Subscribe to:
Posts (Atom)