New award!

This morning my lovely daughter, Lucienne  has decided it’s high time I got some awards for all my top-gun DBAing. And here they are:-


Thanks, meine tochter!


VMM SQL Database Query to find assigned IP Addresses

Yes, this is an edge case. But if someone randomly asks you to interrogate a Virtual Manager (VMM) SQL Server Database, to determine which IP addresses have been allocated to which VMs, use a query like this:-

CAST( CAST( SUBSTRING( [Address], 1, 1) AS INTEGER) AS VARCHAR(3) ) + ‘.’
+ CAST( CAST( SUBSTRING( [Address], 2, 1) AS INTEGER) AS VARCHAR(3) ) + ‘.’
+ CAST( CAST( SUBSTRING( [Address], 3, 1) AS INTEGER) AS VARCHAR(3) ) + ‘.’
+ CAST( CAST( SUBSTRING( [Address], 4, 1) AS INTEGER) AS VARCHAR(3) ) AS ‘IP Address’,
FROM dbo.tbl_NetMan_AllocatedIPAddress
order by AssignedToObjectName

I was asked to do this today, and spent an entire afternoon figuring this stuff out and working out how to reverse engineer an IP address which is stored in a varbinary column.

I hope, I pray (and I am an atheist) that this blog post saves someone a bit of hassle and time one day.

SQL Server Healthcheck Toolbelt

This post outlines which tools I use when health checking a new instance of SQL Server. This mainly applies to on-premise, classic instances, but some of the tools and techniques could be applied to cloud services.

SQL Server Error Log
This should be the key tool in your belt. It tells you many things:- are serious errors occurring? Are databases and their transaction logs being backed up?
You should ensure the log is recycled at least once a day. Set up an SQL Server Agent job to run

dbcc errorlog

in the master database daily.

Review the log – what is going on? There should be SOMETHING there, log backups, database backups, and possibly login failures for example.

Brent Ozar, apart from being basically the finest SQL Server person there is, provides (for free), a suite of code to gather diagnostic info from your servers and present it in a useful format. You should download the “First Responders Kit” from his website and start using it straight away.
Work through the findings of sp_Blitz in a methodical way, prioritising the important stuff, as you should know how to, if you’re an experienced DBA.

The definitive, labour-saving utility proc from Adam Machanic can be found here. It snapshots currently executing sql batches, showing wait types and blocking chains, query statements etc – it is the mutt’s nuts. Don’t bother with sp_who2 any more – its day has long gone.

Does the place you’re at have any 3rd party tools which will give you dashboards and reports to help you straight away? Maybe they have SQL Sentry or Dell Performance Analysis for SQL Server, or one of the many other products off the shelf. Make sure it’s working properly, get access to the interface, start harvesting its rich and very useful plethora of diagnostic information.

Session Capture
If you don’t have access to Tools, or no budget, you can roll your own. You can use my blog post to snapshot currently executing SQL batches, for anaylsis after the fact.

Use Windows Performance Monitor to log some basic diagnostic counters, such as disk memory, processor and network utilisation, SQL memory metrics, cache hit ratios etc. – you need to do this manually if you don’t have external tools. Start benchmarking the instance straight away – so you can prove you’re making things better (or not!)

Aggregate Wait Stats
Use Paul Randal’s script to look at what SQL is waiting for (or the waits that matter, his script ignores all the guff). Get it here.

This stuff is just the start. Once you’ve sorted the basics, start looking at indexing, poorly performing SQL, database design, archiving and so on. Your job is just beginning. Rome wasn’t built in a day – but hey, I wasn’t on that job.

You’ve tried the cowboys – now try ….

SQL Bits 2016 in Liverpool

This coming weekend I am off to Liverpool for SQLBits. I’m very excited, I’ve never been there before. I get a day out of the office, a leisurely and relaxing journey on the train, and the weather here in the UK is currently glorious early summer sunshine and warmth.

I am missing the first two days but will be there on the Friday and Saturday. On Saturday afternoon I am doing a 5-minute “lightning talk” on the subject of SQL Server Healthcheck – hit the ground running – I am the very last speaker of the day in that session. The 9 of us doing lightning talks are in competition, the one whose session is voted the best gets to be guaranteed at 1-hour full session at the next SQL Bits.

To attempt to make mine stand out, it includes pictures of me almost naked, under a car, aged 2 looking at a baby chick with a chick, mixing concrete, and drinking beer while on the TAP Programme at Microsoft in Redmond. Don’t miss it!