How table design can impact your SQL Server performance?

Leave a comment (5) Go to comments

Question :

Does Row Size matters in SQL Server performance?

Can a single byte can impact your sql server performance ?

How to create optimized table at design time?

Should we consider the row size during table design ?

Answer

Yes, a single byte (1 byte) can badly impact your SQL Server Performance, it might get double your I/O operations. Yes we all know, SQL Server stores the data in pages. Page is the smallest storage unit inside the SQL server of size 8 KB. Continues 8 data pages makes a extent.

When SQL Server reads or writes data, the data page needs to be in the memory. If data page is not present in the buffer pool, SQL Server needs to read the page from the disk. Alternatively, when data page has been modified, SQL Server needs to write this page to the disk. Physical I/O is one of the most expensive operations. So it leads to the basic conclusion:
Bigger Row Size = Less Rows Per Page = More data pages per table = More I/O Operations = Performance Degradation

Let’s understand directly from a real example, how 1 single byte can double your I/O performance. To make this simple, we are going to create two tables,


  • First table, which is optimized, has 4039 bytes in a row, this row size can fit two rows in page.
  • Other table, which is Non-Optimized, has 4040 bytes , this row size doesn’t fit 2 rows in a page, thus every page stores a single record.
use tempdb

go

create table Fixed_Lenght_Row_Table_Optimised

(

col1 char(1000),

col2 char(1000),

col3 char(1000),

col4 char(1039)

)

create table [Fixed_Lenght_Row_Table_Non-Optimised]

(

col1 char(1000),

col2 char(1000),

col3 char(1000),

col4 char(1040)

)

declare @i int

set @i = 0

begin tran

 while @i < 10000

 begin

      insert into Fixed_Lenght_Row_Table_Optimised values (1,2,3,4)

      insert into [Fixed_Lenght_Row_Table_Non-Optimised] values (1,2,3,4)

      select @i = @i + 1

 end

commit

go

sp_spaceused Fixed_Lenght_Row_Table_Optimised

go

sp_spaceused [Fixed_Lenght_Row_Table_Non-Optimised]

go

Drop table Fixed_Lenght_Row_Table_Optimised

Drop table [Fixed_Lenght_Row_Table_Non-Optimised]

This show the following results, which clearly proves, that the first table (Optimized table) have 10000 records which are stored in 40000 KB but second table (un optimized table) stored the same 10000 records now are stored in 80000 KB, which is just doubled.

How table design can impact your SQL Server performance? table size in sql server table optimisation table design optimization table design optimisation sql server row size sql server optimized way to creating table sql server optimised way to creating table sql server create optimised table Should we consider the row size during table design ? optimized for creating a table optimised for creating a table How to create optimized table at design time? how table design can improve your performence how table design can improve your performance Does Row Size Matters ? create optimized table create optimised table Can a single byte can impact your sql server performance Calculate the data row size

Tags : Does Row Size Matters ?, sql server optimized way to creating table,table size in sql server, optimized for creating a table,create optimized table,sql server create optimised table,table optimisation, table design optimization, table design optimization,sql server row size,Calculate the data row size,how table design can improve your performance,Can a single byte can impact your sql server performance,How to create optimized table at design time?,Should we consider the row size during table design ?

EOF - How table design can impact your SQL Server performance?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

5 Comments.

  1. Thanks! Great post! I wrote a post in my own blog (www.ittraining.com.ar) referencing your blog. I’m a Software Engineer from Argentina and I work as a C# .NET developer and I use SQL Server 2008 Express as database engine. Recently I wrote a post about SQL Server 2008 Express installation process which is very interesting for developers trying to deploy a software that uses SQL Server.
    Btw, when I design tables I often use nvarchar(50) when I’m sure that I’m not going to use more than 13 (Argentinian legal code of 13 characters). This is a very big issue for storage size and performance. After reading you post I have decided to correct it.
    Thanks!

  2. Gopinath Srirangan

    Hi,
    Thanks for the update.
    Still few more clarification needed.

    We have 8192 bytes of page. 96 bytes is used for header information.In my above example 16 bytes will be reserved for offset storage. Still we have 8080 bytes remaining. But how i am unable to store last record in the same page since it takes only 8000 bytes? Can you pls explian how the memory is used in my example and the reason how the last insert use next page.

    Thanks in advance.
    Gopi

  3. Gopinath Srirangan

    Hi,
    Thanks for posting this. I need some more clarification on this. I am creating a table of row size char(1000) as below.
    create table Optimised(id char(1000))

    i am insert values 1,2,3,4…..
    After inserting value 7, i sp_spaceused shows reserved:16KB and data:8KB which is expected. But when i insert next value ’8′ then sp_spaceused shows reserved:24KB and data:16KB. How the last insert increase the page size to 16KB since still i have used only 8000 bytes which is in 8KB?

    Can you pls explain this..

    Thanks
    Gopi

    • SQL data is stored on 8K data pages (8060 bytes are technically available) and rest is meta data. For Example first 96 bytes is for header, After that page contains the set of data rows and ends with offset array. 2 things are worth to mention. First – each row uses 2 extra bytes for offset storage.

      Just for your information, First 2 bytes contain header information. Next 2 bytes store the length of the fixed width data following by the data. Next are 2 bytes for the number of columns. Next, null bitmask (1 byte per 8 nullable columns). It follows by 2 bytes store number of variable width columns, variable width column offset array (2 bytes per variable column) and variable width data. And finally there is the optional 14 bytes pointer to the version store. This one is used for optimistic isolation levels (snapshot, read committed snapshot), MARS, etc.

      First of all, fixed width data always uses space even when null. Variable width data uses 2 extra bytes for offset storage for every value. Null values of variable width data are not stored although there are still 2 bytes in the offset array unless null values are last in the row. Sounds confusing? Yes, a little bit, but this is how it manage.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.