November 27, 2008 at 6:33 pm
Hugo Kornelis (11/27/2008)
Jacob Luebbers (11/27/2008)
Hey Hugo,Hugo Kornelis (11/27/2008)
...
* The optimizer might choose a non-clustered index to drive the query
...
The technique also uses an index hint specifying the clustered index, with TABLOCKX to ensure that no other modifications could occur to the table during our UPDATE.
Hi Jacob,
Point taken. Though the flipside is that TABLOCK increases the chance to get an unordered scan instead of an ordered one. More on that below.
Jacob Luebbers (11/27/2008)
Hugo Kornelis (11/27/2008)
...
* The query engine might perform a merry-go-round scan
...
Jeff's article covers this (following on from comments from Gail on the merry-go-round index behaviour): http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url].
I see that Jeff defends his use of ORDER BY in a subquery by refering to observed behaviour, not to any documentation. Exactly the danger I am warning about.
In fact, this behaviour of TOP 100 PERCENT ... ORDER BY did in fact change with the release of SQL Server 2005, much to the chagrin of many people who were relying on this undocumented "trick". (In all fairness, I must admit that even some of Microsoft's tools rely on this behaviour. And that apparently a very influential customer cooerced Microsoft into bringing this behaviour back in SQL 2005 and SQL 2008, although it does now require a trace flag to be set). If this doesn't prove how dangerous it is to rely on these tricks, then what does?
Jacob Luebbers (11/27/2008)
Hugo Kornelis (11/27/2008)
...
* The optimizer might choose to use an unordered scan of the clustered index instead of the current ordered scan
...
The initial insert into the table is ordered explicitly. There was some lively discussion on whether or not the results of a subsequent unordered scan would reliably come back in that order without the guarantee of an ORDER BY... so far no-one has shown a case where they don't (to my knowledge).
I'll show you two. The first relies on how SQL Server allocates pages to tables. The first 8 pages come from mixed extents; after that all allocation is in uniform extents (i.e. a whole block of 8 pages is allocated to the table at once). I first create a filler table that allocates a mixed extent and some uniform extents. After deleting the table, the extents are free again, but they apparently keep their "mixed/uniform" mark. So than I allocate 6 tables that only use one page (all from the mixed extent), and then the actual table I will test with. The first 2 pages come from the existing mixed extent, and than a new mixed extent is allocated, after the previously allocated (and now empty) uniform extents. The rest of the table uses uniform extents, first the empty ones allocated previously (between the first and second mixed extent), then new ones (after the second mixed extent).
use tempdb;go
create database testit;
go
use testit;
go
create table filler (a char(8000));
go
insert into filler default values;
go 100
drop table filler;
go
create table fill1 (a int);
create table fill2 (a int);
create table fill3 (a int);
create table fill4 (a int);
create table fill5 (a int);
create table fill6 (a int);
insert into fill1 default values;
insert into fill2 default values;
insert into fill3 default values;
insert into fill4 default values;
insert into fill5 default values;
insert into fill6 default values;
go
create table testtable
(id int identity primary key, val int, filler char(4000));
declare @i int;
select @i = 1;
while @i < 5000
begin;
insert into testtable (val, filler) select @i, str(@i);
set @i = @i + 1;
end;
go
select * from testtable with (nolock) option (maxdop 1);
go
use tempdb;
go
drop database testit;
go
The second example mimicks concurrent behaviour. While you are filling your table, someone else frees some space. Space that will now be used for your table.
use tempdb;go
create database testit;
go
use testit;
go
create table filler (a char(8000));
go
insert into filler default values;
go 100
create table testtable
(id int identity primary key, val int, filler char(4000));
declare @i int;
select @i = 1;
while @i < 5000
begin;
insert into testtable (val, filler) select @i, str(@i);
set @i = @i + 1;
-- SIMULATE CONCURRENT ACTIVITY
if @i = 2000 truncate table filler;
end;
go
select * from testtable with (nolock) option (maxdop 1);
go
use tempdb;
go
drop database testit;
go
(Sorry for the all-lowercase by the way, but I just lost my post because I took too long and I can't be bothered to do the nice formatting again)
Jacob Luebbers (11/27/2008)
Hugo Kornelis (11/27/2008)
...
My point is that every undocumented behaviour that you observe, no matter how consistent it appears, should be considered to be a coincidal side effect of how the current version of the software interacts with your current hardware configuration and current data distribution. And that is NOT a safe foundation for building production software.
...
Agree with you here - however I'm willing to take that risk in certain cases. The massive performance gain in certain cases using this technique is compelling, and if the process using it can bear this risk (and the maintainers of that process are mindful of potential changes in this behaviour with updates to the engine) I say go for it.
Regards,
Jacob
This is where we will have to agree to disagree. I've witnessed this too often. When you warn about the danger, management is always "prepared to take the risk, and they will "of course monitor, and plan followup action". But when the shit hits the fan, it's suddenly your fault...
Maybe you have been lucky not to have experienced this yet 😉 But I won't take any chances 😀
Yep, the code you wrote works about the way you say it will, but I figured out the problem in your code in the second block. First, I wouldn't write a query using the NOLOCK hint unless absolutely necessary. Take your code in the second code block, and change your last query to use the query hint (index = 1) IN PLACE OF the (nolock) you have. Better yet, copy the query and run both (one with nolock and one with index = 1). You will see that the one with (index = 1) returns an ordered set.
Edit: And just for s and g's, I ran it also with no hint on the table with the clustered primary key, still returned an ordered result set.
Another Edit: Added a query using both hints (nolock, index = 1), returned an ordered result set.
November 27, 2008 at 9:24 pm
Lynn Pettis (11/27/2008)
Okay, can't wait to see Jeff's response. I was using a technique I picked up from him for completing running totals.
Hugo is putting out a lot of "what ifs" and he and I have already had this argument on the "Running Total" article. He hasn't been able to make your code or my code or any properly written running total code using the "quirky update" break. If you add the correct index hint to your code, he won't be able to break that, either.
I think that people are way too paranoid about undocumented features, especially this one. In order for them to release a service pack that would destroy the ability of the "quirky" update to work, they would have to rewrite half the engine to change the way clustered indexes work behind the scenes and how Update works behind the scenes. The way clustered indexes work and the way Update works with indexes are both fully documented. Putting 2+2 together, is apparently not.
The other thing is that using ONLY fully documented features is no guarantee that you're not going to have to rewrite code. For example, in SQL Server 2000 SP3a, it was fully documented as to what privs you needed to use the wonderful sp_MakeWebTask extended store procedure. SP4 came out and suddenly you needed SA privs to use it... a change to a fully documented procedure broke a lot of people's code.
Here's another fully documented feature right straight out of BOL...
[font="Arial Black"]SELECT Clause[/font]
Specifies the columns to be returned by the query.
Syntax
SELECT [ ALL | DISTINCT ]
[ TOP n [ PERCENT ] [ WITH TIES ] ]
::=
{ *
| { table_name | view_name | table_alias }.*
| { column_name | expression | IDENTITYCOL | ROWGUIDCOL }
[ [ AS ] column_alias ]
| [font="Arial Black"]column_alias = expression [/font]
} [ ,...n ]
... yet the boogers at Microsoft have deprecated it. It was absolutely wonderful for building up a Select, testing the hell out of it, and then easily converting it to an Update. Also makes for some pretty code if you format it correctly.
So, the continuous din from people insisting that only documented features be used because the undocumented ones could change any time are wrong because they change even the documented features. Use what you need at the time to get the job done. If you follow the best practice of testing service packs on non production machines before you put it into production, you'll never get caught with your pants down.
Heh... and until they either break the hell out of the engine or someone comes up with a better and faster way to do running totals, my recommendation would be to continue to use the "quirky" update to do them.
Just in case anyone wants to see the full article on using the "quirky" update to do running totals and other super high speed "miracles" that would normally require a cursor or while loop, here's the link...
[font="Arial Black"]Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5[/font][/url]
... Lynn, bless his soul, actually has it in his signature line.
I believe that Phil Factor can provide a link from the "Simple-Talk" forum that would provide similar methods.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 27, 2008 at 9:32 pm
Hugo Kornelis (11/27/2008)
Jeff Moden (11/27/2008)
Hugo Kornelis (11/27/2008)
What I was refering too is the lack of ROW_NUMBER() in SQL Server 2000. This means you'll either have to take your chance with IDENTITY, at the risk of gaps, as the author of this article did; or you have to use a correlated subquery to calculate the row number on the fly, which can result in dramatic performance as the amount of rows grows. Plus, the queries tend to get long and hard to understand.Nope... in SQL Server 2000, just do a SELECT INTO a temp table with the IDENTITY function and the author's code works just fine without any difficulty for length or understanding.
Hi Jeff,
You're right. When using SELECT INTO a temp table with the IDENTITY function (*), then there will not be any gaps and the range of A.ID-4 up to A.ID will always have 5 rows. But unless I overlooked something, this was not in the article. To me, the article appears to imply that any IDENTITY row can be used for this. And since many tables already have an IDENTITY column, often with gaps in the sequence due to deleted data or rolled back inserts, I thought it'd be better to point out this danger.
(*) There is another potential problem here. I know that there is only one situation where Microsoft guarantees that identity values are given out in the expected order when using ORDER BY in an INSERT statement, but I can never recall if this guarantee is for SELECT INTO with the IDENTITY() function, of for INSERT ... SELECT on a table with a predefined IDENTITY column. And I can never find this particular bit of documentation when I need it. I think that SELECT INTO with the IDENTITY() function is the supported scenario, but if you are going to use this in production you'd probably better doublecheck first, for my memory is known to .... aaahh, what was I going to say again? 🙂
Shoot... I know it wasn't in the article, Hugo... that's why I posted it! 😀
I'll see if I can find it, but I believe the only time that the IDENTITY function doesn't sort correctly on a SELECT INTO/ORDER BY is when the ORDER BY is based on column aliases and those column aliases refer to indeterminate functions used in the Select List.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 28, 2008 at 1:27 am
I believe that Phil Factor can provide a link from the "Simple-Talk" forum that would provide similar methods.
It wasn't me, but Robyn,
Robyn Page's SQL Server Cursor Workbench (24 January 2007 -http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/)[/url]
'--and here is a very different technique that takes advantage of the quirky behavior of SET in an UPDATE command in SQL Server'.
... it is an old Sybase SQL Server trick which has been around the SSC forums for years. The only problems I've ever had was when using this together with concatenation. In SQL Server 2000, one occasionally needed to add a blank string to the expression, but they fixed it in 2005.
Microsoft may huff and puff and say that the technique is unsupported but it is all wind. If they changed the behavior of UPDATE now, all sorts of systems dating back over the past decade wouldn't be upgradeable to the new version. Because the technique gets more and more important as the database size increases, I've seen it in complex financial systems at the heart of international companies. Microsoft Marketing Department would soon put a stop to the hotheads.
I have to agree with Jeff about sp_makewebtask. It was useless for its original purpose of putting out web-based reports, but it was a gloriously simple way of making regular spreadsheet-based reports, and we all used it as such. I don't think that Microsoft really did much research before they deprecated it.
Best wishes,
Phil Factor
November 28, 2008 at 2:09 am
Jacob Luebbers (11/27/2008)
Hey Hugo,Hugo Kornelis (11/27/2008)
Point taken. Though the flipside is that TABLOCK increases the chance to get an unordered scan instead of an ordered one. More on that below.Maybe I'm being thick, but I don't see from your comments how TABLOCKX will increase the chance of an unordered scan... am I missing something?
Hi Jacob,
That's quite technical. Basically, there are several requirements before you get a scan that uses the IAM pages (accessing pages allocated to the table in their "physical" order -the quotes are because the file may be physically fragmented or striped in which case the real physical order might be different- to take full advantage of read ahead and to minimize head movement) instead of a scan that uses the PrevPage/NextPage pointers to read leaf pages in their logical order.
1. The optimizer has to ask for an unordered scan. It will ask for an ordered scan if it needs the rows to be sorted in the logical order imposed by the index, either for a later operation in the plan or because of an ORDER BY on the outermost query. In other cases, it will ask for an unordered scan, defering the decision to the query engine
2. The number of pages in the table has to be above a certain threshold. The IAM scan is faster, but there is a fixed overhead cost of setting it up, so if only a few pages are involved that overhead will be more than the efficiency gain and the IAM scan will not be chosen. This is why I used lots of rows in my example code.
3. Either no locking (dirty reads) or full table locking has to be in effect. I think that this is to prevent a row that has already been read from being moved to another page (due to an update of the value in the clustered index column, or due to page splits) while the scan is in process, and then being read once more. With table locking, this can't happen, and with dirty reads, you state that you are willing to take the risk. However, I never really understood this reasoning, for a change to the indexed column can also cause a row to be read twice or not at all if it happens during an ordered scan....
Jeff's final solution (bottom of the article) doesn't use an ordered subquery - just the clustered index hint to guarantee order.
Sure, but I was writing this as a reply to your reaction on the merry go round scan. However, I later saw that I didn't read Jeff's article good enough. He never mentions merry go round scans, but only a "merry go round index" - what I would probably describe as a heavily fragmented index. This is unrelated. A merry go round scan (aka advanced scan) occurs when the engine detects that a scan is already in progress - in this case, it will "piggyback" on the existing scan, and once that finished it will return to the start of the index and read from there until it arrives it the starting point. This is an Enterprise Edition only feature, and it's described at http://msdn.microsoft.com/en-us/library/aa175258(SQL.80).aspx.
November 28, 2008 at 2:19 am
Lynn Pettis (11/27/2008)
Yep, the code you wrote works about the way you say it will, but I figured out the problem in your code in the second block. First, I wouldn't write a query using the NOLOCK hint unless absolutely necessary. Take your code in the second code block, and change your last query to use the query hint (index = 1) IN PLACE OF the (nolock) you have. Better yet, copy the query and run both (one with nolock and one with index = 1). You will see that the one with (index = 1) returns an ordered set.Edit: And just for s and g's, I ran it also with no hint on the table with the clustered primary key, still returned an ordered result set.
Another Edit: Added a query using both hints (nolock, index = 1), returned an ordered result set.
Hi Lynn,
My bad. I blundered when I wrote the code; I intended to use the locking hint Jeff recommends in his article, which is TABLOCKX.
I just tested with both "WITH (TABLOCKX)" and "WITH (TABLOCKX, INDEX = 1)", and I got an unordered result in both cases.
As I said to Jacob in my previous reply, both NOLOCK and TABLOCK allow an index scan to be driven from the IAM pages instead of the NxtPage pointers. Row- and pagelocking do not allow this in the current version (I tested on SQL Server 2005, SP2). However, I see no reason why Microosoft could not choose to implement some other mechanism to prevent duplicated or missed rows when using an index scan with row or page locking from the IAM pages - after all, they have already implemented such a mechanism for heaps. (Google "table scan from hell" for more info). I did not test this behaviour on SQL Server 2008 yet - for all I know, it may lareaby have been changed!! :w00t:
November 28, 2008 at 2:47 am
Jeff Moden (11/27/2008)
Lynn Pettis (11/27/2008)
Okay, can't wait to see Jeff's response. I was using a technique I picked up from him for completing running totals.Hugo is putting out a lot of "what ifs" and he and I have already had this argument on the "Running Total" article. He hasn't been able to make your code or my code or any properly written running total code using the "quirky update" break. If you add the correct index hint to your code, he won't be able to break that, either.
Hi Jeff,
All my "what ifs" are actually dependencies on undocumented behaviour you take. Fine if you choose to take those risks, but if you're going to encourage others to do so, without adding a waring in large red letters that the behaviour may change without notice, then I'm going to add that warning.
I just posted code that shows that a SELECT query with the index hints you recommend will not always return rows in sorted order. I'll admit that I did not (yet) break the update. More on that below.
I think that people are way too paranoid about undocumented features, especially this one. In order for them to release a service pack that would destroy the ability of the "quirky" update to work, they would have to rewrite half the engine to change the way clustered indexes work behind the scenes and how Update works behind the scenes.
The only reason I could not break the update is that the query optimizer creates a plan that forces an ordered clustered index scan. And I don't know about you, but I fail to see why it doesn't allow an unordered scan. Probably because some details of how exactly the update is processed internally. Details that might be changed - and that would not require rewriting half the engine.
The way clustered indexes work and the way Update works with indexes are both fully documented. Putting 2+2 together, is apparently not.
The way UPDATE works with variables in the SET clause is also documented. I never realised this before, but I just saw it when I was checking some other info. Here's the quote from Books Online for SQL Server 2005 (sept 2007 update). You can find it in the page about the UPDATE statement:
"Variable names can be used in UPDATE statements to show the old and new values affected, but this should be used only when the UPDATE statement affects a single record. If the UPDATE statement affects multiple records, to return the old and new values for each record, use the OUTPUT clause."
The other thing is that using ONLY fully documented features is no guarantee that you're not going to have to rewrite code. For example, in SQL Server 2000 SP3a, it was fully documented as to what privs you needed to use the wonderful sp_MakeWebTask extended store procedure. SP4 came out and suddenly you needed SA privs to use it... a change to a fully documented procedure broke a lot of people's code.
Agree. This is not Microsoft's usual style, but they sure emssed up here.
Here's another fully documented feature right straight out of BOL...
(...)
[font="Arial Black"]column_alias = expression [/font]
(...)
... yet the boogers at Microsoft have deprecated it.
They did? Hmm, that must have gone past me. Where is this documented? (Not that I mind much, I personally always preferred the "expression AS alias" version).
However, this actually just proves my point. Of course changes will be made. But the normal path for changes to documented features is to announce deprecation without removing support for one or two versions, then continuing to support it in backward compatibility mode only for one or two more versions. This will give you an advance warning, and plenty time to replace the code. Not so with changes to undocumented features - they can (and will!) be changed without notice. Remember views with TOP 100 PERCENT ... ORDER BY in SQL Server 2000, that stopped working in SQL Server 2005? Or GROUP BY always returning ordered results, even without ORDER BY, in SQL Server 6.5?
If you follow the best practice of testing service packs on non production machines before you put it into production, you'll never get caught with your pants down.
Except that some behaviour will only occur under heavy load. I had to go to great lengths to cause pages to be allocated out of order in my repro code, and I only did so because I knew that this would break your code. Low-use test systems will not usually get such allocations - but heavily used production systems might.
Heh... and until they either break the hell out of the engine or someone comes up with a better and faster way to do running totals, my recommendation would be to continue to use the "quirky" update to do them.
And my recommendation would be to calculate them on the client :D. And if that is really impossible, then use either a cursor or a set-based query with a correlated subquery, depending on the data distribution.
November 28, 2008 at 4:17 am
Below my Query is taking if some IDs are missing... 🙂
SELECT Accounts.ID, CONVERT(varchar(50),TransactionDate,101) AS TransactionDate, Balance,
( SELECT Sum( Balance )
FROM ( SELECT Top 5 A.Balance
FROM Accounts A
WHERE A.ID <= Accounts.ID
ORDER BY ID DESC
) AS A
) AS RunningTotal
FROM Accounts
November 28, 2008 at 4:19 am
This query takes care if some IDs are missing..... 🙂
SELECT Accounts.ID, CONVERT(varchar(50),TransactionDate,101) AS TransactionDate, Balance,
( SELECT Sum( Balance )
FROM ( SELECT Top 5 A.Balance
FROM Accounts A
WHERE A.ID <= Accounts.ID
ORDER BY ID DESC
) AS A
) AS RunningTotal
FROM Accounts
November 28, 2008 at 4:25 am
Try running that on a 1M rows table then tell us how long it takes to run... assuming your query is done before xmas!!!
November 28, 2008 at 4:48 am
I must admit I was wrong on this one....
USE tempdb
GO
CREATE TABLE dbo.Accounts
(
ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
TransactionDate datetime,
Balance float
)
go
DECLARE @i as INT
set @i = 0
while @i < 1000000
begin
insert into dbo.Accounts (TransactionDate, Balance) SELECT DATEADD(DD, @i, GETDATE()), CEILING(RAND() * 999)
SET @i = @i + 1
end
--SELECT * FROM dbo.Accounts
go
--activate execution plans before running this one
go
SELECT Accounts.ID, CONVERT(varchar(50),TransactionDate,101) AS TransactionDate, Balance,
( SELECT Sum( Balance )
FROM ( SELECT Top 5 A.Balance
FROM Accounts A
WHERE A.ID <= Accounts.ID
ORDER BY ID DESC
) AS A
) AS RunningTotal
FROM dbo.Accounts
go
DROP TABLE dbo.Accounts
That 1M rows query only processes 95MB of data (from stats in actual plan). I was expecting some sort of triangular join here, but the speed is really not all that bad. My statement returns the 1M rows in les than 8 sec (download included)... so that version might very well make it to production.
Thanks pp.pragnesh
November 28, 2008 at 4:51 am
... of course the cost of this query would be quite more considerable (maybe 2 or 3 times) if this was a real table with 1000 bytes of data and if the clustered index was on the date column is it probably would. Just something to keep in mind when using in real life.
November 28, 2008 at 9:21 am
Hugo Kornelis (11/28/2008)
Yes, I agree... you cannot rely on anything other than an OrderBy on Select statements.
And, yes... I believe that everyone knows that "deprecation" comes with plenty of warnings over at least 1 version and frequently more.
I'll have to look for the deprecation warning on the alias = expression thing, but I do remember it being in the MS documentation for 2k5... I just can't remember where. Might have been in the "what's new" bit of BOL, but I don't know for sure. I'll look for it when I get a bit of time.
All of the things you say are mostly true... everything you say about a change, could actually happen. But, the "quirky" update and the way it operates has been in existance since before the beginning and hasn't changed because it's at the root of the engine. You can certainly post your warnings about the risk, but I do think they are a bit on the over cautious side... psuedo-cursors of this nature have been around for a very long time.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 28, 2008 at 9:24 am
Ninja's_RGR'us (11/28/2008)
I must admit I was wrong on this one....That 1M rows query only processes 95MB of data (from stats in actual plan). I was expecting some sort of triangular join here, but the speed is really not all that bad. My statement returns the 1M rows in les than 8 sec (download included)... so that version might very well make it to production.
Thanks pp.pragnesh
Actually, triangular joins are present... the thing is that they're very very small... limited to 4 or 5 rows each and they "stop" when the rules of distinction have been met in this case. Like I said in the article I wrote about triangular joins and just like most anything else... they're not ALL bad... "It Depends".
--Jeff Moden
Change is inevitable... Change for the better is not.
November 28, 2008 at 9:34 am
Thanks for the correction Jeff... you clearly said what I meant silently :P.
A 5 row triangular join won't kill many servers.... but what if the report needs to return the running total for @X days... that could quickly because a huge bottleneck for the server. I have no time to test now, but my guess is that going from 5 to even only 15 days could really hurt that query!
Viewing 15 posts - 31 through 45 (of 57 total)
You must be logged in to reply to this topic. Login to reply