I’d be struggling to remember a week where I hadn’t run a query against the dmv sys.dm_db_index_usage_stats to find unused or under used indexes (or even heaps). It would have to be in the top shelf of many DBA’s toolboxes. So I was alarmed when I came across this post Read more
Problem: You require the ability to perform a repeatable standardised installation of multiple instances of SQL Server or the ability to create as-built documentation of an installation.
Solution: I’ve made a few attempts at documenting a SQL Server install, and all the various options that were chosen at each step,… Read more
The Problem: A legacy table contains amounts and a char column indicating whether the amount is a credit or a debit. We want an aggregate (over a given range) of the amounts. We need to add the credits and subtract the debits.
The Solution(s): Two possible approaches to the problem… Read more
It’s a well known fact that any sitcom that runs for a certain length of time will release a clip show. Similarly any blog about SQL Server will end up doing a top tips for using SQL Server Management Studio. Here then, in no particular order, are mine:
1.… Read more
A while ago I blogged my about my approach to service packing a SQL Server 2008R2 active active cluster. Time to apply SP1 to SQL 2012 on top of a Windows 2012 failover cluster. The basic approach is similar but there are a couple of subtle differences.
The key… Read more
I struck this error in one of my SSIS packages, I found lots of tips and suggestions online for the error but none of the solutions worked for me. So I want to add my solution to the mix so that it might give the next person searching an extra… Read more
Standard best practise is to have auto create and auto update statistics set for SQL Server databases. But there is no corresponding setting to remove statistics that are no longer necessary. Unless explicitly managed by the DBA these statistics could be causing excess resource usage.
Create a… Read more
I came across an issue today while trying to create a WMI alert for a mirroring state change. The error was something along the lines of WMI Access denied. Error: 0×80041003.
My first port of call was to verify the WMI query by using the WBEMTest utility.
This is a… Read more
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
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
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
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
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
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
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
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
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.
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
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
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