Allocation Page: A SQL Server page used to hold metadata about other SQL Server pages. Some examples of allocation pages are the Page Free Space page, the Global Allocation Map page, and the Secondary Global Allocation Map page.
Bottleneck: Something that prevents SQL Server or a SQL Server application from running optimally. Often, this is a hardware limitation, such as insufficient CPU resources, a lack of RAM, not enough network bandwidth, or slow I/O. If a bottleneck exists in a server, it can prevent the other components of the server from running at their full capacity. As a DBA, one of your goals is to identify and remove as many server bottlenecks as you can in order to boost performance.
Branch Node: Another name for an intermediate node in an index. Branch, or intermediate nodes, are located between the root node and leaf nodes in an index. They contain index pages. Clustered Index: An index that includes both table data and index data. In a clustered index, the leaf level of the index are the actual data pages. Data is physically stored in a clustered index in ascending order. A table can only have one clustered index.
Clustered Index Scan: Similar to a table scan, this is when the Query Optimizer reads each record in a clustered index to locate one or more records.
Clustered Index Seek: This is when the Query Optimizer is able to use a clustered index to directly select one or more specific records based on the WHERE clause of a query. This generally produces the fastest type of search.
Data Definition Language (DDL): Refers to Transact-SQL code used to create, alter, or drop database objects.
Data Page: A page designated to store rows of user data in a SQL Server file.
DDL: See Data Definition Language
Decision Support System (DSS): Refers to any software used to analyze business performance. Generally speaking, this software runs intensive queries against databases in order to analyze data for reporting purposes. These queries are best supported by extensive indexes for best performance. DSS queries run against OLTP databases may hurt the performance of production transactions, and should be avoided.
Density: Refers to the average percentage of duplicate rows in an index. If an indexed column, such as last_name, has much duplicate data, then the index is said the have high density. But if an indexed column, such as employee_number, has unique data, then the index is said to have low density. Density is also related to selectivity. Low selectivity is related to high density, and high selectivity is related to low density.
DSS: See Decision Support System
Execution Plan: A sequence of steps determined by the query optimizer (during compilation) used to execute a query tree or a sequence tree.
Extent: A group of eight continuous pages. Since each page in SQL Server holds 8K, an extent is 64K in size. There are mixed and uniform extents found in SQL Server. A mixed extent contains pages from two or more objects. Uniform extents have all of their 8 pages allocated to the same object.
GAM: See Global Allocation Page.
Ghost Record: When rows are deleted from the leaf level of an index, the are not removed immediately. Instead, they are marked as invalid and are called ghost records. Periodically, SQL Server starts a special housekeeping thread that locates and removes the ghost records.
Global Allocation Page (GAM): An allocation page containing information about allocated extends in a SQL Server file. The second page of every SQL Server file in a GAM page. Each GAM page can track nearly 64K of extents, which is about 4GB of data.
Heap: A collection of data pages containing rows for a table. Another way of saying this is that a heap is a table without a clustered index.
Index Allocation Map (IAM): An allocation page containing information about the extents that a table or index uses. It contains the location of the eight initial pages and a bitmap of extents indicating which extents are in use for that object. Each IAM page can track up to 512,000 data pages. IAM pages are used by SQL Server to help it navigate through a heap in order to locate available space for new rows to be inserted. IAM pages are the only logical connection between data pages in a heap.
Intermediate Level: Refers to the intermediate, or branch nodes, of an index.
Intermediate Node: Intermediate, or branch nodes, are located between the root node and leaf nodes in an index. They contain index pages.
IAM: See Index Allocation Map.
Index Pages: A page designated to store rows of index data in a SQL Server file.
I/O: Specifically, this is an acronym for Input/Output. When referred to in the context of SQL Server performance tuning, it refers to all the reads and writes performed on a server’s disk subsystem.
Leaf Level: Refers to the leaf pages in a clustered or non-clustered index.
Leaf Page: In a clustered index, it refers to the actual data pages of a table. In a non-clustered index, it refers to the lowest set of pages in an index, which includes pointers to the actual data pages.
Logical Read: Refers to when SQL Server reads a single 8K page from either memory or from disk.
Mixed Extend: See extent.
Non-Clustered Index: This type of index points to data pages, either in a heap or clustered index. This is a separate storage structure from a table and only includes index pages, no data pages. There can be a maximum of 250 non-clustered indexes per table.
OLAP: See Online Analytical Processing
OLTP: See Online Transaction Processing
Online Analytical Processing (OLAP): Refers to the analysis of data in a database, generally against a data warehouse or data mart. This generally involved resource-intensive queries to be run against the database.
Online Transaction Processing (OLTP): Refers to transactional processing run against a database, such as INSERTS, UPDATES, and DELETES.
Optimizer: See Query Optimizer.
Page: In SQL Server, a page contains 8K (8096 bytes) of data. There are many different kinds of pages in SQL Server, including data and index pages. Pages are always grouped in groups of 8 pages, called an extent. SQL Server stores, reads, and writes data in pages.
Page Free Space Page (PFS): An allocation page containing information on the amount of free space available on the pages in a file. The first page of each file in SQL Server is a PFS page. Every PFS page tracks up to 8,000 contiguous pages, which is about 64MB of data pages. For each file page, the PFS page contains a byte that tracks whether a page has been allocated, whether the page is on a mixed or uniform extent, and an estimate of how much room is available on the page.
Performance: Refers to the metrics relating to how a particular request is handled. For example, if a particular query takes 5 seconds to run, and after performance tuning, it now takes 3 seconds to run, we have boosted the performance of this query. On the other hand, if a particular query now takes 15 seconds, and it used to take only 10 seconds to run, then performance has degraded.
PFS Page: See Page Free Space Page.
Physical Read: Refers to when SQL Server reads a single 8K page from disk.
Query Optimizer: A software component of SQL Server that is used to analyze queries submitted to SQL Server for execution, and then determines the optimal way to execute the query. The query optimizer cannot be accessed directly by users. Instead, once queries are submitted to SQL Server, and parsed by the parser, the parser passes the queries to the query optimizer where optimization occurs.
Query Plan: The steps used to execute a query. A query plan is generated by the Query Optimizer.
Query Tree: If a stored procedure, trigger, or Transact-SQL batch is parsed, an internal structure is created that describes the series of steps needed to perform the requested actions. If any of these contain a query, then this internal structure is called a query tree.
RAID Level 0: Also referred to as disk striping, RAID Level 0 combines three or more physical disks into a single array, and data is striped evenly among all of the drives. RAID Level 0 offers the fastest speed of all RAID levels, but it is not fault tolerant. Because of this, it is not suitable for storing SQL Server OLTP databases files, although it can be used for the Tempdb database because the Tempdb database is recreated every time SQL Server is restarted. RAID Level 0 may also be considered for read-only databases where speed is of high importance.
RAID Level 1: Also referred to as disk mirroring or duplexing (mirroring uses one channel, and duplexing uses two channels), RAID Level 1 combines two physical disks into a single array, and data is written or read to both drives at the same time. RAID Level 1 offers fault tolerance and reasonable good read and write speed. The biggest downside to it is that it wastes 50% of your disk drives. It is very common to use RAID Level 1 arrays for a SQL Server’s operating system drive.
RAID Level 5: Also referred to as data striping with parity, RAID Level 5 combines three or more physical drives into a single array. Data, along with parity data used to recreate the data should one of the drives in the array fail, is striped among all the disks, making it fault tolerant. RAID Level 5 has good read performance, but mediocre write performance (because of the parity data that must be created and written for fault tolerance). For optimum performance, there should be as many physical drives in the array as possible (the number of drives in the array depends on what the controller can handle). RAID Level 5 is commonly used to store SQL Server production databases.
RAID Level 10: Also referred sometimes to as RAID Level 1+0 or 0+1, RAID Level 10 is a combination of RAID 1 and RAID 0. Essentially, it mirrors two RAID Level 0 arrays, which provides both very high I/O performance and fault tolerance. While it is the most expensive RAID option, it is the preferred RAID option for high performance SQL Server databases.
RID: See row identifier.
Root Node: The top level of a clustered or non-clustered index. In consists of a single index page.
Row Identifier: Identifies the physical location of a row on a page.
Sargable: The ability of the search arguments in a WHERE clause to use an index or not. For example, if the search arguments in a WHERE clause have the ability to use an index, then the search arguments are said to be sargable. If the search arguments in a WHERE clause cannot use an index, then they are considered to be non-sargable.
Scalable: The ability for a resource to increase in capacity in order to meet increasing workloads over a period of time. Scalability specifies how a metric varies with load. For example, if a SQL Server application is now running 1000 transactions per minutes, but later is running 2000 transactions per minutes with only a proportional increase in hardware utilization, then the application could be considered scalable.
Search Argument: The criteria used in a WHERE clause used to restrict a query to an exact match or a range of values. The more exact the search argument, generally the faster the query will perform. Try to avoid using wildcard characters, as they make the search argument less exact, and often slows the query.
Secondary Global Allocation Map Page (SGAM): An allocation page containing information about mixed extents. The third page of each SQL Server file is a SGAM page. SGAM pages track all mixed extents that have at least one unused page. One SGAM page can track up to almost 64K of mixed extents, or about 4GB of data.
Selectivity: Refers to the percentage of rows in a table that are returned by a query. A query is considered highly selective if it returns a very limited number of rows. A query is considered to have low selectivity if it returns a high percentage of rows. Generally speaking, if a query returns more than 5% of the number of rows in a table, it is considered to have low selectivity.
Sequence Tree: If a stored procedure, trigger, or Transact-SQL batch is parsed, an internal structure is created that describes the series of steps needed to perform the requested actions. If any of these contain a procedure, then this internal structure is called a sequence tree.
SGAM Page: See Secondary Global Allocation Map Page
Table Scan: When the SQL Server Query Optimizer determines that there is no useful indexes on a table to assist producing the results of a query, SQL Server performs a table scan on the table. This means that SQL Server has to read every row in the entire table in order to locate the rows requested in the query. In most cases, table scans are slow and should be avoided. In some cases, a table scan is faster when locating records than using an index. This is especially true for small tables.
Uniform Extent: See extent
]]>