• Vijay

    Deadlock because of Indexed Views

    • 2 Comments
    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...
  • Vijay

    SQL Server Workers never yield

    • 1 Comments
    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...
  • Vijay

    How to view the contents of SQL Server Resource Database

    • 1 Comments
    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...
  • Vijay

    SQL Server Group accounts

    • 1 Comments
    SQL Server 2005 and later versions expect the service accounts be changed using Configuration manager and not through Services.msc The configuration manager does perform other activities such as adding the service accounts to the Groups and this way...
  • Vijay

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

    • 1 Comments
    SELECT t . [text] AS [Adhoc Batch or Object Call] , SUBSTRING ( t . [text] , ( qs . [statement_start_offset] / 2 ) + 1 , (( CASE qs . [statement_end_offset] WHEN - 1 THEN DATALENGTH ( t . [text] ) ELSE qs . [statement_end_offset] END - qs...
  • Vijay

    DMV to observe wait count and wait time on Indexes

    • 1 Comments
    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...
  • Vijay

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

    • 1 Comments
    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...
  • Vijay

    Features supported by Editions of SQL Server 2005/2008

    • 1 Comments
    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...
  • Vijay

    Issue with @@SERVERNAME exceeding 30characters

    • 0 Comments
    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...
  • Vijay

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

    • 0 Comments
    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)] ...
  • Vijay

    Logical flow of a query

    • 0 Comments
    The steps below is the logical flow of how a SQL statement is processed A) Cross Join the tables. If the query involves more than a table, the first two tables are cross-joined and the result set is then joined with the third table and so on and...
  • Vijay

    Datetime Imported from external system

    • 0 Comments
    SQL Server accuracy of milliseconds is 1/300 th of a second and the possible values are .000, .003 and .007. Any datetime value in SQL Server with milliseconds will be rounded to one the nearest of these three values. The table lists all possible last...
Page 1 of 1 (12 items)