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 see if someone already had taken care of it (someone usually has). Turns out that there was a blog post by John Pasquet of folioTek here, and a very interesting addition to said blog post (and scripts) by user HAL9000 of Stack Overflow here. It just became my go to script for finding unused indexes.

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.