Friday, 9 August 2013

SQL: Automation for Database Index Defragmentation

The benefit of using index in SQL is to improve the performance when searching through tables without having to scan for each table referenced in a query. Yet as the table grows, the index expands. More devastatingly, the index becomes fragmented, which actually creates unnecessary overhead for queries.

In order to resolve the fragmentation problem on tables and indexes, I created the following T-SQL to look through all tables and indexes and attempt to defragment them.



USE MASTER
  
---------------- CREATE TEMPORARY TABLES ----------------DECLARE @DatabaseName NVARCHAR(100) DECLARE @TableName NVARCHAR(100)DECLARE @IndexName NVARCHAR(100)IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL BEGIN
   DROP TABLE
#DatabaseList END
CREATE TABLE
#DatabaseList (
  
[DatabaseName] NVARCHAR(100) NOT NULL,
)
IF OBJECT_ID('tempdb..#DatabaseTableIndexList') IS NOT NULLBEGIN
   DROP TABLE
#DatabaseTableIndexList END
CREATE TABLE
#DatabaseTableIndexList (
  
[DatabaseName] NVARCHAR(100) NOT NULL,
  
[TableName] NVARCHAR(100) NULL,
  
[IndexName] NVARCHAR(200) NULL,
  
[Rows] BIGINT) IF OBJECT_ID('tempdb..#DatabaseTableIndexFragmentation') IS NOT NULL BEGIN
   DROP TABLE
#DatabaseTableIndexFragmentationEND
CREATE TABLE
#DatabaseTableIndexFragmentation(
  
[ObjectName] NVARCHAR(200),
  
[ObjectId] INT,
  
[IndexName] NVARCHAR(200),
  
[IndexId] INT,
  
[Level] INT,
  
[Pages] INT,
  
[Rows] INT,
  
[MinimumRecordSize] INT,
  
[MaximumRecordSize] INT,
  
[AverageRecordSize] INT,
  
[ForwardedRecords] INT,
  
[Extents] INT,
  
[ExtentSwitches] INT,
  
[AverageFreeBytes] NUMERIC(16, 10),
  
[AveragePageDensity] NUMERIC(16, 10),
  
[ScanDensity] NUMERIC(16, 10),
  
[BestCount] INT,
  
[ActualCount] INT,
  
[LogicalFragmentation] NUMERIC(16, 10),
  
[ExtentFragmentation] NUMERIC(16, 10)
)
IF OBJECT_ID('tempdb..#DatabaseTableIndexResult') IS NOT NULL BEGIN
   DROP TABLE
#DatabaseTableIndexResult END
CREATE TABLE
#DatabaseTableIndexResult (
  
[DatabaseName] NVARCHAR(100) NOT NULL,
  
[TableName] NVARCHAR(100) NULL,
  
[IndexName] NVARCHAR(200) NULL,
  
[Rows] BIGINT,
  
[Pages] BIGINT,
  
[Density] FLOAT,
  
[Fragmentation] FLOAT)---------------- GET DATABSAES ----------------INSERT INTO #DatabaseList (DatabaseName) SELECT LTRIM(RTRIM(Name))FROM MASTER.dbo.sysdatabasesWHERE Name NOT IN ( 'master', 'model', 'msdb', 'tempdb' ) ---------------- GET TABLES AND INDEXES ---------------- WHILE EXISTS ( SELECT TOP 1 1 FROM #DatabaseList ) BEGIN
   SELECT TOP
1 @DatabaseName = DatabaseName
  
FROM #DatabaseList

  
EXEC('
USE ['
+ @DatabaseName + ']
INSERT INTO #DatabaseTableIndexList ([DatabaseName], [TableName], [IndexName], [Rows])
SELECT '''
+ @DatabaseName + ''', so.name, si.name, si.rows
FROM sysobjects so
INNER JOIN sysindexes si ON so.ID = si.ID
WHERE so.xtype = ''U'' AND si.rows > 1000 AND si.indid > 0 AND si.indid < 255 AND (si.status & 64)=0
'
)

  
DELETE FROM #DatabaseList
  
WHERE DatabaseName = @DatabaseName END
DROP TABLE
#DatabaseList ---------------- GET INDEX FRAGMENTATION ----------------WHILE EXISTS ( SELECT TOP 1 1 FROM #DatabaseTableIndexList) BEGIN
   SELECT TOP
1
      
@DatabaseName = DatabaseName,
      
@TableName = TableName,
      
@IndexName = IndexName
  
FROM #DatabaseTableIndexList

  
INSERT INTO #DatabaseTableIndexFragmentation
  
EXEC('USE [' + @DatabaseName + '] DBCC SHOWCONTIG ( [' + @TableName + '] , [' + @IndexName + '] ) WITH TABLERESULTS, NO_INFOMSGS, FAST ')

  
INSERT INTO #DatabaseTableIndexResult (DatabaseName, TableName, IndexName, [Rows], [Pages], [Density], [Fragmentation])
  
SELECT dti.DatabaseName, dti.TableName, dti.IndexName, dti.Rows, frag.Pages, frag.ScanDensity, frag.LogicalFragmentation
  
FROM #DatabaseTableIndexList dti
  
INNER JOIN #DatabaseTableIndexFragmentation frag ON frag.ObjectName = dti.TableName AND frag.IndexName = dti.IndexName
  
WHERE dti.DatabaseName = @DatabaseName AND dti.TableName = frag.ObjectName AND dti.IndexName = frag.IndexName

  
DELETE FROM #DatabaseTableIndexList
  
WHERE DatabaseName = @DatabaseName AND TableName = @TableName AND IndexName = @IndexNameEND

DROP TABLE
#DatabaseTableIndexList DROP TABLE #DatabaseTableIndexFragmentation ---------------- FIX FRAGMENTATION ---------------- WHILE EXISTS ( SELECT TOP 1 1 FROM #DatabaseTableIndexResult) BEGIN
   SELECT TOP
1
      
@DatabaseName =  DatabaseName,
      
@TableName = TableName,
      
@IndexName = IndexName
  
FROM #DatabaseTablleIndexResult

  
EXEC ( 'DBCC INDEXDEFRAG ( [' + @DatabaseName + '], [' + @TableName + '], [' + @IndexName + '] ) WITH NO_INFOMSGS ' )

  
DELETE FROM #DatabaseTableIndexResult
  
WHERE DatabaseName = @DatabaseName AND TableName = @TableName AND IndexName = @IndexName END

DROP TABLE
#DatabaseTableIndexResult


NOTE:

* For defragmenting large tables, I will rather break them down in the 'GET DATABASES' section to avoid long running job and risks of database block.

IMPROVEMENTS (try them as you see fit):

* Time stamps can be added around 'FIX FRAGMENTATION' section to monitor the time consumption.
* The '#DatabaseTableIndexResult' table can be stored in a table as a record for auditing or monitoring purposes.
* A density lookup can be created to filter out the tables and indexes with low density, which means needing to defragment more urgently.
* Each section can be broken down to individual functions or stored procedures to increase reusability, i.e., separate out 'FIX FRAGMENTATION' so that it can be triggered whenever necessary.

TIP (A query to find all indexes on a database):

SELECT
  
o.name,
  
indexname=i.name,
  
i.index_id,
  
user_seeks,
  
user_scans,
  
user_lookups,
  
reads=user_seeks + user_scans + user_lookups,
  
writes =  user_updates,
  
rows =(SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.OBJECT_ID = p.OBJECT_ID),
  
CASE WHEN s.user_updates < 1 THEN 100 ELSE 1.00 *(s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates END AS reads_per_write,
  
'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.OBJECT_ID)) AS 'drop statement',
   (
      
SELECT (8 * SUM(aa.used_pages))/1024 AS 'Indexsize(MB)'
      
FROM sys.indexes AS ii JOIN sys.partitions AS pp ON pp.OBJECT_ID = ii.OBJECT_ID AND pp.index_id = ii.index_id
      
JOIN sys.allocation_units AS aa ON aa.container_id = pp.partition_id
      
WHERE ii.name = i.name
      
GROUP BY ii.OBJECT_ID,ii.index_id,ii.name
  
) AS size FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.OBJECT_ID = i.OBJECT_ID INNER JOIN sys.objects o ON s.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.schemas c ON o.schema_id = c.schema_id WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1 AND s.database_id = DB_ID()  --AND i.type_desc = 'nonclustered'AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 --AND (SELECT SUM(p.rows) FROM sys.partitions p  WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
--AND (user_seeks + user_scans + user_lookups)=0
ORDER BY reads_per_write, name

Tuesday, 6 August 2013

How to: Create a Windows Service Kickstarter using Powershell

Since some legacy Windows services are getting more and more unstable and fall apart without any means of recovery, a kickstarter is needed to ensure those important(?) services don't just disappear quietly without any struggle.

The idea is to use a combination of Powershell script and Task Scheduler to trigger the job every now and then.

0. Precondition


The Windows Server 2003 R2 does not come with Powershell installed. To allow Powershell scripts to run on the machine, follow the link to download and install it, and bear in mind, system reboot will NOT be required.

1. Powershell Script


Powershell is really easy to pick up. It only requires basic knowledge of Command Prompt, and the rest is just C#.

function ServiceKickstarter($serverName, $serviceName)
{  
    $service = Get-Service -ComputerName $serverName -Name $serviceName
    if ($service.Status -eq "Stopped")
    {
        Write-Host $serviceName "is" $service.Status
        Write-Host "Starting" $serviceName
        $service.Start()
        while ($service.Status -ne "Running")
        {
            Write-Host $serviceName "is" $service.Status      
            $service.Refresh()
            Start-Sleep(1)
        }
    }
    Write-Host $serviceName "is" $service.Status
}

function AllServicesKickstarter ($serverName, $servicesName)
{
    $length = $servicesName.length
    for ($i=0; $i -lt $length; $i++)
    {
        "Start " + $servicesName[$i]
        startService $serverName $servicesName[$i]
    }
}

$server = "<server alias>"
$service = "<service name>"
$services = "<service name>", "<another service name>", "<some other service name>"

ServiceKickstarter $server $service
#AllServicesKickstarter $server $services

NOTE: the server name is not required, if the script is to run from the target server directly.


2. Execution Policy


In order to run the script, you may have to sort out execution policy as well, because the restrictions on some servers. And you will see the following message:

"File … cannot be loaded.  The file is not digitally signed.  The script will not be executed on the system."
 

This has everything to do with server execution policy. You can either choose to sign the script, following the link. Alternatively, change your signing policy.

The execution policy details provided by MSDN are:

Restricted - No scripts can be run. Windows PowerShell can be used only in interactive mode.
AllSigned - Only scripts signed by a trusted publisher can be run.
RemoteSigned - Downloaded scripts must be signed by a trusted publisher before they can be run.
Unrestricted - No restrictions; all Windows PowerShell scripts can be run.

 

Instead of using default (which allows you to run nothing), 'RemoteSigned' is the level that can satisfy our requirement, and manage not to piss sysadmins off.

3. Schedule Task


The last job is to create the schedule task to make sure the script will check the target service(s). That's just to follow the Task Scheduler Wizard, and you will get there eventually.

Monday, 5 August 2013

Tips and Hints: Windows Server 2008 Scheduled Tasks Result Codes

0 or 0x0: The operation completed successfully.
1 or 0x1: Incorrect function called or unknown function called.
12 or 0x2: File not found.
110 or 0xa: The environment is incorrect.
0x41300: Task is ready to run at its next scheduled time.
0x41301: Task is currently running.
0x41302: Task is disabled.
0x41303: Task has not yet run.
0x41304: There are no more runs scheduled for this task.
0x41306: Task is terminated.
0x8004130F: Credentials became corrupted (*)
0x8004131F: An instance of this task is already running.
0x800704DD: The service is not available (is 'Run only when an user is logged on' checked?)
0xC000013A: The application terminated as a result of a CTRL+C.
0xC06D007E: Unknown software exception.


Ref: http://msdn.microsoft.com/en-us/library/aa383604