Sql-server – Should I care about physical columns in SQL Server

sql server

So far, I only ever looked at the logical columns of my table but never at the physical columns. My motto was : "let SQL Server deal with it".

After reading that article, I wonder if I should not pay some attention to it.

By now, our 5 column table has actually 15 columns in the physical
storage format, 10 dropped columns and 5 usable columns. The table
uses 412 bytes of fixed space for the 3 fixed length columns that have
a total length of only 112 bytes. The variable length columns that now
store the first_name and last_name are stored in the record after
these 412 reserved bytes for fixed columns that are now dropped. Since
the records always consume all the reserved fixed size, this is quite
wasteful. How do we reclaim it? Rebuild the table:

Physical columns seems to take useless space but can it also affect performance?

Best Answer

Actually, this is a great article. Life would be a way easier if every developer would ask similar questions. Nowadays almost nobody cares about that but to tell the truth the correct answer is: It depends on.

If you have a relatively small software with several million of records, you will be fine with that. Maybe I could recommend you to create a weekly job that does an online rebuild on each table every Sunday to take care of your tables and indexes (of course, please use to Online=On hint or you will have some surprise :D)

If you have a big software with several million of users and plenty of rows, you must ask yourself similar questions.

If you store a row in 400 bytes instead of 100, it does not affect your performance directly. But indirectly, let imagine: you have larger rows, the pages are filled sooner, you will have more page split that takes time and IO. And, of course, your server has to read more pages to get the same amount of rows because each row is larger. So indirectly, it affects your performance.

I think if you keep some rules in your mind, you will avoid the major problems:

every fixed length column takes a place. If you have a nullable int column, it will take 4 bytes. If you have a char(10) column, it will take 10 bytes and so on.
variable length columns take a same amount of bytes as their length multiple by the storage size of one unit. A varchar(10) that stores 'apple' takes 5 bytes (plus some additional info). An nvarchar(10) that stores 'apple' takes 10 bytes.
Very big rows are stored out of the row data (check the Inside SQL Server books).

So my advice is simple:

plan your columns well
avoid to delete or alter a column
maintain your tables

Related Solutions

Sql-server – reliable way to determine when you should run DBCC CLEANTABLE to reclaim space

The solution I'd think of for this problem is to run weekly a job that will run sp_spaceused for all tables in a database and save this data in a table. If there are differences in size for each table greater than..let's say..10%, I would run the dbcc cleantable.

My code to loop through table sizes looks like that:

if OBJECT_ID ('tempdb.dbo.#temp') is not null
    drop table #temp;

if OBJECT_ID ('dbo.BigTables') is not null
    drop table dbo.BigTables;
go

CREATE TABLE [dbo].[BigTables] (
    [table_name] [sysname] NOT NULL,
    [row_count] [int] NULL,
    [col_count] [int] NULL,
    [data_size] [varchar](50) NULL,
    [Date] [datetime] NOT NULL,
    [DBName] [nvarchar](128) NULL
);
GO

CREATE TABLE #temp (
    table_name sysname ,
    row_count int,
    reserved_size varchar(50),
    data_size varchar(50),
    index_size varchar(50),
    unused_size varchar(50)
);
go

INSERT     #temp
EXEC       sp_msforeachtable 'sp_spaceused ''?'''

insert into dbo.BigTables
SELECT     a.table_name,
           a.row_count,
           count(*) as col_count,
           a.data_size,
           getdate() as [Date],
    'MY DB' as DBName
FROM       #temp a
INNER JOIN information_schema.columns b
           ON a.table_name collate database_default
                = b.table_name collate database_default
GROUP BY   a.table_name, a.row_count, a.data_size
ORDER BY   CAST(Replace(a.data_size, ' KB', '') as integer) desc

DROP TABLE #temp;

Select * from dbo.BigTables;

Now you only need to build the logic that will verify what would be the size change through the week and schedule it.

Sql-server – Does IMAGE column affect query performance even if it’s not included in the query

Wait Statistics

Seeing as we have to find out where SQL Server is waiting (if it is actually SQL Server) you will have to have a look at the Wait Statistics.

There is some code you can download from Paul Randal's SQLSkills.com site which provides you with the means to analyse the wait statistics.

Capturing wait statistics for a period of time

Instead of just running the code and modifying the delay period, open up a Query window and execute the first portion of the SQL statement up until the WAITFOR DELAY '00:30:00'; part.
Then execute whatever you have to do.
After the query has executed run the second part of Paul's script to output the differences found. (The part after the WAITFOR DELAY '00:30:00';)

Depending on the wait statistics you find in the results, you can find hints of what may be the issue in the SQL Server Wait Types Library on Paul's site.

Then go from there. Depending on your wait stats, you should see what your SQL Server is waiting for. If you don't find any big issues, then your application might be the limiting factor.

Disk Alignement

Seeing as you had many read aheads without the covering index, you might want to verify that your data disks are formatted with 64k block size. SQL Server reads in Extents which is explained in the Reading Pages information on Microsoft's site. (1 Extent = 8 Pages = 64kB)

You can also find information about disk alignment on Microsoft's Disk Partition Alignment Best Practices for SQL Server. This document will also explain how to retrieve the block size with the command:

fsutil fsinfo ntfsinfo c:

Results will look similar to this (check the bytes per cluster value)

NTFS Volume Serial Number :       0xa2060a7f060a54a7
Version :                         3.1
Number Sectors :                  0x00000000043c3f5f
Total Clusters :                  0x000000000008787e
Free Clusters  :                  0x000000000008746e
Total Reserved :                  0x0000000000000000
Bytes Per Sector  :               512
Bytes Per Cluster :               65536
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x0000000000010000
Mft Start Lcn  :                  0x000000000000c000
Mft2 Start Lcn :                  0x0000000000043c3f
Mft Zone Start :                  0x000000000000c000
Mft Zone End   :                  0x000000000001cf20

Having the MDF/NDF and LDF files on 64k formatted drives can help improve performance regarding read ahead speed.

An appropriate value for most installations should be 65,536 bytes (that is, 64 KB) for partitions on which SQL Server data or log files reside. In many cases, this is the same size for Analysis Services data or log files, but there are times where 32 KB provides better performance. To determine the right size, you will need to do performance testing with your workload comparing the two different block sizes.

You should be able to pinpoint some of your performance issues with these guidelines.

Disclaimer: I am in no way affiliated with Microsoft, Paul Randall, or SQLSkills.com.