Press "Enter" to skip to content

Category: SQL Server Management Studio

Connection Retry Intervals

Published 2017-04-07 by Kevin Feasel

Arvind Shyamsundar explains a recent Management Studio change:

One such change SSMS got for free is the connection resiliency logic within the SqlConnection.Open() method. To improve the default experience for clients which connect to Azure SQL Database, the above method will (in the case of initial connection errors / timeouts) now retry 1 time after sleeping for 10 seconds. These numbers are configurable by properties called ConnectRetryCount (default value 1) and ConnectRetryInterval (default value 10 seconds.) The previous versions of the SqlConnection class would not automatically retry in cases of connection failure.

In general, because of these changes, transient errors (across slow networks or when working with Azure SQL Database) are less frequent. However, when you consider that a lot of SSMS users still use it with ‘regular’ SQL Server – either in a VM in the cloud or on-premises, there is a subtle but distinct impact of these changes which may affect administrators of ‘regular’ SQL Server databases.

Read on for details on why Management Studio hangs for 10-second periods, and see how to disable connection retry yourself.

Comments closed

Keeping Up To Date

Published 2017-04-07 by Kevin Feasel

Daniel Janik shows that query-level wait stats depend on the version of Management Studio:

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.

When reading the solution, it seems obvious, but this is a good reminder that there are a lot of moving parts here, and one of the early troubleshooting steps for “It works here, so why not over here?” types of issues is to make sure software is at the same version number.

Comments closed

Trick Co-Workers With This Extended Property

Published 2017-04-04 by Kevin Feasel

Kenneth Fisher shows how to use extended properties to hide a table from SQL Server Management Studio:

FYI I’ve tried this at the column and schema levels and it didn’t work.

Using this you can hide the object from SSMS object explorer without restricting its use in any way.

I’m curious if there are any other hidden uses of extended properties. I haven’t been able to find any documentation so if you’ve seen any please let me know!

I don’t think I’ve ever had cause to hide objects from Management Studio, but if you’re looking for next year’s April Fools prank, maybe?

Comments closed

SSMS Templates

Published 2017-03-10 by Kevin Feasel

Jana Sattainathan shows some of the value of SQL Server Management Studio templates, along with an important warning:

If you do start creating your own templates, you are responsible for backing them up. To locate the folder where they are stored

  1. Open DOS command prompt
  2. Run “echo %APPDATA%”
  3. Note the base path
  4. Navigate to %AppData%\Microsoft\Microsoft SQL Server\{SQL Server Version}\Tools\Shell\Templates\Sql\

(where %AppData% is the base path from

and {SQL Server Version} = 90 for SQL 2005, 100 for SQL 2008, 110 for SQL 2012, 120 for SQL 2014 and 130 for SQL 2016)

Templates are extremely useful for day-to-day development as well as giving a handy way of generating snippets of code, like estimating row count without having to remember to join to sys.indexes, sys.objects, and sys.dm_db_partition_stats.

Comments closed

What Does Activity Monitor Do?

Published 2017-02-28 by Kevin Feasel

Tibor Karaszi explains each window in the Management Studio Activity Monitor:

The idea here is to show where SQL Server is waiting, “wait stats”.

It uses the same procedure as the “Waiting Tasks” diagram uses, #am_generate_waitstats, to get the information. See the above section for “Waiting Tasks” to understand the time dimension for this. For simplicity, we can say that it shows only wait stats for the past 30-60 seconds. This is important. Imagine that you had loads of a certain wait stats, but none just for the last minute. This pane can now fool you that you didn’t have any waits of that kind, just because you didn’t for the past minute. Note, though, that the “Cumulative Wait Time” column is the sum of wait in the group since SQL Server was re-started or since we last cleared the wait state (DBCC SQLPERF(“sys.dm_os_wait_stats”,CLEAR)).

In an attempt to be friendly, it will group and summarize wait stats into various groups. That would be fine if there were some documentation about which individual wait type is in each group. Also, some wait types are ignored. One of the ignored wait types is CXPACKET, another is THREADPOOL.

Activity Monitor isn’t very good, but sometimes you can’t get the good tools installed on a server and need to check something quickly.  In those cases, it’s a handy thing to know.

Comments closed

Plan Comparison

Published 2017-02-16 by Kevin Feasel

Max Vernon looks at plan comparison in SQL Server Management Studio:

The table has 10,000,000 rows. I’ve create a non-clustered columnstore index on the table, which I’ll talk about in a future post. I’ve included it here because it provides a succinct difference in the two plans.

To compare the plans visually, side-by-side, you need to save the first plan by right-clicking on the plan window, clicking “Save Execution Plan As…”, and specifying a filename. Next, right-click on the plan window, and choose “Compare Showplan”:

I’ve only used this once or twice, but it is an interesting feature.

Comments closed

Managing Azure SQL Database Firewall Rules

Published 2017-02-15 by Kevin Feasel

Cedric Charlier shows how to manage Azure SQL Database firewall rules from within Management Studio:

When you create a new Azure database, you usually need to open the firewall to remotely administrate or query this database with SSMS. An option is to create rules from the Azure Portal. It’s surely a convenient way to do it when you create a database but I prefer to keep a minimum of tools and when the Azure portal is not open, I prefer to not have to open it just to define a few firewall rules.

Opening the firewall with SSMS is a kind of chicken and eggs problem: to connect to your database/server, you need to open the firewall. Hopefully, SSMS has a great suite of screens to call the underlying API of Azure Portal and open the firewall for the computer running SSMS.

Cedric shows off sp_delete_firewall_rule but there’s also a corresponding sp_set_firewall_rule.

Comments closed

Always Encrypted Data And SSMS

Published 2017-02-13 by Kevin Feasel

Justin Goodwin shows how to view Always Encrypted data from within Management Studio:

I am now able to view the encrypted data. Why is this?

Because I am connecting to the database from the database server, I have access to the Encryption Certificate that was generated. I can verify this by opening certmgr.msc and browsing to Personal -> Certificates:

Justin then goes on to show what happens when you don’t have access to the certificate and how to provide access to another machine.

Comments closed

Modifying SSMS Shortcuts

Published 2017-02-10 by Kevin Feasel

Slava Murygin shows how to update shortcuts in Management Studio:

A lot of people in the Internet complain about their version of SSMS “forgot” some hot-key combinations. The oldest complain I remember was about the most useful combination “Ctrl-R”.

The reason why SSMS “forgets” is within code sharing and reusability with other Microsoft development products.
If you have that problem, most probably I have (or had in the past) installed something else from Microsoft, such as Development Studio, Data Tools etc.

It can get annoying when another tool clobbers your expected shortcuts.

Comments closed

Closing SSMS Tabs

Published 2017-01-30 by Kevin Feasel

Andy Mallon shows how to close a tab in SSMS and change the shortcut value:

CTRL + F4

Huzzah! This will close your query tab! Easy as pie. Also, note that CTRL+F4 is a pretty universal shortcut that works to close tabs/windows in other applications, too–including your favorite web browser. If you can switch your muscle memory away from CTRL+W to CTRL+F4, you can use that shortcut pretty much everywhere.

Click through for more information on changing shortcuts.

Comments closed