Posts

SQL Server Performance Tuning – Shrink Is A Four Letter Word

SQL Server Performance Tuning - Shrink Is A Four Letter WordOriginally published on DMMaxwell.WordPress.com.

SQL Server Performance Tuning – Shrink Is A Four Letter Word

Recently, I’ve been noticing some large-scale log growth on some of my developer SQL servers, which has been causing them to run out of disk space. Though shrinking files is almost universally a bad idea, there is a time and a place for doing so provided the ramifications of shrinking are understood. Needing to shrink a log file indicates that either we’re operating on more data than we expected, or operating inefficiently. Either way, it’s almost always the result of unplanned circumstances. Under such circumstances, you can’t always use the option you want to. Sometimes you have to do something regrettable.

When *NOT* to shrink:

  • When the growth happens in a Data file. Shrinking is going to rob you of free space required for index maintenance, not to mention fragmenting the hell out of your existing indexes, making them near useless.
  • When the growth is in TempDB. It can cause corruption. Either add TempDB files, or kill the transaction and recreate tempdb. You’ll need to restart SQL Server for that, and killing said transaction that big may cause a very long rollback. You’ve been warned.
  • As part of regular maintenance. If your log files are growing repeatedly, then they’re trying to get to the size they need to be in order to handle your workload. Why are you shrinking them? What do you have against them? They have dreams, too… Fix the workload, not the log files.

Alternatives to shrinking:

When to shrink. I can only think of two situations off the top of my head

  • Under an emergency where other workloads on the same disk are affected and there’s no additional disk elsewhere for another log file.
  • When a database is being archived / retired, and will be set to read-only. Even in that case, you wouldn’t just shrink the DB. See Retiring a Database.

Benefits to shrinking:

  • Free disk space.
  • That’s about it.

Caveats to shrinking:

  • Massive performance hit during re-growth, as all transactions must be suspended.
  • Massive performance hit due to file fragmentation on re-growth.
  • Massive performance hit due to internal fragmentation on shrink.
  • Massive perfor…. You get the idea.

So really, we want to avoid shrinking log files wherever possible. The best way to avoid shrinking is to properly size files to begin with. Make sure you have enough room for your transactions to run. If you have large transactions or volumes of data to work with, one good way to avoid file growth is to batch large transactions into smaller groups. i.e. Instead of inserting 5M rows, insert 100k rows at a time, 50 times. This can actually be faster than a single large insert, as you’re not stuck waiting for the log file to grow to accommodate 5M records.

Some growth may still occur since, let’s face it, we have a lot of data to work with. What we want to avoid is large, recurring growth. Remember that when the log file must grow, all transactions are suspended. If you are only growing slightly, that may not be a problem, but when you have 100GB of growth, that’s going to stop you in your tracks for the duration of the growth.

If you must shrink a log file due to uncontrolled or unexpected growth, you can use the SHRINKFILE command, but with one caveat – I would recommend shrinking the file by half instead of to its smallest size. This way, future growth may be avoided while queries are tuned to use less log space. For example, if a log were to grow to 40GB, after averaging a size of 2GB, I would shrink the log to 20GB, like this:

USE <DatabaseName>;
DBCC SHRINKFILE(<file ID>,<target size>);

Example:

USE BigLogDB;
DBCC SHRINKFILE(2,20000);

2 is the ID of the log file for most databases. You can use sp_helpfile on a database to check that. 20000 is the target size, which is always in MB. Shrinking requires sysadmin or diskadmin permissions, which some developers do have. I can also assist you with these tasks as needed. Another advantage to this is that it gives us an idea of how large the production log files will need to be to accommodate new SQL code.

Also, I would like to stress that shrinking is used as a temporary fix, not as a workaround, and *never* as part of regular maintenance. If you have a job on a server that is regularly shrinking log files, then you either need more disk space or smaller transactions. Regular shrinking is a sign that something bad is going on. Also, while shrinking can be considered on log files, it is almost never a good idea to shrink a data file. Avoid using SHRINKDATABASE.

Thanks for reading.
-David.

About the Author

Data Platform Consultant

David Maxwell

UpSearch Alum David Maxwell is a database strategist, community evangelist and public speaker. He is passionate about helping community and business leaders get the most out of SQL Server.

Since 2000, David has served to protect, unlock and optimize data's value within such diverse environments as healthcare providers, insurance companies, manufacturers and financial institutions.  David has the unique ability to zero in on a complex challenge quickly, and provide a long-term solution that fits both the business needs and budget.

Since 2012, David has maintained a SQL Server focused blog at https://dmmaxwell.wordpress.com/. He is a frequent presenter at local and regional events for the Professional Association for SQL Server (PASS) and and works with the Columbus, OH based PASS chapter.

In his free time, David is an avid musician who plays several instruments, as well as a lover of puzzle-based games.

Learn more about David Maxwell at https://upsearch.com/david-maxwell/.

SQL Server Health Check Series

This blog series will help you decide if UpSearch’s <span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/microsoft-sql-server-health-check/” target=”_blank”>SQL Server Health Check</a></span> is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.


<ol>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-introduction/” target=”_blank”>SQL Server Health Check Introduction</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-benefits/” target=”_blank”>SQL Server Health Check Benefits</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-1-collect-metrics/” target=”_blank”>SQL Server Health Check Process – Step 1</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-2-analyze-results/” target=”_blank”>SQL Server Health Check Process – Step 2</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-3-report-findings/” target=”_blank”>SQL Server Health Check Process – Step 3</a></span></li>

</ol>

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQL Server Performance Tuning – Dealing With Disk Space Issues

SQL Server Performance Tuning - Dealing With Disk Space IssuesOriginally published on DMMaxwell.WordPress.com.

On a typical day a few months ago, I received an automated warning in my inbox that a particular server was low on disk space. This came along with the requisite “panic” emails I usually get from other areas who are also on that notification list. I responded that I was looking into it in order to keep the email volume down. 🙂

I mapped the volume in question to my local machine and fired up SpaceSniffer to get a quick read on what was taking up all the space. It was a dedicated SQL Server data file drive and the usual suspects were taking up the lion’s share of the space. (SpaceSniffer is a fantastic tool, by the way. You should check it out, if you haven’t already.)

Now, when it comes to SQL Server performance tuning – dealing with disk space issues, there are three basic options:

1. Do nothing. This should always be considered as an approach, though you may rarely use this as a solution. Sometimes you really don’t need to do anything.

2. Reduce the space required. You can relocate files, clean up un-needed data, etc. You can even shrink files if you understand what the consequences would be and have a plan for dealing with that.

3. Increase the space available. In this case, we were on a SAN drive that had available space left, so we could grow the drive to make more space available.

Another quick check showed that most of the data files were over 90% used, and none of them would be able to grow unless more space was made available. That eliminated option 1. There were no other files on that drive to clean up or delete, i.e., no databases to drop, so we couldn’t reduce the amount of space required for option 2. That left us with option 3: Grow the drive. This was an online operation handled off-hours by our SAN admin, and was successful.

After resolving any issue, it’s a good idea to go back and do some root-cause analysis. What caused the disk space issue? One of the first things I do when taking on a new client or job is to set up a monitoring job on every server that keeps a few month’s worth of history on the database files. Using the sys.dm_io_virtual_file_stats DMV I take a snapshot of the disk activity for each data file, as well as it’s size on disk, every 15 minutes. This is then logged to a table in my administrative database on each server.

With the stats in place, I was able to put together a quick query to see which database had grown the most over the last few months. It turned out that one of the smaller databases had now become the second biggest database, all within the space of the last 8 weeks. Checking with the owner of the application, I found out a lot of new data had recently been imported, causing the database to grow by over 600%. After that, it didn’t take much more growth on the part of the other data files to push that disk over its threshold, and send an alert.

Here are the scripts I use, in case you’re interested.

  • DDL for FileIOStatsRaw table and vw_FileIOByHour view. – Created in the administrative DB. (In my case, [SQLAdmin].)
  • GetFileIOStats stored procedure – Run this as frequently as you want and output to the FileIOStatsRaw table. – Also created in the admin DB. Will keep 90 days by default, but call it how you want in an agent job.
  • Trend Query:
SELECT 
 DBName, 
 Yr, 
 Mo, 
 CalculatedGrowthInMBPerMonth = SUM(FileGrowthBytes) / 1024.0
FROM dbo.vw_FileIOByHour 
WHERE FileType = 'ROWS' 
GROUP BY DBName, Yr, Mo 
ORDER BY DBName, Yr, Mo;
GO

How do you monitor, measure and alert on disk space usage?

Thanks for reading.

-David.

About the Author

Data Platform Consultant

David Maxwell

UpSearch Alum David Maxwell is a database strategist, community evangelist and public speaker. He is passionate about helping community and business leaders get the most out of SQL Server.

Since 2000, David has served to protect, unlock and optimize data's value within such diverse environments as healthcare providers, insurance companies, manufacturers and financial institutions.  David has the unique ability to zero in on a complex challenge quickly, and provide a long-term solution that fits both the business needs and budget.

Since 2012, David has maintained a SQL Server focused blog at https://dmmaxwell.wordpress.com/. He is a frequent presenter at local and regional events for the Professional Association for SQL Server (PASS) and and works with the Columbus, OH based PASS chapter.

In his free time, David is an avid musician who plays several instruments, as well as a lover of puzzle-based games.

Learn more about David Maxwell at https://upsearch.com/david-maxwell/.

SQL Server Health Check Series

This blog series will help you decide if UpSearch’s <span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/microsoft-sql-server-health-check/” target=”_blank”>SQL Server Health Check</a></span> is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.


<ol>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-introduction/” target=”_blank”>SQL Server Health Check Introduction</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-benefits/” target=”_blank”>SQL Server Health Check Benefits</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-1-collect-metrics/” target=”_blank”>SQL Server Health Check Process – Step 1</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-2-analyze-results/” target=”_blank”>SQL Server Health Check Process – Step 2</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-3-report-findings/” target=”_blank”>SQL Server Health Check Process – Step 3</a></span></li>

</ol>

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.