VSS and the SQL Writer Service

VSS

What is VSS?

Volume Shadow Copy Service (VSS) is a set of Microsoft APIs. It allows users to perform backups or snapshots on files and volumes even when they are in use. The VSS provides a consistent interface that allows coordination between user applications. Even if they update data on disk (writers) or back up applications (requestors).

How it works

  1. Gather the writer metadata and prepare for shadow copy creation.
  2. Each writer creates an XML description of what is getting backed-up. These XMLs are provided to the VSS. The writer also defines a restore method for all components. The VSS provides the writer’s description to the requester. It then selects the components that is getting backed up.
  3. The VSS notifies all the writers to prepare their data for making a shadow copy.
  4. Each writer prepares the data as appropriate. When finished, the writer notifies the VSS.
  5. The VSS tells the writers to freeze application write I/O requests. Read I/O requests are still possible. This allows it to create the shadow copy of the volume or volumes. The application freeze is not allowed to take longer than 60 seconds. The VSS flushes the file system buffers and then freezes the file system. This ensures that the file system metadata logged and is in a consistent order.
  6. The VSS tells the provider to create the shadow copy. This period lasts no more than 10 seconds. During which all write I/O requests to the file system remain frozen.
  7. The VSS releases file system write I/O requests.
  8. VSS tells the writers to thaw application write I/O requests. At this point applications are free to resume writing data to the disk.
    • The shadow copy creation can abort if the writers stay frozen for longer than 60 seconds. Or, if the providers take longer than 10 seconds to commit the shadow copy.
  9. The requester can retry the process (go back to step 1) or notify the administrator to retry at a later time.
  10. Upon creation, the VSS returns location information for the shadow copy to the requester.

More detailed information here.

SQL Writer Service

When it relates to SQL server, most of the time any errors on backups are not caused by SQL. But when they are, it is likely that the SQL Writer Service is usually not enabled. The service installs without user intervention when installing SQL Server.

This service provides added functionality for backup and restore of SQL Server through VSS.

SQL Writer supports:

  • Full database backup and restore including full-text catalogs
  • Differential backup and restore
  • Restore with move
  • Database rename
  • Copy-only backup
  • Auto-recovery of database snapshot

SQL Writer does not support:

  • Log backups
  • File and filegroup backup
  • Page restore

Limitations/Problems

  1. Point-in-time recovery – It is possible you will not meet your RPO for your business. As usually, you can only recover to the last point of your backup not a specific time.
  2. Transaction Log Clearing – VSS backups will not clear the SQL log file. This needs to happen through the Naive SQL commands.
  3. Pauses in I/O – As mentioned in the “How it works” section. During a backup, it is possible that database I/O will pause for just under 70 seconds. This can lead to users complaining about performance issues or kick-outs.

Common troubleshooting

A few things to check when getting errors:

  1. The service needs to be running at the time of the backup.
  2. This service also needs to be running as the Local System account.
  3. NT Service\SQLWriter login needs to be active inside SQL server. This account is designated as no login, which limits vulnerability.

Next steps

If none of these are causes of your problem, it is a good idea to reach out to your backup solution vendor. You will want to include the following in the email:

  1. That you checked the three steps listed above
  2. The event viewer errors or warnings
  3. The detailed description of what you are having
  4. How often the problem occurs

Helpful tips and tricks for SSMS

top-5

I have been busy working on another project and haven’t been able to get a post together in a bit. So, I wanted to put something quick together for everyone. These are some of the tips and tricks that I have learned over the years and have found uses for almost everyday.

Adding line numbers

Line numbers are key to debugging code. It is the reason code editors tend to have them on by default. But, this is not the case with management studio. To add these select Tools -> Options menu item. In the dialog box that appears click on Text Editor and then Transact-SQL. In the display section on the left, select the check box for line numbers

Inserting a comma separated list

Every select statement needs a comma separated list of columns you would like to return. But, this can be a pain to type especially for many columns. But, what I tend to do is just grab all the columns in the tables and select the blocks I do not need. Now some of you are stating, but why in the world would you write those all out???

The trick is not to write them out to begin with. Drag the ‘Columns’ item in Object Explorer and drop it onto to a query window. This generates a list in your query window. Just add the select, from, and where clause.

Selecting a block of text

Now that you have all the columns from your tables, you need to remove some. Instead of just highlighting the text and deleting the rows. Try to hold the ‘alt’ key and highlight the text. This allows you to select the block of text and remove it. That’s pointless, why would anyone need this?

The best use case for this is to remove the schema/table references at the front of your columns.

Inserting a block of text

Now that you can select this text, there is always one question that comes up. Inserting text, how?

To do this, add the ‘shift’ key along to your ‘alt’ key. Once held down, use the directional keys to navigate up and down. This makes aliasing tables easy.

From BrentOzar.com

Color coding connections

Finally, many of us tend to work on every server at once. This can lead to some confusion when trying to execute queries on a specific box. This can be great to label different boxes specific colors. SSMS has the ability to color code specific connection. This displays at the bottom of the query window.

E.G. Green for test or Red for Production for example.

When connecting, select the Options button in the Connect to Database Engine window. Then select the Connection Properties window. Select the check box towards the bottom of the window and use the ‘Select…’ button to choose a colour.

Database Design (Part 6) – Heaps

database-design-1024x940

Zooming out further, we should take a look at the table structures. By this, I am of course talking about HEAPS and Clustered Tables. Though, the main focus of the post will be Heaps, it is hard to talk about them without the other.

The official definition for a heap”…is a table without a clustered index.” This has always been a wonky definition to me. Who defines something by saying it’s not something else? But, the reason they did it is because it is correct. This is the only difference between a heap and a clustered table.

To explain what a heap IS though, I find it easier to list the properties:

  • Made by using CREATE TABLE syntax without a clustering key
  • CAN have a primary key – with a non clustered index
  • Can have non-clustered indexes

So in reality, a heap is a table that does not store data in a specific order. Because of this, these heap tables can affect performance in two primary ways:

  • Full Table scans
  • Forwarding Rows

Full Table Scans

To examine any row in the table heaps have to perform a full table scan. This in itself should tell you that this will cause problems. These scans are S…L…O…W… due to the heavy amount of I/O reads required. This also causes a high amount of disk to memory swapping caused by SQL loading the table into memory.

Forwarding Rows

These ONLY exist in a heap table structure. Once the record outgrows the page, SQL Server moves the data to another page. It then leaves a Forwarding pointer/row at the original location to point to the new location. This entry can cause I/O increases that you wouldn’t expect. How the I/O problems arise:

  • The query you are running uses a record locator. This points to a page and the DB engine has to go to that page.
  • Upon arriving, the locator sees the forwarding pointer.
  • It then goes to the new page to get the forwarded row.

So why do we have them?

The main reason is that they do have their place. There are specific use cases where you would want them. Microsoft does a decent job at explaining them on their article here.

But again, in most cases they are not appropriate and will cause many problems in production. This is why by default SQL server will default the primary key to be your clustering key. (Not always the best either, will discuss on the next post)

Fun Part

So now that we have the definitions out of the way we can do the demo to see how bad they actually are.

First, let’s create a heap table and insert 1000000 rows.

CREATE TABLE dbo.HeapingHeap
(
Id BIGINT IDENTITY NOT NULL,
column1 VARCHAR(1024) 
DEFAULT REPLICATE('A',200) NOT NULL
);
GO

INSERT INTO HeapingHeap DEFAULT VALUES
GO 1000000

So now that we have our table setup and we can see the data, let’s look at performance.

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
SET STATISTICS IO ON;
SELECT *
FROM HeapingHeap
GO
SET STATISTICS IO OFF;
GO

Here is what I received:

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

Lets also look at how many pages we have in this table with the query to analyze physical stats. With one key change, we need to add forwarding rows.

SELECT
[alloc_unit_type_desc] AS [Data Structure]
, [page_count] AS [pages]
, [record_count] AS [Rows]
, [min_record_size_in_bytes] AS [min row]
, [max_record_size_in_bytes] AS [Max Row]
, [forwarded_record_count] AS [Forwarded Rows]
FROM SYS.dm_db_index_physical_stats
(DB_id()
,OBJECT_ID (N'HeapingHeap')
, NULL
, NULL
, N'detailed')

Here is what I received:
Screen Shot 2016-04-18 at 6.42.14 PM
This makes sense, As the logical reads = the amount of pages. This is good, performance is what we would expect. But what happens if we change the data on the heap? Let’s update the table to have 50% of the rows now be a bigger column.

UPDATE dbo.HeapingHeap
SET column1=REPLICATE('Z',1000)
WHERE Id % 2 = 0;
GO

Again, let’s run our query for performance.

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
SET STATISTICS IO ON;
SELECT *
FROM HeapingHeap
GO
SET STATISTICS IO OFF;
GO

We now receive a new number:

Table ‘HeapingHeap’. Scan count 1, logical reads 485715, physical reads 7145, read-ahead reads 28543, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

This is a huge performance issue, now we are hitting the disks harder. Yet, we are still returning the same count of rows. How did this happen? What caused it to change?

This was the act of the forwarding rows on the table. Let’s take a look at our physical table again.

SELECT
[alloc_unit_type_desc] AS [Data Structure]
, [page_count] AS [pages]
, [record_count] AS [Rows]
, [min_record_size_in_bytes] AS [min row]
, [max_record_size_in_bytes] AS [Max Row]
, [forwarded_record_count] AS [Forwarded Rows]
FROM SYS.dm_db_index_physical_stats
(DB_id()
,OBJECT_ID (N'HeapingHeap')
, NULL
, NULL
, N'detailed')

If you notice, we now have 400,000 new forwarding rows that did not exist before. This is where our extra I/Os are coming from. Remember, as mentioned earlier you are causing SQL to go and fetch each one of these rows.
Screen Shot 2016-04-18 at 7.19.39 PM
For us, the simple way to look at predicting the I/Os of this query is to use this equation.

Logical Reads = allocated pages + forwarded rows

How to fix this

To fix heaps there are two things to do:

  1. Add a clustered index so the table won’t be a HEAP.
  2. If you NEED the heap in your database. use ALTER TABLE tablename REBUILD to rebuild the table.

NOTE:  People stumble because there is no easy way to identify the table is a heap. The only thing you can check for is the lack of clustering index. The best way to tell is to run a script on the database.

SELECT SCH.name AS [Schema Name] ,
OBJ.name AS [Table_Name]
FROM sys.indexes INX
JOIN sys.objects OBJ 
ON INX.object_id = OBJ.object_id
JOIN sys.schemas SCH 
ON OBJ.schema_id = SCH.schema_id
WHERE OBJ.is_ms_shipped = 0 --filters SQL objects
AND INX.index_id = 0 --meaning it has none
AND OBJ.type = 'U'; --user table
GO

This gives us a view of all the tables inside your database that are heaps.

Transaction log Q&A

This week I am going to take a break from the database modeling series. Instead, I want to discuss some of the confusion around the transaction log. The transaction log is one of the most important things to understand in SQL server. Especially when referring to High availability or Disaster recovery. In these features, SQL uses the transaction log as a key component. Afterall, without your transaction log your database is unusable.

What is a transaction?

A transaction is a single unit of work that complies with A.C.I.D. standards. A.C.I.D stands for:

  • Atomic – It either all works or none of it does
  • Consistent – It complies with the “Rules”, meaning constraints, triggers, datatype adherence, etc.
  • Isolated – It will not affect other transactions that are running at the same time
  • Durable – Changes are permanent and will survive a power outage, crash, and/or memory dump

Additionally, SQL server uses implicit transactions. This means that it will apply the begin and commit/rollback for you if not specified. The line shown below is a single transaction.

DELETE FROM person WHERE lastname='Billy'

Whereas, this line is two transactions.

DELETE FROM person WHERE lastname='Billy'
DELETE FROM person WHERE firstname='Timmy'

To make this durable you need to wrap it with a begin transaction and end with commit or rollback. Otherwise known as an explicit transaction.

Transaction log Operation

To outline the transaction log operation I always found it easier

  1. User executes a transaction
  2. Pages that are going to change go to the SQL cache on RAM
  3. The log file records the details of the transaction. This process also assigns the transaction a Log Sequence Number (LSN).
  4. After storing the intent of the transaction, SQL server then modifies the pages on RAM.
  5. The pages are, at some point, written back to the disk.

So the now that we understand the basics of the transaction log. We can answer one of the first questions I receive.

What happens during a crash?

If your server crashes, it is the responsibility of the log file to help you recover. The dirty pages (pages that were in RAM that did not make it to disk) are now lost. But, the transaction log contains a full description of the intent of each transaction.

Upon restart, SQL begins to check integrity and consistency utilizing the log. To do this, it compares the LSNs in the log to those on disk. If the LSN in the log is newer it updates the file on the disk, known as the REDO phase. If the LSN in the log is older, it then knows it needs to rollback, known as the UNDO phase. These make sure that when the database comes online, it is consistent.

This segways into the new question I often receive.

Why does my log file keep growing?

To understand this, you need to first understand how the log file handles information. To start, the transaction log file is a circular file. This means that it has a set amount of space and continues to create new log records sequentially until the end. Once it reaches the end of the file SQL server faces two options.

  1. Circle back to the beginning and override older log files.
  2. Grow the bounds of the log file and continue to consume space.

With Option 2, it requires extra space and needs the overhead to grow the file. When the log file grows, it does so by creating new virtual log files (VLFs) with the log. You can consider these as groups of transactions within the log. Thus using the transaction log with option 1 is preferable.

So since this is preferable, why does sql keep growing? Well it is simple. SQL server needs to keep log records around until no purpose remains for their storage. These purposes vary, but the main reason is the records are critical to the REDO and UNDO phase. SQL does this by marking them as “Active”. This means that SQL is not allowed to reuse the space in the log until all the transactions are “Inactive” in the VLF. Then and ONLY then can SQL server reuse the space allocated to the VLF.

So in short, your SQL server is leaving the transactions as active. But don’t freak out, this is normal for the FULL recovery model. In this model you need to take transaction log backups to fix this.

Unfortunately, this conversation usually doesn’t happen right away. This comes up when the log file is using a massive amount of space on the drive. This leads to the next question…

How do I Manage the log file?

There are many different articles about this. But, they do not always have the best/correct answer. Most people just recommend to either:

  • Switching the database to Simple/Shrink the file/Switch back to full
  • Truncating the log with NO_LOG/TRUNCATE_ONLY

Both of these solutions kill me, luckily, starting in 2008 option two is no longer possible. Yet, Option 1 is still TERRIBLE. When you look at what we talked about earlier, I hope this makes you cringe.

To explain:

When you shrink the file this way you completely discard everything in the log file. That means all the work since the last backup is gone if your database crashes or fails for whatever reason. This violates the whole point of the FULL/BULK_LOGGED recovery models. The point of this model is to preserve the transaction log so a database can recover to a specific time.

Besides, if you shrink the file, it will grow again. This growth is likely to create log fragmentation in the VLFs. This is a performance concern much like disk fragmentation is. Though it is not as much of a problem in SQL 2014 and above but it should still be on your mind. Paul Randall has a good article about the creation of VLFs listed here.

Additionally, this will pause your workload while the log file is growing.

There are only 2 solutions that are acceptable in my opinion:

  1. Run in Simple mode – If you don’t mind the possibility of losing data in the event of a disaster, this is a good option.
  2. Running regular log backups. Especially, if you are looking for point-in-time recovery.

Finally, after I explain this to them, I always get…

How do I get it back to a reasonable size?

It is always tricky to give the “Reasonable” size answer. The truth is that it varies based on your workload and size of your database. I would always suggest to do the following:

  • If it is your company’s proprietary database speak to the developers. They should have a staging environment where you can track the log growth.
  • If you are using another company’s database reach out to their support. They might have an answer but they are more likely to give you the “It depends” response.
  • Guess… I have seen guestimates from 20-25% of the MDF to 1-2% on Multi-terabyte databases.

The good news is that it is easy to change. To resize the log file I always use the following steps:

Wait for an inactive time of day.  It would be best to put the database into single user mode first but it is not required.

ALTER DATABASE databasename
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

Run a transaction log backup if you are in full recovery mode. Or if you’re using the simple recovery just clear the transaction log by running a checkpoint.

BACKUP LOG databasename
TO DISK = 'File path and extension (.TRN)';

Shrink the log to the smallest possible amount.

DBCC SHRINKFILE(TlogLogicalFileName, TRUNCATEONLY);

You will need the filename of the log to complete this step. Run the following script if you do not know it.

EXEC SP_HELP

Alter the database to change the transaction log file

ALTER DATABASE databasename
MODIFY FILE
(
NAME = TlogLogicalFileName
, SIZE = NewSize
);

If you set it to single user, Change it back

ALTER DATABASE databasename
SET MULTI_USER;

SQLSkills  suggests to grow the logs in 8GB chunks using 8000MB as the NewSize variable. This creates VLFs that are 512MB. These smaller chunks make it easier to maintain the smaller log file size.

Database Design (Part 5) – Vertical Partitioning

Data density is something that was hinted at in the last three parts of this series, however, it wasn’t totally discussed. Data density put simply is how many rows you get per page and how much space is left over on each page. It was discussed that a page can only contain up to 8060 bytes. This means that if a row is fixed width for 5000 bytes in a row, you could only have 1 row per page and essentially 3060 bytes wasted on every page. This can result in a huge waste of space in the data files.

A good practice to avoid wasting space is using vertical partitioning. This practice has 2 main categories:

  • Normalization
  • Row splitting

For the focus of this article we will be focusing on Row splitting. Put simply, this is the process of dividing out columns that are not used often OR that are large columns that could potentially be stored into another table.

Since these values are stored in another table it does make the queries a bit more complicated to write as you are slightly de-normalizing your data especially if this is used often and is just a large value. However, if the column is not used that often this can be very useful not only for storage but also for query optimization.

To start off, we are going to demo a table that has not been vertically partitioned.

CREATE TABLE [NOVerticalPartitioning]
(
[Column1] INT IDENTITY,
[Column2] VARCHAR (100),
[Column3] VARCHAR (20),
[Column4] VARCHAR (1000),
)

From here we will insert 1 million rows, I believe this to be a realistic size for a person or product table.

INSERT INTO [NOVerticalPartitioning]
(
[Column2],
[Column3],
[Column4]
)
VALUES
(
REPLICATE ('2', 50),
REPLICATE('3',20),
REPLICATE ('4', 1000)
)
GO 1000000

Now before we analyze this data, lets see how it would look if we move the large VARCHAR (1000) column that is always populated off to another table.

CREATE TABLE [WITHVerticalPartitioning1]
(
[Column1] INT IDENTITY PRIMARY KEY,
[Column2] VARCHAR (100),
[Column3] VARCHAR (20)
)
CREATE TABLE [WITHVerticalPartitioning2]
(
[Column1] INT IDENTITY FOREIGN KEY REFERENCES 
[WITHVerticalPartitioning1]([Column1]),
[Column4] VARCHAR (1000)
)

AND lets also insert the 1 million rows same as previously inserted.

BEGIN TRANSACTION
INSERT INTO [WITHVerticalPartitioning1]
(
[Column2],
[Column3]
)
VALUES
(
REPLICATE ('2', 50),
REPLICATE('3',20)
)

INSERT INTO [WITHVerticalPartitioning2]
(
[Column4]
)
VALUES
(
REPLICATE ('4', 1000)
)
GO 1000000
COMMIT TRANSACTION

Now lets used the same query from the previous post to view the row structure of the table with no vertical partitioning.

SELECT
[alloc_unit_type_desc] AS [Data Structure]
, [page_count] AS [pages]
, [record_count] AS [Rows]
, [min_record_size_in_bytes] AS [min row]
, [max_record_size_in_bytes] AS [Max Row]
FROM SYS.dm_db_index_physical_stats
(DB_id()
,OBJECT_ID (N'NOVerticalPartitioning')
, NULL
, NULL
, N'sampled')

2016-04-03 14_45_44-SQLQuery1.sql - JAMES-PC.AdventureWorks2012 (JAMES-PC_James (90))_ - Microsoft S

Note that the row count is 1000200. This is due to the fact that we only took a “sampled” view of the pages, we will need to view the “detailed” in order to get an exact count. As previously noted, since we have more then 10,000 pages, the sampled view will not to a detailed view automatically.

SELECT
[alloc_unit_type_desc] AS [Data Structure]
, [page_count] AS [pages]
, [record_count] AS [Rows]
, [min_record_size_in_bytes] AS [min row]
, [max_record_size_in_bytes] AS [Max Row]
FROM SYS.dm_db_index_physical_stats
(DB_id()
,OBJECT_ID (N'NOVerticalPartitioning')
, NULL
, NULL
, N'detailed')

2016-04-03 14_48_39-SQLQuery1.sql - JAMES-PC.AdventureWorks2012 (JAMES-PC_James (90))_ - Microsoft S

There we go, exactly 1000000 rows.

Now lets see how our performance looks, but first to make sure we are starting from a clean state lets clear our cache using the following script.

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

Now lets run a query to return only the even rows that we had inserted based off our primary key value. However, prior to running the following query lets also turn on our “STATISTICS IO ON” this helps you to understand how your query performed shows you what actually happened.

SET STATISTICS IO ON
GO
SELECT * FROM [NOVerticalPartitioning]
WHERE Column1 % 2 = 0
GO
SET STATISTICS IO OFF

Once we click on our messages tab we will see the following:

(500000 row(s) affected)
Table ‘NOVerticalPartitioning’. Scan count 1, logical reads 166674, physical reads 0, read-ahead reads 166473, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Now for the Vertically partitioned tables, lets first look at the row structures.

SELECT
[alloc_unit_type_desc] AS [Data Structure]
, [page_count] AS [pages]
, [record_count] AS [Rows]
, [min_record_size_in_bytes] AS [min row]
, [max_record_size_in_bytes] AS [Max Row]
FROM SYS.dm_db_index_physical_stats
(DB_id()
,OBJECT_ID (N'WITHVerticalPartitioning1')
, NULL
, NULL
, N'detailed')
SELECT
[alloc_unit_type_desc] AS [Data Structure]
, [page_count] AS [pages]
, [record_count] AS [Rows]
, [min_record_size_in_bytes] AS [min row]
, [max_record_size_in_bytes] AS [Max Row]
FROM SYS.dm_db_index_physical_stats
(DB_id()
,OBJECT_ID (N'WITHVerticalPartitioning2')
, NULL
, NULL
, N'detailed')

2016-04-03 15_38_33-SQLQuery1.sql - JAMES-PC.AdventureWorks2012 (JAMES-PC_James (54))_ - Microsoft S2016-04-03 15_39_13-SQLQuery1.sql - JAMES-PC.AdventureWorks2012 (JAMES-PC_James (54))_ - Microsoft S

Finally lets see how our query performs.

SET STATISTICS IO ON
GO
SELECT * FROM [WITHVerticalPartitioning1] VP1
inner join [WITHVerticalPartitioning2] VP2 ON vp1.Column1=vp2.Column1
WHERE vp1.Column1 % 2 = 0
GO
SET STATISTICS IO OFF
GO

Now lets compare what we have gathered thus far:

Vertically Partitioned NOT Vertically Partitioned
Total pages 154018 166674
Selecting all columns – Scan Count 10 1
Selecting all columns – Logical Reads 193764 166674
Selecting all columns – Physical Reads 4659 0
Selecting all columns – Read-Ahead Reads 193764 166674

Well it looks like we have now saved 12,656 pages, which is excellent! It saved us just over 101MB, however, if we were always trying to return the last column, this would definitely not be worth it for performance reasons. However, if we weren’t always selecting all of the columns, what would that look like? Would that make the difference for us and make it worth our time?

To find out, lets run the following query and analyze the out puts.

SET STATISTICS IO ON
GO
SELECT Column2,Column3 FROM [WITHVerticalPartitioning1] VP1
WHERE vp1.Column1 % 2 = 0
SELECT Column2,Column3 FROM [NOVerticalPartitioning]
WHERE Column1 % 2 = 0
GO
SET STATISTICS IO OFF
GO
Vertically Partitioned NOT Vertically Partitioned
Selecting Column2 and Column3 – Scan Count 1 1
Selecting Column2 and Column3 – Logical Reads 11154 166674
Selecting Column2 and Column3 – Physical Reads 3 0
Selecting Column2 and Column3 – Read-Ahead Reads 11150 166674

It made a huge difference for performance, we now no longer had to look at that third column and increased our performance by almost 15 times.

In summary, vertical partitioning is a good design practice and should be used, however, it does depend on your workload. Do not throw columns to another table just because they are large objects. Instead, take the time to evaluate your data and make the right choice when it comes to what table the column should be under.

 

Database Design (Part 4) – Row Structure

To completely understand how efficiently your database is using space/pages, it is important to understand the row structure of the database.

let’s create a table that shown below

CREATE TABLE [TestingColumnSize]
(
[Column1] INT IDENTITY,
[Column2] VARCHAR (1000) DEFAULT 'HELLO',
[Column3] VARCHAR (1000) DEFAULT 'SQL',
[Column4] VARCHAR (1000) DEFAULT 'FOLLOWERS',
[Column5] VARCHAR (1000) DEFAULT 'HOPE',
[Column6] VARCHAR (1000) DEFAULT 'THIS',
[Column7] VARCHAR (1000) DEFAULT 'HELPS'
)

From here we will insert default values 4 time using the following script

INSERT INTO TestingColumnSize DEFAULT VALUES
GO 4

2016-03-27 14_40_01-SQLQuery1.sql - JAMES-PC.AdventureWorks2012 (JAMES-PC_James (59))_ - Microsoft S

We now have our table populated with 4 rows, this will allow us to query the table to see how our table is storing this information on the pages. Using the below query you will be to see how many rows we have and what the max and min rows are using for bytes.

SELECT
[alloc_unit_type_desc] AS [Data Structure]
, [page_count] AS [pages]
, [record_count] AS [Rows]
, [min_record_size_in_bytes] AS [min row]
, [max_record_size_in_bytes] AS [Max Row]
FROM SYS.dm_db_index_physical_stats
(DB_id()
,OBJECT_ID (N'TestingColumnSize')
, NULL
, NULL
, N'Sampled')

Additional information – The below query can be run in a few different modes which can be read about here. However, for the purposes of this demo we are using Sampled mode  Since the heap has fewer than 10,000 pages, DETAILED mode is automatically used.

Currently,our table looks like this.

2016-03-27 14_01_24-SQLQuery1.sql - JAMES-PC.AdventureWorks2012 (JAMES-PC_James (59))_ - Microsoft S

However, if you are following along you might ask how we have 55 bytes per row? It doesn’t seem to make sense at first glance since we only used 34 bytes in the default values. (4 on the identity int and 30 for the characters in varchar defaults)To explain this, you need to understand the structure of the row, below I have created an image showing you what each column looks like when the data structure is in_row_data. Image 1

Every column in this data structure is set up this way:

  • Row header – consists of a tag and null bitmap (outside the scope of this tutorial) but it makes up 4 bytes.
  • Fixed length columns – This would be our integer, making up another 4 bytes.
  • Null bitmap – used to optimized storage, allows you not to store “Null” in all columns that are null (outside the scope of this tutorial) uses 2 bytes PLUS an additional 1 bit per variable length column. 3 bytes
  • Variable-width column offset array – allows sql to quickly find the end of the variable length column by storing the end value. 2 bytes plus every column value EXCEPT when there are trailing nulls. 2 bytes plus (2 *6 bytes for our columns)
  • Variable width columns – The values of the variable width columns 30 bytes

This gives us our 55 bytes.

Now, I mentioned in the variable-width column offset array, that if the row had trailing nulls their was  a difference. Let’s demo this…

In our table we created above, we will insert a new row.

INSERT INTO TestingColumnSize 
(Column2,Column3,Column4,Column5,Column6,Column7) 
VALUES 
('inserting30','bytes of data','in row',null,null,null)

Now lets run our query above to find out row size.

2016-03-27 14_51_38-SQLQuery1.sql - JAMES-PC.AdventureWorks2012 (JAMES-PC_James (59))_ - Microsoft S

We now have 49 bytes for one row thanks to the trailing nulls. This was caused by the variable-width column offset array, it did not have to dedicate 2 bytes to the null columns. However, what happens if we insert a value at the end of the row.

INSERT INTO TestingColumnSize 
(Column2,Column3,Column4,Column5,Column6,Column7) 
VALUES 
('inserting30','bytes of data','in row',null,null,'1')

Again, running the query above to examine the rows, we find that the offset array needed to store the 6 bytes again.

2016-03-27 14_53_53-SQLQuery1.sql - JAMES-PC.AdventureWorks2012 (JAMES-PC_James (59))_ - Microsoft S

This can lead to a terrible waste of space if you have columns that are often null stored in the beginning of the table instead of the end. So if you have nullable data, always make sure that you order it in the way where the column most likely to be null is at the end, NEVER out a defaulted value at the end of a row, it will cost you much more space than you think.

Database Design (Part 3) – Understanding Storage

At this point, I want to take a moment to help you understand the database storage a bit more. I am not talking about RAIDs, SANs, or having the transaction log on a different drive either. What I mean is to talk about how the database actually stores the attributes (columns) and data records (rows).

To start off, I would like to dig a bit into a pages and extents.The basic unit of data storage in SQL Server is the page.  Disk I/O operations are performed at the page level. Meaning that SQL server must read or write whole pages. Pages make up 8KB of data, which allows 128 pages for each MB allocated to SQL server’s data files(MDF and LDF).

Below is an outline of a page with how it is made up.

Page image

Extents, on the other hand, are a collection of eight physically sequential pages and are used to efficiently manage the pages. All pages are stored in extents. However, to make space allocate efficiently SQL does not allocate whole extents to tables at one time. This creates two different types of extents, a mixed and uniform extent.

Until objects (tables, indexes, etc.) have enough pages to create a uniform extent they are a mixed extent. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations.

Zooming in further, a row can have 3 types of data structures.

  • LOB_DATA
    • Stored off page when the Data in the LOB data column exceeds the 8060 bytes.
    • This is only possible through certain data types
      • Varchar(MAX)
      • nVarchar(MAX)
      • Varbinary(MAX)
      • XML
      • CLR user defined types
  • IN_ROW_DATA
    • Every row has a portion of its data stored in this data type if not all
    • Cannot span pages
    • ALL fixed width columns must be stored in this data structure
    • Limited to 8060 bytes due to the header (96 bytes), slot array(2 bytes), and future use for microsoft(34 bytes).
    • What this means is you will not be able to create 10 CHAR(1000) columns in the table, since these are fixed width.
      • However, you can create 10 VARCHAR(1000) since they are not fixed. But note, that if you exceed the 8060 bytes in the table you will overflow the table can create the data type below.
  • ROW_OVERFLOW_DATA
    • This only happens to certain data types
      • Varchar(x)
      • nVarchar(x)
      • Varbinary(x)
      • sqlvariant
    • When this happens, it can cause two major issues
      • Poor performance – If the column overflows, you would need to do an extra I/O to retrieve the column
      • Data integrity issues – E.G. an Varchar(2000) column for an address. This allows users to input, “zzzzzz….”

Now that we got through this I hope it gives you some basic insight into the data structures. In the following post, we will go over why this was important for Data Density reasons and other performance concerns.

Database Design (Part 2) – Why does it matter?

As hinted in the previous post, many people do not take database design seriously when first implementing their database idea. These people mostly have the “Let’s just get it done, and we will deal with performance later…” kind of mindset. This is definitely the wrong approach to database design. While the database may be fine for a short period or a small group of people, if you are truly looking for a scalable environment this can be a major problem.

Another excuse I see for my colleague is that most of the time, people are only building databases for the semester or a final project. This can also be the wrong mindset, for example, let’s say that database comes into play for another course or you actually would like to build a database for a real purpose, such as organizing your music. In this situation, you would also fall into the trap where people think that databases are for a short term purpose and not intended to last very long.

There are three things that are critical for building a reliable and robust database:

  • Know your data – Helps you properly choose what data types to use. For example, if you need to record a date. Do you need the time?
    • The date data type is only 3 bytes
    • The datetime2 data type can range from 6 to 8 bytes.
  • Know your workload – This can help you plan what tables should store the data
    • OLTP databases vs Reporting database : Reporting databases tend to store more information in one table for faster reads as they do not need to insert data as often. While OLTP databases store less data in one table to facilitate a more balanced workload.
  • Know how the platform works – This is key; it makes it easier to design a database as you will not only know the syntax but you can understand the difference between a clustering key versus nonclustering keys/indexes.

A good example of why this matters is the 3 byte Versus 6-8 byte argument I mentioned on above. Most people would shrug off a 3 or 5 byte difference as it is so small, however, you are not only dealing with one row or in some situations one column.

In this scenario, Let’s say that you have a table with 3 datetime2 columns that are defaulted to a precision of 7 “Datetime2(7)” and are always populated, however, you only need to store Date which use 3 bytes. In this case you would be saving 15 bytes per row.

 Bytes Saved Count of Rows  Space Saved
15  1,000,000  15 Mb
15  10,000,000  150 Mb
15  100,000,000  1.5 Gb
15  1,000,000,000  15 Gb

So now you would respond with, “So? it just disk space?” That is not true, due to the way sql server handles pages and data it is now read into memory. So you now have bloat on your ram due to inefficient data types. Not to mention, you have to include logs, database backups, replications, and High availability environments.

However, this is not something you would want to choose to close to the mark, if you think you are going to approach the limit of a data type, such as a tinyint (0 to 255) make sure you use smallint. (-2^15 (-32,768) to 2^15-1 (32,767)).

The point I am trying to make here is take your time in choosing the proper data types. These data types are very difficult to change in the future, especially once the code has been written.

Database Design (Part 1) – Introduction

When first starting in the software world many companies do not hire database administrators or data analysts. This often leads to poorly designed databases and inefficient databases. In this series, I will touch on Datatype choice, table structures, and how this affects your database performance.

The main reason, I wanted to start this post is for a friend who is struggling in his college database class, who has a final project for designing a database. However, the concepts are scalable and do apply to anyone who is building a database. I would also like to shout out to Kimberly Tripp as SQLSkills.com, her enthusiasm during her courses on pluralsight and blog have definitely helped me stay interested in SQL.

To start off, I would like to throw out one of Kimberly’s favorite quotes regarding database design. “Disk space is cheap, who cares about 4 bytes versus 16 bytes?”. While disk space has become cheaper, it doesn’t mean that we should always use a bigint when a tinyint will do the job. As the series continues, we will go through some of the reasons why this is generally a bad practice.

Additionally, as a final note in the introduction, I would like to mention again that this blog is for Microsoft SQL server and most of the information that is given will refer to microsoft specifically, and while the theories will transfer in practice it may not be the best solution for the other RDBMS (Relational DataBase Management Systems).

Maintenance Tasks

Every day in the IT field people are thrown to being the accidental DBA. While this is a mistake for most companies to make it happens, especially with the change in the healthcare field and the adoption of Electronic Medical Record (EMR) software. I wanted to make sure to get some knowledge out there on the subject of how to maintain a database and avoid a Resume Producing Event (RPE).

Before getting started on the maintenance it is important to understand and discuss about your Recovery point objectives(RPO) and recovery time objectives(RTO).

RTO & RPO By Paul Randal

Core Maintenance Tasks – SQL server Jobs

Backups – One of the key things to remember is that backups should be ideally performed to a local disk for performance AND then copied to another location for storage. Far to often these are found on the same disk in my experience and is terrible idea for disaster recovery.

Recommended (mission critical)

  • Full database backup 1 week
  • Nightly differential
  • Transaction logs every 15 minutes

Remember FULL recovery mode for mission critical databases where data loss is not acceptable as this allows for point-in-time recovery and log backups. For more information feel free to check out the Previous Post Here.

Consistency Checks – It is usually assumed that SQL server does consistency checks and identifies Database corruption immediately, but in reality, SQL server does not notice the corruption until the corrupt page is accessed from disk again. This could be weeks or even months away if you are unlucky and at that point you will likely not have the backups prior to the corruption.

Recommended – (Based on maintenance windows)

  • Run at least one time a week

Please note to remove the dependency for a maintenance window it is possible restore a database to another server and run a system integrity check on the database from there. If corruption is found, you would have to identify if it is your backup or if it is in your live/production server. This process would then have to call a maintenance window in some circumstances.

Index Maintenance – Rebuilding your indexes is a key part of maintaining performance in a database. This can generate a high amount of log information and high amount of I/Os on the environment as all the tables are updated with a full table scan.

Recommended –

  • Run one time a week

As a note to this, There is an option to “Reorganize” indexes, this should NOT be scheduled at the same time of the Rebuild. This task reorganizes the indexes but does not include an update of the statistics. In order to complete the same thing as a rebuild you would also need to run a update statistics task. Still, reorganizing indexes is a fully online operation and can be a good interim step between regular executions of the rebuild index task.

Recommended –

  • Run one time a week – Halfway between your executions of the Rebuild.

For additional information, I would always recommend looking into the series from SQLskills.com Accedental DBA.