Wednesday, July 21, 2010

Currently Executing SQL Statements

Here is a script to find out the currently executing sql statements, of course you can use the ‘sp_who2’, but I use this one very often

 

select	C.client_tcp_port, T.text
from	sys.dm_exec_connections  C
	CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) T
where	client_net_address = 'myComputerIpHere'

Friday, July 9, 2010

Find the Index fragmentation on SQL Server 2005 /2008

Here is the script to find out the average index fragmentation levels. Based on this value you can either Rebuild or Reorganize the indexes. Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

SELECT DB_NAME(PS.database_id) AS dbName,
		S.name  AS SchemaName,
		O.name AS TableName,
		b.name,
		ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id
INNER JOIN sys.objects O  ON PS.object_id = O.object_id
INNER JOIN sys.schemas S ON S.schema_id = O.schema_id 
WHERE  ps.avg_fragmentation_in_percent >= 20 -- Indexes having Fragmentation >=20
AND PS.index_type_desc IN ('CLUSTERED INDEX','NONCLUSTERED INDEX') -- Only get clustered and nonclustered indexes
AND b.is_hypothetical = 0 -- Only real indexes
AND O.type_desc = 'USER_TABLE' -- Restrict to user tables
AND PS.page_count > 8 --- ignore tables less tha 64K
ORDER BY ps.avg_fragmentation_in_percent DESC
Here is the script which can be used for Index rebuild, you can schedule this as a daily/weekly job 
 
SET NOCOUNT ON
DECLARE @dbName NVARCHAR(128)
DECLARE @SchemaName NVARCHAR(128)
DECLARE @TableName NVARCHAR(128)
DECLARE @IndexName NVARCHAR(128)
DECLARE @avg_fragmentation_in_percent FLOAT
DECLARE @nSQL NVARCHAR(4000)
DECLARE @index_list TABLE(dbName NVARCHAR(128),
						  SchemaName NVARCHAR(128),TableName NVARCHAR(128),
						  IndexName NVARCHAR(128),avg_fragmentation_in_percent FLOAT )
INSERT INTO @index_list
SELECT DB_NAME(PS.database_id) AS dbName,
		S.name  AS SchemaName,
		O.name AS TableName,
		b.name,
		ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id
INNER JOIN sys.objects O  ON PS.object_id = O.object_id
INNER JOIN sys.schemas S ON S.schema_id = O.schema_id 
WHERE  ps.avg_fragmentation_in_percent >= 20 -- Indexes having Fragmentation >=20
AND PS.index_type_desc IN ('CLUSTERED INDEX','NONCLUSTERED INDEX') -- Only get clustered and nonclustered indexes
AND b.is_hypothetical = 0 -- Only real indexes
AND O.type_desc = 'USER_TABLE' -- Restrict to user tables
AND PS.page_count > 8 --- ignore tables less tha 64K
--ORDER BY ps.avg_fragmentation_in_percent DESC
SELECT TOP 1 @dbName=dbName,@SchemaName=SchemaName,@TableName=TableName,@IndexName=IndexName,@avg_fragmentation_in_percent=avg_fragmentation_in_percent FROM @index_list
WHILE( @@rowcount <> 0 )
BEGIN
    IF @avg_fragmentation_in_percent <= 40 -- REORGANIZE
            SET @nSQL='ALTER INDEX ' + @IndexName + ' ON ' + @dbName + '.' + @SchemaName + '.' + @TableName + ' REORGANIZE;'
    ELSE -- REBUILD
            SET @nSQL='ALTER INDEX ' + @IndexName + ' ON ' + @dbName + '.' + @SchemaName + '.' + @TableName + ' REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON, ONLINE = ON, MAXDOP = 0);'
	BEGIN TRY 
		EXECUTE (@nSQL);
    END TRY 
    BEGIN CATCH 
		SET @nSQL =  REPLACE (@nSQL, 'ONLINE = ON,','')
		EXECUTE (@nSQL);
    END CATCH
    --PRINT @nSQL
    DELETE FROM @index_list WHERE dbName=@dbName AND SchemaName=@SchemaName AND TableName=@TableName AND IndexName=@IndexName AND avg_fragmentation_in_percent=@avg_fragmentation_in_percent
    SELECT TOP 1 @dbName=dbName,@SchemaName=SchemaName,@TableName=TableName,@IndexName=IndexName,@avg_fragmentation_in_percent=avg_fragmentation_in_percent FROM @index_list
END

Friday, July 2, 2010

Find table and index name for fragmented indexes

SELECT  
 OBJECT_NAME(object_id) AS tblName 
,(SELECT name FROM sys.indexes WHERE object_id = a.object_id and index_id = a.index_id) 
,avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) a
WHERE avg_fragmentation_in_percent > 30 
AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX') 
ORDER BY avg_fragmentation_in_percent DESC 

Rebuilding Indexes vs Updating Statistics

No matter in which order you perform these, here are some points to keep in mind

 

1) By default, the UPDATE STATISTICS statement uses only a sample of records of the table. Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.

2) By default, the UPDATE STATISTICS statement updates both index and column statistics. Using the COLUMNS option will update column statistics only. Using the INDEX option will update index statistics only.

3) Rebuilding an index, for example by using ALTER INDEX … REBUILD, will also update index statistics with the equivalent of using WITH FULLSCAN. Rebuilding indexes does not update column statistics.

4) Reorganizing an index, for example using ALTER INDEX … REORGANIZE, does not update any statistics.