Press "Enter" to skip to content

Category: SQL Server Management Studio

Generating Scripts in SSMS and ADS

Rob Farley walks us through automation options in SQL Server Management Studio and Azure Data Studio:

User interfaces are great, but I simply don’t want to have to remember to do everything the same way each time.

To that end, I want to wax lyrical for a moment about the Script button on most dialog boxes in SQL Server Management Studio (SSMS), and make a quick mention of what things (currently) look like in Azure Data Studio (ADS). (I say “currently” because ADS is still under very active development and could have changed even by the time I publish this.)

The Script button is one of the best features of Management Studio. The UI lets you know what can be done, and the Script button lets you know the process. It’s also a nice reminder that this functionality is not unique to the UI. Management Studio’s implementation of the Script button isn’t perfect, but it is good.

Leave a Comment

Recovering SSMS Scripts After a Crash

Jonathan Kehayias shows where those recovery scripts for SSMS are located:

It happens to the best of us and this post is more of a reminder for myself the next time it happens to me than anything else.  You are working in SQL Server Management Studio, you have a few tabs open (OK, it was 123 this time, but lets stay focused on the purpose of this blog post – You should see my Desktop and all the icons on top of other icons…), and then suddenly you get the dreaded SSMS has stopped responding/crashed window.  Sure you have been saving the important things along the way, but there are plenty of tabs that were just working queries for analysis that don’t really need to be saved but you still need them.  What do you do?

Even if you do use a tool like SSMS Tools Pack or DevArt’s SQLcomplete (which is what I use), it is still good to know where these scripts are just in case. I’ve also noticed that Azure Data Studio has been quite a bit better about maintaining scripts on close.

Comments closed

SQL Server Management Studio 18.6 Now GA

Drew Skwiers-Koballa announces SQL Server Management Studio version 18.6:

The 18.6 release is the second major release of SSMS in 2020 and packs several high impact changes, including a fix for crashes in database diagrams. Key fixes include:  

– Save to XEL file error fix. 
– Bacpac file import error fix. 
– Database diagrams crash fix. 
– Addressed sources of three common application hangs. 

Lots of bugfixes in here, but there are a few new things as well.

Comments closed

Row Counts and Arrow Widths, Continued

Hugo Kornelis finishes a series on row counts and arrow widths with a look at Compute Scalar operators:

Compute Scalar operator is probably the most common of all operators. I hardly ever see an execution plan that doesn’t have at least a few occurrences of this operator. The task of the Compute Scalar operator is a simple one: to use some of the data in its input and, based on that, produce new data that is then added as extra columns in its output.

Because of the simplicity of this task, the actual execution of that task is often done by one of the other operators in the execution plan, and the Compute Scalar operator itself doesn’t actually execute. A side effect is that it can’t track how many rows it processes, because it doesn’t process anything at all. The result is that, even in an execution plan with run-time statistics (aka “actual execution plan”), no run-time statistics will be reported by a Compute Scalar operator when all its computations are performed by other operators. (See also the note in this (retired) Books Online article).

But then Hugo head-fakes us and shows us the real conclusion:

I already described, in a previous post, how sometimes the optimizer can create an execution plan that uses a Filter operator to evaluate a specific predicate, but then a post-optimization rewrite finds a way to push that predicate down into another operator, as a Predicate property, and then removes the Filter operator. When this happens with a bitmap filter, the Estimated Number of Rows is not adjusted, which can be quite confusing.

But for the issue in this post, the root cause was the same, but the error surfaces completely differently.

This has been a fun series to read, showing how an extremely useful signal can nonetheless exhibit problems in many edge cases.

Comments closed

Preventing Copy and Paste of Text in the Messages Tab

Solomon Rutzky discovered something kind of funny about SQL Server Management Studio:

While researching some odd behavior in SQL Server Management Studio (SSMS) I ran across something even stranger. I was testing the effect of character 0 (sometimes referred to as “NUL”) in the “Messages” tab. For those who have not worked with the C programming language (or similar languages), character 0 (usually indicated by the \0 escape sequence) is the “null terminator” for strings. Meaning, this character marks the end of this string and anything after it should not be displayed. 

In Solomon’s usual fashion, you get a thorough testing of the scenario.

Comments closed

Reviewing SSMS Client Statistics

Reitse Eskens learns about SQL Server Management Studio’s client statistics:

In my case, i was looking for the amount of bytes received from server to determine the network speed. The number of rows is one thing, but i can’t easily tell if a row is 1 or 1000 kilobytes. By checking out the bytes received i can get some feel for the datasize. If there’s a huge amount of data coming towards me, that explains why i’ve got to wait for minutes. If there’s only a few kilobytes in the end, maybe something else is going wrong.

Reitse also takes some time to figure out how the client statistics tool works.

Comments closed

Rows Read Versus Rows Returned

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

Row Counts and Arrow Widths in SSMS

Hugo Kornelis takes us through one of the niceties SQL Server Management Studio (and subsequent tools like SentryOne Plan Explorer and Azure Data Studio) has for reading execution plans:

Because both the Actual Number of Rows and the Estimated Number of Rows are very valuable for many use cases, Microsoft has built their primary tool for viewing execution plans (SQL Server Management Studio) to highlight the value of at least one of these two properties, in a visual way, when you look at the graphical representation of an execution plan.

Look at the arrows that show which operator (is called by and) returns rows to which operator, and that therefore can be thought of as representing the data stream. In most execution plans, they are not all the same width. Management Studio uses the width of the arrow as a quick, simple visual representation of the value of Actual Number of Rows (in an execution plan plus run-time statistics, aka “execution plan plus” or “actual execution plan”), or of the value of the Estimated Number of Rows in an execution plan only (aka “estimated execution plan”).

This first post is the happy path post, where the information is correct and useful. Later posts promise to cover cases in which this does not hold.

Comments closed

Poor SQL Formatter’s SSMS Plugin

Dave Bland shows us the Poor SQL formatter for SQL Server Management Studio:

So what is PoorSQL Formatter?  Well, as you probably know, formatting is very important when writing T-SQL code.  While we try to format our code as we write it, sometimes the code we need to review was written by someone else and maybe doesn’t follow formatting standards. This is where PoorSQL Formatter will come in handy.  It will help us take the poorly written code and format it in a manner that is more in line with T-SQL formatting best practices.  The best part, it will do the formatting in seconds.

I’ve used that website many a time. It’s not as detailed as I want it to be, but if you just need a quick and dirty way to format a query so that it’s understandable, this does the trick.

Comments closed

Find and Replace Database Objects in SSMS

Daniel Hutmacher has a fun trick for us:

Here’s a quick tip that touches on one of the powerful SSMS tricks in my “Management Studio Level-Up” presentation. Say you have a potentially large number of database objects (procedures, functions, views, what have you), and you need to make a search-and-replace kind of change to all of those objects.

You could, of course, put the database in source control and use a proper IDE to replace everything, then check your code back into source control and commit it to the database. That’s obviously the grown-up solution. Thanks for reading this post.

But let’s say for the sake of argument that you haven’t put your database in version control. What’s the lazy option here?

Even if you do have everything in source control, the first part is still quite useful just in case there’s schema drift.

Comments closed