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

Summary for Accidental DBA Book / Chapter 8: Large or Full Transaction Log

Chapter 8: Large or Full Transaction Log

• The fact that writes are always sequential also means that SQL Server will only ever write to one transaction log file at a time. There is therefore no advantage, in terms of log writing performance, to having multiple transaction log files. The only reason to have more than one log file is if space considerations dictate the need for multiple log files, on different disks, in order to achieve the necessary log size for a database … P276.
• The allocated space inside of a transaction log file is internally divided into smaller segments known as virtual log files (VLFs), and the process of log truncation is simply the act of marking a VLF as "inactive" and so making the space in that VLF available for reuse.
• In the SIMPLE recovery model, database log truncation (the mechanism through which SQL Server marks the space inside of the transaction log as available for reuse by the database.) can occur immediately upon CHECKPOINT In the FULL (or BULK LOGGED) recovery model, once a full backup of the database has been taken, the inactive portion of the log is not marked as reusable on CHECKPOINT, because it is necessary to maintain a complete LSN chain, and so allow point-in-time recovery of the database. Truncation can only occur upon a BACKUP LOG operation … P277-278.
• It is very important to size the log appropriately, and try to avoid:
1. A very high number of small VLFs, known as log file fragmentation, can have a considerable impact on performance.
2. Conversely, if the database has only a few VLFs which are large in size, this can lead to problems related to rapid log growth in cases where truncation is delayed for some reason.
3. Third reason: for log files, each growth event is a relatively expensive operation … P279.
• Index rebuilds use a lot more space in the log, but index rebuilds can be minimally logged by temporarily switching the database to run in BULK LOGGED recovery mode (in BULK LOGGED model, the LSN chain is maintained, not like SIMPLE model the LSN chain will be automatically broken). Note that the temporary change in to BULK LOGGED will still allow you to restore to point in time, but NOT in the period of the minimal logged operations, the BULK LOGGED period … P281.
• In contrast to rebuilding an index, reorganizing (defragmenting) an index, using ALTER INDEX REORGANIZE is always a fully-logged operation, regardless of the recovery model. However, index reorganizations generally require less log space than an index rebuild … P283.
• Generally, for fragmentation levels greater than 5 percent but less than or equal to 30 percent, you should reorganize the index, and for fragmentation levels greater than 30 percent, you should rebuild it … P284.
• If you suspect log growth is being caused by the log space not being reused, and to find out what's preventing reuse. Querying sys.databases, as shown in Listing 8.1. The value of the log_reuse_wait_desc column will show the current reason why log space cannot be reused ... P285.
• If the value returned for log_reuse_wait_desc, from Listing 8.1 query, is Log Backup, then you are suffering from one of the most common causes of a full or large transaction log, namely operating a database in the FULL recovery model, without taking transaction log backups (and even if a full backup is being taken, as this will: ❶Only protects the contents of the data file not the log file, which will be required for point-in-time restores. ❷Full database backups do not truncate the transaction log, only a log backup will do)... P286. 
• If the value returned for log_reuse_wait_desc is ACTIVE_TRANSACTION, then you are suffering from the second most common cause of a full or large transaction log in SQL Server: long-running or uncommitted transactions … P287-292.
• To identify whether transaction log growth is being caused by an orphaned (or just long-running) transaction is to use OPENTRAN(DatabaseName). The result will show only the oldest active transaction. In the result look first for the value of Start Time which generally if it is a long time it is a sign of uncommitted transaction. And also use the SPID withsys.dm_exec_sessions and sys.dm_exec_connections DMVs, to determine whether the transaction is actually an orphaned transaction or just a long-running one (as in Listing 8.5), If the SPID is in a runnable, running, or suspended status, then it is likely that the source of the problem is a long-running, rather than orphaned, transaction. sys.dm_tran_session_transactions and sys.dm_tran_database_transactions DMVs can be used to gather as in Listing 8.6, if in its result the open transaction was created before the last request start time, it is likely to be an orphaned transaction. The only solution to those orphaned transaction is KILL the session … P293-295.
• Other possible causes of log growth could be: slow or delayed log reader activity which can lead to log entries being left marked as "pending replication" (instead of "replicated") for long periods so the parent VLF cannot be truncated … P296-297.
• When the log_reuse_wait_desc column shows ACTIVE_BACKUP_OR_RESTORE as the current wait description, a long-running full or differential backup of the database is the most likely cause of the log reuse problems. A solution to this problem is to optimize the backup process or improving the performance of the underlying disk I/O system … P298.
• When the log_reuse_wait_desc column shows DATABASE_MIRRORING, as the current wait description, synchronous database mirroring operations may be the cause of the log reuse issues … P298.
• Error 9002, the transaction log full error, and the database is read-only. Solutions in sequence:
1. Run the query in Listing 8.1 and if the value for the log_reuse_wait_desc column is Log Backup then a lack of log backups is the likely cause of the issue. Run the query in Listing 8.7 to confirm.
2. If, for some reason, it is not possible to perform a log backup, switching _temporarily_ the database to the SIMPLE recovery model.
3. If the second option is not possible, add an addition log file on a different disk array.
4. Use  DBCC SHRINKFILE and specify a target_size to which to shrink the log file, or you can specify 0 (zero) as the target size and shrink the log to its smallest possible size, and then immediately resize it to a sensible size using ALTER DATABASE … P299-301.
   

Mismanagement or What Not To Do

• Do not detach database then delete log file to create a new clean log file, as it can result in the database failing to start, leaving it in the RECOVERY_PENDING state … P302. 
• Do not use BACKUP LOG WITH TRUNCATE_ONLY (in SQL 2000), nor BACKUP LOG TO DISK=‘NUL’ (in SQL 2008). The right way to "force" log truncation is to temporarily switch the database into the SIMPLE recovery model, as discussed earlier … P303.
• Transaction log should never be shrunk using DBCC SHRINKFILE, or a database maintenance plan step to shrink the database, as part of normal, scheduled maintenance operations. The reason for this is that every time you shrink the log, it will need to immediately grow again to store log records for subsequent transactions and every log. First, that log grow operation is expensive. Second, it will result a log file fragmentation, and both may impact the performance … P304.

• As a rule of thumb, the initial size of the transaction log should be set to 1.5 times the size of the largest index or table in the database, to allow for logging requirements to rebuild the index under FULL recovery. Also it is important to monitor and adjust the size of the transaction log periodically to fit the size of the database as it grows … P305.
• Auto-growth should be configured as a security net only, allowing the system to automatically grow the log file when you are unable to respond manually; as there are problems with the auto-growth settings that a database inherits from model: ❶causes fragmentation ❷growth of large log files can take time and if the log can't be grown fast enough this can result in 9002 (transaction log full) errors and even in the auto-growth timing out and being rolled back … P306.

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

Summary for Accidental DBA Book / Chapter 7: Handling Deadlocks

Chapter 7: Handling Deadlocks

• When the Lock Monitor finds two sessions in a deadlock: ❶ it chooses on of them as a deadlock victim, ❷ all of the locks held be the victim session are released, ❸ its current transaction is rolled back, ❹and finally it got terminated, ❺ then error 1205 is return. SQL Server selects the deadlock victim based on the following criteria:
1. Deadlock priority: The session with the lowest priority will always be chosen as the deadlock victim.
2. Rollback cost: if the two session has the same priority, the one with the lowest roll back cost will be chosen … P238.
• The Trace Flag 1222 is used to capture the deadlock graphs and presents the information in an easy way to identify the deadlock victim, as well as the resources and processes involved in the deadlock. Trace Flag 1222 is enabled in using DBCC TRACEON(), or the –T1222 startup parameter … P241.
• The Deadlock Graph event is part of the Locks event category and can be added to a SQL Server Profiler trace by selecting the event in Profiler's Trace Properties dialog. But better and to remove the overhead of the Profiler client use SP_TRACE_* system store procedures, which write the captured graphs to a SQL Trace file, and which can be read using the system function fn_trace_gettable or by opening it inside of SQL Profiler then export it to XDL files that can be opened graphically using SQL Server Management Studio … P243. 
• Event notifications allow the capture of deadlock graph information using SQL Server Service Broker, by creating a service and queue for the DEADLOCK_GRAPH trace event … P243-245.
• WMI Provider for Server Events allows WMI to be used to monitor SQL Server events as they occur. Any event that can be captured through event notifications has a corresponding WMI Event Object. SQL Server Agent can manage WMI events, through the use of WMI Query Language (WQL). How to create a SQL Agent alert to capture and store deadlock graphs: http://msdn.microsoft.com/en-us/library/ms186385.aspx ... P246.
• SQL Server 2008 (and later edtions) includes all of the previously discussed techniques for capturing deadlock graphs, and adds one new one, namely collecting the deadlock information through the system_health default event session in Extended Events … P247-248.
• Interpreting Trace Flag 1204 deadlock graphs … P249-253.
• Interpreting Trace Flag 1222 deadlock graphs … P254-257.
• Interpreting XML deadlock graphs … P257-259.
  

Common types of deadlock and how to eliminate them

• Bookmark lookup deadlocks generally occur when a SELECT statement has a shared lock on a non-clustered index and waits for to take another shared lock to lookup on a data table to complete the requested columns that are not covered be that non-clustered index, but an INSERT (or UPDATE or DELETE) statement is having an exclusive lock on the data table, resulting in a deadlock. To fix for this type of deadlock is to change the definition of the non-clustered index so that it contains, either as additional key columns or as INCLUDE columns … P260-261.
• The SERIALIZABLE isolation level is the most restrictive isolation level in SQL Server, as it uses rang locks (in place of the row or page level locking used under READ COMMITTED isolation) to ensure that no data changes can occur that affect the result set. Range locks have two components associated with their names, the lock type used to lock the range and then the lock type used for locking the individual rows within the range. For example, shared-shared (RangeS-S), and exclusive (RangeX-X). SERIALIZABLE isolation deadlocks are generally caused by lock conversion, where a lock of higher compatibility, shared-update (RangeS-U), needs to be converted to a lock of lower compatibility, such as insert-null (RangeI-N). Possible solutions:
1. If it is not necessary for the SELECT statement to be within the transaction that performs range locks, move it outside.
2. If the operation doesn't require the use of SERIALIZABLE isolation, then changing the isolation level to a less restrictive isolation level, for example READ COMMITTED.
3. Force the SELECT statement to use a lower-compatibility lock, through the use of an UPDLOCK or XLOCK table hint … P262-263.
• Cascading constraint deadlocks: In cascading constraints, SQL Server has to traverse the FOREIGN KEY hierarchy to ensure that orphaned child records are not left behind, as the result of an UPDATE or DELETE operation to a parent table. When a deadlock occurs during a cascading operation, look for non-clustered indexes that are missing for the FOREIGN KEY columns, as this will shorten the time the locks being taken to enforce the constraints; so decreasing the likelihood of a deadlock between two operations …P264.

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

Summary for Accidental DBA Book / Chapter 6: Blocking

Chapter 6: Blocking

• Some of the more commonly seen lock modes are:
• Shared: taken by queries that are reading from a table or index.
• Update: is taken as part of an update operation.
• Exclusive: is taken for any data modification (insert, update, delete). For an update, SQL first takes an update lock and then converts it to an exclusive lock to perform the actual update.
• Intent locks: is used to reduce the work SQL must do to tell if a lock can be granted. If a row lock is needed by a query, SQL first takes the appropriate intent lock at the table level, then an appropriate intent lock at the page level and then it will take the necessary row lock. Each lock mode has its associated Intent lock (shown as I). Hence you will see IS (Intent Shared) locks, IU (Intent Update) locks and IX (Intent Exclusive) locks … P195.
• SQL Server can lock a number of different types of resource, the most obvious being tables (OBJECT locks), pages (PAGE locks), and rows (RID or KEY 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 on an index or heap, or if forced to do so due to memory pressure, 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 … P196.
• In some cases, lock escalation can cause blocking because SQL has escalated locks, and so locked the entire table, with the result that concurrent access to that table is restricted. To identify lock escalations, SQL Trace or Profiler can be used with the Lock:Escalation event, in conjunction with the SP:Started or T-SQL:StmtStarted and SP:Completed or T-SQL:StmtCompleted events, to try to correlate the lock escalation with currently executing statements and procedures … P197.
• Latches can be thought of as light-weight, short-lived locks. Where locks are used to protect the logical and transactional consistency of rows, latches are typically used to protect the physical consistency of pages or memory structures. There are three types of latches that can be encountered in the SQL engine:
• Latch – used to protect various memory structures within SQL Server.
• Page Latch – used when SQL is modifying the structure of the page. So, when the page header gets modified, or a row is added to a page, SQL would take a Page Latch before starting and release it once complete.
• Page I/O Latch – used when pages are moving between disk and memory. So a Page I/O Latch would be taken before a page is fetched from disk, and released once the page is in the cache.
  Latches (usually Page Latch or Page I/O Latch) can cause blocking in much the same way as locks … P203.

Monitoring Blocking

• The wait type for locks has the form LCK_M_<lock type>. So, a wait to acquire a shared lock appears as LCK_M_S, exclusive lock will appear as LCK_M_X, and a wait for an intent shared lock will appear as LCK_M_IS and so on … P204. Latches blocking appears like Latch, Page Latch or Page I/O Latch wait rather than a LCK wait.
• Use the sysprocesses system view to find blocking as well as blocked processes, as shown in Listing 6.1. The Blocked column shows the SPID that is causing the blocking, it will have a value of 0 for any session that is not blocked. System procedures sp_who and sp_who2 are simply views onto the sysprocesses system view and can be used in much the same way. Once we have the SPIDs of the blocking and blocked sessions, we can take a look at what they are running using DBCC INPUTBUFFER([SPID number]), which will return the first 4,000 characters of the batch that each session is running. If we want to know what locks the process in blocked column that are preventing the process in SPID column from getting the shared read lock that it wants, we can query the syslockinfo system table, or use the sp_lock system stored procedure. The latter is a lot less cryptic and far easier to understand. Combining the value of waitresource column in sysprocesses with the result of running EXEC sp_lock [SPID of the blocking process] we can know the type of blocking … P205-207. 
• DBCC SQLPERF(waitstats) command. It provides the total waiting tasks and total wait time since the instance was started or since the wait stats were cleared. While this command cannot help diagnose individual blocking problems, it can be used to get an overall picture of the most common waits in the system. Above SQL Server 2000, Dynamic Management Views (DMVs) offer more in-depth information for troubleshooting the blocking problem than sysprocesses system view, and the DBCC SQLPERF(waitstats) command … P208.
• Using the sys.dm_exec_requests and sys.dm_exec_sessions DMVs in script in Listing 6.4 shows more information than sysprocesses. after finding the blocking process we can get the last command that it ran by querying sys.dm_exec_connections and using the most_recent_sql_handle column as a parameter to sys.dm_exec_sql_text, as shown in Listing 6.5 … P210-211.
• The sys.dm_os_waiting_tasks DMV is primarily used to show all waiting tasks currently active or blocked. In order to take full advantage of this DMV, JOIN on the sys.dm_exec_requests DMV and CROSS APPLY to sys.dm_exec_sql_text, like in listing 6.6. The result of this doesn't give us much more information than the result of the query in listing 6.5 , though it provides more detail on the resource on which blocking is occurring … P212-213.
• Cumulative wait statistics using  sys.dm_os_wait_stats: Any time an executing task is forced to wait for a resource in the engine, the time spent waiting is tracked, accumulatively, in the  sys.dm_os_ wait_stats  DMV. The values provided in this DMV are running totals, accumulated across all sessions since the server was last restarted or the statistics were manually reset. While the information in this DMV cannot be used on its own to diagnose individual blocking problems, it can be used to find out whether locking waits are one of the most common waits in the system. Simply run the query in  Listing 3.2 (Chapter 3)  and look for high incidences of the  LCK_*  wait types ... P215.
• As with the aggregated wait statistics, PerfMon data can be used to indicate that there may be a problem, but it is insufficient on its own to identify the cause of the problem. The main counters of interest  are  Avg  Wait  Time  (ms),  Lock  Waits/sec, and  Number  of  Deadlocks/sec ... P216.

Automated Detection and Notification of Blocking

• SQL Trace is a powerful feature that allows for the creation of event traces within SQL Server, using a set of stored procedures. SQL Trace should be utilized only when there is a need, as it will consume a great deal of resources in order to log all the events … P218.
• The blocked process report is implemented as an event that is fired every time blocking occurs that exceeds in duration the threshold value, configured by the "blocked process threshold" sp_configure option. The default value for the "blocked process threshold" is zero, meaning that the database engine doesn't perform the additional checks of waiting tasks and won't generate the blocked process reports. The blocked process report can be captured in a number of ways. SQL Server Profiler can be used to actively capture the Blocked Process Report event class, or alternatively you can create a server-side SQL Trace script, using the sp_trace_* stored procedures. When the blocked process threshold is met, the trace will log the event in the trace file. The fn_trace_gettable function can be used to read the file into a tabular format in SSMS. For further information on setting up and using the blocked process report event, see http://www.simple-talk.com/content/article.aspx?article=671  or http://bit.ly/qC6zz3 ... P219-222.
• Event notifications offer the ability to capture, in real-time, most DDL events and trace events in SQL Server. Event notifications use Service Broker queues in order to capture log the events that you wish to monitor. When used in conjunction with the BLOCKED_PROCESS_REPORT event and the LOCK_ESCALATION event, this would allow for instances of both events to be logged, asynchronously, as they happen. This asynchronous model means that event logging has a lower impact on overall server performance than when using SQL Trace or Profiler. For examples check P223-225.
• For more information on Extended Events, Jonathan's An XEvent a day series is well worth reading: http://www.sqlskills.com/blogs/jonathan/category/XEvent-a-Day-Series.aspx.
• Causes for blocking:  Bad database design (P229), Inappropriate isolation level (P229), Poorly written queries (P230), Missing indexes (P231), Poor application design (P231), User input within transactions (P231), Reading too much data (P232), Chatty applications (P232), or Outdated hardware (P232). If fixing the previous did not work, there are hints (isolation level hints, lock mode hints, or lock granularity hints … P233-235) and Trace Flags (Trace Flags 1211 and 1224 … P235-236) that influence SQL Server's locking behavior. These should be used only as short-term, temporary measures while more permanent solutions are investigated.

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

Summary for Accidental DBA Book / Chapter 5: Missing Indexes

Chapter 5: Missing Indexes

• Is it better to create multiple indexes, where each individual index has the optimal key column order, based on column selectivity, or to create a single index that covers multiple queries, but has a less selective column order? For a data warehouse, where there are significantly more read operations than write operations, the multiple indexes option may be appropriate. For an OLTP system, where there are more writes than reads, a less selective index that covers multiple queries using a less than optimal column order may be most appropriate … P165.
• A query cannot seek on an index unless the query filters on a left-based subset of the index key. In other words, it is often (though not always) best to order the index key columns such that the most selective column is the first column in the index. This reduces the number of database pages that must be read by the database engine while traversing the index, in order to satisfy the query.
• Listing 5.1 demonstrates this. Listing 5.2 is a solution for Listing 5.1 … P166.
• A covering index is one that contains all of the columns needed by a query, as either key or non-key columns, preventing the need to access the table or clustered index using lookup operations, and so decreasing the number of I/O operations required to return the data … P169.
• In data warehouse environments having indexes with long included columns is acceptable, and it is recommended to disable or drop indexes during ETL to eliminate the impact of indexes maintenance. In contrast, OLTP DB would generally use fewer included columns due to the impact on the performance of data manipulation operations … P170.
• You can use the following to identifying missing indexes: 
1. Database Engine Tuning Advisor: can be used to analyze a single query or an entire database workload, in the form of a trace file generated by SQL Server Profiler. The quality of the DTA's index analysis will only be as high as the quality of the workload that is provided, thus the trace in SQL Server Profiler should be run for a period that convers a full cycle of the work load and covers most of the queries. P174-180 shows how. 
2. Missing index feature: the database engine tracks information about indexes that do not exist but that the optimizer could have used during query execution. This information is stored in the missing index DMVs, and in the MissingIndexGroup in showplan for a query. Those missing indexes must be used with due care, to avoid overlapping or duplicate indexes, as those missing index recommendations are not workload based; they are derived from the execution of individual queries …P181. (Listing 5.10 is XQuery to the ShowPlan XML)
• Missing index DMVs: 
• sys.dm_db_missing_index_details – stores detailed information regarding indexes the optimizer would have used had they been available, such as columns that could have been used to resolve equality or inequality predicates, and suggested INCLUDE columns for covering a query.
• sys.dm_db_missing_index_columns – accepts an index_handle and returns a list of columns that would comprise the suggested index.
• sys.dm_db_missing_index_group_stats – returns summary information regarding the potential benefit of a "missing" index, based, for example, on the number of seeks and scans that would have benefited.
• sys.dm_db_missing_index_groups – a join view between _group_stats and _index_details … P182.
• Missing index DMVs limitations:
• Its information exists in memory and doesn't exist beyond SQL service restarts.
• Index key columns are not ordered according to cardinality … P182.
• To estimate performance improvement associated with a specific missing index in the database use the query in Listing 5.9 … P183. Focus on the indexes with an impact value higher than 50,000.
• A good rule of thumb is for any FOREIGN KEY columns that are commonly used in JOIN operations to have an associated index, either with the FOREIGN KEY column as the leading column in the index, or as a column further down the index key, depending on the queries. The code in Listing 5.11 can be used to identify non-indexed FOREIGN KEY columns in a database. That query is effective for database using single-column PRIMARY KEYs, yet partially helpful … P188.
• The query shown in Listing 5.12, is any non-clustered index that has never been used for a seek, scan, or lookup operation by SQL Server, but is associated with a significant number of update operations. These indexes can be considered to be unused and should be dropped from the database. This code should only be used when the database has been online for a significant period of time, in order to ensure that the appropriate workload has been executed.  … P190.
• In her following two blog posts, Kimberly Tripp offers further insight into the complications of identifying duplicate indexes, and provides a stored procedure for identifying and removing them: "How can you tell if an index is REALLY a duplicate?" (http://sqlskills.com/BLOGS/KIMBERLY/post/UnderstandingDuplicateIndexes.aspx) and "Removing duplicate indexes" (http://sqlskills.com/BLOGS/KIMBERLY/post/RemovingDuplicateIndexes.aspx) … P191.

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

Summary for Accidental DBA Book / Chapter 4: Memory Management

Chapter 4: Memory Management

• SQLOS is a self-tuning features in SQL Server, which is a dedicated thread that monitors memory notifications issued by the Windows operating system, to inform other applications of the status of memory usage in the operating system. Two such memory notifications are set by the Windows OS: 
• memory high lets SQL Server know that it can grow its working set and use additional memory 
• memory low lets SQL Server know that the operating system is under memory pressure and that SQL should try to reduce its working set to return memory to the operating system … P129.

32-bit Virtual Address Space limitations

• The Windows OS runs every process, including the SQL Server process, in its own dedicated area of virtual memory, known as the Virtual Address Space (VAS). The VAS is divided into two regions; kernel mode (or system) space and user mode (or application) space. The kernel mode VAS is used by the OS, for mapping various system data structures such as the file cache, Paged and Non-Page pools (discussed briefly later). The user mode VAS is used to map memory for the currently-executing application process (in our case, SQL Server) … P132.
• VAS tuning: In Windows Server 2000, two switches can be added to the boot.ini file to change the default 50:50 split of the VAS. The /3GB switch in the boot.ini changes the allocation so that 3 GB is allocated to the user mode VAS and 1 GB is allocated to kernel mode. There is trade-off with VAS tuning is that you end up with up to 1 GB less space for kernel mode VAS, and this can have significant consequences, so care must be taken when using VAS tuning … P136.
• On 32-bit servers on which more than 4 GB of RAM is installed, SQL Server can utilize the memory over 4 GB through the use of Address Windowing Extensions (AWE). In order for SQL Server to be able to use AWE to allocate memory, Physical Address Extensions (PAE) must first be enabled on the server. Next, the 'awe enabled' sp_configure option must be set inside of SQL Server, and the SQL Server service account must have the Lock Pages in Memory user right … P138.

Memory configuration options with 64-bit SQL Server

• One of the benefits of 64-bit systems is that the VAS limits are substantially higher, means that 64-bit SQL Server instances do not require any additional configuration in order to allocate memory over 4 GB; the AWE enabled option has no application for these instances … P141.
• SQL Server offers two instance-level settings that can be used to control how memory is allocated to, and removed from, the buffer pool:
• The min server memory option specifies the minimum size to which SQL Server can shrink the buffer pool when under memory pressure; it does not specify the minimum amount of memory that SQL Server will initially allocate.
• The max server memory option specifies the maximum amount of memory that SQL Server can use for the buffer pool, which is primarily used for caching data pages in memory … P143.
• It is recommended that the min server memory configuration be set lower than the max server memory configuration by a couple of gigabytes of memory, to allow SQL Server to resize the buffer pool as needed to respond to OS low memory notifications … P144.
• As a general base configuration, for a dedicated SQL Server machine, reserve 1 GB of RAM for the OS, 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. Then monitor the Memory\Available Mbytes performance counter on the server (along, possibly, with 145 counters like Total Server Memory and Target Server Memory, covered later) to determine the value for max server memory that leaves at least 150–300 MB of memory available at all times for the memory requirements of non-buffer pool and non-SQL memory allocations, for Windows and other applications … P145.
• If SQL Server experiences a working set trim or memory gets paged out by the OS because of memory pressure, SQL will write a message in the error log as follows: 
• A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 16484, committed (KB): 6239692, memory utilization: 0%.
• If this message is frequently seen, it indicates there is a problem with SQL getting paged out, and to prevent this, the Lock Pages in Memory privilege can be assigned to the SQL Server service account. A problem appears after running SQL Server using Lock Pages in Memory is that the information returned by Task Manager no longer reflects the total memory use by the sqlservr.exe process, and to know that the SQL SQL Server:Memory Manager\Total Server Memory performance counter should be used … P146-147.

Diagnosing Memory Pressure

• Buffer pool churn: is the repetitive cycle of flushing data pages from the buffer pool, to make room for other pages, then read it back into the buffer pool. This happens when SQL Server is forced to operate with an insufficient memory, as a result, higher physical I/O is needed to bring data pages from disk into the buffer pool as they are requested, and performance will degrade … P149.
• To tell if SQL Server is under memory pressure and needs more memory, review the following counters:
• The SQL Server:Buffer Manager\Buffer Cache Hit Ratio counter shows how often SQL Server gets data from Memory (buffer cache) as opposed to disk. Generally this counter should be greater than 95% for OLTP systems and greater than 90% for OLAP systems, to say that SQL Server is working normally, but this counter alone doesn’t tell whether or not SQL Server is under memory pressure … P150.
• The SQL Server:Buffer Manager\Page Life Expectancy (PLE) counter provides the time in seconds that a page exists in cache before being aged out to allow reuse of the cache space. Generally the value of this counter should be > (total memory in GB/4) *300 … P151.
• The SQL Server:Buffer Manager\Free Pages counter reflects the total number of free pages that exist for the SQL Server buffer pool, allowing for immediate allocations by an executing request without having to release additional pages from cache to satisfy the request. If the PLE has a value that considered low or Free Pages has a value of zero, and the system is also experiencing Free List Stalls at the same time, then that is a sure sign the instance is under memory pressure and could benefit from additional memory … P152.
• SQL Server:Buffer Manager\Free List Stalls occur whenever a request has to wait for a free page in the buffer pool. If the number of stalls exceeds zero frequently or consistently over a period of time, this is a sign of memory pressure … P152.
• The SQL Server:Buffer Manager\Lazy Writes/sec counter reflects the number of buffer pages that have been flushed by the Lazy Writer process, outside of a normal checkpoint operation, allowing the buffer to be reused for other pages. If you observe Lazy Writes occurring in conjunction with a low PLE, a low number of free pages, and the occurrence of Free List Stalls, this is a sign that the workload is exceeding the amount of memory that is available to the buffer pool, and additional memory needs to be added to the server … P152.
• SQL Server:Memory Manager\Target Server Memory (KB): total amount of memory that has been allocated by SQL Server. SQL Server:Memory Manager\Total Server Memory (KB): the amount of memory that SQL Server wants to commit. If Target Server Memory < Total Server Memory then the SQL Server process wants to commit more memory than is available on the server, which can be a sign of memory pressure …P153.
• SQL Server:Memory Manager\Memory Grants Outstanding : This counter measures the total number of processes that have successfully acquired a workspace memory grant. Low values for this counter, under periods of high user activity or heavy workload, may be a sign of memory pressure, especially if there are a high number of Memory Grants Pending … P153.
• SQL Server:Memory Manager\Memory Grants Pending: this counter measures the total number of processes that are waiting for a workspace memory grant. If this value is non-zero, it is a sign that additional memory needs to be added to the server … P154.
• There is also some information regarding memory-related waits and non-buffer pool memory allocations and so on, that can be extracted from the DMVs, such as the sys.dm_os_memory_* objects in the Operating System-related DMVs, or the sys.dm_exec_query_memory_grants DMV. A list of those counters with brief description in p154. However the writer prefers to use the counters in the previous points, in diagnosing memory pressure rather than those DMVs, as he finds the information in those DMVs header to analyze.

Common Memory-Related Problems

• Paging problems (discussed in point P146-147). The common causes are the following:
• Incorrect settings for the max server memory sp_configure option, when Lock Pages in Memory is not being used
• A large system cache in Windows caused by caching of non-buffered I/O operations such as file copy operations
• Hardware driver issues that result in memory leaks or excessive memory allocations by the driver … P156.
• OS instability due to using Lock Pages in Memory and leaving the default configuration for the max server memory sp_configure option, which means all of the memory available on the server. This leads that when OS is badly under memory pressure it can't page out or trim the SQL Server working set, this leaves the OS at the mercy of SQL Server to respond to the memory pressure fast enough to prevent the Windows OS from crashing. The same case if an inappropriately high value has been set for max server memory for the instance. For this reason, it is critical that when Lock Pages in Memory is set for a SQL Server instance, the max server memory configuration option should be set low enough to ensure that the Windows OS never gets into memory pressure … P157.
• Error 701 and FAILED_VIRTUAL_RESERVE, Inefficient memory utilization by the SQLCLR assembly, or limitations of the VAS that is available for allocation by the SQLCLR assembly. When this error is encountered on 32-bit installations of SQL Server upgrade the server to a 64-bit. However, if the SQLCLR code excessively utilizes memory by using objects like a DataSet, even the upgrade will not resolve the problem, in this case we need to run the SQLCLR assembly outside the SQL Server (in WinForms for example), and profile its memory usage. If upgrade is not possible one potential remediation is to use the –g startup parameter, to increase the size of the MemToLeave VAS Reservation, providing additional VAS to SQLCLR for memory allocations …P157-158.
• Over-provisioned virtual machines: In SQL Server VM, setting the correct min server memory configuration inside of SQL Server can allow for partial ballooning to occur, while ensuring that SQL Server continues to allocate the buffer pool memory needed … P160.

• It is recommended that min server memory be configured for each instance, to guarantee a minimum amount of memory to the buffer pool if the Windows OS sets the low memory notification, and the SQL Server instances reduce their memory allocations in response. If the min server memory configuration option is not set, a single instance of SQL Server may voluntarily reduce its memory usage to the point that it experiences performance degradation. Setting min server memory for all of the instances will prevent this from occurring, and instead cause the other instances to reduce memory usage appropriately, in response to the OS low memory notification …P161.

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

Summary for Accidental DBA Book / Chapter 3: High CPU Utilization

Chapter 3: High CPU Utilization

• If you notice that one or more of the CPUs are working at close to maximum capacity, along with a dramatic decrease in server performance, then you've likely got a CPU issue. The source of CPU pressure is not always easy to pinpoint since what seems like a CPU problem may actually have its root cause elsewhere, such as insufficient memory, causing SQL Server to constantly move data in and out of memory. The source of the CPU pressure may also be a non-SQL Server process running on the server … P73.
• The three main tools used to measure CPU usage, and diagnose CPU pressure, in SQL Server: Performance Monitor, SQLTrace, and Dynamic Management Views.
• This tool will confirm whether the excessive CPU usage is due to SQL Server activity, or is caused by other processes on the server, or the operating system itself.
• The primary PerfMon counters that are of value for monitoring CPU usage are the following with brief explanations:
• Processor/ %Privileged Time – percentage of time the processor spends on execution of Microsoft Windows kernel commands such as core operating system activity and device drivers.
• Processor/ %User Time – percentage of time the processor spends on executing user processes such as SQL Server. This includes I/O requests from SQL Server.
• Process (sqlservr.exe)/ %Processor Time – the sum of processor time on each processor for all threads of the process … P74.
• The main usage for SQL Trace is to identify specific queries that are consuming large amounts of CPU.
• Along with a wait_type column, indicating the type of wait, the sys.dm_os_wait_stats DMV returns several useful wait times, including:
• wait_time_ms – total amount of time that tasks have waited on this given wait type; this value includes the time in the signal_wait_time_ms column. The value increments from the moment a task stops execution, to wait for a resource, to the point it resumes execution.
• signal_wait_time_ms – the total amount of time tasks took to start executing after being signaled (i.e. after the resource it was waiting for became available); this is time spent on the runnable queue, and is pure CPU wait … P78.
• If the signal wait time is a significant portion of the total wait time, it means that tasks are waiting a relatively long time to resume execution after the resources that they were waiting for became available. This can indicate either that there are lots of CPU-intensive queries, which may need optimizing, or that the server needs more CPU. The query in Listing 3.1 will provide a measure of how much of the total wait time is signal wait time … P79.
• Since the wait times in this DMV are aggregated, it is hard to relate a wait time to a specific query, unless you're on a test system with only one session running. However, in SQL Server 2008, it's possible to do this if you use Extended Events. See my blog post: http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/30/an-event-a-day-30-of-31-tracking-session-and-statement-level-waits.aspx ... P79.
• We can also use the sys.dm_os_wait_stats DMV to find out which resource waits are the most common in our CPU-bound system, as shown in Listing 3.2, where we identify the top wait events, ordered according to the total amount of time processes have waited (wait_time_ms) on this event. Three interesting wait types to look out for, in regard to CPU pressure, are SOS_SCHEDULER_YIELD, CXPACKET and CMEMTHREAD … P80.
• SOS_SCHEDULER_YIELD is the time a task voluntarily relinquishes the CPU and begins waiting to resume execution. If queries show high wait times in sys.dm_exec_requests or sys.dm_os_waiting_tasks for the SOS_SCHEDULER_YIELD wait type, it's an indication that the query is extremely CPU-intensive. If there are high wait times for this wait type overall on the server it can indicate either that there are lots of CPU-intensive queries, which may need optimizing, or that the server needs more CPU … P81.
• The query shown in Listing 3.4 returns the top ten most costly queries in cache by total worker time. The *_worker_time columns represent the time spent on the CPU, and the *_elapsed_time columns show the total execution time … P83.
• Lack of appropriate indexing is one of the most common causes of heavy CPU and I/O utilization in SQL Server.
• Outdated statistic leads SQL Server Optimizer to choose a non-appropriate plan to execute a query, leading to a bad performance. To know if a query has an outdated statistic, compare the estimated and actual row counts in the execution plan of the query, if the two counts are significantly different, then one possibility is that the statistics are outdated. UPDATE STATISTICS <Table name> statement updates all statistics on a table, or just for one specific statistics set UPDATE STATISTICS <Table name> <statistic name> … P89.
• To prevent outdated statistics set Auto_Update_statistics to ON … P99.
• SARGable, where SARG stands for Search Argument; it means that that a predicate can be used in an index seek operation. The rules for SARGable predicates, in general, are that the column should be directly compared (equality or inequality) to an expression, and that any functions specified on the column will make the predicate non-SARGable. In other words, WHERE SomeFunction(Column) = @Value is not SARGable, whereas            WHERE Column = SomeOtherFunction(@Value) is SARGable. Non-SARGable predicates can result in table or index scans and, similar to the case of missing indexes, this will cause significant CPU usage as SQL has to read and process far more rows than necessary. Look at the execution plan of the following:
• For example:
  select * from table1 where CONVERT(VARCHAR(10), recordInsertedDate) = '01/01/2010'
  is non SARGable (assuming that recordInsertedDate is of type datetime and that there is an index on that feild). Such case can be solved by something like:
  select * from table1 where  recordInsertedDate>='01/01/2010'   and recordInsertedDate< '02/01/2010'.
• Another example for non-SARGable WHERE ISNULL(SomeCol,0) > 0 … P89, P90, P91, P92.
• The implicit conversions that happens sometimes in JOINs, WHERE clause (example converting varchar to nvarchar) has the non-SARGable problem. To fix this make sure that any variables, parameters or constants are of the same type as the columns to which they are being compared. If they are not, make careful use of conversion functions (CAST, CONVERT) on the variables, parameters or constants so that they match the data type of the column … P94.
• Parameter sniffing is a process used by SQL Server when creating an execution plan for a stored procedure, function, or parameterized query. The first time the plan is compiled, SQL Server will examine, or "sniff", the input parameter values supplied, and use them, in conjunction with the column statistics, to estimate the number of rows that will be touched by the query. It then uses that estimate in its costing of various possible execution plans. A problem only arises if the values that were passed as input parameters on initial plan creation, result in a row count that is atypical of that which will result from future executions of the procedure, which may have a significant impact on the performance of some of the future executions of the procedure. Parameter sniffing only occurs at the time a plan is compiled or recompiled, and all subsequent executions of the stored procedure, function, or parameterized query will use the same plan. The following are some possible solutions (P95-P99):
• Enable Trace Flag 4136: doing so prevents parameter sniffing for the whole SQL Server instance, as a result the optimizer makes a process that often results less accurate estimation of the number of rows. This will benefit the procedures with the sniffing problem, yet   although this option is available, parameter sniffing is beneficial to most procedures that are written to use typical values. Turning parameter sniffing off may inadvertently affect these plans in a negative way. As such, this Trace Flag should be considered an absolute last resort if nothing else fixes the problem … P100.
• Using the OPTIMIZE FOR hint to specify a parameter value for the optimizer to use when compiling a plan. Or OPTIMIZE FOR UNKNOWN hint, which instructs SQL Server to not use parameter sniffing at all … P101.
• Use the OPTION(RECOMPILE)  or WITH RECOMPILE option. It forces a recompile, and generation of a new plan on every execution. This means that row estimations will always be based on the current parameter value, but at the cost of increasing the execution time of the procedure; so techniques are useful when the overhead of the additional compilations is small in comparison with the performance degradation caused by reuse of inappropriate plans … P103.
• Ad hoc queries are statements sent to the optimizer that are not predefined by using stored procedures, sp_executesql or other ways to force reuse of execution plans. Ad hoc queries will cause execution plans to be generated for each and every statement. For example if you have same three queries but each with a different value hard-coded into value assignment in the WHERE clause. These three statements should produce the same execution plan, but they don't, as the optimizer sees them as completely different queries, and hence generate separate execution plans (know that: For very simple queries, SQL Server can use a technique called simple parameterization to replace the fixed values with parameters, and so allow for plan reuse). The problem with non-parameterized queries is two-fold:
• The plan cache fills up with lots of single-use plans from ad hoc queries. This may lead that the reusable plan get discard from the cache due to memory pressure.
• The compilation of these single-use plans wastes CPU. Compilation is expensive, using relatively large amounts of CPU … P104.
• Cases where a lack of parameterization is causing excessive plan compilation, or where simple (or forced) parameterization is attempted but fails, can be identified using the following counters
• SQLServer: SQL Statistics: SQL Compilations/Sec
• SQLServer: SQL Statistics: Auto-Param Attempts/Sec
• SQLServer: SQL Statistics: Failed Auto-Param/Sec … P104.
• There are two ways to solve non-parameterized ad hoc queries (the first option is preferable):
1. Fixing the application by moving those queries from the application to stored procedures, or changing those ad hoc queries to their parameterized version (Listing 3.18 and Listing 3.19 is an example) … P105.
2. If the first option is not possible, there are 2 options in SQL Server that can help alleviate the problem:
1. Set the database-level PARAMETERIZATION option to FORCED, as follows:
   ALTER DATABASE YOUR_DATABASE_NAME SET PARAMETERIZATION FORCED
   The downside of this setting that it forces SQL to use one plan for all matching queries, no matter what the values in the WHERE clause are, so we may fall in the same parameter sniffing problems we’ve mentioned earlier … P106.
2. Use optimize for ad hoc workloads. With this server-level setting, the first time a query runs, SQL Server just put a plan-stub (mark) that this query was seen before, and caches the query at the second run.  This won't reduce the number of compiles for ad hoc queries, but it will make it less likely that the plan cache will grow as much, since the initial stub takes up very little memory. As such, it reduces the chances that other plans which could be reusable will be discarded due to memory pressure (Listing 3.21) … P107.

Inappropriate parallelism

• Query parallelism is the mechanism used by the SQL query execution engine to split the work of a query into multiple threads, each of which will execute on a separate scheduler. Parallel query processing can reduce the time required to process a query by partitioning the job across multiple logical CPUs. This can be very beneficial to data warehouse and reporting operations, which have a few large queries that deal with volumes of data and only a few requests occur concurrently. However, it can be very detrimental to OLTP environments where the workload consists of lots of smaller queries executing concurrently, since the parallel operation can utilize up to all of the processor cores on the server, causing other requests to wait to execute. SQL Server has two configuration options that control the parallel execution of queries by the engine:
1. The cost threshold for parallelism option determines a threshold, when exceeded, will cause a parallel execution plan to be generated, in order to execute the user request. Analyzing the result of the query in Listing 3.22 we can determine what might be an appropriate value for the cost threshold for parallelism option … P109-110.
2. The max degree of parallelism option is to limit the number of the processors that will share the load of executing a query; so this option exists to prevent a single query from utilizing all of the processor cores on a SQL Server. Generally speaking, the appropriate value for the max degree of parallelism option depends largely on the type of workload being executed, and the ability of the other hardware subsystems to cope with the additional workload associated with parallel execution in the system. If your system is experiencing parallelism-related issues (see the Diagnosing inappropriate parallelism points), then it may be necessary to limit the degree of parallelism, in conjunction with tuning the cost threshold for parallelism to resolve the problem ... P110-113.
• Hyper-threading is an Intel technology designed to improve parallel execution by presenting to the operating system two logical cores for each physical core. This means that instead of one scheduler per processor core you get two, and so two threads can be executed "simultaneously". Generally, enable hyper-threading unless you find a good reason to turn it off … P113-115
• If parallel processing is causing a resource bottleneck in a specific system, you will find CXPACKET wait type shows up as one of the top waits for SQL Server. This wait type is set whenever a parallel process has to wait in the exchange iterator for another worker to continue processing. Sometimes the CXPACKET waits are symptoms of a problem not the actual problem. for example, when the disk I/O subsystem can't keep up with the demand required by the parallel execution of a query, the root wait type may be an IO_COMPLETION, ASYNC_IO_COMPLETION, or PAGEIOLATCH_* wait type, and scaling out the I/O subsystem is not possible. When this occurs, reducing the level of parallelism can improve overall system performance. Another example is if one of the top latch waits is ACCESS_METHODS_DATASET_PARENT, in conjunction with CXPACKET, LATCH_*, and SOS_SCHEDULER_YIELD wait types as the top waits, the level of parallelism on the system is the cause of bottlenecking and reducing the 'max degree of parallelism' sp_configure option may solve the problems. But always and before messing with the cost threshold for parallelism option or max degree of parallelism option we have to make sure that we don’t have missing indexes, outdated statistics or badly written queries because they can easily cause inappropriate parallelism … P116 -117.

• The TokenAndPermUserStore cache is an optimization that would allow caching of the results of permissions checks by users against database objects. However, could be the cause of performance problems, especially in earlier builds of SQL Server 2005. Using the query in listing 3.23, we can check is the cache constantly grows in size, and that growth is accompanied by queries waiting with a CMEMTHREAD wait type, then the size of the cache may be the cause of the high CPU usage. To solve the problem on SQL Server 2005 apply SP4, and generally also better long-term solution is to try reduce the usage of ad hoc or dynamic SQL, and move as much logic as possible into stored procedures. This cache problem should not appear on SQL 2008 and later, as it did on SQL 2005 … P118.

• Windows Server or BIOS power saving options can have negative impact on processor performance, as this feature causes under-clocking of the processors on the server. Power saving has impact on the % Processor Usage performance counter. The value for this counter is calculated based on the currently used CPU frequency, divided by the available CPU frequency. As such, an under-clocked CPU causes Windows to report higher CPU usage values, leading people to believe that the server is under heavier load than it is in reality.
• To identify if this is a problem on your system, use a free tool named CPU-Z and look for  CPU Specification, which will show the type of processor and its rated clock speed, and the Core Speed, which shows the current clock speed of the processors in the system. If the Core Speed is lower that the rated specification, then power management is reducing the performance of the system. The first thing to do is to check the current Windows power management scheme. If it is set to Balanced, change it to High Performance. If it is already High Performance, check the BIOS setting if it is set to Hardware, change it to OS Control … P121.

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

Summary for Accidental DBA Book / Chapter 2: Disk I/O Configuration

Chapter 2: Disk I/O Configuration

Random versus sequential I/O

• SQL Server employs a read-ahead mechanism that can read a number of contiguous pages, up to 128 pages on Standard Edition and 1,024 pages on Enterprise Edition, in a single I/O operation … P45.
• Sequential I/O (is any operation where the blocks can be read from, or written to, disk without having to reposition the disk head on the drive) can benefit from the read-ahead mechanism. Unlike Random I/O where the disk head on the drive has to change positions on the platter, incurring seek latency as a part of the operation, which reduces the performance and number of operations in comparison to sequential I/O … P45.
• Read operations in general, especially in OLTP systems, are random I/O operations … P46.

Choosing the Right RAID Level

• RAID technology is used to achieve the following objectives:
• Increase levels of I/O performance, measured in Input/Output Operations Per Second (IOPS).
• Increase levels of I/O throughput, measured in Megabytes Per Second.
• Increase storage capacity available in a single logical device.
• Gain data redundancy … P46.
• RAID 0: strips the data across multiple drives, allowing the read and write operations to be shared amongst the drives inside the array. This level of RAID provides the best performance for both read and write operations, but provides no redundancy or protection against data loss … P48.
• RAIN 1: provides protection against the loss of data from a single disk by mirroring the writes to a second disk, but doesn't provide added write performance to the system. RAID 1 can be used for storing a single transaction log because of sequential nature of the operations on the transaction logs, yet having multiple transaction log files will have the effect of random I/O, because of the movement of the disk head to perform operations against each of the files being written to sequentially.  … P49.
• RAID 5: is commonly known as "striping with parity;" the data is striped across multiples disks, as per RAID 0, but parity data is stored in order to provide protection from single disk failure. The minimum number of disks required for a RAID 5 array is three. RAID 5 provides redundancy with minimal reduction in storage capacity, Striping the data across multiple disks improves read performance, but the need to maintain parity data incurs a performance penalty for writes. For heavy read but low write databases, RAID 5 can be optimal for the data files. RAID 5 is not recommended for the transaction log files, due heavy write activity … P50, P51.
• RAID 6: is an extension of RAID 5 but, instead of a single distributed parity bit, it uses double-distributed parity bits. RAID 6 has a performance penalty similar to RAID 5 for write operations … P51, P52.
• RAID 10: It provides redundancy by first mirroring each disk, using RAID 1, and then striping those mirrored disks, with RAID 0, to improve performance. Cost is the problem in this configuration … P53.
• NTFS format allocation unit sizes: the 4 K default for NTFS is good for file servers and the operating system drives, but not database data files which perform better using a 64 K allocation unit … P55.
• The two most common tools used for benchmarking storage configurations for SQL Server are SQLIO and IOmeter. Of the two, IOmeter is the most flexible, and can generate mixed I/O workloads that more closely reflect what might be generated by SQL Server. IOmeter also has a graphical user interface that is used for configuring the tests and monitoring their progress. … P56.
• SQLIOSim is a tool by Microsoft to tests the storage using the same disk operations that SQL Server would perform. This tool should be used to validate that the I/O subsystem functions correctly under heavy loads, but it should not be used for performance benchmarking the configuration … P57.

Workload considerations

Data files

• The appropriate disk configuration for the data files of a database depends heavily on the read-to-write ratio for the database. SQL Server tracks the I/O usage of the database files for an instance and makes this information available in the sys.dm_io_virtual_file_stats Dynamic Management Function … P57.
• For a database that is primarily read-only, RAID 5 or RAID 6 can offer good read performance, while also maximizing the available storage. RAID 5 or 6 arrays are commonly used for data warehouses, or for storing data where write latency doesn't impact overall system performance. For OLTP implementations of heavy-write databases, RAID 1+0 provides the best performance … P58.

Log files

• Since the transaction log is written to sequentially, RAID 1 can be used in most situations.
• Having the log files for multiple highly transactional databases on the same physical disks can result in write I/O bottlenecks, often shown by high WRITELOG waits in sys.dm_os_wait_stats, and by high io_write_stall_ms values in sys.dm_io_virtual_file_stats() for the transaction log file … P58.

Special considerations for tempdb

• As a general rule, the tempdb database files should be physically separate from the user data files and transaction log files, on a dedicated disk array. Since tempdb is a writeheavy database, RAID 1 or RAID 1+0 are usually the configurations best able to support the concurrent workload of tempdb … P59.
• Creating multiple files for tempdb is highly recommended as this will reduce contention on pages when allocations are made. Generally create one file per processor, but not more than eight unless there is still contention … P60.

Diagnosing Disk I/O Issues

• A primary tool for investigating disk I/O issues is PerfMon and specifically the Physical Disk\Disk sec/Reads and Physical Disk\Disk sec/Writes counters. The key for performance is having the lowest latency possible and the guideline latency values for each of these counters are as follows:
• 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 … P65.

Common Disk I/O Problems

• The first step to resolve I/O issues it to make sure the reason is not missing indexes or poorly written queries.
• The following misconfigurations are at the heart of many of the disk I/O issues: sizing for capacity instead of I/O performance, incorrect workload isolation, incorrect partition alignment, and incorrect bandwidth using SAN configurations … P66.
• Use WMI query to investigate possible disk partition misalignment
  wmic partition get BlockSize, StartingOffset, Name, Index
  If the StartingOffset value is not evenly divisible without a remainder, or decimal result, by the stripe unit size being used by the RAID controller, then the disk is misaligned. Fixing the misalignment is easy, yet a destructive operation that erases all the data on the disk. If you are using Windows 2008 or newer, you can format your drive through Disk Management GUI with a 64K allocation unit. If you are using an older OS, you should use DISKPART … P69. 

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

Summary for Accidental DBA Book / Chapter 1: A Performance Troubleshooting Methodology

Chapter 1: A Performance Troubleshooting Methodology

Wait Statistics: the Basis for Troubleshooting

• Wait statistics is a good place to begin troubleshooting SQL Server performance problems. The Wait time statistics that SQL Server has to wait to execute an operation is tracked by SQLOS and can be viewed by querying sys.dm_os_wait_stats DMV. Combining this information with the information in PerfMon, and other DMVs can provide significant insight into the cause of the performance problems … p23.
• Those wait statistics are erased every time the server restarted, and keep accumulated after that. For analysis you need the period of accumulation to be not less than two weeks, in order to ensure the stats cover the entire workload … p24.
• Listing 1.1 is a query to get those waits that are non-problematic as they are a result of normal SQL Server operations … p24.
• Listing 1.2 is a query that filters out the non-problematic wait types, and finds the top ten cumulative wait events. Then a brief description what the writer conceder as a problematic wait types, each of these are covered in more details in later chapters … p25, p26, p27.
• Listing 1.3 is a command to reset wait statistics that was tracked by the server … P28.

Virtual File Statistics

• We have to examine virtual file statistics, along with wait statistics, because most SQL Servers will show signs of what looks like a disk I/O bottleneck, and using wait statistics alone, may lead to misdiagnose the root cause … P28.
• Listing 1.4 is querying sys.dm_io_virtual_file_stats function, which exposes virtual file statistics, such as providing cumulative physical I/O statistics, the number of reads and writes on each data file and on each log file (from which can be calculated the ratio of reads to write), and the number of I/O stalls and the stall time associated with the requests (which is the total amount of time sessions have waited for I/O to be completed on the file) … P29.

Performance Counters

• Performance Analysis of Logs (PAL) is a good tool to collect a large subset of windows as well as SQL Server counters. This tool is free and available by Microsoft … P30.
• Listing 1.5 is a query to sys.dm_os_performance_counters DMV which its result shows counters that provide critical information to determining how to continue with the troubleshooting process … P32, P33, P34, P35.
• In the result of the query of listing 1.5: a good situation is when “Index Searches/sec” is higher than “Full Scans/sec by a factor” by a factor of 800-1000 (both are Access Methods counter). If the number of “Full Scans/sec” is too high, then missing indexes resulting excess I/O operations … P36.
• In the result of the query of listing 1.5: One of the counters that may tell you if you have memory pressure is Page Life Expectancy (PLE), which is the number of seconds a page will remain in the data cache. To calculate the accepted value of PLE:
                   (Total RAM in GB/4) * 300
  PLE is consistently below this value value, and the server is experiencing high Lazy Writes/sec, which are page flushes from the buffer cache outside of the normal CHECKPOINT process, then the server is most likely experiencing data cache memory pressure, which will also increase the disk I/O being performed by the SQL Server … P36.
• In the result of the query of listing 1.5: The higher the number of SQL Compilations/sec in relation to the Batch Requests/sec, the more likely the SQL Server is experiencing an ad hoc workload that is not making optimal using of plan caching. The higher the number of SQL Re-Compilations/sec in relation to the Batch Requests/ sec, the more likely it is that there is an inefficiency in the code design that is forcing a recompile of the code being executed in the SQL Server… P37.
• The Memory Manager\Memory Grants Pending counter is the number of processes waiting on a workspace memory grant. If this counter is high, SQL Server may benefit from additional memory, but there may be query inefficiencies that are causing excessive memory grant requirements … P37.

Plan Cache Usage

• SQL Server stores execution information for each of the plans in the Plan Cache, until the plan is flushed from the cache. This information can be view by querying sys.dm_exec_query_stats DMV, as shown in Listing 1.6 which lists the top ten statements based on the average number of physical reads that the statements performed as a part of their execution.
  sys.dm_exec_query_stats DMV can also identify the statements that have taken the most CPU time, the longest execution time, or that have been executed the most frequently.
  Applying the plan_handle column value from the sys.dm_exec_query_stats into sys.dm_exec_query_plan() function can be used to get a specific plan which can be analyzed to identify problematic operations … P38, P39.

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