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.

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:

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.

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.

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 DECLARE @cpu_count INT, @file_count INT, @logical_name SYSNAME, @file_name NVARCHAR(520), @physical_name NVARCHAR(520), @alter_command NVARCHAR(max) SELECT @physical_name = physical_name FROM tempdb.

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.

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.

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.