Press "Enter" to skip to content

Author: Kevin Feasel

Rows Read Versus Rows Returned

Published 2020-05-19 by Kevin Feasel

Hugo Kornelis explains another issue with arrow widths in tools like SQL Server Management Studio:

The visual of the arrows in an execution plan strongly suggests that they represent the flow of rows from one operator to another. And hence, the width of that arrow strongly appears to be an indication of how many rows are passed between the connected operators. That used to be always the case. But unfortunately, this changed in December 2017 (or earlier, but that was when I first noticed the change).

The root cause is the addition of a new property, returned by Scan and Seek operators: Number of Rows Read (as well as its estimated counterpart: Estimated Number of Rows to be Read). Now don’t get me wrong, those properties are awesome and I’m super happy that they were added to execution plans. When a filter condition is pushed into a scan or seek operator as a Predicate property, I want to be able to see how effective it is, and comparing Actual Number of Rows to Number of Rows Read (or Estimated Number of Rows to Estimated Number of Rows to be Read) helps me assess just that. Which in turn can help me decide whether I should change my indexing, or try to rewrite the query.  But I digress.

Click through to learn what the issue is. Hugo describes a tricky situation where there are two valuable measures but only one way to show them. If you agree with Hugo’s preferences, here’s a Feedback item for you.

Comments closed

Generating Scripts to a Notebook with SSMS

Published 2020-05-19 by Kevin Feasel

Taiob Ali tries out a new feature in SQL Server Management Studio:

SQL Server Management Studio (SSMS) was released on April 7th, 2020. You can download this latest version from this link. 18.5 is an update to 18.4 with these new items and bug fixes.

One of the features added in this release is to select ‘Azure Data Studio‘ Notebook as a destination for Generate Scripts wizard.

Now you can send the objects definition of Table, View, Stored Procedure, Function along with sample call, sample data, and my comments all packaged in one Azure Data Studio Notebook. I can see scope for better communication between business partners, developers, and database engineers.

Click through for an example of the process.

Comments closed

Getting Row Counts After Execution

Published 2020-05-19 by Kevin Feasel

Grant Fritchey gives us a few ways to find row counts after a query has finished:

But, if you really want to get picky, batches and procedures frequently have multiple statements. This means that the row count you’re getting isn’t for a particular table. If you want to get that, then you need to capture the statement level stuff, sp_statement_completed or sql_statement_completed, depending on if you’re looking at procedures or batches.

However, this is one of those “be careful what you wish for” moments. Because, if you capture all statements in a system, you’re going to have a very large amount of data to deal with. This is where filtering is your friend.

Click through for Grant’s full answer.

Comments closed

Helping Users in Powershell Scripts

Published 2020-05-19 by Kevin Feasel

Greg Moore walks us through Powershell’s ability to display help info:

Shortly after writing my last article on Parameters, I had to update a script, and I wanted to make it easier for others to run. One of the features I wanted to add was the ability to show them what the script would do with the provided parameters without actually running the script, in other words, provide “help”.

There is a thoughtful way to do this, and Greg walks us through it, while also showing us a few false starts along the way.

Comments closed

Text Mining and Sentiment Analysis in R

Published 2020-05-18 by Kevin Feasel

Sanil Mhatre walks us through a sentiment analysis scenario in R:

Sentiments can be classified as positive, neutral or negative. They can also be represented on a numeric scale, to better express the degree of positive or negative strength of the sentiment contained in a body of text.

This example uses the Syuzhet package for generating sentiment scores, which has four sentiment dictionaries and offers a method for accessing the sentiment extraction tool developed in the NLP group at Stanford. The get_sentiment function accepts two arguments: a character vector (of sentences or words) and a method. The selected method determines which of the four available sentiment extraction methods will be used. The four methods are syuzhet (this is the default), bing, afinn and nrc. Each method uses a different scale and hence returns slightly different results. Please note the outcome of nrc method is more than just a numeric score, requires additional interpretations and is out of scope for this article. The descriptions of the get_sentiment function has been sourced from : https://cran.r-project.org/web/packages/syuzhet/vignettes/syuzhet-vignette.html?

Comments closed

Handling Cross-Database Transactions

Published 2020-05-18 by Kevin Feasel

Michael J. Swart explains how cross-database transactions work on a single instance:

The transaction is touching two different databases. So it makes sense that the two actions should be atomic and durable together using the one single transaction.

However, databases implement durability and atomicity using their own transaction log. Each transaction log takes care of its own database. So from another point of view, it makes sense that these are two separate transactions.

Which is it? Two transaction or one transaction?

Click through to read the article, and then check the comments for a cautionary tale around database mirroring and cross-database transactions.

Comments closed

Expression-Based Formatting in DAX

Published 2020-05-18 by Kevin Feasel

Matt Allington points out a new look to a slightly less new feature:

When I say “new”, they are actually not new – this feature has actually been around since the start of 2019. What is “new” is the discoverability of the feature. Prior to the April 2020 release, you had to first hover your mouse button over the section (Title text in the example above). After you hovered your mouse, you would see a vertical … menu (kind of like a vertical ellipsis), then when you hovered over the vertical ellipsis, you would then see the Fx button. Click the mysterious hidden button and only then could you discover the world of expression based formatting.

Click through to learn about expression-based formatting and where it might be useful.

Comments closed

Solving Last Page Contention Problems

Published 2020-05-18 by Kevin Feasel

Erik Darling ponders several techniques for solving the last page contention problem in SQL Server:

If one dare go looking, a list of Microsoft Approved™ solutions for dealing with last page contention exists.

I’m going to walk through my experiences with them in different scenarios, so you get a better understanding of which ones work best, and which ones can be tough to implement.

Read the whole thing.

Comments closed

Removing and Refilling All Tables in a Database

Published 2020-05-18 by Kevin Feasel

Phil Factor has a couple T-SQL scripts for us to remove and reload a test database:

Copying a database using a BCP dataset from a DOS script or from PowerShell is fairly quick and trivial, but what if the data is already in another copy of the database on the same instance? Imagine you have a database build that is a copy of an existing database on the same instance and you want to run a ‘destructive’ test on the data, and do it over and over again.

Deleting all the data in a database should, you’d have thought, be trivial. Truncation doesn’t work because it doesn’t like to truncate tables with foreign keys, even if they are disabled. DELETE is safer but you will need to then reset any identity columns. Deleting large tables in one chunk isn’t scalable, so you have to do it in smaller chunks. I like to disable triggers while I do this as well. The only disadvantage comes if you have triggers that are supposed to be disabled because the simple code just re-enables them all.

This is, I think, one of the biggest selling points for containers where the database is built into the container image. You spin up a container based off of an image, perform your destructive testing, and destroy the container afterward. The next time you need to run these tests, spin up a new container. And if you need to change the data, modify the container. This introduces new challenges like how SQL Server on Linux has limitations which don’t exist on Windows, but for supported functionality, it’s a nice solution.

Comments closed