Cleveland DBA

SQL Server administration in Cleveland, OH

Error: “The file … cannot be moved by this RESTORE operation.”

This morning, I was trying to restore a database from a SQL 2005 backup to a new server.  The restore involved a full backup and a differential.  I restored the full backup with NORECOVERY via SSMS, but when I tried to restore the differential, also via SSMS, I got this error:

Okaaay.  I was a bit befuddled until I scripted out the two restore commands that SSMS is actually generating.  The full restore is fine:

RESTORE DATABASE [TestDiff]
	FROM  DISK = N'F:\myserver_backup\TestDiff.bak'
	WITH  FILE = 1,
		MOVE N'TestDiff' TO N'F:\myserver_data\TestDiff.mdf',
		MOVE N'TestDiff2' TO N'F:\myserver_data\TestDiff_1.ndf',
		MOVE N'TestDiff_log' TO N'F:\myserver_logs\TestDiff_2.ldf',
		NORECOVERY,  NOUNLOAD,  STATS = 10
GO

But look at the differential restore:

RESTORE DATABASE [TestDiff]
	FROM  DISK = N'F:\myserver_backup\TestDiff.diff'
	WITH  FILE = 1,
		MOVE N'TestDiff' TO N'F:\myserver_data\TestDiff.mdf',
		MOVE N'TestDiff2' TO N'F:\myserver_data\TestDiff.ndf',
		MOVE N'TestDiff_log' TO N'F:\myserver_logs\TestDiff.ldf',
		NOUNLOAD,  STATS = 10
GO

Notice anything wrong? The physical filenames aren’t the same as the ones used in the first restore. So SQL Server rightfully assumes you’re trying to move datafiles whle performing a differential restore and spits out an error. If you change the physical filenames in the differential restore to match the first restore, or remove the MOVE clauses altogether, it works:

RESTORE DATABASE [TestDiff]
	FROM  DISK = N'F:\myserver_backup\TestDiff.diff'
	WITH  FILE = 1,
		MOVE N'TestDiff' TO N'F:\myserver_data\TestDiff.mdf',
		MOVE N'TestDiff2' TO N'F:\myserver_data\TestDiff_1.ndf',
		MOVE N'TestDiff_log' TO N'F:\myserver_logs\TestDiff_2.ldf',
		NOUNLOAD,  STATS = 10
GO

-- or

RESTORE DATABASE [TestDiff]
	FROM  DISK = N'F:\myserver_backup\TestDiff.diff'
	WITH  FILE = 1,
		--MOVE N'TestDiff' TO N'F:\myserver_data\TestDiff.mdf',
		--MOVE N'TestDiff2' TO N'F:\myserver_data\TestDiff.ndf',
		--MOVE N'TestDiff_log' TO N'F:\myserver_logs\TestDiff.ldf',
		NOUNLOAD,  STATS = 10
GO

This appears to be a bug in SSMS but I’ve only gotten it to happen under specific circumstances. The backups needed to be from a SQL 2005 instance, but you could be restoring to SQL 2005 or SQL 2008.  And it looks like you need to be restoring a database with multiple datafiles.  These were the 2 conditions I personally experienced the problem with, though I didn’t do exhaustive testing.  If anyone finds any other scenarios where this happens, feel free to leave a comment.  Hopefully this saves others some time.

Advertisements

June 21, 2011 Posted by | Troubleshooting | , , | Leave a comment

What I’m working on

This has been a somewhat scattered week.  As I alluded to earlier, my mind has been pulled in all different directions after a week of training and listening to presentations.  I really have to work on writing my ideas down somewhere, as I have them, and getting them out of my head until I’m ready to deal with them.  At the moment, I’m into a few projects.

Automating development database creation

As a takeaway to the “4 hour DBA” session this weekend, I’ve decided to create a way to allow developers to create their own databases/logins in our Dev environment.  Rather than grant them all db_creator and security_admin and, well, create the Wild Wild West, I’m working with one of our developers to create a web form front end that will pass required parameters to a stored procedure.  The procedure will check for the existence of the database, and the existence of the requested login.  If the login exists, the developer will need to provide the correct password.  An application name will also be required, and will be logged along with the database name, db_owner, creation date, and the developer’s login.  One of the biggest problems we have is databases in Dev that are “forgotten”.  A developer requests it and somewhere down the line everyone forgets about it and what it was for to begin with and I’m left asking everyone “do you know anything about database X?”  Not fun.  And why me, you might ask?  Because I’m the one who developed and maintains our SQL Server inventory database.  Which leads me to…

Upgrading our SQL Server inventory to 2008

A long time ago I wrote a few SSIS packages that go to each SQL Server instance and collect different information and store it in a central repository.  This is in SSIS 2005 currently.  It also resides in our Dev environment.  Leaving it in Dev was fine for a long time, the DBAs were the only ones who used the information.  But recently I’ve opened it up to other users who were looking for that information, and I’ve published some MS Reporting Services reports for our Windows Admin group to reference when they’re scheduling outages, etc.  So, it needs to go to Production.  And if I’m doing that, I might as well migrate it to SSIS 2008.  And if I’m doing that, I might as well take another look and see if there’s a better way to do this.

Preparing a presentation

Another thing on my plate is preparing my very first presentation.  At work we have monthly developer forum presentations on a variety of subjects, and I volunteered to present on SQL Server features that developers could be using but probably aren’t.  (Note to self: Need to come up with a snappier title.)  I’m planning on covering Service Broker, database snapshots, and data compression.  All topics that I’ve covered here and I feel comfortable enough with to present to my coworkers.  It’s not going to happen for a couple of months, but I really need to start organizing my thoughts and making notes.

There are other things I’m doing, of course, routine tasks that are simply part of the job. But these are the bigger ones I’m focusing on now.  The ones that I’ll be working on when my instant messenger status changes to “Do Not Disturb.”  🙂

June 16, 2011 Posted by | General, Professional Development | | Leave a comment

The problem with SQL Saturday…

The problem with user conferences is that they usually leave me with a slight case of ADD the following Monday.  So much stuff, I have to try it all NOW!!!  SQL Saturday #75 this past weekend in Columbus was no exception.  There were some really good presentations and for a couple time slots it was tough choosing which one to attend.  Here’s a very brief recap of the sessions I went to.

Baseline Basics – Erin Stellato (blog|twitter):  This was a really informative presentation, a lot of information.  I’ve done baselining in the past using Perfmon and, more recently, Powershell.  But the best part of this presentation was the second half where Erin discussed analytical methods.  I’m looking forward to checking out PAL (Performance Analysis of Logs).  In fact, I’m running a Perfmon baseline right now just so I can check it out.  There’s also ClearTrace  for analyzing Profiler traces.

SANs and SQL Server – Kevin Boles (twitter):  I really need to get more involved in the hardware configuration of our database servers.  Ours is an environment where the “SAN guys” only want to know how much disk you need and where you want it attached.  The DBAs don’t have much more input beyond that.  That needs to change.

Build your own SQL Server Cloud – Sarah Barela (blog|twitter):  Externally hosted clouds didn’t allow Sarah the kind of control she wanted over the environment, so she decided to build her own.  In this session she provided a somewhat high-level overview of what the requirements were for her cloud and how she accomplished them using Hyper-V, mirroring, resource governor etc.

SQL Server Partitioning – Kevin Boles:  A good overview of partitioning, some best practices, and some shortcomings.  To date we have not used partitioning here, but this is good information for our datawarehouse team.

The 4-hour DBA – Sarah Barela:  This one was about automating the tasks you perform regularly and also partly about letting go of some of the control over your environment.  I know, that last part can make a DBA squirm.  I was hoping Sarah would provide more concrete examples and/or scripts, so I was a little disappointed, but it did get me thinking about the tasks I personally hate doing and how I can automate some of them.

WIT Lunch – Sarah Barela, Jen Myers (blog|twitter), Erin Stellato and Jes Borland (blog|twitter):  This was a very interesting discussion on how to encourage more young people (not just girls/women) to consider a career in technology.  Good stuff.

But I think the best part of this SQL Saturday was meeting and speaking with such wonderful people.  There was a discussion after the event about how supportive the SQL Server user community is and how unique they are (or seem to be) in that respect.  Where else do you have people giving up their personal time, be it evenings at user group meetings, or whole Saturdays (whole weekends for some out-of-towners) to help their colleagues improve their skills?  It’s fantastic!

June 13, 2011 Posted by | Professional Development | , | Leave a comment

Stuff I learned today – XML and CLR

Well, to say I “learned” about XML and CLR is a bit disingenuous.  More accurately, the instructor went over XML and CLR.  But to be honest, I know so little about either subject that it was a bit like listening to Charlie Brown’s teacher.  But anyway, we covered it and it gave me some ideas as to how they can be used, so I’ll have more to offer the next time a developer comes to me asking “How can I…”  And that was kind of the point of picking this class in the first place.

In other news I’m heading down to Columbus this weekend for SQL Saturday #75.  I’m looking forward to some good sessions to cap off this Week ‘o SQL Learnin’.

Have a great weekend folks!

June 10, 2011 Posted by | Features, General, Professional Development | , , | Leave a comment

Stuff I learned today – Table Types

Since SQL Server 2000, you’ve been able to create variables of type TABLE, but you needed to define that table structure when (and each time) you declared the variable. You also couldn’t pass these table variables into a stored procedure.

SQL Server 2008 introduced the user-defined TABLE datatype. Now you can create and store custom-made TABLE types for use with variables and as parameters into your stored procedures. (They cannot, however, be used as the data type for a column within a table.)

TABLE types are created and dropped using the CREATE TYPE and DROP TYPE commands. There’s no ALTER TYPE command, so if you need to change a type, drop it and recreate it.

CREATE TYPE dbo.BookDetails
AS TABLE (
PatronID int NOT NULL,
BookID	int NOT NULL,
Title	varchar(250),
Author 	varchar(100));
GO

So, why would you want to pass a table into a stored procedure? Imagine you work for a library and you’re writing an app to record books that people borrow, and you use a stored procedure to insert the book details into the BooksBorrowed table. Previously, if a patron borrowed multiple books, you’d have to call that procedure once for each record, right? Maybe like this:

CREATE PROCEDURE CheckOutBooks
@CustomerID int, @BookID int, @Title nvarchar, @Author nvarchar
AS
INSERT INTO BooksBorrowed (PatronID, BookID, Title, Author, DateBorrowed)
VALUES (@PatronID, @BookID, @Title, @Author, GETDATE());
GO

-- Now we have to run it multiple times to check out all the books Patron 1 wants

EXEC CheckOutBooks (1, 45, 'The Grapes Of Wrath', 'John Steinbeck');
EXEC CheckOutBooks (1, 532, 'Gone With The Wind', 'Margaret Mitchell');

Not very efficient, right? Now let’s try it with the BookDetails TABLE type we created earlier.

CREATE PROCEDURE CheckOutBooks
@Books BookDetails READONLY
AS
INSERT INTO BooksBorrowed (PatronID, BookID, Title, Author, DateBorrowed)
SELECT PatronID, BookID, Title, Author, GETDATE() from @Books
GO

-- Now we only have to run the procedure once

DECLARE @vBooks BookDetails;

INSERT INTO @vBooks VALUES (1, 45, 'The Grapes Of Wrath', 'John Steinbeck'),
			   (1, 532, 'Gone With The Wind', 'Margaret Mitchell');

EXEC CheckOutBooks @vBooks;

This reduces round-trip overhead and speeds up the entire transaction. Having a single, pre-defined definition of the TABLE type also means consistency across applications. Consistency=good. Fast transactions=gooder.

So, look it up. Play with it. Have fun, people.

June 8, 2011 Posted by | Features, T-SQL | , | Leave a comment

Stuff I learned … yesterday – statistics

Yesterday was a pretty full day between training and the ONSSUG June meeting immediately afterwards, so I didn’t get a chance to blog.  Anyway, I did learn something yesterday, but it didn’t come from the class I’m taking.  Erin Stellato (blog | twitter) gave a great presentation at ONSSUG about statistics.  I really wish she’d had more time because it was simply crammed with good information. 

Anyway, two key things I learned:

  1. Multicolumn statistics – Just what they sound like, statistics over multiple columns.  These are created by default when you create a composite index, but you can also create them yourself without an index using the CREATE STATISTICS command.  Helpful when you have queries that filter on multiple columns where no index exists.
  2. Filtered statistics – You can also put a filter on your statistics.  This is useful when you have a very large table but normally only query on a small subset of rows.  If these rows are highly selective, creating filtered statistics will help the optimizer choose the best path.

For more information on these 2 features or on statistics in general, check out Books Online.

June 8, 2011 Posted by | Features, SQL Tuning | , | Leave a comment

Stuff I learned today – hierarchyid

I’m in training this week. Since I was restricted to a local class, I decided to take one focused on development, rather than dba-related topics, and get some exposure to facets of SQL Server I don’t normally deal with on a day-to-day basis. And I figured I’d share some of my newly garnered knowledge with you.

Today’s nugget of new knowledge: the hierarchyID data type. This is a new data type in SQL 2008 that addresses the challenges of storing parent/child hierarchical relationships inside a table. Previously, developers had to use CTEs and self-joins to handle this kind of scenario. A classic example is the Employee table, where Employees have Managers who are also Employees, seen below.

USE AdventureWorks;
GO

SELECT EmployeeID, LastName, FirstName, ManagerID
INTO EmployeeDemo
FROM HumanResources.Employee
JOIN Person.Contact ON Employee.ContactID = Contact.ContactID;
GO

SELECT
     Mgr.EmployeeID AS ManagerID, Mgr.FirstName + ' ' + Mgr.LastName AS ManagerName,
     Emp.EmployeeID AS EmployeeID, Emp.FirstName + ' ' + Emp.LastName AS EmployeeName
FROM EmployeeDemo AS Emp
LEFT JOIN EmployeeDemo AS Mgr
ON Emp.ManagerID = Mgr.EmployeeID
ORDER BY ManagerID, EmployeeID;

Another way to look at it is via a self-joining CTE. This will allow you to get the hierarchical level of the employee.

 WITH Organization(EmployeeId, LastName, Manager, HierarchyOrder)
 AS
 (
    SELECT emp.EmployeeId, emp.LastName, emp.LastName, 1 AS HierarchyOrder
    FROM EmployeeDemo AS emp
      WHERE emp.ManagerId is Null
    UNION ALL
    SELECT emp.EmployeeId, emp.LastName, Parent.LastName, HierarchyOrder + 1
    FROM EmployeeDemo AS emp
           INNER JOIN Organization AS Parent
                 ON emp.ManagerId = parent.EmployeeId
 )
 SELECT *
 From Organization
GO

That’s great and all, and pretty slick looking, but you could start to see some performance hit with a self-referencing view like that.
So let’s say I want to take my existing EmployeeDemo table and use it to create a hierarchical table, how would I do that? I’d start by creating a new table that contains a hierarchyid column.

CREATE TABLE OrgChart
(
  OrgNode hierarchyid,
  EmployeeID int,
  LastName varchar(50),
  FirstName varchar(50),
  ManagerID int
);
GO

I can then use a slightly modified version of my previous CTE to populate it.

WITH Organization(path, EmployeeID)
AS (
-- This section provides the value for the root of the hierarchy
SELECT hierarchyid::GetRoot() AS OrgNode, EmployeeID
FROM EmployeeDemo AS C
WHERE ManagerID IS NULL
UNION ALL
-- This section provides values for all nodes except the root
SELECT
CAST(p.path.ToString() + CAST(ROW_NUMBER() OVER (PARTITION BY ManagerID ORDER BY ManagerID) AS varchar(30)) + '/' AS hierarchyid),
C.EmployeeID
FROM EmployeeDemo AS C
JOIN Organization AS p
   ON C.ManagerID = P.EmployeeID
)
INSERT INTO OrgChart
SELECT path, o.EmployeeID, LastName, FirstName, ManagerID
FROM Organization o JOIN EmployeeDemo e ON o.EmployeeID = e.EmployeeID

SELECT * FROM OrgChart;
GO

So now what do I do with this new column? Well, having this new column makes the CTE unnecessary. You can traverse the hierarchy branches using built-in functions. Suppose I want to see who works under Peter Krebs, employee 21.

DECLARE @Manager hierarchyid

SELECT @Manager = OrgNode
FROM OrgChart
WHERE EmployeeID = 21 ;

SELECT *
FROM OrgChart
WHERE OrgNode.IsDescendantOf(@Manager) = 1
order by OrgNode;

With that query we can see that Cynthia Randall reports to Peter, and Andy Ruth reports to Cynthia, etc.

I’m definitely going to have to play around with this new datatype and all of the associated functions to get my brain wrapped around it better, but it looks pretty cool.

Some resources for further reading on the hierarchyid data type:
Populating a Table with Existing Hierarchical Data

Creating and Managing Data in a Hierarchical Table

Using hierarchyid Data Types (Database Engine)

June 6, 2011 Posted by | Features, T-SQL | , , | Leave a comment

Maintenance 3.0

I just couldn’t let it go.  Right after my last post I decided I just wasn’t satisfied with that maintenance script.  First of all, it only worked on one database at a time.  And secondly, there were just too many selects within the cursor loop.  And, personally, I’d rather work with a temp table than a physical table.

So I made a few adjustments, and here’s the new and improved (!)… Maintenance 3.0.

SET NOCOUNT ON;
DECLARE @dbid int;
DECLARE @dbname varchar(250);
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @fillfactor int;
DECLARE @command varchar(8000);

CREATE TABLE [dbo].[#work_to_do](
[dbid] [int] NULL,
[dbname] [varchar](250),
[schemaname] [varchar](250),
[objectid] [int] NULL,
[objectname] [varchar](250),
[indexid] [int] NULL,
[indexname] [varchar](250),
[partitionnum] [int] NULL,
[frag] [float] NULL,
[fillfactor] [int]
)

exec sp_msforeachdb 'USE [?];
INSERT INTO #work_to_do
SELECT
db_id() as dbid,
''?'',
sch.name,
s.object_id AS objectid,
o.name,
s.index_id AS indexid,
i.name,
s.partition_number AS partitionnum,
s.avg_fragmentation_in_percent AS frag,
i.fill_factor
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'') s
join sys.objects o (NOLOCK) on o.object_id = s.object_id
join sys.indexes i (NOLOCK) on i.object_id = s.object_id and i.index_id = s.index_id
JOIN sys.schemas sch (NOLOCK) ON sch.schema_id = o.schema_id
WHERE avg_fragmentation_in_percent > 5.0 AND s.index_id > 0
and fragment_count > 1000;'

PRINT '************FINISHED LOADING TABLE, REBUILDING INDEXES**************';

DECLARE partitions CURSOR FOR
SELECT * FROM #work_to_do where [dbname] not in ('master', 'tempdb', 'model', 'msdb', 'distribution');

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
FETCH NEXT
FROM partitions
INTO @dbid, @dbname, @schemaname, @objectid, @objectname, @indexid, @indexname, @partitionnum, @frag,
@fillfactor;

WHILE @@FETCH_STATUS = 0
BEGIN;

SELECT @partitioncount = count (*)
FROM sys.partitions (NOLOCK)
WHERE object_id = @objectid AND index_id = @indexid;

IF @fillfactor = 0
SET @fillfactor = 80
ELSE
SET @fillfactor = @fillfactor - 5

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
IF @frag < 30.0
BEGIN;

SELECT @command = 'USE ['+@dbname+']; ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REORGANIZE';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
PRINT 'Executed ' + @command;

SELECT @command = 'USE ['+@dbname+']; UPDATE STATISTICS ' + @schemaname + '.' + @objectname + ' (' +@indexname + ') WITH SAMPLE 30 PERCENT'
EXEC (@command);
PRINT 'Executed ' + @command;

END;

IF @frag >= 30.0
BEGIN;

SELECT @command = 'USE ['+@dbname+']; ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + @objectname + ' REBUILD WITH (PAD_INDEX = ON,  FILLFACTOR = '+ cast(@fillfactor as varchar) +') ';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
PRINT 'Executed ' + @command;

END;

FETCH NEXT
FROM partitions
INTO @dbid, @dbname, @schemaname, @objectid, @objectname, @indexid, @indexname, @partitionnum, @frag,
@fillfactor;

END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

TRUNCATE TABLE #work_to_do

PRINT '************FINISHED REINDEXING, UPDATING STATS ON REMAINING TABLES**************';

exec sp_MSforeachdb 'USE [?];
INSERT INTO #work_to_do
select distinct db_id(), db_name(), sch.name, t.object_id, t.name, s.index_id, i.name, NULL, NULL, NULL
from
sys.tables t
join sys.dm_db_index_usage_stats s
on s.object_id = t.object_id
and s.database_id = DB_ID()
and user_updates > 0
and STATS_DATE(s.object_id, s.index_id) < s.last_user_update
join sys.indexes i
on t.object_id = i.object_id
and i.index_id = s.index_id
and s.index_id > 0
JOIN sys.schemas as sch ON sch.schema_id = t.schema_id;'
;

DECLARE stats CURSOR FOR
SELECT * FROM #work_to_do where [dbname] not in ('master', 'tempdb', 'model', 'msdb', 'distribution')

-- Open the cursor.
OPEN stats;

-- Loop through the partitions.
FETCH NEXT
FROM stats
INTO @dbid, @dbname, @schemaname, @objectid, @objectname, @indexid, @indexname, @partitionnum, @frag,
@fillfactor;

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @command = 'USE ['+@dbname+']; UPDATE STATISTICS ' + @schemaname + '.' + @objectname + ' (' +@indexname + ') WITH SAMPLE 30 PERCENT'
EXEC (@command);
PRINT 'Executed ' + @command;
FETCH NEXT
FROM stats
INTO @dbid, @dbname, @schemaname, @objectid, @objectname, @indexid, @indexname, @partitionnum, @frag,
@fillfactor;

END;
CLOSE stats;
DEALLOCATE stats;

drop table #work_to_do

June 6, 2011 Posted by | Maintenance | | Leave a comment

The ever-shrinking maintenance window

As I’ve stated before, databases aren’t getting any smaller. Business and legal requirements are mandating that we gather and retain more and more data that, in the past, we might have purged from time to time. It’s a simple fact that database indexes must be maintained, statistics updated, and databases backed up; and the bigger our databases get, the more important basic maintenance becomes. But in these days of 24/7 availability, what is getting smaller is our maintenance window. Some of us are lucky to get a few hours every week or month for maintenance. Others don’t even get that.

I have a OLTP database I support that’s around 300GB. Certainly not huge compared to your average data warehouse, but big enough that index maintenance was becoming cumbersome. When we first migrated this database to SQL Server 2000 several years ago, it was less than 100GB and your out-of-the-box Maintenance Plan tasks were fine for index and statistics maintenance. Maintenance 1.0, if you will.

As the database grew and our maintenance window shrank, however, this solution started taking too much time, so I switched to a more fine-tuned, TSQL approach that you’re probably familiar with already. Basically, I would focus on indexes with a fragmentation higher than 5%, of those, I would rebuild indexes more than 30% fragmented and reindex those less than 30% fragmented. This was Maintenance 2.0.

Well, that was certainly better, but I seemed to be rebuilding the same indexes every time. Obviously the fill-factor on those indexes was set too high, causing page splits (and thus fragmentation) to occur during inserts/updates. So I added logic to drop the existing fillfactor by 5% when the index was rebuilt. I monitor the fillfactor on my indexes periodically to make sure nothing is getting too low. Maintenance 2.1.

The reindexing portion of Maintenance 2.0 and 2.1 takes almost 3 hours to run. I’d like to see that cut down, but I don’t know how I’d be able to on our current system. However, I still wanted to address updating statistics during the maintenance window, and running update stats for the entire database with a 30% sampling was taking over 5 hours. Add to this the fact that I was looking at switching from nightly full backups to a weekly full backup with nightly differentials. I wanted to be able to run the index maintenance, update statistics, and take the full backup all within our weekly maintenance window.

In prior Maintenance versions, I was updating statistics on all of the tables in the database. That was something I could do on a monthly basis, but on a weekly basis it felt like overkill. There were some tables, and some large tables at that, that simply aren’t used. And more importantly, that aren’t updated. That was the key: why update statistics on a table that hasn’t changed since the last time you updated stats? So, in my most recent version, I’ve added logic to update statistics on those indexes that have been updated since the last STATS_DATE. Maintenance 2.2. Or maybe it should be 3.0? Whatever. Here it is. And it ran in just over 3 hours.

USE myDatabase;
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @fillfactor int;
DECLARE @command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
    DROP TABLE work_to_do;
---- conditionally select from the function, converting object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 5.0 AND index_id > 0
and fragment_count > 1000;
-- Declare the cursor for the list of partitions to be processed.

PRINT '************FINISHED LOADING TABLE, REBUILDING INDEXES**************';
DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
FETCH NEXT
   FROM partitions
   INTO @objectid, @indexid, @partitionnum, @frag;

WHILE @@FETCH_STATUS = 0
    BEGIN;
        SELECT @objectname = o.name, @schemaname = s.name
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;

        SELECT @indexname = name, @fillfactor = fill_factor
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;

        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

	IF @fillfactor = 0
		SET @fillfactor = 80
	ELSE
		SET @fillfactor = @fillfactor - 5

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
IF @frag < 30.0
    BEGIN;
    SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REORGANIZE';
    IF @partitioncount > 1
        SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
    EXEC (@command);
        SELECT @command = 'UPDATE STATISTICS ' + @schemaname + '.' + @objectname + ' (' +@indexname + ') WITH SAMPLE 30 PERCENT'
    EXEC (@command);
    END;

IF @frag >= 30.0
    BEGIN;
    SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + @objectname + ' REBUILD WITH (PAD_INDEX = ON,  FILLFACTOR = '+ cast(@fillfactor as varchar) +') ';
    IF @partitioncount > 1
        SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
    EXEC (@command);
    END;
PRINT 'Executed ' + @command;

FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

PRINT '************FINISHED REINDEXING, UPDATING STATS ON REMAINING TABLES**************';

DECLARE stats cursor static for
select distinct t.object_id, t.name, s.index_id, i.name
from
	sys.tables t
	join sys.dm_db_index_usage_stats s
		on s.object_id = t.object_id
		and s.database_id = DB_ID()
		and user_updates > 0
		and STATS_DATE(s.object_id, s.index_id) < s.last_user_update
	join sys.indexes i
		on t.object_id = i.object_id
		and i.index_id = s.index_id
		and s.index_id > 0;
;

OPEN stats
FETCH NEXT FROM stats INTO @objectid, @objectname, @indexid, @indexname
WHILE @@FETCH_STATUS = 0
BEGIN

		SELECT @schemaname = s.name
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;

        SELECT @command = 'UPDATE STATISTICS ' + @schemaname + '.' + @objectname + ' (' +@indexname + ') WITH SAMPLE 30 PERCENT'
		EXEC (@command);
		PRINT 'Executed ' + @command;
		FETCH NEXT FROM stats INTO @objectid, @objectname, @indexid, @indexname

END;
CLOSE stats;
DEALLOCATE stats;

-- drop the temporary table
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
    DROP TABLE work_to_do;
GO

June 2, 2011 Posted by | Maintenance | , | Leave a comment