DBA and SysAdmin World

February 10, 2009

Review Fill Factor of The Indexes

Filed under: Script — Tags: , , — solihinho @ 12:53 AM
analyze Background
When I worked as DBA in my office, It has already hundreds of databases. I have to know the functionality of each databases. How often the tables of the database are accessed, inserted and updated by users. Once you already know, it is easier for DBA to decide the fill factor for the index of the tables.

The right value of the fill factor will upgrade the performance of the application. The fill factor value is a percentage between 0 to 100. This percentage specifies how much to fill the data pages after the index is created. A value of 100 means the pages will be full and will take the least amount of storage space. This setting should be used only when there will be no changes to the data, for example, on a read-only table. A lower value leaves more empty space on the data pages, which reduces the need to split data pages as indexes grow but requires more storage space. This setting is more appropriate when there will be changes to the data in the table.

The fill factor is implemented only when the index is created; it is not maintained after the index is created as data is added, deleted, or updated in the table. Trying to maintain the extra space on the data pages would defeat the purpose of originally using the fill factor because SQL Server would have to perform page splits to maintain the percentage of free space, specified by the fill factor, on each page as data is entered. Therefore, if the data in the table is significantly modified and new data added, the empty space in the data pages can fill. In this situation, the index can be re-created and the fill factor specified again to redistribute the data (SQL Server BOL 2000).

Therefore I created script for listing all indexes’s fill factor. There are 2 types of script. One for SQL 2000 and other for SQL 2005. For SQL 2005 also list value of the index fragmentation.

Script
SQL Server 2000

/*
  Created by : Solihin ho - https://solihinho.wordpress.com
  
  Compatibility : SQL 2000

*/

IF object_id('tempdb..#result') IS NOT NULL
   DROP TABLE #result

CREATE TABLE #result
(
   DBName       sysname,
   TableName    sysname,
   IndexName    sysname,
   [Rows]       int,
   [FillFactor] tinyint,
   [TimeStamp]  datetime
)

GO

sp_msforeachdb 'USE ? 
INSERT #result (DbName, TableName, IndexName, [Rows], [FillFactor], [TimeStamp])
SELECT db_name() as DbName
,o.name as TableName
,i.name as IndexName
,i.rows as RowsCount
,i.OrigFillFactor
,GetDate() as [TimeStamp]
FROM sysindexes i
INNER JOIN sysobjects o ON i.id = o.id
WHERE i.indid > 0 and i.indid < 255
AND i.name NOT LIKE ''_WA_Sys_%'''

SELECT * FROM #Result

SQL Server 2005 and Next Version

/*
  Created by : Solihin ho - https://solihinho.wordpress.com
  
  Compatibility : SQL 2005 and next version

*/

IF object_id('tempdb..#result') IS NOT NULL
   DROP TABLE #result

CREATE TABLE #result
(
   DBName       sysname,
   TableName    sysname,
   IndexName    sysname,
   [Rows]       int,
   [FillFactor] tinyint,
   Index_Fragmentation float,
   page_count   int, 
   [TimeStamp]  datetime
)

GO


sp_msforeachdb 'USE ?
INSERT INTO #Result (DBName, TableName, IndexName
    , [FillFactor], [Rows], Index_Fragmentation
    , page_count, [TimeStamp])
SELECT
  db_name() AS DbName
, B.name AS TableName
, C.name AS IndexName
, C.fill_factor AS IndexFillFactor
, D.rows AS RowsCount
, A.avg_fragmentation_in_percent
, A.page_count
, GetDate() as [TimeStamp]
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN sys.objects B 
   ON A.object_id = B.object_id
INNER JOIN sys.indexes C 
   ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D 
   ON B.object_id = D.object_id AND A.index_id = D.index_id
WHERE C.index_id > 0'

SELECT * FROM #Result

What next?
You can schedule above script and keep the result in a table for reviewing next time. You can review the row count to see whether the table grow or not. Just decide the value of the table’s fill factor e.g.: 100% for read only table. For SQL 2005 script, you also review of the index fragmentation. You can review how fast the index become fragmented. You can set the fill factor until you get a best value for the index.

January 31, 2009

Missing an Index? Check for it ..

Filed under: Script — Tags: , , — solihinho @ 12:14 AM
SqlServer2005
Behind the scene
There was a story when I created this script. There was an issue on our production server. After investigated, there was no index on one table but the table has an index on testing environment. Looks like when published the table, the DBA missed publish the index. After this issue, my supervisor asked me to create a script for listing all tables that do not have an index. Every table should be have an index obviously. Below script could not run on SQL Server 2000 and previous version.

Script
Below the script for list index availability of the tables.

Result of the script

Column Name Description
TableName Name of the table
SchemaName Name of the schema
HasIndex Yes if the table has an index vice versa
IndexName Name of the index
IndexKeys Keys of the index
IsPrimaryKey Is this index a primary key?
IndexType Type of the index whether clustered or non clustered
/*
Created by : Solihin ho - https://solihinho.wordpress.com

Compatibility : SQL Server 2005 and next
*/


DECLARE @ObjectID INT, @IndexID INT
DECLARE @ObjectName SYSNAME, @SchemaName SYSNAME
DECLARE @ColumnName SYSNAME
DECLARE @IndexKeys  NVARCHAR(1000)

IF OBJECT_ID ('tempdb..#Result') IS NOT NULL 
  DROP TABLE #Result

CREATE TABLE #Result
(	
   ObjectID     INT,
   ObjectName   SYSNAME,
   SchemaName   SYSNAME,
   IndexId      INT,
   IndexName    SYSNAME NULL,
   IndexType    NVARCHAR(60),
   IndexKeys    NVARCHAR(1000),
   HasIndex     VARCHAR(3),
   IsPrimaryKey BIT
)

INSERT INTO #Result (ObjectID, ObjectName, SchemaName, IndexId
           , IndexName, IndexType, HasIndex, IsPrimaryKey)
SELECT o.object_id, o.name AS ObjectName
  , s.name as SchemaName
  , i.index_id, i.name AS IndexName
  , i.type_desc
  , 'No'
  , i.is_primary_key
FROM sys.objects o
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.indexes i ON o.object_id = i.object_id
WHERE o.type = 'U'

--Index Keys
IF (OBJECT_ID('tempdb..#keys_temp') IS NOT NULL) 
   DROP TABLE #keys_temp

SELECT r.ObjectID, r.IndexId, c.name AS ColumnName 
INTO #keys_temp
FROM #Result r
LEFT OUTER JOIN sys.index_columns ic 
   ON r.ObjectID = ic.object_id AND r.IndexID = ic.index_id
LEFT OUTER JOIN sys.columns c 
   ON c.column_id = ic.column_id AND r.ObjectID = c.object_id 
WHERE r.IndexID > 0


WHILE (1 = 1)
BEGIN

SET @IndexKeys = ''
SET @ObjectID = NULL
SET @IndexID = NULL

SELECT TOP 1 @ObjectID = ObjectID, @IndexID = IndexID
FROM #keys_temp
ORDER BY ObjectID, IndexID

IF @ObjectID IS NULL
   GOTO _Loop1

WHILE (1 = 1)
BEGIN
 
   SET @ColumnName = NULL
   
   SELECT TOP 1 @ColumnName = ColumnName 
   FROM #keys_temp
   WHERE ObjectID = @ObjectID AND IndexID = @IndexID
   ORDER BY ColumnName
   
   IF @ColumnName IS NULL
     GOTO _Loop2

   SET @IndexKeys = @IndexKeys + @ColumnName + ', '

   DELETE FROM #keys_temp 
   WHERE ObjectID = @ObjectID AND IndexID = @IndexID 
     AND ColumnName = @ColumnName
END

_Loop2:
   UPDATE #Result SET IndexKeys = LEFT(@IndexKeys, LEN(@IndexKeys)-1) 
   WHERE ObjectID = @ObjectID AND IndexID = @IndexID

END
_Loop1:
   DROP TABLE #keys_temp
   
   IF OBJECT_ID('tempdb..#object_temp') IS NOT NULL 
      DROP TABLE #object_temp

   SELECT DISTINCT ObjectName, SchemaName INTO #object_temp 
   FROM #Result
   
   WHILE (1 = 1)
   BEGIN
      SET @ObjectName = NULL
 
      SELECT TOP 1 @ObjectName = ObjectName, @SchemaName = SchemaName
      FROM #object_temp
      ORDER BY ObjectName, SchemaName

      IF @ObjectName IS NULL
         GOTO _Loop3

      IF EXISTS (SELECT * FROM #Result WHERE ObjectName = @ObjectName
                        AND SchemaName = @SchemaName
                        AND IndexID > 0)
      BEGIN
         UPDATE #Result SET HasIndex = 'Yes' 
         WHERE ObjectName = @ObjectName AND SchemaName = @SchemaName                                        
      END 

      DELETE FROM #object_temp WHERE ObjectName = @ObjectName
               AND SchemaName = @SchemaName
   END

_Loop3:
   
   SELECT ObjectName AS TableName
   , SchemaName
   , HasIndex
   , IndexName
   , IndexKeys
   , CASE WHEN IsPrimaryKey = 1 Then 'Yes' ELSE 'No' END AS IsPrimaryKey
   , IndexType
   FROM #Result
   ORDER BY ObjectName, SchemaName


This is the result when I ran the script on AdventureWorks database.
result

December 6, 2008

Index Fragmentation in SQL 2005

Filed under: SQL Server — Tags: , , — solihinho @ 11:40 PM
fragment Last Tuesday, our email administrators had a problem. One of their 3rd tools became slow from one day to another day. After they contacted the vendor, the vendor asked to check the database which is using SQL 2005. Because our email administrators do not have knowledge about database, it’s time for DBA take an action Cool.

Money Eyes Some companies think that they don’t need a DBA. These days almost all windows applications using SQL Server as their database. Unfortunately almost of these applications do not provide a task for maintenance their own database. My company has many 3rd party applications that using SQL Server. If there is a performance issue, the vendor always ask the customer to check the database and the box health. So as DBA is possible that we have to maintenance an application of another section like email, citrix, proxy, web application, sharepoint, K2 workflow, Business Object etc besides our in house database application .

After this issue came up, I ask the email admin to granted me as sysadmin role to their database which is located on their box. When I checked the index fragmentation, some of indexes were very fragmented.

Detecting Index Fragmentation
In versions prior to SQL Server 2005, for displaying fragmentation information for the data and indexes of specified table or view using DBCC SHOWCONTIG command. We still can use this command for SQL Server 2005 with some limitations but this feature will be removed in the next version according to MSDN. For the replacement, SQL Server 2005 and next version provide dynamic management function to display information about index fragmentation is sys.dm_db_index_physical_stats. This function need parameters such as database id, table id, index id, partition number and the mode. For further information just visit the MSDN or see SQL Server book online. There are many enhancement from this function than DBCC SHOWCONTIG command such as handle index partition information which is a new feature on SQL Server 2005.

The most important information from this function is column avg_fragmentation_in_percent. The range value from 0 to 100. 100 means the index is fragmented and DBA should reorganize or rebuild the index.

Here is an example how to display the index fragmentation for active database. From function sys.dm_index_physical_stats join to table sys.objects to get the table name, join to sys.indexes to get the index name and current fill factor setting and join to sys.partitions to get the approximate number of rows in the index partition.

SELECT
  db_name() AS DatabaseName
, B.name AS TableName
, C.name AS IndexName
, C.fill_factor AS IndexFillFactor
, D.rows AS RowsCount
, A.avg_fragmentation_in_percent, A.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN sys.objects B ON A.object_id = B.object_id
INNER JOIN sys.indexes C ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D ON B.object_id = D.object_id AND A.index_id = D.index_id

Reorganize OR Rebuild Index
In SQL Server 2000, we use DBCC INDEXDEFRAG to reorganize index and DBCC DBREINDEX to rebuild index. This feature is still available on SQL Server 2005 but this feature will be removed in future version of Microsoft SQL Server. In SQL 2005 and next version, these commands will be replaced with ALTER INDEX command. For reorganize the index use REORGANIZE option and for rebuild the index use REBUILD option with ALTER INDEX command.

Rebuild :
ALTER INDEX [IndexName] ON [SchemaName].[TableName] REBUILD;

Reorganize :

ALTER INDEX [IndexName] ON [SchemaName].[TableName] REORGANIZE;

If you have reorganized or rebuilt the index, you can check again avg_fragmentation_in_percent and the page_count value will reduce.
Reorganize index uses minimal system resources and other processes such as update, insert and delete on this table won’t be blocked.  But if you rebuild the index, other processes on this table won’t be proceeded until the rebuild process is finished.
Rebuild the index is better rather than reorganize the index. But if it is hard to find the right time to rebuild the index especially the huge table so just reorganize the index. It is much better rather doing nothing Batting Eyelashes.

Online Rebuild Index
When working on SQL Server 2000, as our production server must online 24×7, it is little difficult to do rebuild index on big table because during this process other processes will not be able to do DML activities on this table. This is a major hindrance of rebuilding index.

SQL Server 2005 introduces the ability for DBA to do online rebuild index so other processes can access the table when the rebuild is occurring. This feature is really helpful so we can rebuild our index any time. But of course it is better to do it on off peak hours Winking. When doing online rebuild index, just make sure that you have enough disk space on your box because during this process, actually SQL Server create a new index that mimics of current index. After this processing is finished, the old index will be dropped and replace with the new one.

Coffee Online rebuild index is available only on SQL Server 2005 Enterprise Edition.
Online Rebuild Index :
ALTER INDEX [IndexName] ON [SchemaName].[TableName] REBUILD WITH (ONLINE = ON);

Best Practice
When collecting index fragmentation status, it is recommended to keep the data in table. Add timestamp column to this table. Just schedule this task to do it either weekly or monthly.

IdeaJust modified above query for detecting index fragmentation. You can make it loop to all database and keep the result on DBA database. We have a database called SQLDBAdb to keep all information that we need to keep and review.

From this data we can review how fast the indexes become fragmented so we can adjust the fill factor of the index. (See fill factor information from BOL for further information). To see current setting fill factor of the index, we can query from table sys.indexes. To change fill factor value of the index, use ALTER INDEX with FILLFACTOR option.

Blog at WordPress.com.