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 ««12345»»»

Solving the “Running Totals” Problem in SQL Server 2005 with Partitioned Tables Expand / Collapse
Author
Message
Posted Tuesday, January 27, 2009 9:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 3:05 AM
Points: 41, Visits: 271
That can happen on multi-processor systems that SQL is allowed to use.
Post #644249
Posted Tuesday, January 27, 2009 9:18 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 20,857, Visits: 32,877
noeld (1/27/2009)

I liked your article ( a lot ) and it seems that you put a lot of effort into it.


And I'm forgetting my manners. Thank you, it makes me feel good making the effort definitely worthwhile.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #644255
Posted Tuesday, January 27, 2009 9:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 7, 2009 6:48 AM
Points: 3, Visits: 32
Apologies, I realise that my previous post represented something of a Utopian ideal With that said however, I do think that whilst making do and mending is *good*, striving for a simpler way is not to be dismissed. Yes, perhaps Microsoft will eventually get around to properly implementing such functionality.

Whilst writing this, I can see that a middle tier person just rode in to town.

6 lines of code vs. potentially 1,000,000 rows of data over the network.

When does the next craft to Utopia depart?
Post #644257
Posted Tuesday, January 27, 2009 9:26 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 20,857, Visits: 32,877
Smendle (1/27/2009)
That can happen on multi-processor systems that SQL is allowed to use.


I am sure this is in response to the following:

I received a private message with a question regarding my article that I can't answer, so I thought I'd put it here with the hopes that some of the gurus (Jeff, Gail, Grant, Gus, et al) out there may be able to help out.


Why is the CPU time greater than the elapsed time

CPU time = 5063 ms, elapsed time = 3309 ms.



Thinking about it, that actually makes sense and isn't something I'd have come up with on my own, thanks. Do any of the other gurus out there also agree?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #644265
Posted Tuesday, January 27, 2009 10:00 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, July 12, 2014 6:44 AM
Points: 441, Visits: 1,799
Lynn Pettis (1/27/2009)
Smendle (1/27/2009)
That can happen on multi-processor systems that SQL is allowed to use.


I am sure this is in response to the following:

I received a private message with a question regarding my article that I can't answer, so I thought I'd put it here with the hopes that some of the gurus (Jeff, Gail, Grant, Gus, et al) out there may be able to help out.


Why is the CPU time greater than the elapsed time

CPU time = 5063 ms, elapsed time = 3309 ms.



Thinking about it, that actually makes sense and isn't something I'd have come up with on my own, thanks. Do any of the other gurus out there also agree?


I concur. What happens if you add MAXDOP = 1 to that particular query?

FWIW, in a previous life MAXDOP was used as execution plans differed wildly on (single CPU, pre-hyper thread) Test kit than when they ran on the multi-CPU Live kit!

Nice article btw ;)

Dave Jackson



http://glossopian.co.uk/
"I don't know what I don't know."
Post #644320
Posted Tuesday, January 27, 2009 10:13 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 20,857, Visits: 32,877
David Jackson (1/27/2009)
Lynn Pettis (1/27/2009)
Smendle (1/27/2009)
That can happen on multi-processor systems that SQL is allowed to use.


I am sure this is in response to the following:

I received a private message with a question regarding my article that I can't answer, so I thought I'd put it here with the hopes that some of the gurus (Jeff, Gail, Grant, Gus, et al) out there may be able to help out.


Why is the CPU time greater than the elapsed time

CPU time = 5063 ms, elapsed time = 3309 ms.



Thinking about it, that actually makes sense and isn't something I'd have come up with on my own, thanks. Do any of the other gurus out there also agree?


I concur. What happens if you add MAXDOP = 1 to that particular query?

FWIW, in a previous life MAXDOP was used as execution plans differed wildly on (single CPU, pre-hyper thread) Test kit than when they ran on the multi-CPU Live kit!

Nice article btw ;)

Dave Jackson


First, to remember my manners, thank you for the complement on the article.

I'd have to go back into my article to figure out which one it was, and then try it again at home with MAXDOP set to 1. The other thing I'll need is to find the time to do it. Maybe I'll get a few minutes some time this week, but right now I'm a bit busy with another article as well as other life activities that are clamoring for my time.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #644331
Posted Tuesday, January 27, 2009 12:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:51 AM
Points: 14,201, Visits: 28,530
Uh... Wow... Over-achieve much?

I feel a bit stupid to be commenting on this. I don't have much experience with partitioning beyond reading up on the theory and a few discussions. If I can make some spare time, I'd like to see what the execution plans looked like between your final solution and Jeff's original, purely academic.

Nice job.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #644480
Posted Tuesday, January 27, 2009 1:22 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 20,857, Visits: 32,877
Grant Fritchey (1/27/2009)
Uh... Wow... Over-achieve much?

I feel a bit stupid to be commenting on this. I don't have much experience with partitioning beyond reading up on the theory and a few discussions. If I can make some spare time, I'd like to see what the execution plans looked like between your final solution and Jeff's original, purely academic.

Nice job.


Thank you, this makes me feel really good. I'll try to get the execution plans for you and post them here. It may a few days, as I do have several things going on right now.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #644491
Posted Tuesday, January 27, 2009 1:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:51 AM
Points: 14,201, Visits: 28,530
Lynn Pettis (1/27/2009)

Thank you, this makes me feel really good. I'll try to get the execution plans for and post them here. It may a few days, as I do have several things going on right now.



No rush. If I stumble across a spare hour or two I'll do it myself. I'm just curious.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #644493
Posted Tuesday, January 27, 2009 1:37 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 20,857, Visits: 32,877
Actually, Grant, I'm curious also, and with your experience with working with execution plans, your feedback on that side would be quite interesting.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #644501
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse