Wish you were here

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”

#SQLThursday is here

Struggling to source quality free content on SQL Server? Finding it difficult to fill the void between #TSQL2sDay and Un-SQL Friday?

Join me for the first EMEA SQL Server webcast of the year Deploying SQL Server Tips & Tricks  on Thursday 19th January 2012!

Starting this month I’m proud to announce that I will be hosting the all new #SQLThursday. Quest has provided free community SQL Server webcasts for many years in the U.S. (You can find dates for both future events and watch previously recorded events at the Experts Perspective). Starting Thursday, January 19th, watch live webcasts at a time specifically for you, attendees in the EMEA region (10AM GMT).

As before, my good friend and colleague Kevin Kline (Blog | Twitter) hosts a webcast in the States on the first and third Thursday of every month, and now on the second Thursday of the month I host one in EMEA.

Session details are currently provisional, but I am excited that many excellent community members have agreed to take part in the series. Get all the details for the EMEA webcasts here.

Choosing the right professional

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.

2011 – The dissection

 

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

Why I blog #Meme15

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:

image

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 :)

T-SQL Tuesday #025 – How to view the size of all your databases – #TSQL2sDay

T-SQLLogo

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.

image

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.

Speaking Engagements

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.

Writing queries without data

Sometimes it’s not possible to have the data you need in order to create the most efficient queries possible. This may be because you don’t have a machine with enough storage capacity, it might even be on a laptop so there is also the security aspect. So how do you address the issue of writing queries with no data to work with?

Luckily there are a couple of ways that this problem can be overcome.

  1. Use a data generator.
    This sounds great in theory, however the data you will then be querying against will bare no resemblance to the data you will be querying against in production (hopefully a dev/uat/pre-production server first!). The reason for this is that the statistics will be completely different, the same problem occurs when you use obfuscated data. If only there were a way you could query against the statistics themselves, oh wait, there is!
  2. Export the production database schema and it’s statistics.
    You may not have access to do this in your live environment, so ask your friendly DBA to do it for you. If you don’t have any third party analysis tools it might be a good idea to do this once a month and keep copies so you can see how queries change over time.

 

In this example I will export the schema for all database objects of the AdventureWorks database with the exception of extended properties and foreign keys. Foreign keys may cause you an issue here so it might be best to create them as a separate step at the end. I do recommend that you do add the foreign keys in if you are going to be using this methodology as constraints will be checked during insert and update statements as well as giving the optimizer extra options in certain select scenarios.

Open up SSMS and right click on the database you wish to export the statistics for, select “Tasks” and then “Generate Scripts…”:

image

Depending on your setting this may launch a wizard with the following welcome page. If this is displayed simply click Next:

image

In this example I am choosing to export data from the AdventureWorks database and have checked the option to script all objects:

image

The key part of the whole script is changing the “Script statistics” option to be “Script Statistics and histograms” as you can see this produces a warning, but in a database the size of AdventureWorks it really took no extra time at all.

image

The next two screens are pretty self explanatory, select your destination be it a new query or a file. Then review the selections you have made thus far.

In my example I chose to create a new database called AdventureWorks_Nodata and ran the script in the context of this new database to create a clone on AdventureWorks with no data but up to date statistics.

Once this is done your server will think that each table actually contains data, if you run the Disk Usage Top Tables Report on your new database you will see that SQL Server believes there are records and these records take up physical space:

TopTables

Brilliant, we’re now in a position to start writing and running some queries! Well not quite, remember we didn’t actually export any data other than the metadata. This means that to do any testing using this database you will need to be looking at the estimated execution plans. Estimated execution plans do have their own issues of course, but that’s a blog post for another time.

Let’s now write a simple query and view the estimated plan it creates form both our AdventureWorks and new database, as you can see I have taken the liberty of pasting the properties together into one screenshot for you so you can see in one image that the statistics are indeed the same:

image

As you can see the estimated query plan is identical, and so it should because the statistics are identical!

If we look at the actual query plan for the same query we can see that the estimated number of records is the same, but unsurprisingly the number of rows returned is not. Now we know what the statistics are we could use various performance tuning tricks to tune this query – all without the need for data.

Top 5 Third Party Quotes

Unless you’ve worked in a vacuum the likelyhood is that you either are a third company or you have many third companies with you, possibly both. Some are really really good and you can learn a lot from talking to them and working things out together, others have staff who should never have been allowed to go near a keyboard period or speak to a customer. I.T. can be extremely stressful and bizarrely it’s these people that can make your week. With this in mind I’d like to nominate my favourite 5 third party quotes of my career so far in reverse order to brighten up your day:

  • Email from third party: We’re delighted to inform you that we received your server yesterday, our software has been installed and configured on the two drives which have been configured as RAID 10 which means mirrored and striped.
    My reply: That’s really impressive, would you mind telling me how you managed to mirror AND stripe on only two physical disks?!?!
    I never received a response.

  • Third Party: We’re concerned that you are not backing up your databases.
    Me: I am backing them up.
    Third Party: You’re not.
    Me: I’ll double check 99.9% sure they are though, not had any failure notification come through. Yup just checked MSDB everything looks fine.
    Third party: I tell you you’re not! I dialled in yesterday upon X’s request on ticket Y and saw you had no maintenance plans!!!
    Me: Can I just put you on hold for a few seconds please…. Guy’s you’ll never believe this, this guy thinks you can only back up databases via maintenance plans….

  • Me: I’m not convinced this design is scalable, you have lots of redundant data here.
    Third Party: Normalization went out with the dinosaurs.

  • Me: I’m just reading this maintenance schedule, you say we have to abide by to keep your application optimal. I’m all for proactive maintenance but this doesn’t seem right can I skip some of it.
    Third Party: No, that would invalidate your support agreement with us.
    Me: But you want me to autoshrink all your databases EVERY night.
    Third Party: Yup, keeping them small makes them more optimal.
    Me: That’s not true, all you’ll do is fragment all the data. Have you not read Paul Randal’s blog?
    Third Party: Never heard of him, does he work for your company?
    Me: I wish.

  • Me: X, I’ve looked at this database and you have 250 tables all with triggers on and they all have the same logical flaw.
    Third Party: It’s not our fault, SQL Server comes with triggers built in!

I have paraphrased in places to protect the guilty, but there are many many more where these came from which I might store up for another post at another time if it proves popular. Hope you enjoyed reading the conversations as much as I did having them!

SQLRelay: The Final Sprint

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!