performance

Performance Monitor counters to analyze memory pressure

Counter Description Expected Value
Memory
Available Bytes Free physical memory Should not be too low
Pages/sec Rate of hard page faults.

A page fault occurs when a process requires data that is not in its space in physical memory. If the faulted page is found elsewhere in physical memory, then it is called a soft page fault. A hard page fault occurs when a process requires data that must be retrieved from disk.
The speed of a disk access is in the order of milliseconds, whereas a memory access is in the order of nanoseconds.

Average Value < 50
Page Faults/sec Rate of total page faults (soft page faults plus hard page faults) Compare with its baseline value for trend analysis
SQLServer:Buffer Manager
Buffer cache hit ratio Percentage of requests served out of buffer cache.
A low value indicates that few requests could be served out of the buffer cache, with the rest of the requests being served from disk.
Average Value >= 90% in an OLTP system.
Page Life Expectancy Time page spends in buffer cache without being referenced. A low value means that pages are being removed from the buffer, lowering the efficiency of the cache and indicating the possibility of memory pressure.
Checkpoint Pages/sec Pages written to disk by a checkpoint operation.
A dirty page is one that is modified while in the buffer. When it’s modified, it’s marked as dirty and will get written back to the disk during the next checkpoint.
Average Value < 30
Lazy writes/sec Dirty aged pages flushed from buffer. Average Value < 20
SQLServer:Memory Manager
Memory Grants Pending Number of processes waiting for memory grant. If this counter value is high, then SQL Server is short of memory.
See sys.dm_ exec_query_memory_grants dmv.
Average value = 0.
Target Server Memory (KB) Indicates the total amount of dynamic memory SQL Server is willing to consume. Close to size of physical Memory
Total Server Memory (KB) Amount of physical memory currently assigned to SQL. Close to Target server Memory (KB)
Process
Private Bytes Size, in bytes, of memory that this process has allocated that can’t be shared with other processes

When you get out-of-memory issues, they are recorded in sys.dm_os_ring_buffers dmv:

SELECT *
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type='RING_BUFFER_OOM';

Improve performance using filtered indexes

A filtered index is a non-clustered index that contains only a subset of the number of rows contained in a table. You add a WHERE clause to reduce the number of rows that are stored at the leaf level.

CREATE NONCLUSTERED INDEX <Index_Name>
ON <Table Name>(<Column(s)>)
WHERE <Filter Condition>

A good real example it is use is on Status column, as you might have a large number of rows with Closed status that are not part of your current searches (the business wants to query for Open, Processing, Invoicing, etc)

CREATE NONCLUSTERED INDEX [IX_Orders_IdStatus_Filterered]
ON [dbo].[Orders] ([IdStatus])
WHERE ([IdStatus] IN ('O', 'P', 'I'))

Another well-suited scenarios is where you frequently have to filter out NULL values.

Pros:

  • By having fewer rows in an index, less I/O is done when that index is used.
  • Index size is dramatically reduced.
  • The statistics on these filtered indexes are filtered as well, which typically results in them being more accurate.
  • Cons:

  • Use certain expressions, such as BETWEEN, NOT IN, or a CASE statement.
  • Parameterized queries doesn't take advantage of filtered indexes.
  • Index design recommendation - how to choose optimal indexes

    Indexes should be considered on all columns that are frequently accessed by the WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.

    Clustered index recommendations
    - It is highly recommended that every table should have at least a clustered index. However, there is also a very interesting topic to be taken into account on use-the-index-luke.com.
    - Consider having a clustered indexes when the data retrieval needs to be sorted.
    - Avoid creating a clustered index on columns that are highly updatable as row locator of all the non clustered indexes will be updated accordingly.
    - Create the clustered index first. In case the clustered index is created later => all non clustered indexes need to be rebuilt.
    - Keep clustered indexes narrow as this directly impacts index size on disk. This is very important as all non clustered indexes store the clustered keys as their row locator, or simpler said:
    clustered index row width = non clustered index column width + clustered index column width

    Nonclustered Index Recommendations
    - A nonclustered index is most useful when all you want to do is retrieve a small number of rows and columns from a large table.
    - Consider using INCLUDE clause in order to create covering indexes.
    - To improve the performance of a query, SQL Server can use multiple indexes on a table. Therefore, instead of creating wide index keys, consider creating multiple narrow indexes.
    - Foreign keys columns are good index candidates.

    Disk consideration
    - Place the table and index on separate disks.
    - Take into account index compression i.e. fewer pages and fewer index levels are needed to store the index.

    Column order matters in a composite index
    Using the most selective column first will help filter the index rows more efficiently.

    Avoid indexes on columns with lower selectivity
    If selectivity ratio > 0.85 => a table scan will be preferred.

    Take into account column uniqueness
    Creating an index on columns with a very low range of possible unique values (such as gender) will not benefit performance, because the query optimizer will not be able to use the index to effectively narrow down the rows to be returned.

    The data type of an index matters
    Unless they are absolutely necessary, minimize the use of wide data type columns with large sizes in an index. A large index key size increases the number of index pages, thereby increasing the amount of memory and disk activities required for the index.

    Indexed or materialized views
    - A database view can be materialized on the disk by creating a unique clustered index on the view. After a unique clustered index is created on the view, the view’s result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing costly operations during query execution.
    - Aggregations can be precomputed and persisted in the indexed view to minimize expensive computations during query execution.
    - Available only in Enterprise Edition.

    Other aspects to be taken into account
    - Considering using the Database Engine Tuning Advisor tool provided by SQL Server that helps you determine the correct indexes in a database for a given SQL workload ( a trace file or a table or, new with SQL Server 2012, you can use the queries that exist in the plan cache).
    - ColumnStore indexes. Used to index information by columns rather than by rows.
    - Spatial indexes.
    - XML indexes.
    - Index computed columns.
    - Consider using filtered indexes.
    - Resolve Key or RID lookups by using a clustered index or by using a covering index or by using a index join (i.e. an index intersection between two or more indexes to fully cover a query).

    Performance troubleshooting using Waits

    WITH [Waits]
    AS
    (SELECT
            [wait_type],
            [wait_time_ms] / 1000.0 AS [WaitS],
            ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
            [signal_wait_time_ms] / 1000.0 AS [SignalS],
            [waiting_tasks_count] AS [WaitCount],
            100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
            ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
            N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
            N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
            N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
            N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
            N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
            N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
            N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
            N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
            N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
            N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
            N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
            N'DIRTY_PAGE_POLL')
    )
    SELECT TOP 10
            W1.[wait_type] AS [WaitType],
            CAST (W1.[WaitS] AS DECIMAL(14, 2)) AS [Wait (seconds)],
            CAST (W1.[ResourceS] AS DECIMAL(14, 2)) AS [Resource wait (seconds)],
            CAST (W1.[SignalS] AS DECIMAL(14, 2)) AS [Signal wait (seconds)],
            W1.[WaitCount],
            CAST (W1.[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
            CAST ((W1.[WaitS] / W1.[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait (seconds)],
            CAST ((W1.[ResourceS] / W1.[WaitCount]) AS DECIMAL (14, 4)) AS [AvgResource (seconds)],
            CAST ((W1.[SignalS] / W1.[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSignal (seconds)]
    FROM
            [Waits] AS W1
            INNER JOIN [Waits] AS W2 ON W2.[RowNum] <= W1.[RowNum]
    GROUP BY W1.[RowNum], W1.[wait_type], W1.[WaitS],
            W1.[ResourceS], W1.[SignalS], W1.[WaitCount], W1.[Percentage];
    GO

    Find out waiting tasks - sys.dm_os_waiting_tasks

    sys.dm_os_waiting_tasks dmv shows all threads that are currently suspended:

    SELECT
            DB_NAME(ER.database_id) DatabaseName,
            OT.[session_id],
            OT.[exec_context_id],
            OT.[wait_duration_ms],
            OT.[wait_type],
            OT.[blocking_session_id],
            OT.[resource_description],
            ES.[program_name],
            ST.[text],
            ES.[cpu_time],
            ES.[memory_usage],
            QP.[query_plan]
    FROM
            sys.dm_os_waiting_tasks OT
            INNER JOIN sys.dm_exec_sessions ES ON OT.[session_id] = ES.[session_id]
            INNER JOIN sys.dm_exec_requests ER ON ES.[session_id] = ER.[session_id]
            OUTER APPLY sys.dm_exec_sql_text (ER.[sql_handle]) ST
            OUTER APPLY sys.dm_exec_query_plan (ER.[plan_handle]) QP
    WHERE ES.[is_user_process] = 1
    ORDER BY OT.[session_id], OT.[exec_context_id];
    GO

    How to clear wait statistics

    You may need to clear the aggregated wait statistics (available via sys.dm_os_wait_statistics DMV) in order to measure the effect of a workload change against previous wait statistics.

    This can be done at any time by running the below statement:

    DBCC SQLPERF (N'sys.dm_os_wait_statistics', CLEAR)

    Deadlock victim selection

    The deadlock victim is chosen based on the following criteria:

    • The DEADLOCK_PRIORITY of the two sessions is compared and the lowest priority session is selected as the victim.
    • If both sessions have the same DEADLOCK_PRIORITY value, the transaction that is the least expensive to rollback (based on the log records that have been generated) is selected as the victim.

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

    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;

    Pages

    Subscribe to RSS - performance