When I was growing up, we used to sit round the t.v. (families only had one in those days) and one of the programs we used to watch as a family was “Wish you were here”. It was a travel guide to not so far off, but at the time exotic locations showing golden beaches and crystal clear waters that were the envy of everyone who just used to end up at somewhere like Butlins.

The reason I’m writing this post is that I’m travelling a fair bit at the moment and although I will be dearly missing my own family, I will be in the company of my #SQLFamily. That’s right folks it conference time!

This Saturday see’s the first ever SQL Saturday in Ireland, it promises to be a great event with some 350 people registered and around 70 on the waiting list. If you had registered and can’t make it please let the organisers know so that they can allocate your place to someone else who is desperate to go. I’m going to be there on one of the sponsor stands and look forward to having a good chat about the problems you face. No hard sell, that’s not my style. I’m just here to help there’s also a great prize, as long as I remember to take it

On Sunday it’s back to old Blighty to prepare for the Maidenhead SQL Server User Group that I run. This is going to be a really exciting event as I have managed to coerce not one but two MVP’s in the form of Kevin Kline (Blog | Twitter) and Jen Stirrup (Blog|Twitter) that are due to be presenting at SQLBits later that week. If you live or work in the Maidenhead area, or even if you’re just down this way for SQLBits we’d love for you to come along. The event is being held on Tuesday 27th March and you can register on SQLServerFAQ.com or for further information about the group please head over to our new site http://www.sqlstudy.org It’s a completely free event, we’ll even provide refreshments and there’s a chance to win some cool SQL Server swag!

Thursday is the big one SQLBits X if you haven’t been to SQLBits before then what have you been doing? It’s the biggest event for SQL Server in Europe and this time there are some 1500 attendees registered across the three days and there are waiting lists for the Friday and Saturday events. Again if you can’t make it please do let the organisers know so that they can allocate your place to somebody else. I’ve been a volunteer for SQLBits a number of times and after packing hundreds of bags it’s really demoralising when you see them left over at the end, especially when you know of people who really wanted to go but were told the event was fully subscribed! Again I’ll be on one of the stands, so feel free to come over and have a chat about the issues in your environment, or even to ask for some advice on how to go about something. The more stands you visit and forms you fill in, the better chance you have of winning prizes at the end of the Saturday event.

The thing I love best about these events (awseome content and speakers aside) is the chance to speak to people who face the same day to day problems as yourself. You can bounce ideas off of them and vice versa. It really is a great opportunity to network as it provides such a great opportunity for both personal and professional growth.

As I’ve mentioned both SQL Saturday in Dublin and SQLBits X are full, buy there are plenty of people who give up their own time to run local user groups near you! To find your local user group in the UK please head over to http://www.SQLServerFAQ.com for more details. If you live outside of the U.K. then there’s a good chance that there is a PASS chapter somewhere near you, head over to http://www.SQLPass.org to find out where. Membership is free and they also provide some great content, in fact there is a free virtual event going on right now, so go over and sign up now!

The message from all UG leaders and conference organisers like myself is simple,  “Wish you were here”

I’m writing this blog post from my bed, you may think that’s a little too much information there is a good reason for telling you this as it provided the inspiration for this post. I hadn’t been feeling particularly well at the tail end of last week but pushed on (as you do) as there were things that needed to be done. Come the weekend I had to relent and haul myself off to seek medical advice. In the UK local GP surgeries are not open at weekends so I had to go to something called a “walk-in centre” where I was made to wait outside as I had arrived 10 minutes before it was officially open. That’s right in the UK you are expected to be ill at times convenient to the NHS! I should point out that I think the NHS is a wonderful institution and admire the people who work for it, without them the country would be in a sad state of affairs indeed.

However, after seeing the initial triage nurse I waited to see somebody else for further assessment who I explained my symptoms and concerns to and I was told that I had a viral infection and should go home and rest up and take paracetamol. Come Monday things were much worse and I made an appointment at my local surgery, within 5 minutes I was diagnosed with a chest infection and prescribed anti-biotics for the infection. Not only was this the right diagnosis the whole experience was a lot nicer too. My concerns had been listened too, having had a bad chest infection a few years back I knew what the signs were and what my body was telling me yet the first “professional” chose to ignore me, was quite curt and subsequently made the wrong diagnosis.

This post wasn’t designed just to get something off my chest (nice pun eh) it was designed to make you evaluate the service that you provide as an individual to your clients be they internal or external. Who would you rather be perceived as Professional #1 or Professional #2? To succeed in Information Technology (or Information Services if you prefer) it is no longer just down to your technical skills (although this obviously helps), you must also be able to communicate clearly and effectively with every audience that you interact with. I wrote a post on the same topic last year that you may find useful – Bridging the gap – Good communication is key 

I urge you all to take stock of who you are, how you represent yourself and outline the steps you need to take to become who you deserve to be. Take that step now and become the right professional.

 

Blimey, doesn’t seem like a year since I wrote my last end of year review End of year review 2010 and goals for 2011

Things didn’t quite go to plan, mainly because of an unexpected career change this majorly shifted a number of my priorities which meant that some goals were re-prioritised, others were replaced and some simply dropped. With this in mind let’s see how I faired with my Goals for 2011:

Community

Last year I made a commitment to the SQL community by starting my blog and attending a few events, this year I have built and consolidated on that platform and intend to take things on to the next stage next year.

• Write at least two articles for one of the major SQL community websites.
• Attend at least one SQL User Group per quarter on top of SQL Bits.
• Present some SQL Nuggets at a User Group session.
• Monitor and contribute more to #SQLHelp on Twitter.
• Contribute more to the various SQL forums.

Partial success here. 

• No published articles, but I did produce 4 SQL Server webcasts for Quest in November so I like to think that this is a tick.
• Tick, I attended 4 UG’s this year. Southampton, Surrey, two in Maidenhead as well as SQL Relay and SQLBits (twice). I kind of cheated a bit here by starting up the Maidenhead User Group. If you’re interested in attending here’s the link for future events and if you fancy your hand at presenting please get in touch.
• Contributing more to various media channels is a subjective task. Whilst I did contribute you always feel that you can do more and I hope to address this in 2012.

One very nice surprise was that I was awarded the Microsoft Community Contributor Award earlier this year so I must have done something right. It’s a great honour to be recognised in this way and I am very thankful to Microsoft for it.

2012 Community Goals

• Publish more content. Not sure on the media for this yet, I’m in talks with marketing to produce more SQL webcasts so watch this space…
• Work permitting I’m planning on hosting 6 User Group meetings this year and hopefully attending some of the others around the UK. Unfortunately I can’t commit to a number as it really does depend on where I am in the country on that particular day!
• In my new role I have the opportunity to speak to number of users across EMEA, it’s really satisfying to help them with issues and pass on best practices to make their lives easier so this goal is to differentiate between community and professional development better and to try and make a positive difference in and expand the SQL community.

 

Professional Development

I love SQL Server and try to learn something new each day, to help me achieve this I have set myself the following goals.

• Take and pass 70-433 and 70-451 exams (SQL Server 2008 Development track).
  Eventually I want to take the MCM exams and these are the pre-requisites that I have yet to take.
• Take and pass a Windows OS or networking exam.
  The idea behind this is that it will provide me with more peripheral knowledge which will enhance my DBA troubleshooting skills.
• Blog more, I have a stack of ideas but have not written them up.
  This year I intend to become more organised with my blogging and blog at least once or twice a week. The increase in blogging should help with my goal of publishing a couple of articles.
• Overhaul my website, the look and feel hasn’t quite turned out as I had hoped, so a redesign with a few new features is just what the doctor ordered.
• Read 2 technical and 2 professional development books cover to cover.
  At the moment I tend just to read the relevant sections of books and am no doubt missing some real gems.
• Make better goals!
  One of the books I am interested in reading is “Getting Things Done” by David Allen which was recommended by Brent Ozar Blog|Twitter in his blog article Goals? Where we’re going, we don’t need goals. The idea of having goals at different levels which keep you on track does sound appealing and is something I will be looking into.

Changing jobs really hammered the plans I had for personal development in a number of areas as I found myself studying other areas that I had not expected.

• SQL Exams, I took and passed 70-433 you can read up on the resources I used to pass this exam here. Study time for the 70-451 exam has been limited so this will be a goal for 2012.
• Periphery exam goals were dropped.
• Blog more, I seem to have blogged less than last year. Definitely an area for improvement.
• As you can see my blog hasn’t been overhauled yet, but I have found someone who’s quite good at that kind of thing and is willing to lend a hand.
• Book reading, tick.

2012 Professional Development Goals

• Make time to study for and take the 70-451 exam.
• Make a big dent in the MCM reading list. This means reading it not printing it out, sticking it up and throwing things at it (that wouldn’t be professional at all).
• Learn more about the Windows OS, especially Windows Core and Virtualisation.
• Blog more to reinforce study material as well as overhauling it.

I still have personal goals, but this probably isn’t the right place for them so I shall refrain from posting about that area of my life. This is a SQL blog after all.

Happy New Year,

Rich

I’d seen various #meme tweets on twitter but it wasn’t until I saw this blog by John Sansom (Blog|Twitter) that I had peeked through the curtains at the party going on across the street. Ironically I had planned to write a post on this very topic because of something I saw online last night, but more about that later. My reason’s for starting a blog are scarily similar to that of John’s in fact it turns out that we even read some of the same resources on how to blog about SQL Server. To summarise John’s post Brent Ozar (Blog|Twitter) wrote some really inspiring content about blogging with lots of information on how to start you’re own blog, which I followed with great interest eventually leading to this very site.

For those of you that don’t know what #meme is you can read about it here where the invitation from Jason Strate (Blog|Twitter) for #Meme15 started. In this post Jason asks two questions:

1. Why did you start blogging?
2. Why do you currently blog?

Hopefully the first paragraph answers question 1, but why do I currently blog?

They say a picture says a thousand words, in order to save me typing lots and wasting valuable coffee drinking time here’s my picture:

Hopefully this answers why I currently blog, it’s simply to help others. This comment was left on a post by Pinal Dave which can be found here. There’s some awesome content on his site and one of the most prolific bloggers I have come across.

The take away from this has to be if you like what I’m writing tell you’re friends, if you don’t blame Brent

It’s that time of the month again, no not time, T-SQL time! If you have not heard of T-SQL Tuesday before it was set up by Adam Machanic (Blog|Twitter) and you can read all about it here: T-SQL Tuesday. This months host is Allen White who asked the question “What T-SQL tricks do you use today to make your job easier?” I’m guessing that people will be going mad with DMV’s so I’m going to go down a slightly different route. I must admit I haven’t used it recently due to changing jobs which has meant a change of duties, but it’s just too good not to share.

At some point in your career you’ll be asked by somebody “so just how big is the database?” At this point you have a couple of choices, you can look at the properties of the database or you could even use the system stored procedure sp_spaceused. The problem I found with this sproc was it returned data in two result sets making storing the data more complicated than it actually needs to be, as you can see from the image below.

I decided to find out if there was an easier way to collate the data for all databases on an instance and indeed there was. The following script uses the internals of sp_spaceused which I have tweaked to return a single result set and is called from within the most documented of undocumented commands sp_msforeachdb the results are written to a temporary table. A good way to use this script is to schedule it within a job to write to a permanent table that can then be used to monitor database growth.

/*
Script written by Richard Douglas
HTTP://SQL.RichardDouglas.co.uk

Script will insert data about the sizes of all databases on the instance into a predetermined table.
This can be used as a snapshot or made into a job to chart the growth of databases over time.

Please note that the DATA_COMPRESSION functionality was introduced in SQL 2008.
If you're running on SQL Server 2005 comment out the use of DATA_COMPRESSION WHEN creating storage table.
*/

USE TempDB
GO

/*
Create a table to store results.
*/
IF NOT EXISTS (SELECT 1
                FROM sys.Objects
                WHERE Name like '#DatabaseGrowth%'
                AND OBJECT_SCHEMA_NAME(object_id) = 'dbo'
                )
BEGIN
    CREATE TABLE [dbo].[#DatabaseGrowth]
    (
         [Database_Name] [char](128) NOT NULL
        ,[Database_Size_MB] DECIMAL(15,2) NOT NULL
        ,[Unallocated_Space_MB] DECIMAL(15,2) NOT NULL
        ,[Reserved_MB] DECIMAL(15,2) NOT NULL
        ,[Data_MB] BIGINT NOT NULL
        ,[Index_Size_MB] BIGINT NULL
        ,[Unused_MB] BIGINT NOT NULL
        ,[DateTimeStamp] DATETIME NOT NULL DEFAULT GETDATE()
    ) ON [PRIMARY]
    WITH (DATA_COMPRESSION = PAGE)
END

TRUNCATE TABLE [dbo].[#DatabaseGrowth]

/*
Insert the data into storage table
*/
INSERT INTO [dbo].[#DatabaseGrowth]
([Database_Name]
,[Database_Size_MB]
,[Unallocated_Space_MB]
,[Reserved_MB]
,[Data_MB]
,[Index_Size_MB]
,[Unused_MB])
EXEC sp_MSforeachdb
'DECLARE
    @pages    BIGINT            -- Working variable for size calc.
    ,@dbname SYSNAME
    ,@dbsize BIGINT
    ,@logsize BIGINT
    ,@reservedpages  BIGINT
    ,@usedpages  BIGINT
    ,@rowCount BIGINT

SET NOCOUNT ON

    SELECT
         @dbsize = SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 = 0 THEN Size ELSE 0 END))
        ,@logsize = SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 <> 0 THEN Size ELSE 0 END))
        FROM [?].dbo.sysfiles

    SELECT
         @reservedpages = SUM(a.total_pages)
        ,@usedpages = SUM(a.used_pages)
        ,@pages = SUM(
                CASE
                    /* XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size" */
                    WHEN it.internal_type IN (202,204,211,212,213,214,215,216) THEN 0
                    WHEN a.type <> 1 THEN a.used_pages
                    WHEN p.index_id < 2 THEN a.data_pages
                    ELSE 0
                END
            )
    FROM [?].sys.partitions p
    JOIN [?].sys.allocation_units a ON p.partition_id = a.container_id
    LEFT JOIN [?].sys.internal_tables it on p.object_id = it.object_id

    /* unallocated space could not be negative */
    SELECT
         database_name = ''?''
        ,database_size = (@dbsize + @logsize) * 8192 / 1048576
        ,''unallocated space'' = LTRIM(STR((CASE WHEN @dbsize >= @reservedpages THEN (CONVERT (dec (15,2),@dbsize) - CONVERT (DEC (15,2),@reservedpages)) * 8192 / 1048576 ELSE 0 END),15,2))
        ,reserved = LTRIM(STR((@reservedpages * 8192 / 1024.)/1024,15,0))
        ,data = LTRIM(STR((@pages * 8192 / 1024.)/1024,15,0))
        ,index_size = LTRIM(STR(((@usedpages - @pages) * 8192 / 1024.)/1024,15,0))
        ,unused = LTRIM(STR(((@reservedpages - @usedpages) * 8192 / 1024.)/1024,15,0))'

/*Show data*/

SELECT
 Database_Name
,Database_Size_MB
,Unallocated_Space_MB
,Reserved_MB
,Data_MB
,Index_Size_MB
,Unused_MB
,DateTimeStamp
FROM [dbo].[#DatabaseGrowth]

 

Thanks for hosting Allen.

It only seems like a couple of weeks ago since I wrote a post about new years resolutions which you can read here, scary thing is it’s almost time to think up some new ones. Whilst some of my goals for the year have slipped I’m glad to say that I have exceeded on a number of my community goals.

One of my goals was to go to at least one user group a quarter on top of going to SQLBits. I’m glad to say that I’m on target with this having attended groups at Southampton, Surrey and Maidenhead with another planned event in Maidenhead on 6th December which brings me nicely up to my quota. Another one of my community goals was to present some Nuggets at a User Group. Thanks go to Mark Pryce-Maher leader of the Southampton UG who allowed me to come down and do a few minutes I also gave the same nugget during the Lightening Talk Sessions at SQLBits in October and another nugget at Microsoft’s London office as part of SQL Relay – tick, another one completed.

All good so far, so what’s all this about exceeding expectations I hear you cry? Since joining Quest 3 months ago I have been able to secure a meeting room that I can use at Quest from which to start my very own SQL User Group which I blogged about here. If you are interested in coming along to an event or even speaking at one then please register your interest at http://www.sqlserverfaq.com/default.aspx?EVTCTAG=Maidenhead. Starting the Maidenhead group has given me a real insight into how much effort the organisers of events are putting in around the globe, I’ve been very lucky to have the support of the other UK SQL Server User Group leaders and we’re all working hard to organise not only our own events but also the second National SQL Relay event in May 2012. I’ll post more about it when we’re allowed to speak more publicly.

As the blog title is called “Speaking Engagements” I should really list what is coming up in the next few months. Last week saw the first of four SQL Server webcasts that I am presenting for Quest software which should be up on the On Demand section of the Quest website any day now. You can find details about the other webcasts and user groups I’m presenting at in the near future below:

• 15/11/2011 – Live Webcast: Harness the Power of SQL Optimisation – Register here.
• 22/11/2011 – Live Webcast: Deliver, Manage and Control Optimal Database Performance  – Register here.
• 29/11/2011 – Live Webcast: Don’t get Burnt by these Disaster Recovery Myths – Register here.
• 12/01/2012 – Surrey User Group (Plan Guides) – Register here.
• 07/02/2012 – Maidenhead User Group (Session TBC) – Register here.

The UK SQL community has never had so many events in such a short space of time. Last week there were three days of SQLBits and this week all 15 SQL User Groups have held a free event with each meeting having a session presented by a SQL Server MVP. This figure includes three user groups (Cambridge, Essex and Maidenhead) who had their very first meetings this week, what a baptism of fire!

We’re now on the final straight and we enter Microsoft’s Cardinal Place offices in London where T-SQL guru Itzik Ben-Gan will be speaking. We have several prizes to give away by our sponsors including a Pass 2010 Summit DVD! Not only that but all the user group leaders are going to do a talk on “The A – Z of SQL”

I’d like to thank the whole team who have made this possible. As the leader of a new group it was all finalised by the time I had joined, but I do know how much hard work they had put in.

It promises to be a great evening of SQL Server, I hope to see some of you there!

Feedback from the second leg of SQLRelay was very positive indeed. The attendees from my Maidenhead group were very impressed and glad they had a user group near them. Navigate to http://www.sqlserverfaq.com to see if there is a user group near you, if there isn’t we can help you start one!

You can see the lead up to all the action to all six meetings being held tonight by following the twitter hash tag #SQLRelay:
Cambridge
Cardiff
Edinburgh  
Essex 
Exeter
Southampton

There are still places available and to help you make up your mind here are the agendas:

Cambridgeshire SQL Server User Group’s inaugural meeting

Come along to see the birth! For expanded details of this event please visit sqlcambs.org.uk

Agenda

18:00 – 18:30 – Introduction and Networking

18:30 – 19:30 – Jen Stirrup - iPad and PowerPivot – Mobile Business Intelligence in Action 

19:30 – 19:50 – Break
More time to Network and Food

19:50 – 20:50 – Allan Mitchell – Data Quality for Business – What Denali brings

20:50 – 21:00 – Feedback and Close

Location: Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ

http://www.red-gate.com/our-company/about/directions

Cardiff User Group Meeting with Satya SK Jayanty(MVP)

As part of the SQLRelay events the South Wales SQL Server UG welcomes Satya K Jayanty (MVP)

Satya Shyam K Jayanty is an Independent consultant working as a Principal Architect and Director for D Bi A Solutions Limited based in the UK.

He has a Master’s degree in Economics and Commerce from Osmania University, Hyderabad (India) and an MBA – Systems from the University of the West of England.

Satya started his career in the IT industry in 1992, and he has been working with SQL Server (from version 4.2) for more than 15 years and has been an accomplished Microsoft SQL Server MVP for the last six years.

Satya is a frequent speaker and SME volunteer at Microsoft Tech-Ed, SQL PASS, SQL Bits, Scottish Area SQL Server user group. He maintains a blog at sqlserver-qa.net and sql-server-performance.com.

SQL Server upgrade 2000/2005 to 2008/R2: best practices

Upgrade of the data platform is no simple task, but using best practices you can accomplish the mission!

We will go through the SQL Server 2008 upgrade process, how to identify the issues to resolve and notes from the field. The session will cover the essential phases and steps involved in upgrading SQL Server 2000, SQL Server 2005, and SQL Server 2008 to SQL Server 2008 R2 by using best practices and available resources.

We will cover the complete upgrade cycle, including the preparation tasks, upgrade tasks, and post-upgrade tasks. This session will also cover notes on upgrading a stand-alone instance; upgrading a clustered instance; upgrading instances involved in mirroring, log shipping, and replication; feature-specific considerations; and recommended tools for a successful upgrade.

SQL Server Integration Services

SSIS has many diverse uses on ETL, take from simple data import/export to advanced techniques on quality/transformation and Integration Services.

In this talk we will go through the best practices which have been developed/implemented/followed over the years. Important areas covered include performance, connectivity, logging and troubleshooting, and deployment.

 

General Information

On arrival please sign in at the reception desk and wait in the foyer to be taken up to the room.

One of the main objectives of the user groups is to introduce new speakers, and here at Cardiff we’re always looking for to support anyone who has something interesting to share with the SQL Community. We’ll give you all the encouragement and support you need. We’re really flexible so if you only want to speak for a shorter amount of time, say 15 mins then this is perfectly fine. If you’ve be thinking about this, then please get in touch with myself, Adam Morton.

You can keep up to date with the South Wales SQL Server User Group on twitter at twitter.com/SQLServerUG follow this user to hear the latest event news.

Scottish Area SQL Server User Group with Andrew Fryer from Microsoft and Martin Bell(MVP)

We will also have the usual food and prize draws!
Agenda
18:00 – 18:30 – Introduction and Networking

18:30 – 19:30 – BI features in Denali
Denali is the next release of SQL Server. There are some great new features lined up to be delivered and Andrew will look into what enhancements to the BI stack are on the release schedule, such as BISM and Project Crescent

Andrew Fryer
Andrew is a technical evangelist at Microsoft UK. This means that he spends time explaining the future to IT Professionals who do data management and data centre administration. As there are just under a million IT Professionals in the UK, then to reach all of these requires him spending a lot of time on line: updating his blog, doing webcasts, and the usual social media channels. However Andrew loves going to user groups and community events to swap stories and help people get the most out of the Microsoft technologies.

19:30 – 19:50 – Break
More time to Network and Food
19:50 – 20:50 – What’s new for developers in Denali
The next version of SQL Server is shaping up to be a huge release and is going to have some great new features for developers. This include changes to the SQL Server Developer Tools codename Juneau. This session will look at this exciting new release with demos of features available in CTP3.
Martin Bell
Martin is that suave and sophisticated SQL Server User Group leader who runs the SQL Server User Groups in Edinburgh and Leeds. On top of that he is one of the organisers of the biggest SQL Server conference in Europe – SQLBits. This takes place twice a year at different locations in the UK.
20:50 – 21:00 – Feedback, Prize Draw and Close

Location:

Microsoft Office at Waverley Gate

http://www.microsoft.com/uk/about/map-edinburgh.mspx

Registration will be necessary as I have to submit registrations to Microsoft on Thursday morning.

Essex SQL Server User Group’s inaugural meeting with Dave Ballantyne and Chris Testa-O’Neill (MVP)

Come along to see the birth!

Agenda

18:00 – 18:30 – Introduction and Networking

18:30 – 19:30 – Dave Ballantyne

Cursors are evil, and shouldn’t be used

That’s frequent advice given on forums when asking about performance problems. Its correct advice, but how do you go about removing cursors and what do you replace them with? That’s the tricky part! By deconstructing a cursor based routine, Dave will demonstrate a number of TSQL techniques and how they can be applied to generate a new routine with significantly less overhead.

Dave Ballantyne is a freelance SQLServer database developer/designer and has been working in the IT field for over 20 years. Tuning and optimizing SQLServer processes is his particular talent. Dave regularly contributes to online forums and is a regular speaker at UK events such as SQL Bits and user groups. He also is founder of the Kent .NET/SqlServer user group.

19:30 – 19:50 – Break
More time to Network and Food

19:50 – 20:50 – Chris Testa-O’Neill

We have control! – Controlling Resources in SQL Server

Since SQL Server 2008, you can control environments using Policy based management and with the Resource Governor. This session will demonstrate the implementation and workings of Policy based management and with the Resource Governor and provide you compelling reasons why these tools can be used to enforce standards and control SQL Server hardware in your environments

Chris Testa-O’Neill is a SQL Server Microsoft Most Valuable Professional (MVP) and a Senior Consultant at Coeo specialising in SQL Server Business Intelligence. He is also a technical author, working on courses for SQL Server 2008 R2 with Microsoft Learning and advising on the SQL Server Denali curriculum. Chris runs the Manchester SQL Server User Group and is an organiser for SQLBits. Chris has spoken at a range of events including user groups in the UK and Adelaide, Australia, SQLBits, the MCT Conference and this year’s SQLPASS. Chris is a Microsoft Certified Trainer (MCT), MCDBA, MCTS and MCITP in SQL Server.

20:50 – 21:00 – Feedback and Close

Location: Xchanging, Endeavour Drive, Basildon, SS14 3WF
http://www.xchanging.com/Locations/UK.html

SQLSouthWest User Group Meeting with Tony Rogerson (MVP)

Start : 17:00 (for 17:30 start)

SQLSouthWest User Group Meeting with Tony Rogerson (MVP)

Tony will talk about High Availability in SQL Denali, he will also cover backgrounder HA topics so it will be relevant to other editions too, but all demos will be in SQL Denali and concentrating on the new features. In his second session of the evening he will talk about what SSD’s mean to database professionals and the game changer that they are creating.

http://sqlsouthwest.co.uk/meetings.htm

Southampton – Justin Langford presenting on Denali and Nicholas Colebourn presenting Designing tables for scale.

 

Network, learn, ask a question, meet other folk, get fed – these are all things that happen at user group events. These events are a really great opportunity to socialise in an informal learning experience.

Remember to tell your friends and the people you work with; make sure you register as soon as you can.

 

6.00pm – 6.30pm Registration and networking
Meet and greet. (Eat biscuits)
6.30pm – 6.40pm  Introduction.

6.40pm – 6.50pm Lightning Speakers

6.50pm – 7.40pm Nicholas Colebourn – Designing tables for scale – data typing, data compression and table partitioning with Nick Coleburn
Good database design is the foundation of building robust, scalable database systems. Optimising your table design can make or break a database. In this session Nick will cover data typing, utilising SQL Server data compression and table partitioning, and how optimising the design of these technologies can substantially reduce disk I/O, memory usage and improve overall system performance.

7.40pm – 8.10pm SQL Supper

Eat Pizza and talk about everything SQL Server.

8.10 pm – 9.00pm Justin Langford talking about the latest release of SQL Server (Denali)

Directions:

Please use the entrance on Alba Road.  The signs should point you in the right direction.

The relay so far:

SQLRelay: The First Leg

SQL Relay: The Second Leg

Join in tomorrow as we prepare to handover for the sprint down the final straight!

This post is brought to you by the letter “K”. Those of you that have been following my blog and tweets recently will know all about the SQLRelay event that is happening in the UK this week. Tonight is the second of a four night series of events culminating on the 6th October in London where none other then Itzik Ben-Gan will be headlining. There will also be a session by all of the UK User Group leaders who are doing an A – Z of SQL Server where I volunteered to take stage for the letter – you guessed it – “K”.

So what do Key Lookups have to do with joins which is the basis of this T-SQL Tuesday? Well, according to BOL “Key Lookup is always accompanied by a Nested Loops operator” and as we know this is the physical join that the optimiser chooses to perform the logical join written in the query.

Let’s run a query that will involve a key lookup to see if this is true, but before we do I’ll ask you to run this script which will set up the environment as everybody plays about with AdventureWorks. If you don’t have the AdventureWorks database you can download it from Codeplex.

USE AdventureWorks;
go

/* Clear up session */
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
SET NOCOUNT ON;
GO

/*Remove Demo Indices if they exist*/
-------------------------------------
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]') AND name = N'IX_SalesOrderHeader_CustomerID_Demo')
DROP INDEX [IX_SalesOrderHeader_CustomerID_Demo] ON [Sales].[SalesOrderHeader] WITH ( ONLINE = OFF )
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]') AND name = N'IX_SalesOrderHeader_CustomerID_DueDate_Demo')
DROP INDEX [IX_SalesOrderHeader_CustomerID_DueDate_Demo] ON [Sales].[SalesOrderHeader] WITH ( ONLINE = OFF )
GO
-------------------------------------

/* Ensure correct indices are in place*/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]') AND name = N'IX_SalesOrderHeader_CustomerID')
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID
    ON Sales.SalesOrderHeader (CustomerID ASC)
go

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]') AND name = N'IX_SalesOrderHeader_CustomerID_Demo')
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID_Demo
    ON Sales.SalesOrderHeader (CustomerID ASC)
    INCLUDE (DueDate)
go

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]') AND name = N'IX_SalesOrderHeader_CustomerID_DueDate_Demo')
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID_DueDate_Demo
    ON Sales.SalesOrderHeader (CustomerID ASC, DueDate ASC)
go

Now we have built are indices we can look to see how they affect our query plans.

The first query I am going to run will cause the optimiser to choose to do a key lookup as the index being used does not cover all of the attributes being returned. Remember to turn on “Show actual execution plan” before running the query.

SET STATISTICS IO ON;
GO

PRINT 'Example 1 - Key Lookup';
SELECT
    DueDate,
    COUNT(*) [Qty]
FROM Sales.SalesOrderHeader WITH (INDEX = IX_SalesOrderHeader_CustomerID)
WHERE CustomerID = 11091
GROUP BY DueDate
ORDER BY DueDate;
go

When you look at the query plan you can see that it has indeed used a Nested Loop operator. Note how expensive the key lookup operator was in this batch. It took up 85% of the cost of the batch!

As you can see from the properties of the key lookup below the base table was being accessed to return the DueDate attribute.

If we were to have an index that contained both the CustomerID and DueDate attributes we would be able to negate the need to perform this key lookup which would in turn produce a more efficient query. Let’s test this by running the following code:

PRINT 'Example 1 - Key Lookup';
SELECT
    DueDate,
    COUNT(*) [Qty]
FROM Sales.SalesOrderHeader WITH (INDEX = IX_SalesOrderHeader_CustomerID)
WHERE CustomerID = 11091
GROUP BY DueDate
ORDER BY DueDate;
go

--Resolve Key Lookup with covering index
/*
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]') AND name = N'IX_SalesOrderHeader_CustomerID_Demo')
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID_Demo
    ON Sales.SalesOrderHeader (CustomerID ASC)
    INCLUDE (DueDate)
*/    

PRINT CHAR(10);
PRINT 'Example 2 - Key Lookup resolved';
SELECT
    DueDate,
    COUNT(*) [Qty]
FROM Sales.SalesOrderHeader WITH (INDEX = IX_SalesOrderHeader_CustomerID_Demo)
WHERE CustomerID = 11091
GROUP BY DueDate
ORDER BY DueDate;
go

With the two queries run in the same batch we can see how much more efficient the second query is, the reason for this is that it is no longer having to retrieve data from the base table. The index has satisfied the needs of the query.

So you can see just how much more efficient this is take a look at the messages tab which has recorded the amount of IO needed to return the resultset. I’m sure you’ll agree that the difference is staggering. This is just a contrived example imagine scaling this query out to 1 million rows and think of the overhead that is being saved.

The attentive people reading this will no doubt be wondering why there were three indices created at the beginning of the script. The reason for this is that there is still an expensive operator in our second query plan, if we change our index we can remove this bottleneck too.

SET STATISTICS IO ON;
GO

PRINT 'Example 1 - Key Lookup';
SELECT
    DueDate,
    COUNT(*) [Qty]
FROM Sales.SalesOrderHeader WITH (INDEX = IX_SalesOrderHeader_CustomerID)
WHERE CustomerID = 11091
GROUP BY DueDate
ORDER BY DueDate;
go

--Resolve Key Lookup with covering index
/*
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]') AND name = N'IX_SalesOrderHeader_CustomerID_Demo')
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID_Demo
    ON Sales.SalesOrderHeader (CustomerID ASC)
    INCLUDE (DueDate)
*/    

PRINT CHAR(10);
PRINT 'Example 2 - Key Lookup resolved';
SELECT
    DueDate,
    COUNT(*) [Qty]
FROM Sales.SalesOrderHeader WITH (INDEX = IX_SalesOrderHeader_CustomerID_Demo)
WHERE CustomerID = 11091
GROUP BY DueDate
ORDER BY DueDate;
go
----------------------------------------

--Create alternative Index
/*
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]') AND name = N'IX_SalesOrderHeader_CustomerID_DueDate_Demo')
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID_DueDate_Demo
    ON Sales.SalesOrderHeader (CustomerID ASC, DueDate ASC)
*/    

PRINT CHAR(10);
PRINT 'Example 3 - Alternative Index';
SELECT
    DueDate,
    COUNT(*) [Qty]
FROM Sales.SalesOrderHeader WITH (INDEX = IX_SalesOrderHeader_CustomerID_DueDate_Demo)
WHERE CustomerID = 11091
GROUP BY DueDate
ORDER BY DueDate;
go

By creating a composite index on Customer and DueDate in that order we are able to satisfy the ORDER BY requirement meaning that SQL Server does not have to perform this operation, with this index we are using just 3% of the cost of all three queries combined. From this you can really see the benefit of thinking carefully about the indices that you implement. There are some great savings to be had by creating indices, but you must also think about the overhead that they can cause to write performance.

One final word of warning, if you use SET STATISTICS IO ON as you’re main way of performance tuning you will be interested to see that there was no difference in the IO consumed between the second and third queries as the sort was performed in memory in this example:

For those of you that follow athletics and have seen how the 4 * 100metre relay works you may appreciate that I decided not to go with the title “SQL Relay: Going round the bend” for yesterdays blog post SQLRelay: The First Leg. I might still go for it tomorrow depending on how tonight turns out!

Last night was a great success in terms of the first leg with four user group events Birmingham, Farnham – Surrey, Kent and Manchester. Tonight we look forward to a further five events in Bristol , Hatfield , Leeds , London & Maidenhead. If you haven’t registered yet then please do so using the links above. Not only will you enjoy some great speakers but there are some fantastic prizes to be won too. The London event is full but there are still places available at the other four venues.

Here’s the agenda for tonight’s meetings:

SQLServerClub User group evening with Tony Rogerson(MVP)

We’re delighted to be part of the SQL Relay event across the UK. A series of 16 SQL Server community events at the start of October, culminating in an event at Microsoft’s Cardinal Place venue in London with an internationally recognised author and speaker.

MVP Tony Rogerson returns to Bristol for his second visit to talk about High Availability in SQL Denali and to kick off Bristol’s event in the UK SQL Relay!

Tony is a freelance SQL Server specialist and BI consultant. He founded the UK SQL Server User Group in 1997, SQLBlogcasts.com in 2003 and was a founding member of Developer Developer Developer and SQLBits community conferences.

Visit http://www.sqlserverclub.co.uk for further event details.

Location
Room with a View
4th Floor
Bush House
72 Prince Street, BRISTOL BS1 4QD

Time
Doors open 6pm, meeting begins 6.30pm. We will break at 7.30pm for beer and pizza. After final questions and feeback, we’ll head to the pub at 9pm.

SQLHerts User group evening with Chris Webb(MVP)

As part of the SQLRelay events

Visit http://www.sqlherts.org.uk/ for event details.

October Leeds UG meeting meeting with Jen Stirrup(MVP) and Andrew Fryer from Microsoft

Network, learn, ask a question, meet other folk, get fed – these are all things that happen at user group events. These events are a really great opportunity to socialise in an informal learning experience – if you want your own exposure then come and do a 1 – 5 minute nugget in front of your peers.
Remember to tell your friends and the people you work with; make sure you register as soon as you can.
5.30 – 6:00pm Registration and networking
Meet and greet.

6:00 – 6:30pm Round Table discussion and Nuggets – ALL
Take stock and get the latest news in the SQL Server field. This is also a great opportunity to ask any burning questions you have, may be a problem at work.
It’s also a great opportunity to share your knowledge and gain exposure in the industry by giving a short 1 – 6 minute “nugget” demo/tip.
6.30 - 7:30pm  iPad and PowerPivot – Mobile Business Intelligence in Action
Mobile Business Intelligence allows end users to access data to support their decisions, irrespective of time and place. The Apple iPad is the definitive tablet of the decade, and is game-changer as a credible business tool. PowerPivot is a game-changer because it places data, and data structures, back in the hands of business users. Apple’s cutting edge usability, along with PowerPivot’s accessibility, can be combined to enhance productivity for mobile business users. Together, these technologies can support Mobile Business Intelligence as an essential tool for end users who need data ‘on the go’.
What you will learn from this session:
- Capabilities of Microsoft PowerPivot in terms of scalability and empowering access to data
- Using PowerPivot as source for data, viewing the visualisation via an Apple iPad
- Specific considerations for visualising data to serve Mobile Business Intelligence, supported by Tableau
Come and see Mobile Business Intelligence in action using the Apple iPad, with Microsoft PowerPivot as a data source.v
Jen Stirrup
Newly awarded SQL Server MVP Jen Stirrup is an MCP, with a Bachelors degree in Psychology and two Masters in Artificial Intelligence and Cognitive Science. Currently, Jen is a freelance Business Intelligence consultant, delivering solutions to global clients. Jen is a veteran SQL Server Developer with over 12 years SQL experience in delivering end-to-end Business Intelligence solution. Jen blogs for SQLServerPedia as well.
7.30 – 7.50pm BREAK: Light refreshments
More time to network and ask questions…

7:50 – 8.50pm – BI features in Denali
Denali is the next release of SQL Server. There are some great new features lined up to be delivered and Andrew will look into what enhancements to the BI stack are on the release schedule, such as BISM and Project Crescent

Andrew Fryer
Andrew is a technical evangelist at Microsoft UK. This means that he spends time explaining the future to IT Professionals who do data management and data centre administration. As there are just under a million IT Professionals in the UK, then to reach all of these requires him spending a lot of time on line: updating his blog, doing webcasts, and the usual social media channels. However Andrew loves going to user groups and community events to swap stories and help people get the most out of the Microsoft technologies.

8:50 – 9:00pm Close
9:00pm Down to the pub!

 

 

London User-Group with Chris Testa-O’Neill(MVP) and Neil Hambly

**** SOLD OUT **** Please Note: Our SQL Relay final event also in London on the Thursday has a few spaces left before it becomes full

** Due to number of attendees we have moved the event to a larger meeting room @ Fenchurch Street (Libra Room) .. **

Nearest Tube : Fenchurch Street

Network, learn, ask a question, meet other folk, get fed – these are all things that happen at user group events. These events are a really great opportunity to socialise in an informal learning experience – if you want your own exposure then come and do a 1 – 5 minute nugget in front of your peers.
Remember to tell your friends and the people you work with; make sure you register as soon as you can.
6.00 – 6:20pm Registration and networking
Meet and greet, take a seat

Avanade (Accenture)
1 Plantation Place
30 Fenchurch Street
London
EC3M 3BD

Registration 18:00 – 18:20
Event Ends ~ 21:00
Also Feel free to join us for a post event drink @ a nearby pub
Please also bring some Photo ID for security checks
Avanade Contacts: Charles Feddersen & Igal Greenberg
SQL UG Contacts: Neil Hambly (London UG Leader) & Chris Testa O’Neill (MVP)
We look forward to seeing you there, as we have some great prizes from our vendor sponsors to give-away along and our fantastic sessions lined up

Exact Session order will be decide on the night – Neil Hambly will be your host and will enusre your have a terrific evening {and he will buy the Pizza’s}

Chris Testa O’Neill (SQL MVP) I need my reports……….Yesterday!

As greater collaboration between business stakeholders and BI professional occur in implementing reports, this session will focus on how best to facilitate the reports created by installing, configuring and administering Reporting Service.

The reports may look good and provide the users with the information required. However, ensuring that the reports are delivered in a timely manner can be an important measure of the success of even entire BI projects. This session will focus on tips and techniques that you can use tune and optimise reports to ensure that report consumers can get the reports they need, when they need it!Steve Wright (SQL Sentry) Demo of their FREE community Tool Plan Explorer
I leave the details for Steve to give you
Igal GreenBerg (Avanade) Saying no to no-SQL
We are living in momentous times for the database world.

A forty year old reign of amazingly complicated Relational database engines is coming to an apparent end. Internet scale is the battle cry of the new social networks, and the only databases that can cope are the bastard children of SQL databases the no-SQL databases.

These new databases are remarkably simple and difficult to query but can scale to dizzying heights of Petabyte, Exabyte & Zettabyte with apparent ease.Who needs a state of the art database server when you have ten thousands CPUs & un-imaginable amount of RAM & disks at your service is the word on the street.

Internet scale applications cannot run on sophisticated database servers is another. We are living in a time of many interesting common sense statements.The truth is that the same businesses that are producing data in internet social networks need to analyse it with tools that actually work.

Loading data from social networks to SQL Server is a great way to bring your data to life. During this session we would go over a few examples of doing that the reasons & benefits for getting it done.

Maidenhead SQL Server User Group’s inaugural meeting

Come along to see the birth!

Agenda

18:00 – 18:30 – Introduction and Networking

18:30 – 19:30 – Chirag Roy presenting “Troubleshooting Transactional Replication in the Wild!”

19:30 – 19:50 – Break
More time to Network and Food

19:50 – 20:50 – Justin Langford talking about the latest release of SQL Server (Denali)

20:50 – 21:00 – Feedback and Close

Location: Maidenhead, Berkshire