Defragmenting SQL Server 2008+ using ALTER INDEX

-
In SQL 2009 DBCC INDEXDEFRAG will no longer work as Microsoft have removed the command. You need to user ALTER INDEX instead:

CREATE PROCEDURE sjh_defragment_indexes_2008
(
    @maxfrag FLOAT
)
AS
 
/* Simon Hughes 15 May 2010
 
For SQL 2008+
This stored procedure checks index fragmentation in a database and defragments
indexes whose fragmentation fall above a specified threshold: @maxfrag
 
Must be run in the database to be defragmented.
 
exec sjh_defragment_indexes_2008 5
*/
 
SET NOCOUNT ON
SET XACT_ABORT ON
 
-- Write start time for information purposes
PRINT 'Started defragmenting at: ' + CONVERT(VARCHAR, GETDATE())
 
-- Check fragmentation
DECLARE @execstr NVARCHAR(255)
DECLARE lcsr CURSOR local fast_forward READ_ONLY FOR
    SELECT  'ALTER INDEX [' + i.name + '] ON [' + so.name + '] REORGANIZE;' AS execstr
    FROM    sys.sysobjects AS so
            INNER JOIN sys.dm_db_index_physical_stats(DB_ID('bybox'), NULL, NULL, NULL, 'LIMITED') AS phystat
            INNER JOIN sys.indexes AS i
                ON i.object_id = phystat.object_id
                   AND i.index_id = phystat.index_id
                ON so.name = OBJECT_NAME(i.object_id)
    WHERE   (i.name IS NOT NULL)
            AND (so.type = 'U')
            AND (phystat.avg_fragmentation_in_percent > @maxfrag)
 
OPEN lcsr
FETCH NEXT FROM lcsr INTO @execstr
 
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_executesql @execstr
    FETCH NEXT FROM lcsr INTO @execstr
END
 
CLOSE lcsr
DEALLOCATE lcsr
 
-- Report on finish time for information purposes
PRINT 'Finished defragmenting at: ' + CONVERT(VARCHAR, GETDATE())
GO

Download code here.

Popular posts from this blog

AWS DynamoDB vs Azure CosmosDB vs Azure Table Storage pricing comparison

-
Image
Using a db size of 10Gb, and 10% writes and 90% reads. Provisioned databases are for 1 year. For AWS SimpleDB the amount of CPU is hard to quantify, so I used the following formulae (0.248+((requestsPerSecond)/1.5*0.14))*usdgbp This matches closly to Azure Table Storage, which is to be expected. Here are the results: (click on image to view) (click on image to view) Zooming into the lower end (click on image to view) Download Azure-CosmosDB-pricing.xlsx spreadsheet here . Summary:   Both AWS and Azure are similar in NoSQL pricing. For the small companies out there needing the cheapest NoSQL options there is Azure Table Storage and AWS SimpleDB . These are the cheapest up until you hit 400 reads per seconds which is plenty for most companies. However, be aware of the per-item size constraints below. Table Storage is about 13ms for a read and CosmosDB is about 8ms. Many developers use Amazon SimpleDB in conjunction with Amazon Simple Storage Service (Amazon S3). Amazon Sim...
Read more

Service Broker sys.transmission_queue clean up

-
If you’re developing a Service Broker application and you misspell your services, forget to create a master key, or do any of the many things that result in your messages not being delivered, then your transmission queue will fill up. Due to the nature of service broker, errors don't occur at the client but rather your messages go in to Service Brokers sys.transmission_queue and are then subsequently processed. If they fail, they stay in the queue. Some errors can be resolved, i.e. missing services. However, others can't, or are just plain difficult. I.e. You've misspelt something, or not used the correct security. To clear your transmission queue you need to end the conversations, this is a handy little script for doing this. Be aware this ends ALL conversations in the queue and so should NEVER be used on a production system. To see how many messages are in your transmission queue, use this SELECT   COUNT ( 1 )   FROM   sys . transmission_queue On my system i7, D...
Read more

EntityFramework Reverse POCO Code First Generator

-
Reverse engineers an existing database and generates EntityFramework Code First POCO classes, Configuration mappings and DbContext. To install and use this project: Use Nuget and install EntityFramework. Add a connect string to your app.config. Somethine like: In Visual Studio, right click project and select "add - new item". Select Online, and search for "reverse poco". Or you can download it from this page. Select "EntityFramework Reverse POCO Code First Generator". Give the file a name, such as Database.tt and click Add. Edit the Database.tt file and specify the connection string as MyDbContext which matches your name in app.config. Save the Database.tt file, which will now generate the Database.cs file. To see a video, head over to http://visualstudiogallery.msdn.microsoft.com/ee4fcff9-0c4c-4179-afd9-7a2fb90f5838
Read more