Fuzzy Lookup componant eating all available virtual memory

Hey,

I have a large set of data that I need to match against another large set of data. The reference table has 9.8mill rows and my input has 14.6mill rows. I started with a new project. I added my connection, then a task to clear the result table, then my data flow, then my OLE source, then my Fuzzy Lookup task, then my SQL Server Destination. I set the connection of my OLE source and set the query to pull the data. Then I set the connection of my Fuzzy Lookup task, set the reference table and told it to create a new index (the problem also occurs if I use a generated index) and then set up the matching criteria. Then I set the connection and destination for the SQL Server Destination.

After setting all this up, I hit Run. The thing ran great until ~ 800k rows and then it failed. I ran it several times and it always failed right around 800k with a message saying there was not enough space and then an error with buffers being passed to the Fuzzy Lookup component. I opened Task Manager and watched the resources as it ran and was amazed at what I saw. The Fuzzy Lookup component eats up every bit of Virtual Memory available and when it can't take any more, it errors out. I tried setting the Max Memory setting on the component and it seems to have no effect. I also played with the buffer settings on the data flow task to no avail. I even went as far as to put an identity on my input table and create a function that outputs selects that use a between on the identity to break the data into 600k chunks. I set up a ForEach component and DTS variables, but the Fuzzy Lookup component does not free the VM after the iteration of the ForEach component!

I ended up running each chunk of 600k one at a time. I have to automate this for the future, so I need a solution. Does anyone have an idea for me?

The errors I get are:

[Fuzzy Lookup 1 [3067]] Warning: Not enough storage is available to complete this operation.

[DTS.Pipeline] Warning: A call to the ProcessInput method for input 3068 on component "Fuzzy Lookup 1" (3067) unexpectedly kept a reference to the buffer it was passed. The refcount on that buffer was 2 before the call, and 1 after the call returned.

[DTS.Pipeline] Error: The ProcessInput method on component "Fuzzy Lookup 1" (3067) failed with error code 0x8007000E. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0x8007000E.

[OLE DB Source [1923]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1923) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread1" has exited with error code 0xC0047039.

|||

Pls check to see whether http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=191199&SiteID=1 helps.

Thanks

Wenyang

|||

I found that thread after posting and tried the solutions there, but they had no effect on the package. It still almost linearly eats up all virtual memory. By the time it is at 800k records, it has eaten up 40gb of virtual memory.

The server is:

Dual AMD 64bit Processors

2TB storage with 650GB free

8GB ram

Windows Server 2003 64bit

SQL Server 2005 64bit

Of note also is that I have tried running the package from the command line with identical results. Here is what I used:

dtexec /f package1.dtsx /ref n

|||I had the VM size increased to 200gb and I can do 3m sets now. It still isn't to where I need to to be for automation, but it will let me get the job done for now. If anyone has some ideas on what might be wrong, please please let me know!!|||

Hey James,

Did you have any luck with this? I'm in exactly the same situation you are in regarding fuzzy lookup taking all memory. If you or anyone else figured out a work around it would be great to hear.

Thanks,

SL

|||There's been fixes for memory leak issues with the fuzzy components in the past. Could you check KB 912423: "FIX: Memory leaks occur when you use Fuzzy Lookup and Fuzzy Grouping to transform a SQL Server 2005 Integration Services package"?

Fuzzy Lookup componant eating all available virtual memory

Hey,

I have a large set of data that I need to match against another large set of data. The reference table has 9.8mill rows and my input has 14.6mill rows. I started with a new project. I added my connection, then a task to clear the result table, then my data flow, then my OLE source, then my Fuzzy Lookup task, then my SQL Server Destination. I set the connection of my OLE source and set the query to pull the data. Then I set the connection of my Fuzzy Lookup task, set the reference table and told it to create a new index (the problem also occurs if I use a generated index) and then set up the matching criteria. Then I set the connection and destination for the SQL Server Destination.

After setting all this up, I hit Run. The thing ran great until ~ 800k rows and then it failed. I ran it several times and it always failed right around 800k with a message saying there was not enough space and then an error with buffers being passed to the Fuzzy Lookup component. I opened Task Manager and watched the resources as it ran and was amazed at what I saw. The Fuzzy Lookup component eats up every bit of Virtual Memory available and when it can't take any more, it errors out. I tried setting the Max Memory setting on the component and it seems to have no effect. I also played with the buffer settings on the data flow task to no avail. I even went as far as to put an identity on my input table and create a function that outputs selects that use a between on the identity to break the data into 600k chunks. I set up a ForEach component and DTS variables, but the Fuzzy Lookup component does not free the VM after the iteration of the ForEach component!

I ended up running each chunk of 600k one at a time. I have to automate this for the future, so I need a solution. Does anyone have an idea for me?

The errors I get are:

[Fuzzy Lookup 1 [3067]] Warning: Not enough storage is available to complete this operation.

[DTS.Pipeline] Warning: A call to the ProcessInput method for input 3068 on component "Fuzzy Lookup 1" (3067) unexpectedly kept a reference to the buffer it was passed. The refcount on that buffer was 2 before the call, and 1 after the call returned.

[DTS.Pipeline] Error: The ProcessInput method on component "Fuzzy Lookup 1" (3067) failed with error code 0x8007000E. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0x8007000E.

[OLE DB Source [1923]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1923) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread1" has exited with error code 0xC0047039.

|||

Pls check to see whether http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=191199&SiteID=1 helps.

Thanks

Wenyang

|||

I found that thread after posting and tried the solutions there, but they had no effect on the package. It still almost linearly eats up all virtual memory. By the time it is at 800k records, it has eaten up 40gb of virtual memory.

The server is:

Dual AMD 64bit Processors

2TB storage with 650GB free

8GB ram

Windows Server 2003 64bit

SQL Server 2005 64bit

Of note also is that I have tried running the package from the command line with identical results. Here is what I used:

dtexec /f package1.dtsx /ref n

|||I had the VM size increased to 200gb and I can do 3m sets now. It still isn't to where I need to to be for automation, but it will let me get the job done for now. If anyone has some ideas on what might be wrong, please please let me know!!|||

Hey James,

Did you have any luck with this? I'm in exactly the same situation you are in regarding fuzzy lookup taking all memory. If you or anyone else figured out a work around it would be great to hear.

Thanks,

SL

|||There's been fixes for memory leak issues with the fuzzy components in the past. Could you check KB 912423: "FIX: Memory leaks occur when you use Fuzzy Lookup and Fuzzy Grouping to transform a SQL Server 2005 Integration Services package"?

Function is 10 times slower than SP

Hi all,

In order to return a table for a specific input parameter, I am using Function, but the performance is just awful! After I have tried same code as SP, the whole thing is running under 1 sec (like 0.5 sec), while the function is about 10 times slow (4-6 sec). I know in SQL 2000 function is slower than SP, but that cannot be as bad as 10 times slower.

Now, in order to use that table from SP, I have to create a temp table, then insert result into that temp table, before I can direct use any "select" sentence. Any explanation here? Or how to "select" from a SP directly?

Thanks,

Ning

It is very difficult for us to attempt to help you with being able to see the code.

You may wish to consider posting the procedure code and maybe someone here will help create an efficient TVF (table valued function.)

|||

As you said you have to insert the sp output to temp table to use in select statement ... you can not use sp in select statement... you are rightly mentioned that function used to be more or less slower than sp... also check whether all the parameters used in sp are used in function also...

Madhu

Function doesn't use indexes

Hello!
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
Tom
It would help a lot if we could see this "function". Is it really a
function or a stored procedure?
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>
|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:

> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>

Function doesn't use indexes

Hello!
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
TomIt would help a lot if we could see this "function". Is it really a
function or a stored procedure?
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
--
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:

> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement
.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>

Function doesn't use indexes

Hello!
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
TomIt would help a lot if we could see this "function". Is it really a
function or a stored procedure?
--
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
--
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>

Function - List Windows Group Members

Hello!
I thinking of creating a SQL Server clr function that works like this.
Input paremeter: A Windows group, e.g. Domain1\Group1
Resultset: A list of all accounts that directely or indirectely belongs to
that Windows group.
e.g.
Domain1\Account1
Domain1\Account2
Domain1\Account3
The idea is to use this together with the security catalog views to get a
list of accounts that is for example sysadmins.
Could it be that a function like this exist in SQL Server?
I also thought about what AD permisssions that would be needed?
Best regards
Ola Hallengren
Hi
You can create an ADSI linked server and query the active directory, but
recursing through the groups not always that easy. There is a script to list
all group members on http://www.rlmueller.net/freecode3.htm
John
"Ola Hallengren" wrote:

> Hello!
> I thinking of creating a SQL Server clr function that works like this.
> Input paremeter: A Windows group, e.g. Domain1\Group1
> Resultset: A list of all accounts that directely or indirectely belongs to
> that Windows group.
> e.g.
> Domain1\Account1
> Domain1\Account2
> Domain1\Account3
> The idea is to use this together with the security catalog views to get a
> list of accounts that is for example sysadmins.
> Could it be that a function like this exist in SQL Server?
> I also thought about what AD permisssions that would be needed?
> Best regards
> Ola Hallengren
|||Thanks, John. As I understand it I have about these options.
1. Create an ADSI linked server and query against that with TSQL. This can
be packaged into a SQL Server function.
2. Use the VBScript at http://www.rlmueller.net/freecode3.htm from
xp_cmdshell. This can be packaged into a stored procedure.
3. Create some CLR code with the same logic as in the VBScript and use that
assambly in a SQL Server function.
Is this right?
/Ola
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You can create an ADSI linked server and query the active directory, but
> recursing through the groups not always that easy. There is a script to list
> all group members on http://www.rlmueller.net/freecode3.htm
> John
> "Ola Hallengren" wrote:
|||I think so, I would opt for either 2 or 3 but have never tried 3!.
John
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:05FC89C1-3816-41B9-9123-5D9E4FFBEFC0@.microsoft.com...[vbcol=seagreen]
> Thanks, John. As I understand it I have about these options.
> 1. Create an ADSI linked server and query against that with TSQL. This can
> be packaged into a SQL Server function.
> 2. Use the VBScript at http://www.rlmueller.net/freecode3.htm from
> xp_cmdshell. This can be packaged into a stored procedure.
> 3. Create some CLR code with the same logic as in the VBScript and use
> that
> assambly in a SQL Server function.
> Is this right?
> /Ola
>
> "John Bell" wrote:

Function - List Windows Group Members

Hello!
I thinking of creating a SQL Server clr function that works like this.
Input paremeter: A Windows group, e.g. Domain1\Group1
Resultset: A list of all accounts that directely or indirectely belongs to
that Windows group.
e.g.
Domain1\Account1
Domain1\Account2
Domain1\Account3
The idea is to use this together with the security catalog views to get a
list of accounts that is for example sysadmins.
Could it be that a function like this exist in SQL Server?
I also thought about what AD permisssions that would be needed?
Best regards
Ola HallengrenHi
You can create an ADSI linked server and query the active directory, but
recursing through the groups not always that easy. There is a script to list
all group members on http://www.rlmueller.net/freecode3.htm
John
"Ola Hallengren" wrote:
> Hello!
> I thinking of creating a SQL Server clr function that works like this.
> Input paremeter: A Windows group, e.g. Domain1\Group1
> Resultset: A list of all accounts that directely or indirectely belongs to
> that Windows group.
> e.g.
> Domain1\Account1
> Domain1\Account2
> Domain1\Account3
> The idea is to use this together with the security catalog views to get a
> list of accounts that is for example sysadmins.
> Could it be that a function like this exist in SQL Server?
> I also thought about what AD permisssions that would be needed?
> Best regards
> Ola Hallengren|||Thanks, John. As I understand it I have about these options.
1. Create an ADSI linked server and query against that with TSQL. This can
be packaged into a SQL Server function.
2. Use the VBScript at http://www.rlmueller.net/freecode3.htm from
xp_cmdshell. This can be packaged into a stored procedure.
3. Create some CLR code with the same logic as in the VBScript and use that
assambly in a SQL Server function.
Is this right?
/Ola
"John Bell" wrote:
> Hi
> You can create an ADSI linked server and query the active directory, but
> recursing through the groups not always that easy. There is a script to list
> all group members on http://www.rlmueller.net/freecode3.htm
> John
> "Ola Hallengren" wrote:
> > Hello!
> >
> > I thinking of creating a SQL Server clr function that works like this.
> >
> > Input paremeter: A Windows group, e.g. Domain1\Group1
> >
> > Resultset: A list of all accounts that directely or indirectely belongs to
> > that Windows group.
> >
> > e.g.
> > Domain1\Account1
> > Domain1\Account2
> > Domain1\Account3
> >
> > The idea is to use this together with the security catalog views to get a
> > list of accounts that is for example sysadmins.
> >
> > Could it be that a function like this exist in SQL Server?
> >
> > I also thought about what AD permisssions that would be needed?
> >
> > Best regards
> >
> > Ola Hallengren|||I think so, I would opt for either 2 or 3 but have never tried 3!.
John
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:05FC89C1-3816-41B9-9123-5D9E4FFBEFC0@.microsoft.com...
> Thanks, John. As I understand it I have about these options.
> 1. Create an ADSI linked server and query against that with TSQL. This can
> be packaged into a SQL Server function.
> 2. Use the VBScript at http://www.rlmueller.net/freecode3.htm from
> xp_cmdshell. This can be packaged into a stored procedure.
> 3. Create some CLR code with the same logic as in the VBScript and use
> that
> assambly in a SQL Server function.
> Is this right?
> /Ola
>
> "John Bell" wrote:
>> Hi
>> You can create an ADSI linked server and query the active directory, but
>> recursing through the groups not always that easy. There is a script to
>> list
>> all group members on http://www.rlmueller.net/freecode3.htm
>> John
>> "Ola Hallengren" wrote:
>> > Hello!
>> >
>> > I thinking of creating a SQL Server clr function that works like this.
>> >
>> > Input paremeter: A Windows group, e.g. Domain1\Group1
>> >
>> > Resultset: A list of all accounts that directely or indirectely belongs
>> > to
>> > that Windows group.
>> >
>> > e.g.
>> > Domain1\Account1
>> > Domain1\Account2
>> > Domain1\Account3
>> >
>> > The idea is to use this together with the security catalog views to get
>> > a
>> > list of accounts that is for example sysadmins.
>> >
>> > Could it be that a function like this exist in SQL Server?
>> >
>> > I also thought about what AD permisssions that would be needed?
>> >
>> > Best regards
>> >
>> > Ola Hallengren