Lock granularity

RID A row identifier used to lock a single row within a heap.
KEY A single row is locked - index key for a single row in an index.
PAGE An 8-kilobyte (KB) page in a database, such as data or index pages.
EXTENT A lock on an extent (=a contiguous group of 8 pages, such as data or index pages). Used, for example, when an ALTER INDEX REBUILD command is executed on a table and the pages of the table may be moved from an existing extent to a new extent.
HoBT It acts like a table-level lock, but on a partition instead of on the table itself.
TABLE The entire table, including all data and indexes => blocks all write requests on the entire table (including indexes), it can significantly hurt database concurrency.
FILE A database file.
APPLICATION An application-specified resource.
METADATA The table schema definition is locked.
ALLOCATION_UNIT An allocation unit.
DATABASE The entire database.

Temporary tables vs table variables in SQL Server 2012

Table variables Temporary tables (local or global)
Scope Duration of batch execution. Local temporary tables: current session, so it can be accessed only by the session that created it.
Global temporary tables: as long there is a session referencing it.
Support for SELECT INTO No Yes
Support for usage of user defined types Yes No. As alternative you can re-create the UDT in tempdb database.
Support for ALTER TABLE No. Change definition cannot be changed after table variable declaration. Yes
Supported in triggers definition Yes Yes
Supported in functions definition Yes No
TRUNCATE TABLE support Not Allowed Yes
Collation Use the collation of the current DB Use the collation of TempDB database.
Support for constraints Yes, except foreign keys. Also, named constraints are not supported. Yes, except foreign keys (they are allowed but not enforced). Also, named constraints are supported.
Support SET IDENTITY_INSERT ON/OFF No Yes
Can participate in a transaction? No Yes
Column statistics No column-level statistics. This is not an issue when the table actually contains only a small quantity of data ( less than 100 rows), otherwise cardinality estimate skews may appear. Yes
Storage
  • Both make use of tempdb system database.
  • There is only one tempdb database per SQL Server instance => high usage of temporary object can make this database to becode the central point of contention.
  • By default, there is only one data file allocated to tempdb database => can cause latch contention in case of heavy creating and deletion of small temporary objects. As a general practice, number of data files for tempdb database is:
    MIN (8, # processor logical cores).

Performance statistics for stored procedures

By looking at the results returned by dm_exec_procedure_stats DMV, we can gather info about how a stored procedure is performing:

SELECT
        DB_NAME(database_id), p.name as SPName, execution_count
        , total_physical_reads, total_physical_reads / execution_count as avg_physical_reads
        , total_logical_reads, total_logical_reads / execution_count as avg_logical_reads
        , total_elapsed_time, total_elapsed_time / execution_count as avg_elapsed_time
        , QP.query_plan
FROM
        sys.procedures P
        JOIN sys.dm_exec_procedure_stats S ON S.object_id = P.object_id
        CROSS APPLY [sys].[dm_exec_query_plan] (S.[plan_handle]) QP
WHERE
        S.database_id = DB_ID()
        AND P.is_ms_shipped = 0

List of cached query plans

dm_exec_cached_plans returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

SELECT
      CP.[objtype] -- ad hoc query, stored procedure, prepared statement, etc.
    , CP.[cacheobjtype]
    , CP.[size_in_bytes]
    , CP.[refcounts]
    , CP.[usecounts]
    , ST.[text]
    , QP.query_plan
FROM
    sys.dm_exec_cached_plans as CP
    CROSS APPLY sys.dm_exec_sql_text (CP.plan_handle) as ST
    CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
WHERE
    1 = 1
    AND ST.[text] LIKE '%<string to find>%'
    --AND CP.[objtype] NOT IN (N'Adhoc', N'Prepared')
ORDER BY
    CP.[objtype]       

When statistics really change?

Stats change:

  • When a table with no rows gets a row
  • When a table has fewer than 500 rows and is increased by 500 or more rows
  • Through SQL Server's auto-updating mechanism based on database option AUTO_UPDATE_STATISTICS (which by default is enabled and it performs synchronously), statistics get automatically changed when 500 + 20% of the table rows change. E.g. for a table with 100k rows, stats will get invalidated when 20500 rows are updated. Read more about this.
  • In addition to statistics on indexes, SQL Server can build statistics on columns with no indexes. See AUTO_CREATE_STATISTICS option.
  • If trace flag 2371 is turned on, then updates are dynamic Read more about this.
  • When you rebuild an index (equivalent to FULLSCAN).
  • Or you can decide to manually update the statistics.

Performance statistics for cached query plans

SELECT
          DB_NAME(QP.dbid)
        , ST.[text]
        , QS.[execution_count]        
        , QS.query_hash
        , QS.query_plan_hash
        , QP.query_plan
        , QS.*
FROM
        [sys].[dm_exec_query_stats] as QS
        CROSS APPLY [sys].[dm_exec_sql_text] (QS.[sql_handle]) AS ST
        CROSS APPLY [sys].[dm_exec_query_plan] (QS.[plan_handle]) AS QP
WHERE
        ST.[text] LIKE '%<part of your query here>%'
ORDER BY
        QS.[execution_count] DESC;
GO

Detecting fragmentation for all indexes in a SQL Server database

The first step in deciding which defragmentation method to use is to analyze the index to determine the degree of fragmentation. By using sys.dm_db_index_physical_stats DMV, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.

The result set returned by the sys.dm_db_index_physical_stats dynamic management function includes the following columns:
avg_fragmentation_in_percent: the percent of logical fragmentation (out-of-order pages in the index)
fragment_count: the number of fragments (physically consecutive leaf pages) in the index
avg_fragment_size_in_pages: average number of pages in one fragment in an index
avg_record_size_in_bytes: This number simply represents a useful measure for the amount of data stored within the record (index or heap).

SELECT
     OBJECT_NAME(S.OBJECT_ID) as TableName,
     I.name as IndexName,
     S.avg_fragmentation_in_percent
FROM
     sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) as S
     INNER JOIN sys.indexes AS I ON S.OBJECT_ID = I.OBJECT_ID AND S.index_id = I.index_id
WHERE
     S.database_id = DB_ID()
ORDER BY
     TableName, IndexName

After the degree of fragmentation is known, check how you can "resolve" the fragmentation.

Code template for transactions

Code template for correctly handling transactions in SQL Server:

SET XACT_ABORT ON;

BEGIN TRY
        BEGIN TRANSACTION
                -- Your script here

        COMMIT TRANSACTION
END TRY
BEGIN CATCH
        IF XACT_STATE() != 0  
                ROLLBACK TRANSACTION

        -- Re-throw error
        EXEC ThrowError
END CATCH
GO

Sample of ThrowError function:

CREATE PROCEDURE ThrowError
AS
BEGIN
   DECLARE
     @ErrorNumber INT
     , @ErrorMessage NVARCHAR(4000)
     , @ErrorSeverity INT
     , @ErrorState INT
     , @ErrorProcedure NVARCHAR(128)
     , @ErrorLine INT
     
   SELECT @ErrorNumber = ERROR_NUMBER()    
        , @ErrorMessage = ERROR_MESSAGE()
        , @ErrorSeverity = ERROR_SEVERITY()
        , @ErrorState = ERROR_STATE()
        , @ErrorProcedure = ERROR_PROCEDURE()
        , @ErrorLine = ERROR_LINE()

    SELECT @ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine
   
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState )
END
GO

UNPIVOT table example

You can use the UNPIVOT relational operator rotate columns into rows values. For example, having the below table:

We'll have the below ouput:

First, let's create a table variable and fill it with some sample rows:

DECLARE @Orders AS TABLE
     ( OrderId INT IDENTITY (1,1), CustomerId INT, YearNo SMALLINT, Jan INT, Feb INT, Mar INT)
INSERT INTO @Orders (CustomerId,YearNo, Jan, Feb, Mar)
VALUES (1, 2013, 3, 33, 333), (1, 2012, 2, 22, 222), (2, 2009, 9, 99, 999)
SELECT * FROM @Orders

Here it is a very simple usage of UNPIVOT:

SELECT CustomerId, YearNo, MonthNo, Value FROM @Orders UNPIVOT (Value FOR MonthNo IN (Jan, Feb, Mar)) AS unpvt;

How to find and replace text in all stored procedures and functions

Use SQL Search tool from RedGate, a free tool which allows you to search a specific text in procedures, functions, triggers, views, constraints, etc. The steps would be the following:

1/ Get the generated script by SQL Search
2/ Copy/paste it in SSMS and replace your string (via CTRL-H) 
3/ Validate the new script (i.e execute it between BEGIN TRAN / ROLLBACK TRAN statements)
4/ If everything is ok then you can run it in SSMS
 

Pages

Subscribe to SQLhint RSS