SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in


Matt Bowler is a DBA at Trade Me, and teaches database design and administration courses at the local institute of technology. A recent but avid discoverer of SQL Server, Matt is a regular contributor to forums at MSDN, Experts Exchange and SSC and he blogs at mattsql.wordpress.com.

In Memory OLTP with SQL Server 2014

SQL Server 2014 CTP is available for download and evaluation and it contains a couple of exciting performance enhancements. One of these is OLTP databases optimised to be memory resident.

So I wanted to have a play – and I immediately came across a couple of gotchas:

Gotcha 1:… Read more

1 comments, 2,340 reads

Posted in Ctrl-alt-geek on 8 July 2013

Wildcards and Indexes

I was asked a good question the other day, one that I wasn’t sure of the answer. The question was “does SQL Server use an index if you have a wildcard in the search predicate?”

My initial thought was it would if the wildcard was not the first character but… Read more

1 comments, 1,228 reads

Posted in Ctrl-alt-geek on 22 May 2013

Quick tip: Using sp_server_diagnostics

In SQL Server 2012 a new procedure sp_server_diagnostics returns a payload of information about server health and performance. The sproc is leveraged by the Windows Failover Cluster service and is designed to run in repeat mode. However the sproc can be run in non-repeat mode and loaded into a temp… Read more

3 comments, 3,663 reads

Posted in Ctrl-alt-geek on 3 April 2013

SQL Server 2008 Statistics: What does a DBA need to know?

I’ve been looking into the behaviour of statistics in SQL Server 2008 – so I’d like to present an overview from a DBA perspective.

What are statistics?

Statistics are a database scoped object that are used by the query optimiser in creating query plans. They describe the distribution of values… Read more

0 comments, 6,593 reads

Posted in Ctrl-alt-geek on 14 February 2013

Restore database permissions


I came across a couple of subtle gotchas around the permissions required to restore a database in SQL Server 2008+ recently. I’m interested in situations where a DBA needs to allow certain users the ability to restore a given database, for example refreshing UAT, while maintaining minimum permission levels… Read more

10 comments, 3,398 reads

Posted in Ctrl-alt-geek on 10 January 2013

Migrating SQL Server Databases that use Database Master Keys

There’s a lot of things to consider when migrating databases between SQL Server instances. I want to provide some tips for dealing with Database Master Keys, and in particular the case when those keys are encrypted by the Service Master Key.

Before getting into the details, let’s take a quick… Read more

10 comments, 3,120 reads

Posted in Ctrl-alt-geek on 13 November 2012

Don’t believe everything you read: Truncate table is not logged

Actually I’m not sure if anyone still believes this anymore. If you read the BOL description carefully it states that truncate de-allocates the pages rather than deleting the rows – meaning only the de-allocations are logged – resulting in far fewer log records. Incidentally this is also why the truncate… Read more

1 comments, 4,207 reads

Posted in Ctrl-alt-geek on 31 October 2012

Quick tip: Count table rows in a database, and locate those rows in the data files.

Here are a couple of quick database and table level queries that I have found useful lately.

How many rows in each table of a database?

The first temptation with this one was to use something like sp_MSforeachtable with a count(*), until I recalled that the catalog view sys.indexes has… Read more

0 comments, 1,442 reads

Posted in Ctrl-alt-geek on 30 October 2012

Querying database usage

A question that a DBA will often be asked is what databases are actively being used on my SQL Server? We get asked it all the time, usually as part of a consolidation or rationalisation project.

My solution to this is to query the sys.dm_db_index_usage_stats DMV, which maintains counters of… Read more

0 comments, 2,719 reads

Posted in Ctrl-alt-geek on 17 October 2012

Quick tip: Querying database metadata with DBCC DBINFO

Page 9 in file 1 (of the primary filegroup) in a SQL Server database is the header page and contains a wealth of metadata about the database. We can have a look at this with DBCC PAGE or DBCC DBINFO.

In order to redirect the output of the DBCC command… Read more

2 comments, 1,206 reads

Posted in Ctrl-alt-geek on 11 September 2012

Quick tip: Using DBCC SQLPERF(‘LogSpace’)

A lot of a DBA’s time is spent managing and investigating transaction log files.

A commonly used tool is the command:


which returns a list of all databases, the size of their transaction log file, and the percentage of that space that is used for active transactions. One… Read more

2 comments, 2,619 reads

Posted in Ctrl-alt-geek on 10 September 2012

Deploying and processing SSAS cubes

In SSAS we build what’s known as a UDM, or Unified Dimensional Model, over the top of a database schema, usually a data warehouse. The UDM consists of several objects designed to aid analysis of the data. The UDM is built in BIDS (Business Intelligence Development Studio) and in terms… Read more

3 comments, 5,670 reads

Posted in Ctrl-alt-geek on 1 August 2012

Restores in SQL Server Analysis Services

“You’re only as good as your last restore”

I’ve no idea who originally said that – but it probably qualifies as DBA folklore. I blogged recently about backups in SSAS so to close the loop here are the basics of restoring SSAS databases.

In the GUI:

Connect to the SSAS… Read more

1 comments, 954 reads

Posted in Ctrl-alt-geek on 1 August 2012

Backups in SQL Server Analysis Services

Analysis Services databases should be backed up at regular intervals like any other database. Here are the basics.

Using the GUI: Connect to the SSAS service in SQL Server management studio. Right click on the database and select backup.

The Backup dialog presents a number of options.

Allow file overwrite… Read more

1 comments, 2,407 reads

Posted in Ctrl-alt-geek on 19 July 2012

Quick tip: configure SQL Server authentication mode using T-SQL

SQL Server stores its authentication mode in the registry. The registry can be read and updated using the T-SQL commands xp_regread and xp_regwrite.

Step 1: Construct our registry key path, run the following query and record the result:

DECLARE @InstanceName NVARCHAR(1000),

EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\',
   N'MSSQLSERVER', --for…

Read more

0 comments, 1,204 reads

Posted in Ctrl-alt-geek on 11 July 2012

Don’t believe everything you read: Reconfigure flushes the plan cache

I’ve been doing a fair bit of research and investigation into the behavior of the plan cache recently. So I was concerned when I read somewhere that the reconfigure statement causes the SQL Server plan cache to be flushed. If this is true then it is a pretty crucial piece… Read more

7 comments, 3,502 reads

Posted in Ctrl-alt-geek on 25 June 2012

Corrupt Model database

An interesting case arose at work the other day. SAN issues had caused SQL Server to shutdown and it was unable to be restarted because the data files were unavailable.

Once the SAN was back online the instance still could not be restarted. The error logs were reporting that tempdb… Read more

1 comments, 694 reads

Posted in Ctrl-alt-geek on 20 June 2012

Service Broker: What does a DBA need to know?

Service Broker is SQL Server’s internal messaging system. It has been designed as an asynchronous, reliable and transactional messaging system for inter or intra database messaging, or even inter SQL instance messaging.

It is a powerful tool for decoupling extended transactions across time and across databases. It is designed to… Read more

7 comments, 8,858 reads

Posted in Ctrl-alt-geek on 7 June 2012

Plan cache in SQL Server 2008R2

I was curious to look at the effect of database options, on the SQL server plan cache.

I have restored the Adventure works sample database onto my SQL Server 2008 R2 RTM instance four times as AW1, AW2, AW3 and AW4.

I have used, and slightly modified, a query from… Read more

1 comments, 1,098 reads

Posted in Ctrl-alt-geek on 23 May 2012

Quick tip: sp_configure and sys.configurations

In order to query the configuration settings of a server you can run this sproc:

exec sp_configure

On a default install of sql server this will return 16 rows. In order to see all rows you need to run this:

exec sp_configure 'show advanced options',1

Now when you run… Read more

4 comments, 1,038 reads

Posted in Ctrl-alt-geek on 16 May 2012

Newer posts

Older posts