And you can find it here !
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!
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’,
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.
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
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.
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.
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!
On Thursday (26.8.2010) last week I had the privilege to be invited to spend the day at Microsoft HQ in Reading, for SQL Bits Speaker Training. The day was run by Guy Smith-Ferrier, an MVP, conference regular, and trainer.
We were told to prepare (in advance), a 5-minute presentation on any subject. Mine was on how an aeroplane’s engines work. The day consisted of Guy giving sessions on subjects like demos, how to prepare your laptop and so on, and his presentations contained lots of very useful tips and thought-provoking ideas (and discussion). Interleaved with this were two breakout sessions, where we divided into 4 groups of 5, each led by a conference luminary: Simon Sabin, Mike Taulty, Dave McMahon and Andrew Fryer.
In these groups each participant (not the leaders) gave their 5 minute presentation then faced 5 minutes of feedback and constructive criticism from the group. This was very useful, somewhat nerve-wracking for some people, but clearly beneficial! We then did the same thing nearer the end of the day, in different groups, and there was a clear improvement over what we’d managed in the morning.
Some of the key learning points I took away from the day are:-
– Don’t say “thanks for coming” (credit to Dave McMahon for this one). Say only at the end “Thank you for supporting the community” or some such. You are the one they should be thanking, especially at an event like SQL Bits Community Day. Not all people agreed with this one and there was an interesting discussion, but I decided I agreed with Dave.
– Strategies for dealing with nerves
– How to diplomatically get past obstructive questioners
– Techniques for good demos
– Tips for preparing your laptop
– How to structure your agenda, how to summarise
– How to do an elevator talk (i.e. a 1 to 2 minute initial summary of the session’s goal and outline)
– How to react to negative feedback
There was loads to take away, but I would like to add a personal thank you to all the group leaders and organisers, Chris Testa-O’Neill who was instrumental in the planning but sadly couldn’t make it to the actually event, Simon and all the group leaders but principally to Guy for laying on some really excellent sessions and giving me plenty to think about in how to improve my public speaking.
I have previously written recruitment advice for candidates, in my new job – which is lovely and full of variety – I have been tasked with doing telephone interviews for contract SQL developers. This is fairly straightforward – I have their CVs which I review beforehand and I have a list of 15 technical questions which I use some of (but never all) during the course of the conversation.
Reviewing CVs is very interesting. I have my own views about how a CV should look. I prefer them to be very simple: laid out with no tables, no lines, no multi-column sections. I like only the use of bold for emphasis, no colour, and above all – not too much detail!
One CV I was reviewing recently was 12 pages long! I nearly choked. No one is ever going to read that much, it is simply boring. CVs also have limited utility – they are factual documents and give you an idea of what a candidate’s skill and experience is, but very little more. Even some of the ‘personal statement’-type sections can be a little sterile.
Obviously it’s a subjective thing, but I really like CVs in which there is some kind of evidence of personality. I look for touches of humour, or something about a candidate’s personal (non-work) interests that shows a bit of colour.
In terms of the telephone interviews themselves, I’m not a great fan of the phone at all in general, again, not being able to see a person’s face and how they react to things, makes the process of limited utility. But you can get an idea of a person’s communication skills, a bit of personality and use the technical questions as a filter.
It has certainly made me realise I’m selling myself far too cheaply!
I’ve landed myself a really big one. I’m trying to sort out a database and application which have things like this in them:-
1. DDL designed (and executed) by the user in the application, not temporary tables, not worktables, not permanent tables in tempdb, not table variables. Permanent user tables in the database that are actually crucially important to the application. None of them have any more indexes on them than a clustered primary key IDENTITY field. Yeah.
2. Use of the ‘One True Lookup Table’. Well two actually! One of these is particularly heinous. Free text data, or optionally, images (ugh) are stored in an image data type field and are then converted & looked up by some really WTF-style UDFs. Urgh, urgh, urgh.
3. Use of an ORM tool to abstract interaction with the databases for the programmers. In a word, yuk. have you ever seen LINQ queries? ‘nuff said. This isn’t even LINQ.
4. Error handling. WTF? The only error the application can ever return is “invalid data”. This covers every single SQL Server error that may get raised. Glorious.
5. Cursors in recursive scalar functions. Yum!
6. Views of views of views of views. Love them, I really do.
7. Use of DISTINCT – just in case!
8. Duplicate data. Duplicate columns! Duplicate duplicates!
9. Cartesian product from a join eliminated by using DISTINCT. Performant!
I think I need a lie down. Wish me luck!
|Voting is now open for sessions you’d like to see at SQL Bits 7 in York at the end of September 2010. I have submitted a session on ‘Performance Tuning Quick Wins’. So head on over there and vote. For me! Obviously.
I have recently started a new job and I am working on a post about the challenges facing me. Not to put to fine a point on it, there is enough daily WTFery to fill that wonderful site with content for the rest of the year!
More on that topic later …. until then, get voting! For me!
|SQL Bits 7 has just been announced and it’s going to be in York at the end of September! The free community day is Saturday, with 2 paid days on Thursday and Friday, similar to SQL Bits 5. I have submitted a session on ‘Performance Tuning Quick Wins’! It was a compulsive thing .. I hope I get chosen!
I will let you know when voting opens!
I’m greatly enjoying a bit of free time. I am spending a lot of it here:-
Yesterday, the summer solstice, was perfect: 25°C, cloudless sky, and barely any wind. The pool was heavenly!
|For the rest of the week, it’s more of the same, with a few lunch meetings with friends and ex-colleagues, then on Thursday I’m going down to look after my Mum’s farm (pictured, right) while she and her husband are away. The forecast is excellent, I can’t wait for some lovely swims in the Atlantic (maybe at Perranporth beach, below)!
I am going to take my bike (bottom right) and do some riding on the lovely, rolling, quiet roads of North Devon. I hope you’re envious!!
I love having a week off at this time of the year – I should definitely do it more often!
But maybe not always unpaid …..