Entries by alexander

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 […]


Very useful to transfer logins with their passwords intact. USE master GO IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar (514) OUTPUT AS DECLARE @charvalue varchar (514) DECLARE @i int DECLARE @length int DECLARE @hexstring char(16) SELECT @charvalue = ‘0x’ SELECT @i = 1 SELECT @length […]

Table size information

SELECT t.NAME AS TableName, i.name as indexName, sum(p.rows) as RowCounts, sum(a.total_pages) as TotalPages, sum(a.used_pages) as UsedPages, sum(a.data_pages) as DataPages, (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, (sum(a.data_pages) * 8) / 1024 as DataSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p […]

Erland Sommarskogs wait_stats_monitor

CREATE PROCEDURE wait_stats_monitor AS SET NOCOUNT ON DECLARE @latest datetime, @now    datetime SELECT @now = getdate() IF object_id(‘waitstats’) IS NULL BEGIN CREATE TABLE waitstats ( sample_time       datetime     NOT NULL, wait_type         nvarchar(60) NOT NULL, sample_length_sec int          NULL, delta_tasks_count bigint       NULL, delta_wait_time   bigint    […]

Get statements from waiter list

if exists (select * from sys.objects where object_id = object_id(N'[dbo].[get_statements_from_waiter_list]’) and OBJECTPROPERTY(object_id, N’IsProcedure’) = 1) drop procedure [dbo].[get_statements_from_waiter_list] go create proc get_statements_from_waiter_list (@wait_type nvarchar(60)=NULL) as select r.wait_type ,r.wait_time ,SUBSTRING(qt.text,r.statement_start_offset/2, (case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else r.statement_end_offset end -r.statement_start_offset)/2) as query_text ,qt.dbid, dbname=db_name(qt.dbid) ,qt.objectid ,r.sql_handle ,(SELECT query_plan FROM sys.dm_exec_query_plan (r.plan_handle)) as […]

Dude, where’s my disk?

Today I took a look at a clients 10g Oracle RAC environment where one node apparently had started misbehaving quite some time ago. The scenario was that both nodes were up, cluster ready services was up, nodeapps were up, ASM was up but the instance was down. Starting from the top with the database alert […]

Basic settings for SQL Server 2012/2014, part 1

Whenever I set up an SQL Server I usually do the same things. I’ve collected a list of best practice settings over the years (and most often put them in a script, as most of my friends are scripts). Throughout a few blog posts I’ll go over the settings I do use and discuss a […]

Failover on Oracle Standard Edition?

We have all heard (and gnashed our teeth over) that in order to get a proper automatic failover environment for Oracle (i.e Data Guard) we need Enterprise Edition. Oracle Enterprise Edition (or as Brent Ozar so succinctly put it: Expensive Edition) is hideously expensive, especially if you’re only ever going to use the Data Guard […]