SQL Server Workers never yield

SQL Server scheduler/worker is dsigned such that they are Cooperative and the scheduler expects that the workers yield cooperative. However, the API calls such as External modules, Extended procedures etc. may never yield and as a result the worker will be stuck doing an activity. Error 17883 is reported if the Worker does not yield…


Deadlock because of Indexed Views

Indexed views can cause deadlock when two or more of the participating tables (in the indexed view) is updated/inserted/deleted from two or more sessions in parallel such that they block each other causing a deadlock scenario. The deadlock scenario in this case cannot be completely eliminated, however, the impact/cause can be minimized. I have explained…


Features supported by Editions of SQL Server 2005/2008

Refer the link for SQL Server 2005 features supported by each of the edtiion  http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx   For SQL Server 2008 features supported by each of the edtiion http://msdn.microsoft.com/en-us/library/cc645993.aspx


Query fn_dblog() for the list of Operation in active transaction of the current session

declare @xactid bigint declare @transactionid nvarchar(28) select @xactid = transaction_id from sys.dm_tran_current_transaction select @transactionid = [transaction id] from fn_dblog(null,null) where [Xact ID] = @xactid select * from fn_dblog(null,null) where [Transaction ID] = @transactionid   The [Xact ID] introduced in SQL Server 2008 has the transaction id for the operation LOP_BEGIN_XACT and all the transaction occuring…


DMV to observe wait count and wait time on Indexes

select  database_id         ,object_id         ,index_id         ,partition_number         ,leaf_insert_count         ,leaf_delete_count         ,leaf_update_count         ,leaf_ghost_count         ,nonleaf_insert_count         ,nonleaf_delete_count         ,nonleaf_update_count         ,leaf_allocation_count         ,nonleaf_allocation_count         ,leaf_page_merge_count         ,nonleaf_page_merge_count         ,range_scan_count         ,singleton_lookup_count         ,forwarded_fetch_count         ,lob_fetch_in_pages         ,lob_fetch_in_bytes         ,lob_orphan_create_count         ,lob_orphan_insert_count         ,row_overflow_fetch_in_pages         ,row_overflow_fetch_in_bytes         ,column_value_push_off_row_count         ,column_value_pull_in_row_count         ,row_lock_count        …


List all the statements in the Plan Cache along with the counts and CPU usage

SELECT t. AS [Adhoc Batch or Object Call], SUBSTRING(t., (qs.[statement_start_offset]/2) + 1, ((CASE qs.[statement_end_offset] WHEN -1 THEN DATALENGTH(t.) ELSE qs.[statement_end_offset] END – qs.[statement_start_offset])/2) + 1) AS [Executed Statement] , qs.[execution_count] AS [Counts] , qs.[total_worker_time] AS [Total Worker Time], (qs.[total_worker_time] / qs.[execution_count]) AS [Avg Worker Time] , qs.[total_physical_reads] AS [Total Physical Reads], (qs.[total_physical_reads] / qs.[execution_count]) AS…


Query to list all the databases in the order of CPU usage

USE master SELECT a.[value] AS [dbid] , ISNULL(DB_NAME(CONVERT(INT,a.[value])),’Resource’) AS [DB Name] , SUM(qs.[execution_count]) AS [Counts] , SUM(qs.[total_worker_time]) / 1000 AS [Total Worker Time (mSecs)] , SUM(qs.[total_physical_reads]) AS [Total Physical Reads] , SUM(qs.[total_logical_writes]) AS [Total Logical Writes] , SUM(qs.[total_logical_reads]) AS [Total Logical Reads] , SUM(qs.[total_clr_time]) / 1000 AS [Total CLR Time (mSecs)] , SUM(qs.[total_elapsed_time]) / 1000…