Data-Tales

This is the eleventh tale in a series of stories about data. I hope you enjoy the series.

Recently, I’ve written a series of articles on how the overall size of a financial services database was tamed by the application of table compression, XML compression, and PDF size reduction. I have applied this approach at many sites but recently came across one where the outcome seemed to constantly be getting worse rather than better. Every time I tried to improve the situation, it got worse. Let’s discuss why.

2025-10-13

This is the tenth tale in a series of stories about data. I hope you enjoy the series.

I regularly see code that calculates a count of a set of rows then makes a decision that’s based on the count, but only on whether or not the count is zero. Consider this (slightly contrived) example based on AdventureWorks:

DECLARE @PersonCount int;

SELECT @PersonCount = COUNT(*)
FROM Person.Password
WHERE BusinessEntityID = 12
AND PasswordHash = 0x208394209302;

IF @PersonCount > 0
BEGIN
    PRINT N'Password matches';
END;

Now I’ll start by saying that the last thing I’d ever want to see in SQL Server code is this sort of playing around with users and passwords, but I’m ignoring that for now.

2025-10-09

This is the ninth tale in a series of stories about data. I hope you enjoy the series.

In the first article we saw why size really does matter, both for the DB itself and all the other copies of it that exist in most organizations. We then saw how to estimate the improvement in size with ROW compression. Our customer’s database that started at 3.8TB was reduced to 2.6TB by applying ROW compression without any code changes. Better still the performance of the I/O bound application improved significantly by compressing the tables and indexes.

2025-10-05

This is the eighth tale in a series of stories about data. I hope you enjoy the series.

In the first article we saw why size really does matter, both for the DB itself and all the other copies of it that exist in most organizations. We then saw how to estimate the improvement in size with ROW compression. Our customer’s database that started at 3.8TB was reduced to 2.6TB by applying ROW compression without any code changes. Better still the performance of the I/O bound application improved significantly by compressing the tables and indexes.

2025-10-01

This is the seventh tale in a series of stories about data. I hope you enjoy the series.

In the previous article I started discussing a large customer database that needed to go on a diet. We needed to drastically reduce the size of the database. I described why ROW compression was important, and showed how to estimate the savings from using it.

This month, we’ll start by looking at how ROW compression actually works, then look at the greater savings from PAGE compression. We’ll also look at how it works internally.

2025-09-27

This is the sixth tale in a series of stories about data. I hope you enjoy the series.

Time for a diet

One of the busiest places to be in the new year is in any gym. So many people make new year resolutions and in so many cases, it involves losing weight. So I thought I’d start the new year with a tale of a database that needed to go on a diet. The client’s primary database started at 3.8 TB. This case will be described over a short series of articles. This is part 1 and describes phase 1 of the diet.

2025-09-23

This is the fifth tale in a series of stories about data. I hope you enjoy the series.

3rd party applications and indexes

Most of my end-user clients run applications that are written by other companies ie: 3rd party applications. Very few write most of their own applications. One of the endless challenges with this is that the application vendors never want their clients to make any changes to the databases that are used by their applications. As a customer, it can be very painful to see performance problems caused by poor or inappropriate indexing choices, and yet to be blocked out from fixing those issues by supportability concerns.

2025-09-19

This is the fourth tale in a series of stories about data. I hope you enjoy the series.

As very few people seem to have worked with SQLCMD mode, I thought I’d start this article with a little background.

Some background

Way back in SQL Server 2005, Microsoft added SQLCMD as a new command line tool to replace OSQL. It was an OLE-DB based tool rather than an ODBC based tool and had a richer set of options. For example, you could execute operating system commands within your scripts.

2025-09-15

This is the third tale in a series of stories about data. I hope you enjoy the series.

Anyone who has worked with SQL Server for any length of time knows the common errors that happen. Before a new user even completes their question, you often know how it’s going to end.

One of those questions is always about database log files that keep growing and growing until they fill all available disk space. Sound familiar? We’ve all heard about that one and most of us would have a straightforward checklist for what to look for.

2025-09-07

This is the second tale in a series of stories about data. I hope you enjoy the series.

I was recently at a customer site where the developers were very concerned about the impact of adding columns to a table. They told me that when they added a new column that their deployment code was timing out and the database was massively increasing in size.

The table had increased from around a small size to well over 50GB during the single operation. The deployment operation involved adding the column and writing one row to a deployment history table. Because they were only writing a single new row, they were blaming SQL Server for bloating the database size when a column was added.

2025-09-03