Chapter 4 Much Ado About Indexes

The chapter opens with the observation that healthy indexes are the key to healthy SQL. Useful scripts are provided to find missing indexes, duplicate indexes, and fragmented indexes.

The chapter looks at the fundamentals of indexes, what an index is, the various types, and index terminology (terms discussed include: clustered, non clustered, unique, composite, included, covering, and filtered). Next the advantages and disadvantages of indexes are briefly discussed.

Various index-related DMVs are discussed, these include: sys.dm_db_index_operational_stats, sys.dm_db_index_usage_stats, and sys.dm_db_index_physical_stats. The importance of the clustered index is highlighted, and its desired attributes explained. The columns involved in WHERE clauses are often good candidates for indexes. The importance of not over-indexing is explained.

Data changes can lead to indexes becoming fragmented, this can degrade performance. The impact of fill factor on data changes and data retrieval is explained. Code is provided to identify the degree of fragmentation.

The chapter next discusses common index issues together with potential solutions. Changing the fill factor can produce less fragmentation, however you get less data per page read. Depending on the level of fragmentation, indexes can be reorganized or rebuilt. There’s a useful reference to Michelle Ufford’s index defrag maintenance script. Code is provided to identify missing indexes, adding these may improve the performance of your queries. Duplicate indexes can slow modifications, and take up unnecessary space, code is provided to identify these.

The chapter ends with a look at the Database Engine Tuning Advisor (DTA). This useful tool can analyse a workload (e.g. the plan cache) and recommend the best indexes, indexed views, partitions etc. A useful step-by-step walkthrough of DTA is provided.

This chapter highlights the importance of indexes, showing how to use DMVs and perfMon counters to identify indexing problems, and provides discussions on how to improve indexes. Additionally, useful code is provided to identify missing indexes, duplicate indexes, and fragmented indexes. There’s a helpful walkthrough on how to use DTA.

The chapter seems to confuse the attributes of a clustered index with that of a primary key (i.e. narrow, unique, static, and ever-increasing), although the primary key is often also the clustered index. It states the clustered index should be on the most common column in the WHERE clause - to me, the clustered index should be driven by the most “important” query(s), which may, for example, require the clustered index to be on invoice date – the author is not really wrong, but perhaps lax with his language. The chapter says “All inserts and deletions affect all indexes”, this is not true of filtered indexes. Perhaps the DTA section should have been in the tools sections?

Chapter 5 Tools of the Trade: Basic Training

This chapter discusses some of the basic tools to help with your healthy SQL. The tools discussed mostly relate to SQL Server Management Studio (SSMS), they include: 

• Activity Monitor – quickly shows what’s currently happening on the server instance (e.g. CPU usage, waiting tasks, I/O usage, recent expensive queries etc)

• sp_whoisactive – identifies blocking, locking, waits, etc. Very useful for troubleshooting

• SSMS standard reports – canned reports included with SQL Server (e.g. top queries by CPU, disk usage, blocking transactions etc)

• SQL Server 2012 Performance Dashboard – quickly find current bottlenecks, capture and report diagnostics (e.g. CPU and IO bottlenecks, index recommendations, blocking)

• DMVs/DMFs – various queries provided for investigating perfMon counters, and problems with IO, CPU, memory, transactions, locks etc

• SQL profiler/trace – records what is happening. The default trace an be very useful for identifying certain changes

• PerfMon – the Windows tool, rather than the subset of counters available via the DMVs

• Data Collector – allows you to collect and store data for future analysis. Can store in MDW

• Management Data Warehouse (MDW) – this tool stores monitor data, useful for comparing with baseline, monitoring and troubleshooting. 

This is a helpful overview of some of the basic tools that can help you monitor and diagnose your SQL Server. Some step-by step walkthroughs of tool installation are given (e.g. SQL Server 2012 Performance Dashboard). The DMV scripts and links in particular are very useful. Some helpful website links are given.

Perhaps the chapter should say SQL trace is deprecated (it does so in chapter 6, but it’s better to mention it on first use). The section on sys.dm_os_performance _counters shows an empty grid.

Chapter 6 Expanding Your Tool Set

This chapter continues the tools theme, including newer and more advanced tools. The chapter opens with a look at the new tools included with SQL Server 2012, these include: 

• Extended Events –the lightweight replacement for SQL profiler/trace

• The New Session Wizard – GUI to create an XE session. Useful step-by-step creation of XE session for monitoring locks is provided

• The system_health Session – built-in session, similar to the default trace

• The sp_server_diagnostics Procedure – checks health every 5 minutes. Areas examined: system, resource, query processing, IO subsystem, and events

• XQuery – queries XML. Example given queries sp_server_diagnostics data

• SQL Server 2012 System Health Reporting Dashboard – displays health information graphically. 15 reports e.g. memory usage, blocking, queries waiting etc 

The chapter continues with a look at other downloadable tools that are free, these include: 

• PowerShell – a powerful scripting language. Links to teaching websites

• SQLPowerDoc – useful as documentation and inventory system. Output formatted to Excel spreadsheet with tabs

• Performance Analysis of Logs (PAL) – analyses perfMon counter logs against industry known thresholds. Shows step by step usage

• SQL Server Best Practice Analyzer (Through 2012) – checks instances are configured properly and follow Microsoft’s Best Practices    

This is a helpful overview of some of the more advanced and free tools that can help monitor and diagnose your SQL Server. Some step-by step walkthroughs of installing the tools are given (e.g. PAL). Some useful links are provided.

Chapter says “Luckily, all the previous functionality of SQL Profiler is available with Extended Events.”, however this is not completely true, XEs do not have profiler’s replay facility. Code should have been included here to decode what has been captured by the system_health XE session (it is given in chapter 8). The section on XQuery doesn’t use XQuery, it uses OpenXML.

Chapter 7 Creating a SQL Health Repository

This chapter shows you how to create a repository to collect and store data. This can be used to create baselines, trend analysis, reporting, auditing etc.

The chapter opens with a look at some simpler methods of storing data (e.g. spreadsheets). MDW can be used to create a central repository, and SQL Agent jobs can be run on the client instances to populate this central repository.

The chapter has a step-by-step walkthrough for creating a centralised MDW, via the MDW wizard. For data collection, the MDW wizard should be run in each of the instances you want to collect metrics from, to set up local data collection sets. This will create SQL Agent jobs that move data from the cache and send it to the central MDW. Archiving jobs are also created. Common problems relating to Accounts, Privileges, Rights, and Credentials are then discussed.

The data collected so far has been from the default data collections. It is possible to create your own custom collections. An example shows how to collect data from the DMV sys.dm_exec_query_stats.

There are some problems associated with using MDW, these include: it can be difficult to remove, there is a performance cost, and it can use a lot of space. To get around these problems, you can create your own MDW-like system, using DMVs, SQL Agent jobs, stored procedures, and SQL Server Reporting Services (SSRS) reports. A code example is given that collects wait stats data.

This chapter provides a useful overview of why MDW should be used. Additionally, it has a very useful step-by-step walkthrough of setting up and using MDW.

MDW is potentially a very useful tool, however, I’ve never seen it implemented, instead shops seem to prefer creating their own custom solutions. I suspect part of the problem is that servers are often under pressure without adding more via the relatively complex MDW.

Chapter 8 Monitoring and Reporting

The chapter opens with a look at the need to be proactive and fix errors before they are escalated, this requires monitoring and alerting to be in place. Various items can be monitored, including: resources, blocking, the error log, threshold, trends and patterns.

To process alerts, Database Mail needs to be setup. There’s a step-by-step look at configuring Database Mail, using its wizard. This is followed a walkthrough on setting up an operator (to receive the alerts), then the setting up of various SQL Agent Alerts. The examples given monitor for severity codes 17-25 (severe errors) and error codes 823, 824, and 825 (severe IO errors).

The chapter continues with a look at monitoring using XEs. Useful SQL code is provided to count the types of errors/conditions captured by the system_health XE (e.g. waits). Next, deadlocks are examined, they are automatically captured in the system_health XE, making the collection of information much easier than in earlier SQL Server versions. Code is provided to create a deadlock, and query its XE data. Tips are given on how to reduce deadlocks. Next, an XE session that records blocking information is created, and code provided to analyse it. The section ends with a look at monitoring errors using XEs, code is provide to analyse the errors captured in the system_health XE.

The chapter ends with a look at reporting on the health of the instance using SSRS reports. MDW contains several built-in reports that can report on various areas, including: CPU%, memory, IO, network, waits, and some SQL Server perfMon counters. Additionally, it is possible to create your own custom SSRS reports relatively easily, and a step-by-step example report is given for wait stats.

This chapter provides a practical discussion on how to perform monitoring and alerting on your instances, additionally some very useful code is given for analysing deadlocks, blocking and other errors. If this data is saved in the MDW repository, various build-in reports can easily report on it.

The use of “ROLLBACK IMMEDIATE” in the chapter should have come with a warning, since it will forcibly rollback any open transactions currently running. The chapter says “I will talk about database corruption in Chapter 8.” this should read chapter 9. Other useful tips to reduce deadlocks not mentioned are: set the deadlock_priority, and adding an index.