sql-server-2012

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).

View state of plan cache - verifying for Plan Cache Pollution

SELECT
        CP.[objtype] as [Cache Type]
        , COUNT (*) as [# Plans]
        , SUM (CASE WHEN CP.[usecounts] = 1  THEN 1 ELSE 0 END) as [# Plans - UseCount1]        
        , SUM (CAST(size_in_bytes as DECIMAL)) / 1024 / 1024 as [Space_MB)]
        , SUM (CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END ) /1024/1024  as [Space_MB_UseCount_1]
        , AVG (CP.[usecounts]) as [Avg Use Counts]
FROM [sys].[dm_exec_cached_plans] CP
GROUP BY CP.[objtype]
ORDER BY [Space_MB_UseCount_1] DESC;

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

How to delete or invalidate a query plan

Reboot / Restart
When SQL Server is rebooted, or the service is stopped and restarted, any DMV data is lost.

Flushed Cache
The hard way, delete all query plans from cache:

-- Never execute this on your production server
DBCC FREEPROCCACHE

or you can target specific plans:
DBCC FREEPROCCACHE(plan_handle)

Memory pressures
Keeping a large number of cached plans can quickly fill the available memory, especially in systems that create lots of SQL queries that differ subtly (for example, having different environment settings), resulting in separate cached plans for each one.
SQL Server manages the size of the procedure cache by maintaining the age of the execution plans in the cache. If a stored procedure is not re-executed for a long time, the age field of the execution plan can come down to 0, and the plan can be removed from the cache when SQL Server observes a pressure on its memory.

Recompiling
Recompile all queries related to a specific table or view:

EXEC sp_recompile <tablename>

Delete all query plans associated with a stored procedure.

-- Manually mark a stored procedure for recompilation, so it will be recompiled next time it is executed.
EXEC sp_recompile <procname>

Or you can enforce a query recompilation each time, then add a query hint to the end of the query:

OPTION (RECOMPILE)

Add the WITH RECOMPILE option when a stored proc is created:

CREATE PROCEDURE <ProcedureName>
WITH RECOMPILE
AS
...

When statistics are updated
More details about when statistics change.

Simple parametrization
Setting configuration option optimize for ad hoc workloads to 1, will store only query_hash into the cache and and on second execution (if it happens), the plan itself will be placed in cache.

Forced parametrization
Parametrize all queries like in case of simple parametrization

ALTER DATABASE <dbname> SET PARAMETRIZATION FORCED;

How to rename a database even it is in use - "The database could not be exclusively locked to perform the operation"

USE master
GO

ALTER DATABASE <oldname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE <oldname>
MODIFY Name = <newname>
GO

ALTER DATABASE <newname> SET MULTI_USER
GO

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.

Get statistics info about an index

DBCC SHOW_STATISTICS returns info about 3 components:
- header
- density vector
- histogram = is a statistical construct that shows how often data falls into varying categories. The histogram stored by SQL Server consists of a sampling of data distribution for a column or an index key of up to 200 rows.

DBCC SHOW_STATISTICS('<tablename>', '<indexname>');

You can limit the statistics information displayed by using WITH HISTOGRAM clause:

DBCC SHOW_STATISTICS('<tablename>', '<indexname>')
WITH HISTOGRAM;

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

How to restore a SQL Server database even if it is in use

You can restore a SQL Server database that by executing the following script that kills all active connections:

USE master
ALTER DATABASE <databasename> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- execute restore operation here

ALTER DATABASE <databasename> SET MULTI_USER

Pages

Subscribe to RSS - sql-server-2012