SQLServer : Script to check and create missing multi-column stats on a table

 

Statistics are critical for SQL Server performance. By default, SQL Server creates statistics for columns  However, SQL Server isn’t yet quite smart enough to automatically create multi-column stats objects that might be helpful. 

So the below script compares the details provided by the missing index DMVs against the existing sys.stats and sys.stats_columns views to provide a list of potential new stats.

;WITH suggested_indexes AS

(

    SELECT ObjectName = QUOTENAME(s.name) + N'.' + QUOTENAME(o.name)

        , Columns = STUFF((

            SELECT N', ' + QUOTENAME(mic.column_name)

            FROM sys.dm_db_missing_index_columns(mig.index_handle) mic 

            WHERE mic.column_usage = N'EQUALITY' 

                OR mic.column_usage = N'INEQUALITY' 

            ORDER BY mic.column_name FOR XML PATH(N'')

            ), 1, 2, '')

    FROM sys.dm_db_missing_index_groups mig

        INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

        INNER JOIN sys.objects o ON mid.object_id = o.object_id

        INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

    WHERE mid.database_id = DB_ID()    

)

, existing_stats AS

(

SELECT ObjectName = QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name)

    , StatsName = s.name

    , AutoCreated = s.auto_created

    , UserCreated = s.user_created

    , StatsColumns = STUFF((SELECT N', ' + QUOTENAME(c.name)

        FROM sys.stats_columns sc 

            INNER JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id

        WHERE s.object_id = sc.object_id AND s.stats_id = sc.stats_id 

        ORDER BY sc.stats_column_id

        FOR XML PATH(N'')

        ), 1, 2, N'')

    , FilterDefinition = CASE WHEN s.has_filter = 1 THEN s.filter_definition ELSE N'' END

FROM sys.stats s

    INNER JOIN sys.objects o ON s.object_id = o.object_id

    INNER JOIN sys.schemas sch ON o.schema_id = sch.schema_id

WHERE o.is_ms_shipped = 0

)

SELECT *

    , CreateStmt = N'CREATE STATISTICS [stats_name_' 

       + CONVERT(nvarchar(30), CONVERT(bigint, CRYPT_GEN_RANDOM(4))) 

       + N'] ON ' + si.ObjectName + N'(' + si.Columns + N') WITH FULLSCAN;'

FROM suggested_indexes si

WHERE NOT EXISTS (SELECT 1

    FROM existing_stats es

    WHERE es.ObjectName = si.ObjectName

        AND es.StatsColumns = si.Columns

    )

GROUP BY si.ObjectName

    , si.Columns

ORDER BY si.ObjectName

    , si.Columns;

SQLServer: Query Plan with missing index details

 

Run the following query to identify queries that cause high CPU usage and that contain at least one missing index in the query plan

SELECT

    qs_cpu.total_worker_time / 1000 AS total_cpu_time_ms,

    q.[text],

    p.query_plan,

    qs_cpu.execution_count,

    q.dbid,

    q.objectid,

    q.encrypted AS text_encrypted

FROM

    (SELECT TOP 500 qs.plan_handle,

     qs.total_worker_time,

     qs.execution_count FROM sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS qs_cpu

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q

CROSS APPLY sys.dm_exec_query_plan(plan_handle) p

WHERE p.query_plan.exist('declare namespace 

        qplan = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";

        //qplan:MissingIndexes')=1

SQLServer: Find Top CPU and IO intensive queries

SELECT TOP 50

[Avg. MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000 / qs.execution_count,

[Total MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000,

[Avg. Elapsed Time(sec)] = qs.total_elapsed_time / 1000000 / qs.execution_count,

[Total Elapsed Time(sec)] = qs.total_elapsed_time / 1000000,

qs.execution_count,

[Avg. I/O] = (total_logical_reads + total_logical_writes) / qs.execution_count,

[Total I/O] = total_logical_reads + total_logical_writes,

Query = SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1,

(

(

CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.[text])

ELSE qs.statement_end_offset

END - qs.statement_start_offset

) / 2

) + 1

),

Batch = qt.[text],

[DB] = DB_NAME(qt.[dbid]),

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

where qs.execution_count > 5 --more than 5 occurences

ORDER BY [Total MultiCore/CPU time(sec)] DESC

SQLServer -: Find Top CPU Intensive Queries

Queries that are currently in cache that are consuming more CPU

;WITH eqs

AS (

    SELECT 

         [execution_count]

        ,[total_worker_time]/1000  AS [TotalCPUTime_ms]

        ,[total_elapsed_time]/1000  AS [TotalDuration_ms]

        ,query_hash

        ,plan_handle

        ,[sql_handle]

    FROM sys.dm_exec_query_stats

    )

SELECT TOP 10 est.[text], eqp.query_plan AS SQLStatement

    ,eqs.*

FROM eqs

OUTER APPLY sys.dm_exec_query_plan(eqs.plan_handle) eqp

OUTER APPLY sys.dm_exec_sql_text(eqs.sql_handle) AS est

ORDER BY [TotalCPUTime_ms] DESC

Queries with average CPU usage 

select query_stats.query_hash,

SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) as avgCPU_USAGE,

min(query_stats.statement_text) as QUERY

from (

select qs.*,

SUBSTRING(st.text,(qs.statement_start_offset/2)+1,

((case statement_end_offset

when -1 then DATALENGTH(st.text)

else qs.statement_end_offset end

- qs.statement_start_offset)/2) +1) as statement_text

from sys.dm_exec_query_stats as qs

cross apply sys.dm_exec_sql_text(qs.sql_handle) as st 

) as query_stats

group by query_stats.query_hash

order by 2 desc;

SQLServer - Always On Availability Group TCP Port and Endpoint Conflicts

 

              Always On Availability Group and SQL Server FCI can have below port conflicts 

Having an availability group with a listener that listen on the same TCP port than the standalone instance on the same process will not result to a TCP port conflict.

Having an availability group with a listener that listen on the same TCP port than the standalone instance on a different process will result to a TCP port conflict. In this case each SQL Server process will attempt to open a socket on the same TCP port and on the same address IP.

Having Multiple availability group  running on the same TCP endpoint port is not a problem because they can open a socket on their distinct IP address. However you can face port conflicts in the case you have multiple  instances on the same node using the same endpoint port ,So in that case you need to change the TCP endpoint port .

Having several SQL Server FCI that listen on the same port is not a problem because they can open a socket on their distinct IP address. However you can face port conflicts in the case you have also a standalone instance installed on one of the cluster node

Always On Secondary Replica in a disconnected state where we have Multiple Availability Groups on the same SQLServer Instance

Always On Secondary Replica is showing as disconnected state where we have multiple Availability group  on the same SQL Server instance .

Scenario:-

we have 2 Availability Group running on  2 servers. First AG is working fine as expected . 

But secondary replica on the 2nd AG is showing as disconnected state  and databases are not in sync .

Resolution:-

when we checked  and understand  that the endpoint URL of the secondary AG replica  is configured with wrong TCP port , 

TCP://DBSERVER1.manufacturing.Adventure-Works.com:5022

TCP://DBSERVER2.manufacturing.Adventure-Works.com:5023

After updating the correct port on the endpoint URL , secondary replica came online and starts synchronizing 

It should be as below ,

TCP://DBSERVER1.manufacturing.Adventure-Works.com:5022

TCP://DBSERVER2.manufacturing.Adventure-Works.com:5022

Cause:-

1) We can not have multiple endpoint on the SQL Server Instance 

2) Each port number must be associated with only one endpoint, and each endpoint is associated with a single server instance; thus, different server instances on the same server listen on different endpoints with different ports. Therefore, the port you specify in the endpoint URL when you specify an availability replica will always direct incoming messages to the server instance whose endpoint is associated with that port.

3)We can use the same port on multiple AG's  on the same server since each listener is associated with an IP address and it will use the same TCP socket 

4) If you are using multiple instance on the same server then we need to have different endpoint created with different port  .Hence the endpoint URL also will change for the named instance .