SQL Centre of Excellence

I was lucky enough to take and pass the MCM lab exam this week, which as anyone who is in the depths of it knows, qualifies you as a Microsoft Certified Master in SQL Server. They’ve really lowered the bar letting me join the club Smile


There are 101 great blogs about taking the MCM, the experience itself, ranging from the SQL Ranger days, to the public on sites in Redmond, up to now, when you can take it via Prometric, and remote proctor. So why am I writing about this?

Panic stations

Well, I found myself in a position, like a lot of others now, where there was a countdown timer on my opportunities to take and pass the MCM. Microsoft announced last month that they will be retiring the program, with the last bookings for the lab exam being taken on the 17th of December. And like a lot of people, I was unsure if I was really ready to complete the program, and if my ability level justified the cost of taking the lab exam. At $2,000 per attempt, you want to be certain you’re up for the challenge.


Unlike a lot of people, I work for a very supportive company, who really understand and appreciate the value of this certification, so thanks to their support financially and in allowing me time off, my personal risk was slightly lowered. But if you’re funding it yourself, here are some notes from my experience that may help you make up your mind.


The first thing I would say is, the knowledge exam in no way mirrors the difficulty of the lab exam. Even on my passing attempt, I finished the last scenario literally as the exam expired. It’s bloody tough. You may notice I said my passing attempt, I passed this second try, my first attempt was an epic failure. I took a month between retries, and luckily that month of hard study bridged the gap for me.


So back to the original premise of this post, with less than two months to go, should you go for it yourself? If you meet any of the following criteria, then I would say yes.


  • You have passed the knowledge exam and have time to study for the lab
    • Do it. You’ve one shot left to take it, prove it to yourself
    • By time to prep I mean really get your hands dirty in features you’re not comfortable with
  • You have taken the lab unsuccessfully once and have study time
    • Bridge the gap, you know your weak areas from the first attempt, work hard to reduce them.
    • You’ve invested in it once, don’t back down now

Quantifying “enough” time to study

This is an “It Depends”. If you work with every aspect of the SQL Engine, then you may need less time than I did. But if you’ve only ever used the wizards on some features, or only used some features in a “First Look” example, then you may need to put in a bit more work.

I put in between 1 and 2 hours per evening, and another 2 – 3 hours per weekend day, over a period of 3 1/2 weeks. This time was spent setting up all of the features, and really trying to learn them inside out and backwards.

It’s worth noting that this was after the first attempt. I completely misjudged the first attempt, and it was only after getting spanked the first time, I knew what I was in for. I wouldn’t put it down to laziness, or apathy, but without knowing what was required, it was very hard to put together a proper study plan. Which leads me to…

In my opinion, while it seems wasteful, if you really want to accelerate the process, take the lab. Even if you don’t feel ready, if you have the available funds to invest, just take it. Best case, you pass. Worst case, you will see where you fall down, get a feel for the level of the questions, and can act accordingly. While trying to plan for it without having seen it, study plans can be very aimless, and you may lose a lot of time.

It seems wasteful, but with the time constraints in place, I would highly recommend this strategy if possible. I made more progress in 3 weeks between tries, than I did in the months leading up to the first attempt. Knowing the gap between where you are and where you need to be really lets you accelerate your progress.

So should you go for it?


I know the challenge seems great, for those of you who have taken the lab before, you may not want to do that again, and feel like that again, which is the position I was in. I almost quit. But I decided to give it one more try. And I can honestly say that the studying, the disappointments, the hard work, was completely worth it to see that email from Bob Taylor hitting my inbox telling me I had achieved my goal.


Also the excuse to drink champagne is always a welcome one!


If you decide to go for it, I wish you the very best of luck, and I look forward to picking your brains on the MCM distribution list in the future. If you’ve got this far, you’re clever enough to see it all the way through. Have faith and go for it!


A while back we were sizing some storage to take a monster of a SharePoint instance, and it’s fairly common knowledge that SharePoint can eat some resources. But the question is, how much does it actually need? How do you size the storage appropriately? Do you just start it up and run away screaming?

Well, you can now reap the benefits of me having too much time on my hands at the time, and use the ACME SharePoint IO Calculator available here. This will help you calculate your IO requirements for the SQL Server Databases (Not the app tier) in a SharePoint deployment. These numbers are all based on the official Microsoft Whitepapers, and links to the resources are included in the tool.

Great! How do I use it?


Glad you asked! There are 2 main sections. The first is:


The explanations in the column are quite straight forward, depending on the level of performance you want, fill in the relevant number. In the example I’m going for completely optimal performance, but you may want to decrease this if you’ll have a whole pile of archive data which is rarely accessed.

The Second part is:


Quite simply you fill in the # of databases and Database Size GB fields. Database size should be the estimated max size of the content database. Last time I checked it was recommended to go no larger than 200GB, but your SharePoint team should be able to give you indications as to how they will be managing that. The Estimated Overall I/O’s field will update itself based on your numbers.

Another note, the Crawl and Property databases are fixed at that size unless otherwise stated by the SharePoint team, so it’s usually just the content databases that you need to focus on.

Total Data IOPs is the number you need to give to your storage guys in order to achieve maximum happiness.


Is it accurate?


So far, so good. It’s all based on MS best practices, but seeking them out and condensing them was a solid weeks work. However in sizing a 50TB SharePoint deployment, this little spread sheet didn’t lead us astray.

Let me know if you have any feedback or questions on this, but it’s proved useful internally when working on SharePoint deployments, so hopefully someone else can get some use out of it too!

I received some great questions from the Pass Performance Virtual Chapter (http://performance.sqlpass.org/) session the other day, and the below post will try to answer as many of them as possible. Where similar questions occur, I’ve paraphrased slightly to cover them off in one answer. Also if I answered it online at the time and it was just a clarification, I haven’t included it.

If there’s anything missing that you’d like me to add, or something that isn’t contained in the slides, feel free to drop me a line on twitter or in the comments below!

Thanks again to everyone who took the time out to attend!

How do I move data to a new filegroup?


To move data to a new filegroup, it’s thankfully quite simple. In the event of a Clustered or Non Clustered Index, you just drop the index, and re-create it on the new file group, similar to the screenshot below:


If it’s a heap you’re moving, it’s a little more counter-intuitive. The simplest way in my mind is to create a Clustered Index on the table, which is built on the new filegroup, and then drop it. The heap will not revert to its old location and will stay on the new filegroup.

Another alternative is to create a table with the same structure on the new filegroup, migrate the data, and re-name the table, but for me that introduces a lot more steps for no additional gain. You still have to move a lot of data, and it carries additional risk.

How do I re-balance across multiple files in a filegroup


This one is a bit trickier, as SQL doesn’t automatically re-balance data across files if you just add in a new one. In order to get the benefit of multiple files, you have to meet the conditions for the Round Robin algorithm (Equally sized, equally full).

In general I would look to move my table/index to a filegroup which already has multiple empty files, that way it will re-balance automatically. Otherwise, the closest way I can think of to re-balance is after you add in your new file, do a full index/table rebuild on all objects in that file group. That should start to bring the fullness to a better balance.


How do you measure throughput and I/O request size?


I’ve been using Perfmon, and specifically the Avg Disk Bytes/Transfer counter in Logical Disk, pointed at the disk partition I want to monitor. I’m using logical disk as I only care about reads on that particular partition, and don’t really care about the underlying physical disk.

The method I am using to simulate a table scan as fast as possible is running something like the query below. The reason I do a checksum_agg over say a BCP or a select *, is that I don’t want any other factors impacting my testing. If the disk you are writing to in a BCP can’t keep up, that’ll slow down your read requests. And the same with a select *, if the client can’t consume the information, it will also impact the reads.


Thank you to everyone for attending the Pass VPC presentation tonight (http://performance.sqlpass.org/), it is much appreciated that you chose to spend an hour listening to me ramble! I hope you got some good from it.

As discussed, here is a link (http://www.mediafire.com/download/3o02j9w33tvqloc/Getting_the_most_from_your_SAN_%E2%80%93_Deck.pptx) to the slides from the presentation, and keep an eye out for the Q+A over the next few days.

Any questions you may have forgotten to ask, or think of afterwards, please do let me know in the comments or on twitter, always happy to talk SQL!

Thanks again,


(Script can be downloaded here)


TempDB is the work horse of most SQL Servers, if a query is doing something wrong, or just being a resource hog, chances are it’s TempDB picking up the slack. It’s a safe bet that the majority of queries running on your system are using TempDB in some way. (Good technet article hereon how TempDB is used)

So it pays to give your TempDB some TLC. Starting out as a DBA I found it pretty confusing as to what the “Best Practices” actually were when it came to TempDB configuration, as there are many differing opinions. So here is a script I’ve put together with what I’m going to call my “Recommended Practices”, rather than “Best Practices”. In my experience structuring your layout to get green lights on each of these checks has yielded the best performance and stability, but your mileage may vary!

What am I checking, and why?

The script performs 6 checks, and I’ll break them down below:


1. If you have multiple CPU’s, have you got multiple TempDB data files?

On a multi core system, it is recommended to have multiple TempDB data files. This is to reduce the contention of threads requiring TempDB space trying to hit a single file. The official recommendation from Microsoft is to have 1 TempDB file for each CPU/Core in the server.

Now this is massive overkill, particularly on large multi core boxes, so a ratio of 1 File for every 4 cores is a good place to start, potentially rising to a 1:2 ratio if you still have some hotspots. Paul Randal wrote an excellent blog on this already, so I won’t repeat the effort.

This check measures the ratio of CPU’s to TempDB data files, and reports back accordingly


2. If you have multiple TempDB Data files, are they all sized equally?

If you have multiple files, you want to ensure they are all sized equally to ensure the Round Robin effect is in play, which gives you that performance boost. Again Paul discusses this in the previous blog link, so I won’t duplicate on the reasoning.

This check ensures that if multiple files exist, they are all equally sized, and if not, it has a loud whinge.

3. Do you have TF1118 enabled?

This is a contentious one, as it’s not necessarily a best practice, but I’ve found it really helps concurrency in any situation I’ve had issues in. This checks if TF1118, which disables mixed extents (Over to Paul Randal for this one again blog), and in my experience enhances concurrency.

One important note here, this disables mixed extents server wide, not just for TempDB. This isn’t a worry in most cases as disk space isn’t as expensive any more, but just be aware of this.

4. If you have multiple data files, do you have TF1117 enabled?

This trace flag ensures that if one data file experiences a growth event, then all data files grow equally. This means that file growth on a data file doesn’t effectively take it out of the Round Robin pool, as all files must be sized equally to be considered.

You should be pre-allocating anyway, but this trace flag will ensure you don’t take a performance hit if one or more of your files grows.

Again this is server wide, so if you have any user DB’s with multiple files in a Filegroup, they will exhibit the same behaviour

5. Has TempDB data file experienced any auto growth events

You should be pre-allocating space for these files where possible, but this is a check to make sure you got it right. If you have no auto growth events, then you are sized right for your system. If you do, it may be worth taking a look at how much it has grown, and re-evaluate your sizing.

6. Are the TempDB data files on their own storage

TempDB is generally the most I/O intensive database in an instance, and it can be very heavy random I/O. While a lot of workloads are now on SAN’s, it still makes sense to separate TempDB from all of your other files. This is from a management perspective within the OS, but also if you are using tiered storage, such as the EMC VNX, the partition holding TempDB can be placed on the higher tier if it’s out on its own, without having to move all of your user DB’s too.

It’s a recommended practice from me, but if you’re having no issues and you are within your I/O thresholds, then this is by no means a must do.


So there you have it, some recommended practices, and a way to give your TempDB a quick health check. If anyone has any feedback, questions or comments, or rebuttals, on any of this, as always I’d love to hear it.


Indexing “Strategy”

by Stephen Archbold 16. March 2013 20:47


Welcome to my first Prodata blog! First, allow me to introduce myself, my name is Stephen Archbold and I joined the Prodata team around 6 months ago. Having spent 5+ years in the same role as a SQL DBA, I was lucky enough to nab a role working as a SQL consultant with Prodata in order to expand my horizons. While some of the expansion has been terrifying, I’m delighted I made the move.

The Prodata blog is known for its excellent technical content and as a reliable source of information thanks to the hard work of Bob and Fintan (And sometimes Carmel when we make her!), and I hope I can keep up that tradition.

My previous blog http://simplesql.blogspot.com was geared towards “Reluctant DBA’s” and beginners, and trying to get people starting out more comfortable with the SQL product. I still feel that the gap between “Beginner” and “Comfortable” can be quite hard to bridge, so I want to introduce some of the more advanced concepts, in what I hope is an easy to follow and structured approach.

“Indexing” Versus “Indexing Strategy”

With that, I wanted to talk about a subject which has been cropping up more and more in the field. I will assume that most people know what indexes are, and why they’re useful (If not, hop over here, then hop back! http://simplesql.blogspot.ie/2011/11/why-are-indexes-good-for-your-server.html).

A pretty common indexing methodology is to play “Fix the broken query”. Find a query which is dying, give it a good index, watch it revive. And there’s not necessarily anything wrong with that. But what happens when over a 6 month period, you’ve address two poor queries per month, and they all happen to use the same table? You’ve applied the best index for your queries individually, but what impact is that having across the board. Is it better to have 12 individual “Best” indexes for 12 individual queries, or have 2 “Good enough” indexes for a workload of 12 queries?

This post is not intended to be a scare tactic against applying indexes, they are super, super important in performance tuning, and in most cases are your best friend for a poor performing query. This is just to give you an idea of some of the follow on steps you should consider after applying indexes to make sure they are effective enough to warrant the overhead they carry.

A phrase I’ve been hearing a lot when discussing indexes is “I know they can add some over head, but is it that much?”. Aside from the obvious additional overhead on maintenance (Rebuilds, Re-orgs), what about the day to day work of getting new data into your table? Well, let’s find out.

Overhead on DML

Let’s take a 3 column table, with a clustered index, and do a very simple insert.

(Please note this doesn’t include any of the Allocation Unit work (GAM, SGAM, IAM etc.), and won’t, that’s another topic for another day!)


2 Logical reads performed when updating a clustered index. Not so bad.

Now let’s add a non-clustered index on two of the columns and try that insert again.


6 logical reads, a 3X increase on the clustered index alone. Traversing the non-clustered index to perform the update added some additional IO’s.

Now let’s add a second Non Clustered Index on only one column this time and try the insert again :


It had a smaller index to traverse, but still incurred two additional reads to perform the insert, on top of the other Non Clustered Index we added previously.

This is a very simple demonstration, but I hope it gets the point across, that the “strategy” part of “Index strategy” is very important, as you need to make sure that what you’re gaining in read performance, you’re not losing on write performance.

And also, just because the index you add is to service one query, doesn’t stop it impacting on every insert, update and delete which occurs on the table.

Identify indexes who don’t justify their existence

This is very much “it depends” as to what quantifies justification, but the result of the below script should be a good guide. It calculates number of reads vs number of writes on the index, and if the ratio is write heavy, the index may not be the most efficient. I would advise looking for a ratio of at least 1:1 to justify keeping it, as anything below probably means you’re incurring write over head for little return on reads.

Important Note: This only tracks reads/writes since the last restart of the instance. If this doesn’t include a full business cycle, you may throw away an important month end index

This script is a slightly modified version of Brent Ozar’s (t|b) unused index script (Found over at http://sqlserverpedia.com/wiki/Find_Indexes_Not_In_Use). I’ve only dropped the “Drop Index” create statement so you have to think about what you’re doing :)

, indexname=i.name
, i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes = user_updates
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
WHEN s.user_updates < 1 THEN 100
ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
END AS reads_per_write
+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id
INNER JOIN sys.objects o on s.object_id = o.object_id
INNER JOIN sys.schemas c on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
ORDER BY reads

Identify potentially redundant indexes

Kimberly Tripp has already done extensive work in this area, and I would struggle to do it justice in the way she does, so I’ll give a link to her blog, and specifically sp_helpindex at the end.

What makes an index potentially redundant (Apart from never being used)? Take the following scenario:

You have two queries

· Select Col1 from MyTable

· Select Col1, Col2 from MyTable

You create two indexes :

· One on Col1

· One on Col1 and Col2

The first index is optimal for the first query, the second index is optimal for the second query. But both indexes share the “Col1” column. This comes back to “Best” vs “Good enough”, the second index cover both queries, and only requires one index update per write. If they are used equally, the first index could be considered redundant, as the second index will also service the first query.


Try the sp_helpindex query, and do some experimenting with your queries (on a test system!) and indexes, and try to strike the right balance between read performance and write performance.

Indexes are absolutely a good thing, and can drag a system/process back from the brink of falling over, to completing in milliseconds. But there’s no such thing as free money, so make sure you keep the “Strategy” portion in your mind when considering your Indexing Strategy.

Further reading

http://www.sqlskills.com/blogs/kimberly/removing-duplicate-indexes/ - Kimberly Tripp sp_helpindex

http://www.sqlskills.com/blogs/kimberly/category/sp_helpindex-rewrites/ - sp_helpindex blog archives

Page List

Page List