Showing posts from 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 in…

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 in…

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…

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 determi…

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_con…

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 …

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_physi…

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 STATISTI…

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

Principal Instance - Put the database you want to mirror in FULL recovery mode. For example: 1: BACKUP DATABASE AdventureWorksLT2008 2: TO DISK = 'c:\AdventureWor…
Subscribe Our Newsletter