Wait Stats, Here and gone again?

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:

4001 plan 1 waits

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?

One thought on “Wait Stats, Here and gone again?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s