The Heap™ – Consultancy ©®

Andriy M

7:07 AM

UDTT clash isn't the only problem you've got with remote TVF calls. — Andriy M 1 min ago

Tom V

8:08 AM

Morning

@swasheck thanks for your input yesterday

Andriy M

morning

ypercubeᵀᴹ

Haha, I'm reading Aaron's blog post about the VS outage ;)

> And I do sincerely hope they fix those object names – why a table needs a tbl prefix is beyond me.

identity is reserved. They had to use tbl_identity ;)

Tom V

8:25 AM

Please give me 15 minutes before closing this

0

Q: data warehouse automation tool: benefit or bane?

Today while investigating ETL and data warehouse solutions like Kettle, I came across a new concept (for me) called data warehouse automation tool. I also looked at the white paper here, which indicates there are few vendors in this space. One reaction I have to these tools is: whoa, anyone p...

I'd like to weigh in a second opinion to avoid the vendor position ending up the only truth

Andriy M

@TomV You could post a stub, delete, edit, undelete.

Tom V

@AndriyM Ah, good idea

ypercubeᵀᴹ

@TomV Why? Seems like a valid question to me.

Did I miss something?

Andriy M

8:44 AM

@ypercubeᵀᴹ If only the fact that four people have voted to close it as primarily opinion-based :)

I'm one of those four, as it happens. And if I hadn't chosen "opinion-based", I'd've probably ended up choosing "too broad".

Tom V

@ypercubeᵀᴹ I think it's a 50/50 but given that I noticed close votes rising while I was thinking about answering it made me realize that it could have been closed by the time I finished typing

Daniel Hutmacher

Good morning, Heapsters. If you're bored at work or in a reading mood, I would love some feedback on my new post on creating a T-SQL function for calculating medians.

ypercubeᵀᴹ

9:08 AM

@DanielHutmacher Nice!

wBob

@DanielHutmacher You can do that in SQL 2016 with R now look : ) Not to say you should of course ...

ypercubeᵀᴹ

Have you done any performance tests, to compare against the OFFSET/FETCH and the PERCENTILE_CONT methods?

Daniel Hutmacher

@ypercubeᵀᴹ Itzik did, and the OFFSET-FETCH was a bit quicker if memory serves. I think it came down to whether the data was high or low density (few groups with a lot of rows vs many groups with few rows). I'll run some tests.

Mark Sinkinson

@ypercubeᵀᴹ Aaron did on this post. sqlperformance.com/2012/08/t-sql-queries/median PERCENTILE_CONT runs like a three legged dog

ypercubeᵀᴹ

@MarkSinkinson I'm reading that, just now.

Daniel Hutmacher

9:14 AM

@wBob yikes. I'm still too scared (and statistically challenged) to do anything with R, but that'll probably change sometime around 2029 when my customers are on SQL Server 2016...

wBob

@DanielHutmacher lol feel your pain. It would be interesting to do some perf testing with it. I wouldn't expect it to do that well but worth a look. Now to find the time ...

Royi Namir

Hello and good morning. Basically I have 2 questions which one relates to the other.
1) Does `select top 5 * from T` actually translated to `order by pk` ? and if not , so `order by` what?
2)Will SQL server puts more effort with `select top 5 * from T order by PK desc` than `select top 5 * from T`?

ypercubeᵀᴹ

@RoyiNamir 1) No. It's ORDER BY "whatever you like";

Royi Namir

So what is the order by that SQL provides result with ?

( I didnt specify "whatever you like")

Tom V

@RoyiNamir Whatever happens to be the output of the best plan he could come up with satisfying your requirements (which don't include a sort)

ypercubeᵀᴹ

9:28 AM

@RoyiNamir "Whatever SQL Server likes at the moment you run the query"

Royi Namir

oh

Daniel Hutmacher

@RoyiNamir, assuming you're just selecting from a single table, in most cases the query will use the order of the clustered index (or heap) that the table is stored in, but this is not guaranteed. <guesswork> Sometimes, like with fragmented clustered indexes, SQL Server may find it quicker to just read the physical storage order from the drive (as opposed to the order of the index pages), in which case you will get a random-like order. </guesswork>.

For short, if you don't specify ORDER BY, the output is not ordered.

ypercubeᵀᴹ

TOP without ORDER BY says "get me 5 rows, I don't care which"

Royi Namir

Thanks. Regarding the second question ( pedant POV) : Will it take more time to run :
`select top 5 * from T order by ClusteredIndexColumn desc` VS `select top 5 * from T order by ClusteredIndexColumn asc` ? I mean : the first query should SORT IT DESC and THEN yields 5 results , while in the second query , it is already ordered ( clustered index) so all it has to do is yield 5 first records.

Am I right?

(or is it really smart and the data can be read straight from end to start)

Daniel Hutmacher

No, the plan is the same, the difference is whether the scan goes forward or backward.

Try both queries in SSMS, view the estimated query plan (Ctrl+L). If you hover over the Clustered Index Scan operator, you'll see Ordered: True. If you hover over the arrow just after the Scan, before the Top operator, it'll estimate 5 rows.

Tom V

9:43 AM

Since you are saying deadlocks are canceled by restarting, are you confusing locks with deadlocks? deadlocks are resolved by the engine pretty quickly — Tom V 21 secs ago

wBob

Backwards scans can't parallelise, interestingly enough.

Tom V

Happens all the time with our developers and I hate it

Royi Namir

@DanielHutmacher I just tried that. ( actual execution plan). But what is the meaning of "estimated number of rows" ? AFAIK , the REQUEST operators are from left to right. So TOP is asking row by row from the Clustered index Scan and that , in turn , yielding row by row. so where is the estimation here?

ypercubeᵀᴹ

@MarkSinkinson , @DanielHutmacher I have an alternative query (that uses PERCENTILE_CONT). See in the SQLfiddle. @AaronBertrand you may be interested to compare against your (2012+2). I tried in SQLfiddle but I guess even 1k rows is too much for it and I get errors.

Daniel Hutmacher

@RoyiNamir Exactly. The Top operator tells the Scan operator five times to send one row, and in which order.

@ypercubeᵀᴹ Curious! Will check.

Royi Namir

9:58 AM

@DanielHutmacher Yes , but if it asks 5 times , then why does it shows : "Estimated Number of rows?" it suppose to show only "ACtual number of rows". I don't see why estimation is needed when it knows exactly how many times it requested a row.

Daniel Hutmacher

@RoyiNamir You could have less than five rows in the table, I suppose. :)

Mark Sinkinson

@ypercubeᵀᴹ Not sure if SQL Fiddle just doesn't like me, but I can only see the Schema pane completed

ypercubeᵀᴹ

@MarkSinkinson Yah, I get errors, too. Here is the query:

SELECT d.SalesPerson, m.Median
FROM
    ( SELECT DISTINCT SalesPerson FROM dbo.Sales ) AS d
  OUTER APPLY
    ( SELECT TOP (1) Median = PERCENTILE_CONT(0.5) WITHIN GROUP
             (ORDER BY Amount) OVER ()
      FROM dbo.Sales
      WHERE SalesPerson = d.SalesPerson
    ) AS m ;

(and yeah, it has TOP without ORDER BY ;) @RoyiNamir

Royi Namir

:-)

Mark Sinkinson

10:18 AM

@ypercubeᵀᴹ It's appears to be a little faster at lower volumes, but once you start to scale then the standard PERCENTILE_CONT is faster

ypercubeᵀᴹ

@MarkSinkinson Thnx! I guess you tried with the NCI index as Aaron's blog? ON dbo.Sales(SalesPerson, Amount)

Mark Sinkinson

10:34 AM

@ypercubeᵀᴹ I used the schema from the Fiddle with that NCI

ypercubeᵀᴹ

@MarkSinkinson Great thnx. I had some hope that the "group by first, then outer apply" might be more efficient than the "percentile first, then group by" :(

Mark Sinkinson

@ypercubeᵀᴹ Someone better at doing performance comparisons may have more useful results. Plus we're only running Microsoft SQL Server 2012 - 11.0.5058.0

Tom V

12:16 PM

-1

Q: I want to write query as per business requirement

Universe Addition for Historic Data: Expired Contact Company Associations with companies as defined in universe, provided the record has either Committee or Compensation info, and belongs to Company which is tagged to index-either Russell3000 or S&P500. (Index will be added incrementally here, ...

sql sql-server-2012

no you don't, you want us to write a query as per your business requirement

Mark Sinkinson

1:12 PM

@TomV I was expecting him to edit the query once he said he would provide the information...

ypercubeᵀᴹ

0

Q: Equalize datafile size

I have a DB (Microsoft SQL server 2008 R2 (10.50.1600.1)) that has 28 datafiles with all different filesize. Some are 10GB, some 15GB, some 27 GB, etc. The difference in filesize is a historical fact. Different DB managers, forgotten autogrowth options, max size, ... My question is, how can I equ...

sql-server sql-server-2008-r2 datafile

Is this worth reopening?

Tom V

1:38 PM

@MarkSinkinson I hope, his previous questions didn't really turn out well

Mark Sinkinson

1:55 PM

Somehow he fluked a +14 on a question. Probably felt like a god after that...

Tom V

@ypercubeᵀᴹ I think it's an interesting problem but may need some more information about spaceused and things like that in each file

ypercubeᵀᴹ

@TomV What would the solution be? Backup the db and restore to 10GB size files?

Tom V

I'm not sure, that's why I'm interested in the possible solutions (which I hope I'll never need)

Can you add more details about what's in the data files? Is there enough empty space in them to perform the downsizing or does data need to be migrated between files before they can be evened out? Also, any reason you want to keep multiple files and not have everything consolidated in one file? — Tom V 10 secs ago

Transcript for

The Heap™ – Consultancy ©®