SQL Centre of Excellence

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:

Section1

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:

Section2

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:

image

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.

image

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,

Stephen

(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.

</Lecture>

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.

Stephen

Page List

Page List