January 6, 2012 at 1:25 am
J Livingston SQL (1/4/2012)
.....unless someone can provide a solution that is just as quick to run.???...
A recursive CTE yielding an output set would do the job in about 4 times the duration of the quirky update, providing you can create a unique clustered index on an incrementing column. Much slower without the CI, and if the results have to be persisted.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 6, 2012 at 1:26 am
SQL Kiwi (1/6/2012)
Has anyone else managed to reproduce this now given the extra information I provided? Jeff in particular? I'm particularly interested to hear from anyone running 32 bit SQL Server with the reduced max server memory settings I mentioned.
Microsoft SQL Server 2008 (SP1) - 10.0.2573.0 (X64)
Feb 4 2011 11:27:06
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)
64 bit Vista Home Premium here Paul, but I'll go for it if you give the nod.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 6, 2012 at 2:40 am
SQL Kiwi (1/6/2012)
J Livingston SQL (1/4/2012)
SO...until we have a budget that allows migration to 2012...I will continue with "quirky" for the tasks I use it for...feeling safe in the fact that, providing the 'safety check' is used..it will give me what I need........unless someone can provide a solution that is just as quick to run.???Just for my own curiosity, would you be able to give a brief summary of a typical requirement? Obviously I saw the example given in your code here, but I am curious to know whether you need to persist the running totals, what the broader use case description is...things like that. As I say, I'm just interested to know a bit more, I don't have a particular agenda.
Paul...primarily we use it for running totals on stock movements....this is run nightly.
The running stock figure is then used in further analysis for closing stock period comparisons/stock ageing/ stock turns...comparing current periods with historical periods....etc etc
(we rip the data out of a legacy system which cannot provide the details we require)
As I have already said...I haven't had any issues before now...and this occurrence ONLY materialized on a memory compromised PC....it has never occurred on our servers.
The reason why I raised it was because I was interested in understanding why it was failing on the PC...and I believe that you have identified the cause....for which I very much appreciate your efforts and detailed analysis.
many thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 6, 2012 at 11:00 am
J Livingston SQL (1/6/2012)
As I have already said...I haven't had any issues before now...and this occurrence ONLY materialized on a memory compromised PC....it has never occurred on our servers.
Thanks for the background information - I appreciate it.
The reason why I raised it was because I was interested in understanding why it was failing on the PC...and I believe that you have identified the cause....for which I very much appreciate your efforts and detailed analysis.
No worries. I am just keen for other people to validate my results for completeness, and I am also interested to know if the read-ahead/low memory combination I am proposing might cause the same issue on 32-bit. Of course it is gratifying that my safety check extension to the quirky update caught this condition, and, I realise you will be quite happy with things from a practical point of view. Anyone that knows me will know that I do like to understand the fine details of problems like this, as much as possible anyway...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 6, 2012 at 11:01 am
ChrisM@home (1/6/2012)
64 bit Vista Home Premium here Paul, but I'll go for it if you give the nod.
I'd be grateful. It would be good to know that at least one other person gets the same results as me, 64-bit or not. Thanks, Chris.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 6, 2012 at 11:51 am
SQL Kiwi (1/6/2012)
Has anyone else managed to reproduce this now given the extra information I provided? Jeff in particular? I'm particularly interested to hear from anyone running 32 bit SQL Server with the reduced max server memory settings I mentioned.
Using 256MB memory it failed 7 out of 8 times. Ran it once with 512MB, failed also. Ran it once with 1GB, succesfully.
System:
Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
May 26 2009 14:24:20
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 6.1 (Build 7601: Service Pack 1)
Windows 7 Ultimate 32-bit
January 6, 2012 at 12:09 pm
Peter Brinkhaus (1/6/2012)
Using 256MB memory it failed 7 out of 8 times. Ran it once with 512MB, failed also. Ran it once with 1GB, succesfully.
Excellent, a 32-bit confirmation, thank you Peter! Did you happen to try DBCC TRACEON (652, -1) to disable read-ahead to avoid the failure? I've not seen a single failure with read-ahead off yet (though of course performance is tragic).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 6, 2012 at 12:51 pm
SQL Kiwi (1/6/2012)
Peter Brinkhaus (1/6/2012)
Using 256MB memory it failed 7 out of 8 times. Ran it once with 512MB, failed also. Ran it once with 1GB, succesfully.Excellent, a 32-bit confirmation, thank you Peter! Did you happen to try DBCC TRACEON (652, -1) to disable read-ahead to avoid the failure? I've not seen a single failure with read-ahead off yet (though of course performance is tragic).
Performance with 256MB is tragic anyway, but I've turned the trace on, ran it three times and all runs succeeded. Here are the results:
Run 1 (full script):
Table 'StockMovements'. Scan count 1, logical reads 30316287, physical reads 92456, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 30596864, physical reads 64948, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 112196 ms, elapsed time = 219475 ms.
Run 2 (only quirky update):
Table 'StockMovements'. Scan count 1, logical reads 30316287, physical reads 92461, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 30596864, physical reads 64944, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 98936 ms, elapsed time = 176406 ms.
Run 3 (only quirky update):
Table 'StockMovements'. Scan count 1, logical reads 30316287, physical reads 92461, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 30596863, physical reads 64941, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 99481 ms, elapsed time = 172156 ms.
January 6, 2012 at 1:12 pm
Peter Brinkhaus (1/6/2012)
Performance with 256MB is tragic anyway, but I've turned the trace on, ran it three times and all runs succeeded.
Win! Thanks again.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 6, 2012 at 1:21 pm
No thanks. Interested in SQL2008 32-bit as well?
January 6, 2012 at 1:28 pm
Peter Brinkhaus (1/6/2012)
No thanks. Interested in SQL2008 32-bit as well?
Certainly. I ran the tests on 2005, 2008, R2, and 2012 64-bit because I wanted to see if this behaviour was introduced 'recently' or not. I installed a 2000 VM today, so I might give it a spin with that (once I modify the code to remove 2005+ only features). It would be great to get the same picture for 32-bit.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 6, 2012 at 3:37 pm
SQL Kiwi (1/6/2012)
Peter Brinkhaus (1/6/2012)
No thanks. Interested in SQL2008 32-bit as well?Certainly. I ran the tests on 2005, 2008, R2, and 2012 64-bit because I wanted to see if this behaviour was introduced 'recently' or not. I installed a 2000 VM today, so I might give it a spin with that (once I modify the code to remove 2005+ only features). It would be great to get the same picture for 32-bit.
Paul, strange results!
@@version Microsoft SQL Server 2008 (SP2) - 10.0.4064.0 (Intel X86)
Feb 25 2011 14:22:23
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
Configuration 256MB: the script doesn't terminate. The process is blocking itself.
Configuration 512MB: if I run the whole script statement-by-statement it terminates but the quirky update failes. Turning on trace 652 and running the quirky update only succeeds.
Configuration 1GB: again the script doesn't terminate. The process is blocking itself. After a server-restart, running the script step-by-step the statement 'SELECT TOP 8000000 ...' blocks itself and doesn't terminate.
Edit: removed remark about '2005+'
January 6, 2012 at 3:50 pm
Peter Brinkhaus (1/6/2012)
Configuration 256MB: the script doesn't terminate. The process is blocking itself.
The way I read your comments makes it sound like this mysterious self-blocking only occurs when TF 652 is OFF, would that be correct? Anything extra you can share about the nature of the blocking? (things like wait types and so on). I'm in the process of setting up a 32-bit VM here so I can install 32-bit SQL Servers and have a go myself.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 6, 2012 at 4:04 pm
Paul,
I don't think the TF 652 has something to do with it but I'm still trying to figure it out. I'll let you know when I've some better results. Taking a break now.
Peter
January 6, 2012 at 5:17 pm
J Livingston SQL (1/6/2012)
SQL Kiwi (1/6/2012)
J Livingston SQL (1/4/2012)
SO...until we have a budget that allows migration to 2012...I will continue with "quirky" for the tasks I use it for...feeling safe in the fact that, providing the 'safety check' is used..it will give me what I need........unless someone can provide a solution that is just as quick to run.???Just for my own curiosity, would you be able to give a brief summary of a typical requirement? Obviously I saw the example given in your code here, but I am curious to know whether you need to persist the running totals, what the broader use case description is...things like that. As I say, I'm just interested to know a bit more, I don't have a particular agenda.
Paul...primarily we use it for running totals on stock movements....this is run nightly.
The running stock figure is then used in further analysis for closing stock period comparisons/stock ageing/ stock turns...comparing current periods with historical periods....etc etc
(we rip the data out of a legacy system which cannot provide the details we require)
As I have already said...I haven't had any issues before now...and this occurrence ONLY materialized on a memory compromised PC....it has never occurred on our servers.
The reason why I raised it was because I was interested in understanding why it was failing on the PC...and I believe that you have identified the cause....for which I very much appreciate your efforts and detailed analysis.
many thanks
It would also be interesting if that problem causes a regular update to fail for that many rows... perhaps, just using ROW_NUMBER() over the PK.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 55 total)
You must be logged in to reply to this topic. Login to reply