SQL Centre of Excellence

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.


blog comments powered by Disqus

Page List

Page List