Find missing indexes using DMVs

The following query determines which are the missing indexes and displays their column details based on dm_db_missing_index_group_stats DMV.

USE <DatabaseName>;

SELECT 
        REPLACE(ID.[Statement], '[' + DB_NAME(ID.database_id) + '].', '') as table_name,
        CAST(S.avg_total_user_cost * S.avg_user_impact * S.user_seeks as INT) as [score],
        ID.equality_columns, ID.inequality_columns, ID.included_columns,               
        'CREATE INDEX '
        + 'IX_' + OBJECT_NAME(ID.object_id) + '_' + CAST(ID.index_handle as VARCHAR)
        + ' ON ' + REPLACE(ID.[Statement], '[' + DB_NAME(ID.database_id) + '].', '')
        + ' (' + ISNULL (ID.equality_columns,'')
        + CASE
                WHEN ID.equality_columns IS NOT NULL AND ID.inequality_columns IS NOT NULL
                THEN ', ' + ID.inequality_columns
                ELSE ''
          END
        + ')'
        + ISNULL (' INCLUDE (' + ID.included_columns + ')', '') AS create_index_statement
FROM
        sys.dm_db_missing_index_details ID
        INNER JOIN sys.dm_db_missing_index_groups G
                ON ID.index_handle = G.index_handle
        INNER JOIN sys.dm_db_missing_index_group_stats S
                ON G.index_group_handle = S.group_handle
WHERE
        ID.database_id = DB_ID()
        AND OBJECTPROPERTY(ID.[object_id], 'IsMsShipped') = 0  
ORDER BY score DESC;

Tags:

Performance Monitor counters to analyze network bottleneck

Object Counter Description Expected Value
Network Interface (Network card) Bytes Total/sec Rate at which bytes are transferred on the network adapter. Compare this value with that reported by the Network Interface\Current Bandwidth performance counter, which reflects each adapter’s bandwidth. Average value < 50% of NIC capacity
Network Segment % Net Utilization Percentage of network bandwidth in use on a network segment Average value < 80% of network bandwidth

Tags:

Performance Monitor counters to analyze processor bottleneck

Counter Description Expected Value
Processor (_Total)%
Processor Time Percentage of time processor was busy Average value < 80%
% Privileged Time percentage of processor time spent in privileged mode Average value < 10%
System
Processor Queue Length Number of requests outstanding on the processor Average value < 2
Context Switches/sec Rate at which processor is switched per processor from one thread to another Average value < 2,000
SQL Server:SQL Statistics
Batch Requests/sec SQL command batches received per second Based on your standard workload
SQL Compilations/sec Number of times SQL is compiled Based on your standard workload
SQL Recompilations/sec Number of Recompiles

Tags:

Performance Monitor counters to analyze disk bottleneck

Counter Description Expected Value
% Disk Time Percentage of time disk was busy with read/write activities. Average value < 85%
Current Disk Queue Length Number of outstanding disk requests at the time performance data is collected Average value < 2 per disk
Avg. Disk Queue Length Average number of queued disk requests during the sample interval Average value < 2 per disk
Disk Transfers/sec Rate of read/write operations on disk Maximum value < 400 per disk
Disk Bytes/sec The rate at which bytes are transferred to or from the disk during read or write operations.
If the amount of data transfer exceeds the capacity of the disk subsystem, then a backlog starts developing on the disk subsystem, as reflected by the Disk Queue Length counter.		 Maximum value < 800MB per second
Avg. Disk Sec/Read Average time in ms to read from disk Average value < 10 ms
Avg. Disk Sec/Write Average time in ms to write to disk Average value < 10 ms

Tags:

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';

Tags:

Get database restore history

Returns last 10 restores performed on your server:

SELECT TOP 10
        RH.destination_database_name AS [Database],
        RH.user_name AS [Restored By],
        RH.restore_date AS [Restore Started],
        BMF.physical_device_name AS [Restored From],
        RF.destination_phys_name AS [Restored To],
        RH.*
FROM
        msdb.dbo.restorehistory RH
        INNER JOIN msdb.dbo.backupset BS ON RH.backup_set_id = BS.backup_set_id
        INNER JOIN msdb.dbo.restorefile RF ON RH.restore_history_id = RF.restore_history_id
        INNER JOIN msdb.dbo.backupmediafamily BMF ON BMF.media_set_id = BS.media_set_id
--WHERE destination_database_name = '<DatabaseName>'
ORDER BY RH.restore_history_id DESC
GO

Tags:

Execute a SSIS package using T-SQL

DECLARE @execution_id BIGINT;
DECLARE @use32bitruntime BIT = CAST(0 AS BIT);

-- A new execution operation is created.
EXEC catalog.create_execution
@folder_name = N'<FolderName>',
@project_name = N'<ProjectName>',
@package_name = N'<PackageName.dtsx>',
@use32bitruntime = @use32bitruntime,
@reference_id = NULL,
@execution_id = @execution_id OUTPUT;

-- Set execution properties
EXEC catalog.set_execution_parameter_value
@execution_id,
@object_type = 50,
@parameter_name = N'LOGGING_LEVEL',
@parameter_value = 1;

-- Execution is started asynchronous
EXEC catalog.start_execution @execution_id;
GO

Tags:

Support for simplified pagination using OFFSET-FETCH filter

Starting with SQL Server 2012.

SELECT orderId, orderDate, customerId, empId
FROM sales.orders
ORDER BY orderDate
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

Tags:

Compute the last day of the year

You bring the last day of a year (let's say December 31th 1900) and then you add it the number of years passed:

DECLARE @dt AS DATETIME = SYSDATETIME();
SELECT DATEADD(year, DATEDIFF(year,'19001231', @dt), '19001231');
GO

Tags:

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.

    • Tags:

    Pages