- SQL Server Management Studio
- SQL Server 2005 Infrastructure Management
- Operations Management Tools
- SQL Server 2005 Remote Management Features
- SQL Server Monitoring
- Managing Very Large Databases
- SQL Server Replication Enhancements
- High Availability for the Masses
- General Data Availability
- Indexed View
- Common Language Runtime and Database Administrator
- Business Intelligence and the Database Administrator
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
General Data Availability
Microsoft has two new features that help applications connect to the database, which is referred to as general database availability:
- Table partitioning. Table partitioning is the ability to break a table into smaller subtables and then use them as if they were one table. This allows for better query performance and a more manageable database.
- Snapshot isolation. This is a new transactional locking scheme that keeps readers from blocking writers and gives data users a consistent point-in-time view of the database. This implementation of snapshot isolation overlaps with the ANSI standard for isolation levels.
Before we delve into the details of table partitioning, you should have a better understanding why partitioning is interesting. Going back to basic database development, database tables are designed to store all an entity’s attributes. For example, your customers table contains the relevant customer information. In the sales table, the order header and order detail tables together contain the relevant characteristics of an order. Over time, these tables become large. Also, the tables’ access patterns are inconsistent. The customer table receives relatively fewer updates than the order detail table. Additionally, organizations are now finding that the use of business intelligence and reporting are requiring older data to be kept in active systems for longer periods of time. After a point, the sheer size of the table is detrimental to query performance. So what makes a table large? The number of rows and the number of bits per row. The first factor is consequential to the query processor. You might conclude that a large table is one that doesn’t perform well. You might also add that a table that requires extended periods of time to properly maintain the indexes could be a large table. The simple goal of partitioning is to make the table more manageable by breaking it into smaller pieces. SQL Server 2005 supports only one kind of partitioning—range partitioning. A type of partitioning called vertical partitioning breaks columns into partitions; this is supported through distributed partition views. Testing and using partitioning has many benefits:
- Greater ease of management. Sliding window partitions can be added and removed.
- Better query performance for partitioned tables via increased parallelism on multi-CPU systems.
- Large-scale deletions and insertions can be greatly improved.
- Creation of relational data warehouses is simplified.
Snapshot Isolation
Snapshot isolation provides a locking mechanism that prevents readers from blocking writers on systems with a high level of mixed workload. In a perfect world, data would be divided by usage: a read database and a write database. This seems logical as a model, but in fact it is difficult to do. Transactions systems focus on inserts and updates, using stored procedures or dynamic Transact-SQL to insert new information. The read-only-type database is heavy on indexes and views, with queries that require significant caching and computational horsepower. Snapshot isolation is implemented as an optimistic locking strategy. DBAs have fine-grained control, allowing for usage of snapshot isolation at the query or stored procedure level. Snapshot isolation needs to be turned on at the database level. Inside SQL Server, snapshot isolation creates a read-consistent version of the data and places it in the TempDB.
Here are some cases in which you might need to use snapshot isolation:
- A large retail company records all transactions in a database and updates inventory levels in real time. Employees need to execute reports and ad hoc queries against this database. The DBA can set the isolation level to Snapshot to allow the reporting and queries to run in parallel with the inventory application.
- A DBA needs to increase the consistency of aggregates such as AVG, COUNT, and SUM, index intersections, and index joins without escalating read scans to a higher isolation level.
Snapshot isolation uses a row-versioning mechanism that stores the version of the data being queried at the time of the connection. The SQL-99 standard defines the following isolation levels, all of which SQL Server supports:
- Read uncommitted (the lowest level, where transactions are isolated only enough to ensure that physically corrupt data is not read)
- Read committed (Database Engine default level)
- Repeatable read
- Serializable (the highest level, where transactions are isolated from one another)
The new implementation of read-committed isolation level uses row versioning if the READ_COMMITTED_SNAPSHOT database option is set to ON. If the option is set to OFF, the read-committed isolation level uses the same type of locking as in earlier versions of SQL Server.