Archive for the ‘Database Optimisation’ Category

Index Fragmentation Part 2

Thursday, April 15th, 2010

Further to my previous post Index Fragmentation you can use the following script to automatically rebuild and reorganize indexes.

I have it installed in an agent job that runs daily:

———————————————————————
– Rebuild the Indexes in EBS
———————————————————————

– Declare the Variables
DECLARE  @Table_ID  int,
   @Index_ID  int,
   @Pages   bigint,
   @Index_Name sysname,
   @Fragmentation float,
   @Table_Name sysname,
   @Alter   varchar(200)

– Declare the Cursor
DECLARE Indexes CURSOR FOR

SELECT  ID.object_id AS Table_ID
  ,ID.index_id AS Index_ID
  ,ID.page_count AS Pages
  ,I.name AS Index_Name
  ,ID.Avg_Fragmentation_In_Percent AS Fragmentation
  ,T.name AS Table_Name

FROM  sys.dm_db_index_physical_stats (db_id(), NULL,NULL, NULL, NULL) AS ID
JOIN  sys.indexes AS I WITH(NOLOCK)
ON   ID.Object_id = I.Object_id
 AND   ID.Index_id = I.Index_id
JOIN sys.objects AS T
ON  T.object_id = I.object_id
WHERE  I.name IS NOT NULL

OPEN Indexes
FETCH Indexes INTO @Table_ID,
     @Index_ID,
     @Pages,
     @Index_Name,
     @Fragmentation,
     @Table_Name 

– Start the Processing Loop
WHILE @@Fetch_Status = 0
   BEGIN

 IF @Fragmentation >30
 BEGIN
  set @alter = ‘ALTER INDEX ‘ + @Index_Name + ‘ ON ‘ + @Table_Name + ‘ REBUILD’;
  exec(@alter);
 END
 ELSE IF @Fragmentation >10
 BEGIN
  set @alter = ‘ALTER INDEX ‘ + @Index_Name + ‘ ON ‘ + @Table_Name + ‘ REORGANIZE’;
  exec(@alter);
 END

 – Perform Row-by-row Processing.
 – Get the Next Row
 FETCH Indexes INTO @Table_ID,
      @Index_ID,
      @Pages,
      @Index_Name,
      @Fragmentation,
      @Table_Name
 END

CLOSE Indexes

DEALLOCATE Indexes

RETURN

Posted in DBA, Database Optimisation | No Comments »

Index Fragmentation

Thursday, April 15th, 2010

Indexes can improve performance on your database however they do need to be maintained to. As more data is added to tables the corresponding data is added to an index somtimes in an inefficient order, this process is known as fragmentation.

To target fragmentation you first have to work out which indexes need optimising. I do this with the following code:

SELECT ID.object_id,
ID.index_id,
I.name AS Index_Name,
ID.Avg_Fragmentation_In_Percent AS Fragmentation,
T.name AS Table_Name
FROM sys.dm_db_index_physical_stats (db_id(), NULL,NULL, NULL, NULL) AS ID
JOIN sys.indexes AS I WITH(NOLOCK)
ON ID.Object_id = I.Object_id
AND ID.Index_id = I.Index_id
JOIN sys.objects AS T
ON T.object_id = I.object_id
WHERE Avg_Fragmentation_In_Percent >= 10
AND I.name IS NOT NULL

It ouputs all the indexes with fragmentation over 10% showing the index name, the table its associated with as well as the fragmentation in percent. Any fragmentation below 10% can be left alone.

For the remainder you can choose to rebuild or reorganize an index. A loose rule of thumb is to rebuild an index if its over 30% fragmented and reorganize if its over 10% but below 30%.

The syntax to reorganize or rebuild an index is relatively simple:

ALTER INDEX {INDEX_NAME} ON {TABLE_NAME}
REORGANIZE

Or

ALTER INDEX {INDEX_NAME} ON {TABLE_NAME}
REBUILD

Tags:
Posted in Database Optimisation | No Comments »

Remove Duplicates from a Table

Thursday, April 15th, 2010

Duplicates are a problem for any database. Luckily the problem can be easily solved using a combination of CTEs and Windowed Functions.  Firstly let’s create a table and insert our duplicates:

Create Table Employees(ID int NOT NULL, Forename varchar(50), Surname varchar(50))  Insert into Employees values(1,’John’,'Doe’)
Insert into Employees values(1,’John’,'Doe’)
Insert into Employees values(2,’John’,'Smith’)
Insert into Employees values(2,’John’,'Smith’)
Insert into Employees values(3,’Jane’,'Smith’)
Insert into Employees values(4,’Jane’,'Doe’)

We can now build our code to remove the duplicate John Doe and John Smith:

With Duplicates As
( Select  ID,
  Forename,
  Surname,
  ROW_NUMBER() OVER (PARTITION BY ID,Forename,Surname Order By ID) AS Ranking
  From   Employees
) Delete
From  Duplicates
Where  Ranking >1

If we run a Select statement:

SELECT * FROM Employees

We can see that the duplicate records have been removed.

Tags:
Posted in Database Optimisation | No Comments »