That Old Cleanup

Once you're consolidating information into your database tracking worksheet, one thing you'll quickly discover is that your shop has muckloads of old data lying about. Some are completely obsolete databases, some of them are cranky old former incarnations of databases that are now active elsewhere, duplicate test or QA versions, or ghosts of playthings that former employees forgot to delete. If you examine the directories where databases are housed you will even find remnant mdf and ldf files that somebody detached (but never deleted). Inside of existing active reporting databases you will find large obsolete tables, former versions of tables, and old historical data in the actively referenced tables.

Cleanup and archiving is a royal mess, but it's the first step in organizing your storage and then reallocating filegroups appropriately. Here are some general principles and guidelines to help resolve this headache.

The first principle is to err on the side of caution. Before you permanently remove any database take it offline for a couple of weeks to verify that no unanticipated processes are affected. If you have low-cost compressible storage available (say on an old file server) then zip the zombie databases there before you delete them.

If you encounter a database with lots of old date-versioned tables, the best trick I've found is to download a tool along the lines of TOAD. With their database explorer you can easily highlight rows, ctrl-c for copy, and then paste the resulting table names into an Excel spreadsheet. Sort out the tables you've targeted for deletion and run it past the staff to make sure they are indeed obsolete. Then you can prepend "drop table" in front of each row in Excel and copy and paste the rows back into a SQL editing window. Presto. (Keep a current backup just in case).

Archiving historical data from live tables is the most work. Depending upon the version of SQL you are running (Standard or Enterprise) you may tackle this with archiving or partioning, but in any case the last few steps typically require weekend or off-hours work to down the database. In the Standard environment my general approach is first to archive, then to shrink.

You can run the first step, archiving, while everyone is blissfully plugging away at work. You may choose to keep the archive in a new table in a separate database, or you may export it to a text file for zipping to your low cost compressible storage. To avoid having too severe an impact on production I like performing the archival copy (or export) with SSIS, running on a view that selects perhaps a half million rows at a time.

Once you've got your archive you're ready for the more consumptive task (on the weekend with production stopped). Identify whereabouts on the clustered index you will be "cutting" the table. Select top(0) from the old table into a new working table. Make sure the new target table has the correct cluster index defined and any auto-increment Identity column with a seed. Now set identity insert on for the new working target and use "Select Into" with a tablock hint to load the data you are saving into the new working table. Rescript the original table's non-cluster indices for a later step, remove any parent-child table constraints, and then you can drop the original table. Is your heart beating in your ears yet? Now rename the new work table back to the original table name and rebuild the non-cluster indices.  If you'd like you can recreate the parent-child referential integrity. There, that wasn't so bad now, was it?

Of course if you're on Enterprise edition you can use partitioned tables.  So much easier.

Security Checkup

Sure you maintain the security on your instances, setting up the logins and the database mappings. You probably also assign folks to a couple of database roles you maintain to restrict access to certain critical tables. Still though, employees come and go, and even though your company likely follows a termination procedure that loops you in for disabling logins, security tasks still fall through the cracks.

Help is on the way. Run this:

EXEC xp_logininfo

Do you see any ex-employees listed with admin rights?  Oops. Next check the members of the groups that are listed:

EXEC xp_logininfo @acctname =  '[group_name]', @option = 'members'

Maybe you still have a little more cleanup work to perform, eh?

Here's another way to see the active sysadmins:

SELECT l.loginname
FROM SYS.syslogins l
join sys.server_principals ss on l.[name] = ss.[name]
where ss.is_disabled = 0
and l.sysadmin = 1 

Finally, within a single database, this query is useful for reviewing folks' rights:

;WITH RT AS (SELECT   name AS RoleType, principal_id
          FROM      sys.database_principals
          WHERE     (type_desc = 'DATABASE_ROLE'))
SELECT      P.name, RT.RoleType
FROM        RT INNER JOIN
          sys.database_role_members AS RM ON RT.principal_id = RM.role_principal_id INNER JOIN
          sys.database_principals AS P ON P.principal_id = RM.member_principal_id
ORDER BY RT.RoleType, P.name

Improving Bad Queries

The canned reports available from SSMS are great for starting to identify the worst of the Bad Queries. If you right click on an instance, then go to reports, standard reports, you can then see the top queries by total IO and by CPU (these are two separate reports). Be aware however these are based upon the underlying queries currently in the plan cache (they aren't the worst queries forever). One thing that's a bit frustrating is you can't copy and paste directly from these reports. A little hidden trick though is you can right-click on the report, and then choose Export to Excel. Now you've got some queries to optimize.

Another way to obtain this same information is to examine RedGate monitor; if you click to see the detail of an alert and then scroll down a bit, you can click on the Top-10 queries. This shows essentially the same thing as the SSMS report, except it's a snapshot from the time when the alert occurred. A great practice to follow is to view the RedGate graphs of disk reads over the last 24 hours, and then look for instances of high activity. Find an alert triggered around the same time and look at its detail for Top-10 queries: now you have more to fix.

Once you've identified a handful of troublemakers it's time to put each one under the microscope. One way of course is to examine the query plan, but first I like to paste the SQL for the query into a new-query window, and then highlight the whole thing, right click, and view in designer. Look at joins by moving the table boxes around. Is there a way to arrange them such that none of the lines representing the joins cross?

Once you've done that, look and see if there's a path around the joins taking you in a circle (SQL doesn't like to resolve such things). See if any of the joins show an f(x) symbol, another troublesome indicator. Now look at the tables out at the far reaches of the diagram, linked by a single join to the mass of tables. Do these extremities have any columns or filters selected? If not then you don't need the table in the query (but double check the SQL first before you delete it -- sometimes the table is in a Case clause). If no columns are selected and the filter is a foreign key, can you instead just specify its column value in the where clause on the table it joins?

Now examine the label grid toward the bottom of the designer. Are the filter criteria aligned properly in adjacent columns? Sometimes a developer will confuse the precedence of AND, OR, and parentheses (returning way more rows than they intended) and this will become apparent when looking at how the filters are set up. After all that it's time to now look at the query plan (fodder for a whole 'nother post).

Advanced Query Inspection

Pretty much every DBA who gets a breather from their daily maintenance would like to spend some time tracking down those particularly gnarly SQL queries that are hosing their system. This post will give you a strategy for both how to identify them and then how to clean them up. First the Giant Disclaimer: it is not the DBA's job to write application level SQL. Even if you have the knowledge and skills to do so, once you cross the line from support to development you will find it difficult to allocate sufficient time to your actual DBA duties. Stick with being the operational DBA, and when you find queries that are poorly optimized (or just plain wrong) throw them over the wall for a developer to fix. As part of that process you may certainly suggest what needs to be changed and how to do so (in fact you rather have an obligation to provide this guidance) but don't check the stuff out of source control and make the changes yourself.

Once I'm done using the canned package bad-query tools mentioned in this previous post, I am ready to dig in deeper for the bad boys by running this DMV inquiry:

SELECT   TOP (20) execution_count AS [Number of Executions],      total_worker_time / execution_count AS [Average CPU Time],
     total_elapsed_time / execution_count AS [Average Elapsed Time],
         (SELECT   SUBSTRING(text, sys.dm_exec_query_stats.statement_start_offset / 2,
            (CASE WHEN statement_end_offset = - 1 THEN LEN(CONVERT(nvarchar(MAX),
            [text])) * 2 ELSE statement_end_offset END
              - sys.dm_exec_query_stats.statement_start_offset) / 2) AS Expr1
      FROM  sys.dm_exec_sql_text(sys.dm_exec_query_stats.sql_handle) AS dm_exec_sql_text_1) AS query_text,
      total_rows, total_logical_writes, total_logical_reads, total_physical_reads, qp.query_plan
FROM  sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_query_plan(sys.dm_exec_query_stats.plan_handle) qp
WHERE   (execution_count > 20) AND (last_execution_time > GETDATE() - 14)
ORDER BY [Average CPU Time] DESC

Note that this query has a couple of parameters you can play with to achieve different results. As presented above it shows the top 20 queries executed within at least the last two weeks with more than 20 executions since the last SQL server restart. Sometimes it will become evident (from some of the other tools mentioned in a earlier post) that you are really looking for a query that has run more than a hundred thousand times, so change the selection appropriately. Another one of my favorite tricks is after the development team rolls out a large upgrade, wait a couple days and then reduce the date selection to show the last 3 days, change the execution count to "< 100" and catch the newer instances of bad code. Anyway you get the idea. This is a powerful tool for keeping a close eye on what needs to be most seriously improved.

Recovery Full or Simple

Once you've got your databases identified and organized into a spreadsheet and accomplished the preliminary cleanup, the next step to getting your act together is to determine the recovery model on each database. Well, maybe some basic instance tuning, but we can leave that for another blog post.

You only get two choices for the recovery model, full or simple, but I still can't believe how much difficulty folks have when choosing between the two. One thing you'll notice right off the bat is software developers are your worst resource for intelligently making this choice. You need to ping them for critical information to measure certain volatility features, but after that the choice really should be up to you. You're the one that will be managing disk space, backups, and restores.

There are only two key questions you need to ask yourself to make a final determination on what recovery model to choose for each database: 1) would it kill me if I was asked to restore this database to a precisely specific point in time and could only get it to the closest day? 2) Am I willing to manage log file backups and shrinks on this puppy?

If your answer to both questions is yes, then you need the full recovery model. Otherwise, use the simple model. But getting to the answer of these two questions is really the bulk of what I'd like to chat about.

Despite all the confusing things you will read about the recovery model, the gist of the difference between full and simple is the ability to perform "point in time" recovery. On the simple model you can restore whatever backup you want to your database, then any differential you ran, and you'll get the state of the database naturally from when you made that last differential backup. On the full model, you can additionally restore all or portions of the log file backup(s) to reflect the transactions that happened after your last differential.

Therein lies the rub: on simple recovery your log files don't grow much, but on full they can get quite large. On databases set to full recovery you will need to periodically shrink the log files, and of course you need to add them to your maintenance plan to back them up as well.

This is where that spreadsheet of databases is helpful. If a production database exhibits a highly combined level of Write Coupling and Software Volatility then you'll likely need to bite the bullet and set it on full recovery model. Very seldom will I set a development, QA, or staging database to this model though. A better way to manage high software volatility on a development database is to make sure your developers are using good quality source control. If a production database is written to at random times by all sorts of batch and OLTP systems or your developers are constantly rolling out production upgrades, then you'd best stick with full recovery.