Entries by alexander

Back in the saddle – index time

Well, I’m back in the saddle. Four weeks of vacation, of which three resembled some kind of monsoon. The last was great, and ofcourse the weather became great as soon as I returned to work. I decided to get me a new script to show me unused indexes, and started looking at Stack Overflow to […]

Vacation! And a few gotchas…

The blog has been slow lately due to the impending vacation. I’ll toss in a few things I stumbled on the last week though: When dealing with a cluster, chances are that some of the disks just won’t be returned to the fold when deleted from available storage. For some reason, the cluster sometimes keeps […]

Basic settings for SQL Server 2012/2014, part 4

Part 4 – scripts for the previous parts Below are scripts for most of the SQL Server specific settings I discussed in the previous three parts. Please note that not quite everything is included (trace flags, instant file instantiation, etc.).   — Set memory amount sp_configure ‘show advanced options’, 1; GO RECONFIGURE; GO sp_configure ‘max server memory’, 40000; GO RECONFIGURE; GO — Turn on optimize for adhoc workloads EXECUTE sp_configure ‘show advanced options’, 1; GO RECONFIGURE; GO EXECUTE sp_configure ‘optimize for ad hoc workloads’, 1; GO RECONFIGURE — Turn on DAC EXECUTE sp_configure ‘remote admin connections’, 1; GO RECONFIGURE; GO — Adjusting TEMPDB ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N’tempdev’, SIZE = 100MB , FILEGROWTH = 50MB); GO — Script to create TEMPDB files like the one existing USE [master] go […]

Migrating databases, part 2

Having gone through the truly offline ways of transferring data, let’s take a look at log shipping and database mirroring. These are fairly old techniques as both of them came into being back in SQL Server 2005. The concept is very simple – every once in a while data from the transaction logs are sent […]

Migrating databases, part 1

Migrating databases Most of us poor souls that for some reason enjoy this work called “database administration” get to meet “Mr. Migration” from time to time. I’ve lost count of the number of customers in need of moving one or more (most often A LOT) of databases from one server to another. The reasons for […]

Better Index Usage DMV

Slightly altered Aaron Bertrand’s “Better Index Usage DMV” to script out CREATE INDEX-statements.   SELECT d.[object_id], s = OBJECT_SCHEMA_NAME(d.[object_id]), o = OBJECT_NAME(d.[object_id]), d.equality_columns, d.inequality_columns, d.included_columns, s.unique_compiles, s.user_seeks, s.last_user_seek, s.user_scans, s.last_user_scan, ‘CREATE INDEX [IX_’ + OBJECT_NAME(d.OBJECT_ID,d.database_id) + ‘_’ + REPLACE(REPLACE(REPLACE(ISNULL(d.equality_columns,”),’, ‘,’_’),'[‘,”),’]’,”) + CASE WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL THEN ‘_’ ELSE ” […]

Basic settings for SQL Server 2012/2014, part 3

Part 3: simplifying the life of the DBA When I’ve got the environment set up quite the way I like it, I usually set up a repository where I keep all my scripts. I designate one server as the “master” for my purposes, and set up a database called DBADB. In here I put all my […]

Double Whammy

So there I was, gearing up for a trip to a customer 90 minutes away and my audiobook had just finished. Having listened to Matan Yungman at SQL Pass in Copenhagen, I vaguely remembered him saying something about SQL Server Radio, so I decied to check it out. Turns out they’ve been doing it for […]

Basic settings for SQL Server 2012/2014, part 2

Updated: 2016-11-07 Part 2: settings inside SQL Server When the server is all good and installed, it’s time to go through and set up a bunch of server settings. First of all – dedicated admin connections. This is used as a “get out of jail for free”-card for a DBA if all the schedulers are […]