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 the reservations on some disks, leading to some rather weird error messages. The solution is to us powershell to give said reservations the boot like this: Clear-ClusterDiskReservation -disk X -force Speaking of clusters; trying to find which node the SQL Server is running from can be a bit of a challenge from within SQL Server.

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;GORECONFIGURE;GOsp_configure'max server memory',40000;GORECONFIGURE;GO-- Turn on optimize for adhoc workloads EXECUTEsp_configure'show advanced options',1;GORECONFIGURE;GOEXECUTEsp_configure'optimize for ad hoc workloads',1;GORECONFIGURE-- Turn on DAC EXECUTEsp_configure'remote admin connections',1;GORECONFIGURE;GO-- Adjusting TEMPDB ALTERDATABASE[tempdb]MODIFYFILE(NAME=N'tempdev',SIZE=100MB,FILEGROWTH=50MB);GO-- Script to create TEMPDB files like the one existing USE[master]goDECLARE@cpu_countINT,@file_countINT,@logical_nameSYSNAME,@file_nameNVARCHAR(520),@physical_nameNVARCHAR(520),@alter_commandNVARCHAR(max)SELECT@physical_name=physical_nameFROMtempdb.sys.database_filesWHERENAME='tempdev'SELECT@file_count=Count(*)FROMtempdb.sys.database_filesWHEREtype_desc='ROWS'SELECT@cpu_count=8WHILE@file_count<@cpu_countBEGINSELECT@logical_name='tempdev'+Cast(@file_count+1ASNVARCHAR)SELECT@file_name=Replace(@physical_name,'tempdb.mdf',@logical_name+'.ndf')SELECT@alter_command='ALTER DATABASE [tempdb] ADD FILE ( NAME =N'''+@logical_name+''', FILENAME =N'''+@file_name+''', SIZE = 100MB, FILEGROWTH = 50MB )'PRINT@alter_commandEXECSp_executesql@alter_commandSELECT@file_count=@file_count+1END;--Create operator DBATeam USE[msdb]GOEXECmsdb.dbo.sp_add_operator@name=N'DBATeam',@enabled=1,@pager_days=0,@email_address=N'foo@bar.com; gazonk@bar.

Better Index Usage DMV

Slightly altered Aaron Bertrand’s “Better Index Usage DMV” to script out CREATE INDEX-statements. SELECTd.\[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,''),', ','\_'),'\[',''),'\]','')+CASEWHENd.equality\_columnsISNOTNULLANDd.inequality\_columnsISNOTNULLTHEN'\_'ELSE''END+REPLACE(REPLACE(REPLACE(ISNULL(d.inequality\_columns,''),', ','\_'),'\[',''),'\]','')+'\]'+' ON '+d.statement+' ('+ISNULL(d.equality\_columns,'')+CASEWHENd.equality\_columnsISNOTNULLANDd.inequality\_columnsISNOTNULLTHEN','ELSE''END+ISNULL(d.inequality\_columns,'')+')'+ISNULL(' INCLUDE ('+d.included\_columns+')','')ASCreate\_StatementINTO#candidatesFROMsys.dm\_db\_missing\_index\_detailsASdINNERJOINsys.dm\_db\_missing\_index\_groupsASgONd.index\_handle=g.index\_handleINNERJOINsys.dm\_db\_missing\_index\_group\_statsASsONg.index\_group\_handle=s.group\_handleWHEREd.database\_id=DB\_ID()ANDOBJECTPROPERTY(d.\[object\_id\],'IsMsShipped')=0;CREATETABLE#planops(oINT,iINT,hVARBINARY(64),ucINT,Scan\_OpsINT,Seek\_OpsINT,Update\_OpsINT);DECLARE@sqlNVARCHAR(MAX)=N'';withxmlnamespaces(default'http://schemas.microsoft.com/sqlserver/2004/07/showplan')insert#planopsselecto=coalesce(T1.o,T2.o),i=coalesce(T1.i,T2.i),h=coalesce(T1.h,T2.h),uc=coalesce(T1.uc,T2.uc),Scan\_Ops=isnull(T1.Scan\_Ops,0),Seek\_Ops=isnull(T1.Seek\_Ops,0),Update\_Ops=isnull(T2.Update\_Ops,0)from(selecto=i.object\_id,i=i.index\_id,h=t.plan\_handle,uc=t.usecounts,Scan\_Ops=sum(casewhent.LogicalOpin('Index Scan','Clustered Index Scan')then1else0end),Seek\_Ops=sum(casewhent.LogicalOpin('Index Seek','Clustered Index Seek')then1else0end)from(selectr.n.value('@LogicalOp','varchar(100)')asLogicalOp,o.n.value('@Index','sysname')asIndexName,pl.plan\_handle,pl.usecountsfromsys.dm\_exec\_cached\_plansasplcrossapplysys.dm\_exec\_query\_plan(pl.plan\_handle)ASpcrossapplyp.query\_plan.nodes('//RelOp')asr(n)crossapplyr.n.nodes('\*/Object')aso(n)wherep.dbid=db\_id()andp.query\_planisnotnull)astinnerjoinsys.indexesasiont.IndexName=quotename(i.name)wheret.LogicalOpin('Index Scan','Clustered Index Scan','Index Seek','Clustered Index Seek')andexists(select1from#candidatesascwherec.object\_id=i.object\_id)groupbyi.object\_id,i.index\_id,t.plan\_handle,t.usecounts)asT1fullouterjoin(selecto=i.object\_id,i=i.index\_id,h=t.plan\_handle,uc=t.usecounts,Update\_Ops=count(\*)from(selecto.n.value('@Index','sysname')asIndexName,pl.plan\_handle,pl.usecountsfromsys.dm\_exec\_cached\_plansasplcrossapplysys.dm\_exec\_query\_plan(pl.plan\_handle)ASpcrossapplyp.query\_plan.nodes('//Update')asr(n)crossapplyr.n.nodes('Object')aso(n)wherep.dbid=db\_id()andp.query\_planisnotnull)astinnerjoinsys.indexesasiont.IndexName=quotename(i.name)whereexists(select1from#candidatesascwherec.\[object\_id\]=i.\[object\_id\])andi.index\_id>0groupbyi.object\_id,i.index\_id,t.plan\_handle,t.usecounts)asT2onT1.o=T2.oandT1.i=T2.iandT1.h=T2.handT1.uc=T2.uc;SELECT\[object\_id\],index\_id,user\_seeks,user\_scans,user\_lookups,user\_updatesINTO#indexusageFROMsys.dm\_db\_index\_usage\_statsASsWHEREdatabase\_id=DB\_ID()ANDEXISTS(SELECT1FROM#candidatesWHERE\[object\_id\]=s.\[object\_id\]);;WITHxAS(SELECTc.\[object\_id\],potential\_read\_ops=SUM(c.user\_seeks+c.user\_scans),\[write\_ops\]=SUM(iu.user\_updates),\[read\_ops\]=SUM(iu.user\_scans+iu.user\_seeks+iu.user\_lookups),\[write:read ratio\]=CONVERT(DECIMAL(18,2),SUM(iu.user\_updates)\*1.0/SUM(iu.user\_scans+iu.user\_seeks+iu.user\_lookups)),current\_plan\_count=po.h,current\_plan\_use\_count=po.ucFROM#candidatesAScLEFTOUTERJOIN#indexusageASiuONc.\[object\_id\]=iu.\[object\_id\]LEFTOUTERJOIN(SELECTo,h=COUNT(h),uc=SUM(uc)FROM#planopsGROUPBYo)ASpoONc.\[object\_id\]=po.oGROUPBYc.\[object\_id\],po.h,po.uc)SELECT\[object\]=QUOTENAME(c.s)+'.'+QUOTENAME(c.o),c.equality\_columns,c.inequality\_columns,c.included\_columns,x.potential\_read\_ops,x.write\_ops,x.read\_ops,x.\[write:read ratio\],x.current\_plan\_count,x.current\_plan\_use\_count,c.create\_statementFROM#candidatesAScINNERJOINxONc.\[object\_id\]=x.\[object\_id\]ORDERBYx.\[write:read ratio\];/\*droptable#candidatesdroptable#indexusagedroptable#planops\*/{{</hightlight>}}

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 standard scripts (like the bunch from Brent Ozar & co., SP_WHOISACTIVE from Adam Machanic, the log table for Ola Hallengren’s maintenance solution and such) to keep track of them, and to have only one place to update. In my standard setup scripts for new databases, I also create a local DBADB as well as a local user for replication.

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 a while, and the show length (around 60 minutes) suits me just fine. Matan Yungman and Guy Glantser both work for Madeira SQL Serverices, and are well known speakers in the SQL Server Community. The only problems I’ve found are that they’ve only done 19 shows so far (and I spend A LOT of time in my car :P ) and that they have some issues with sound and mixing levels.

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 log, it was apparent that ASM had no available disk groups for this database. Okay, let’s ask ASM what it feels, then. Looking in the ASM log file it was equally obvious that the ASM instance was online but didn’t service any disk groups. That’s odd.