Sql-server – SQL Server IOPS

sql server

I'm trying to evaluate the IOPS of TPCH queries on SQL Server. Specifically, for TPCH query 13, using "set statistics io on" and "set statistics time on", I got:

Table 'orders'. Scan count 9, logical reads 257826, physical reads 2, read-ahead reads 237643, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'customer'. Scan count 9, logical reads 33816, physical reads 1, read-ahead reads 33662, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I calculated the total physical reads issued by this query as the sum of "physical reads" and "read-ahead reads" for each table. For this example it is (2+237643+1+33662)=271308

The elapsed time for this query is: 43901 ms = 43.901 s

 SQL Server Execution Times:  CPU time = 21795 ms,  elapsed time = 43901 ms.

So per my understanding, the average IOPS of this query is (271308/43.901 sec=6180/sec).

However, I used windows performance counter to get IOPS for sqlserver:

Get-Counter -Counter "\process(sqlservr)\io read operations/sec" -SampleInterval 1 -MaxSamples 3600

And the IOPS shown for that query is only hundreds:

"(Pacific Daylight Time)"   "\process(sqlservr)\io read operations/sec"
"07/01/2016 14:15:55.782"   "470.67705754299271"
"07/01/2016 14:15:56.792"   "380.1531707549322"
"07/01/2016 14:15:57.809"   "463.43367654026179"
"07/01/2016 14:15:58.818"   "488.44764282221558"
"07/01/2016 14:15:59.828"   "457.38267181343429"
....

So here is what I'm confused:

What should be calculated as IOPS of this query? Is it total physical reads divided by total elapsed time, or is it the average of windows performance counter results?
Per my understanding, each physical read reads one 4k physical page from disk, then each "io read operations" shown in windows performance counter could read multiple pages? How many pages are read for each IO and where this number is configured?

Thanks!

Best Answer

Point 2 is where your confusion lies, 1) Pages in SQL Server are 8KB 2) SQL Server does not only issue 8KB (or 4KB reads) it will issue them at varying sizes, which can be bigger so your IOPS would be smaller.

Related Solutions

Sql-server – Which of these queries is best for performance

Sometimes I wonder if SHORT scripts really is the best thing to focus on.

The size of a script has little to do with how efficiently the query will execute. A more compact statement will likely consume fewer resources in terms of compilation, but (re)compilation is usually a rare occurrence in a live system.

Fewer table accesses is usually desirable, though, and this does lead to more compact code.

Very generally speaking, a smaller execution plan will yield better results, and a lower estimated cost will yield better results. Again, though, it's highly situational. Cost estimates in particular can be way off in some cases. It's important to measure the actual execution time, because at the end of the day, that's what matters.

With left joins i can achieve what i want with just a few lines. But then I tried with a longer script, using unions. Which is the best method?

First of all, we need to know how much data will be in these tables in a real system. Right now there's so little it will be difficult to use the STATISTICS TIME performance metrics to figure out a winner -- the results that come back will be dominated by factors other than the query execution. With more data, it's likely the plans will change, thus rendering the comparison here moot.

Having said that, by looking at the query plans as they are now from a logical point of view, the first one is the winner.

You can see that the Clustered Index Scan of quantities appears once in the first plan, while it appears four times in the second one. The second plan also contains an expensive Distinct Sort as a result of using UNIONs (this operator could be eliminated by using UNION ALLs instead, which won't change the results).

The first query could also probably be improved, by getting index seeks on the colors and sizes tables, instead of table scans. It might be worth trying a hash match plan as well (which is what you'll probably see when quantities and products are larger), but for tables this small, the startup cost may be too much overhead to be of benefit.

What I would suggest you do is run each of the statements you want to test 10,000+ times in a loop, figure out the average execution time, and then compare.

Sql-server – Multi-statement TVF vs Inline TVF Performance

Your numbers table is a heap and is potentially being fully scanned each time.

Add a clustered primary key on Number and try the following with a forceseek hint to get the desired seek.

As far as I can tell this hint is needed as SQL Server just estimates that 27% of the table will match the predicate (30% for the <= and reduced down to 27% by the <>). And therefore that it will only have to read 3-4 rows before finding one that matches and it can exit the semi join. So the scan option is costed very cheaply. But in fact if any palindromes do exist then it will have to read the whole table so this is not a good plan.

CREATE FUNCTION dbo.InlineIsPalindrome
(
    @Word NVARCHAR(500)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
    WITH Nums AS
    (
      SELECT
        N = number
      FROM
        dbo.Numbers WITH(FORCESEEK)
    )
    SELECT
      IsPalindrome =
        CASE
          WHEN EXISTS
          (
            SELECT N
            FROM Nums
            WHERE N <= L / 2
              AND SUBSTRING(S, N, 1) <> SUBSTRING(S, 1 + L - N, 1)
          )
          THEN 0
          ELSE 1
        END
    FROM
      (SELECT LTRIM(RTRIM(@Word)), LEN(@Word)) AS v (S, L)
);
GO

With those changes in place it flies for me (takes 228ms)

Best Answer

Related Solutions

Sql-server – Which of these queries is best for performance

Sql-server – Multi-statement TVF vs Inline TVF Performance

Related Question