Sql-Server

When you get to a large number of query windows or other documents open as tabs in SQL Server Management Studio (SSMS), it can start to be difficult to keep track of them, and to find them when needed.

It’s not too bad when you can immediately find the tab that you want in the drop-down list:

But if you have more tabs than are shown in this drop-down list or if, like me, you often end up with many of them without names (as they are temporary), it can get very hard to find the few that you are mainly referring to.

2019-01-10

In our free SDU Tools for developers and DBAs, there’s another useful scripting tool. It scripts out the server permissions that have been granted to logins. It’s called ScriptServerPermissions.

One key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.

But if you just want to create a script, that works too.

By default, it lists permissions for all logins, but you can also provide a list of the logins that you’re interested in, as a comma-delimited list, to the @LoginsToScript parameter.

2019-01-09

Clustered indexes vs heaps

OK so you know that a table can either be a heap or it can have a clustered index.  The first question of course, is which should I use?

If you have any doubt at all, put a clustered index on the table. Most SQL server tables will end up performing better that way. There are a few scenarios like log files where heaps will be preferable but if you’re not sure, start with a clustered index.

2019-01-07

SQL Server execution plans can become quite large. That makes them hard to navigate because you are endlessly scrolling around the results pane in SQL Server Management Studio (SSMS).

The pane does have some zoom features. Note that if I right-click in the whitespace, I get these options:

So I can zoom in and out, set a custom zoom level, or zoom until the entire plan fits. Generally though, that would make the plan too small to read, as soon as you have a complicated plan.

2019-01-03

In our free SDU Tools for developers and DBAs, there’s a tool that makes it easy to create a script for creating SQL Server database users.  No surprise, it’s called ScriptDatabaseUsers.

One key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.

But if you just want to create a script, that works too.

2019-01-02

Hi Folks,

Just a short note today to wish everyone a happy new year. I know that new year is a time when many people make resolutions, with the intention of changing something about themselves during the upcoming year. It makes it a time full of hope for fresh starts.

I don’t tend to make too many resolutions as I think that needs to happen on an ongoing basis rather than once per year, but I understand why people do.

2019-01-01

One data type that’s often been quite controversial is the bit data type. So a bit is where we have an individual value, which is either a zero or one. They’re the only values allowed unless it’s NULL.

Always keep in mind when you’re working with bits in SQL server that they’re aren’t just 2 potential states, there are three. It can be zero, or one, or it can be NULL. That is, it can have no value at all.

2018-12-31

In an earlier post, I described how query plans could be saved as .sqlplan file, shared, and loaded again in SQL Server Management Studio (SSMS). It’s also possible to extract them out of SQL Server Profiler or Extended Events Profiler.

This is useful, but the same applies to deadlock graphs. SQL Server 2005 added Deadlock graph as a type of event in SQL Server Profiler. (It’s also part of Extended Events Profiler).

2018-12-27

I’m often wondering about how large different objects in my databases are. There are reports to provide that info, but I often want it in a programmatic form.  One of our free SDU Tools for developers and DBAs does just that. It’s ListUserTableAndIndexSizes.

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) -> ‘ALL’ or comma-delimited list of schemas to list @TablesToList nvarchar(max) -> ‘ALL’ or comma-delimited list of tables to list @ExcludeEmptyIndexes bit -> 0 for list all, 1 for don’t list empty objects @ExcludeTableStructure bit -> 0 for list all, 1 for don’t list base table (clustered index or heap) @IsOutputOrderedBySize bit -> 0 for alphabetical, 1 for size descending

2018-12-26

A pet dislike of mine is application developers creating connection strings, but without Application Names. That’s painful because when  you view the processes in SQL Server Activity Monitor, or look at commands in traces from SQL Server Profiler or Extended Events Profiler, you see this:

If your connection just says .Net SqlClient Data Provider, no-one has any idea what application that is. If you are using another connection library, it might say a different name but the concept is the same. You are making management of the server much harder than it needs to be.

2018-12-24