Monday 16 June 2014

Optimising Index Optimisations on SQL Server


So you have created an maintenance plan for your databases which will rebuild indexes? Nice.
Is it taking a long time? Hmmm... Not so nice.

How can you improve this? Firstly, sometimes you can and should avoid messing with your indexes. Blindly rebuilding all indexes all of the time is a bit 'brute force' - you can however just get away with reorganising the index, and in other cases the index might not need any attention whatsoever. This is a more intelligent and optimised approach which could get you valuable time during a maintenance plan.
The sql code snippet below will 'intelligently' generate index optimising DDL. 

Remarks:
  1. Assumes that you know the difference between an index rebuild and an index reorganise.
  2. Avoid using DBCC DBREINDEX. It is obsolete and will be removed from a future version. (although still present in sql server 2014). Instead use ALTER INDEX.
  3. Using the Microsoft suggested fragmentation thresholds i.e. 
    1. <= 5% - dont do anything. The performance gains will be negligible compared relative to the cost of  rebuilding or reorganising.
    2. > 5% and <= 30% - perform a reorganise
    3. > 30% - perform a rebuild
  4. Assumes you are optimising a single database - i.e. the database where this sp will execute.
  5. Allows accuracy to be tweaked via @statsMode parameter.
  6. Assumes that you want to optimise all tables
  7. Assumes you have a sysadmin role on the server.
  8. Assumes you have dbo or db_ddladmin role on database.
  9. Assumes a constant fill factor for all tables e.g. 90%. Its a good starting point default but is it really the best setting for every index in your database? Probably not! Some tables will grow very fast and some wont grow at all. Meaning 90% will not be enough or be overkill respectively. Experiment by seeing how often you need to defrag each of your indexes. This suggests a further improvement for this sql - how to automatically decide good fill factors based on past fragmentation. This assumes you are regularly optimising indexes e.g. every day or every week and the database is growing at a fairly constant rate, then you could use the fragmentation percentage as a guide to 'predicting' what the fill factor should be during the next index rebuild. (you cant set fill factor during a reorganisation)
  10. In real life, I run the generated DDL statements script during my overnight maintenance plan.

IF  EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[db_ddladmin].[spGenerateIndexOptimisationDDL]') AND TYPE IN (N'P', N'PC'))
DROP PROCEDURE [db_ddladmin].[spGenerateIndexOptimisationDDL]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Joe
-- Create date: 16/06/2014
-- Description: Build appropriate DDL to optimise indexes
-- =============================================
CREATE PROCEDURE [db_ddladmin].[spGenerateIndexOptimisationDDL]
@forceRebuild BIT = 0,
@forceReorganise BIT = 0,
@defaultFillFactor TINYINT = 90,
@statsMode nvarchar(20) = 'LIMITED' --(least accuracy, least time) LIMITED | SAMPLED | DETAILED (most accuray, most time)
AS
BEGIN

SET NOCOUNT ON;

if(@forceRebuild = 1)
begin
SELECT 
'ALTER INDEX [' + i.name + '] ON [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] REBUILD WITH (FILLFACTOR = ' + CAST(@defaultFillFactor AS VARCHAR(3)) + ', SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);'
FROM sys.indexes AS i
INNER JOIN sys.tables AS t ON t.object_id = i.object_id
WHERE i.name IS NOT NULL;
end
else if(@forceReorganise = 1)
begin
SELECT 
'ALTER INDEX [' + i.name + '] ON [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] REORGANIZE;'
FROM sys.indexes AS i
INNER JOIN sys.tables AS t ON t.object_id = i.object_id
WHERE i.name IS NOT NULL;
end
else
begin 
SELECT 
CASE 
WHEN s.avg_fragmentation_in_percent > 5.0 AND s.avg_fragmentation_in_percent <= 30.0 THEN 'ALTER INDEX [' + i.name + '] ON [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] REORGANIZE;'
WHEN s.avg_fragmentation_in_percent > 30.0 THEN 'ALTER INDEX [' + i.name + '] ON [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] REBUILD WITH (FILLFACTOR = ' + CAST(@defaultFillFactor AS VARCHAR(3)) + ', SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);'
ELSE NULL
END
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, @statsMode) AS s
INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
INNER JOIN sys.tables AS t ON t.object_id = i.object_id
WHERE s.avg_fragmentation_in_percent > 5
AND i.name IS NOT NULL;
end
END;
GO

/* 
-- TESTING

EXEC [db_ddladmin].[spGenerateIndexOptimisationDDL] 
@forceRebuild = 0,
@forceReorganise = 0,
@defaultFillFactor = 90,
@statsMode = 'SAMPLED' --(least accuracy, least time) LIMITED | SAMPLED | DETAILED (most accuray, most time)
*/



References:
http://msdn.microsoft.com/en-gb/library/ms189858.aspx

Thursday 15 May 2014

SQL Server Intellisense Not Working?

Im using SQL Server 2008 - or to be exact (as sometimes these things make a difference...:)

Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (X64)   Aug 22 2012 19:25:47   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1.

With that said, you should check the following:

  1. With an open query in SSMS > Edit > Intellisense > Refresh Local Cache (Ctrl + Shift + R)
  2. SSMS > Tools > Options > Text Editor > Transact-SQL > General : > Statement completion:
    1. auto list members = checked
    2. parameter information = checked
  3. SSMS > Tools > Options > Text Editor > Transact-SQL > IntelliSense > Enable IntelliSense
  4. With an open query in SSMS > Query > Intellisense Enabled (should already be enabled because of point 3 but just in case...)
  5. With an open query in SSMS > Query > SQLCMD Mode should be disabled (if you want intellisense to work!) This is what caught me out recently and prompted this post. I had set my queries set to open in SQLCMD mode by default i.e. SMS > Tools > Options > Query Execution > SQL Server > General > 'By default, open new queries in SQLCMD mode' = checked.
If its still not working then try the latest services packs, cumulative updates, installation repair or just turn it off and on again. Good luck. Happy sensing!