Tag: syndicated

  • The Default Fillfactor for an Index

    I ran down the rabbit hole on transaction logs recently. I started with Paul Randal’s post over at the SQL Sentry blog on trimming the transaction log, then went to his video on log analysis. I also glanced at the posts on index cleaning and what index stats don’t tell you. What started out as a quick “what can I do to help transaction logs perform better” became a few hours of reading, executing code and thinking.

    However the post that caught my eye was Paul’s post on choosing an index fill factor. I’ve seen various notes on the fact that fill factor can matter for performance and maintenance, but I haven’t often seen someone give some good concrete rules on what you should choose. In a nutshell, here’s Paul’s advice:

    1. Don’t set a system wide fill factor with sp_configure
    2. Start with 70 for specific indexes that seem to experience lots of fragmentation

    I like this advice. It’s simple, and easy to start using, although the caveat to #2 is that you need to monitor and perhaps adjust the fill factor (up or down from 70) and possibly change your maintenance schedule. I might lean towards leaving my maintenance alone, especially with a script like the SQL Fool Index Defrag Script running and playing with fill factor to ensure I minimized page splits.

    There’s also the trade-off of requiring more space for your index (and maintenance) if you move to 70 from 100.

    I do think that changing the system wide level is a bad idea. If you aren’t sure what your system wide fill factor is, here’s a post on checking it.

  • Speaking at SQL Saturday #183 – Albuquerque

    sqlsat183I’ve been accepted to speak at SQL Saturday #183 in Albuquerque, NM on Feb 9, 2013. This is the first SQL Saturday in New Mexico, and I’m excited to drive down and meet the SQL Server pros down there.

    My brother used to live in Albuquerque, and I’ve been there 2 or 3 times, but not in a long time. I was debating if I should fly or drive, but with Taos on the way, I’m thinking to stop by on the trip down and spend a day skiing before the event. If anyone’s interested in a Friday or Sunday out, let me know. I’m not sure which day to hit the mountain.

    I’ll be delivering my Branding Yourself for a Dream Job presentation at the event, but there will be a lot of SQL Server learning to be had. Denny Cherry, Aaron Bertrand, Jason Brimhall, TJay Belt, Mike Fal, Ben Miller, Reeves Smith, Caros Bossy and more are coming from out of town to give, and receive a day of training.

    Yes, most of us come to learn as well as speak. I’m not sure how busy my day will be or if I’ll be giving a lunchtime talk for Red Gate, but I am hoping to see a few sessions. These are on my list.

    If you are within driving distance, consider coming out and enjoying a Saturday of SQL learning.

  • Checking the Instance Fill Factor

    I was reading a post from Paul Randal recently and noted that he recommends not changing the instance’s default fill factor. I agree, and you shouldn’t alter it. However if you aren’t sure if it’s been changed on any of your instances, here’s how to check it.

    I’ll show both the GUI and code ways to do this.

    SSMS

    In SSMS, right click your server instance in Object Explorer and choose properties.

    fillfactor1

    This will open a dialog that has a number of tabs. If you click the “Database Settings” item on the left, you will have various settings appear on the right side. One of these is the default fill factor.

    fillfactor2

    It should be zero or 100. Nothing else.

    T-SQL

    In T-SQL, we can also check by opening a query window and typing:

    EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE
    GO
    EXEC sys.sp_configure;
    

    This will return all the settings on the server. This is an advanced setting, so you don’t need to enable those.

    If you scroll through the list of items, you will find one that is labeled “fill factor (%)”

    fillfactor3

    Again, this should be zero or 100 in the config_value and the run_value columns. To change this, run this code:

    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    sp_configure 'fill factor', 100;
    GO
    RECONFIGURE;
    GO
    
  • Vote for my SQL Bits sessions

    “Rob? Tsk tsk tsk. That’s a naughty word. We never rob. We just sort of borrow a bit from those who can afford it. “

    – Robin Hood

    SQLBitsLogoSQL Bits XI is coming to Nottingham forest, home of Robin Hood and Sherwood Forest in Nottinghamshire. This is a fun three day event, with a day of pre-cons, a paid day of sessions, and a free day. It has taken place twice a year in the UK, though the organizers skipped this past fall.

    I’ve submitted a few sessions and I need your votes to get the chance to go back. I’d really like to since I really enjoyed the conference.

    You can vote for these by logging into the site and clicking on the description for the sessions. You get to vote for ten sessions, so pick the ones you are most interested. I’m hoping you pick a few of mine, but whether you vote for me or not, vote.

    I really enjoyed SQL Bits a couple years ago and I’m hoping to get the chance to go back this spring.