SQL Server Rebuild or Reoganize Your Indexes – It depends, but here is a script I use.

So if you are like me, chances are you have that one server where the reindexing or reorganizing is taking a long time to finish. I came up with a solution that has helped me reduce the time it takes to maintain my indexes.

Background

Rebuilding indexes basically recreates an index from scratch (retrieving the data from the table again) whereas reorganizing is a process that relocates the pages of an index.

Rebuilding an Index

Rebuilding an index is typically an offline process, unless you have the Enterprise edition of SQL Server with the online flag set (check BOL for your particular version). While non-clustered indexes are being rebuilt, the table is available. However, rebuilding clustered indexes results in the the table being offline.

Reorganizing an Index

Reorganizing is an online process. It can reduce your fragmentation, but doesn’t always. Remember it is only moving pages around and not actually recreating the pages from data in the table.

Best Practices

Based on what I have read from various experts of SQL Server, rebuilding is recommended for indexes over 30 percent fragmentation. Under 30 percent, the index should be reorganized.

It is also recommended to not do anything to the indexes that are under 100 pages in size as they will not gain anything from a rebuild or reorganize. Of course, sometimes you might need to rebuild a small index, if for example, one of the pages has data corruption.

What the Script Does

Based on the best practices I mention above, it scans the sys.indexes view of every database on your SQL Server instance and then creates (and executes) rebuild or reogranize command for each index that has more than 5% fragmentation and more than 100 pages in size.

It uses the server default fill factor, which you can set in the properties of server instance.It currently DOES rebuild clustered indexes, so if you are running this be sure to run it in your maintenance window.

I encourage you to read through and understand the script as well as test it in your test environment before running it on production to get a feel for its behavior.

Let me know if it helps you out, I know it has helped me get more done in my maintenance window.

DECLARE @rebuildThreshold FLOAT;
SET @rebuildThreshold = 30.0;

CREATE TABLE #indices (
dbname VARCHAR(300),
tablename VARCHAR(300),
indexname VARCHAR(300),
fragmentation FLOAT
)
DECLARE @db SYSNAME;
DECLARE @sql VARCHAR(2000);

DECLARE curs CURSOR
FOR
SELECT  name
FROM    sys.databases
WHERE   name<>’tempdb’  AND state_desc <> ‘OFFLINE’ AND is_read_only = 0

OPEN curs

FETCH NEXT FROM curs INTO @db;
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql = ‘SELECT ”’ + @db + ”’,
”[” + SCHEMA_NAME(schema_id) + ”].[” + OBJECT_NAME(i.object_id, DB_ID(”’ + @db + ”’)) + ”]”,
i.name AS indexname,
ips.avg_fragmentation_in_percent
FROM    sys.dm_db_index_physical_stats(DB_ID(”’ + @db + ”’), NULL, NULL, NULL, NULL) ips
INNER JOIN [‘ + @db + ‘].sys.indexes i ON ips.object_id=i.object_id AND
ips.index_id=i.index_id
INNER JOIN [‘ + @db + ‘].sys.objects o ON i.object_id = o.object_id
WHERE   page_count>=100 AND
avg_fragmentation_in_percent > 5’;

INSERT  INTO #indices
(
dbname,
tablename,
indexname,
fragmentation
)
EXEC (
@sql
);

FETCH NEXT FROM curs INTO @db;
END
CLOSE curs;
DEALLOCATE curs;

DECLARE curs2 CURSOR
FOR
SELECT  CASE WHEN fragmentation<@rebuildThreshold THEN ‘ALTER INDEX ‘ + indexname + ‘ ON [‘ + dbname + ‘].’ + tablename + ‘ REORGANIZE;’
ELSE ‘ALTER INDEX ‘ + indexname + ‘ ON [‘ + dbname + ‘].’ + tablename + ‘ REBUILD;’
END
FROM    #indices
WHERE   indexname IS NOT NULL;

OPEN curs2;

FETCH NEXT FROM curs2 INTO @sql;
WHILE @@FETCH_STATUS=0
BEGIN
EXEC (@sql);
PRINT @sql;

FETCH NEXT FROM curs2 INTO @sql;
END

CLOSE curs2;
DEALLOCATE curs2;

DROP TABLE #indices

Advertisements

Monitoring SQL Server with Profiler ~ 5 Things to Avoid

SQL Profiler

Everyone loves Profiler and for good reason, it provides you with good data to help you make your SQL Server run even better. To quote FDR, “great power involves great responsibility.” Here is a brief list of things to avoid.

Run Profiler on the same server as SQL Server.

This adds too much overhead to the server, please don’t do it, EVER! Ideally, run it on a test server that has some good system resources to be able to capture and analyse all that data.

Select too many events to trace.

For example, selecting Batch Begin and Batch Completed. Choose only what you need.

Don’t filter the results.

Filtering saves you time crawling through the results and saves SQL Server time. This falls back on number 3, choose only what you need.

Don’t join the Performance Monitoring data with Profiler data.

Why chase down a problem, that is not the real cause of your slow server? While you may see a small performance boost on the server, you may be missing the elephant in the room.

How To: Review Saved Perfmon (aka Performance Monitor) Binary Log (BLG) Files

Alright so this isn’t really obvious  in Perfmon.  This is what you do to review a log file.

Step 1: Right mouse click on Performance Monitor (figure 1).

Step 2: Select Properties.Step 3: Select Source then connect to the file, database, etc. (figure 2).

SQL Server News – Denali!!!

If you are interested in taking a look at the latest and greatest…Denali is found here: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9

What’s new?

Column based query acceleration is greatly improved. Computed values and aggregates are calculated much, much faster (at least by a factor of 10 according to Microsoft).

Improved performance for FileStream. FileStream was a technology that was introduced in SQL Server 2008.

BI has also been greatly improved with a lot more functionality.

Better programming interfaces.

A workflow approach has been included for better integration.

It now uses Visual Studio 2010 for its interface!

There is now an actual dashboard that is more responsive, a nice bell or whistle (whichever you prefer).

The official Microsoft page for all future versions of SQL Server is here:

http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx

Removing Unused Indexes

Removing Unused Indexes.

This is some thought provoking information. For those of you who add indexes to everything…

SQL Server Performance Tips For The Programmer

Through the years I have amassed some tips and trick to make SQL Server run more efficiently. For right now, I would like to tell you about some common mistakes that developers make, I know I have made some of these!

Tip 1 – It’s all about the cached execution plan!

Ok so SQL Server has execution plans, big deal,  what does this mean to you? Here’s what you need to know, for every query you execute SQL Server determines the best possible way of getting data from the database files. For some very complex queries, the possibilities could be well into the millions! To save time executing repeated queries, it stores the best possible way of executing these queries in a cache.

Those are the details but here is how to take advantage of this. Store the queries you are using in Stored Procedures. This will help SQL Server store the execution plan. There is one secret to this, assign the parameters to local variables within the Stored Procedures!

Tip 2 – Reduce Network Traffic!

You may have heard that Select * is a bad thing well turns out that there a number of reasons. The big one extra packets are getting sent from the SQL Server. Secondly it also increases disk I/O on the server. In SQL Server, less is more. Also if you SET NOCOUNT ON, the server won’t send messages back to your program throughout it’s execution so traffic is further reduced.

Tip 3 – Set the Initial Database Size Accordingly!

If you set it too small, as the database expands, the data file(s) will grow in size but not necessarily together. These files will become fragmented on the disk. This results in more disk operations, which unfortunately result in more CPU usage as well as more disk operations. Sizing it appropriately will prevent this fragmentation from occurring for the most part. If you feel that you databases are too fragmented you could rebuild the databases or detach them from the SQL Server engine and run a disk defrag on the disk where the database files are stored.

There is another aspect of this. Set the growth increments large enough. If the increments are too small you will create more fragmentation for the database.

Tip 4 – Use Indexes!

Use indexes where your “where” clauses point to.  When indexes are not used the query has to scan every record in the table to find the results. If it is indexed, SQL Server scans through the index (containing just the field(s) that you need). Which results in less disk I/O and much faster results. Don’t do this on fields that are constantly written over because because writing to the field has two writes (one in the table and one in the index).  Also constant changes can lead to index fragmentation, which will slow down queries where many rows are returned.

That’s it for now but more will be posted in the future.  If you found this useful, please subscribe.