Three amigos screw up a cluster

Three amigos joined me for a cluster installation the other day. Neither of them was very friendly, so I decided to call them all out in one place in order for others to steer clear. The opinionated folder Using mount point folders is a great idea for neatness and to avoid having a gazillion drive letters in play. It is also a great idea to review the best practices for SQL Server clusters on mount point folders, as there are a few gotchas. This blog post lays it all out. If you fail to set up the correct permissions, funky stuff will ensue.

Finding the culprit

I had a case the other day where the customer called and told me that he had a bit of a performance problem. Well, I’ve heard that a few times over the years, and asked him to elaborate. He sent me a screenshot of SolarWinds Database Performance Analyzer and, well, it kind of points the finger to one thing: Um. Yeah. The ASYNC_NETWORK_IO thing. It kind of … sticks out. Okay, so how to tackle this then? We know what waits are happening, but where do we go from here? Well, It’s time to find out exactly what is generating said waits.

PASS Summit 2015 - a quick reflection

PASS Summit 2015 is winding down and I am strolling through increasingly deserted hallways. This was my second PASS Summit, and I already know it will not be my last. I’ve been to Oracle OpenWorld in San Francisco six times and compared to that conference this one is downright tiny. But the thing with the PASS community in relation to the Oracle community is like night and day. A quick background: I started out with Oracle back in ‘97 and SQL Server shortly thereafter, and let’s face it - SQL Server was not a serious contender back then. Both SQL Server and Oracle has come a good ways since then, and Oracle is ahead in many respects.

Azure Automation and index maintenance gotchas

I decided to write up a blog post about index maintenance in SQL Azure databases (yes, I’m gravitating towards Azure just like everyone else these days). I had everything planned, what research to do, the case I was going to use, the whole nine yards. It would have been epic, had it not been for Pieter Vanhove, who’s already done it. Worse still, he did it way better than what I had planned. http://pietervanhove.azurewebsites.net/?p=14137 Somewhat miffed, I decided to play with it anyway (just not write a blog post about it, no, I’m not bitter :P ). Turns out there’s quite a gotcha with this:

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>}}