Defragging Indexes Without Losing Index Stats - Part 2

In my last post, I introduced the concept of synchronizing index maintenance and index usage statistics logging as an important part of a DBA's performance tuning routine. In this post I will start delving into the individual scripts involved.

UPDATE: The full script (see link at end of post) has been both updated and tested (a bit)
                  I've also augmented the script below to clean up the atblIndexUsageStats table.

As a recap, below are the main steps involved:

1. Log Index usage stats
2. Determine which indexes need defragging and generate scripts
3. Generate update statistics scripts based on step 2
4. Log defrag operation to maintenance history table
5. Execute defrag and update stats scripts
6. Cleanup logging tables (optional)

Defragging Indexes Without Losing Index Stats - Part 1

Introduction

As part of my regular performance tuning and maintenance schedule I rely heavily on queries that interrogate the DMV sys.dm_db_index_usage_stats. Based on this DMV I can judge whether indexes should be removed, modified or left as they are. See my previous blog post on SQL Server DMVs. I also believe in the regular defragging of indexes in a database (although I recently read a post from Brent Ozar challenging this idea, but if your servers don’t have the RAM required for such caching as his article suggests and/or you'll never get the budget to upgrade, you’ll need to keep reading). 

Answer to the Partitioned Index Puzzle

A few weeks ago I posted a description of an error I encountered when creating a partitioned index on a SQL Server 2005 Standard Edition instance. It had me quite literally tearing my hair out. The error was a typically vague Microsoft one:

Msg 4436, Level 16, State 12, Line 1

UNION ALL view 'PartWindow.dbo.vwPartitionedView' is not updatable because a partitioning column was not found.

I spent quite a few hours staring at my SQL statements and re-reading documentation before the solution hit me.

If you haven't had a chance to read the post, please do before you skip down to the answer. It was, unfortunately, a total "duh!" moment. But at the same time quite easy to overlook.

A Quick Partitioned View Puzzle

I needed to create a few partitions on a legacy SQL Server 2005 Standard Edition instance. As partitions are not supported on standard edition, I needed to go the partitioned index route. In my research into how to  implement them, I found some really good resources:

• SQL Server Partitioning without Enterprise Edition
• MSDN: Using Partitioned Views

I'm not going to explain the basics of partitioning here, so you may want to read the above pages if you're not familiar with it before continuing. 

ISNULL vs. COALESCE

A friend of mine just asked me when COALESCE should be used. As I'm a DBA and don't do too much SQL development work, I never had a need to use COALESCE (or I never thought I had a need). Therefore, I was inspired to do a little digging. As there has been much written about the similarities and differences I will use this post as a jumping off point to a few good resources that shed light on the topic. Though, I'll highlight some of the points here as well.

Last SQL Server Instance Start Date

I was interested in finding when a new SQL Server instance I started working on was last restarted and came across this blog post.

Although the information was very good I felt the queries could be tweaked slightly and broken out into the variations needed for the different SQL Server editions.

I find that it is helpful to know when the database server was started due to the various Dynamic Management Views and Functions (DMV) that I use. Many of these collate cumulative statistics beginning at a SQL Server start. Which means that they are cleared down when the instance is restarted, so it's helpful to know the uptime of the server in order to know how much data has been collected and whether it is representative of how the server is being used: i.e. one day's worth of statistics vs. one month's.

As the blog post mentioned above outlines, there are several ways of getting to this data and some of them work only in certain versions of SQL Server. Here they are in my modified form:
NB: I am ignoring SQL Server 2000

Method 1 - Check TempDB create date
SQL Server 2005+ 

SELECT [crdate] AS [LastStartupDate],

DATEDIFF(dd,[crdate],GETDATE()) AS [Days since server start]

FROM   [dbo].[sysdatabases]

WHERE  [name] = 'tempdb'

AND [crdate] < GETDATE() - 0 --filter results to instances restarted greater than X days

Method 2 - Check the system DMV
SQL Server 2008+

SELECT [sqlserver_start_time] AS [LastStartupDate],

DATEDIFF(dd, [sqlserver_start_time],GETDATE()) AS [Days since server start]

FROM   [sys].[dm_os_sys_info]

Method 3 - Interrogate the SQL Server error log
SQL Server 2005 - 2008

DECLARE @LogNum TINYINT

SET @LogNum = 0 --0=current 1=.1 etc

DECLARE @LogType TINYINT

SET @LogType = 1 --1=SQL 2=Agent

DECLARE @ProcessType VARCHAR(64)

SET @ProcessType = 'Server'

DECLARE @TextSearch VARCHAR(20)

SET @TextSearch = 'Server process ID is'

DECLARE @ErrLog AS TABLE([LogDate] DATETIME, [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))

INSERT INTO @ErrLog

EXEC sys.xp_readerrorlog @LogNum, @LogType , @ProcessType, @TextSearch

--grab the first occurrence and report back the timestamp as the last startup

SELECT MIN(LogDate) AS [LastStartupDate],

DATEDIFF(dd, MIN(LogDate),GETDATE()) AS [Days since server start]

FROM @ErrLog

WHERE LogDate < GETDATE() - 0 --filter results to instances restarted greater than X days

SQL Server 2008 - 2012
In SQL Server 2012 the extended stored procedure accepts only two parameters 

DECLARE @LogNum TINYINT = 0 --0=current 1=.1 etc

DECLARE @LogType TINYINT = 1 --1=SQL 2=Agent

DECLARE @ProcessType VARCHAR(64) = 'Server'

DECLARE @TextSearch VARCHAR(20) = 'Server process ID is'

DECLARE @ErrLog AS TABLE([LogDate] DATETIME, [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))

INSERT INTO @ErrLog

EXEC sys.xp_readerrorlog @LogNum, @LogType --, @ProcessType, @TextSearch

--grab the first occurrence and report back the timestamp as the last startup

SELECT MIN(LogDate) AS [LastStartupDate],

DATEDIFF(dd, MIN(LogDate),GETDATE()) AS [Days since server start]

FROM @ErrLog

WHERE [ProcessInfo] = @ProcessType AND [TEXT] LIKE '%' + @TextSearch + '%'

AND LogDate < GETDATE() - 0 --filter results to instances restarted greater than X days

SQL Server 2005+ Performance Tuning - Part 2

This series of posts aims to provide an introduction to resolving big and sudden performance degradation.
In my last post I covered the reasons for poor performance and the ways of pinpointing performance bottlenecks.
In this post I will detail some of the ways of resolving those performance bottlenecks.

SQL Server 2005+ Performance Tuning - Part 1

This next series of posts aims to provide an introduction to resolving big and sudden performance degradation. I will also add a few tips for general performance related database best practice and tidying.

Remove a Cursor From a Stored Procedure - Part 2

Why is it so slow?!?!?

In my last post, I used a stored procedure from a database I manage to illustrate a real world example of converting a cursor into set based SQL. In this post I will look into a the mechanics behind the scenes and explain at least one of the reasons why it is generally unwise to use cursors when a set based query will provide the same results.

Remove a Cursor From a Stored Procedure - Part 1

A huge problem DBAs encounter with system performance is the ever-present cursor. While there are some scenarios where cursors are unavoidable (or at least difficult to avoid), many instances are down to the developer being more comfortable writing cursors - or some myths as to why a cursor is better. That said, here is a short post highlighting some cursor pros or (non-cons): When are TSQL Cursors the best or only option?

In my organisation, and no doubt many others, the problem stems from the fact that the developers writing SQL are not SQL developers. Rather they are [insert relevant coding language] developers. And in many programming languages processing is best performed on a row-by-row basis. Not so SQL, especially T-SQL.