Come see me in Baton Rouge Aug 2nd!

I've been accepted for SQL Saturday Baton Rouge!

Yes, I am a little excited. I've wanted to become a speaker for some time. I'm excited to be speaking at my second SQL Saturday. My first session was at SQL Saturday Houston. I'll be honest, I chose the less used topic of fill factor. I honestly didn't expect it to be a session picked very often. So far I'm 2/2 on submissions. I'll be speaking at the last spot of the day,enough about that for now.

There are many other great speakers coming to SQL Saturday Baton Rouge! It's really hard to beat a free event with tons of free training and contacts you can make going forward. I honestly thing SQL Saturdays are what will keep MS SQL Server ahead in the game. I've not seen any other DB Software offer so much training and support for free or have near as strong of a community. Honestly, if you know of one... I'd love to know about it. 

Free Training?!

I've talked about a few free training sources before. SQL Saturdays, User Groups, website links I personally like, ideas on what I did as a junior DBA, Lists of many other resources and I even post scripts I find useful. You might even be able to talk your boss into a free lunch while getting some free training! 

What I'd like to see is some of you post something you've learned. I don't mean just here, I mean anywhere. What have you been learning recently? What are some subjects you wish you knew more about? Is there something you've been working with that you didn't find much information about? These can all lead to good presentations! Have you thought about speaking at your User Group? They don't bite... It's a great place to get started. 

Thank You!

Honestly, thank you all. I've been writing since November. From the people who've posted on my site, the e-mails I've received, and just the number of people who've visited the site... Thank you. This was a big motivation to start trying to present and I'm even working on a second presentation now. I encourage anyone who wants to speak to do so. 

Wasted Space Per Database For Fill Factor: Quick Tip

As I keep looking for answers on Fill Factor... I keep finding interesting Metrics to look into. Looking my own stuff, I've found 300GB of space. That could be huge. Just simple things to think about. My previous post explains some things I've learned about Fill Factors, pros vs cons. If you have time, what's your highest number? What's your lost space as a whole?

select db.name as DatabaseName, SUM(a.total_pages*8/1024)as SizeInMB, SUM(((a.total_pages*fill_factor/100)-a.total_pages)*8/1024)*(-1) as SpaceInMBLostToFillFactor
from sys.dm_db_index_usage_stats stats
inner join sys.tables as tbl on stats.object_id = tbl.object_id
inner join sys.schemas as sch on tbl.schema_id = sch.schema_id
inner join sys.databases as db on stats.database_id = db.database_id
inner join sys.indexes as idx on stats.object_id = idx.object_id
and stats.index_id = idx.index_id
inner join sys.partitions as part on stats.object_id = part.object_id
and stats.index_id = part.index_id
INNER JOIN sys.allocation_units a ON part.partition_id = a.container_id
where fill_factor != 0
and fill_factor != 100
and db.name = DB_NAME()
group by db.name

Fill Factor, Why Do You Taunt Me? Part 3

Let's look at something a bit more in depth. Let me start by saying, "Please don't arbitrarily change any default setting without understanding it fully, meaning how it's used, what it does, what impacts will exist, and how much it could hurt." Now! for something interesting.

First we need to create our table.

I create most of my tables in a DBA only Database. I've worked in places where you weren't allowed to create any Databases for any reason ever without 3 levels of permission, a signed letter with a blood seal and two recommendations from two separate Illuminati knights. OK, maybe it wasn't that bad... but it definitely felt like it. Place this where ever you want, just modify the scripts accordingly.

Now we need to fill out the table.

This will take some time. Get some coffee, find a new book, plant a tree. I'm hoping to find a faster way to get this same data, but as it stands... pulling this information off of a 5TB + database is a days event. The good thing is that this data will be good for quite some time.

Note: This is something I would suggest addressing on a yearly basis in a slow moving environment, or monthly in a volatile one.

Now for some fun. we need to query our table.

Note: I realize that a table has the ability to use up to 8096 bytes if you're using multiple records. This includes other overheads I did not calculate. 8060 was used to allow that 36 byte area to be used just for overhead.

You should only be concerned with the tables that you know to be active. I will add a way to track that as well in another post that will marry this concept with a true use concept. you may find one that looks a bit off.

if your max record size is 3908, you are only going to get 2 on a page. If you really want every page to be able to insert two records.... you need a fill factor of 51%. That's a good time to just be happy with page splits and let it go.

If you use the query built off AVG, remember that an average item is 50%. It'll catch... well half roughly. Take that into consideration.

Note: It could also help you identify if you have one extremely large record and a bunch of tiny ones. This could be something to investigate.

--select * from master.dbo.FillFactorStatistics

It's interesting to me at least.

Kendra Little has a wonderful post on fill factors.

There's a white paper on them by Ken Lassesen.

Karel Coenye has a Technet video with more detail.

Fill Factor, Why Do You Taunt Me? Part 2

So Friday we were discussing the idea of an idea. I think... or an idea of an idea's idea. What she said...

Let's answer the easy question. How far are your fill factors off from the default settings?

First we want to look at what our default setting actually is.

EXEC SP_CONFIGURE 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO;
EXEC SP_CONFIGURE

0 and 100 are the same thing. I strong advise that you do not use a global setting for fill factor other than 100%. It may take more work to address the indexes and tables on a case by case basis... but doing it right also has it's merits.

 I've got a little script that builds out a table for easy viewing. This is also a part of the larger automated fill factor project I'm working on.

The script can be found here.

Now we have a table showing us how all our fill factors look that are not default.

select DatabaseName, SchemaName, TableName, IndexName, fill_factor from [master].[dbo].[DBA_Reset_Fragmentation_Levels] order by DatabaseName, fill_factor asc

Now, depending on your environment, you could have zero results, or thousands.

This is as script I found some time ago, I do not know the author. If anyone knows who wrote this, I'd like to at least give the correct credit.

I will try and define better what constitutes an (user_update), but for now this still works for good information. you can see what table is taking the most updates. These may be better candidates for lower fill factors than others. The one clearly defined thing is that when the updates are near zero, why do you need a fill factor of 50%?

I know it's not spring yet, but maybe a little house cleaning is in order?

Fill Factor, Why Do You Taunt Me? Part 1

Like all the arbitrary numbers we hear in the SQL community such as the 30% fragmentation and 1000 pages. These come more as a best practice, not a rule. There are so many sites talking about this, it must be an interesting or highly misunderstood idea. I don't know how deep I can go, but over the next few weeks I plan on adding as much information to this as I can.

On highly updated tables, a lower fill factor number can be beneficial, but it's a dangerous game to play. If you have your pages at 70% capacity, that's 30% lost disk space, 30% lost memory space, and higher read costs when you retrieve data. SQL retrieves data one page at a time. That wasted space is also retrieved. It does help you update and insert data into those pages cleaner, but there's a point when it's not worth it. How often and how many updates do you do? Is your clustered index on an autoID? How big are your records for that table?

These all all questions you should be asking. There are many more as well. I would say as a rule of thumb, don't change the default fill factor. A server level fill factor impacts system databases, temp tables, and other databases besides the one you're working with.


My goals are to cover a few key points:

How do we find the fill factor of all our tables? (Or at least isolate those that are not on the default setting.)
What fill factor do we truly need? (if we need it at all)
What are some things to consider before working with our fill factor?

Hopefully I can cover this in more detail as well.

This will be updated some and referenced at the end. I'm adding in all things I've learned over the course of tracking down Fill Factors.

A page is 8192 Bytes.
The maximum column size is 8000.
The maximum record size is 8060.
The maximum record size for a table using sparse columns is 8018.
The maximum usable page size is 8096.
96 is reserved for headers.
Row_Overflow_Data -When you fill up a page and it needs to split a record across multiple pages.
Row_Overflow_Data uses a 24 Byte pointer.

Update 11/5/2013 10:43 CDT: Fill Factor only applies to the index's leaf pages.
Update 11/5/2013 13:46 CDT: User_Updates from dm_db_index_usage_stats does not differentiate bulk updates from single updates.

Knowing how the pages work greatly enhances how your fill factors work. There may be points that 94% will actually help just as much as 90%. This can save space and reduce read times.