Yearly Archives: 2014

Read more about the article Tables larger than tempdb
Microsoft SQL Server

Tables larger than tempdb

If your database contains very large tables, querying or managing those tables may fill tempdb. When tempdb is full, an error is raised and the transaction is rolled back. If this will happen or not depends on how large your tempdb can get. Even if the size of tempdb isn’t limited by turning off autogrowth or by setting a max size, the disk space available is the upper limit for how big tempdb can get. Check out this blog post to see how to calculate the maximum possible size of tempdb.

Large tables can be a latent problem, since there may be no problems at all until someone, for instance, tries to sort the table in a query. SQL Server uses tempdb (as the name implies) as a temporary workspace for data, and if SQL Server can’t handle all the data in memory tempdb is used.

To avoid this problem you need to know if there are very large tables that won’t fit in tempdb when at its maximum size. Here is a script to look for tables larger than tempdb:

(more…)

Continue ReadingTables larger than tempdb
Read more about the article Tempdb max size
Microsoft SQL Server

Tempdb max size

There is always a limit on how large tempdb can get. Even if there are no size limitations on the data files in tempdb, the limit is still the free space on disk drives. Here I’ll show how to calculate the maximum size of tempdb (data only, not log).

(more…)

Continue ReadingTempdb max size
Read more about the article Changing the database owner

Changing the database owner

When a database is created, the logged in user is set as the database owner as default. Often, this user account is a physical person and he or she may get more permissions in the database than intended. Normally, a system account should be used as database owner. For instance sa.

Note that when you change the database owner the old account may get less permissions than before, not being the owner (who can do anything) anymore. So check how and where the old account is used before changing.

(more…)

Continue ReadingChanging the database owner
Read more about the article Multi Server Query
Microsoft SQL Server

Multi Server Query

In SQL Server Management Studio, it is possible to execute a multi server query against several servers at the same time. The description from TechNet says most of it:

This topic describes how to query multiple servers at the same time in SQL Server 2012, by creating a local server group, or a Central Management Server and one or more server groups, and one or more registered servers within the groups, and then querying the complete group. The results that are returned by the query can be combined into a single results pane, or can be returned in separate results panes. The results set can include additional columns for the server name and the login that is used by the query on each server. Central Management Servers and subordinate servers can be registered by using only Windows Authentication. Servers in local server groups can be registered by using Windows Authentication or SQL Server Authentication.

Here I’ll show how to create a multi server query from a group under registered servers.

(more…)

Continue ReadingMulti Server Query
Read more about the article Index selectivity

Index selectivity

When SQL Server evaluates if an index should be used to retrieve data in a query, selectivity is one of the most important factors. Of course, given that the index column matches the filter or join columns.

If a column only has one or very few unique values, index selectivity is low. True/false columns and gender is common examples.

If there are only unique values in a column, index selectivity is high. Primary keys meets these requirements as they are always unique.

SQL Server favors indexes that are built on columns with high selectivity, since this may return fewer rows and therefore at a lower cost.

If the index has multiple columns, it is preferable (if possible) that the columns are in the order of their selectivity (most selective first).

So how is selectivity computed?

(more…)

Continue ReadingIndex selectivity
Read more about the article SQL Server Join Methods
Nested Loops Operator

SQL Server Join Methods

When you submit a query to SQL Server – before the query is executed – SQL Server tries to optimize the way data is retrieved from the tables and indexes to create an execution plan that is as effective as possible. One of the decisions the optimizer in SQL Server has to make is which of the join methods (if any) to use.

There are three join methods: Nested Loops Join, Hash Join and Merge Join.

They all have their relative strengths and weaknesses, which will be covered in this blog post.

(more…)

Continue ReadingSQL Server Join Methods

Add and edit Windows environment variables

Windows environment variables are a great way to store server specific information that may be accessed from different systems on the server. Connection strings, folder paths and ip addresses are common examples of what may be suitable to store as environment variables on the server.

(more…)

Continue ReadingAdd and edit Windows environment variables