74 posts from: SQL Server Storage Engine
Vardecimal storage format and upgrade to SQL2008 RTM or SQL2008R2
(Indexed 2010-08-10):
As you all may know that starting with SQL2008 RTM, we have deprecated vardecimal storage format feature. The reason is that ROW compression, introduced starting SQL2008 RTM extends the variable storage format to other data fixed length types such as INT, CHAR, DATETIME including DECIMAL datatype. This deprecation is aligned with the standard deprecation policy of SQL Server. This means that when you upgrade from SQL2005/SP2 to SQL2008 and you are using vardecimal storage format, it will just wo...(truncated)...
Common Concurrency Issues with Applications
(Indexed 2010-06-04):
As part of my role as a program manager in SQL Server Engine, one of the areas I am reponsible for is the concurrency related features in SQL Server. If you have been involved with database applications as a develeoper or as a DBA in keeping the application running, I am sure you will agree that blocking is one of the most interesting and challenging problems. I have seen cases where an application was running fine yesterday but it starts blocking suddenly. The culprits can be many such as lock ...(truncated)...
Minimal logging and MERGE statement
(Indexed 2010-06-03):
In my earlier blog http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/...(truncated)... we discussued how we can get minimal logging using TF-610 when using TSQL INSERT statement. Interestingly, you can get minimal logging when using MERGE statement as well. Here is a simple example to illustrate it.use bulktestgoalter database bulktest set recovery SIMPLE-- enable the trace flag 610dbcc traceon (610, -1) -- create the staging tablecreate table t_staging (c1 int, c2 int, c3 char (100), ...(truncated)...
Backup compression and Virtual Device Interface (VDI)
(Indexed 2010-02-21):
People often ask if they can get native SQL Server backup compression using VDI interface. The simple answer is 'yes' but there are few things you need to be aware of as described below. You can get details on VDI by clicking link-to-VDI-Specifications.Within the VDI protocol, the backup application specifies a TSQL backup command. This means that the backup application can use any TSQL command line options including the option 'WITH COMPRESSION'. In this case, the SQL Server will send a pre-co...(truncated)...
Performance improvement by orders of magnitude when merging partitions in SQL Server 2008R2
(Indexed 2010-02-03):
I am publishing this Blog on behalf of Hermann Daeubler, our SAP expertScenario:Lets assume we have a table consisting of five partitions and the one in the middle needs to be dropped. In SQL Server we need the following steps to accomplish this task a) Create a non-partitioned target table with the same structure and indexes as the partitioned tableb) Use the switch command to move the data from the middle partition to the new target table. Now the original partition is empty but st...(truncated)...
Improvement in minimizing lockhash key collisions in SQL Server 2008R2 and its impact on concurrency
(Indexed 2010-01-18):
Another improved functionality in SQL Server 2008 R2I am posting this on behalf of Juergen Thomas who has been with SQL Server PM team from 12+ years and is an expert in SAP. Juergen>> In this article Id like to talk about another improvement we made to SQL Server 2008 R2. The improvement pretty much is not noticed since it is buried deep into the SQL Server Engine. There also is nothing to tune about it. It is about the hash key algorithm which is used by SQL Server in its Lock Manager. So let...(truncated)...
Changes to sp_estimate_data_compression_savings stored procedure in SQL2008R2
(Indexed 2009-09-16):
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 question 'Is there a way to know how much savings do I get from each of the two components?". Interestingly, starting with SQL...(truncated)...
A Unicode Compression example
(Indexed 2009-08-17):
Now that we have Unicode compression available in SQL Server 2008R2, let me take a simple example to show you the additional compression that can be achieved on tables with one or more columns of type NCHAR or NVARCHAR.use [AdventureWorksDW2008]go -- the table FactResellerSales is a FACT table with three columns of type -- NVARCAHR types. Let us find the average length of each of these columnsselect AVG( LEN(salesordernumber))from FactResellerSales This returns a value of 7....(truncated)...
Unicode Compression in SQL Server 2008R2
(Indexed 2009-08-17):
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 SQL Server.Problem: As more and more businesses developing global customer base, applications are being developed/modified to use uni...(truncated)...
Customer feedback on Data Compression
(Indexed 2009-08-17):
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 offering in future releases. Here is some of the feedack and the respective rationale. Please feel free to contact me for your suggestions on data compression.(1) Provide Uni...(truncated)...
Update on Data Compression as provided in SQL Server 2008 RTM and links to white papers
(Indexed 2009-08-16):
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 the size of their database significantly leading to reduction in the cost of hardware and storage management. The table below shows the actual space savings that some of the cust...(truncated)...
Update on data compression performance/space-savings and links to published white papers
(Indexed 2009-08-16):
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 the size of their database significantly leading to reduction in the cost of hardware and storage management. The table below shows the actual space savings that some of the cust...(truncated)...
TempDB Monitoring and Troubleshooting: Out of Space
(Indexed 2009-01-12):
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 following suggestions1. Provide a way to control how much TempDB space can be allocated by various applications on an instance of SQL Server. Clearly, this will provide a very go...(truncated)...
TempDB Monitoring and Troubleshooting: DDL Bottleneck
(Indexed 2009-01-12):
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 understand why DDL bottleneck is most commonly related to TempDB and not in user databases. Well, if you think about user database(s), they are created as part of application desi...(truncated)...
TempDB Monitoring and Troubleshooting: Allocation Bottleneck
(Indexed 2009-01-11):
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...(truncated)... 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 caused when allocation structures are accessed by concurrent threads in conflicting modes. Please ...(truncated)...
TempDB Monitoring and Troubleshooting: IO Bottleneck
(Indexed 2009-01-05):
I hope my earlier blogs on TempDB (http://blogs.msdn.com/sqlserverstorageengine/archive/tags/T...(truncated)... 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, dont we all wish it was so but as you may have already experience perhaps one time too many that the performance/configuration problems do happen. In fact, most DBAs/Administrators spend 20-...(truncated)...
Managing TempDB in SQL Server: TempDB Configuration
(Indexed 2009-01-04):
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:1. Where should I create TempDB? 2. What should be the size of TempDB? 3. Single file vs multiple file?Let us tackle each of these questions in Order.Where (i.e. what disks) should I create ...(truncated)...
What is allocation bottleneck?
(Indexed 2009-01-04):
Allocation bottleneck refers to contention in the system pages that store allocation structures. There are three types of pages that store allocation structures as follows PFS (Page Free Space): it tracks the following key information about each page. It uses 1 byte/page and each PFS page stores status for 8088 pages.o Free space availableo If it is allocated or noto Has ghost records (when a row is deleted, it is marked as ghost) GAM (Global Allocation Map): Tracks if a un...(truncated)...
Managing TempDB in SQL Server: TempDB Basics (Version Store: Growth and removing stale row versions)
(Indexed 2009-01-01):
In the previous blog http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12...(truncated)... , I described the logical/physical structure of a row in version store. A natural question that comes up is how these pages are grouped together and how does SQL Server garbage collect these rows. You may recall that if you need to DELETE a large number of rows from a table, an efficient option is to truncate or even drop the table rather than deleting one row at a time. Similarly, the SQL Server ...(truncated)...
Managing TempDB in SQL Server: TempDB Basics (Version Store: Simple Example)
(Indexed 2008-12-31):
In order to understand the version store, let me start with an example. I will use a database that has RCSI (read committed snapshot) and SI (Snapshot Isolation) enabled as it provides more controlled environment to manage versions. When a database is enabled for RCSI/SI, any update of a row will create a row version. This version stays in the version store as long as it is needed. Now, that is a tricky statement. How does SQL Server know when to reclaim the version? Some other questions that m...(truncated)...
Managing TempDB in SQL Server: TempDB Basics (Version Store: logical structure)
(Indexed 2008-12-31):
Now that we know a few things about Version Store, let us now look into its structure to understand how it stores rows from different tables/indexes with different schema. Interestingly, you dont need to look far and the answer is available when you examine the DMV sys.dm_tran_version_store.This DMV shows the full logical structure of the version store. There are two key points to note. First, the version store consists of 8K pages just like data or index pages. These pages exist in the buffer ...(truncated)...
Managing TempDB in SQL Server:TempDB Basics (cont..)
(Indexed 2008-12-22):
In the previous blog http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12...(truncated)... I started the discussion on the TempDB basics and this blog continues it.TempDB Basics: As I indicated, the TempDB is created every time the SQL Server is started, it is tempting let it grow starting with the size initially set in the Model Database. While this works, it will fragment your TempDB database because by default the size of the TempDB will increase in the increments of 10% of i...(truncated)...
Managing TempDB in SQL Server: TempDB Basics (Version Store: Why do we need it?)
(Indexed 2008-12-22):
Version store is a new entity in SQL Server 2005. It is used to store versions of data and index rows. A row version typically is an older copy of the data or an index row and is created to support existing (triggers) and new features (snapshot based isolation levels, MARS and ONLINE index build) in SQL Server 2005. Let me give you an example of row version in each of these contexts as follows Triggers: These operate on DELETED and INSERTED rows as part of DML operations on a table. Bef...(truncated)...
Managing TempDB in SQL Server
(Indexed 2008-12-21):
Over past few of years, I have given TempDB presentation in many conferences and the feedback that I have received on TempDB has been very useful. Most of you understand that TempDB is a critical database that needs to be configured carefully otherwise the performance of your application may suffer. I am not suggesting that the current implementation of TempDB addresses the challenges of every application but SQL Server has made significant improvements starting with SQL Server 2005. Given there...(truncated)...
DML operations on a HEAP and compression
(Indexed 2008-12-21):
In my previous blog http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12...(truncated)... I described how to enable compression on a HEAP. You can enable either ROW or PAGE compression on the heap. Let me now describe the implications of DML operations on a heap. ROW compression: No issues. The updated/inserted data stays ROW compressed though out its life time.PAGE Compression: There are two key points to note (a) when you enable PAGE compression on an index or a heap, SQL Server evalua...(truncated)...
Enabling compression on a HEAP
(Indexed 2008-12-20):
I often get a question how to do enable compression on a table that is a heap (i.e. it does not have a clustered index). Clearly, one could create the clustered index with compression option set to PAGE and then drop the clustered index. This is an expensive operation because Creating a clustered index requires a SORT When you drop the clustered index, internal allocation structure, namely the PFS (Page Free Space) needs to updated to reflect the free space on each page. Note, w...(truncated)...
New update on minimal logging for SQL Server 2008
(Indexed 2008-10-24):
Based on the customer feedback, we have decided to make minimal logging functionality into a btree available to SQL 2008 customers. All you need to do is to enable TF-610 in RTM bits. As a cautionary note, we have seen some slowdown if you are loading data into a btrree using TF-610 on a slower IO subsystem. So please make sure you test it first. For details, please refer to my blog entrieshttp://blogs.msdn.com/sqlserverstorageengine/archive/...(truncated)... thanks[Image]
Update on minimal logging for SQL Server 2008
(Indexed 2008-08-11):
As part of SQL2008 release, you can get minimal logging when bulk importing into a HEAP using the following commandinsert into with (TABLOCK) select * from This was detailed in the blog entry http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03...(truncated)... had similar functionality available when inserting data into btree in SQL Server 2008 but it was removed couple of months back but unfortunately I did not update my blog. I have now removed those blog entries. My apologies for t...(truncated)...
Does data compression lead to more fragmentation?
(Indexed 2008-08-06):
Lately I have been asked how data compression impacts fragmentation (i.e. does it cause more or less fragmentation?). I believe this question is best answered by looking at how does fragmentation occur in the first place and then analyze each of these cases for compressed and uncompressed data.Let us start off with create clustered index on a table so there is no fragmentation to begin with. Now the following operations will cause fragmentation Delete Operaton: This means the pages are...(truncated)...
Enabling FILESTREAM post SQL2008 Setup - a Known Issue in SQL Config Manager
(Indexed 2008-06-09):
In SQL1008 Feb CTP, there is a bug in the WMI provider for FILESTREAM, inside SQL Configuration Manager (select instance, r-click properties and go to FILESTREAM tab). This bug causes the FILESTREAM configuration UI, to fail. This happens only if: 1) you want to enable FILESTREAM after SQL setup 2) another instance of SQL 2005 or SQL 2000 (Pre-SQL2008) is installed on the same machine, for example when you install SQL 2008 (any SKU other than CEC) on a machine running Visual Studio 2008 (w...(truncated)...
TempDB:: Table variable vs local temporary table
(Indexed 2008-03-30):
As you know the tempdb is used by user applications and SQL Server alike to store transient results needed to process the workload. The objects created by users and user applications are called user objects while the objects created by SQL Server engine as part of executing/processing the workload are called internal objects. In this blog, I will focus on user objects and on table variable in particular. There are three types of user objects; ##table, #table and table variable. Please refer to B...(truncated)...
Overhead of Row Versioning
(Indexed 2008-03-29):
Last week I was teaching a class on snapshot isolation. One question was the overhead of row versioning on the data or index row. When you enable Snapshot Isolation or Read-Committed-Snapshot option on the database, the SQL Server starts adding a 14 byte overhead to each row to keep the following information:XTS (transaction sequence number). It takes 6 bytes. This is used for marking the XSN that did the DML operation on the rowRID (row identifier) that points to the versioned row. It takes 8 b...(truncated)...
CHECKSUM and Tempdb
(Indexed 2008-03-23):
You may recall that starting with SQL Server 2005, you have an option available to enable CHECKSUM on the user databases. For details, please refer to http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06...(truncated)... fact, any new database created in SQL2005 have CHECKSUM enabled automatically but it does not happen to databases that are upgraded from previous versions of SQL Server. For the upgraded databases from SQL Server versions earlier than SQL Server2005, you will need to en...(truncated)...
Minimal Logging changes in SQL Server 2008 (part-3)
(Indexed 2008-03-23):
So far we had discussed how minimal logging changes impact when you are moving data from one table to another table. Now let us look at how does this change more conventional bulk import. As you will see, conventional bulk import takes advantage on these changes as well but these changes are only useful when importing into a btree as conventional bulk import already provides minimal logging for heaps. Here are the series of scenarios that I tried (1) Inserting into an HEAP. No changes in thi...(truncated)...
Minimal Logging changes in SQL Server 2008 (part-2)
(Indexed 2008-03-23):
In the previous example, I described minimal logging while moving data from a source table into a heap. You may recall that it requires a X table lock to get minimal logging. Now, I will show you what happens when you move data from a source table into a btree. In order to get minimal logging with btree, the only requirements areLike minimal logging for heap, the database must be set to bulk-logged or simple recovery modelThe input data must be sorted in the index key order. Note, it does not re...(truncated)...
Minimal Logging changes in SQL Server 2008 (part-1)
(Indexed 2008-03-06):
Please refer to the earlier post for the background information on minimal logging. Starting with SQL Server 2008, the minimal logging has been enhanced. These enhancements are available to regular TSQL Insert as well. One of the key customer scenario was to be able to transfer data from a staging table to the target table. The only choice the customers had was to use SELECT * INTO from . The limitation of this solution was that customers had no control on DDL aspect of the target table. WIth t...(truncated)...
FILESTREAM Configuration and Setup Changes in SQL Server 2008 February CTP
(Indexed 2008-03-03):
Filestream configuration has been changed for the February CTP to properly separate the Windows Built-in admistrator and SQL administrator roles. In the November CTP, enabling/disabling FILESTREAM required Built-in Admin privilege. Enabling Filestream is now made up of 2 admin layers: 1- The Windows configuration layer handles Windows related setup changes required for FILESTREAM. This requires built in administrator privilege. FILESTREAM settings in the SQL Configuration Manager (SQL-CM) r...(truncated)...
Example: data fragmentation with insert/updates, measuring it and fixing it
(Indexed 2008-03-01):
This blog shows a very simple example to illustrate data fragmentation, measuring it, seeing behind the scene data and steps to address it. create database indextestgo use indextestgo drop table t_cigo create table t_ci (c1 int, c2 char (100), c3 int, c4 varchar(1000))go -- load the datadeclare @i intselect @i = 0while (@i < 1000) begininsert into t_ci values (@i, 'hello', @i+10000, replicate ('a', 100))set @i = @i + 1end -- find fragmentation in the simple HEAP loadselect avg_fragmentation...(truncated)...
Example: Index fragmentation with insert/updates, measuring it and fixing it
(Indexed 2008-03-01):
This blog shows a very simple example to illustrate data fragmentation, measuring it, seeing behind the scene data and steps to address it. I recommend reading the overall series on index fragementation at http://blogs.msdn.com/sqlserverstorageengine/archive/tags/In...(truncated)... -- TSQL Scriptcreate database indextestgo use indextestgo -- create the index after loading the datadrop table t_cigo create table t_ci (c1 int, c2 char (100), c3 int, c4 varchar(1000))go -- load the datadeclare ...(truncated)...
Bulk Import Optimizations (Minimal Logging)
(Indexed 2008-02-05):
In the previous blog I had listed all bulk import optimizations. Now, I will describe each of these optimizations in a separate blog entry. Let me first start with minimal logging. By the way minimal logging is also called bulk-logging but the correct term is 'minimal logging' while bulk logging refers more the database recovery model that enables minimal logging. But minimal logging can also be enabled by SIMPLE recovver model as well. Under minimal logging, the SQL Server does not log individu...(truncated)...
What are the Bulk Import Optimizations?
(Indexed 2008-02-04):
Bulk import provides an optimized insert path that minimizes this overhead with following optimizations Minimal Logging: under this logging mode, individual rows are not logged and only the changes to page allocation structures are logged. This reduces the amount of logging significantly. BU Locking: a special mode table level locking only available for bulk import path. Using this locking mode, each bulk import thread acquires a table level BU lock while still allowing concurren...(truncated)...
Why Bulk Import?
(Indexed 2008-02-04):
Most IT shops using SQL Server need to load/import large amount of data obtained from external sources or from another Database Server into SQL Sever. It is typcially done using the optimized insert path provided by SQL Server through Bulk Insert and BCP commands. Customers often wonder what optimizations are available through Bulk Insert/BCP and under what conditions? In this series of Blog posts, I will walk you through all bulk import optimizations with examples. To understand the optimized i...(truncated)...
Compression Strategies
(Indexed 2008-01-27):
In the previous blogs, I had described the data compression in SQL Server 2008 and its implications on CPU and IO. One of the question that keeps coming up is Should I compress my database? Or Why SQL Server does not provide an option to compress the whole database. I am not suggesting that you may never want to compress the whole database, but here are some basic guidelines that you should keep in mind. First, why compressing the whole database blindly is not such a good idea? Well, to give yo...(truncated)...
Details on PAGE compression
(Indexed 2008-01-18):
In the previous blog, I had mentioned that the PAGE compression is used to minimize the data redundancy in columns in one or more rows on a given page. You may wonder what exactly SQL Server does under the PAGE compression cover? With PAGE compression, the SQL Server eliminates two types of data redundancy known as column-prefix compression and dictionary compression. I will describe column-prefix compression in this blog followed by dictionary compression in the next.Under column-prefix compre...(truncated)...
Details on page compression (page-dictionary)
(Indexed 2008-01-18):
In the previous blog, I described column-prefix compression that is done as part of PAGE compression. The other component of PAGE compression is the page-dictionary. As the name indicates, this dictionary is specific to a page and the page can be data page or a leaf-index page. The non-leaf pages of an index are ROW compressed even if you choose to compress the index with PAGE compression option. It was a design decision to reduce the CPU impact (it is more expensive to decompress a row with PAG...(truncated)...
Details on PAGE compression (column-prefix)
(Indexed 2008-01-18):
In the previous blog, I had mentioned that the PAGE compression is used to minimize the data redundancy in columns in one or more rows on a given page. You may wonder what exactly SQL Server does under the PAGE compression cover? With PAGE compression, the SQL Server eliminates two types of data redundancy known as column-prefix compression and dictionary compression. I will describe column-prefix compression in this blog followed by dictionary compression in the next.Under column-prefix compre...(truncated)...
Data Compression will be available in CTP-6
(Indexed 2007-11-20):
Thanks to all who have contacted me for their interest in the data compression feature.Unfortunately, data compression feature is NOT available in the just released CTP-5 which is now available on the Microsoft Download Center as SQL Server 2008 Community Technology Preview (CTP) November 2007. Data compression will be released with CTP-6. Also, Data Compression is an Enterprise Only feature. [Image]
Estimating the space savings with data compression
(Indexed 2007-11-13):
Like I had indicated in my previous blog, it is recommended that you estimate compression savings on the object of interest before actually enabling compression as enabling compression is an expensive operation. To show how to estimate data compression, I have created a very simplistic and definitely not realistic example to show case data compression estimates for the following table. create table t1_big (c1 int, c2 int, c3 char(8000))go I will show you the compression estimates both for ROW ...(truncated)...
Why not use compressed disk files or disk volumes
(Indexed 2007-11-12):
When we think of compressing the database, one of the first question that pops up is why not used the compressed volume? You may know that Windows OS has supported compression of individual files, folders and the entire NTFS volumes since Windows 2000. Given this, you may wonder why dont we just create databases on compressed volume and get the disk savings? Well, there are couple issues with it as follows Generally the compression methods are effective on large chunks of data. So you ma...(truncated)...
Types of data compressions in SQL Server 2008
(Indexed 2007-11-12):
SQL Server deploys two strategies to compress the data First, it stores all fixed length data types in variable length format. If you recall, SQL Server 2005/SP2 provided a new variable length storage format decimal and numeric. Please refer to the series of blogs vardecimal-storage-format for details. SQL Server 2008 extends this to all fixed length data types. Some examples of fixed length data types are integer, char, and float data types. One important point to notes is that even ...(truncated)...
Types of data compression in SQL Server 2008
(Indexed 2007-11-12):
SQL Server deploys two strategies to compress the data First, it stores all fixed length data types in variable length format. If you recall, SQL Server 2005/SP2 provided a new variable length storage format decimal and numeric. Please refer to the series of blogs vardecimal-storage-format for details. SQL Server 2008 extends this to all fixed length data types. Some examples of fixed length data types are integer, char, and float data types. One important point to notes is that even ...(truncated)...
Why not use compressed disk files or disk volumes?
(Indexed 2007-11-12):
When we think of compressing the database, one of the first question that pops up is why not used the compressed volume? You may know that Windows OS has supported compression of individual files, folders and the entire NTFS volumes since Windows 2000. Given this, you may wonder why dont we just create databases on compressed volume and get the disk savings? Well, there are few issues with it as follows Generally the compression methods are effective on large chunks of data. So you may n...(truncated)...
When is too much success a bad thing?
(Indexed 2007-10-30):
I was talking to a customer the other day who had an interesting problem: Successful backups.Specifically, their problem had to do with the success messages that SQL backup puts in the SQL errorlog and the system event log.Seems like a nice, friendly thing to do right? Drop a note that your backup was successful, note the LSN, etc.The problem comes when you do a LOT of backups. In this case, the customer was doing 1 T-log backup per minute on EACH of 5 production databases. That adds up to a...(truncated)...
Data compression techniques and trade offs
(Indexed 2007-09-30):
Ok, now that we have sort of agreed that data compression is a good thing, you may wonder how SQL Server compresses the data, what does this compression mean to my data and to my workload? Link to the previous blog entry: http://blogs.msdn.com/sqlserverstorageengine/archive/2007/09...(truncated)... If you are a theory nut or crave for mathematics, you can browse the web or read the related books and find many fine techniques to compress the data but note, not all compression techniques are appl...(truncated)...
Data Compression: Why Do we need it?
(Indexed 2007-09-29):
As announced in Tech-Ed 2007, data compression is a new and exciting feature targeted to be available in SQL Server 2008. This is a huge topic to be covered in one BLOG post, I have decided to break it into a series of posts, each building on the previous ones. If you are interested discussions on any specific topic on data compression, please send me a note and I will make sure that I include a discussion on it in my subsequent BLOGs. This is the first in the series of BLOG entries for data co...(truncated)...
Splits and unions...
(Indexed 2007-07-30):
Hey folks,This is my last blog post on the SQL Server Storage Engine blog as I've made the decision to leave Microsoft and join Kimberly running her company, SQLskills.com. This lets me indulge my passion for teaching and consulting full-time and lets me work with my best friend. My last day at Microsoft is August 31st and from September 1st I'll be blogging at http://www.sqlskills.com/blogs/paul/. I'd like to thank all of you for reading my posts here, commenting and emailing me, and I really h...(truncated)...
What happens to non-clustered indexes when the table structure is changed?
(Indexed 2007-06-07):
Heres a topic thats cropped up several times during Q&As at TechEd this year what happens to non-clustered indexes when changes are made to the underlying table? Are they always rebuilt or not? Before we get into that discussion, Ill give you a little background. One way to describe a non-clustered index is whether it is a covering index or not. A covering index is one which has all the table columns necessary to satisfy a query, and so there is no need to go back to the underlying table (which...(truncated)...
Database mirroring questions from TechEd: failovers and partner timeouts
(Indexed 2007-06-06):
This year at TechEd there's been even more interest in database mirroring than last year - Kimberly's Always-On Demo-Fest yesterday had a whole bunch of questions during the first demo on mirroring and 50% of the booth questions I've answered have been on it too. It seems that more people are getting to the stage of actually implementing SQL Server 2005 this year, which kind of makes sense now that we've put out two Service Packs and companies have had a chance to implement and test new applicat...(truncated)...
Katmai == SQL Server 2008 == available to start playing with!
(Indexed 2007-06-05):
Very exciting!!! On Monday at TechEd we announced the official name of the next version of SQL Server - Microsoft SQL Server 2008 and released the first public CTP (Community Technology Preview) of it for people to start playing with and evaluating. Here are some links for you:"SQL Server 2008 Product Overview" whitepaperCTP 3 for downloadDavid Campbell's TechEd session on SQL Server 2008 webcastSQL Server 2008 press releaseSQL Server 2008 MSDN ForumsSQL Server 2008 websiteCheck it out, download...(truncated)...
SQL Server's 'black-box' flight recorder
(Indexed 2007-06-03):
So I learned something about SQL Server at TechEd today. In Kimberly and Bob Beauchemin's pre-con, Kimberly mentioned that SQL Server has a 'black-box' trace, similar to an aircraft flight-recorder, which I'd never heard of. It's an internal trace that has the last 5MB of various trace events and it's dumped to a file when SQL Server crashes. This can be really useful if you're troubleshooting an issue that causing SQL Server to crash or someone or something is telling SQL Server to shutdown and...(truncated)...
The Ins and Outs of Offline Files
(Indexed 2007-06-02):
First blog post of the year from TechEd! Well, Kimberly and I arrived a day early hoping to chill out in the sun by the pool but contrary to all expectations the weather sucks today its actually much better in Seattle. Instead we're hibernating in our room blogging and having a Blokus re-match last time we were in Orlando in March she beat me soundly so I need to get my revenge :-) Over the next few posts I want to touch on some of the issues that have been causing confusion on the MSDN Disast...(truncated)...
White Paper: Reducing Database Size by Using Vardecimal Storage Format
(Indexed 2007-05-29):
Hermann Daeubler and Sunil Agarwal have co-authored this white paper. Hermann is a Program Manager with the SQL team and is a SAP expert. Hermann was involved with vardecimal storage format team from the very beginning to guide us with the customer scenarios and the performance implications/trade-offs. This paper provides a general overview of vardecimal storage format usage scenarios, restrictions, database migration to SQL Server 2005/SP2 in the context of vardecimal, space savings and the im...(truncated)...
First TechEd of the year coming up!
(Indexed 2007-05-15):
TechEd US is in Orlando this year - see here for details and here to register.We're doing a bunch of HA and maintenance related sessions - here's the list:Sunday 3rd June: Full day pre-conference on Leveraging SQL Server Always-On Technologies to Achieve High Availability and Scalability with Kimberly and Bob Beauchemin. I'll also be there all day. See Kimberly's TechEd blog post here for more details. This one's filling up fast so make sure you don't miss out.Monday 4th June: Chalk-talk: SQL 20...(truncated)...
Naked Tour of Australia
(Indexed 2007-05-06):
ok - a little explanation is required.At the last MVP Summit here in Redmond, I met up with a bunch of the Australian MVPs, most of whom run SQL Server User Groups. I came up with the idea of doing a series of remote user group presentations from Redmond. The trouble is that Australia's waaaay ahead of Redmond in terms of timezones, so most of the presentations will be at midnight or 1am for me. This means I'll be doing them from my office at home in the middle of the night, and I don't have a w...(truncated)...
Quick list of VLDB maintenance best practices
(Indexed 2007-04-30):
One evening last week I sat down with Kimberly for 5 minutes to come up with a top-of-our heads list of VLDB maintenance concerns for a company migrating a multi-TB database to SQL Server 2005. This isn't in any way based on the VLDB survey I've been doing (see previous posts) but is a common-sense list of things that everyone should do. People really liked the list so I'm posting it here. Maybe we should turn this into a book???Hope this helps.Have page checksums turned onMake sure auto-stats u...(truncated)...
What's the difference between database version and database compatibility level?
(Indexed 2007-04-26):
I had a question this week from someone who'd heard me say at SQL Connections (paraphrasing) "database compatibility level is mostly about query parsing" and was having trouble trying to forcibly attach a 2005 or 7.0 database to a 2000 server.His confusion is between database compatibility level and database version. Here's a quick explanation of the difference.Database versionThe database version is a number stamped in the boot page of a database that indicates the SQL Server version of the mos...(truncated)...
And an example corrupt 2000 database to play with
(Indexed 2007-04-20):
As promised in my earlier post of an example corrupt 2005 database, here's one I've just created for SQL Server 2000. The attached WinZip file contains a backup of a simple 2000 database called 'broken' . It has a simple table called 'brokentable' (c1 int, c2 varchar(7000)) with one row in it. The table has a single data page with page ID (1:75) that I've corrupted so the page header is corrupt. When you try selecting from the table, you should get a 605 error that kills the connection. See the ...(truncated)...
How can you tell if an index is being used?
(Indexed 2007-04-20):
Whenever Im discussing index maintenance, and specifically fragmentation, I always make a point of saying Make sure the index is being used before doing anything about fragmentation. If an index isnt being used very much, but has very low page density (lots of free space in the index pages), then it will be occupying a lot more disk space than it could do and it may be worth compacting (with a rebuild or a defrag) to get that disk space back. However, usually theres not much point spending resou...(truncated)...
Example corrupt database to play with and some backup/restore things to try
(Indexed 2007-04-17):
I've been asked several times over the last few weeks for an example corrupt database to play with, and for testing logic built around DBCC CHECKDB.The attached WinZip file contains a backup of a simple 2005 database called 'broken' (I can do a 2000 one too if there's enough demand). It has a simple table called 'brokentable' (c1 int, c2 varchar(7000)) with one row in it. The table has a single data page with page ID (1:143) that I've corrupted so the page checksum is corrupt.This means you can ...(truncated)...
How to avoid using shrink in SQL Server 2005?
(Indexed 2007-04-16):
Late night blog post to round out spring-break vacation... A number of customers Ive spoken to in the last few weeks have been making use of database or file shrink in situations where they dont really need to. There are few recurring scenarios Ive seen:Deleting a lot of data and then taking a backup how to make the backup smaller without running shrink?Emptying a file before removing itMoving a file/filegroup/database to read-onlyRunning ALTER INDEX REBUILD and then running shrink to reclaim ...(truncated)...
SP2 Maintenance Plan bugs fixed
(Indexed 2007-04-13):
From the tools team here in Redmond:We recently posted updates to address an issue in two SP2 Maintenance Plantasks. The KB article describes the symptoms as follows:FIX: The Check Database Integrity task and the Execute T-SQL Statement taskin a maintenance plan may lose database context in certain circumstances inSQL Server 2005In Microsoft SQL Server 2005 Service Pack 2 (SP2), the following tasks in amaintenance plan may lose database context in certain circumstances:. Check Database Integrity...(truncated)...
VLDB Maintenance Practices and Problems?
(Indexed 2007-04-09):
Its survey time again. Ill be doing lots around VLDB maintenance in the coming months so Id like to get more info from all of you about whats happening in the field. Ill be working on Katmai features, strategizing about Katmai+, blogging on maintenance topics, planning lectures, including a session for TechEd VLDB Maintenance Q&A and much more. I have questions about your maintenance practices (e.g. backups, disaster recovery, fragmentation, file growth/shrink, stats, and so on). Additionally,...(truncated)...
SSWUGtv interview with Paul and Kimberly
(Indexed 2007-04-06):
While we were at the SQL Server Connections conference in Orlando last week, Kimberly Tripp and I did a half-hour interview for SSWUGtv with Stephen Wynkoop. We cover a bunch of stuff around HA, database maintenance and board games.The addictive game we discuss that we took with us is called Blokus - well worth trying out, but be warned... Kimberly did get her revenge that evening when she trounced me over a series of games. Now dominoes is a different story entirely... :-)The interview is live ...(truncated)...
How does your schema affect your shrink run-time?
(Indexed 2007-03-29):
For part two of my short series on data file shrinking, I want to look at how elements of your schemas can cause shrink to take much longer to run. In SQL Server 2005, three things in your schema will drastically affect the run-time of data file shrink. LOB dataBy LOB data, I mean any large-value data-type (e.g. text, image, varchar(max), XML). The problem here is with the way that LOB values are stored. Theyre usually stored off-row, which means theyre stored in separate text pages from the tab...(truncated)...