Microsoft SQL Server 2012 Internals (Developer Reference)
Kalen Delaney, Jonathan Kehayias, Benjamin Nevarez
Language: English
Pages: 982
ISBN: 0735658560
Format: PDF / Kindle (mobi) / ePub
Dive deep inside the architecture of SQL Server 2012
Explore the core engine of Microsoft SQL Server 2012—and put that practical knowledge to work. Led by a team of SQL Server experts, you’ll learn the skills you need to exploit key architectural features. Go behind the scenes to understand internal operations for creating, expanding, shrinking, and moving databases—whether you’re a database developer, architect, or administrator.
Discover how to:
- Dig into SQL Server 2012 architecture and configuration
- Use the right recovery model and control transaction logging
- Reduce query execution time through proper index design
- Track events, from triggers to the Extended Event Engine
- Examine internal structures with database console commands
- Transcend row-size limitations with special storage capabilities
- Choose the right transaction isolation level and concurrency model
- Take control over query plan caching and reuse
the original location. Later, this chapter examines forwarding pointers. Looking at this from another angle, using variable-length columns can sometimes improve performance because it can allow more rows to fit on a page. But the efficiency results from more than simply requiring less disk space. A data page for SQL Server is 8 KB (8,192 bytes), of which 8,096 bytes are available to store data. (The rest is for internal use to keep track of structural information about the page and the object to
the space isn’t used now and can be used by a new row. In addition to space on pages not being reclaimed, empty pages in heaps frequently can’t be reclaimed. Even if you delete all the rows from a heap, SQL Server doesn’t mark the empty pages as unallocated, so the space isn’t available for other objects to use. The dynamic management view (DMV) sys.dm_db_partition_stats still shows the space as belonging to the heap table and sys.dm_db_database_page_allocations shows the is_allocated column
internal structures of indexes are a large part of finding the right balance between having too many and too few indexes and, most importantly, of having the right indexes. Tools for analyzing indexes To help you understand index structures fully, this section uses a few tools whose appropriate use you need to get a feel for. This section also focuses on an overview of the options for execution, as well as some tips and tricks. The details on analyzing various aspects of the output can
an index on computed columns, the six previously mentioned SET options must first have the correct values set. Here’s an example with an incorrect setting for ANSI_NULLS: SET ANSI_NULLS OFF; GO CREATE TABLE t1 (a INT, b as 2*a); GO CREATE INDEX i1 ON t1 (b); GO Because one of the SET options doesn’t have the correct value when the table is created, you get this message when you try to create the index: Server: Msg 1935, Level 16, State 1, Line 2 Cannot create index. Object 't1' was created
request is inserted and decremented when the request is completed. This number doesn’t indicate the state of the I/Os. load_factor. The internal value that indicates the perceived load on this scheduler. This value determines whether a new task should be put on this scheduler or another scheduler. It’s useful for debugging purposes when schedulers appear to be unevenly loaded. In SQL Server 2000, a task was routed to a particular scheduler; in SQL Server 2012, the routing decision is based on