Estimating SQL Server Table Sizes

Microsoft gives guidance for calculating the estimated sizes of your tables and indexes at http://msdn.microsoft.com/en-us/library/ms175991.aspx.  Performing such estimates is an important step in determining required server capacity.

Unfortunately, Microsoft’s guidance consists of long textual descriptions of the steps that need to be followed to perform the estimations.  It takes some time to interpret the instructions given by Microsoft and perform the calculations.

With that in mind, I put together a spreadsheet to help me perform the calculations.  To use the spreadsheet, I simply fill out the spreadsheet with the total number of rows in the table and details of the columns (data types and sizes) in the table and/or index.  In my experience, the estimates provided by the spreadsheet are within 5-10% of the actual sizes of the tables/indexes, which has been good enough for my needs.

You can download the spreadsheet here.

To complete the spreadsheet, simply enter the requested data into the cells with black borders.  For example, enter the expected number of rows in the table in the cell labeled “Rows in Table’”.  Enter the number of columns in the table in the cell labeled “# of Columns”.  Continue until you have specified all of the information about the table or index.  The spreadsheet uses the information to calculate the total space needed for the table or index.

To help you get started, the spreadsheet includes sample data for a table that contains 1699488 rows and has six columns.  This table has two integer columns (one of which is the primary key), two datetime columns, one tinyint column, and one nvarchar column.  The average size of the data in the nvarchar column is 12 characters.  Compare these table specifications to the sample data in the spreadsheet for an example of how to map the specifications for your table/index into the spreadsheet.

Note that while Microsoft provides guidance for estimating sizes of tables, clustered indexes, and non-clustered indexes, this spreadsheet only handles tables and clustered indexes.  I originally intended to add the calculations for non-clustered indexes, but simply did not find the time.  So, I am providing the spreadsheet as-is.  It should, at the very least, provide a good starting point for estimating table and index sizes.

St. Louis Day of .NET 2011 – Reflections and Session Materials

The annual St. Louis Day of .NET conferences was held August 5th and 6th.  It was the largest yet, with over 800 attendees. 

The keynote address, given by Microsoft’s Jay Schmelzer, focused on Visual Studio Lightswitch.  Lightswitch is billed as a simplified development tool for creating business applications.  I have read comparisons to Microsoft Access, and from what I saw at the keynote, those comparisons are apt.  I’ll give more detail about the keynote and the other sessions that I attended in future posts.

Interestingly, I did not hear anyone talking about Lightswitch after the keynote.  I don’t think I’ve ever been at a conference where there was no further discussion of the keynote.

Among the sessions that I attended were three focusing on MVC… none were great, or even particularly good.  This was very disappointing, as it was a topic of particular interest to me.  On the other hand, sessions on Entity Framework 4.1, SOLID design principles, Powershell, Inversion of Control containers, and jQuery Plug-ins were good.  Two speakers that particularly impressed me were Steve Bohlen (SOLID and Inversion of Control) and Muljadi Budiman(Objective-C).

I found it interesting (but not surprising) that sessions on Objective-C and jQuery were filled to overflowing, despite their focus on non-Microsoft technology and/or vendor-agnostic technologies.  I was happy to see that the developer community, while supportive of Microsoft, isn’t blindly supportive.

One last note… there were a large number of sponsors of this year’s event, and I was impressed by the number that were hiring or looking for contract workers. One hopes that this bodes well for developers looking for work in the St. Louis area.

As far as I know, the conference web site (http://www.stlouisdayofdotnet.com/2011/) doesn’t provide a way for speakers to deposit their session materials.  Because of that, slides and sample code have been slowly trickling out on personal blogs and web sites.  I have been attempting to accumulate as many links to those materials as I can find, and those links are listed below.  Full session descriptions can be found here.

Application Development With HTML5
https://bitbucket.org/bsatrom/applicationdevelopmentwithhtml5/downloads

Architecting Applications the Microsoft Way
http://www.notsotrivial.net/blog/post/2011/08/08/Slides-from-my-St-Louis-Day-of-NET-2011-sessions.aspx

Architecting for Massive Scalability
https://skydrive.live.com/view.aspx?cid=8299A1C60E094D2A&resid=8299A1C60E094D2A%211168

Building jQuery Plugins
https://github.com/irobinson/Presentation-Resources

Building Office 365 Web Parts
http://blog.beckybertram.com/Lists/Posts/Post.aspx?ID=153

Common UX Pitfalls
http://www.slideshare.net/bnunnally/common-ux-pitfalls

Consuming Data From Many Platforms: The Benefits of OData
https://skydrive.live.com/view.aspx?cid=8299A1C60E094D2A&resid=8299A1C60E094D2A%211168

Customizing BlogEngine.NET
http://geekswithblogs.net/scottkuhl/Default.aspx

Design Quality: Learning From the Mistakes of the US Auto Industry
http://www.slideshare.net/jtruemper/design-quality-learning-from-the-mistakes-of-the-us-auto-industry

DotNetNuke Jumpstart
https://github.com/irobinson/Presentation-Resources

Embrace the Buzz – Building Modern DotNetNuke Extensions
https://github.com/irobinson/Presentation-Resources

Get Funcy With C# and F#
https://github.com/dahlbyk/Presentations/tree/master/GetFuncy

IE9 Pinned Sites
http://geekswithblogs.net/scottkuhl/Default.aspx

Introduction to User Experience Methods
http://www.slideshare.net/dgcooley/introduction-to-ux-methods

Javascript is not C#
http://www.codebadger.com/blog/post/2011/08/06/Slides-available-from-2011-Day-of-NET-presentations.aspx

Jumpstart: SharePoint Web Part Development
http://blog.beckybertram.com/Lists/Posts/Post.aspx?ID=153

Keynote: Lightswitch
http://channel9.msdn.com/Blogs/Dan/Jay-Schmelzer-Introducing-Visual-Studio-LightSwitch

Lightswitch Jumpstart
http://www.architectnow.net/Resources.aspx

The “Little Pitfalls” of C#/.NET
http://www.blackrabbitcoder.net/archive/2011/08/11/c.net-little-wonders–pitfalls-the-complete-collection-so-far.aspx

The “Little Wonders” of C#/.NET
http://www.blackrabbitcoder.net/archive/2011/08/11/c.net-little-wonders–pitfalls-the-complete-collection-so-far.aspx

Making $$$ with Windows Phone 7
https://skydrive.live.com/view.aspx?cid=8299A1C60E094D2A&resid=8299A1C60E094D2A%211170

The Making of the Day of .NET Web Site with Orchard CMS
http://www.notsotrivial.net/blog/post/2011/08/08/Slides-from-my-St-Louis-Day-of-NET-2011-sessions.aspx

Overview of Windows Azure Marketplace DataMarket
https://skydrive.live.com/view.aspx?cid=8299A1C60E094D2A&resid=8299A1C60E094D2A%211172

Powershell: Not Your Father’s Command Line
http://blogs.technet.com/b/matthewms/p/powershell.aspx

Reactive Extensions (Rx) in .NET
http://www.architectnow.net/Resources.aspx

Refactoring to a SOLID Foundation
http://unhandled-exceptions.com/blog/index.php/2011/08/11/st-louis-day-of-netthats-a-wrap/

Startup Tips and Tricks
http://www.architectnow.net/Resources.aspx

Taming Your Dependencies With Inversion of Control Containers
http://unhandled-exceptions.com/blog/index.php/2011/08/11/st-louis-day-of-netthats-a-wrap/

The Three Tasty Flavors of Entity Framework 4.1
http://www.codebadger.com/blog/post/2011/08/06/Slides-available-from-2011-Day-of-NET-presentations.aspx

Unit Testing Patterns and Anti-Patterns
http://unhandled-exceptions.com/blog/index.php/2011/08/11/st-louis-day-of-netthats-a-wrap/

What’s Wrong With This Picture?
http://openlightgroup.net/Blog/tabid/58/EntryId/183/Whats-Wrong-with-this-Picture-St-Louis-Day-of-Net-Presentation-Info.aspx

Why You Should Care the Sony PSN Was Hacked
https://github.com/k0emt/Presentations

Updating My Dev Environment: VS2010SP1, MVC3, EF4.1, and NuGet vs the Web Platform Installer.

Earlier this week I realized that I had let the development environment on my laptop get a bit out of date.  Sure, it was running Visual Studio 2010, but I had not applied Service Pack 1, nor any of the big buzzy out-of-band add-ons from the first half of  2011.

So, I sat down to install Visual Studio 2010 Service Pack 1, ASP.NET MVC3, Entity Framework 4.1, and the Nuget package manager.  I learned a few things along the way.  Following is a recap of the upgrade process, along with some notes about what I know now that I didn’t know before.

Step 1) Install Visual Studio 2010 Service Pack 1.  This installation was a painless hands-off procedure, although it did take 2-3 hours to complete.  My dev tools are set up in virtual machines (one for Visual Studio 2010 and related tools, one for WAMP platform tools), so running the upgrade against the virtual machine probably slowed things down.  Regardless, it was a lengthy install.

Step 2) Go to nuget.org and install the NuGet Visual Studio extension.  Again, this was painless; I downloaded the installation package, chose Run, and that was it.  NuGet is a package manager for easily installation and updating of open source libraries and tools in Visual Studio.  Package managers are already popular in other environments, but are a relatively new addition to the Microsoft development stack.

Step 3) Start Visual Studio.  So far, so good.

Here is where things started to go awry.  My own fault, as you will see.  I followed the instructions found at http://docs.nuget.org/docs/start-here/Using-the-Package-Manager-Console to attempt to install the latest versions of ASP.NET MVC and Entity Framework.  I could have just done this via the usual download-and-install process, but I wanted to give NuGet a spin.

Step 4) From the Tools menu, use NuGet by selecting Library Package Manager and then clicking Package Manager Console.  This opens a PowerShell dialog within Visual Studio.

Step 5) Type "get-help NuGet" to view all of the available NuGet commands.

Step 6) Type "Get-Package -ListAvailable" to view a list of all available NuGet packages.  Locate the “Entity Framework” package in the list.

Step 7) Type "Install-Package Entity Framework".  Oops… FAIL. I received the following error:

    Install-Package : The current environment doesn’t have a solution open.
    At line:1 char:16
    + Install-Package <<<<  EntityFramework
        + CategoryInfo          : InvalidOperation: (:) [Install-Package], InvalidOperationException
        + FullyQualifiedErrorId : NuGetNoActiveSolution,NuGet.PowerShell.Commands.InstallPackageCommand 

What I’d uncovered was a fundamental misunderstanding on my part about what NuGet does.  My desire was to add capabilities to Visual Studio itself, not just to a single project or solution.  Turns out that’s not what NuGet is for. 

Step 8 ) Type "get-help Install-Package" to get more information about what I had just attempted.  Included in the description of the “Install-Package” command is the statement "Installs a package and its dependencies into the project."  Ok then, that confirms it… NuGet is for individual projects, not for the development environment as a whole.

My initial misunderstanding of NuGet stems from my only previous Package Manager experience, which was with the “apt” system that is a part of Ubuntu Linux..  Using that Package Manager, I was able to add capabilities and tools to the operating system… things like MySQL and Apache.  This, in fact, is somewhat similar to the Web Platform Installer from Microsoft.  But not NuGet.  NuGet is actually more like RubyGems, which are used to adding capabilities to software projects written in Ruby. 

So, I learned some useful information, but as far as installing ASP.NET MVC and Entity Framework, it was back to the drawing board

Step 9) Shut down Visual Studio.

Step 10) Install ASP.NET MVC 3.  Speaking of the Web Platform Installer, I hadn’t used it a while, so I decided to go that route to install ASP.NET MVC 3.  I browsed to http://www.microsoft.com/web/gallery/install.aspx?appid=MVC3, and clicked Install Now.  The Web Platform Installer itself installed first, followed by ASP.NET MVC 3.  This took much longer than expected to install.  Overall, though, Web Platform Installer was as painless to use as I remembered.  Nice.

Step 11) Install IIS Express 7.5.  Though not part of the original plan, I decided to also install IIS Express 7.5.  I did this using a "normal" download-and-install procedure.  Easy.  As advertised, very lightweight.

Step 12) Install Entity Framework 4.1.  I did this via the download-and-install process (choosing to "save" and then run the downloadable installer… Web Platform Installer was not used).  This installation was very quick when compared to MVC3.  I’m not sure if that was due to the NON-use of Web Platform Installer to install EF, or if EF is simply a much more lightweight install than MVC.

While looking for the correct Entity Framework 4 installer package I noticed the following statement on the ADO.NET blog: "Note: The NuGet package only includes the EF 4.1 runtime and does not include the Visual Studio item templates for using DbContext with Model First and Database First development." So, that provides further clarification that NuGet adds the necessary run-time libraries to specific projects, but does NOT add full development tools to VS.

Step 13) Start Visual Studio and verify all installs.  To confirm that everything was as it should be, I checked the following:

    Step 13a) File/New/Project shows ASP.NET MVC3 Web Application as a project type.
    Step 13b) EntityFramework 4.1.0.0 shows up as an option when adding a reference to a project.
    Step 13c) There is a new option for Add Library Package Reference when adding a reference to a project.
    Step 13d) On the property page for a a web project, there is a new option for running under IIS Express.

That’s it.  My Visual Studio 2010 development environment is now up-to-date. Along the way, I gained greater insight into NuGet, the hot new Package Manager add-on for Visual Studio  In particular, I learned that if you’re looking to upgrade your entire development environment, and want to use a package-manager type application, Web Platform Installer is your choice.  On the other hand, if you want to add libraries to a project on which you are working, Nuget is the tool to use.

Follow

Get every new post delivered to your Inbox.