June 2011 - Posts

If you missed my session @ SQLBits 8 (Brighton) on "Queues & Waits" and are feeling somewhat brave, you can now view it in your own home/office and @ your leisure, so pull up a chair and settle in for a hour of "Waiting" .. hmmm that doesn't sound quite right but if you insist

Follow this link and enjoy me in Full HDD if that is your preference (is that the right word ?), well hopefully you will not cringe quite as much as I did watching it

{But I do think that is universal response when watching yourself on video - or it just me} Why are we Waiting.. Video

Finally a BIG THANK you to all on the SQLBits team for a fabulous SQL conference we had and the upcoming one will be just as exciting if not more so in Liverpool @ the end of September, remember to register early to not miss out, and for not making me seem like I had just escaped from somewhere

Here's to the next SQLBits I can't "Wait".. Somebody please stop me making these stupid puns! 

Posted by NeilHambly | with no comments

Time for a quick reflection on my 5-day's training with SQLSkills, they have 4 weeks in their immersion training program, this was week 1:
Internals & Performance held @ large Heathrow Hotel http://www.sqlskills.com/T_ImmersionInternalsDesign.asp

So was the Course worth the Time and Money... undoubtedly, I believe we had a large number of the people there also self-funding along with the lucky corporate sponsored ones.

It was akin to doing say the "London marathon" in that you know that you have a long journey ahead, so you need to "pace" yourself but will
be pushed both "Physically & mentally" along that journey @ times and you probably hit the "Wall" during the event, but the support of all those with you can carry you onto success.

We did had one casualty who left us early on due to a medical concern and that was unfortunate

With plenty of refreshment breaks which also did give you time to {+ a mental breather} and chat with your fellow attendee's, swap notes
and horror stories as well as gorge on snacks and treats, and lots of Coffee & Tea on Tap, we had bacon rolls @ one point, alongside with cookies -
somebody just told Kimberly we had Cookies with Tomato ketchup! {very funny}

I asked a ton of questions, added some notes from my experience {sorry} @ times and was the most vocal of the group for sure, just
hoped it helped {some of the time}

My favourite sections where module 8: Partitioning (Kimberly) and module 4: Logging and Log Files (Paul) - but I learnt a ton from each one of the modules

I enjoyed my open-mike slot where I did a brief (now there's a first) 15 minutes on my topic "Query Hash Statistics" http://archive.msdn.microsoft.com/QueryHashStatistics in SQL 2008, we had also some great other session - My favourite of them all was the last one on Backups

Our group photo was a feat in itself - I'm look forward to seeing that one soon

So would I do another of the weeks - YES and YES, so let's hope we see them back in the UK for the other weeks soon

Posted by NeilHambly | 1 comment(s)
Filed under:

As I prepare to embark on my Immersion training week with Paul & Kimberly from SQLSkills, which is another one of the courses being offered in the UK this year, it seems that these invariably get full very quickly, so don't hang around or you will miss your opportunity to attend them

I do know of some other great SQL courses that give you in-depth training and these are the ones that I know of (shown is date order)

We also have the upcoming SQLBits 9 Conference, more details to be announced on this soon I believe (No dates or Venue announced just yet)

Watch this space for further announcements on courses, conference, user-groups and social events over the coming months as I get more details


Posted by NeilHambly | 1 comment(s)
Filed under:

If you didn't already know Redgate have 2 full 1 day conferences planned Called "SQL in the City", these being held @ the following 2 major cities

  • London, UK         on 15th July (Now Full with waiting list)
  • Los Angeles, US on 28th Oct

It is a full days’ worth of FREE SQL Server training sponsored by Redgate, you get the opportunity to attend a number of training sessions in the track of your choosing, along with the chance to network with your peers and interact with SQL MVP's, Redgate staff & other speakers throughout the day

In my London session @ 13:30 - 14:25 “Effective Data Volume Management with Data Compression” (straight after the provided lunch) for the Database Admin track.

I'll be speaking about Data Compression within SQL Server as well as the Redgate “SQL Storage Compress” Product

Looking forward to the day and hope to see you there (if you are lucky enough to have a ticket)


Posted by NeilHambly | with no comments

London SQL Server User Group (Host by IMGROUP) 16th June

One of my own failings of past, has been not doing a follow-up on the User-Group events I attend and frequently speak @, with post event blog & slide decks, this last Thursday was just one such occasion so here is the blog I promised

We had somewhere around the mid 30's attend (I forgot to count it seems) and I think we all had a wonderful evening covering both SQL & non-SQL topics during the evening (let me know you thoughts on the evening- we appreciate the feedback)

Firstly we had the speaker from the IMGROUP up and due to the intended speaker being ill @ the 11th hour, they found us a replacement with a few hours ‘notice.

So we had the very talented speaker "Kay Lau" Senior Consultant @ IMGROUP

She held us captivated for the 20-25 minutes talking on "How does Microsoft BI compare with SAP BusinessObjects?" with a slide deck not even her own (very brave), but you wouldn't have known that from her delivery - so Kay our THANKS for a wonderful presentation

Next up was the very talented SQL Server MVP and long-time UG leader Tony Rogerson, Tony had a different twist in store for us that evening than his normal SQL fare, this time he showed us the delights of a very high performance / low cost commodity kit "Reporting Brick" I was fascinated by it all and enjoy the benefits of a FusionIO IOXtreme card in my desktop @ home and know first-hand how impressive these numbers are in reality, find his deck @ the follow


We then broke for Pizza (+ the trimmings) & Beer or other drinks and some networking - let's just say we did have a few not turn up who had registered (tsk tsk) but the amount of food laid on was Awesome and was more than enough to satisfy the hungry SQL professionals

Also a HUGE thanks to Panos @ IMGROUP, who helped us with everything that evening and I must say did a great job of making sure it all went smoothly

Once feed and ‘watered' we resumed the evening with the final session, this was when I presented my "Why are we Waiting" session, it is a repeat of my session I had given earlier in the year @ SQLBits 8 in Brighton, but to a smaller crowd this time around... it is strongly focused on the dm_os_wait_stats DMV information

I think it went well and I will post up the slide deck along with this blog, the material is going to be a struggle to cover in any depth in 1 hour session, so I hope I
was able to convey the concepts well enough and you can learn more on this methodology

I do recommend that everyone takes time to read the Whitepaper for this "Waits & Queues Methodology" initially it was written for SQL 2005, but still more than
relevant today for SQL 2008 http://msdn.microsoft.com/en-us/library/cc966413.aspx

A quick pint @ the local pub (right next door) with Mark Pratt, who I will see next week while attending SQLSkills immersion training @ Heathrow from Paul Randal &
Kimberly Tripp

There are also some evening events sponsored by SQLSentry and FusionIO for others not on the course to attend, so I may even see you there if not on the course itself of course

Finally we have another other SQL User Group event in London & around the UK, London has a BI one next week 21st June

Please visit www.sqlserverfaq.com for more details on that & other events

Posted by NeilHambly | with no comments

I will not go into a full discussion as to why you would want to convert HEAP into a Clustered table .. as there are plenty of resources out there that describe those elements and the relevant Pro's & Con's

However you may just want to understand which database tables are of the HEAP variety and how many of them "percentage wise" exist in each of your Databases


So here is a useful script I have (it uses the sp_msforeachDB to iterate through all DBs on an instance), that easily give me a current tally, I also use it to get an eye on number of tables in each database etc

I'd usually put this is Excel and date my worksheets for this or store the data in your Admin Database's - if you use a permanent table make it a clustered one !

Perhaps you may find it useful like I do to convert these HEAPS into our preferred Clustered Tables -- Enjoy


Definitely useful if like me you have more than a few Databases to keep track of

Today's count is 1170 Databases with more than  1 million tables in total and yes we do have some HEAPs in amongst those and I'm working on moving those to Clustered variety


create table #Heaps ([DBNAME] VARCHAR(128),[Heap Count] INT,[Table Count] INT,[Percentage of Heaps] DECIMAL(5,2)); EXEC sp_msforeachdb @command1 ='USE ?-- percentage of heapsdeclare @heapCount numeric, @tableCount numeric;select @heapCount = count(t.object_id)from sys.tables as tjoin sys.indexes as i on t.object_id = i.object_idwhere i.type_desc = ''HEAP'';select @tableCount = COUNT(*) from sys.tables; INSERT INTO #Heaps ([DBNAME],[Heap Count],[Table Count],[Percentage of Heaps])select DB_NAME() AS [DBName],@heapCount  AS [Heap Count],@tableCount AS [Table Count],CASE       WHEN @tableCount = 0 THEN 0       ELSE CAST((@heapCount / @tableCount * 100) as numeric(5, 2))       END as [Percentage of Heaps];'Select * from #HeapsDROP table #HeapsGO  -- list all the heaps in the currently selected databaseselect OBJECT_NAME(t.object_id) as 'Table Name', i.type_descfrom sys.tables as tjoin sys.indexes as i on t.object_id = i.object_idwhere i.type_desc = 'HEAP'order by OBJECT_NAME(t.object_id);GO 


Posted by NeilHambly | with no comments
Filed under:

This post is about my attempt and slight failure @ getting a presenting session @ this year’s SQLPASS Summit 2011

I had submitted for the 1st time 2 submissions (think we had max of 4 we could enter, but I was happy to go with just 2 this time, 1 I had already presented & 1 was nearly completed)

My general session (75 minutes) the same session on  “Waits” I had done @ SQLBits 8 back in Brighton last April,  and a new 1/2 day 3.5 hours format which is a session I’m completing on SQLOS layer

Well today was the day of reckoning and I’m a little sad to admit I was NOT selected for either to be a "Confirmed" session, moreover they both where given “Alternate” status, so that means I could be asked if things change for any reason …. But that is not very likely in reality - but it's still a win in my eye's

Now this may seem that I failed and on one hand that is true (I'm not presenting @ SQLPASS), however it was my 1st attempt - so let's put it in perspective.

This time around they had > 650 submitted sessions by some of the most experienced speakers out there as well as first timers like myself, and with only 111 sessions have been selected and some of those include the spotlights sessions etc. it was going to be a tough battle for anyone’s session to be chosen..

They have only 4 of the new format ½ day sessions (all by very well-known speakers) so I was pushing the boat out on that to start with {perhaps}

The normal session I had was again up against really stiff competition including MVPs & MCMs alike, and I don’t know how many “Alternate” ones they have and where mine would have ranked but it was validation to me that I could have been asked (or still could – “living the dream”) if I was perhaps more X & Y in some category or another or I had worn my luck socks @ some point etc..

Let’s not forget this is #1 SQL conference after all and people from all around the globe come to this event, so the scrutiny is that little more intense  

So I will be @ the SQLPASS Summit 2011 and hope to meet some of you there and enjoy the event as a Attendee but will try to rub shoulders with some of the Speakers as well  if they wonder across my path

My regular session is also now in the submission stage for SQLRally Nordic <fingers crossed again>

THANKS to the SQLPASS committee – you have done a great job and I hope to meet you when I’m in Seattle again in November

I was there only last week returning from my Alaska SQLCruise (Wonderful adventure that was)

Congrats to all those chosen you are worthy winners and I hope to see some of your sessions when I'm there 


Posted by NeilHambly | with no comments
Filed under:

Aaron Betrand has posted a great article on the Patch Tuesday Security Bulletin and I have quickly translated that into a SQL script to check your version and advise what you should be doing

http://www.microsoft.com/technet/security/Bulletin/MS11-049.mspx Aaron's article: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/06/14/security-updates-for-all-supported-versions-of-sql-server.aspx#comments 

Naturally ANY Script needs to be carefully vetted before it is used in your own environments; this is no exception to that rule

-- This Script only reports on your current version and recommends your course of action as per Aarons article - please ensure this is suitable for you environment
;With CTE_SQLEditions([Major],[Minor],[Build],[BuildMinor])
  parsename(convert(varchar,serverproperty ('productversion')),4) As Major,
  parsename(convert(varchar,serverproperty ('productversion')),3) As Minor,
  parsename(convert(varchar,serverproperty ('productversion')),2) As Build,
  parsename(convert(varchar,serverproperty ('productversion')),1) As Buildminor
Select @@SERVERNAME, *
 WHEN Major = 9      -- SQL 2005
   WHEN Build < 4035 THEN 'Upgrade to Service Pack 3 (9.0.4035) or Service Pack 4 (9.0.5000), then come back for the GDR'
   WHEN Build = 4035 THEN 'Install the SP3 GDR (9.0.4060) from KB #2494113'
   WHEN Build > 4035 and Build <= 4339 
    THEN '(a) Upgrade to Service Pack 4 (9.0.5000), then come back for the GDR  OR  (b) Install the SP3 QFE (9.0.4340) from KB #2494112'
   WHEN Build = 5000 THEN 'Install the SP4 GDR (9.0.5057) from KB #2494120'
   WHEN Build > 5000 THEN 'Install the SP4 QFE (9.0.5292) from KB #2494123 '
 WHEN Major = 10 And Minor = 0   -- SQL 2008
   WHEN Build < 2531 THEN 'Upgrade to Service Pack 1 (10.0.2531) or Service Pack 2 (10.0.4000), then come back for the GDR'
   WHEN Build = 2531 THEN 'Install the SP1 GDR (10.0.2573) from KB #2494096'
   WHEN Build > 2531 and Build <= 2840  THEN '(a) Upgrade to Service Pack 2 (10.0.4000), then come back for the GDR  OR (b) Install the SP1 QFE (10.0.2841) from KB #2494100'
   WHEN Build = 4000 THEN 'Install the SP2 GDR (10.0.4064) from KB #2494089'
   WHEN Build > 4000 THEN 'Install the SP2 QFE (10.0.4311) from KB #2494094'
 WHEN Major = 10 And Minor = 50  -- SQL 2008 R2
   WHEN Build = 1600 THEN 'Install the GDR (10.50.1617) from KB #2494088'
   WHEN Build > 1600 and Build <= 1789 THEN 'Install the QFE (10.50.1790) from KB #2494086'
   WHEN Build > 1789 THEN 'Wait for the final release of Service Pack 1, Watch for cumulative update or updates to MS11-049, At this time there is no fix for the CTP of SQL Server 2008 R2 SP1'

Posted by NeilHambly | with no comments

I start off by saying, "I'm not that organised" @ times and things just don't get done even If I want them too

So having missed some of my own personal targets so far this year already, I thought I needed some better way to achieve on those targets and one of these is to be a "little" more organised with my spare time, so I have decided that I will "try out" the following to see if it helps

For 3 weeks each month I will follow this routine (my evenings after work)

Monday - Spend some time Reading the blogs I follow & Write my own Blog entries (don't do hardly any and have tons of ideas for those)

Tuesday - Book night - this is predominately focused on working my way through the SQL MCM reading list + I will mix this up with entries from my 12 books I will choose to read in the next year - it's my SQLCruise homework (I know they sent us back with homework to do !!)

Also to add to my MCM resources list* @ least once each week (or @ the weekend) I also do need to find help with this big task to add content myself *MCM Resource list

If interested in adding some content to this resource drop me an email @ Neil.Hambly@Hotmail.co.uk

Wednesday - Either Presenting, creating or rehearsing session (PPT slide etc...) or other SQLFAQ User-Group tasks, also help answer a few questions on the forums I don't do enough on them and it's been a time thing really - so want to spend some time each week on those

Thursday - Either presenting or same as Tues focusing on my SQL MCM material, also going to have this as "Denali" night and will be reviewing the next release probably on a week on/off basis

Friday - Nothing, having the night off - good to let the mind have some downtime

Weekend - well that depends, if away then will unlikely be SQL related, but if @ home that gives me a whole lot of time to get some quality time with SQL if I so choose, unlikely to be every weekend but maybe a couple of them each month

Posted by NeilHambly | 1 comment(s)