Shortcut: Set SQLCMD mode for all new query windows in SSMS

SQLCMD mode changes how queries are executed in SQL Server Management Studio (SSMS). When using this mode, you can work with options that aren't normally part of SQL Server T-SQL scripts.

Some installation scripts also require SQLCMD mode and will fail if it's not enabled.

Here's an example of running a query against 3 servers within the same script:

First we open a new query window, then on the Query menu, we choose SQLCMD Mode.

Then I execute the following query:

Note the :CONNECT command is used to connect to another server.

Because everything else works pretty much the same, and you get a whole lot of additional options, you might choose to open all your new queries in SQLCMD mode. That's easy to do.

In Tools, Options, Query Execution, SQL Server, then General, there is a checkbox to enable this.



SDU Tools: List Mismatched Database Collations in SQL Server

Anyone who's worked with SQL Server for any length of time has probably run into collation issues. They will have seen something like this:

SQL Server Error: Cannot resolve the collation conflict

In many cases, it will be a difference between the collation in tempdb and their own database, they've created temporary tables, and tried to join them to tables in their database. And it goes bang !

While you can fix it in the query by specifying a COLLATE clause, it's a pain in the neck. If you use temp tables, you really should have this when working with them so your application isn't dependent upon the server's collation.

But one of the things you might need to check is which databases have collations that are different to the server's collation. In our free SDU Tools for developers and DBAs,  we added a procedure ListMismatchedDatabaseCollations to do just that.

You can see how to execute it in the main image above. The procedure takes these parameters:

@ExcludeSystemDatabases bit – Do you want to omit any system databses from this list?
@ExcludeReportingServicesDatabases bit – Do you want to omit any SQL Server Reporting Services databases from this list?

One row is returned for each database.

The columns returned are DatabaseName, DatabaseCollation, and ServerCollation.

You can see it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

SQL: When Performance Tuning SQL Server, Fix the Problem, not the Symptom

I spend a lot of time working with teams of DBAs and developers who need to fix performance problems. One of the real issues that I see time and again though, is people fixing the symptom, not the problem.

Many DBAs spend their time tracing query activity, working out which queries the system is spending a lot of time on, and then working out how to make those important queries run faster.

Automated tuning systems like those being put into SQL Server currently achieve much the same outcome.

But I'd always contend that if that's all you can consider, you are often working on the wrong problem.

I was at a site some years back, where I found a single query that was loading a list of countries, but was being executed 12,000 times per minute and the system seemed constantly busy executing the query.

Some DBAs that I have worked with, would have just ignored the query. Each individual execution of the query took very few resources, and it wouldn't have made it through their filter of queries that need looking at, because it ran fast.

Other (better) DBAs might identify it as an important query because they aggregated the impacts of the queries. Even better if they aggregated the normalized version of the query. (See our free course on doing this if you aren't familiar with it). They would then spend their time working out if they could improve the performance of the query by indexing, looking at wait stats, reducing blocking, and other techniques.

This is all good stuff, but they're all still missing the real problem which is why is the application asking the same question 12,000 times a minute?

That's the actual problem that needs solving.

Awesome image by Annie Spratt
Awesome image by Annie Spratt

To really solve performance problems, you need to be prepared to step back, look at the bigger picture, and be prepared to go back into the application code with the developers. It's worth noting that many 3rd party application vendors won't be keen on you doing that, but it's the only way to actually solve the problems.

Now, in this case, it's pretty clear the list of countries isn't changing. (It's a few years since we got a new one in South Sudan).

Awesome image by Kyle Glenn
Awesome image by Kyle Glenn

So why on earth (pun intended) is the application constantly asking for this? Surely it should be caching that data.

And of course, when I checked with the development team, they had been caching the data. What went wrong though, is they had a typo in the caching code. Every time they checked the cache, they thought the values weren't there, so they reloaded them.

That was the real problem that needed to be solved. And no amount of fiddling with indexes, wait stats, locks, etc. was going to have a noticeable impact.


Shortcut: Set query shortcuts for favorite stored procedures in SSMS

In an earlier post, I mentioned how useful the F1 key is. On its own, it provides syntax help, but when you highlight an object and hit Alt-F1, you get to see metadata about the object.

Under the covers, this just runs the sp_help stored procedure. Alt-F1 has been mapped to that.

You can see where this is configured, change it if required, and/or configure other procedures as well.

In Tools, Options, Environment, Keyboard, then Query Shortcuts, you can see this:

Here we can see how the Alt-F1 command is configured. By default, only three shortcuts are configured. It's easy to configure another one for your own use.

SDU Tools: List Indexes in a SQL Server Database

When I'm working with a SQL Server database, I often need a simple list of all the indexes that are already in place. I want it in a concise form. In our free SDU Tools for developers and DBAs,  we added a procedure ListIndexes to do just that, and to provide their details in a form that's easy to consume programmatically if you need that.

You can see how to execute it in the main image above. The procedure takes these parameters:

@DatabaseName sysname – This is the database to process
@SchemasToList nvarchar(max) – a comma-delimited list of schemas to include (ie: 'Sales,Purchasing') or the word 'ALL'
@TablesToList nvarchar(max)- a comma-delimited list of tables to include (ie: 'Customers,Orders') or the word 'ALL'

One row is returned for each index.

The columns returned are SchemaName, TableName, IndexName, KeyColumnList, and IncludedColumnList as shown below:

You can see it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

SQL: Shrinking Files and Auto Shrink in SQL Server – Just Say No

I first started using SQL Server back in 1992 with version 4.2 I was running a software house and was hoping to use SQL Server as the back end of our updated application. 4.2 wasn't up to it. Versions 6 and 6.5 were closer but I still thought they needed far too much administration for me to be comfortable deploying them to most customer sites. Version 7 though, changed the game. Rather than having to manage devices (which was painful), we now had just standard operating system files, and even had options for automatically growing and shrinking the files.

Growing automatically was a big deal. Running out of disk space within SQL Server 6x devices was really painful and commonplace. Automatically shrinking was an interesting option that we tried on some sites.

In building version 7, Microsoft made a conscious effort to work out if there was a way to automatically set anything that a DBA normally needed to set themselves. Version 7 was the first version of SQL Server that we really felt comfortable deploying to customers who had no onsite IT people. We deployed it, and the sites just went quiet.

Awesome image by Kristina Flour

Awesome image by Kristina FlourFor a software house (ISV), quiet is good.

We tended not to use Auto Shrink. Manual shrink worked but we barely ever used it anyway and that didn't change through later versions.

However, Paul Randal noted that in 2005, a bunch of things made shrinking much, much slower. We started hearing more and more horror stories about shrinking.

To give you an example, I was at a client site a year back, and they'd decided to shrink a 2TB database back to 1.3TB. They started the shrink and within an hour, it said it was 64% complete. However, a week later (yes a whole week), it was 62.8% complete. They had contacted us because they thought it was never going to complete. Worse, they were now seeing deadlocks that didn't normally happen. The deadlocks stopped if they stopped the shrink operation.

This is a horrible outcome for them. And worse, even if it had ever completed, it would have so messed up the internal fragmentation within the database that they would have needed to rebuild all the indexes anyway.

What we did instead was to create a new database and migrate the data over. It took 2 hours and was super-clean. We did it in the right order to avoid the need for index rebuilds later.

Shrinking a SQL Server database isn't a good option right now. Auto Shrink is almost always an even worse choice.

Migrate the data to another database, or rebuild the data onto a new filegroup if that's an option.


Shortcut: Multi-server queries in SQL Server Management Studio

In an earlier post, I mentioned that you can create a registered list of servers, either in Local Server Groups or stored in a Central Management Server.

What I didn't really talk about though, is what you can do with these groups of servers, rather than just executing queries on an individual server.

I've created three local server groups, for my development, UAT, and production servers.

The Development Servers group has three database servers in it. If I right-click the group, rather than any individual server, we get these options:

I'll talk about policies another day but notice that you can import or check (evaluate) policies across a whole group of servers.

But the option that interests me today is the New Query option. When you click this, it opens a query window for the group of servers.

The window color at the bottom has changed from the default, and in the bottom right, we can see that the window is connected to the local server group:

If I type the query SELECT @@VERSION; and click Execute, I see this:

Even though this looks like a single result set, this is just a SQL Server Management Studio (SSMS) trick. Under the covers, it has run the query individually against each server. It has just presented the results to us as though they are a single set of results.

We can configure several things about how this happens. In Tools, Options, Query Results, SQL Server, Multi-server Results, we have these options:

We could add a column that shows our login name for each server to the results. We could remove the server name if required, although that doesn't seem very useful. And we could choose to not have the results merged. If we do that, SSMS returns a separate result set for each server.

You can also change the color of the bar below, in this location:

Multi-server queries were an interesting addition to SSMS. They are useful for a relatively small number of servers. As the number of servers increases, they would become more fragile, and you might want to consider using a 3rd party tool that works out which servers have or haven't had the query run, retry options, etc.

SDU Tools: List Incoming Foreign Keys in a SQL Server Database

One of the challenges when you have foreign keys in place, is that there are restrictions on what you can do to tables that are the targets of the foreign keys. What's often needed though is a list of all references to a particular table or set of tables.

In our free SDU Tools for developers and DBAs,  we added a function ListIncomingForeignKeys to do just that, and to provide their details in a form that's easy to consume programmatically if you need that.

You can see how to execute it in the main image above. The procedure takes these parameters:

@DatabaseName sysname – This is the database to process
@ReferencedSchemasToList nvarchar(max) – a comma-delimited list of schemas to include (ie: 'Sales,Purchasing') or the word 'ALL'
@ReferencedTablesToList nvarchar(max)- a comma-delimited list of tables to include (ie: 'Customers,Orders') or the word 'ALL'

One row is returned for each foreign key.

The columns returned are SourceSchemaName, SourceTableName, ForeignKeyName, SourceColumnList, ReferencedSchemaName, ReferencedTableName, ReferencedColumnList, IsNotTrusted, IsDisabled.

You can see it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

SQL: What is REFERENCES permission and why does it matter?

I was at a client site the other day and they were puzzled by SQL Server object permissions. In particular, they couldn't understand what the point of the REFERENCES permission is. It seemed pointless to them. It's not. Let me explain why.

Keep in mind that if all the objects in the database have the same owner, then this isn't an issue. But not all databases are like that. Many have different teams that control different parts of a large database.

Imagine that the Accounting team owns a Customers table. The Marketing team wants to create a LoyaltyPoints table and wants to add a foreign key to the Customers table. What permission do they need?

REFERENCES is the permission required.

But why do they need permission at all? It's because after they create the foreign key, the Accounting team is now restricted in what they can do with their Customers table. For example, they can no longer truncate the table. If they want to drop and recreate the table, that won't work. Before they can delete a row in the Customers table, it's now a requirement that no related rows exist in the LoyaltyPoints table.

Because the owner of the table will be restricted by the foreign key, they need to grant at least REFERENCES permission to anyone who wants to create a foreign key to their tables.

Shortcut: Configuring registered servers in SQL Server Management Studio

When working with SQL Server systems, it can be hard to remember the names of all the servers, to remember connection details for the ones that need SQL logins (instead of Windows authentication), and to remember other details of those servers, such as which environments they are part of (eg: production, UAT, test)

SQL Server Management Studio (SSMS) has a facility to help you to do this. It allows you to register server details in a single place.

By default, the window isn't shown, but from the View menu, you can choose Registered Servers.

When the window opens, you can see this:

Note the toolbar near the top of the window. It is showing that we're configuring database servers but the other icons let you know that you can also work with Analysis Services, Integration Services, and Reporting Services servers.

The first decision that you need to take is to decide where the details will be stored. Local Server Groups are stored on your local system ie: the system that is running SSMS. If you move to a different system to work, you won't have those connection details. Alternately, a Central Management Server can be configured. This is a server that agrees to hold connection details. While this seems a great idea (because the details would be held in a single place), one of the down-sides of this arrangement is that only Windows authentication can then be used. Local Server Groups can also work with SQL logins.

Let's create a server group as an example. If I right-click Local Server Groups, here are the available options:

Note that there is an option to Import (and Export) these details. This at least allows you to move details between systems.

Let's create a new Server Group:

It just needs a name and an optional description, then OK. When it's created, right-click it, and choose New Server Registration:

I've connected to the server SDUPROD and I've given the registered server the same name. Note that you don't need to do that. I could have called it PayrollServer or some other more meaningful name. You'll also notice that there are tabs for configuring other connection properties.

I've then created a second server called HRServer and under the covers, I've pointed it to another server.

Now I have all my servers in groups, in an appropriate location. I can right-click them to open new queries to them, and to do much more.