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

.::: Query index fragmentation level SQL Server with detail, specify Table, Database :::.

1. index fragmentation level

SELECT @@servername as Server, DB_NAME(ips.database_id) AS DatabaseName,
       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 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

.::: Query Check ServerName, Database Name, Database ID, Table ID, Object ID,Object Name, Size Table, master_files, size Database :::.

1. Info ServerName, Version, db_name
select @@SERVERNAME as ServerName,
       @@SERVICENAME as ServiceName,
       db_name() as DBName,
       @@VERSION as VersionDB,
       @@MICROSOFTVERSION as MICROSOFTVERSION,
       @@LANGUAGE as Language
 

.::: Create Store Procedure & Check Dependencies :::.

A. Sample Query 1 & Store Procedure 1
1. Sample 1
-- Query 1
select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;

2. Store Procedure 1

Create Procedure Barang_pasok

as
 begin
 select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from   barang b,suplier s,pasok p where b.KODE_BARANG=p.KODE_BARANG and  s.KODE_SUPLIER=p.KODE_SUPLIER;

end

.::: Insert Into table from Linkedserver to Linkedserver with Open Query & dbname(database_id) :::.

A. Linked Server (MSSQL)

1. Check Database ID on Linked Server
select @@Servername as LocalServer,@@servername as ServerName_Link, name as dbname,database_id  from sys.databases

2. Check Result Query on Linked Server
SELECT @@Servername as LocalServer, @@Servername as ServerName_Link,db_name('5')  as [DBName], * from teguhth.dbo.barang

.::: Create Report SQL Server Using SSRS (Sample Lab ) :::.

A. Check Table SQL Server
select * from barang;

B. Install & Enable SSRS Service

.::: Performance Compare 4 CPU vs 1 CPU In SQL Server Using Execution Plan (Setting MaxDop Max Degree Of Parallelism) :::.

 
1. Download & restore AdventureWorks2019
2. Open New Query and run Query below & Execute
set Statistics io, time on
use AdventureWorks2019

-- Query running on 4 logical processors

.::: Lab Integration SSIS with SQL Server using SQL Jobs Agent :::.

A. Create Database & Table from Source & Destination

1. Create database source

tgh_source

2. create table source

create table barang(

KODE_BARANG char(6) not null ,

NAMA_BARANG varchar(25), 

SATUAN_BARANG varchar(20),

STOK_BARANG decimal(4),

primary key (KODE_BARANG));

.::: Configure Enable NTP Server & Client Windows Server using Group Policy Editor :::.

 A. Windows Server become NTP Server

1. Open Group Policy Editor (gpedit.msc)

2. Open Time Provider

Computer Configuration > Administrative Templates > System > Windows Time Service > Time Providers.