Category: Tips & Tricks

Chicago, CrossRealms, Microsoft, MS SQL, Tips & Tricks

Tips and Tricks with MS SQL (Part 5)

Dec 6, 2019 by Sam Taylor

Separate Your File Types

It’s too common and important of an occurrence to not mention the need for file separation in this series. If you’re running Microsoft SQL Server of any version, it’s important you separate your file types to different logical or physical locations. “Data” files, “Log” files, and “TEMPDB” files shouldn’t ever live in the same logical drive. This has a big impact on performance and makes troubleshooting issues much harder to isolate when it comes to finding read/write contention as a suspect.

It’s understandable, the quick need of a SQL Server pops up and you install a Development Edition or Express Edition in 10 minutes leaving file types to their default locations. However, once this system becomes a production server, you better know how to relocate these files to new locations or do it right the first time around. It’ll be easier earlier on rather than after the data grows and needs a bigger maintenance window to move.

To keep with Microsoft Best Practices, you can use a drive naming convention similar to what I’ve listed below to help remember where to place your files. If you’re fortunate enough to have physical drive separation, all the power to you. For most servers I see in this situation, it’s best to start with logical separation at a minimum to yield some powerful results.

Filetype Mapping:

– C:\ – System Databases (default MS SQL installation location)

– D:\ – Data Files

– L:\ – Log Files

– T:\ – TEMPDB Files

– B:\ – Backup Files (with redundancy of course…)

Any questions, comments, or feedback are appreciated! Leave a comment or send me an email to aturika@newtheme.jlizardo.com for any SQL Server questions you might have!

CrossRealms, Microsoft, MS SQL, Tips & Tricks

Tips and Tricks with MS SQL (Part 4)

Dec 6, 2019 by Sam Taylor

Don't Forget to Enable "IFI" on New Installations​

Instant File Initialization (IFI) is a simple feature with performance benefits often left behind on installations of SQL Server that have seen their share of upgrades or migrations. If it wasn’t available in previous versions of Windows Server or Microsoft SQL Server, there’s a good chance someone unfamiliar with its purpose didn’t enable it during an upgrade. Why risk enabling a new feature to a system that’s been stable and passed the test of time? During installations of SQL Server 2016 onwards, this presents itself as the “Grant Perform Volume Maintenance Task” checkbox SQL Server asks you to check on or leave off (1). It can be enabled in older SQL versions as well, though by different means.

The benefits of enabling this means being able to write data to disk faster. Without IFI enabled, anytime SQL Server needs to write to disk it first must zero out previously deleted files and reclaim any space on the disk that was once used. This happens anytime a new database is created, data or log files are added, database size is increased (including autogrowth events), and when restoring a database. Enabling the IFI feature can bypass this “overwriting the disk with zeros” process used in the Windows file initialization process. The resulting benefits to disk performance compound as data grows and especially when non-solid-state media is used.

An analogy to what’s happening here is when you’re formatting a USB thumb drive and being presented with “Perform a Quick Format” checkbox. This would be like enabling IFI where Windows basically just claims all the diskspace quickly and lets you go about your day. Without the Quick Format, Windows goes through and writes zeros to each sector of the drive (which also reveals bad sectors – but unrelated to SQL’s IFI usage) which takes much longer. It’s essentially writing enough to cover all available space, hence taking longer. You’ve probably noticed these differences in formatting speeds before. The performance benefit of Quick Format is like SQL Server with IFI enabled. It’s becomes more evident as the size of storage or data increases.

Note (1) : If you’re using a SQL Domain User Account as a Service Logon Account instead of the service account (NT Service\MSSQLSERVER) SQL Server defaults to, you’ll need to grant the account “Perform Volume Maintenance Tasks” separately under the “Local Policies”. Double check your SQL service account has this right granted to be safe. For instructions on granting permissions, you can follow Microsoft’s documentation here.

If you want to know other ways to enable IFI on your server without the re-installation SQL or want to know how to check if IFI is enabled, feel free to reach out.  Any questions, comments, or feedback are appreciated! Leave a comment or send me an email to aturika@newtheme.jlizardo.com for any SQL Server questions you might have!

CrossRealms, Microsoft, MS SQL, Tips & Tricks

Tips and Tricks with MS SQL (Part 3)

Dec 6, 2019 by Sam Taylor

Edit Change Database Auto-Growth from Percent-Based to Fixed-Size Growth​

  • Edit
 
 

In the ideal world all the Microsoft SQL Servers I came across would have their databases pre-grown to account for future growth and re-evaluate their needs periodically. Unfortunately, this is almost never the case. Instead, these databases rely on SQL’s autogrowth feature to expand their data files and log files as needed. The problem is the default is set to autogrow data files by 1MB(*) and log files by 10%(*).

Since this was such a big issue with performance, Microsoft made some changes in SQL Server 2016 onward, where the data files and log files will default to a growth of 64MB each. If your server is still using the 1MB autogrowth for data and 10% autogrowth for logs, consider using Microsoft’s new defaults and bump it up to at least 64MB.

Growing a data file by 1MB increments means the server must do extra work. If it needs to grow 100MB – it must send over 100 requests to the server to grow 1MB, add data, then ask the server to grow again and repeat. Imagine how bad this gets for databases growing by gigabytes a day! This is even worse if growing by a percentage. This means the server has to do some computing first before it can grow. Growing 10% of 100MB is easy to account for but as the log file grows it can quickly get out of hand and runaway bloating your storage system while adding CPU overhead as an extra kick in the rear!

The change is luckily very simple. Right-click one of the user databases using SQL Server Management Studio and select “Properties”. From there click on the “Files” page. Next expand the “…” button near the “ROWS” cell and change this to 64MB or greater (depending on how much room you have to work with and growth expected). Do the same for the “LOG” file type. That’s it! You’re done and gave your server some well needed breathing room!

Any questions, comments, or feedback are appreciated! Feel free to reach out to aturika@newtheme.jlizardo.com for any SQL Server questions you might have!

Chicago, CrossRealms, Microsoft, MS SQL, Tips & Tricks

Tips and Tricks with MS SQL (Part 2)

Dec 6, 2019 by Sam Taylor

Database Compatibility Levels Left Behind Post-Upgrades & Migrations

What’s common with almost every Microsoft SQL Server I come across that’s recently been upgraded or migrated to? The user database compatibility levels are still stuck in the past on older SQL versions. The compatibility level remains on version of SQL the database was created on. This could be several versions back or a mixed bag of databases, all on different versions. When Microsoft SQL is upgraded or databases are migrated to newer versions, the compatibility levels don’t update. It must be done manually. It’s important to update those databases to the most recent version to take advantage of all the newer version’s features. Good news is it’s very simple to change and only take a minute.

Changing the compatibility level upwards doesn’t really hold any risks unless there’s linked servers involved that run on much older versions of SQL. Even then, it’s usually relatively safe change. If you’re unsure, check with your DBA or reach out to me for questions. All you need to do is right-click the database is SQL Server Management Studio, select “Properties”, choose “Options”, and update the drop-down selector for “Compatibility Level” to your current version of SQL Server. It’s important you don’t forget to update these settings after migrating or upgrading to a newer version of MS SQL Server.

Any questions, comments, or feedback are appreciated! Feel free to reach out to aturika@newtheme.jlizardo.com for any SQL Server questions you might have! 

CrossRealms, Microsoft, MS SQL, Tips & Tricks

Tips and Tricks with MS SQL (Part 1)

Dec 6, 2019 by Sam Taylor

Change your Power Plan

By default, Windows chooses “Balanced” as the recommended Power Plan on a new Windows Server deployment. It’s an option you should change and one that’s most often overlooked in my experience. Production SQL servers usually aren’t being powered by mobile laptops with batteries, so we’ll need to use an option that gives SQL more breathing room. The goal is to make sure the server is always on-the-ready and not sacrificing processes or services for the sake of fairly minimal reduction in power consumption.

Instead of “Balanced”, choose the “High Performance” mode. Your SQL Server will thank you. This is easily done by going to the Control Panel, clicking on “Power Options” and picking the power plan more optimized to run SQL Server. Those that are savvy could easily update the changes to all their SQL servers at once by using a Group Policy.

Any questions, comments, or feedback are appreciated! Feel free to reach out to aturika@newtheme.jlizardo.com for any SQL Server questions you might have!