Chapter 7 Dynamic Management Views and Dynamic Management Functions

DMVs give you a view on internal system metrics that can be very useful in monitoring, analysing and troubleshooting systems. The chapter starts with a good overview of the major DMV groups, followed by recipes for monitoring current query execution, index performance, tempdb usage, and disk I/O usage.

The chapter’s scripts are interesting and useful, especially the one showing what SQL is currently running. However this could have been extended to show what individual line of SQL within a stored procedure or batch is executing. The script used to get the degree of fragmentation should have included a warning, if the table/index is large, the script can take a long time to run, and it can also block other queries. Perhaps more emphasis could have been placed on the importance of tempdb, since it can be used by all the other databases on the server, and is often the most used database. No information was given about the average disk read/write times, but this can be an easy way to quickly determine if certain disks have problems.

Overall a useful chapter.

Chapter 8 SQL Server Cache and Stored Procedure Recompilations

An execution plan contains details of how SQL is executed. Creating the execution plan can take time, be heavy on resources, and lead to resource usage spikes. So, reusing an execution plan is generally a good idea. However, sometimes a plan cannot be reused, so the query is recompiled, which can be a bad thing.

The chapter starts with an explanation of the importance of execution plan reuse, this is followed by a list of reasons why a query may be recompiled (e.g. temp table usage, statistics have changed, new index created etc). Scripts are provided to monitor recompilation via PerfMon and Profiler.

There are some useful links to Microsoft documents on plan cache in 2008 and compilation/recompilation in 2005. These are very detailed and useful papers, if you want to know more about SQL Server I strongly urge you to read them:

http://msdn.microsoft.com/en-us/library/ee343986%28v=sql.100%29.aspx

and

http://technet.microsoft.com/library/Cc966425

Chapter 9 Implementing indexes

Tables are typically ordered to improve SQL query performance, ordered tables are called clustered indexes. Unordered tables are heaps. Non-clustered indexes duplicate data, but are often useful for quickly retrieving a small subset of specific data.

The chapter provides recipes that illustrate how performance can be improved by using a clustered index, a non-clustered index, a covering index, included index columns, filtered index, and a columnstore index. As always, the recipes provide step-by-step detailed walkthroughs.

Again, bad grammar detracts from what is otherwise good content. For example, “That is why it is mandatory to have a proper index on proper column(s) of the table”, and “Well, this is when index makes an entry into the life of a database administrator!”

Chapter 10 Maintaining Indexes

Indexes are very important structures for performance, however as their data changes, performance can degrade. Problems occur due to fragmentation. Typically, indexes are rebuilt or reorganized as part of daily or weekly maintenance.

The chapter discussions fragmentation, fill factor, rebuild v reorganize, online v offline, missing indexes, unused indexes, indexed views and index disk space usage. Scripts are provided to illustrate most of the items discussed. There’s a useful tip about not using the FULL database recovery model when rebuilding a large index (be sure to take a backup before you change the database’s recovery model).

Overall this is a detailed, well argued chapter, with plenty of useful scripts.

Chapter 11 Points to Consider While Writing Queries

There are well known best practices that should be used to improve your SQL queries, many are included here with supporting evidence of usage.

The practices covered include: 

• Limit the number of rows/cols

• Use sargable conditions

• Don’t use functions on predicate columns

• Use Declarative Referential Integrity (DRI)

• Trust your Foreign Key

Most of the best practices are well known, and the supporting scripts are useful to drive home the advantages. I particularly liked the script that identifies where NOCHECK is being used.

Chapter 12 Statistics in SQL Server

Statistics contain details of the distribution and density of column values, they’re typically used by the optimizer to determine if an index should be used, and how the index should be used (i.e. seek, scan, or lookup). It is important to ensure the statistics are up to date, since stale statistics can result in a query running very slowly. Statistics are critical to the performance of SQL Server queries.

The chapter discusses how SQL Server can create and update statistics automatically, and how creating indexes also creates statistics. Scripts are given to create and update statistics, together with updating stale statistics.

This is another very interesting and useful chapter, providing lots of evidence-backed detail.

Chapter 13 Table and Index Partitioning

Large tables/indexes can become unmanageable. It is possible to divide the table/index horizontally by using partitioning. This often results in easier maintenance and improved performance, since the query often only uses the partition of the table/index it needs.

Scripts are provided to partition tables with LEFT or RIGHT ranges. Partitions are examined in terms of deleting, bulk loading, splitting, merging, and switching.

<ASIN:1849685746>