Voice of the DBA

Tag: sql server

  • MCM Prep – VLF Allocations

    I had heard that you could have too many VLFs in your transaction log, and it could cause performance issues. Kimberly Tripp has a great blog on how to get better performance from your log, but until I was studying for the MCM, I didn’t realize you could have too few.

    The issue seems to be that if you allocate too much transaction log at once, you could end up with a few, very, very large (GB in size) VLFs, and despite regular log backups, you could not be clearing any VLFs inside the log until there are no active transactions in that VLF. And that could cause a performance slowdown as it clears that large VLF.

    How are VLFs allocated? Here’s the formula:

    • chunks less than 64MB = 4 VLFs
    • chunks of 64MB and less than 1GB = 8 VLFs
    • chunks of 1GB and larger = 16 VLFs

    This is from : http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx#ixzz19KysnYcK

    So if you need 50GB of log space, do as Kim recommends and get 512MB chunks by allocating 8GB at a time. You can do this contiguously, as in all the same day, but allocate 8GB, let it finish, then ALTER to add 8GB more, repeat until you reach the correct size.

  • Getting a Dedicated Admin Connection

    Did you know you can easily get a Dedicated Admin Connection (DAC) in SSMS? I didn’t assuming that I’d need to use a command line and SQLCMD. However while studying for the MCM, I learned that there’s an easy way.

    I tested this on a SQL Server 2008 instance, having a normal connection, as seen in the lower status bar:

    admin_connection2

    Right click in the query window, and select “Change Connection”. In the dialog, not add “Admin:” before the server name, as shown below.

    admin_connection

    When you connect, you’ll have an admin connection, which you can see in the status bar.

    admin_connection1 

    Pretty cool.

  • 64 Bit

    Today we have a reprint of an editorial from Nov 25, 2005 as Steve is on vacation.

    No news yet on SQL Server, but apparently the next version of Exchange will not have 32-bit support. The reason appears to be the improvements in disk I/O because of larger caches, so Microsoft if warning users to validate, test, and prepare for Windows 2003 64-bit.

    I guess is makes sense, but if the improvements are really good because of caching, why not push harder on SQL Server? I’d think that it would benefit greatly as well. The demos I’ve seen definitely show improvement in performance, but they aren’t really pushed.

    Maybe the reason is that so many SQL Servers are developer and personal editions on desktops and no one wants deployment issues moving between versions. I know there shouldn’t be any, but how many developers have told you there shouldn’t be any problems? After they’ve deployed something?

    I think SQL Server will see more and more 64-bit installations, but they’ll be the minority of installations. Not too many people are playing with it, at least not that I’ve heard. I’d love to get some 64-bit servers for SQLServerCentral.com and upgrade to 2005, 64-bit just to see how it runs. However the investment just for kicks isn’t worth it as we’re not stressing out SQL 2K installation at this time.

    However, if anyone has any pull with Dell, HP, or even Unisys, we’re looking for a site sponsor to supply a bit of hardware. I’ve got all servers from one vendor, but I’d definitely be looking to switch for the right offer

  • SQL Server 2005 SP4

    Last year I submitted a Connect item for SQL Server 2005 SP4. Quite a few people supported this item and voted for it, and Microsoft decided to build this last, final Service Pack for SQL Server 2005.

    No timeline was given, but last week someone noticed that my Connect item had been changed to “closed” status, which to me is an indication that the Service Pack is getting close to being finished. I have no idea when this might be released, but I am guessing since we just passed the five year anniversary of the release of SQL Server 2005, it will be soon. I am sure Microsoft would like to get this finished, and move more people out of SQL Server 2005 support and into supporting newer products.

    There are lots of people that run SQL Server 2005 instances. Actually I think there are lots of people still running SQL Server 2000 instances as well. These are mature products, and I understand that need to stop bug fixes for these products as time marches on. However I also think that we ought to finalize the products with a final rollup of the CUs and patches that is tested and deployable.

    Not everyone can, or wants to upgrade, and the customers that did pay for those products deserve to have a full lifecycle of support. Which can be up to ten years after the original release of the product.