Thursday 11 October 2018

SSMS 18.0 - Actual vs Estimated Rows

Last week Microsoft released SQL Server Management Studio 18.0 into public preview, here's a link so you can read about the new and improved functionality it offers.

One significant change is the addition of actual vs estimated row counts onto the showplan operators in execution plans (only actual, not estimated...which kinda makes sense).

Here I'm running a very simple bit of code on some DMV's (namely exec requests and sessions) to demonstrate this addition.

Here's an execution plan in a previous version of SSMS:

But in new SSMS we get some extra info:

If we look at the Table Valued Function on the far right of the plan (because that's where plans start) we can see a time statistic (0.00s) and under that "69 of 70 (98%)", hovering over the operator to bring up the tooltip shows what the numbers mean:


We can see here for this operator that the actual number of rows is 69 whilst the estimated was 70 (a 98% accuracy) so any skew in row counts are now much more visible, this is really useful when it comes to really big numbers in execution plans.



In this example (from the same query) we've got the actual number of rows displayed correctly (2) but its estimated value doesn't match what is seen in the tooltip (69 vs 8.3666). Worth noting that the values do get rounded so 8.3666 would be displayed as 8. 

Originally I thought it had got the 69 in the tooltip wrong but in looking for a bug I'd actually missed the glaringly obvious; because SQL Server has to times rows by the number of operator executions; 8.3666 x 8.30662 = 69, so no bug, just me not looking hard enough!

I like this release of SSMS, I really do and despite all my love for Operations Studio, now renamed to Azure Data Studio it's great to see the continued development of Management Studio.

Tuesday 2 October 2018

Embracing Learning

The recent release of SQL Server 2019 offers new and improved features for data professionals. There's already lots of information out there about the latest bells and whistles; here's a link to start with and if you haven't already then you can download yourself a copy and have a good look around. 

If we look at the added functionality or support for different technologies between the 2016 and 2019 releases then clearly there's a lot of learning required to stay up to date; new introductions like Linux, containers, Kubernetes, R, Python, Apache Spark and lots more provide quite a list of new capabilities for DBA's and developers to put to use within their data platforms so gaining a good understanding of these technologies is becoming more and more vital.

But staying up to date can be difficult, especially in a technical landscape that is rapidly evolving as it is in current times but it's also a time where embracing the learning opportunities out there will undoubtedly be massively beneficial for those willing to invest in their career management.

I say "willing" because ultimately it is a choice, and with all the learning material being made available from vendors and from within the community it should be a very straightforward choice to make. Whilst it's not going to be free; it is going to take some time, money or more commonly both, the benefits far outweigh any personal contribution (or if you prefer to view it, sacrifice) that you'll be making. 

The main point that I want to put across is that right now, more than ever, it's become absolutely essential for anyone working in a data environment to take ownership of their personal development.

Some may be unsure where to start, which isn't surprising considering the rate of change but that's no reason to simply sit back either. Have a think about some of the factors that go into learning roadmaps; job requirements, your personal career aspirations, areas of interest, new technologies and of course, marketability all need to be considered (yes, the latter does matter) and then map to what training material is out there. It's virtually impossible to not find what you're looking for and if you're stuck for ideas - hey, ask away!


An early SQL conference.

So if you are just watching and waiting take the release of SQL Server 2019 and use it as the perfect opportunity to kick start your personal development journey; go through the release material, explore the new features and supported technologies, check out the changes to existing features, read blogs, watch videos, buy or download some books and attend events like conferences and user groups - there's plenty of choice out there to fit any favoured learning style and many require nothing more than an hour or two of your time. Above all, investing in yourself is the most rewarding investment you can ever make.