"Operating system error 112(There is not enough space on the disk.) encountered." Error When Starting SQL Server Service After Changing The Initial Size or Location of [tempdb] Data File.

You've changed the location or the initial size of the data file of the [tempdb], then you restarted SQL Server services, but that failed with errors in the Event Viewer like the following:

The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file '[some path]\tempDB.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

and

[some path]\tempDB.mdf: Operating system error 112(There is not enough space on the disk.) encountered.


Solution:

1. Go to SQL Server Configuration Manager and change the SQL Server services account to [NT AUTHORITY\NetworkService].
2. Make sure you are login into windows using a machine administrator.
3. Run CMD as administrator.
4. Execute the following command:
    net start mssqlserver /f
   this will start SQL Server services with the minimal requirement and configuration, and in single user mode that allows just one administrator to login.
5. Immediately after that run the following command:
   osql -E
   you will connect to SQL Server using the current windows user (which should be machine admin), through CMD, and using command line.
6. Now change the initial size to a size you think it is available on where the tempdb data file is located. For example, the following will change the initial size of the tempdb data file to 1GB:
   alter database tempdb modify file ( name=tempdev ,size=1000MB);
   go
7. If the above point finished successfully, go back to SQL Server Configuration Manager and change back the account that runs SQL Server service to the one you want, then try to start the service again.

SQL Server Storage Monitoring

Use the following query (by Paul Randal)  to break the I/O workload by disk:

SELECT

    --virtual file latency

    ReadLatency =

              CASE WHEN num_of_reads = 0

                     THEN 0 ELSE (io_stall_read_ms / num_of_reads) END,

       WriteLatency =

              CASE WHEN num_of_writes = 0

                     THEN 0 ELSE (io_stall_write_ms / num_of_writes) END,

       Latency =

              CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)

                     THEN 0 ELSE (io_stall / (num_of_reads + num_of_writes)) END,

  --avg bytes per IOP

       AvgBPerRead =

              CASE WHEN num_of_reads = 0

                     THEN 0 ELSE (num_of_bytes_read / num_of_reads) END,

       AvgBPerWrite =

              CASE WHEN io_stall_write_ms = 0

                     THEN 0 ELSE (num_of_bytes_written / num_of_writes) END,

       AvgBPerTransfer =

              CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)

                     THEN 0 ELSE

                           ((num_of_bytes_read + num_of_bytes_written) /

                           (num_of_reads + num_of_writes)) END,

            

       LEFT (mf.physical_name, 2) AS Drive,

       DB_NAME (vfs.database_id) AS DB,

       vfs.*,

       mf.physical_name

FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS vfs

JOIN sys.master_files AS mf

       ON vfs.database_id = mf.database_id

       AND vfs.file_id = mf.file_id

--WHERE vfs.file_id = 2 -- log files

-- ORDER BY Latency DESC

-- ORDER BY ReadLatency DESC

ORDER BY WriteLatency DESC

PerfMon counters:

• Physical Disk Object: Avg. Disk Queue Length represents the average number of physical read and write requests that were queued on the selected physical disk during the sampling period. If your disk queue length frequently exceeds a value of 2 during peak usage of SQL Server, then you might have an I/O bottleneck.
• Avg. Disk Sec/Read is the average time, in seconds, of a read of data from the disk. Any number:
• Less than 10 ms = good performance
• Between 10 ms and 20 ms = slow performance
• Between 20 ms and 50 ms = poor performance
• Greater than 50 ms = significant performance problem.
• Avg. Disk Sec/Write is the average time, in seconds, of a write of data to the disk. Please refer to the guideline in the previous bullet.
• Physical Disk: %Disk Time is the percentage of elapsed time that the selected disk drive was busy servicing read or write requests. A general guideline is that if this value is greater than 50 percent, it represents an I/O bottleneck.
• Avg. Disk Reads/Sec is the rate of read operations on the disk. You need to make sure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity.
• Avg. Disk Writes/Sec is the rate of write operations on the disk. Make sure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity.

SQL Server 2005 Waits and Queues SQL Server Best Practices Article --- Summary

• A session process rotates between or falls in the following statuses: Running (only one session can be running or executing, per scheduler), Runnable (sessions waiting for CPU), or Suspended.  SPIDs with suspended statuses are placed in Waiter List until the requested resources are available (e.g. If a running session needs a data page that is not in cache, or needs a page that is blocked by another user’s lock)
• The current runnable queue is found in sys.dm_exec_requests where the status is “runnable”. The total time that is spent waiting in sys.dm_os_waiting_tasks is found in the column wait_time_ms and the time that is spent waiting for CPU in the runnable queue is called signal_wait _time_ms. Resource waits can be computed by subtracting signal_wait_time_ms from wait_time_ms. The difference between resource and signal waits shows the extent of CPU pressure, if any, on overall performance. A low signal (where signal is less than 25% of the total waits) to resource wait ratio indicates there is little CPU pressure.
• Common scenarios to avoid in OLTP:
• High Frequency queries having a high number of table joins (>4).
• Frequently updated tables having # indexes(>3).
• Table Scans and Range Scans.
• Unused Indexes.
• Signal wait >25%
• Plan reuse <90%
• Parallelism: Cxpacket waits >5%
• Page life expectancy < Total RAM /4*300
• Memory Grants Pending >1
• SQL cache hit ratio <90
• Average Disk sec/read >20
• Average Disk sec/write >20
• If Top 2 values for wait stats are any of the following: ASYNCH_IO_COMPLETION, IO_COMPLETION, LOGMGR,WRITELOG, PAGEIOLATCH_x, there is IO bottleneck.
• Block percentage >2%
• *Average Row Lock Waits >100ms
• *If Top 2 values for wait stats are any of the following: LCK_M_BU, LCK_M_IS, LCK_M_IU, LCK_M_IX, LCK_M_RIn_NL, LCK_M_RIn_S, LCK_M_RIn_U, LCK_M_RIn_X, LCK_M_RS_S, LCK_M_RS_U, LCK_M_RX_S, LCK_M_RX_U, LCK_M_RX_X,LCK_M_S, LCK_M_SCH_M, LCK_M_SCH_S, LCK_M_SIU, LCK_M_SIX, LCK_M_U, LCK_M_UIX, LCK_M_X
• High number of deadlocks >5 per hour 
• Common Scenarios to avoid with DataWarehousing:
• Excessive fragmentation: Average fragmentation_in_percent >25%
• Table scans and ranges >=1
• Signal waits >25% 
• Plan reuse >25%  Data warehouse has fewer transactions than OLTP, each with significantly bigger IO. Therefore, having the correct plan is more important than reusing a plan. Unlike OLTP, data warehousequeries are not identical.
• Parallelism: Cxpacket wait < 10%
• Memory grants pending > 1
• Average Disk sec/read > 20 ms
• Average Disk sec/write > 20 ms
• If Top 2 values for wait stats are any of the following: ASYNCH_IO_COMPLETION, IO_COMPLETION, LOGMGR, WRITELOG, PAGEIOLATCH_x, then there is IO bottleneck.
• Block percentage >2% 
• Average Row Lock Waits >100ms 
• If Top 2 values for wait stats are any of the following: LCK_M_BU, LCK_M_IS, LCK_M_IU, LCK_M_IX, LCK_M_RIn_NL, LCK_M_RIn_S, LCK_M_RIn_U, LCK_M_RIn_X, LCK_M_RS_S, LCK_M_RS_U, LCK_M_RX_S, LCK_M_RX_U, LCK_M_RX_X,LCK_M_S, LCK_M_SCH_M, LCK_M_SCH_S, LCK_M_SIU, LCK_M_SIX, LCK_M_U, LCK_M_UIX, LCK_M_X 
• Latencies caused by disk to memory transfers frequently surface as PageIOLatch waits.  Memory pressure or disk IO subsystem issues can also increase PageIOLatch waits. Consequently, high values for both PageIOLatch_ex and PageIOLatch_sh wait types can indicate IO subsystem issues. Pertinent performance counters include Physical disk: disk seconds/read and Physical disk: disk seconds/write and SQL Server Buffer Manager: Page Life Expectancy.
• If IO_stalls is inordinately high for one or more files, it is possible that there is either a disk bottleneck or that high reads and writes areoccurring on one drive. This should be corroborated with Performance Monitor countersPhysical Disk: Average Disk Seconds/Read and Average Disk Seconds/Write. The script in page 16 computes the Average Disk Seconds/Read and Average Disk Seconds/Write.
• OLTP applications should have high plan reuse, > 90%. To compute that:Initial Compilations = SQL Compilations/sec – SQL Re-Compilations/secPlan reuse = (Batch requests/sec – Initial Compilations/sec) / Batch requests/sec.Memory pressure can cause query plans to be discarded and therefore result in reduced plan reuse.

 ASYNC_IO_COMPLETION: 

Occurs when a task is waiting for asynchronous I/Os to finish.

Identify disk bottlenecks, by using PerfmonCounters, Profiler, sys.dm_io_virtual_file_stats and SHOWPLAN.

Any of the following reducesthese waits:

1. Adding additional IO bandwidth.

12. Balancing IO across other drives.

13. Reducing IO with appropriateindexing. 

14. Check for bad query plans.

15. Check for memory pressure.  

See PERFMON Physical Disk performancecounters: 

1. Disk sec/read

16. Disk sec/write

17. Disk queues

See PERFMONSQLServer:Buffer Manager performancecounters for memory pressure:

1. Page Life Expectancy

18. Checkpoint pages/sec

19. Lazy writes/sec

See PERFMONSQLServer:Access Methods for correct indexing:

1. Full Scans/sec

20. Index seeks/sec

SQL Profiler can be used to identify which Transact-SQL statements do scans. Select the scans event class and events scan:started and scan:completed. Include the object Id data column. Save the profiler trace to a trace table, and then search for the scans event. The scan:completed event providesassociated IO so that you can also search for high reads, writes, and duration.

Check SHOWPLAN for bad query plans.

Reference: http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc

Summary for SQL Server on VMware Best Practices Guide

The following is a summary for SQL Server on VMware Best Practices Guide:

• Using VMware Capacity Planner™, this service collects all essential performance metrics, including processor, disk, memory, and network statistics, and specific SQL Server metrics on existing database servers in the environment. Capacity Planner analyzes this data to recommend server consolidation opportunities. This exercise helps you to understand what resources your current physical SQL Servers use, and makes it easier for you to create a short list of SQL Server instances to virtualize and to determine the order in which you should virtualize the SQL Server instances.
• We strongly recommend using a VMware enterprise-class hypervisor, vSphere, to deploy virtualized SQL Server instances, even for development and test environments.

CPU

• When maximizing performance is generally the primary goal for these workloads: Provide CPU resources by maintaining a 1:1 ratio of the physical cores to vCPUs. For example, if the workloads run on a 4 CPU core machine in the physical system, allocate 4 vCPU on the vSphere host for the virtual machine. 
• For small SQL Server virtual machines, allocate virtual machine CPUs equal to or less than the number of cores in each physical NUMA node. For wide SQL Server virtual machines, size virtual machine CPUs to align with physical NUMA boundaries. Configure vNUMA to enable SQL Server NUMA optimization to take advantage of managing memory locality.
• Hyperthreading Sharing on the Properties tab of a virtual machine provides control of whether a virtual machine should be scheduled to share a physical processor if hyperthreading is enabled on the host. Choose one of the following settings:
• Any – This is the default setting. The vCPUs of this virtual machine can freely share cores with other virtual CPUs of this or other virtual machines.
• None – The vCPUs of this virtual machine have exclusive use of a processor whenever they are scheduled to the core. Selecting None in effect disables hyperthreading for your virtual machine. When maximizing performance is generally the primary goal for these workloads.
• Internal – This option is similar to none. Virtual CPUs from this virtual machine cannot share cores with virtual CPUs from other virtual machines. They can share cores with the other virtual CPUs from the same virtual machine.

Memory

• To avoid performance latency resulting from remote memory accesses, you should size an SQL Server virtual machine’s memory so it is less than the amount available per NUMA node.
• Large pages can significantly improve the performance of this workload, compared to running the workload using small pages; so if the server (the VM) is SQL Server dedicated (or all major servers on that VM) can benefit from large pages then: ❶enable large page on windows and ❷ enable large page in SQL Server. This is possible when the following conditions are met:
• You are using SQL Server Enterprise Edition.
• The computer has 8GB or more of physical RAM.
• The Lock Pages in Memory privilege is set for the service account…. I personally see granting this (Lock Pages in Memory) as dangerous option to play with and has to be set after very careful considerations.    
• When calculating the amount of memory to provision for the virtual machine, use the following formulas:
  VM Memory = SQL Max Server Memory + ThreadStack + OS Mem + VM Overhead
  ThreadStack = SQL Max Worker Threads * ThreadStackSize

The value for 'max worker threads' can be found in the max_worker_count column of sys.dm_os_sys_info. ThreadStackSize = 1MB on x86 = 2MB on x64 = 4MB on IA64. OS Mem: 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM.

Storage

• A single Iometer thread running in a virtual machine can saturate the bandwidth of the respective networks for all four storage protocols (NFS, SW iSCSI, HW iSCSI, and Fibre Channel), for both read and write. Fibre Channel throughput performance is higher because of the higher bandwidth of the Fibre Channel link. For the IP-based protocols, there is no significant throughput difference for most block sizes.
• It is preferable to deploy virtual machine files on shared storage to take advantage of vSphere vMotion, vSphere HA, and vSphere DRS. This is considered a best practice for mission-critical SQL Server deployments that are often installed on third-party, shared-storage management solutions. VMware recommends that you set up a minimum of four paths from a vSphere host to a storage array. This means that each host requires at least two HBA ports.
• Create VMFS partitions from within VMware vCenter. They are aligned by default. Align the data disk for heavy I/O workloads using diskpart or, with Windows Server 2008, the disk is automatically aligned to a 1 MB boundary.
• If you are deploying a heavy workload SQL Server, VMware recommends that you consider using eagerzeroedthick disks for SQL Server data, transaction log, and tempdb files. An eagerzeroedthick disk can be configured by selecting the Thick Provision Eager Zeroed option in vSphere 5.x. 
• Place SQL Server binary, log, and data files into separate VMDKs (Virtual Machine Disk). In additional to the performance advantage, separating SQL Server binary from data and log also provides better flexibility for backup. The OS/SQL Server Binary VMDK can be backed up with snapshot-based backups, such as VMware Data Recovery. The SQL Server data and log files can be backed up through traditional database backup solutions.
• Maintain 1:1 mapping between VMDKs and LUNs. When this is not possible, group VMDKs and SQL Server files with similar I/O characteristics on common LUNs.
• Use multiple vSCSI adapters. Place SQL Server binary, data, log onto separate vSCSI adapter optimizes I/O by distributing load across multiple target devices.

Networking:

• Use two physical NICs per vSwitch, and if possible, uplink the physical NICs to separate physical switches.
• Use separate physical NICs for management traffic (vSphere vMotion, FT logging, VMkernel) and virtual machine traffic.
• If using iSCSI, the network adapters should be dedicated to either network communication or iSCSI, but not both.

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.

SQL Server Table Partitioning without Enterprise Edition

The idea is to create a table for each group of rows from your original big table (based on period, for example), then union them all in a view. You can use that view in any DML operation... this is explained in:

• SQL Server Partitioning without Enterprise Edition by Barry King
• or Using Partitioned Views in Books Online

Summary for Accidental DBA Book / Chapter 9: Truncated Tables, Dropped Objects and Other Accidents Waiting to Happen

Chapter 9: Truncated Tables, Dropped Objects and Other Accidents Waiting to Happen

• Marked transactions can be used to create a known recovery point for significant changes to a database, or multiple databases when the same transaction mark is used in multiple databases, to establish a common recovery point for all of the affected databases. DBA can use them to create an easy recovery point, prior to deploying a large set of changes to a database, in cases where the potential for problems exists … P311-318.
• Point-in-time recovery can be used to apply the transaction logs up to a known point in time before the data loss occurred. If the exact time when the data loss occurred is unknown, one option is to restore a backup of the database in STANDBY mode. This allows further log backups to be restored but, unlike when using NORECOVERY, the database is still readable …P319-320.
• If the database is in SIMPLE recovery, there is no real hope of recovering with zero data loss, since the only recovery point is the latest full or differential backup. The same happens if the database is in FULL recovery but there has never been a full database backup, it won't be possible to recover the lost data. When a database is changed from the SIMPLE recovery model to the FULL recovery model, the transaction log continues to be truncated at CHECKPOINT as it would under SIMPLE recovery until a full backup of the database is taken, which restarts the log chain. If the database is in FULL recovery, and it has had a full backup since the database was switched to FULL recovery, but no log backups (and no one ever took a log backup and deleted it), then you can take a log backup and proceed as the process of restoring to a point-in-time …P321.
• Log recovery tools … P322.
• Default trace is active by default. It captures a number of important trace events that can be used to identify changes made to the database schema, and who made them. However, the default trace does not contain any information about data modification statements (INSERT, UPDATE, DELETE). Details of the default trace, including rollover characteristics, the file to which the trace is writing, and so on, can be found through the sys.traces dynamic management view (Listing 9.15). The full list of events that the default trace collects can be found by running the query in Listing 9.16. The contents of a trace file can be read using the sys.fn_trace_gettable system function (Listing 9.17) … P324-325.
• DML triggers can be created on a table or view and execute code in response to any data manipulation language event (INSERT, UPDATE, or DELETE) on the parent object. DML triggers can be used to provide audit tracking of all changes in a table, by writing information about the changes to a secondary table … P332-P336.
• DDL triggers can be used to log database changes as well as prevent the changes from occurring at all. Unlike DML triggers, DDL triggers can be scoped to a specific database, or at the server level, and they can be configured to fire in response to a much larger set of events. DDL events are grouped into a hierarchy to allow a trigger to fire for multiple events while simplifying the trigger's definition … P337.

Reference: http://www.amazon.com/Troubleshooting-SQL-Server-Guide-Accidental/dp/1906434786