Stats IO Oddities

Josh Darnell collects a few cases where SET STATISTICS IO ON doesn’t behave quite as you might expect:

The first one comes from a post on Database Administrators Stack Exchange: STATISTICS IO for parallel index scan

To summarize the situation, the OP had a query that was scanning a clustered index. They were seeing significantly higher numbers reported in the logical reads portion of the STATISTICS IO output when the query ran in parallel vs. serially (with a MAXDOP 1 query hint). There is a demo of this behavior in the post, so I won’t reproduce it here.

There are several interesting cases in here, so check them out.

Related Posts

COUNT(*) Versus COUNT(1)

Lukas Eder takes on the myth that COUNT(*) differs from COUNT(1): Now that we know the theory behind these COUNT expressions, what’s the difference between COUNT(*) and COUNT(1). There is none, effectively. The 1 expression in COUNT(1) evaluates a constant expression for each row in the group, and it can be proven that this constant expression will never evaluate to NULL, so effectively, we’re running COUNT(*), […]

Read More

Workload Analysis with Query Store

Erin Stellato shows how you can mine the Query Store tables to learn more about your workload: The query text and plan are stored in two separate tables, sys.query_store_query_text and sys.query_store_plan, respectively.  The text is stored as a nvarchar(max) data type, and the plan is stored as varbinary(max).  This means that the data can be […]

Read More

Categories

June 2019
MTWTFSS
« May Jul »
 12
3456789
10111213141516
17181920212223
24252627282930