Wednesday, December 22, 2010

How to create a SQL trace using T-SQL

Some users want to know if there is a way to monitor events on SQL server without using SQL Profiler. Yes, there is: the engine support behind SQL Profiler is the feature called SQL Trace which is introduced in SQL 2005. SQL Trace provides a set of stored procedures to create traces on an instance of the SQL Server Database Engine. These system stored procedures can be used from within user's own applications to create traces manually, and allows to write custom applications specific to their needs.

The following sample code shows how to create customized SQL trace to monitor events to user's interest

-- sys.traces shows the existing sql traces on the server 
SELECT * FROM sys.traces 
go
--create a new trace, make sure the @tracefile must NOT exist on the disk yet 
DECLARE @tracefile NVARCHAR(500) SET @tracefile=N'c:\temp\newtraceFile'
DECLARE @trace_id INT
DECLARE @maxsize BIGINT
SET @maxsize =1
EXEC sp_trace_create @trace_id OUTPUT,2,@tracefile ,@maxsize 
go
--- add the events of insterest to be traced, and add the result columns of interest
--  Note: look up in sys.traces to find the @trace_id, here assuming this is the first trace in the server, therefor @trace_id=1
DECLARE @trace_id INT = 1, @on BIT = 1, @current_num INT
SET @current_num =1
WHILE(@current_num <65)
BEGIN
	--add events to be traced, id 14 is the login event, you add other events per your own requirements, the event id can be found @ BOL http://msdn.microsoft.com/en-us/library/ms186265.aspx
	EXEC sp_trace_setevent @trace_id,14, @current_num,@on
	SET @current_num=@current_num+1
END 
go
 
--turn on the trace: status=1
-- use sys.traces to find the @trace_id, here assuming this is the first trace in the server, so @trace_id=1 
DECLARE @trace_id INT
SET @trace_id=1
EXEC sp_trace_setstatus  @trace_id,1
 
--pivot the traced event
SELECT LoginName,DatabaseName,* FROM ::FN_TRACE_GETTABLE(N'c:\temp\newtraceFile.trc',DEFAULT)
go
 
-- stop trace. Please manually delete the trace file on the disk
-- use sys.traces to find the @trace_id, here assuming this is the first trace in the server, so @trace_id=1 
DECLARE @trace_id INT
SET @trace_id=1
EXEC sp_trace_setstatus @trace_id,0 
EXEC sp_trace_setstatus @trace_id,2 
go

Thursday, December 9, 2010

Find Best Clustered Index

The following query will compare the nonclustered indexes vs the clustered index and determine which index would qualify as the best clustered index based upon the DMV statistics.

If a clustered index does not exist on the table, it will also suggest one of the existing nonclustered indexes as the clustered index.

DECLARE @NonClusteredSeekPct float 
DECLARE @ClusteredLookupFromNCPct float 
-- Define percentage of usage the non clustered should  
-- receive over the clustered index  
SET @NonClusteredSeekPct = 1.50
 -- 150%  
-- Define the percentage of all lookups on the clustered index  
-- should be executed by this non clustered index  
SET @ClusteredLookupFromNCPct = .75 -- 75%  
SELECT  TableName = object_name(idx.object_id)
       ,NonUsefulClusteredIndex = idx.NAME
       ,ShouldBeClustered = nc.NonClusteredName
       ,Clustered_User_Seeks = c.user_seeks
       ,NonClustered_User_Seeks = nc.user_seeks
       ,Clustered_User_Lookups = c.user_lookups
       ,DatabaseName = db_name(c.database_id)
FROM    sys.indexes idx
LEFT JOIN sys.dm_db_index_usage_stats c ON idx.object_id = c.object_id
                                           AND idx.index_id = c.index_id  
--AND c.database_id = @DBID  
JOIN    ( SELECT    idx.object_id
                   ,nonclusteredname = idx.NAME
                   ,ius.user_seeks
          FROM      sys.indexes idx
          JOIN      sys.dm_db_index_usage_stats ius ON idx.object_id = ius.object_id
                                                       AND idx.index_id = ius.index_id
          WHERE     idx.type_desc = 'nonclustered'
                    AND ius.user_seeks = ( SELECT   MAX(user_seeks)
                                           FROM     sys.dm_db_index_usage_stats
                                           WHERE    object_id = ius.object_id
                                                    AND type_desc = 'nonclustered' )
          GROUP BY  idx.object_id
                   ,idx.NAME
                   ,ius.user_seeks ) nc ON nc.object_id = idx.object_id
WHERE   idx.type_desc IN ( 'clustered', 'heap' )  
-- non clustered user seeks outweigh clustered by 150%  
        AND nc.user_seeks > ( c.user_seeks * @NonClusteredSeekPct )  
-- nc index usage is primary cause of clustered lookups 80%  
        AND nc.user_seeks >= ( c.user_lookups * @ClusteredLookupFromNCPct )
ORDER BY nc.user_seeks DESC 

Wednesday, December 8, 2010

Find Table without Clustered Index / Primary Key

One of the basic Database Rule I have is that all the table must Clustered Index. Clustered Index speeds up performance of the query ran on that table. Clustered Index are usually Primary Key but not necessarily.

SELECT OBJECT_NAME(i.OBJECT_ID) AS tableName, rows
FROM sys.indexes i
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE i.INDEX_ID = 0
AND OBJECTPROPERTY(i.OBJECT_ID,'IsUserTable') = 1
ORDER BY 2 DESC 
 

Using TRY...CATCH to Rollback a Transaction in case of an error

As you all might know, one of the downsides of the @@ERROR variable approach  to catch the  error is that we must check the value of this variable after each and every DML /DDL  statement to determine if an error occurred and, if so, to rollback the transaction. With SQL Server 2005's TRY...CATCH block, however, these types of scripts are greatly simplified.

 

BEGIN TRAN
BEGIN TRY
    SELECT  1 / 0 infinite
    COMMIT TRAN
END TRY
BEGIN CATCH
    RAISERROR ( 'err', 16, 1 )
    SELECT  @@trancount before
    ROLLBACK TRAN
    SELECT  @@trancount after
END CATCH
GO 


Does the order in which we put BEGIN TRAN..BEGIN TRY matters , let us check



 



BEGIN TRY 
BEGIN TRAN 
    SELECT  1 / 0 infinite
COMMIT TRAN
END TRY
BEGIN CATCH
    RAISERROR ( 'err', 16, 1 )
    SELECT  @@trancount before
    ROLLBACK TRAN
    SELECT  @@trancount after
END CATCH
GO 	


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.

Tuesday, June 29, 2010

How to Setup High Performance (asynchronous) Mirroring with No Witness

 

  1. Principal Instance - Put the database you want to mirror in FULL recovery mode.
    For example:
       1:  BACKUP DATABASE AdventureWorksLT2008 


       2:  TO DISK = 'c:\AdventureWorksLT2008.bak'


       3:  


       4:  BACKUP LOG AdventureWorksLT2008 


       5:  TO DISK = 'c:\AdventureWorksLT2008.trn'



  2. Copy the backups you made on the Principal Instance to the Mirror Instance


  3. Mirror Instance - Restore the database and log with the NORECOVERY option
       1:  RESTORE Database AdventureWorksLT2008 


       2:  FROM DISK = 'c:\AdventureWorksLT2008.bak'


       3:      WITH MOVE 'AdventureWorksLT2008_Data' TO 'E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT2008.mdf',


       4:      MOVE 'AdventureWorksLT2008_Log' TO 'E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT2008.ldf',


       5:  NORECOVERY


       6:  


       7:  RESTORE LOG AdventureWorksLT2008


       8:      FROM DISK = 'c:\AdventureWorksLT2008.trn' 


       9:  WITH NORECOVERY



  4. On the Principal Instance right click on the database you are going to mirror and select Tasks > Mirror...


  5. Click on the Configure Security... button. This will launch the Configure Database Mirroring Security Wizard.


  6. Click the Next button on the initial screen. On the Include Witness Server dialog select No


  7. On the Principal Server Instance dialog leave the default and click Next


  8. On the Mirror Server Instance dialog enter in the name of the server where you will be mirroring to. Click the Connect... button. Leave the port and endpoint name with the defaults and click on the Next button.


  9. Enter in the names of the accounts used to mirror the data. Since we have a domain I would recommend leaving these boxes empty. Click the Finish button.


  10. If everything goes well you should see a dialog box documenting the different endpoints and mirroring configuration. Click on the Do Not Start Mirroring Button. This way we can review the settings before we start mirroring.


  11. Change the operating mode to High performance (asynchronous)


  12. Click on the Start Mirroring Button. After a few moments you should see the status change to "Synchronized"