I love your approach to careful consideration to query tuning and reviewing options and plans. I wish more developers did this. One caution would be - always test with a lot of rows, looking at the logical reads, this is a smallish table. Try and generate a sample load and run the query again. One small issue - in your top query you are not asking for an order by, in your bottom query you are. You should compare and contrast them each with ordering.
I just quickly created a SalesOrders table with 200,000 sales orders in it - still not huge by any stretch of the imagination. And ran the queries with the ORDER BY in each. I also played with indexes a bit.
With no clustered index on OrderID, just a non-clustered index on CustID The second query outperformed. Especially with the order by included in each. There was twice as many reads on the first query than the second query, and the cost percentages were 67% / 33% between the queries.
With a clustered index on OrderID and a non-clustered index just on CustID They performed in a similar speed and the exact same number of reads.
So I would suggest you increase the number of rows and do some more testing. But my final analysis on your queries -
You may find them behaving more similarly than you realize when you increase the rows, so keep that caveat in mind and test that way.
If all you ever want to return is the maximum OrderID for each Customer, and you want to determine that by the OrderID being the greatest OrderID then the second query out of these two is the best way to go from my mindset - it is a bit simpler and while ever so slightly more expensive based on subtree cost it is a quicker and easier to decipher statement. If you intend on adding other columns into your result set someday? Then the first query allows you do to do that.
Updated:
One of your comments under your question was:
Please keep in mind, that finding the best query in this question is a
means of refining the techniques used for comparing them.
But best takeaway for doing that- test with more data - always makes sure you have data consistent with production and expected future production. Query plans start looking data when you give more rows to the tables, and try and keep the distribution what you'd expect in production. And pay attention to things like including Order By or not, here I don't think it makes a terrible bit of difference in the end, but still worth digging into.
Your approach of comparing this level of detail and data is a good one. Subtree costs are arbitrary and meaningless mostly, but still worth at least looking at for comparison between edits/changes or even between queries. Looking at the time statistics and the IO are quite important, as is looking at the plan for anything that feels out of place for the size of the data you are working with and what you are trying to do.
You could use xp_cmdshell, or you could use xp_regread. Since xp_regread is 'safer' (I mean, you don't have to change any configuration options to use it), you could do something like this:
DECLARE @cpu_speed_mhz int,
@cpu_speed_ghz decimal(18,2);
EXEC master.sys.xp_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'HARDWARE\DESCRIPTION\System\CentralProcessor\0',
@value_name = '~MHz',
@value = @cpu_speed_mhz OUTPUT;
SELECT @cpu_speed_ghz = CAST(CAST(@cpu_speed_mhz AS DECIMAL) / 1000 AS DECIMAL(18,2));
I tested this on a few different computers and it seemed to get me what I needed. It may work for you as well.
Just remember that xp_regread is undocumented, and may blah blah blah like Microsoft ever deprecates anything anyway.
Hope this helps!
Best Answer
There are a few conceptual things to work through. When you run a tool like diskspd, you're benchmarking theoretical limits of your storage, not guaranteeing a specific performance profile of a query. Additionally, you're only testing for the configured pattern (2MB block size, queue depth of 32, 56 threads, 100% read [random? sequential?]). SQL Server has varied read/write patterns and there's no guarantee that your query follows this test pattern. Essentially, you're testing two different things with diskspd and a query.
The following two points are primarily valid if you're using a SAN.
Additionally, different storage tools run tests in different ways. sqlio sizes a 10GB test file with a null byte (
0x0). diskspd appears to have a varying pattern, but it repeats nonetheless.fio sizes with random data. I've only used vdbench once and have honestly not checked what it uses to fill files. I bring this up because most SANs will compress and deduplicate empty space and repeating patterns. As such, you're not necessarily testing performance on a files of a similar size (assuming your SQL Server data file is 10GB) on the SAN. Your data file can be compressed and deduped, to be sure, but probably not to the same level as files generated by diskspd.
This leads to a final point I'd like to make. Depending on your SAN's cache size, generally a 10GB file isn't large enough to actually even test your SAN performance (from an IOPS perspective). The SAN controller is probably able to compress and dedupe this test.dat file down to something of reasonable enough size to sit in controller cache and never actually touch the backing disks. As such, what you're testing is the path between the SAN controller and the OS, which you've identified as 1400MB/s.