Microsoft recently introduced a handy new feature to query plans: waitstats. That’s right, you can get actual runtime waitstats for a query you run.

Though, I’ve seem to run into a small snag with this new feature. I’m running two Surface Pro machines. My older Surface Pro 3 has SQL 2016 SP1 (13.0.4202.2) and is a core i5 with 8 GB of RAM and a 256 GB SSD drive. My newer machine is a Surface Pro 4 with the same specs but 16 GB of RAM. It’s running SQL 2016 SP1 (13.0.4001.0).

On both machines, I query something very simple:

SELECT*
FROM AdventureWorks2012.Person.Address
WHERE StateProvinceID in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19)

or to make things even more simple, query without the predicate.

One my newer Surface, the query plan contains waitstat data. My older machine’s plan does not. For fun I decided to query a 1 million row table and still no waitstats. Is my older machine faster than the Pro 4?

Here’s what the Pro 4 plan looks like:

The waitstats don’t appear at all in my older Surface which has a newer version of SQL. So what is 4202.2?  It’s a refresh for Master Data Services and R. Could that really have broken my query plan waitstats?

I doubt it but maybe. I updated to make the two equal. Did the waitstats go away?

No.

Is this an issue with my Surface Pro 4? Both databases are a copy of an old backup so they are identical. Both DB options and sys.configurations match.

While waiting for the patch to apply, I moved the plan from the newer machine to my older Surface so I could screen shot it and write this blog. Hold up! The wait stats are missing. I checked the XML and sure enough they are there. Could this actually be an issue with SSMS?

New machine: SSMS 13.0.16106.4

Old machine: SSMS 13.0.15000.23

The older SSMS plans didn’t include the waitstats at all, not even in the XML.

After upgrading to SSMS 13.0.16106.4 on my older machine the waitstats appeared. This highlights the importance of keeping not only the database engine updated but also the tools you use to manage it.

If you’re using SQL 2016 be sure to update SSMS to get all of the latest features.

On a side note, updating to the latest version still did not fix the touch screen bug I detailed here: New SSMS bug?