Neil Hambly

Well today was defintely eventful to say the least, firstly my mum was moving house so it was a big occasion, fortunately she is only relocating to a new property a few streets away from what was our family home for more than 20 Years.. so although I’m sad to not be going back to that house again, she is still in the same town and that is great news

Secondly … now honestly this was a COMPLETE surprise to me, was that I got an email informing me that my regular 75 minute SQLPASS Summit Session that was previously choosen as an "alternate" had been promoted to be an “Accepted” session 

WOOT I’m going to be a SQLPASS Summit Speaker

My Session is aptly named “Why are we Waiting..”  see the abstract on the site for more information, it should be up on the SQLPASS website sometime next week is my guess

http://www.sqlpass.org/summit/2011/Speakers/CallForSpeakers/SessionDetail.aspx?sid=1135

I have previously done this session a few times, I 1^st presented it @ SQLBits 8 (Brighton, April) and then again a somewhat shortened session in London (last month) , I’m also doing a special 2-hour version of this session @ Cardiff on 11^th August {looks like that is a good practice session}

I have some new revision I have working on, with the recent CTP3 Denali release and for those not going to SQLPASS and in the UK, get along for a pre-view of my session in Cardiff (11^th August)

Registration @ www.SQLServerFAQ.com look @ events and select Cardiff, incidentally I have 4 other session in August as well

10^th Aug – Southampton – session on Partitioning

11^th Aug – Cardiff – The special 2-hour Waits session

18^th Aug – London – Session on Partitioning

24^th Aug – Manchester – Replication (not an intro – but troubleshooting and performance)

25^th Aug – Leeds – XQuery (Intro)

I could tell you all about my submissions and why this session is my “Favourite” session to give, but I do think you have to see it in person to get the “real impact” as to why this is so useful a tool to have @ your disposal – video of my initial sessions @ the SQLBits website

I must take some time to THANK any and all of you for your generosity and support, you know who you are and I will do my thanks in-person as I see you over the coming months, to all those I will not get to see in that time – thank you all and the SQL Community has been a JOY to become a part of over the last couple of years

To celebrate – as we have a Drinks session on Friday 5^th August in London (nr Liverpool Street) so hope to see some of you there and I’m buying a few rounds

Now have some work to do to make this session the best I can

See you @ SQLPASS Summit – not long now {I must book flights/hotel in next few days}

I was describing my SQL activities that I have done this year to someone (& those I’ve still yet to attend) , I even surprised myself a little on just how much it came too and I'm not just talking about costs involved, which believe me are no way incidental when @ > £10K from my own funding

So was it worth it – so far the answer has to be “You bet your bottom dollar it was”

Here is a brief rundown of those events

Conferences

SQLBits With #8 was in Brighton (7^th – 9^th April) where I did my “Waits” session  - video for that is up and can be seen in a few different versions http://sqlbits.com/Sessions/Event8/Why_are_we_Waiting_

With  # 9 is going to be in Liverpool (Sept 29^th – 1^st Oct) – I have 4 sessions submitted and we’ll know soon, If  I will be presenting any of those

SQL-in-the-City {Red Gate} this was in London (15^th July) and I covered Compression for my presentation, more on that session will be posted soon

http://sqlinthecity.red-gate.com/events/london/

SQLPASS Summit

{Revision 28-July}

Now a speaker @ SQLPASS Summit - my "Why are we Waiting.." session was moved to "Accepted" from "Alternate" - how cool is that

Sadly was not successful in my bid to get my session selected on the agenda, both however are @ “Alternate” status so could Theoretically be chosen as a replacement, so I will have these ready and waiting just in case, one is a Regular 75 session (a version of my “Waits”) and the other is a ½ day session (3.5 Hours) on SQLOS – I will plan to run this in London before I go to the summit, trying to find suitable venue to run this @ - any attendee costs for this will be very low

SQL Saturday #92

Great opportunity to attend my 1^st SQL Saturday in Portland the weekend preceding the Summit, Again not one to miss an opportunity I have submitted 3 session and if I’m fortunate enough will be presenting one @ that event {Wish me luck with the selection} – but will be going to this event for sure, just have to figure out my travel plans carefully

SQL Rally Nordic

I’m hoping I can get a chance to attend and present @ the upcoming SQL Rally to be held in Aronsborg, Sweden on 8^th – 9^th November {have 2 sessions in so far may add another before closing date on 31^st July}  and if I don’t get to present @ this one I may just sadly have to miss it – so let’s hope it is not the case

Training

This was 2 separate and different events but each had completely different setting and objective

Firstly I attended the SQLCruise – Alaska http://sqlcruise.com/ May 29^th – 5^th June , it was 7 Days of pure SQL Training and cruising all rolled into one “hell of an adventure” – It was for me @ least part Vacation and part SQL Training to take it easy and yet still indulge in my passion for SQL – Excellent training sessions by the instructors (Brent, Tim,Kendra,Jeremiah,Buck,Brad,Grant) and great discussions with my fellow cruisers – did a bunch of excursions on one some of us went snorkelling in Alaska !

SQLSkills Immersion (Week 1)

Yes – Paul and Kimberley did manage to hold a week-long event in our own backyard (well next to Heathrow) , some 40+ of us spent the week trying to keep up with the fast paced, question heavy {ahem – I’ll admit quite a few from me } that was 20^th -24^th June, this was the agenda we covered http://www.sqlskills.com/T_ImmersionInternalsDesign.asp

Now if that was all then that would have been quite a lot in itself, but I haven’t covered the User Groups events as yet

User Groups

19^th Jan – Surrey - Not Presenting

2^nd Mar – London - Presented on XQuery

9^th Mar – Southampton - Not Presenting

10^th Mar – Birmingham - Presented on Service Broker

13^th April – Southampton – Presented on Extended Events

16^th June – London – Presented on Waits Stats

20^th July – London – Not Presenting (but was supposed due, postponed to 18^thAug)

Upcoming User Groups I’m speaking @ (Confirmed)

10^th August - Southampton – Presenting on Partitioning

11^th August - Cardiff – Presenting on Waits (this is a 2 hour session)

18^th August – London – Presenting on Partitioning (postponed from July)

24^th August – Manchester – Presenting on Replication

25^th August – Leeds – Presenting on XQuery

Sept – Dec – events are still to be organised but likely it will be between 4-6 of them in total

So as you can see I’ve been somewhat busy on the SQL front and this is not including the many evenings where I spend time learning SQL towards my MCM goal and putting together the presentations for all these session I present @ - anyone who presents will know just how much time that entails, Why do I do all this – Well I love being a SQL Professional and I really enjoy teaching a small part of what I know to others.. With drinks after these sessions good as well!!!!

Having just returned from London where I was one of the presenters @ the 1st Red Gate "SQL in the City" Event (London), next one is in Los Angeles

I was fortunate enough to be a presenter amongst others, as well as the some of the team from Red Gate - here is a quick listing of the speakers/sessions

• Grant Fritchey "Performance Tuning with Knowledge" & "T-SQL Horrors"
• Stephanie Herr "Team based SQL Development"
• Brad McGehee "Managing Data Growth" & "How to become an Exceptional DBA"
• Noemi Monero/Tom Austin "Business Intelligence Development using SQL Developer Tools"
• Neil Hambly "Effective Data Volume Management with Data Compression"
• Mladen Prajdic "Team Database Testing"
• Jonathan Allen "Real-life Problem Scenarios Solved"
• SQL Tools Team "Launch Pad - New & Improved Tools for Database Best Practices"
• Steve Jones "Preparation for Disaster"
• Buck Woody "Put your Database in the Cloud with SQL Azure"

I thoroughly enjoyed giving my presentation and look forward to seeing your feedback on it, along with viewing the recordings of it and the others sessions once they are available, one of my favourite aspects of these events is the meeting of old friends and also making some new ones, helping answer questions that some of the attendees had for me, both during my session and afterwards - if you still have some Q's and want to email those to me please send to Neil.Hambly@Hotmail.co.uk and I will try to provide an answer to those for you as quickly as I can

If you were @ my session then you would have seen that I listed some of the upcoming UK SQL user group events, further details & registration for those can be found @ our website www.sqlserverfaq.com & also please join our LinkedIn group for information and discussions we have regularly @ "UK SQL Server User Group" http://www.linkedin.com/groups?home=&gid=2904068&trk=anet_ug_hm

As well I mentioned that some regular speakers from the UK have their SQLPASS Summit sessions in the Community Choice Voting until the 20th July we have from the UK Chris Webb, Jen Stirrup & Neil Hambly - so please login and vote for you preferences of the selected sessions { We need your support } you'd like to see http://www.sqlpass.org/summit/2011/SummitContent/CommunityChoice.aspx

Also SQLBits 9 "Query Across the Mersey" in Liverpool (29th Sept - 1st Oct) you once again have your very own 10 votes to place for your favourite sessions, over 100 sessions to choose from .. once again I'm are counting on your votes to get selected to present a session http://www.sqlbits.com/information/PublicSessions.aspx

Finally a Big "THANK YOU" to Red Gate Team for putting on a wonderful event for all who attended, and hope to see you @ the next ones and SQLBits later this year

Also great to see Klaus Aschenbrenner who fly in, come to conference and then fly back out straight after it (Great to see you - hope you enjoyed the day)

If you didn't know, Klaus is running a 3-day Training event on SQL in London (Sept) for which you can still get an "Early Bird" discounted price on until end of July "Advanced SQL Server Performance Troubleshooting Workshop" on September 12 - 14 in Central London, United Kingdom http://www.csharp.at/Registration_UK.aspx

Enjoy the Weekend - maybe will se you @ the next London User group event on 20th July, I'm presenting on partitioning and we have 2 BI topics as well, full details @

http://sqlserverfaq.com/events/297/SQL-London-UserGroup-20th-July-2011-with-Neil-Hambly-Alex-Whittles-Avanade.aspx

So you are on SQL 2008 versions, have Enterprise Edition and wish to take FULL advantage of Compression

Well you could just compress everything but frankly that is highly unlikely to be you BEST choice

So which objects are good candidates to be compressed and those that can leave uncompressed

Well for both my upcoming session http://sqlinthecity.red-gate.com/events/london/agenda#row-2 on Compression @ http://sqlinthecity.red-gate.com/ on 15th July as well as some compression projects @ work

I decided that a quick report of objects within a database with the right level of information on Table/indexes/pages/compression/usage would be great start to find those objects just prime to apply the appropriate compression level, but what level Row or Page would be the best

Striking the right balance between the compression level and storage benefits vs. the overhead of compressing/decompressing is key, so by looking @ the Update & Scan percentages, it can be relatively straightforward then to define your thresholds and identify those objects that should have either

PAGE | ROW | NONE compression applied

Below is a script to find the information and you can choose you own thresholds for what is right for you environment.

Mine would be likely be something < 10-15% update < 70% scan for PAGE, ROW would be slightly higher thresholds and NONE would be for those not compressed

;WITH

CTE_Partitions

(

 [OBjectID],[Table_Name],[Index_Name],[Partition]

,[Index_ID],[Index_Type],[Percent_Update],[Percent_Scan]

,[Compression_Level],[IN_ROW_DATA],[ROW_OVERFLOW_DATA],[LOB_DATA]

)

AS

(

SELECT

 o.object_id                      AS [Objectid]

,o.name                                  AS [Table_Name]

,i.name                                  AS [Index_Name]

,ios.partition_number                    AS [Partition]

,i.index_id                       AS [Index_ID]

,i.type_desc                      AS [Index_Type]

,ios.leaf_update_count* 100.0 /

 (ios.range_scan_count          + ios.leaf_insert_count

+ ios.leaf_delete_count     + ios.leaf_update_count

+ ios.leaf_page_merge_count + ios.singleton_lookup_count

 )                                AS [Percent_Update]

,ios.range_scan_count* 100.0 /

 (ios.range_scan_count          + ios.leaf_insert_count

+ ios.leaf_delete_count     + ios.leaf_update_count

+ ios.leaf_page_merge_count + ios.singleton_lookup_count

 )                                AS [Percent_Scan]

,p.data_compression_desc          AS [Compression_Level]

,ps.in_row_used_page_count        AS [IN_ROW_DATA]

,ps.row_overflow_used_page_count  AS [ROW_OVERFLOW_DATA]

,ps.lob_used_page_count           AS [LOB_DATA]

FROM sys.dm_db_partition_stats ps

JOIN sys.partitions        p     ON ps.partition_id= p.partition_id

JOIN sys.indexes           i     ON p.index_id  = i.index_id

                                 AND p.object_id = i.object_id

JOIN sys.dm_db_index_operational_stats(db_id(),NULL, NULL, NULL)

                             ios    ON i.object_id = ios.object_id

                                 AND i.index_id  = ios.index_id

JOIN sys.objects o ON o.object_id= ios.object_id

WHERE

 ( ios.range_scan_count + ios.leaf_insert_count

+ ios.leaf_delete_count + ios.leaf_update_count

+ ios.leaf_page_merge_count + ios.singleton_lookup_count)!= 0

AND objectproperty(i.object_id,'IsUserTable')= 1

)

Select *

From CTE_Partitions

--WHERE [IN_ROW_DATA] > 100

ORDER BY [IN_ROW_DATA] DESC,[Index_Name]ASC, [Percent_Update]ASC