Blog

SQL Server and Evil Greedy Data Types

Mar 2, 2018

In a recent post, I talked about SQL Server reads and how the data is stored in pages. One of the points I made in that post was that you should always use the smallest type that makes sense for a given set of data in order to minimize the IO required. I once worked on a system that constantly used data types that were greedier than needed (e.g INT instead of BIT, CHAR(200) where CHAR(80) or even better VARCHAR(80) would have sufficed) and had all sorts of issues with performance. One of the things we did to improve this was to go through the application/database and set everything to use the correct types. One of the things we noticed with this process was that initially page reads went up not down after correcting the data types, let’s look at why that was and how to fix it…

Run this to create a demo environment….

CREATE DATABASE DataTypeCleanup
GO
USE DataTypeCleanup
GO

CREATE TABLE Users
(
   Id INT IDENTITY,
   Username CHAR(100) NOT NULL,
   IsAdmin INT,
   IsReporter INT,
   IsManager INT 
)
ALTER TABLE Users ADD  CONSTRAINT [pk_users] PRIMARY KEY CLUSTERED(Id ASC)

Hopefully you can already spot some potential issues with this…

  • Username wont always be the same length so VARCHAR is a better fit than CHAR, also in this case I know we never use usernames longer than 50 characters.
  • The 3 Boolean fields have all been given the type INT when they will only ever contain a 1 or 0, this should have used the BIT data type.

Before we correct these issues let’s insert some dummy data….

INSERT INTO [Users](Username,IsAdmin,IsReporter,IsManager)
SELECT 
   s1.[Name],
   s1.number % 2,
   s2.number % 2,
   s1.number % 2
FROM 
   master.dbo.spt_values s1
   CROSS JOIN master.dbo.spt_values s2
WHERE 
   s2.number < 100
   AND s1.Name IS NOT NULL

Lets then turn on STATISTICS IO and run a query selecting all the rows and see how many reads that causes…

SET STATISTICS IO ON
SELECT Id,Username,IsAdmin,IsReporter,IsManager FROM Users

(140220 rows affected) Table ‘Users’. Scan count 1, logical reads 2200, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So reading 140k records aused 2200 page reads.

Let’s now alter our column types the be more suitable for this data…

ALTER TABLE Users ALTER COLUMN Username VARCHAR(50) NOT NULL
ALTER TABLE Users ALTER COLUMN IsAdmin BIT 
ALTER TABLE Users ALTER COLUMN IsReporter BIT 
ALTER TABLE Users ALTER COLUMN IsManager BIT 

Now let’s run our select again and see if the read counts have dropped…

SET STATISTICS IO ON
SELECT Id,Username,IsAdmin,IsReporter,IsManager FROM Users

(140220 rows affected) Table ‘Users’. Scan count 1, logical reads 4393, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

They almost doubled!! How can this be if we’ve shrunk the data types? This is because SQL Server wont reclaim the space used in every row for the old column type and is in fact storing our new BIT/VARCHAR data types along side the space already allocated for the old data types. The only way to then reclaim this space is to REBUILD the table if it’s a heap or rebuild the clustered index if it is not…

ALTER INDEX pk_users ON Users REBUILD

If we then try our select again…

SET STATISTICS IO ON
SELECT Id,Username,IsAdmin,IsReporter,IsManager FROM Users

(140220 rows affected) Table ‘Users’. Scan count 1, logical reads 1191, physical reads 0, read-ahead reads 21, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

In our simple example we now have over 1000 less reads just by changing the types of 3 columns. On the system I was working on at the time IO dropped nearly 50% after correcting greedy data types as our largest most frequently run queries were constantly hitting these fields. One point to note is that unless you are using Enterprise edition REBUILDS must be done offline so make sure this is done in scheduled downtime if you’re doing this.

What Do Read Counts On Your SQL Server Exection Plans Even Mean?

Feb 27, 2018

If you’ve ever spent any time looking at execution plans you will have almost definitely noticed all the read counts on the different operators, you’ve probably already realised lower reads is best as that equates to less time reading disks. Have you ever wondered what the number actually means though? What does 10 reads mean? Time? Operations? Volume?

Let’s step back and look at how SQL Server stores data.

The smallest unit SQL Server stores/accesses data at is called a page, these are 8kb blocks and are stored inside 64kb extents. If you have a row that takes 1kb and you run a query to get one row it will actually bring the whole page that row is in to memory before sending the single row on to the client, this is what counts as a single read.

Let’s look at some examples…

DROP TABLE PageTest
GO
CREATE TABLE PageTest
(
   OnlyColumn CHAR(3000),
)

INSERT INTO PageTest(OnlyColumn) VALUES('test')

So we know a page is 8kb, inside the page is a 96 byte header, a 2 byte list of row offsets and the actual row data. Based on that given the example above we can see that a 2 rows will fit in a single page as it’s a single CHAR(3000) field.

If you turn on statistics IO and run a select from this table how many reads should that show?…

SET STATISTICS IO ON
SELECT OnlyColumn FROM PageTest

Table ‘PageTest’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

1 Logic read, that’s one record and one page. A logical read means the page is already in memory and it’s being read from there, physical means it had to first be read from the disk. As we can never guarantee what will or will not be in memory never assume logical wont become physical. Let’s insert one more record and run that select again…

INSERT INTO PageTest(OnlyColumn) VALUES('test')
SET STATISTICS IO ON
SELECT OnlyColumn FROM PageTest

Table ‘PageTest’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Still 1 read. Let’s try that one more time with 3 records…

INSERT INTO PageTest(OnlyColumn) VALUES('test')
SET STATISTICS IO ON
SELECT OnlyColumn FROM PageTest

Table ‘PageTest’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Now we’re up to 2 reads, As you can probably see in this simple example we have 3 records of CHAR(3000) totalling at 9000 which pushes the 3rd record onto a new page. It’s worth noting at this point that this works exactly the same for indexes only in that case the index page contains only the fields within the index.

This example is pretty trivial but it does illustrate that a field that is using a type larger than the underlying type needs is going to cause a lot of extra IO. If we’d made that field a CHAR(10) or even a VARCHAR(10) we could get close to 800 records on a single page.

SQL Server Tune Your Transaction Log

Feb 26, 2018

Whenever a transaction log file grows the space it is allocated is divided up into virtual log files (VLFs). Depending on the amount the log file is set to grow by the amount of VLFs that are added varies, on instances < SQL Server 2014

  • A growth of 0mb to 64mb results in 4 new VLFs
  • 64mb - 1gb = 8 VLFs
  • Anything larger than 1gb gets 16 new VLFs

On instances 2014 and after

  • If growth size < 1/8th of the current total size then add one new VLF

ELSE

  • A growth of 0mb to 64mb results in 4 new VLFs
  • 64mb - 1gb = 8 VLFs
  • Anything larger than 1gb gets 16 new VLFs

Looking at the formula for 2014 you can see they have tried to cut down on the amount of VLFs allocated if your large transaction log is constantly growing in small increments.

So for example on SQL Server 2008 if you have your log file set to grow at 1mb intervals and it grows 100mb that is going to assign 400 VLFs to that 100mb of new transaction log.

On SQL Server 2014+ if your log file is currently at 3gb with 1mb growth size set and 100mb of space needed it will allocate 1 VLF per growth so rather than the 400 VLFs created before 2014 you will instead get 100 VLFs (Still not ideal).

Let’s create a new database and run a simple query to see how many VLFs it’s created with…

CREATE DATABASE VlfDemo
GO
USE VlfDemo
GO

/*Get Count of VLFs*/
SELECT 
   COUNT(l.database_id) AS 'VLFs'
FROM 
   sys.databases s
   CROSS APPLY sys.dm_db_log_info(s.database_id) l
WHERE  
   [name] = 'VlfDemo'
GROUP BY 
   [name], s.database_id

Depending on SQL version and default settings the numbers may vary slightly but on my system this new database has 4 VLFs. Let’s now change the transaction logs auto growth size to 1mb (This is not a good idea in the real world). On my system this defaults at a 64mb growth size for new databases….

ALTER DATABASE VlfDemo MODIFY FILE ( NAME = N'VlfDemo_Log',  FILEGROWTH = 1000kb)

Let’s now create a demo table with a char 6000 field, I’m using char to force space to be used in the transaction log…

CREATE TABLE LogFileGrower
(
   Id INT PRIMARY KEY IDENTITY,
   LongName CHAR(6000)
)

Now lets insert a load of data to get that transaction log working…

INSERT INTO LogFileGrower (LongName)
SELECT 
   'HelloWorld'
FROM 
   master.dbo.spt_values s1
   CROSS JOIN master.dbo.spt_values s2
WHERE 
   s2.number < 100

Firstly you will probably notice this query is quite slow, we’re growing the transaction log in increments that are far too small causing constant file growths. On my laptop this took 3 minutes to complete. If we then run the above query to get the count of VLFs again, assuming you’re on 2014+ you’ll see we now have 7081 VLFs (Even more if you’re on < SQL Server 2014).

For reference if you drop the database and recreate but this time set the growth size to 100mb on 2014+ you’ll end up with 131 VLFs and on my laptop the same insert went from 3 minutes down to 1 minute (Mainly due to far less auto growths occurring than the amount of VLFs). So we can see from this already that high VLFs will slow down our writes that cause file growths. This can largely be mitigated by setting sensible file growths of even an initial file size for your environment and recovery model.

There are also some other fairly large impacts of large VLF counts. Namely slow backup/restore and recovery times for transaction logs. For example when a SQL Server comes online it performs a recovery process on each databases that looks for incomplete transactions and rolls them back, this process can be massively slower if your VLF counts have gotten out of hand.

In answer to how many VLFs is too many, it really depends. I try to aim for < 200 with alarm bells seriously ringing when I see systems in the thousands. At a previous company we had an unplanned power outage on one of our SQL Servers and when it came back up SQL Server took about half hour to bring a database back online that had somehow assigned itself 15,000 VLFs.

Lowering the VLF Count

If you have a database with a lot of VLFs firstly you need to correct the cause, this will normally involve changing the auto growth size as we did above. Then to lower the count we need to backup the transaction log if we’re in full recovery and run shrinkfile on the log file.

BACKUP DATABASE VlfDemo TO DISK='nul'
BACKUP LOG VlfDemo TO DISK='nul'
DBCC SHRINKFILE (N'VlfDemo_log' , 0, TRUNCATEONLY)

The ‘nul’ backup location just means don’t save anything to disk, I’m just using it to force the database to think it’s been backed up. NEVER use this on a realy system. After running this if you run the query to get the count of VLF’s again you can see we’re back to 4. You may also want to then set the initial filesize of the log back to the value it was before you ran the shrink as that’ll save it having to auto grow several times to get back to where it needs to be. This can be done like this…

/*Set size to 1gb*/
ALTER DATABASE VlfDemo MODIFY FILE ( NAME = N'VlfDemo_Log',  Size = 1000000kb)

subscribe via RSS