SQL and the like - All Comments

re: [BUG] Inserts to tables with an index view can fail

Dave Ballantyne — Sat, 07 Jan 2012 12:18:22 GMT

Hi Peter,

Hmm , thats a pretty tenuous piece of documentation :)

From a user perspective there should be no difference between using literal values and variables that is why im calling it a bug.

Indeed, even if you :

insert into myTable(Id,InView,SomeData)

select 1,'N','a'

union all

select 1,'N','a'

the problem also occurs.

Im not seeing it as the same as your example , as there is a definate path of logic here that should be obeyed. The calculation is only useful and can only be used if InView = 'Y'. Though i doubt that this will be something simple to solve by the team.

This also does make me think of the inefficiences here too, the operations are firing when the output will not be used. Maybe thats also something worth exploring later.

Dave

re: [BUG] Inserts to tables with an index view can fail

Peso — Sat, 07 Jan 2012 08:01:17 GMT

It's not really a bug, it's a feature and it's documented in Books Online for SQL Server 2005.

See "Expressions in queries" here technet.microsoft.com/.../ms143359(SQL.90).aspx

See this example, which is the same thing.

SELECT Column_Name

from (

SELECT Column_Name

FROM (

select 'staff' as Column_Name union all

select '234000' as Column_Name

) as Table_Name

WHERE ISNUMERIC(Column_Name) = 1

) as d

where CAST(Column_Name AS INT) <= 1000000

re: Book review - SQL Server Secret Diary (Know the unknown secrets of SQL Server)

GrumpyOldDBA — Thu, 05 Jan 2012 13:25:37 GMT

As I know from a number of "discussions" I've had on blogs and forums you are correct in saying "It just depends". I put it down to the joys of the internet whereby equal amounts of rubbish and quality can get the same coverage ( perhaps more rubbish to be honest! ).

I've been asked many times to document how I go about performance tuning, say, a stored procedure. Trouble is it's just not linear and is full of if 1 or + 3 or 4 or if 6 and 7 then 2 so in the end it's just impossible to plot a logical path. It's easy to generalise but that hardly makes for such a book title.

re: SQL 101 : Without “ORDER BY”, order is not guaranteed.

Dave Ballantyne — Mon, 02 Jan 2012 18:02:07 GMT

Hi Paul, sorry for the lateness of my reply.

One possible way to reproduce is to delete a row and then insert a row, this *should* then use the same row slot on the page and cause an unordered result set.

drop table #t1

create table #t1

(

id integer identity

)

insert into #t1 default values

go 2

select * from #t1

delete from #t1 where id =1

insert into #t1 default values

select * from #t1

As for heaps, well they are just a collection of rows with no order. Any scan will start at page 0 and go through to page N following the page link chain...

Hope that helps

Dave

re: Book review - SQL Server Secret Diary (Know the unknown secrets of SQL Server)

aolcot — Mon, 02 Jan 2012 15:04:57 GMT

I actually spent a couple of quid buying this book a few months back and I agree with your comments that it isn't exactly the best and probably one to avoid in all honesty.

re: Extended Events - inaccurate_cardinality_estimate

mcflyamorim — Tue, 06 Dec 2011 17:22:17 GMT

Well done, I really liked this new xEvents...

Voted.

Cheers

Fabiano

re: SQL 101 : Without “ORDER BY”, order is not guaranteed.

paulbarbin — Tue, 29 Nov 2011 21:53:07 GMT

Great reference. You are absolutely correct, and this is definitely 101 stuff. I've been preaching it to developers for decades(?). But I appreciate the academics.

My personal experience shows the execution plan pretty much dictates the order if ORDER BY isn't given. That is, if a clustered index scan comes back in clustered index order. Nonclustered index scans come back in nonclustered index order.

I didn't realize parallelism would change the order but that certainly makes sense.

Finally, I thought perhaps a heap might always return the same order since I "THOUGHT" a table scan would just go in RowId order (i.e. the order they were inserted). As any self respecting DBA, I would NEVER rely on this!

But when troubleshooting a developer's code lately, I saw an issue that appeared to have been caused by an unordered resultset. I am yet to actually prove it and I guess I was wondering two things. Is there anyway to reproduce the different ordering reliably? And what is the technical reason why the order is different if it's a heap (temp table too)?

Thanks, mainly just academic, but I'd really like to know.

re: Rows or Range, What’s the difference ?

mcflyamorim — Tue, 22 Nov 2011 20:50:44 GMT

Hi Dave I only found your blog right now, congrats for the posts, I loved… lot of things to read :-).

There is another difference I would like to mention,

The execution plan window spool operator (used to process the window) as two ways for storing the frame data, on-disk or on-memory. Unfortunately the default frame (RANGE…) always use the on-disk window spool… the ROWS frame use an on-memory worktable if the number of rows is less than 10000 rows… If the number of rows is greater than 10k it will also use the on-disk spool.

I saw in your video at SQLBits you said the FIRS_VALUE function was not good on performance… In fact the problem was that you were using the default frame (RANGE…) and it was using the on-disk window spool, if you change the frame to ROWS, it will be better than the old solution (CROSS APPLY) because it will use the on-memory window spool.

You could use xEvents to capture the warning (window_spool_ondisk_warning), you can also see the difference between windows spooling on disks looking at the results of statistics io, the worktable will show lots of page reads.

I think there are some scenarios where MS could change this behavior, for instance if the ORDER BY clause is unique (as in your sample) even if we use the RANGE frame we know we could use the ROWS and consequently use the in-memory worktable…

Also, this 10000 number is fixed, I think it should use the same model on spill to disk if the memory grant has been exceeded, use an rowsize base to know how much memory to grant…

I think this is something that could be improved on SQL2012.

What do you think?

Cheers

Fabiano N. Amorim - SQL Server MVP

Twitter: @mcflyamorim │Blog: http://blogfabiano.com

re: Denali Paging–Is it win/win ?

Dave Ballantyne — Thu, 11 Nov 2010 10:36:29 GMT

Hi Steve, can i say , "it depends" ? :)

I wouldnt say that there are hard and fast rules for a tipping point , its going to be down to data size etc.

Maybe the most efficient method would be to use the denali functionality for 'low' pages but Paul's for 'high' pages. Will make for more maint, but if it is being executed thousands of times , it should make a difference overall.

re: Denali Paging–Is it win/win ?

steveh99999 — Wed, 10 Nov 2010 21:46:30 GMT

excellent bit of research Dave - was wondering, did you fnd out the tipping point where the new functionality starts to become less efficient ?