Professional SQL Server 2012 Internals and Troubleshooting
Christian Bolton, Rob Farley, Glenn Berry, Justin Langford, Gavin Payne
Format: PDF / Kindle (mobi) / ePub
Hands-on troubleshooting methods on the most recent release of SQL Server
The 2012 release of SQL Server is the most significant one since 2005 and introduces an abundance of new features. This critical book provides in-depth coverage of best practices for troubleshooting performance problems based on a solid understanding of both SQL Server and Windows internals and shows experienced DBAs how to ensure reliable performance. The team of authors shows you how to master the use of specific troubleshooting tools and how to interpret their output so you can quickly identify and resolve any performance issue on any server running SQL Server.
- Covers the core technical topics required to understand how SQL Server and Windows should be working
- Shares best practices so that you know how to proactively monitor and avoid problems
- Shows how to use tools to quickly gather, analyze, and effectively respond to the source of a system-wide performance issue
Professional SQL Server 2012 Internals and Troubleshooting helps you to quickly become familiar with the changes of this new release so that you can best handle database performance and troubleshooting.
However, the SQLOS is core to SQL Server’s architecture so you need to understand why it exists and what it does to complete your view of how SQL Server works. In short, the SQLOS is a thin user-mode layer that sits between SQL Server and Windows. It is used for low-level operations such as scheduling, I/O completion, memory management, and resource management. To explore exactly what this means and why it’s needed, you first need to understand SQL Server’s execution model. Execution Model When an application authenticates to SQL Server it establishes a connection in the context of a session, which is identified by a session_id (in older versions of SQL Server this was called a SPID).
It shows not only the lock resource (as per the list of lock resources described next) and lock mode (also discussed later), but also the ID of the database in which the lock is located, plenty of information to identify the resource that has been locked, whether the lock was actually granted (it could be being converted, or it could be waiting), how many locks of that type are on the resource, the sessionid, and more. There are a lot of columns, as described in Table 6-1. TABLE 6-1: Currently Active Lock Resource Information Returned by sys.
Grant Order In any system, particularly as the number of processor threads grows, a number of requests will be queued for a particular page. For example, a number of pages might be inserting data into a table while others are reading that data, and the data may need to be moved from disk, and so on. For a page that has no latches on it, the first process that wants a latch will be granted one. That’s straightforward; but when more processes start coming along, the behavior is slightly different. A KP latch will skip the queue completely — unless there is a DT latch on the page, a KP latch will jump ahead and keep it alive.
The parse tree is an internal representation of the query. If parsing detects any errors, the process stops and the errors are returned. Algebrizing The algebrization stage is also referred to as the binding stage. In early versions of SQL Server this stage was referred to as normalization. During algebrizing, SQL Server performs several operations on the parse tree and then generates a query tree that is passed on to the Query Optimizer. The steps performed during algebrizing follow this model: Step 1: Name resolution — Confirms that all objects exist and are visible in the security context of the user.
A row is at the bottom of this hierarchy and belongs to a page; the page itself belongs to a table, and so on. The lock hierarchy is covered in detail in the next section, but the purpose of the intent lock is to indicate at the higher levels of the lock hierarchy that a part of the resource has a lock held against it. This allows checks to be performed at the level at which a lock is requested, which is a great performance optimization. If an exclusive row lock is acquired on a table, the page and the table will have intent exclusive locks held against them.