SQL Centre of Excellence

Thanks to everyone who made it to the first in the series of the SQL MasterClass events. We had over 100 people sign up, and judging from the questions –lot of people in Ireland looking at virtualisation and sql server.

Here is the decks for the event so you can show your co-workers or virtualisation dude when they start talking about memory ballooning or throwing your VM’s on the same disks as your SQL data files when the SAN only has two controllers!

In addition we discussed a few resources to help with SQL performance counters. the best link I have found on the internet is from my fellow MCM Jimmy May. Great guy – especially on performance matters. his blog is here with a link to a mega cool spread sheet.


Major Changes in MCM program

by Bob Duffy 13. November 2010 06:26

The changes in the top tier MCM program are now public and blogged about by Paul Randal


First the cost is way down at just 500 dollars for the exam and 2,000 dollars for the lab. secondly no need to travel to Redmond for the 3 week training (It used to be five weeks!).

I could name five to six people in Ireland who would be great candidates for the SQL MCM programme, of course I don’t think anyone can pass without some serious preparation.

These free videos are a great way to gauge the standard of the MCM. if you can watch them all and say “I already knew all that”, then your ready Winking smile


I’m currently watching Brent Ozars virtualisation web cast  to check we haven’t missed any tricks or tips for own consolidation and virtualisation Masterclass seminar on 24th November.


Its widely know that a best practise for space management for more important SQL data and log files is to ensure that growth events are handles manually so that there is no outage while the growth events happens. Of course most people still leave auto grow on in case the growth event happens while the DBA is asleep,  on holiday or sacked.

Paul Randal has a good survey with notes on database autogrow here


As Paul points out with SCOM versions before  6.1.314.36 if Auto grow was enabled the management pack did not alert when log or data files filled up, which was always a bit of a joke as it seems that the SCOM SQL MP designers did not realise that its not the running out of disk space we want alerting on its the growth event itself which is the performance risk. The running out of disk space is not a performance risk (its a more serious availability risk)

Anyways, Paul did raise this at an MCM class and lo and behold good news, in the new version SQL MP for SCOM now alerts if auto grow is enabled. It looks like they really went to town on the little feature by independently tracking space for files and file groups too.

Bad news is reading the blurb, it looks like the SQL SCOM MP has still missed the whole point and has decided to only alert if the data or log file is going to fill up the ENTIRE disk, not the file. Ohh dear, what a shame as it looks like a lot of effort went into the coding of this feature.

Here is the blurb from the new SQL Management Pack:

No Auto Grow

For a file that has no auto grow, available space would be the difference between the initial size for a file and the used space.

Auto Grow Enabled

With auto grow enabled, the available disk space is included as part of the over available space. In addition to the difference between the file size and the used space, the available space for files with auto grow enabled would be the minimum of either the difference between the max size and the file size and the free space left on disk.

Low available space for database files that have auto grow could mean that the file is approaching the limits of the hosting logical drive. For files with auto grow enabled with a max size, low available space could also mean that the file is approaching the max size specified for a file.

The available space calculations also take into account that the file can also fail to grow if the growth amount of the file is greater than the amount left on disk and that the file cannot grow if the difference between the max size and the current file size is less than the growth amount. In these situations, available free space left on disk is not included as part of the available space because we cannot grow any longer.


More bad news. The implementation of the feature adds some FOURTEEN rules and rollups to EACH database. On our one test server the health explorer view for a SQL Server  is many times slower to load. Think about it, fifty user databases with 14 monitors and rollups per database = 700 rules and rollups to query and load on the GUI per server on the health explore, just for one little rule. If only fifty rules were implemented in this  fashion we would have 35,000 rules and rollups on the health explorer screen for our one server. This doesn't sound too scalable at all to me.


Example SCOM health explorer (above) with  new space tracking feature and all the rules and rollups it creates. Pity that it:

a) Still doesn’t alert on growth events.

b) Causes the Health Explorer to load much slower

In partnership with Microsoft we are bringing a series of advanced SQL Server training to Ireland.  These are individually planned seminars aimed at level 300-400. Classes will be either led by our local SQL Server Microsoft Certified Master/Architect and MVP (Bob Duffy) or well-known industry experts and MVP speakers such as Chris Webb.

This is a community project (non profit). The first session will be free and for subsequent sessions there will be nominal charge of 100 euro for individually booked seminars or only 250 euro to attend the entire programme of classes.

This goal of this training is to help serious SQL professionals in Ireland with professional development with only a commitment of half a day every two months. Places are limited to about fifty. 

Full event details are below

· Class 1 - SQL Consolidation and Virtualization. Wed 24th Nov 2010

· Class 2 – PowerPivot Fundamentals. Thursday 20th Jan 2011 

· Class 3 – SSIS Design Deep Dive. March 2011

· Class 4 - SQL Scalability. May 2011

· Class 5 – SQL Tuning for Dot.Net Applications. July 2011

For more information or to register you can contact events@prodata.ie or visit http://www.prodata.ie/Events/SqlMasterClass2011/

Page List

Page List