Heap size estimation

In term of table size estimation, it comes to three parts, namely clustered index, non-clustered index and heap. Heap basically is a table without any index. This write up only covers the size estimation of a nuke table (Heap).

You can use the following steps to estimate the amount of space that is required to store data in a heap:

  1. Specify the number of rows that will be present in the table:

    Num_Rows = number of rows in the table

  2. Specify the number of fixed-length and variable-length columns and calculate the space that is required for their storage:

    Calculate the space that each of these groups of columns occupies within the data row. The size of a column depends on the data type and length specification.

    Num_Cols = total number of columns (fixed-length and variable-length)

    Fixed_Data_Size = total byte size of all fixed-length columns

    Num_Variable_Cols = number of variable-length columns

    Max_Var_Size = maximum total byte size of all variable-length columns

  3. Part of the row, known as the null bitmap, is reserved to manage column nullability. Calculate its size:

    Null_Bitmap = 2 + ((Num_Cols + 7) / 8)

    Only the integer part of this expression should be used. Discard any remainder.

  4. Calculate the variable-length data size:

    If there are variable-length columns in the table, determine how much space is used to store the columns within the row:

    Variable_Data_Size = 2 + (Num_Variable_Cols x 2) + Max_Var_Size

    The bytes added to Max_Var_Size are for tracking each variable-length column. This formula assumes that all variable-length columns are 100 percent full. If you anticipate that a smaller percentage of the variable-length column storage space will be used, you can adjust theMax_Var_Size value by that percentage to yield a more accurate estimate of the overall table size.

    If there are no variable-length columns, set Variable_Data_Size to 0.

  5. Calculate the total row size:

    Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4

    The value 4 in the formula is the row header overhead of the data row.

  6. Calculate the number of rows per page (8096 free bytes per page):

    Rows_Per_Page = 8096 / (Row_Size + 2)

    Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row. The value 2 in the formula is for the row’s entry in the slot array of the page.

  7. Calculate the number of pages required to store all the rows:

    Num_Pages = Num_Rows / Rows_Per_Page

    The number of pages estimated should be rounded up to the nearest whole page.

  8. Calculate the amount of space that is required to store the data in the heap (8192 total bytes per page):

    Heap size (bytes) = 8192 x Num_Pages

This calculation does not consider the following:

  • Partitioning

    The space overhead from partitioning is minimal, but complex to calculate. It is not important to include.

  • Allocation pages

    There is at least one IAM page used to track the pages allocated to a heap, but the space overhead is minimal and there is no algorithm to deterministically calculate exactly how many IAM pages will be used.

  • Large object (LOB) values

    The algorithm to determine exactly how much space will be used to store the LOB data types varchar(max), varbinary(max), nvarchar(max),text, ntext xml, and image values is complex. It is sufficient to just add the average size of the LOB values that are expected and add that to the total heap size.

  • Compression

    You cannot pre-calculate the size of a compressed heap.

  • Sparse columns

What does DBA do right after SQL Server installation?

What usually does an experienced DBA do after installation of SQL Server?

Here is the list by my best knowledge,

Step 1: Install the Service Pack, Hotfixes and Cumulative Updates

Step 2: Configure SQL Services
SSCM(SQL Server Configuration Manager)
Services started?
Which account to start the service?
Restart Service on SQL Server and SQL Agent

Step 3: Configure Default Directories
Should be done in installation

Step 4: Configure Default Database Properties
Step 5: Configure tempdb Database
Step 6: Instance Level config
Configuring Parameter Values
Configuring SQL Server Network
Step 7: Configure Security
Step 8: Configure Error Logs
Step 9: DB mail
Step 10: SQL Agent
Job History Size

Who disabled my Reporting Service Instance Property in Management Studio?

Did you ever try to use SQL Server Management Studio(SSMS) to connect your Reporting Service engine? I believe you may connect SQL Server Database Engine or SSAS much more often but fewer people try this way to SSRS.

This thread is to walk through this option using SSMS to connect your reporting service.

To start, launch your SSMS in your start menu. In the SSMS connect dialog, choose “Reporting Services” in your service type and fill in the RS server name, also let’s choose windows authentication for this case then hit “connect” button. Yes, it takes a little bit longer time to connect if this is your first time to do so. Now connected!

First question you may have is what we can manage for SSRS in SSMS. In SSMS, we can manage all instance-level options including jobs, Security roles, shared schedules and the most important one, as least to me, is the property setup for SSRS. Now, let’s right click SSRS instance, in the following popup menu, please check out your last item: property. Surprise? Yes, it surprised me for a couple of times when I started to use this tool in the earlier time. The “property” item is, by default, disabled as shown in below picture.

SSMS-SSRS1How to turn it on? Easy and tricky! Shut down your SSMS first. Go to the Start menu and find the shortcut of SSMS, right click on it, choose “Run as administrator” in the popup menu. And connect to SSRS again. What do you see? It is working this time, right?!


Now, you ask, if the login I am using to connect SSRS is the member of Administrator Group, do I still need to run as administrator? The answer is firmly YES! Further, let’s check what we can setup in property window. A lot: Portal name, Enabling “My Reports”, execution options, History, logging and security, I like it, you know what. For some options, this is the only approach you can setup SSRS instance-level properties.


Alright, guess you got it. Have fun with SSRS property setup. See you next time.


SQL Server Versions and Editions


SQL Server 6.5 or 7.0 probably is the first version we started to use as a 12-year DB/BI engineer. Before this version, I know SQL Server 4.2 is the first version Microsoft independently developed. Before 4.2, Microsoft collaborating with Sybase started to developed this database product but I don’t know what version they called. As you see Sybase was a big player in SQL Server development, today’s DB products from Sybase like Sybase IQ still look similar with SQL Server to some degrees.

Following the earlier versions(4.2, 6.0, 6.5 and 7.0), it comes with SQL Server 2000, which named after year. Then as you probably know, SQL Server 2005, 2008, 2008R2, 2012 and now you have the latest one SQL Server 2014. If you examine the SQL Server instance in Management Studio carefully, it is not hard to find actually the internal version number following this pattern XX.YY.ZZZZ though the commercial one is named after years.

What XX.YY.ZZZZ tells us? XX is the major version number, for example, SQL Server 2000’s major number is 8 and SQL Server 2005 is 9 and so on. YY stands for the minor version number, taking SQL Server 2008R2 as an instance, it looks like 10.50.ZZZZ, here 50 is the minor version number for SQL Server 2008R2. The last piece ZZZZ means build number. You probably know in development tools, this number goes self-incremental every time you build the solution. With the comprehensive automation test, if build number 1300 is the most stable one then the release version will choose this build number, this is why you can see “10.0.1600” is the SQL Server 2008 first release version number.

A Quick summary for SQL Server public versions and internal versions

RTM(no SP) SP1 SP2 SP3 SP4
SQL Server 2014

Codename: Hekaton

SQL Server 2012,

Codename: Denali

11.0.2100.60 11.0.3000.0 11.0.5058.0
SQL Server 2008 R2,

Codename: Kilimanjaro

10.50.1600.1 10.50.2500.0 10.50.4000.0 10.50.6000.34
SQL Server 2008,

Codename: Katmai

10.0.1600.22 10.0.2531.0 10.0.4000.0 10.0.5500.0 10.0.6000.29
SQL Server 2005,

Codename: Yukon

9.0.1399.06 9.0.2047 9.0.3042 9.0.4035 9.0.5000
SQL Server 2000,

Codename: Shiloh

8.0.194 8.0.384 8.0.532 8.0.760 8.0.2039
SQL Server 7.0,

Codename: Sphinx

7.0.623 7.0.699 7.0.842 7.0.961 7.0.1063

Version-related abbreviations

When you download the SQL Server installations, you may see some shortened words. Below is a list of shortened names confused me before.

CTP Community Technology Preview (beta release)
RC Release Candidate
RTM Released To Manufacturing; It is the original, released build version of the product, i.e. what you get on the DVD or when you download the ISO file from MSDN.
CU Cumulative Update; Cumulative updates contain the bug fixes and enhancements–up to that point in time–that have been added since the previous Service Pack release and will be contained in the next service pack release. Installation of the Cumulative Update is similar to the installation of a Service Pack. Cumulative Updates are not fully regression tested.
SP Service Pack; much larger collection of hotfixes that have been fully regression tested. In some cases delivers product enhancements.
GDR General Distribution Release; GDR fixes should not contain any of the CU updates.
QFE Quick Fix Engineering; QFE updates include CU fixes.

All SQLServer service packs are cumulative, meaning that each new service pack contains all the fixes that are included with previous service packs and any new fixes.


In addition to different versions, Microsoft also composes the same version into different editions to target various audience. Express edition is a free edition, which has the limitation of DB size less than 2GB. Above the Express edition, there is a standard edition, which is mostly designed for small or middle size companies. Above the standard edition, there is a more powerful edition called developer edition, which is considered to be used for developers. Traditionally, the most powerful edition is Enterprise edition, which includes all features. When SQL 2012 came to the market, more editions are introduced like Business Intelligence, Enterprise Core and Data Center editions.

They are about it in term of versions and editions. Looking for more details for all components, check out below link,  http://support.microsoft.com/kb/321185/en-us


Display Folder in Tabular Model

SSAS 2012 has been released for almost 2 years. More and more organizations as I heard have already embraced this fabulous BI tool. But maybe, you, as well as I, are still used to some advanced features in Multiimensional Cube like “Measures Display Folder”, “Drill Through Designing” …Admittedly, all these advanced features, by design, are not available so far for tabular Cube. But here is the good news for you–“BIDS Helper”.

Yes, BIDS helper can help you design all these features in Tabular model.

After BIDS Helper intsallation–just copying and unlocking, launch your VS/BIDT tool again. You will see BIDS helper in “Help–>About’ window as below. (BTW, I am a little bit proud of this. Because, vey early, I tried this tool when it was a beta version and I found out a bug, if you dont unlock these DLLs, it wont work. Then I reported this error to BIDS helper team, and they listed this in notes on Codeplex–a little piece of my contribution.)

BIDS about

Then, right click your “model” file, more features are available now as below.


Have  a qucik try on “Tabular Display Folers…”, it pops up a window for you to desing the display folder, simple, easy and clean.

bids windowActually, more useful features are provided by BIDS helper. Check this out: http://bidshelper.codeplex.com/, have fun.

Is someone still out there with you, my friend–SQL Server Service Broker?

It has been eight years since Microsoft first introduced this asynchronous message queue into SQL Server 2005 database. Before I am writing this blog entry, I checked SQL Saturday events all over the world for the most recent year. Not surprisingly, only four sessions were submitted and one was finally selected. It reminds me that, in a sense, this product is not popular among developers and enterprises sadly though I heard some good implementation as well before, for example, mySpace and Newegg implemented Service Broker for their e-business platforms, wondering whether they are still with Service Broker.
Am I a pioneer to use this product? Maybe. At the time I worked in Microsoft 7 years ago, I was tasked to implement the asynchronous logging function leveraging Service Broker for the project Vulcan. I struggled for less examples I can found online. Years after, I planted this technology in Starcite for implementing asynchronous aggregation for the order status change. It worked well finally after we evolved the implementation a couple of time (I hope our performance QA agrees with me at this point), wondering whether my baby is still there or not. Today, once again, someone asked me to share some experiences on Service Broker applications. I am glad to take this evangel task and check out some new updates in the latest version SQL Server 2012.
Comparing the tremendous changes in SQL 2012 BI and Always-on in Database engine, Service Broker in SQL2012 cannot be the super star but still gives us hopes.
First of all, messages can be sent to multiple target services (multicast).
This change is reflected in the syntax of “SEND”. SEND command can trig multiple conversions in one time as below.
SEND ON CONVERSATION (@dialog_handle1, @dialog_handle2, @dialog_handle3)
MESSAGE TYPE [//AllDBs/OrderMsg]

Next, queues expose the message queued time. In the system DMV, it exposes an aggregation showing how long a message has been in the queue.
Additionally, poison messages handling has been enhanced. The queue creation statement has the ability to turn off the poison messages handling.
Last but not least, high availability is redesigned to combine with Always-on group, which was supported by database mirroring.

Glad to see these changes in the newest version as I like this product because of a number of reasons but you need to catch up to be the super start in your family:). Next time, I will share a case study of Service Broker.