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.

---------------- CREATE TEMPORARY TABLES ----------------DECLARE @DatabaseName NVARCHAR(100) DECLARE @TableName NVARCHAR(100)DECLARE @IndexName NVARCHAR(100)IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL BEGIN
#DatabaseList END
#DatabaseList (
[DatabaseName] NVARCHAR(100) NOT NULL,
IF OBJECT_ID('tempdb..#DatabaseTableIndexList') IS NOT NULLBEGIN
#DatabaseTableIndexList END
#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
[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
#DatabaseTableIndexResult END
#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
1 @DatabaseName = DatabaseName
FROM #DatabaseList

USE ['
+ @DatabaseName + ']
INSERT INTO #DatabaseTableIndexList ([DatabaseName], [TableName], [IndexName], [Rows])
+ @DatabaseName + ''',,, 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
#DatabaseList ---------------- GET INDEX FRAGMENTATION ----------------WHILE EXISTS ( SELECT TOP 1 1 FROM #DatabaseTableIndexList) BEGIN
@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

#DatabaseTableIndexList DROP TABLE #DatabaseTableIndexFragmentation ---------------- FIX FRAGMENTATION ---------------- WHILE EXISTS ( SELECT TOP 1 1 FROM #DatabaseTableIndexResult) BEGIN
@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



* 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):

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,
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
GROUP BY ii.OBJECT_ID,ii.index_id,
) 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
        while ($service.Status -ne "Running")
            Write-Host $serviceName "is" $service.Status      
    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.