Administering Storage, I/O, and Partitioning in Microsoft SQL Server 2012
- What's New for DBAs When Administering Storage on SQL Server 2012
- Storage Hardware Overview
- Designing and Administering Storage on SQL Server 2012
- Designing for BLOB Storage
- Designing and Administrating Partitions in SQL Server 2012
- Data Compression in SQL Server 2012
- Summary
- Best Practices
This chapter is from the book
This chapter is from the book
This chapter is from the book
Storage and I/O (input/output) within SQL Server 2012 requires special attention because it is perhaps the most likely subsystem to experience suboptimal performance when left to the default settings. As a consequence, the well-prepared DBA will want to spend a bit of extra time in planning, configuring, and tuning SQL Server’s storage and I/O settings.
This chapter introduces the fundamental concepts around SQL Server storage and I/O configuration and tuning. This overview includes hardware-related topics, such as hard disks, RAID, and SAN. Going beyond overview, this chapter will delve into the best practices and industry standards for SQL Server administrator activities, such as the number and placement of database and transaction log files, partitions, and tempdb configuration.
Most features related to SQL Server storage and I/O are configured and administered at the database level. That means that administrative tasks in SQL Server Management Studio will typically focus on database-level objects in the Object Explorer, as well as on database properties. Toward the end of this chapter, an important I/O performance-enhancing feature, data compression, is also discussed.
Even though the chapter introduces and explains all the administration and configuration principles of the SQL Server 2012 Storage Engine, you will occasionally be directed to other chapters for additional information. This is a result of the Storage Engine feature being so large and intricately connected to other features.
What’s New for DBAs When Administering Storage on SQL Server 2012
SQL Server 2012 enhances the functionality and scalability of the Storage Engine in several significant ways. The following are some of the important Storage Engine enhancements:
- SQL Server 2012 introduces a powerful new way to accelerate data warehouse workloads using a new type of index called a columnstore index, also known as a memory-optimized xVelocity index. Columnstore indexes can improve read performance on read-only tables by hundreds to thousands of time, with a typical performance improvement of around tenfold. Refer to Chapter 5, “Managing and Optimizing SQL Server 2012 Indexes,” for more details on this new type of index.
- SQL Server has long supported creation, dropping, and rebuilding indexes while online and in use by users, with a few limitations. SQL Server 2012 eliminates some of those restrictions, such that indexes containing XML, varchar(max), nvarchar(max), and varbinary(max) columns may be handled while the index is still online and in use.
- Partitioning in SQL Server 2012 has been enhanced, allowing up to 15,000 partitions by default, whereas older versions were limited to 1,000 partitions by default.
- SQL Server’s storage methodology for storing unstructured data, FILESTREAM, has been improved. FILESTREAM allows large binary data, such as JPEGs and MPEGs, to be stored in the file system, yet it remains an integral part of the database with full transactional consistency. FILESTREAM now allows the use of more than one filegroup containing more than one file to improve I/O performance and scalability.
- The Database Engine Query Editor now supports IntelliSense. IntelliSense is an autocomplete function that speeds up programming and ensures accuracy.
To properly maximize the capabilities of SQL Server storage and I/O, it is important to understand the fundamentals about storage hardware. The following section introduces you to most important concepts involving storage and I/O hardware and how to optimize them for database applications.