.::: Top 50 CPU, Disk IO Consuming Queries, Find Slow SQL Queries & History Query :::.

  -- >> query 11 sdh di coba . Top 50 CPU Consuming Queries in SQL Server

Select
     @@SERVERNAME as ServerName,
     @@servicename as ServiceName,
     DB_NAME(st.dbid) AS [Database Name],
     st.[text] AS [Query Text],          
     wt.last_execution_time AS [Last Execution Time],
     wt.execution_count AS [Execution Count],
     wt.total_worker_time/1000000 AS [Total CPU Time(second)],
     wt.total_worker_time/wt.execution_count/1000 AS [Average CPU Time(milisecond)],
     qp.query_plan

 

.::: Get CPU Utilization History for last 60 minutes,Get I/O Usage,Memory usage by Database,Memory Usage Query SQL Server :::.

-- Query Get CPU Utilization History for last 60 minutes SQL Server 

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

SELECT TOP(60) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
               SystemIdle AS [System Idle Process],
               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] , @@SERVERNAME as ServerName,@@servicename as ServiceName
FROM (
      SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
            AS [SystemIdle],
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
            'int')
            AS [SQLProcessUtilization], [timestamp]
      FROM (
            SELECT [timestamp], CONVERT(xml, record) AS [record]
            FROM sys.dm_os_ring_buffers
            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE '%<SystemHealth>%') AS x
      ) AS y
ORDER BY record_id DESC;

.::: Top 10 Query SQL Server base on Top 10 Total CPU, Average CPU, Expensive Query, Execution count :::.

TOTAL_WORKER_TIME = in mocrosecond https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?view=sql-server-ver16
-- Query 1 : Top 10 total CPU consuming queries >> sdh dicoba

SELECT TOP 10

       @@SERVERNAME as ServerName,

       @@servicename as ServiceName,

       DB_NAME(qp.dbid) AS database_name,

       QT.OBJECTID AS OBJECT_ID, 

       object_name(QT.OBJECTID) as ObjectName,

       QS.TOTAL_WORKER_TIME AS CPU_TIME,

       QT.TEXT AS STATEMENT_TEXT,

       QP.QUERY_PLAN

FROM SYS.DM_EXEC_QUERY_STATS 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 not DB_NAME(qp.dbid) = 'msdb' and not DB_NAME(qp.dbid) = 'master' and not DB_NAME(qp.dbid) = 'model' and not DB_NAME(qp.dbid) = 'tempdb'

-- where  OBJECT_ID is not  = 'null'

-- where DB_NAME(qp.dbid) = 'teguhth' and QT.OBJECTID is not null

-- where  QT.TEXT ='select * from pembelian'

where  QT.OBJECTID is not null

ORDER BY TOTAL_WORKER_TIME DESC

.::: How to identify and monitor unused indexes in SQL Server from sqlshack :::.

-- A. Finding unused indexes
-- 1. Query 1
-- A simple query that can be used to get the list of unused indexes in SQL Server (updated indexes not used in any seeks, scan or lookup operations) is as follows:

SELECT @@servername as ServerName,@@Servicename as ServiceName,db_name() as DBName,
    objects.name AS Table_name,
    indexes.name AS Index_name,
    dm_db_index_usage_stats.user_seeks,
    dm_db_index_usage_stats.user_scans,
    dm_db_index_usage_stats.user_updates
FROM

    sys.dm_db_index_usage_stats
    INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
    INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID
WHERE
    dm_db_index_usage_stats.user_lookups = 0
    AND
    dm_db_index_usage_stats.user_seeks = 0
    AND
    dm_db_index_usage_stats.user_scans = 0
ORDER BY
    dm_db_index_usage_stats.user_updates DESC

.::: Check Querying SQL Server Agent Job Information :::.

-- 1. SQL Server Agent Job Setup and Configuration Information
SELECT @@SERVICENAME as ServiceName,
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sDBP].[name] AS [JobOwner]
    , [sCAT].[name] AS [JobCategory]
    , [sJOB].[description] AS [JobDescription]
    , CASE [sJOB].[enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
      END AS [IsEnabled]
    , [sJOB].[date_created] AS [JobCreatedOn]
    , [sJOB].[date_modified] AS [JobLastModifiedOn]
    , [sSVR].[name] AS [OriginatingServerName]
    , [sJSTP].[step_id] AS [JobStartStepNo]
    , [sJSTP].[step_name] AS [JobStartStepName]
    , CASE
        WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
        ELSE 'Yes'
      END AS [IsScheduled]
    , [sSCH].[schedule_uid] AS [JobScheduleID]
    , [sSCH].[name] AS [JobScheduleName]
    , CASE [sJOB].[delete_level]
        WHEN 0 THEN 'Never'
        WHEN 1 THEN 'On Success'
        WHEN 2 THEN 'On Failure'
        WHEN 3 THEN 'On Completion'
      END AS [JobDeletionCriterion]

.::: Sample Query T-SQL Select Statements from AdventureWorks2019 :::.

SELECT ROW_NUMBER() OVER (ORDER BY T.Name) AS No,@@SERVERNAME as ServerName, @@servicename as ServiceName

use AdventureWorks2019
go

--- sample 1
SELECT ROW_NUMBER() OVER (ORDER BY [TotalDue]) AS No,@@SERVERNAME as ServerName, @@servicename as ServiceName,
CustomerID, SalesOrderID, [SubTotal], [TotalDue]
FROM Sales.SalesOrderHeader

--- sample 2
SELECT ROW_NUMBER() OVER (ORDER BY SalesOrderID) AS No,@@SERVERNAME as ServerName, @@servicename as ServiceName,
* FROM Sales.SalesOrderHeader
WHERE [OrderDate] BETWEEN '1/1/2000' AND '1/31/2023'
 

.::: Procedure Indexing SQL Server Using DBCC DBREINDEX Sample SalesOrderHeaderEnlarged in AdventureWorks2019 :::.

 
A. Precheck before indexing
1. Check Index fragmentation
SELECT @@servername as Server, DB_NAME(ips.database_id) AS DatabaseName,@@servicename as ServiceName,
       SCHEMA_NAME(ob.[schema_id]) SchemaNames,
       ob.[name] AS ObjectName,
       ix.[name] AS IndexName,
       ob.type_desc AS ObjectType,
       ix.type_desc AS IndexType,
       -- ips.partition_number AS PartitionNumber,
       ips.page_count AS [PageCount], -- Only Available in DETAILED Mode
       ips.record_count AS [RecordCount],
       ips.avg_fragmentation_in_percent AS AvgFragmentationInPercent
-- FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'DETAILED') ips
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'SAMPLED') ips -- QuickResult
INNER JOIN sys.indexes ix ON ips.[object_id] = ix.[object_id]
                AND ips.index_id = ix.index_id
INNER JOIN sys.objects ob ON ix.[object_id] = ob.[object_id]
WHERE ob.[type] IN('U','V')
AND ob.is_ms_shipped = 0
AND ix.[type] IN(1,2,3,4)
AND ix.is_disabled = 0
AND ix.is_hypothetical = 0
AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
AND ips.index_level = 0
and ob.[name] ='SalesOrderHeaderEnlarged'
-- AND ips.page_count >= 1000 -- Filter to check only table with over 1000 pages
-- AND ips.record_count >= 100 -- Filter to check only table with over 1000 rows
-- AND ips.database_id = DB_ID() -- Filter to check only current database
-- AND ips.avg_fragmentation_in_percent > 50 -- Filter to check over 50% indexes
-- ORDER BY DatabaseName
order by ips.avg_fragmentation_in_percent desc, DatabaseName asc