C Sharp Logs: November 2008

Looks neat http://sqlfool.com/2008/11/updated-index-defrag-script-2005-2008/

For the standard version of SQL server the script is: (all credits to the author in the link provided above):

   1:  IF EXISTS(SELECT OBJECT_ID FROM sys.tables

   2:  WHERE [name] = N'dba_indexDefragLog')

   3:  BEGIN

4:

   5:  DROP TABLE dbo.dba_indexDefragLog;

6:

   7:  PRINT 'dba_indexDefragLog table dropped!';

8:

   9:  END

10:

  11:  CREATE TABLE dbo.dba_indexDefragLog

  12:  (

  13:  indexDefrag_id    INT IDENTITY(1,1)   NOT NULL

  14:  , objectID          INT                 NOT NULL

  15:  , objectName        NVARCHAR(130)       NOT NULL

  16:  , indexID           INT                 NOT NULL

  17:  , indexName         NVARCHAR(130)       NOT NULL

  18:  , fragmentation     FLOAT               NOT NULL

  19:  , page_count        INT                 NOT NULL

  20:  , dateTimeStart     DATETIME            NOT NULL

  21:  , durationSeconds   INT                 NOT NULL

  22:  CONSTRAINT PK_indexDefragLog

  23:  PRIMARY KEY CLUSTERED (indexDefrag_id)

  24:  );

25:

  26:  PRINT 'dba_indexDefragLog Table Created';

27:

  28:  IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefragStandard_sp'),

  29:  N'IsProcedure') IS Null

  30:  BEGIN

  31:  EXECUTE ('Create Procedure dbo.dba_indexDefragStandard_sp

  32:  As Print ''Hello World!''');

  33:  RAISERROR('Procedure dba_indexDefragStandard_sp created.'

  34:  , 10, 1);

  35:  END;

  36:  Go

37:

  38:  SET ANSI_Nulls ON;

  39:  SET Ansi_Padding ON;

  40:  SET Ansi_Warnings ON;

  41:  SET ArithAbort ON;

  42:  SET Concat_Null_Yields_Null ON;

  43:  SET NOCOUNT ON;

  44:  SET Numeric_RoundAbort OFF;

  45:  SET Quoted_Identifier ON;

  46:  Go

47:

  48:  ALTER PROCEDURE dbo.dba_indexDefragStandard_sp

49:

  50:  /* Declare Parameters */

  51:  @minFragmentation     FLOAT           = 10.0

  52:  /* in percent, will not defrag if fragmentation

  53:  less than specified */

  54:  , @rebuildThreshold     FLOAT           = 30.0

  55:  /* in percent, greater than @rebuildThreshold

  56:  will result in rebuild instead of reorg */

  57:  , @executeSQL           BIT             = 1

  58:  /* 1 = execute; 0 = print command only */

  59:  , @tableName            VARCHAR(4000)   = Null

  60:  /* Option to specify a table name */

  61:  , @printCommands        BIT             = 0

  62:  /* 1 = print commands; 0 = do not print commands */

  63:  , @printFragmentation   BIT             = 0

  64:  /* 1 = print fragmentation prior to defrag;

  65:  0 = do not print */

  66:  , @defragDelay          CHAR(8)         = '00:00:05'

  67:  /* time to wait between defrag commands */

  68:  AS

  69:  /********************************************************************

  70:  Name:       dba_indexDefragStandard_sp

71:

  72:  Author:     Michelle F. Ufford

73:

  74:  Purpose:    Defrags all indexes for the current database

75:

  76:  Notes:      This script was designed for SQL Server 2005

  77:  Standard edition.

78:

  79:  CAUTION: Monitor transaction log if executing for the first time!

80:

  81:  @minFragmentation     defaulted to 10%, will not defrag if

  82:  fragmentation if less than specified.

83:

  84:  @rebuildThreshold     defaulted to 30% as recommended by

  85:  Microsoft in BOL;

  86:  > than 30% will result in rebuild instead

87:

  88:  @executeSQL           1 = execute the SQL generated by this proc;

  89:  0 = print command only

90:

  91:  @tableName            Specify if you only want to defrag indexes

  92:  for a specific table

93:

  94:  @printCommands        1 = print commands to screen;

  95:  0 = do not print commands

96:

  97:  @printFragmentation   1 = print fragmentation to screen;

  98:  0 = do not print fragmentation

99:

 100:  @defragDelay          time to wait between defrag commands;

 101:  gives the server some time to catch up

 102:

 103:  Called by:  SQL Agent Job or DBA

 104:

 105:  Date        Initials  Description

 106:  ----------------------------------------------------------------

 107:  2008-10-27  MFU       Initial Release

 108:  2008-11-17  MFU       Added page_count to log table

 109:  , added @printFragmentation option

 110:  ********************************************************************

 111:  Exec dbo.dba_indexDefragStandard_sp

 112:  @executeSQL         = 1

 113:  , @printCommands      = 1

 114:  , @minFragmentation   = 0

 115:  , @printFragmentation = 1;

 116:  ********************************************************************/

 117:

 118:  SET NOCOUNT ON;

 119:  SET XACT_Abort ON;

 120:

 121:  BEGIN

 122:

 123:  /* Declare our variables */

 124:  DECLARE   @objectID         INT

 125:  , @indexID          INT

 126:  , @schemaName       NVARCHAR(130)

 127:  , @objectName       NVARCHAR(130)

 128:  , @indexName        NVARCHAR(130)

 129:  , @fragmentation    FLOAT

 130:  , @pageCount        INT

 131:  , @sqlCommand       NVARCHAR(4000)

 132:  , @rebuildCommand   NVARCHAR(200)

 133:  , @dateTimeStart    DATETIME

 134:  , @dateTimeEnd      DATETIME

 135:  , @containsLOB      BIT;

 136:

 137:  /* Just a little validation... */

 138:  IF @minFragmentation Not Between 0.00 And 100.0

 139:  SET @minFragmentation = 10.0;

 140:

 141:  IF @rebuildThreshold Not Between 0.00 And 100.0

 142:  SET @rebuildThreshold = 30.0;

 143:

 144:  IF @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]'

 145:  SET @defragDelay = '00:00:05';

 146:

 147:  /* Determine which indexes to defrag using our

 148:  user-defined parameters */

 149:  SELECT

 150:  OBJECT_ID AS objectID

 151:  , index_id AS indexID

 152:  , avg_fragmentation_in_percent AS fragmentation

 153:  , page_count

 154:  , 0 AS 'defragStatus'

 155:  /* 0 = unprocessed, 1 = processed */

 156:  INTO #indexDefragList

 157:  FROM sys.dm_db_index_physical_stats

 158:  (DB_ID(), OBJECT_ID(@tableName), NULL , NULL, N'Limited')

 159:  WHERE avg_fragmentation_in_percent > @minFragmentation

 160:  And index_id > 0

 161:  OPTION (MaxDop 1);

 162:

 163:  /* Create a clustered index to boost performance a little */

 164:  CREATE CLUSTERED INDEX CIX_temp_indexDefragList

 165:  ON #indexDefragList(objectID, indexID);

 166:

 167:  /* Begin our loop for defragging */

 168:  WHILE (SELECT COUNT(*) FROM #indexDefragList

 169:  WHERE defragStatus = 0) > 0

 170:  BEGIN

 171:

 172:  /* Grab the most fragmented index first to defrag */

 173:  SELECT TOP 1

 174:  @objectID         = objectID

 175:  , @fragmentation    = fragmentation

 176:  , @indexID          = indexID

 177:  , @pageCount        = page_count

 178:  FROM #indexDefragList

 179:  WHERE defragStatus = 0

 180:  ORDER BY fragmentation DESC;

 181:

 182:  /* Look up index information */

 183:  SELECT @objectName = QUOTENAME(o.name)

 184:  , @schemaName = QUOTENAME(s.name)

 185:  FROM sys.objects AS o

 186:  Inner Join sys.schemas AS s

 187:  ON s.schema_id = o.schema_id

 188:  WHERE o.OBJECT_ID = @objectID;

 189:

 190:  SELECT @indexName = QUOTENAME(name)

 191:  FROM sys.indexes

 192:  WHERE OBJECT_ID = @objectID

 193:  And index_id = @indexID

 194:  And type > 0;

 195:

 196:  /* Look for LOBs */

 197:  SELECT TOP 1

 198:  @containsLOB = column_id

 199:  FROM sys.columns WITH (NOLOCK)

 200:  WHERE

 201:  [OBJECT_ID] = @objectID

 202:  And (system_type_id In (34, 35, 99)

 203:  -- 34 = image, 35 = text, 99 = ntext

 204:  Or max_length = -1);

 205:  -- varbinary(max), varchar(max), nvarchar(max), xml

 206:

 207:  /* See if we should rebuild or reorganize; handle thusly */

 208:  IF @fragmentation < @rebuildThreshold             Or IsNull(@containsLOB, 0) > 0

 209:  -- Cannot rebuild if the table has one or more LOB

 210:  SET @sqlCommand = N'Alter Index ' + @indexName + N' On '

 211:  + @schemaName + N'.' + @objectName + N' ReOrganize;'

 212:  ELSE

 213:  SET @sqlCommand = N'Alter Index ' + @indexName + N' On '

 214:  + @schemaName + N'.' + @objectName +  ' Rebuild '

 215:  + 'With (MaxDop = 1)'; -- minimize impact on server

 216:

 217:  /* Are we executing the SQL?  If so, do it */

 218:  IF @executeSQL = 1

 219:  BEGIN

 220:

 221:  /* Grab the time for logging purposes */

 222:  SET @dateTimeStart  = GETDATE();

 223:  EXECUTE (@sqlCommand);

 224:  SET @dateTimeEnd  = GETDATE();

 225:

 226:  /* Log our actions */

 227:  INSERT INTO dbo.dba_indexDefragLog

 228:  (

 229:  objectID

 230:  , objectName

 231:  , indexID

 232:  , indexName

 233:  , fragmentation

 234:  , page_count

 235:  , dateTimeStart

 236:  , durationSeconds

 237:  )

 238:  SELECT

 239:  @objectID

 240:  , @objectName

 241:  , @indexID

 242:  , @indexName

 243:  , @fragmentation

 244:  , @pageCount

 245:  , @dateTimeStart

 246:  , DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd);

 247:

 248:  /* Just a little breather for the server */

 249:  WAITFOR Delay @defragDelay;

 250:

 251:  /* Print if specified to do so */

 252:  IF @printCommands = 1

 253:  PRINT N'Executed: ' + @sqlCommand;

 254:  END

 255:  ELSE

 256:  /* Looks like we're not executing, just print

 257:  the commands */

 258:  BEGIN

 259:  IF @printCommands = 1

 260:  PRINT @sqlCommand;

 261:  END

 262:

 263:  /* Update our index defrag list when we've

 264:  finished with that index */

 265:  UPDATE #indexDefragList

 266:  SET defragStatus = 1

 267:  WHERE objectID  = @objectID

 268:  And indexID   = @indexID;

 269:

 270:  END

 271:

 272:  /* Do we want to output our fragmentation results? */

 273:  IF @printFragmentation = 1

 274:  SELECT idl.objectID

 275:  , o.name AS 'tableName'

 276:  , idl.indexID

 277:  , i.name AS 'indexName'

 278:  , idl.fragmentation

 279:  , idl.page_count

 280:  FROM #indexDefragList AS idl

 281:  JOIN sys.objects AS o

 282:  ON idl.objectID = o.OBJECT_ID

 283:  JOIN sys.indexes AS i

 284:  ON idl.objectID = i.OBJECT_ID

 285:  AND idl.indexID = i.index_id;

 286:

 287:  /* When everything is done, make sure to get rid of

 288:  our temp table */

 289:  DROP TABLE #indexDefragList;

 290:

 291:  SET NOCOUNT OFF;

 292:  RETURN 0

 293:  END

 294:  Go

C Sharp Logs

Thursday, 27 November 2008

Improve windows startup time

.NET Cheatsheets

Friday, 14 November 2008

For Mac Lovers ...

Wednesday, 12 November 2008

SQL Index Defrag

Tuesday, 11 November 2008

SQL Server Best Practices

windows server 2008 workstation

Tuesday, 4 November 2008

Google visualization API

About Me