Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Custom Aggregate Causing Issues Sometimes Expand / Collapse
Author
Message
Posted Tuesday, June 19, 2012 7:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:43 AM
Points: 7,127, Visits: 12,656
I am seeing some reports of this kind of issue with earlier builds of 2005. What build are you running?

SELECT  SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1318025
Posted Tuesday, June 19, 2012 7:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:53 AM
Points: 20, Visits: 417
Edition ProductVersion ProductLevel
Standard Edition 9.00.3215.00 SP2

Nick
Post #1318029
Posted Tuesday, June 19, 2012 9:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:43 AM
Points: 7,127, Visits: 12,656
SP2 is very old. Is applying SP4+CU3+MS11-049 an option?

One more question about the data. How many rows are you calculating the Median over? Median being what it is, you have to store every value before you can calculate the answer meaning your object may be asking for a lot of memory and I have also seen reports of a high volume of memory allocations coming from SQLCLR becoming an issue at times. The Sort in Terminate is also going to be very expensive. I wrote a Median aggregate in SQLCLR not too long ago as a proof-of-concept (POC) and IIRC I used a sorted collection so the class sorted values into the collection as they were read. I am not sure how this compares to your technique in terms of performance, but I can share my code if you're interested. Mine never made it beyond the POC and I never really hammered it from a performance perspective. SQL Server may not be serializing your object, which is why rewriting that method had no affect one way or the other. Just some thoughts.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1318128
Posted Tuesday, June 19, 2012 10:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:53 AM
Points: 20, Visits: 417
I don't think my group will want to update the service pack. Since we would have to test all of our DBs and retest them when we upgrade.

For the number of rows, the underlying table has 270,107 values but I do an aggregate before that, so when I run the median function it is only being done on 426 rows. It does work just fine when I use it on a straight select of 500 runs I have never had a problem.

That's an interesting idea I will try to rewrite my accumulate function to insert into a sorted position so I won't use the List classes sort function.


Nick
Post #1318147
Posted Friday, November 9, 2012 2:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:53 AM
Points: 20, Visits: 417
Looks like it was the build of that server. I am currently testing my code on our new server (SQL Server 2012) and everything appears to be running great. Thanks for all the help.

Nick
Post #1383241
Posted Friday, November 9, 2012 2:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:43 AM
Points: 7,127, Visits: 12,656
Good to know, you're welcome. Thanks for posting back that you found a path forward!

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1383250
Posted Monday, March 3, 2014 2:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 9:42 AM
Points: 5, Visits: 36
Long time ago.... but what the hec,

Had same problem on one our 2008 R2 Servers

Setting Max Degree of Parallelism to 1 resolved it.

I am assuming it has something to do with the way the optimizer works with parallel queries (such as a union)




Post #1547102
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse