Changes to sp_estimate_data_compression_savings stored procedure in SQL2008R2

When you compress an object (index or table or partition), there are two components of space savings that you get. First component is fragmentaton (i.e. the original object might have been fragmented). The object gets degragmented as part of doing compression so you get some space savings. Second component is actual data compression savings. Many customers have asked the… Read more

A Unicode Compression example

Now that we have Unicode compression available in SQL Server 2008R2 as described in, let me take a simple example using AdventureWorksDW database to show you the additional compression that can be achieved on tables with one or more columns of type NCHAR or NVARCHAR. use [AdventureWorksDW2008] – the table FactResellerSales is a FACT table with… Read more

Unicode Compression in SQL Server 2008R2

In my previous blog, I had mentioned that unicode compression will be available as part of next SQL Server release named SQL 2008R2. You can down load the CTP2 version http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx  to play with it. This is a new exciting addition to the data compression offering.  Let me describe the problem and how it has been addressed in… Read more

Customer feedback on Data Compression

As I described in my previous blog, the data compression feature has been very successful. We do appreciate all the feedback that we have received as this provides us a way to know how customers have been using the feature and the challenges they are facing. Based on the feedback, we plan to improve our data compression… Read more

Update on data compression performance/space-savings and links to published white papers

It has been a while since I blogged about data compression so I thought it will be good to provide an update on data compression usage within SQL Server community. I am happy to say that the Data compression feature has been a tremendous success in SQL Server 2008 with customers.  Many customers have been able to reduce… Read more

TempDB Monitoring and Troubleshooting: Out of Space

One of the key challenges in TempDB is that it is a common resource for all applications running on an instance and any misbehaving application or rouge user command can take up all the space in TempDB bringing down other applications with it. In my discussions with customer during various conferences, I often hear of… Read more

TempDB Monitoring and Troubleshooting: DDL Bottleneck

This blog continues the discussion on the common issues in TempDB that you may need to troubleshoot. In previous blogs, I discussed how to identify and troubleshoot IO and allocation bottleneck in TempDB. In this blog, I will describe how to indentify DDL bottleneck in TempDB and to troubleshoot it. It will be useful to… Read more

TempDB Monitoring and Troubleshooting: Allocation Bottleneck

This blog continues the discussion on the common issues in TempDB that you may need to troubleshoot. In the blog http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/05/tempdb-monitoring-and-troubleshooting-io-bottleneck.aspx, we discussed how to identify and troubleshoot IO bottleneck in TempDB. In this blog, I will describe how to indentify allocation bottleneck and to troubleshoot it. As you may recall, the allocation bottleneck is… Read more

TempDB Monitoring and Troubleshooting: IO Bottleneck

I hope my earlier blogs on TempDB (http://blogs.msdn.com/sqlserverstorageengine/archive/tags/TempDB/default.aspx)  have given you a good background on the objects contained in TempDB and its configuration and have deployed your workload in production and it runs for ever without any glitches. Oh, really? Well, don’t we all wish it was so but as you may have already experienced,… Read more

Managing TempDB in SQL Server: TempDB Configuration

In my previous blogs, I described the types of objects in TempDB and how they are managed. I hope that it provided you with a good working knowledge of TempDB. Now the next question is how do I configure the TempDB for my production workload? In this context, there are three common questions as follows:… Read more