Browse by Tags

• Backup
• Bulk Import/Export
• Concurrency
• Conferences 2007
• Data Compression
• Database Mirroring
• DBCC
• DBCC CHECKDB Series
• Disaster Recovery
• FILESTREAM
• General
• Index Fragmentation Series
• On-Disk Structures
• Restore
• Service Level Agreements
• Shrink
• SQL Express
• SQL Server 2008
• SQL Server IO
• SQL Server Security
• TechEd 2006
• TechEd 2008
• TempDB
• VarDecimal Storage Format
• VLDB Maintenance

• Update on minimal logging for SQL Server 2008

  As part of SQL2008 release, you can get minimal logging when bulk importing into a HEAP using the following command insert into <heap> with (TABLOCK) select * from <source> This was detailed in the blog entry http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/06/minimal-logging-changes-in-sql-server-2008-part-1.aspx Read More... Posted Monday, August 11, 2008 6:58 PM by Sunil Agarwal | 1 Comments Filed under: Bulk Import/Export

• Does data compression lead to more fragmentation?

  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 Read More... Posted Wednesday, August 06, 2008 5:40 AM by Sunil Agarwal | 1 Comments Filed under: Data Compression

• Enabling FILESTREAM post SQL2008 Setup - a Known Issue in SQL Config Manager

  In SQL Server 2008 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 Read More... Posted Monday, June 09, 2008 9:24 PM by OmelJ - MSFT | 2 Comments Filed under: SQL Express, SQL Server 2008, TechEd 2008, FILESTREAM

• TempDB:: Table variable vs local temporary table

  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 Read More... Posted Sunday, March 30, 2008 9:56 PM by Sunil Agarwal | 6 Comments Filed under: TempDB

• Overhead of Row Versioning

  Last week I was teaching a class on snapshot isolation and we discussed the overheads of snapshot isolation. There are three main overheads that you need to be aware of when you enable snapshot isolation read-committed-snapshot on a database. First, each Read More... Posted Sunday, March 30, 2008 12:41 AM by Sunil Agarwal | 7 Comments Filed under: Concurrency

• CHECKSUM and Tempdb

  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/29/Enabling-CHECKSUM-in-SQL2005.aspx . Read More... Posted Sunday, March 23, 2008 10:52 PM by Sunil Agarwal | 0 Comments Filed under: TempDB, General

• Minimal Logging changes in SQL Server 2008

  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 Read More... Posted Thursday, March 06, 2008 5:12 AM by Sunil Agarwal | 4 Comments Filed under: Bulk Import/Export

• FILESTREAM Configuration and Setup Changes in SQL Server 2008 February CTP

  FILESTREAM is disabled by default in SQL2008. Before you can start to use FILESTREAM, you must enable FILESTREAM on the instance of the SQL Server Database Engine. Enabling/configuring FILESTREAM is a bit different from configuring other SQL features Read More... Posted Monday, March 03, 2008 5:14 PM by OmelJ - MSFT | 5 Comments Filed under: SQL Server 2008, TechEd 2008, FILESTREAM

• Example: Index fragmentation with insert/updates, measuring it and fixing it

  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/Index+Fragmentation+Series/default.aspx Read More... Posted Saturday, March 01, 2008 1:11 AM by Sunil Agarwal | 0 Comments Filed under: Index Fragmentation Series

• Bulk Import Optimizations (Minimal Logging)

  In the previous blog I had listed all bulk import optimizations. Now, I will describe each of these optimizations in detail in separate blog entries. Let me first start with minimal logging. By the way minimal logging is also called bulk-logging but the Read More... Posted Tuesday, February 05, 2008 3:01 AM by Sunil Agarwal | 7 Comments Filed under: Bulk Import/Export

• What are the Bulk Import Optimizations?

  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 Read More... Posted Monday, February 04, 2008 7:12 PM by Sunil Agarwal | 1 Comments Filed under: Bulk Import/Export

• Why Bulk Import?

  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 Read More... Posted Monday, February 04, 2008 6:50 PM by Sunil Agarwal | 1 Comments Filed under: Bulk Import/Export

• Compression Strategies

  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 does the SQL Server not provide an option to compress Read More... Posted Sunday, January 27, 2008 4:20 AM by Sunil Agarwal | 2 Comments Filed under: Data Compression

• Details on page compression (page-dictionary)

  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 Read More... Posted Friday, January 18, 2008 9:02 PM by Sunil Agarwal | 1 Comments Filed under: Data Compression

• Details on PAGE compression (column-prefix)

  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, Read More... Posted Friday, January 18, 2008 2:38 AM by Sunil Agarwal | 2 Comments Filed under: Data Compression