Summary for Performance Tuning Using SQL Server DMVs Book / Chapter 7: OS and Hardware Interaction

Chapter 7: OS and Hardware Interaction

• signal_wait_time_ms is a value returned by sys.dm_os_wait_stats DMV and represents the time a thread is waiting in the queue for the CPU. wait_time_ms (total waits) is another value returned by the previous query and represents the total time spent waiting for ALL resources (not just CPU). The key metric, with regard to potential CPU pressure, is the signal wait as a percentage of the total waits. A high percentage signal is a sign of CPU pressure. The literature tends to quote "high" as more than about 25%, but it depends on your system … P263-264.
• The values provided by sys.dm_os_wait_stats DMV are running totals, accumulated across all sessions since the server was last restarted or the statistics were manually reset using the DBCC SQLPERF command shown in Listing 7.1.
• Listing 7.2: The most common waits.
• OLEDB wait type explanation in … P267.
• CXPACKET wait type explanation in … P268.
• ASYNC_NETWORK_IO wait type explanation in … P269.
• SOS_SCHEDULER_YIELD wait type explanation in … P270.
• The script in Listing 7.3 will help determine on which resources SQL Server is spending the most time waiting, as a percentage of the total amount of time spent waiting on any wait_type that doesn't appear in the exclusion list.
• If you feel that excessive locking may be the root cause of a performance issue, the query shown in Listing 7.4 filters on all locking wait types and order by the number of occurrence. 
• This query is useful to help confirm CPU pressure. Since signal waits are time waiting for a CPU to service a thread, if you record total signal waits above roughly 10–15%, this is a pretty good indicator of CPU pressure. These wait stats are cumulative since SQL Server was last restarted, so you need to know what your baseline value for signal waits is, and watch the trend over time.
• sys.dm_os_performance_counters DMV returns only SQL Server counters are represented in the DMV (not any Windows or other counters).
• Listing 7.8: Monitoring changes in the size of the transaction log. The query there returns data related to ‘Log Growths’ and ‘Log Shrinks’. Shrinking the log (i.e. removing any unused space) is, as a general habit, a bad idea as it just means that you are likely to see a log growth event very soon. The best practice is to have log space pre-allocated and not to adjust it; free log space is not harmful to performance, but a full log is.
• With the query in listing 7.11 you can returning the current value for the buffer cache hit ratio.
• The sys.dm_os_sys_ info DMV returns a single row, summarizing the characteristics of a given machine, in terms of static configuration, such as the number of processors, as well as how long a server has been running. Columns represented in the view are described in … P289-290. Listing 7.14 (information about CPU) and listing 7.15 (info about memory).
• In order to identify or rule out CPU pressure as a potential cause of slow execution times, we can use sys.dm_os_schedulers to investigate the kind of load being applied to our CPUs. It provides information on the current activity of the schedulers that are being used to apportion work to CPUs, along with a few cumulative counters that reveal how often certain events, such as context switches, have occurred since the computer was last started. The query in Listing 7.16 calculates the average number of tasks being managed, and tasks waiting in the runnable queue, across all available schedulers. High, sustained values for the current_tasks_count column usually indicate a blocking issue. It can also be a secondary indicator of I/O pressure. High, sustained values for the runnable_tasks_count column are usually a very good indicator of CPU pressure, since this means that many tasks are waiting for CPU time. The longer the queue, and the greater the number of schedulers with requests waiting, the more stressed is the CPU subsystem. For most systems, a sustained runnable task queue length of more than about 10–20 is a cause for concern. For individual schedulers, we can employ the query shown in Listing 7.17, which uses various status columns to investigate potential CPU pressure or, by a simple variation of the WHERE clause, to indicate whether or not I/O pressure is the root cause of the system slowdown.
• If the system isn't experiencing CPU pressure, is not I/O bound, and there aren't too many other types of waits on the system, then you may need to investigate the possibility that there are simply too few threads available to the server. The query in Listing 7.18 investigates the possible need to adjust the server threads system configuration, by checking the average value of work_queue_count. In the absence of CPU pressure, values of more than one for the average of the work_ queue_count column can mean that you need to increase the number of threads allowable to the server.
• Context switching occurs when a process fails to complete in a reasonable time, and the process has to give up control of the scheduler (again, representing a CPU) to let another process complete. This is not ideal as it increases load on the CPU. Listing 7.19 gets the counts that represents context switching, but those a accumulative and may include some night activities that where such context switching does not affect us; so in order to detect excessive context switching during normal OLTP operations caused, we need to take a baseline measurement and then track and compare the results over time. As noted during the earlier discussion on common wait types, hyper-threading (parallel execution) in OTLP systems can lead to context switching, and to CXPACKET and SOS_SCHEDULER_YIELD waits. if hyper-threading is enabled and you are seeing more than 5000 * (Number of Processors) context switches per second, it suggests that the scheduler is context switching between the same processor, yet thinking it is swapping to a different one, and you should consider turning hyper-threading off and retesting performance. A large number of idle switches, indicating periods when the processor was idle with no requests to process, can indicate a poor distribution of workload across available processors. A low value on one scheduler, coupled with very high utilization on another one, might indicate that large operations that really could benefit from using parallel operations are not doing so. For example, if you have MAXDOP set to 1 on a server with 8 CPUs, then your CPU-intensive nightly reports will run on only one of the CPUs, while all the others sit idle.
• Listing 7.22 uses sys.dm_os_sys_ memory to get an overview of how much RAM is available to the operating system in total, and how much of it is currently not in use.
• sys.dm_os_sys_memory DMV: gives you an overview of how the server is managing memory
• sys.dm_os_process_memory DMV: gives you an overview of how the SQL Server process is doing for memory.
• sys.dm_os_memory_cache_counters – stats on how the cache memory is being utilized, including how much memory is actively being used 
• sys.dm_os_latch_stats – stats on the low-level resource locks that SQL Server uses to lock physical pages in memory, and more.

Reference: http://www.amazon.com/Performance-Tuning-Server-Dynamic-Management/dp/1906434476

Summary for Performance Tuning Using SQL Server DMVs Book / Chapter 6: Physical Disk Statistics and Utilization

Chapter 6: Physical Disk Statistics and Utilization

• It is vital that, before even installing SLQ Server, you gain a good understanding of the I/O capacity of your disk subsystem, using tools such as SQLIO.
• sys.dm_db_partition_stats helps us find the largest tables and indexes, especially those that are subject to heavy updates, monitor the effectiveness of their partitioning scheme, or investigate the need to upgrade/configure the disk I/O subsystem appropriately. It returns one row per table partition; if the table is not partitioned it will return one row for the table. Note that when querying this view, we filter out system objects which reach to around 100 for each database.
• The query in Listing 6.1 will provide the total number of rows in all clustered indexes and heaps on a given SQL Server instance. System objects such as sys.dm_db_partition_stats are updated asynchronously, for performance reasons, so the counts may not be completely up to date.
• Rebuilding indexes can bloat transaction logs and, when using differential backups, it means that all the pages in the index/tables will have changed since the last full backup … P230.
  https://social.msdn.microsoft.com/Forums/en-US/5146912a-395c-4129-8aeb-4f82b949bdfc/does-reorganizing-indexes-bloat-transaction-logs-and-differential-backups-as-rebuilding-them-the?forum=sqlkjmanageability

• The occurrence of page splits (which happens due to inserting new records in full data pages, which in turn causes fragmentation) can be minimized to some degree, though not avoided altogether, by setting the appropriate fill factor for the clustered table (e.g. 90%) thus allowing space for new data on each page. In fact, a common cause of fragmentation is rebuilding clustered tables and indexes and forgetting to set the fill factor appropriately. By default, the fill factor will be 0 (meaning zero spare space). This can end up causing a lot more subsequent fragmentation than was resolved by rebuilding! … P233.
• Many designers persist in the habit of using GUIDs for surrogate keys, and clustering on them. GUIDs are random in nature, and tend not to be created sequentially and, as a result, insertions of data into the middle of the table are common which causes pages splitting and fragmentation in P233-235 there are examples to illustrate this.
• With heaps, the storage engine inserts all rows at the end of the table, in order of arrival. This makes inserting into a heap super-fast. As such, many people use heaps as a place to drop rows (for instance, when logging operations, and even when loading data using bulk copy), while avoiding the performance impact of index maintenance. On the contrary reading from a fragmented heap, is a performance nightmare.
• Diagnosing I/O Bottlenecks: start with PerfMon counters such as PhysicalDisk Object: Avg. Disk Queue Length and Avg. Disk Reads/Sec, which can help you work out the number of I/Os per disk, per second, and how many physical I/O requests are being queued, on a given disk. And also use sys.dm_os_wait_stats DMV and look if you can find PAGEIOLATCH_EX or PAGEIOLATCH_SH among the top waits, this indicates that many sessions are experiencing delays in obtaining a latch for a buffer, since the buffer is involved in physical I/O requests … P239
• sys.dm_io_virtual_file_stats DMF gives cumulative physical I/O statistics, indicating how frequently the file has been used by the database for reads and writes since the server was last rebooted. It also provides a very useful metric in the form of the "I/O stall" time, which indicates the total amount of time that user processes have waited for I/O to be completed on the file in question. Ultimately, high stall rates could simply indicate that the disk I/O subsystem is inadequate to handle the required I/O throughput Note that this DMF measures physical I/O only. Logical I/O operations that read from cached data will not show up here. Explanation of the columns it returns are in P240-241.
• Investigating physical I/O and I/O stalls: the writers store in a temporary table the result of the query in listing 6.13, which captures the baseline disk I/O statistics from sys.dm_io_virtual_file_stats, then wait for 10 seconds (or depending on the workload on your server), then they run the query in listing 6.15 which compares the current statistics with the baseline. And they are suggesting that, in any event, it is certainly worrying to see that the stall times on a drive are substantially greater than the elapsed time on that same drive … P243-246.
• sys.dm_io_pending_io_requests DMV returns a row for each currently pending I/O request at the file level. More than two or three pending process could indicate an issue. If you regularly observe a high number of pending I/O requests on a single drive, you should consider moving some of the files onto a separate drive, on a different access channel. Using the query in Listing 6.16, we can view the file name, the status, and how long the operation has been waiting.
• In an optimized system the read:write ratio should ideally be close to 50:50. In reality there are almost always more reads than writes. A higher ratio than around 80:20, start to suspect non-optimal queries, or insufficient cache memory to avoid physical disk access. If you find that the read:write ratio is 50:50 based on counts (NUMBER of read operations versus write operations), and 99:1 based on data (SIZE or amount of data read versus written), this indicates that you are reading a lot of data to write a little data, which could be caused by inefficient database code, perhaps allowing users to search in a very inefficient manner, resulting in table scans. Bear in mind that this includes only actual writes to the files, or reads from the file, and will not reflect data read from the cache, or written to the cache and not yet flushed to the disk. Listing 6.17 calculates this based on the AMOUNT (size) of data. Listing 6.18 does the same, but also slice and group on the drive letter. If those queries results something to worry about, the next step is to obtain the read:write ratios for this database in terms of the number of read and write operations. If this method reveals a ratio much closer to 50:50 then we know that reads are reading a disproportionately high amount of data. You can find this using query in listing 6.19. Listing 6.21 gets number of reads and writes at the table level, but note that the returned counts include logical operations also.
• Listing 6.22 demonstrates how to get an overview of tempdb utilization.

Reference: http://www.amazon.com/Performance-Tuning-Server-Dynamic-Management/dp/1906434476

Summary for Performance Tuning Using SQL Server DMVs Book / Chapter 5: Indexing Strategy and Maintenance

Chapter 5: Indexing Strategy and Maintenance

• sys.indexes indexing catalog view provides metadata at the index level, such as index identifiers (e.g. index name) and configuration settings (e.g. fill factor).
• You can either create clustered index on a key that naturally reflects the way the data will be queried, or you can cluster on a narrow, ever-increasing integer key (to minimize subsequent fragmentation) and use non-clustered indexes for query efficiency. Most of the advice out there points to the latter approach … P180.
• The ratio of unique values within a key column is referred to as index selectivity. The more unique the values, the higher the selectivity, which means that a unique index has the highest possible selectivity. The query engine loves highly selective key columns, especially if those columns are referenced in the WHERE clause of your frequently run queries. The higher the selectivity, the faster the query engine can reduce the size of the result set. If you've chosen a low selectivity column for the index key (i.e. where each key value matches many rows), then the optimizer may decide to simply perform a table scan to return a piece of data. Table scans have a bad reputation, but this is because they often mean reading a huge number of rows; in small tables, scanning all the rows is sometimes quicker than reading the data from the leaf levels of an index. In creating an index, the leading (first) column should be selective. However, this does not mean each index should start with the PK column; it must be a column that is likely to get searched on… P181
• It is a balancing act; having a huge number of single column indexes (narrow indexes) is a bad idea, as there will be too many indexes to maintain. Also wide indexes is bad, as you will fit few on a data page, index will take up a lot of space, and scanning it will be inefficient… P182.
• sys.dm_db_index_usage_stats DMV is used to obtain statistics on how indexes have been used to resolve queries. Data in this DMV is cumulative, and is refreshed when the server is restarted, when the index is dropped and recreated, or rebuilt or reorganized. Listing 5.3 is an example of how to use it, and the following are some important columns there:
• • user_seeks – the number of times the index has been used in a seek operation (to find a specific row)
  • user_scans – number of times the index has been used by scanning the leaf pages of the index for data.
  • user_lookups – for clustered indexes only, this is the number of times the index has been used in a "bookmark lookup" to fetch the full row; this is because non-clustered indexes use the clustered indexes key as the pointer to the base row
  • user_updates – number of times the index has been modified due to a change in the table's data.
  • User reads – total number of times that the index is used by the optimizer to resolve a query … P182-184
• Identify indexes that have never been accessed: Listing 5.4 finds those indexes that have had no reads or writes, in other words those indexes that do not show up in sys.dm_db_index_usage_stats. Those indexes can potentially be dropped, after some further investigation ... P185
• Listing 5.5 identifies indexes that are being maintained but not used. You can add the following to the select statement to generate the drop index command that drops the index:
• 'DROP INDEX [' + i.[name] + '] ON [' + su.[name] + '].[' + o.[name]+ '] WITH ( ONLINE = OFF )' AS [drop_command]
• it is always advisable to first check how recently the usage stats were cleared, by running the query in listing 5.6, and also after adequate testing in a non-production environment… P188.
• Listing 5.7: Finding rarely-used inefficient indexes.
• The sys.dm_db_index_operational_stats is a DMF provides index usage statistics at a more detailed level than those provided by the sys.dm_db_index_usage_stats DMV, it offers more detailed information about how the index is used at a physical level, via columns such as leaf_insert_count, leaf_update_count and leaf_delete_count, as well as the nonleaf_* equivalents, for modifications above the leaf level. Also it can provide evidence of potential lock or latch contention on the objects, or of excessive I/O being issued by the object. The data returned by this DMF exists only as long as the metadata cache object that represents the heap or index is available. Application for this is listing 5.8 … P190-193.
• Listing 5.9 uses sys.dm_db_index_operational_stats to return records that relate to locking and blocking at the row level for the indexes of the active database.
• Listing 5.10 highlights which of our indexes are encountering latch contention using the page_io_latch_wait_count and page_io_wait_in_ms columns. I/O latching occurs on disk-to-memory transfers, and high I/O latch counts could be a reflection of a disk subsystem issue, particularly when you see average latch wait times of over 15 milliseconds.
• The query in Listing 5.11 uses sys.dm_db_index_operational_stats DMV to provide information on the count of attempts made by SQL Server to escalate row and page locks to table locks for a specific object, and the percentage success in performing the escalation.
• Listing 5.12 identifies indexes associated with lock contention, and it is a good next step after querying sys.dm_os_wait_stats DMV (discussed in Chapter 7), and the outcome points to locking problems.
• Finding Missing Indexes: information about missing indexes is exposed via the following four MDOs: sys.dm_db_missing_index_details, sys.dm_db_missing_index_columns, sys.dm_db_missing_index_group_stats, and sys.dm_db_missing_index_groups. The data stored by each of these DMOs is reset on a server restart. You can find a brief explanation of those DMOs and a review for the most significant columns in P199-204. You should never just blindly add every index that these DMOs suggest, instead, you need to examine the results of the query carefully and manually filter out results that are not part of your regular workload. Listing 5.13 puts those DMOs to good use and find the most beneficial missing indexes. The following is brief explanation of the returned columns:
• Index_advantage: overall benefit of a suggested index, or the importance of creating the suggested index. The higher this value is, the more important the index is.
• Statement: the table or view name.
• equality_columns: the columns that would have been useful, based on an equality predicate.
• inequality_columns: the columns that would have been useful, based on an inequality predicate (i.e. any comparison other than "column = value")
• included_columns: columns that, if included, would have been useful to cover the query.
• unique_compiles: the number of plans that have been compiled that might have used the index.
• user_seeks: the number of seek operations in user queries that might have used the index.
• avg_total_user_cost: average cost saving for the queries that could have been helped by the index in the group
• avg_user_impact: the estimated percentage by which the average query cost would drop, for the queries that could use this index.
• When creating the suggested indexes (after careful consideration), list the equality columns first (leftmost in the column list), then list the inequality columns after the equality columns (to the right of equality columns listed). Be warned that the order in which these DMOs list missing columns does not accurately suggest the correct column order for an index key. To determine an effective order for the equality columns, order them based on their selectivity; that is, list the most selective columns first… P205-207.
• Finding Indexes Fragmentation: The sys.dm_db_index_physical_stats DMF provides invaluable information regarding the state of your index partitions, including type of index, index depth, and degree of fragmentation. This DMF accepts a number of parameters, one of them “mode”, which determines the thoroughness with which the statistics are collected. Possible values are: 
• LIMITED: is the default and the least costly, will not scan the leaf level of the indexes and the data pages of heaps are not scanned
• SAMPLED: mode returns statistics based only upon a 1% sample of all pages of the indexes and heaps in the scope of the function call; if any page or heap within the scope of the function call has less than 10,000 pages, then DETAILED is automatically used.
• DETAILED provides the most complete result set from this function of the three, but can require ample resources; it scans all pages; it returns all statistics.
• This DMF returns a large number of columns and we'll only review a subset of them in P211. The query in Listing 5.14
• will return fragmentation information for each index in the current database, where the average_fragmentation_in_percent column is greater than 15% and where the page count is greater than 500. On a busy system, this can be a resource-intensive query. The rule of thumb for index reorganization is 15–30% fragmentation. The often recommended process for indexes with fragmentation greater than 30% is a rebuild of the index.

Reference: http://www.amazon.com/Performance-Tuning-Server-Dynamic-Management/dp/1906434476

Summary for Performance Tuning Using SQL Server DMVs Book / Chapter 4:Transactions

Chapter 4: Transactions

• READ COMMITTED: These locks prevent another transaction from modifying that data while the query is in progress, but do not block other readers. In READ COMMITTED mode, shared read locks are released as soon as a query completes, so data modification transactions can proceed at that point, even if the transaction to which the query belongs is still open. Therefore, non-repeatable reads are possible; if the same data is read twice in the same transaction, the answer may be different. If this is not acceptable, the isolation level can be made more restrictive. The REPEATABLE READ level will ensure that all the rows that were read cannot be modified or deleted until the transaction which reads them completes. However, even this level does not prevent new rows (called phantom rows) being inserted that satisfy the query criteria, meaning that reads are not, in the true sense, repeatable. To prevent this, you could switch to SERIALIZABLE, the most restrictive isolation level of all, which basically ensures that a transaction is completely isolated from the effects of any other transaction in the database … P117-118.
• The sys.dm_tran_locks DMV provides information regarding both the resource on which the lock is being held (or has been requested), and the owner of the request. Description of the useful columns in this DMV are listed in P120-122.
• Lock types: SQL Server can lock a number of different types of resource, the most obvious being tables (OBJECT locks), pages (PAGE locks), rows (RID locks), and keys (KEY locks) (other common types in P124), in order of increasing granularity. The more granular the lock, the higher the degree of concurrent access that can be supported. However, with that comes a higher memory overhead, from having to manage a large number of individual locks. SQL Server automatically chooses locks of the highest possible granularity, suitable for the given workload. However, if too many individual locks are being held, SQL Server may use lock escalation to reduce the total number of locks being held. While this will result in lower overhead on SQL Server, the cost will be lower concurrency. If processes are running on your servers that are causing lock escalation, it's worth investigating whether the escalation is justified, or if SQL tuning can be performed to prevent it … P123.
• SQL Server employs five different lock modes, in order to control how concurrent transactions interact. These modes are exposed in the request_mode column of the sys.dm_tran_locks DMV. Lock modes are described in P125. 
• Listing 4.5: Which sessions are causing blocking and what statement are they running. A better one and adds to listing 4.5 is Listing 4.6, it allows investigating locking and blocking based on waiting tasks [need to be fixed: replace “2 AS [blocked_command]” with “2) AS [blocked_command]”]. You can add to resulting columns and get the name of the database by “db_name(DTL.[resource_database_id]) DatabaseName”.
• Listing 4.14: identifies all CURRENT ACTIVE transactions and their physical effect on the databases' transaction logs. This is especially useful when seeking out transactions that may be causing explosive transaction log growth.
• Snapshot Isolation and the tempdb Version Store: Each time a row is modified in a database running under snapshot isolation, a version of the row from prior to the modification is stored within tempdb in a version store. Read transactions targeting the affected rows will use the row version(s) from the tempdb, while the writing transaction will modify the actual table data. Update locks are issued for writes, and when a read transaction encounters such a lock, it is diverted to the version store. This ensures that write transactions do not block reads nor the inverse.  … P148 
• Snapshot isolation introduces two new modes of operation:
• SNAPSHOT mode isolates read transactions from modifications that committed after the transaction began
• READ_COMMITTED_SNAPSHOT mode isolates read transactions from modifications which committed after the current statement began…. P149 – 151 show how to enable and initiate the two modes.
• P151 the writer shows how to “Investigating snapshot isolation”. He shows how to use sys.dm_tran_active_snapshot_database_transactions, sys.dm_tran_currrent_snapshot, and sys.dm_tran_transactions_snapshot to get the “current snapshot activity”, then how to use sys.dm_tran_version_store, and sys.dm_tran_top_version_generators to get “Version store usage”.

Reference: http://www.amazon.com/Performance-Tuning-Server-Dynamic-Management/dp/1906434476

Summary for Performance Tuning Using SQL Server DMVs Book / Chapter 3: Query Plan Metadata

Chapter 3: Query Plan Metadata

• Statistics and query plan metadata are extracted from the DMVs below:
• sys.dm_exec_query_stats – returns aggregated performance statistics for a cached query plan. Returns one row per statement within the plan.
• sys.dm_exec_procedure_stats – returns aggregated performance statistics for cached stored procedures. Returns one row per stored procedure.
• sys.dm_exec_cached_plans – provides detailed information about a cached plan, such as the number of times it has been used, its size, and so on. Returns a row for each cached plan.
• sys.dm_exec_query_optimizer_info – returns statistics regarding the operation of the query optimizer, to identify any potential optimization problems. For example, you can find out how many queries have been optimized since the last time the server was restarted … P75.
• In order to return the query plan for a given batch, as well as some interesting attributes of these plans, we can pass the identifier for that plan batch, the plan_handle, to one of the DMFs below:
• sys.dm_exec_query_plan – returns in XML format the query plan, identified by a plan_handle, for a SQL batch.
• sys.dm_exec_text_query_plan – returns in text format the query plan, identified by a plan_handle, for a SQL batch or, via the use of this DMF's offset columns, a specific statement within that batch.
• sys.dm_exec_plan_attributes – provides information about various attributes of a query plan, identified by a plan_handle, such as the number of queries currently using a given execution plan. It returns one row for each attribute … P76.
• The root cause of many performance problems is the fact that the plan you get when query execution is optimized under a full production server load can be very different from the one you saw in Management Studio while building the query … P78.
• To retrieve the query plan, simply extract the plan_handle from the sys.dm_exec_query_stats DMV, then pass it as a parameter to one of the following DMFs:
• sys.dm_exec_query_plan, which accepts the plan_handle as its only parameter. Check last query of listing 3.2.
• sys.dm_exec_text_query_plan, which accepts the plan_handle and adds two additional parameters, statement_start_offset and statement_end_offset, which mark the start and end points of individual SQL statements within the batch or procedure … P79.
• You can save the extracted query plan to .SQLPLAN file, so you can compare it with the one after optimization … P82.
• To show only the “subplan” for each individual query in a batch, use listing 3.5. Unfortunately, the sys.dm_exec_query_plan DMF returns the plan in a form we can save and use, but not view in SSMS … P85.
• Listing 3.6: Retrieving the plans for compiled objects using the sys.dm_exec_cached_plans view. The following explains some columns in the result:
• refcounts – number of cache objects that reference this cached plan.
• usecounts – number of times the plan has been used since its creation.
• size_in_bytes – size of the plan.
• cacheobjtype – type of object in the cache. The domain is: Compiled Plan, Parse Tree, Extended Proc, CLR Compiled Func, CLR Compiled Proc.
• objtype – the type of object. The domain is: Proc (stored procedure, function), Prepared (prepared statement), Adhoc (query), Repl Proc (replication filter procedure), Trigger, View, Default, UsrTab (user table), SysTab (system table), CHECK, Rule … P88.
• Good plan reuse is one sign of a heathy system. Compiling a query plan can be a CPUintensive operation, especially with complex queries, so reuse is a very good thing. The greater the value in the usecount column for each of your plans, the greater the number of times query plans are reused, and the smaller the number of times a new query plan has to be recreated. Conversely, a usecount of 1 for a large number of plans indicates that your cache space is being taken up with plans that were compiled and used once to execute an ad hoc query, then never used again … P90.
• Listing 3.8 shows the "distribution" of plan reuse on your system. The second column is the number of query plans that were [the value of the first column] number of times reused.
• If we can improve the efficiency of a plan that that is being reused many times, it could be highly beneficial. Listing 3.9 uses sys.dm_exec_cached_plans in conjunction with sys.dm_exec_query_plan and sys.dm_exec_sql_text to return the text of the plan, plus the text of the query that is associated with the plan and has the highest use counts. Alternatively, we can filter on the objectid, to examine plan reuse for a single procedure, as shown in Listing 3.10. … P94-95.
• Listing 3.11 identifies ad hoc queries with a use count of 1, ordered by the size of the plan. It provides the text and size of single-use ad hoc queries that waste space in the plan cache. If you determine that you have a mostly ad hoc workload with minimal reuse, check out the "optimize for ad hoc workloads" system option. This setting changes SQL Server behavior and does not store a plan on first usage, only the query text. If it matches a second time, the plan is stored … P97.
• The sys.dm_exec_query_stats DMV: Returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows is tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view. List of the columns returned by this DMV is in page P102.
• Listing 3.14: Gets the CPU-intensive queries. That query can be modified to get queries of excessive: logical writes, logical reads, physical reads, running time, or expensive CLR code using the columns listed in page 106.
• Listing 3.16: uses sys.dm_exec_procedure_stats to find out which CACHED stored procedures are generating the most total logical reads. This query is especially useful if there are signs of memory pressure, such as a persistently low page life expectancy and/or persistent values above zero for memory grants pending. This query is filtered by the current database, but we can change it to be instance-wide by removing the WHERE clause. Simply by selecting the total_physical_reads column, instead of total_logical_reads in this query, we can perform the same analysis from the perspective of physical reads, which relates to read, disk I/O pressure. Lots of stored procedures with high total physical reads or high average physical reads, could indicate severe memory pressure, causing SQL Server to go to the disk I/O subsystem for data. It could also indicate lots of missing indexes or "bad" queries (with no WHERE clauses, for example) that are causing lots of clustered index or table scans on large tables.
• Listing 3.17 uses sys.dm_exec_query_optimizer_info DMV to get statistics on all optimizations that have been performed by the optimizer. It will allow us to get a feel for how queries have been optimized, and how many of them have been optimized, since the last time the server was restarted. The three columns returned by this DMV are as follows:
• counter – the type of operation that the optimizer has done
• occurrence – number of times the operation the counter represents has occurred
• value – may or may not have some value, but is typically an average of the values that were recorded when the counter was written to … P110-112

Reference: http://www.amazon.com/Performance-Tuning-Server-Dynamic-Management/dp/1906434476

Summary for Performance Tuning Using SQL Server DMVs Book / Chapter 2: Connections, Sessions and Requests

Chapter 2: Connections, Sessions and Requests

• The following DMO give us a view of what is happening now in SQL Server:
• sys.dm_exec_connections – provides information about network traffic and protocols, as well as user attributes from the calling source of the connection. One of its resulted columns is most_recent_session_id which is more appropriate to use when joins between this DMV and sys.dm_exec_sessions as sessions will reuse open connections as seen fit, and this column will store the more accurate information to complete the join. Another column is most_recent_sql_handle which its text can be returned by passing the handle to the sys.dm_exec_sql_text DMF. For the auth_scheme column if the value is SQL then it is SQL Server authentication, other than that it is Windows authentication … P38-39.
• sys.dm_exec_sessions – returns information about each user and internal system session on a SQL Server instance including session settings, security, and cumulative CPU, memory, and I/O usage. Note that many of the columns in this DMV may have a NULL value associated with them if the sessions are internal to Microsoft SQL Server (those with session_id < 51).  Values in this sessions DMV are updated only when their associated requests have finished executing. One of its column is status, If none of the session's requests are currently running, then the status is “sleeping”, or if the "work" owned by a session is currently being executed, then the value will be “running”, there is also a third status value of dormant, indicating a session that "has been reset due to connection pooling". Other interesting columns in this DMV are: total_elapsed_time, last_request_start_time, cpu_time, memory_usage (number of 8 KB pages), total_scheduled_time, logical_reads (from cache), and reads & writes (from disk) … P40-41.
• sys.dm_exec_requests – provides a range of query execution statistics, such as elapsed time, wait time, CPU time, and so on. It returns one row for every query currently executing. The information returned from sys.dm_exec_requests is real time; it's not returned after the fact.
• sys.dm_exec_sql_text – returns the text of the SQL batch identified by a sql_handle.
• sys.dm_exec_query_plan – returns, in XML format, the query plan, identified by a plan_handle.
• Who is connected? The query in Listing 2.3 identifies sources of multiple connections to your SQL Server instance and so will allow the DBA to identify where the bulk of the connections originate, for each of their instances ... P42.
• Listing 2.4 gives who is connected by SSMS, and what query they are running (you can also add des.login_name, to the selected column, to get the login) … P44.
• Listing 2.5: Return session-level settings for the current session. The various session settings determine, not only how the session handles requests and transactions that flow along its ownership chain, but also how the session interacts with other sessions running concurrently on the SQL Server instance. Sessions with elevated deadlock priority, for example, are able to run roughshod over any other sessions with which they may conflict in a deadlock situation … P46.
• Listing 2.6 reports on the number of sessions being run by each login on your SQL Server instance. It's especially useful for seeking out those logins that own more than a single session … P47.
• Context switching is the act of executing T-SQL code under the guise of another user connection, in order to utilize their credentials and level of rights. Listing 2.7 allow DBAs to identify its occurrence … P48.
• Inactive sessions: The query shown in Listing 2.8 identifies all sessions that are open and have associated transactions, but have had no active requests running in the last n days.  If cumulative activity is high, as indicated by the values of cpu_time, total_elapsed_time, total_scheduled_time, and so on, but the session has been inactive for a while, then it may be an application that keeps a more-or-less permanent session open, and therefore there is little to be done about it.… P49.
• Listing 2.9: Identifying sessions with orphaned transactions …P51.

sys.dm_exec_requests

• The status column of the _requests DMV reveals the status of a given request within a session. If a request is currently executing, its status is running. If it is in the "runnable" queue, which simply means it is in the queue to get on the processor, its status is runnable. This is referred to as a signal wait. If it is waiting for another resource, such as a locked page, to become available in order to proceed, or if a running request needs to perform I/O, then it is moved to the waiter list; this is a resource wait and the waiting request's status will be recorded as suspended … P55.
• The blocking_session_id column of the _requests DMV lists the session_id that is blocking the request; if no blocking exists, the value will be NULL; if the value is (-2), then the block is owned by an orphaned distributed transaction; if (-3) the block is owned by a deferred recovery transaction; and if (-4) the session_id of the blocking latch owner could not be identified … P56.
• The _requests DMV contains useful activity and workload columns: percent_complete, can be used as a metric for completion status for certain operations. cpu_time, the total amount of processing time spent on this request. row_count, the number of rows that were processed for the request. granted_query_memory, number of 8 KB pages allocated to the execution of the request. reads & writes, total physical disk reads/writes performed for this request. logical_reads, total number of reads from the data cache for this request … P57. 

• Listing 2.10: Retrieving the text for a currently executing ad hoc query … P58-60.
• Pass the sql_handle to the sys.dm_exec_sql_text DMF, to obtain the SQL text of the executing batch. This batch or procedure may consist of tens or even hundreds of SQL statements, so we'll get back the text for all those statements. A common pattern is to use the SUBSTRING function and the byte offset columns (statement_start_offset and statement_end_offset), supplied by sys.dm_exec_requests, to extract the text for only that statement within that batch that is currently executing, as in listing 2.14 … P61-63.
• By adapting the script in Listing 2.16, we can examine the activity of each currently active request in each active session in terms of CPU usage, number of pages allocated to the request in memory, amount of time spent waiting, current execution time, or number of physical reads… P65.
• Who is running what, right now? Listing 2.17 … P67.
• The script provided in Listing 2.20, essentially provides a more detailed version of sp_who2. It returns all of the columns provided by sp_who2, and augments it with the executing portion of the T-SQL text associated with the request and the query plan in XML format. Unlike in sp_who2, this query breaks down the Disk I/O information into reads and writes. Finally, it also includes wait metrics and the transaction_isolation_level, to provide insight into how this session is interacting with other sessions currently running on the instance. As a whole, this information offers a very useful snapshot of activity, resource impact, and processing health on the SQL instance. Others have taken this much further; in particular, SQL Server MVP Adam Machanic has created a script called Who Is Active, which is available in http://tinyurl.com/WhoIsActive and which returns, not only all columns provided by script in listing 2.20, but also detailed information on tempDB impact, context switches, memory consumption, thread metadata, and the query text rendered as XML…P69.

Reference: http://www.amazon.com/Performance-Tuning-Server-Dynamic-Management/dp/1906434476

Summary for Performance Tuning Using SQL Server DMVs Book / Chapter 1: Using Dynamic Management Objects

Chapter 1: Using Dynamic Management Objects

• Unless you are the system administrator of the server, you will need to be granted one or two privileges to be able to use the DMOs: VIEW SERVER STATE and VIEW DATABASE STATE … P21.
• This chapter is just an introduction and a very general view of what will be explained in the next chapters.