Reorgs, Rebuilds, and Fill

In an earlier post I chatted about the joy of creating indices; now we get a bit deeper into the topic to explore how you maintain the things. Then we'll circle back to discuss how you should have built them in the first place, so as to minimize your maintenance (there's nothing like 20-20 hindsight, eh?).

Let me preface the post with a disclaimer: some DBAs feel the best way to handle index maintenance is to use the wizard to create a maintenance plan for overnight or weekend defragments. I call this the lazy man's way of index maintenance, but it does have its place under many circumstances. If your employees use your database mainly during weekday working hours and it is quiescent at night (or on the weekend) and if it's not gigantic (say 100 gigabyte or less), this automatic maintenance is a smart idea and works well enough. Otherwise though I have concerns that it is a bit too easy and hides the sins of bad index design in the first place.

For large databases that never sleep you should take care to spend some time reorganizing indices online. If you are running the enterprise edition you can even rebuild indices online. Certainly not all of them however. First, learn to target the indices that need the most love and attention; I have several ways to do this. In the database explorer window of SSMS, after clicking on Tables, include the column for index size and sort the tables descending. Double click the top table, double click the index icon, and again include the column for index size. Sort descending. Fortunately SSMS remembers all this column setup so now that you've done it, it will always be at the ready.

Every week or so page through the topmost indices, right click on properties, then click fragmentation. Anything in the 30 to 40 percent range should be defragmented. How do you do this? Well you have two options, reorganize or rebuild, but more about that briefly.
Another good way to find indices to target is by running this query (the same one as in my earlier post on indices). The top rows in the resultset are the heaviest used so they need a bit tighter maintenance planning.

SELECT sys.objects.name AS object_name, sys.indexes.name AS index_name,
Case when (is_unique = 1 and is_primary_key = 1) then 'PK UNIQUE '
when is_unique = 1  then 'UNIQUE ' else '' end + sys.indexes.type_desc type_desc,
c.index_columns AS index_columns_key, s.used_page_count * 8   AS IndexSizeKB,
sys.dm_db_index_usage_stats.user_seeks, sys.dm_db_index_usage_stats.user_scans,
sys.dm_db_index_usage_stats.user_lookups, sys.dm_db_index_usage_stats.user_updates,
sys.dm_db_index_usage_stats.last_user_seek, sys.dm_db_index_usage_stats.last_user_scan,
sys.dm_db_index_usage_stats.last_user_lookup, sys.dm_db_index_usage_stats.last_user_update
FROM sys.objects JOIN sys.indexes ON sys.indexes.object_id=sys.objects.object_id
JOIN (SELECT distinct
object_id, index_id, stuff((SELECT ','+col_name(object_id,column_id ) as 'data()'
FROM sys.index_columns t2
WHERE t1.object_id =t2.object_id and t1.index_id = t2.index_id
FOR XML PATH ('')),1,1,'') as 'index_columns'
FROM sys.index_columns t1
) c ON c.index_id = sys.indexes.index_id AND c.object_id = sys.indexes.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats
ON sys.indexes.index_id=sys.dm_db_index_usage_stats.index_id
AND sys.indexes.object_id=sys.dm_db_index_usage_stats.object_id
JOIN sys.dm_db_partition_stats  AS s
ON s.[object_id] = sys.indexes.object_id AND s.index_id = sys.indexes.index_id
WHERE sys.objects.type='u'                 --Only UserTables will be selected
AND sys.indexes.type_desc  <> 'HEAP'       --Only indexes will appear, not HEAP tables
AND sys.indexes.type_desc like '%NONCLUSTER%'
ORDER BY 6 desc

Now that you're ready to fix these puppies, you can reorganize or rebuild. A reorganize unshuffles the b-tree by using the free space you left by allowing a fill factor (ahhh, you didn't allow a fill factor? More on that in a moment). The nice thing about a reorganize is you can interrupt it any time without incurring any rollback and it doesn't interfere much with existing SQL activity. Option two is a rebuild. Unless you have enterprise edition though, a rebuild will interfere or deadlock other queries and may escalate to a table lock. You do however have to do a rebuild if you are changing the fill factor (so back to that again).

Now you see the value of a fill factor: it creates extra space for inter-branch growth of the b-tree, so your indices don't fragment as quickly. The extra allocated space also provides the means for running the online reorganization. How much fill should you allow? I start by setting the fill factor to 90 (in other words 10 percent vacant space). Then when I am checking for fragmentation I may adjust the fill lower, depending upon the property of page utilization. Usually I midpoint the difference, so for example if my present fill factor is 90 and the fragmented index shows 70 percent page fullness, I'll schedule a rebuild to set the fill factor down to 80.

Keeping your indices happy will make your developers and your managers happy, and it's a pleasant enough diversion for a daytime job.

Mid-Sized Databases

Most of my problem indices happen on mid-size databases (MSDs), in the range of 100 GB or so. Small databases rebuild their indicies quickly; giant databases tend either to get reloaded and reindexed offline or have a limited exposure to random query plans.  The MSDs though can be sticklers for high random access, high required availability, and a large quantity of fairly large indices.

Several factors contribute to MSD index maintenance woes.  For one, MSDs often run on the Standard Edition of SQL, so you can't rebuild the indices with the "online" option. Secondly MSDs are large enough that rebuilding an index takes a fair amount of time.  Finally the MSD indices often get built by developers who forget to include a fill factor.

Obviously at your first opportunity you will schedule some jobs to rebuild the fragmented indices with an adequate fill factor.  More importantly though you should find some time to meet with the developers and encourage them to use a fill factor (when appropriate) on indices where the referenced fields are clearly loading with out-of-sequence data. They'll be clueless what factor to use so it's easiest to specify a safe fixed fill, say 85 or 90%... then you can adjust it to lower if you find it fragmenting exceedingly fast.

Once you've got your fill allocated the next challenge is finding the time to run index reorganization on a regular basis. Although it won't lock tables or block other spids, the reorgs do take a fair amount of resources. If you've got a nice chunk of quiet time on the weekend you can schedule a big reorg job then, naturally, but if your systems is always active then you might like this little trick.

First build a table within your MSD to hold the reorganization SQL statements; I use something like this:

CREATE TABLE [IndexReorgScripts](
[ReorgSQL] [varchar](350) NOT NULL,
[RunDayNum1] [smallint] NULL,
[RunDayNum2] [smallint] NULL) 

Now from a connection to your MSD, run this query:

INSERT INTO IndexReorgScripts
        (ReorgSQL)
SELECT 'ALTER INDEX ' + [name] + ' ON [dbo].' + OBJECT_NAME(OBJECT_ID) +
' REORGANIZE WITH ( LOB_COMPACTION = ON )' AS ReorgSQL
FROM sys.indexes
where type_desc = 'NONCLUSTERED'
and fill_factor > 0
and is_disabled = 0

This will build all the statements to reorganize the SQL indices. The two RunDayNum columns are for you to set to a day of the month you want the index rebuilt -- I made two columns because I reorganize my indices on average twice a month.  Try to assign a broad range of days across the various tables.

Finally, schedule a SQL job to run this script every evening:

DECLARE @ReorgStatement varchar(350)
DECLARE @ThisDayOfMonth AS smallint
SET @ThisDayOfMonth = DAY(GETDATE())

DECLARE IndicesToReorg CURSOR FOR
SELECT [ReorgSQL]
  FROM [IndexReorgScripts]
  WHERE [ReorgSQL] LIKE 'ALTER INDEX%'
         AND ( [RunDayNum1] = @ThisDayOfMonth
OR [RunDayNum2] = @ThisDayOfMonth )

OPEN IndicesToReorg;
FETCH NEXT FROM IndicesToReorg into @ReorgStatement
WHILE @@FETCH_STATUS = 0
BEGIN
   EXEC(@ReorgStatement)
   FETCH NEXT FROM IndicesToReorg into @ReorgStatement
END
CLOSE IndicesToReorg;
DEALLOCATE IndicesToReorg;

Now you're all set to check in every morning and find another handful of indices got rebuilt!  The method isn't foolproof; you will need to occasionally update the table with additional indices that folks have added, or remove those that got disabled.  Still though quite a lifesaver.

Grokking Query Plans

In an earlier post I gave you some good clues to identify the problematic queries on your instance. After you've finished the initial review in the Designer to resolve bad joins and poorly formed filters, you should click the button "Show Execution Plan". Right off the bat you may see a red warning of a missing index. If the projected savings are high then you should stop and build that index first, before you continue examining the plan.

There are a few hints you should know about when reviewing an execution plan. First off check the width (the thickness) of the arrows joining the elements. Thicker paths indicate more voluminous records are traversing. Now examine the percentage shown underneath each element. They are approximate but indicate how much processing is dedicated to each step. Work from the bottom right corner of the graph to the upper left corner (for some reason the engine displays the actual dataflow sequence right to left, bottom to top).

Generally "scans" are way worse than "seeks," and operations on tables will be slower than the same on an index. The exception is a clustered index scan, which in actuality is a regular table scan (that has a clustered index).

To look at it strictly from the engine's viewpoint: the elapsed time for access depends upon the number of rows it has to read, multiplied by the quantity of times it needs to read them, multiplied by the number of pages it needs to read (determined by the width of the sum of the columns it needs to access and then write to tempdb). From that viewpoint you want to minimize read and write lengths, counts, and widths.

Hover your mouse over one of the query plan boxes and it will show you the estimated number of rows it expects to read, and how often it thinks it need to repeat this operation (number of executions). Table scans (and clustered index scans) of wide tables with many rows should be avoided.

Even with all its smarts the plan engine is only about half as bright as you. The optimizer won't always suggest a helpful index even if you could easily create one. If you examine the criteria in the where clause and the way tables are joined, you can almost always come up with an effective index encapsulating what the engine needs for its filter and joins (thus turning scans into seeks). Furthermore if the where clause has a range of selection (for example "> getdate() - 7") you can build a filtered index that will be even faster.

The primary objective is to narrow down the length and width of your resultset as early into the dataflow as possible, to reduce the stress of joins further up the line. Many times you can do this by building an included subquery, or by splitting out the keys of the targeted records into a temp table to begin with in a first step. In other words, you want to delay adding in the fat wide rows until the end of the dataflow, when you have already applied the necessary filters to determine the count of rows you will be returning.

Spend a bit of occasional love reviewing the worst queries and if the fix requires more than just adding an index, pass your recommendations back to the development staff so they can learn and improve while going forward.

Indices

Most of the time I think it should be spelled "indexes" but okay whatever -- throughout this blog I'll follow my spellcheck and call them indices. There's so much to say about them, all of it fun. What makes indexing so much fun? You can secretly do it in the background (well, you can if you're on enterprise version). You can't screw up too much adding an index. There are all sorts of tools to help spot those that are missing. You get to look at SQL plans to figure out which indices the optimizer missed. They gradually fragment, thus providing you another monitoring opportunity. You get to play around with various fill parameters. You can reorganize them while online. Finally adding indices vastly improves queries and makes you a hero with the developers.

Having said all that, you do have to be fairly judicious with the time you allocate to these puppies. In a RAD environment most of your work will be building and evaluating them, but in a mature legacy system you will likely just have reorganizations or rebuilds scheduled and you won't pay them much mind. In any case there's enough to say about indices that it will stretch into a couple of blog posts, so herewith the details of evaluating, building, and disabling them.

To start, it makes a lot of sense to impose a naming standard at your shop. Nobody likes to see that index DTA_0013x_abcde_482 is fragmented. What the heck does that index do? In my shop we use names like ix_myTable_col1col2col3. To keep the names a useful length we might abbreviate the table or column names.

What indices do you need to build? This query will give you a good clue:

SELECT        mid.statement, round(migs.user_seeks * migs.avg_total_user_cost * migs.avg_user_impact / 100, 0) as imPactor,
    migs.unique_compiles, migs.user_seeks, migs.user_scans, migs.last_user_seek, migs.last_user_scan,
    migs.avg_total_user_cost, migs.avg_user_impact, mig.index_group_handle, mig.index_handle,
              mid.object_id, mid.equality_columns, mid.inequality_columns,
              mid.included_columns
FROM            sys.dm_db_missing_index_group_stats AS migs INNER JOIN
                         sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle INNER JOIN
                         sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
WHERE        (mid.database_id = DB_ID())
and migs.last_user_seek > GETDATE() - 14
ORDER BY 2 DESC

You need to select the database you are analyzing or include a Use statement and then run this for each dB you are inspecting. Should you build every index in this resultset? No, you need to use some good judgement, start at the top, and think about what you're doing. Certainly indices toward the top of the list with a couple key columns and a smattering of included columns make sense. On the other indices you need to use your better judgement.

Why not build them all? Well, indices take IO and CPU power to maintain. They immensely speed up SELECT queries, however they slow down the UPDATEs and INSERTs. Hence if you walk into a shop with a legacy database, you should run this query to see how the existing indices are actually being used:

SELECT sys.objects.name AS object_name, sys.indexes.name AS index_name,
Case when (is_unique = 1 and is_primary_key = 1) then 'PK UNIQUE '
when is_unique = 1  then 'UNIQUE ' else '' end + sys.indexes.type_desc type_desc,
c.index_columns AS index_columns_key, s.used_page_count * 8   AS IndexSizeKB,
sys.dm_db_index_usage_stats.user_seeks, sys.dm_db_index_usage_stats.user_scans,
sys.dm_db_index_usage_stats.user_lookups, sys.dm_db_index_usage_stats.user_updates,
sys.dm_db_index_usage_stats.last_user_seek, sys.dm_db_index_usage_stats.last_user_scan,
sys.dm_db_index_usage_stats.last_user_lookup, sys.dm_db_index_usage_stats.last_user_update
FROM sys.objects JOIN sys.indexes ON sys.indexes.object_id=sys.objects.object_id
JOIN (SELECT distinct
object_id, index_id, stuff((SELECT ','+col_name(object_id,column_id ) as 'data()'
FROM sys.index_columns t2
WHERE t1.object_id =t2.object_id and t1.index_id = t2.index_id
FOR XML PATH ('')),1,1,'') as 'index_columns'
FROM sys.index_columns t1
) c ON c.index_id = sys.indexes.index_id AND c.object_id = sys.indexes.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats
ON sys.indexes.index_id=sys.dm_db_index_usage_stats.index_id
AND sys.indexes.object_id=sys.dm_db_index_usage_stats.object_id
JOIN sys.dm_db_partition_stats  AS s
ON s.[object_id] = sys.indexes.object_id AND s.index_id = sys.indexes.index_id
WHERE sys.objects.type='u'                 --Only UserTables will be selected
AND sys.indexes.type_desc  <> 'HEAP'       --Only indexes will appear, not HEAP tables
AND sys.indexes.type_desc like '%NONCLUSTER%'
ORDER BY 6 desc

Again precede it with a "use" statement or select the database to run it against. The results of this query are useful for two different purposes, but you have to understand that the context of these results are statistics only since the last SQL restart. It's best therefore to review this after your instance has been up and running happily for a few weeks.

First scroll down toward the bottom of the result set. Indices that have zeros or nulls in all the columns of seeks, scans, and lookups aren't being used by the plan optimizer. If they have something in the updates column, then you are incurring the extra overhead of maintaining them without gaining any advantage from their use. Before you disable them however, do a courtesy check to see if they are perhaps badly fragmented -- sometimes this is why they aren't being used in the first place.

And that is the second value of this resultset: those indices shown toward the top of the list are the prime movers, the high optimizers of your database. You should check them regularly to see if they should be rebuilt or reorganized.

Avoiding Phishing

Like all DBAs I suppose I'm irrationally paranoid: with the private records of millions of customer records at my fingertips I am acutely aware of any security threats. Besides the usual best practices of securing the firewall access and installing antivirus software on the clients, a DBA has to be particularly careful to avoid getting phished. An outside miscreant can approach you a couple of ways, by phone or by eMail.

Usually I won't even click on an eMail unless it's from somebody I know and isn't obviously spam. You probably shouldn't open an eMail from someone outside your organization if you weren't previously expecting it. Sometimes though the eMail will appear to be a legitimate communication from somebody you already do business with. Even then, I take special precautions. Never ever click on a link within such an eMail, and don't download images. Rather, save the eMail as an HTML file to disk, and then open it in a simple text editor. Now after verifying the link address corresponds to what you are expecting, manually copy and paste the relevant parts of the link into a browser. Be especially careful of intentional spoofs that may have been injected into the spelling.

Avoiding phish eMails tend to be less taxing than a phishing phone call. A lot of this is due to the immediacy of talking to somebody who is expecting a verbal response. I have a twofold defense to this: first, be the one who is controlling the path of the conversation. Second, work from a prepared script. Don't answer any questions unless the information you are providing helps move the conversation in the direction you wish it to resolve. Use courteous deferral and the handy excuse of "sorry but our vendor security operating procedures require me to first find out...".

I could post the exact script that I've found helpful here, but having such a document in the public domain would defeat its purpose as it encourages "social engineering" to bypass its safeguards. Rather, I will describe its contents in general terms, so you can write your own prepackaged talk track.

The primary purpose of your script should be to positively validate the identity of the caller. You will need to gather enough information about the caller to independently verify who he is and who he works for. Besides his full name, other useful factotums are:

• work eMail address
• private (not work) cell phone number
• name of employer
• city where his employer is located
• department he works for
• name of his supervisor
• how they obtained your phone number

I'm sure you can think of other things to ask. Any reputable vendor account manager should readily be able to provide this information without hesitation. If the caller questions why you need this information explain that your company policies require positive identification of vendors. Whether you record your phone call or not is up to you, but regardless I like saying "this call may be recorded for training or quality assurance purposes." You'd be surprised how many scammers immediately hang up after that little tidbit.

Once you've obtained the above information you may ask the caller how you can help them. At this point you don't want to provide any useful information, but you do want to gauge the nature and the scope of their inquiry. Reply with most of the inquiries with "I can get that for you." Then you need to perform a "backflip" -- ask the caller to hold on, put the phone down briefly, take a slow breath to calm and center yourself, then tell the caller you will need to call them back as something urgent has arisen. Ask for the best time to call, thank them for calling, and hang up.

Now is when the actual work starts. If you are indeed planning to return this call you will need to go through the effort to validate who they are and who they work for. Depending on how suspicious you are, some good avenues to explore include:

• call the main switchboard of their employer and ask for them by name
• find their profile on LinkedIn to verify it shows the same employer
• use a web service to text a verification code to their cell phone
• call their supervisor and ask for verification of employment
• send a message to his eMail address asking for a reply, then verify the routing information in the reply's header

And there's always Google. This discourages most casual phishing, but always be alert to anything suspicious that might compromise your security, and never provide an outsider with any information that isn't abolutley necessary for them to work effectively with you.