I have a customer that is complaining of poor performance when doing
searches against the FTS catalog during the day.
I believe what is happening is that the indexing process is taking place at
the same time as their searches.
select @.@.version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
The data is derived from an OCR process on scanned forms.
The data is added to the table throughout the day.
The catalog index is on one column of one table.
I have Change Tracking, Update Index in Background enabled.
Question 1 - When Chg Trk, UIB is enabled, should I see the crawl start and
crawl end events enumerated in the event log?
I thought this operation went on continuously.
I have included an abridged listing from the event log for one day.
You can see that on average, 200-300 items are processed each iteration
during the day.
Is this typical of Change Tracking, UIB?
The following list displays the data for one day,
crawl start time
crawl end time, gatherer count, modified count
7:21 crawl start
8:43 crawl end 2031581 6
9:04 crawl start
10:29 crawl end 2033495 307
11:01 crawl start
12:22 crawl end 2034510 385
12:50 crawl start
14:14 crawl end 2035862 341
14:23 crawl start
15:49 crawl end 2036796 244
16:27 crawl start
17:48 crawl end 2038034 244
19:00 crawl start
20:21 crawl end 2038557 1
Since the crawl process runs for one to one and one-half hours each event, I
am guessing that this is what is affecting their performance.
Question 2 - Would it be better to turn off Change Tracking, UIB during the
day and start an incremental index in the evening?
I thought CT, UIB was designed to allow indexing to take place while normal
database operations are executing.
This depends on your needs. If you need real time indexing updates you will
probably have to use Change tracking with Update Index in Background.
Otherwise, you might want to use Change Tracking and then schedule the index
update to periods when there is low activity on the server.
Note that there are dramatic improvements with SQL Server FTS in SQL Server
2005.
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
"Binder" <rgondzur@.hotmail.com> wrote in message
news:eZPShpwLFHA.2888@.TK2MSFTNGP12.phx.gbl...
> I have a customer that is complaining of poor performance when doing
> searches against the FTS catalog during the day.
> I believe what is happening is that the indexing process is taking place
at
> the same time as their searches.
> select @.@.version
> --
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> The data is derived from an OCR process on scanned forms.
> The data is added to the table throughout the day.
> The catalog index is on one column of one table.
> I have Change Tracking, Update Index in Background enabled.
>
> Question 1 - When Chg Trk, UIB is enabled, should I see the crawl start
and
> crawl end events enumerated in the event log?
> I thought this operation went on continuously.
> I have included an abridged listing from the event log for one day.
> You can see that on average, 200-300 items are processed each iteration
> during the day.
> Is this typical of Change Tracking, UIB?
>
> The following list displays the data for one day,
> crawl start time
> crawl end time, gatherer count, modified count
>
> 7:21 crawl start
> 8:43 crawl end 2031581 6
> 9:04 crawl start
> 10:29 crawl end 2033495 307
> 11:01 crawl start
> 12:22 crawl end 2034510 385
> 12:50 crawl start
> 14:14 crawl end 2035862 341
> 14:23 crawl start
> 15:49 crawl end 2036796 244
> 16:27 crawl start
> 17:48 crawl end 2038034 244
> 19:00 crawl start
> 20:21 crawl end 2038557 1
>
> Since the crawl process runs for one to one and one-half hours each event,
I
> am guessing that this is what is affecting their performance.
> Question 2 - Would it be better to turn off Change Tracking, UIB during
the
> day and start an incremental index in the evening?
> I thought CT, UIB was designed to allow indexing to take place while
normal
> database operations are executing.
>
>
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Thursday, March 8, 2012
Sunday, February 12, 2012
Change of Behavior in Pipeline
Dear Forum Members,
As part of the pipeline work to improve performance and stability, we are considering a change in how buffers are managed, which will affect custom transforms. Michael Entin explained what is about to change in the link below:
http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx
My questions are :
What is the likelihood of your scenarios broken by this?
How expensive is it to change your custom component code as Michael suggests in his blog?
Thanks,
Deniz Erkan - msft
To summarize the impact, can I assume that we're only talking about SQL Server 2008, with which custom transformations for SQL 2005 would need to be recoded anyway so as to take advantage of the new interfaces (e.g. IDTSxxxx100), for example?In other words, it shouldn't be a huge issue as SQL Server 2008 isn't even released, correct? And custom component development at this juncture might be a bit premature?
We're not changing the behavior in SQL Server 2005, are we?|||I have no problem with this because I saw that blog entry and fixed my code. 
>We're not changing the behavior in SQL Server 2005, are we?
The SQL 2005 behavior will not be changed.
The concern is scenario when someone takes a component developed for SQL 2005, mechanically renames the interfaces, recompiles it and and uses it in SQL 2008. If the component used incorrect pattern, the new version will be producing incorrect results in SQL 2008.
|||Phil,
Yes, your assumptions are correct. This is only for 2008, and custom components developed for 2005 will have to be re-compiled at a minimum to work against the new interfaces.
However, what otherwise would be a simple re-compile job, now would be an additional check on the semantics of the custom component to make sure the "end of buffer logic" is done properly as per Michael's post.
oh, and I didn't see Michael's post prior to this, apologies for repetition. 
I don't see a major issue with this. As part of an ETL process, the record counts should be reconciled anyway, so this should be easy to catch.
|||Transform component authors need to change behavior together for 2008, as not everyone has access to the source code of all the non stock components they use. To me, this issue is more coordination then rocket science...
As jwelch mentioned, I don't think the problem would be all that difficult to detect, since rowcounts should be part of an etl process, but then there's the fix and recompile part...
For example, we don't have source code for some non-stock components, but Reflector shows they exhibit the incorrect behavior. So while I've recompiled owned source code, I just hope everybody gets the message, or is willing to part with source.
|||I rememeber this confusing me at the time I started writing components. I got it into my head that calling buffer.NextRow() when there weren't any caused an error so I put the EndOfRowset check first.
Not too difficult to change in my components.
Will SQL2005 compiled components work in a SQL2008 pipeline, if not then everyone has to recompile there code anyway.
|||
SimonS_ wrote:
Will SQL2005 compiled components work in a SQL2008 pipeline, if not then everyone has to recompile there [sic] code anyway.
No. You have to edit and recompile to use the new interfaces.
http://blogs.msdn.com/mattm/archive/2007/06/05/katmai-custom-components-and-upgrade.aspx|||
There's no big issue here that affects me. I have one question though - this will ge changed in the docs right?
And, have the SSIS2005 docs been updated yet as per Michael's post? I know Michael said that this was work-in-progress.
-Jamie
Change of Behavior in Pipeline
Dear Forum Members,
As part of the pipeline work to improve performance and stability, we are considering a change in how buffers are managed, which will affect custom transforms. Michael Entin explained what is about to change in the link below:
http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx
My questions are :
What is the likelihood of your scenarios broken by this?
How expensive is it to change your custom component code as Michael suggests in his blog?
Thanks,
Deniz Erkan - msft
To summarize the impact, can I assume that we're only talking about SQL Server 2008, with which custom transformations for SQL 2005 would need to be recoded anyway so as to take advantage of the new interfaces (e.g. IDTSxxxx100), for example?In other words, it shouldn't be a huge issue as SQL Server 2008 isn't even released, correct? And custom component development at this juncture might be a bit premature?
We're not changing the behavior in SQL Server 2005, are we?|||I have no problem with this because I saw that blog entry and fixed my code.
>We're not changing the behavior in SQL Server 2005, are we?
The SQL 2005 behavior will not be changed.
The concern is scenario when someone takes a component developed for SQL 2005, mechanically renames the interfaces, recompiles it and and uses it in SQL 2008. If the component used incorrect pattern, the new version will be producing incorrect results in SQL 2008.
|||Phil,
Yes, your assumptions are correct. This is only for 2008, and custom components developed for 2005 will have to be re-compiled at a minimum to work against the new interfaces.
However, what otherwise would be a simple re-compile job, now would be an additional check on the semantics of the custom component to make sure the "end of buffer logic" is done properly as per Michael's post.
oh, and I didn't see Michael's post prior to this, apologies for repetition.
I don't see a major issue with this. As part of an ETL process, the record counts should be reconciled anyway, so this should be easy to catch.
|||Transform component authors need to change behavior together for 2008, as not everyone has access to the source code of all the non stock components they use. To me, this issue is more coordination then rocket science...
As jwelch mentioned, I don't think the problem would be all that difficult to detect, since rowcounts should be part of an etl process, but then there's the fix and recompile part...
For example, we don't have source code for some non-stock components, but Reflector shows they exhibit the incorrect behavior. So while I've recompiled owned source code, I just hope everybody gets the message, or is willing to part with source.
|||I rememeber this confusing me at the time I started writing components. I got it into my head that calling buffer.NextRow() when there weren't any caused an error so I put the EndOfRowset check first.
Not too difficult to change in my components.
Will SQL2005 compiled components work in a SQL2008 pipeline, if not then everyone has to recompile there code anyway.
|||
Will SQL2005 compiled components work in a SQL2008 pipeline, if not then everyone has to recompile there [sic] code anyway.
No. You have to edit and recompile to use the new interfaces.
http://blogs.msdn.com/mattm/archive/2007/06/05/katmai-custom-components-and-upgrade.aspx|||
There's no big issue here that affects me. I have one question though - this will ge changed in the docs right?
And, have the SSIS2005 docs been updated yet as per Michael's post? I know Michael said that this was work-in-progress.
-Jamie
Change of Behavior in Pipeline
Dear Forum Members,
As part of the pipeline work to improve performance and stability, we are considering a change in how buffers are managed, which will affect custom transforms. Michael Entin explained what is about to change in the link below:
http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx
My questions are :
What is the likelihood of your scenarios broken by this?
How expensive is it to change your custom component code as Michael suggests in his blog?
Thanks,
Deniz Erkan - msft
To summarize the impact, can I assume that we're only talking about SQL Server 2008, with which custom transformations for SQL 2005 would need to be recoded anyway so as to take advantage of the new interfaces (e.g. IDTSxxxx100), for example?In other words, it shouldn't be a huge issue as SQL Server 2008 isn't even released, correct? And custom component development at this juncture might be a bit premature?
We're not changing the behavior in SQL Server 2005, are we?|||I have no problem with this because I saw that blog entry and fixed my code.
>We're not changing the behavior in SQL Server 2005, are we?
The SQL 2005 behavior will not be changed.
The concern is scenario when someone takes a component developed for SQL 2005, mechanically renames the interfaces, recompiles it and and uses it in SQL 2008. If the component used incorrect pattern, the new version will be producing incorrect results in SQL 2008.
|||Phil,
Yes, your assumptions are correct. This is only for 2008, and custom components developed for 2005 will have to be re-compiled at a minimum to work against the new interfaces.
However, what otherwise would be a simple re-compile job, now would be an additional check on the semantics of the custom component to make sure the "end of buffer logic" is done properly as per Michael's post.
oh, and I didn't see Michael's post prior to this, apologies for repetition.
I don't see a major issue with this. As part of an ETL process, the record counts should be reconciled anyway, so this should be easy to catch.
|||Transform component authors need to change behavior together for 2008, as not everyone has access to the source code of all the non stock components they use. To me, this issue is more coordination then rocket science...
As jwelch mentioned, I don't think the problem would be all that difficult to detect, since rowcounts should be part of an etl process, but then there's the fix and recompile part...
For example, we don't have source code for some non-stock components, but Reflector shows they exhibit the incorrect behavior. So while I've recompiled owned source code, I just hope everybody gets the message, or is willing to part with source.
|||I rememeber this confusing me at the time I started writing components. I got it into my head that calling buffer.NextRow() when there weren't any caused an error so I put the EndOfRowset check first.
Not too difficult to change in my components.
Will SQL2005 compiled components work in a SQL2008 pipeline, if not then everyone has to recompile there code anyway.
|||
Will SQL2005 compiled components work in a SQL2008 pipeline, if not then everyone has to recompile there [sic] code anyway.
No. You have to edit and recompile to use the new interfaces.
http://blogs.msdn.com/mattm/archive/2007/06/05/katmai-custom-components-and-upgrade.aspx|||
There's no big issue here that affects me. I have one question though - this will ge changed in the docs right?
And, have the SSIS2005 docs been updated yet as per Michael's post? I know Michael said that this was work-in-progress.
-Jamie
Subscribe to:
Posts (Atom)