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…


Issue with @@SERVERNAME exceeding 30characters

SQL Server 2005 SP2 has an issue with modifying Agent Job’s through Management Studio if the @@servername exceeds 30characters For more info on the issue refer http://support.microsoft.com/kb/940269 If you do not wish to apply a hotfix or upgrade to later service packs. You can also decrease the size of  the server name if standalone and use SP_DROPServer  to remove…


How to view the contents of SQL Server Resource Database

To be able to view the SQL Server Resource Database  A) SQL Server must be started in a Single user Mode       Start with -m flag in configuration manager  Even if the Server is started in Single user mode, the database will still not be visible in sys.databases view Open the database with the use command USE mssqlsystemresource…