Optimize Query Performance

Optimize Query Performance berarti proses menganalisis dan meningkatkan performa query SQL agar berjalan lebih cepat, efisien, dan menggunakan resource seminimal mungkin pada database seperti Microsoft SQL Server atau Azure SQL Database

1.1 Determine the Appropriate Type of Execution Plan

Ada 3 jenis Execution Plan di SQL Server:

• Estimated kapan di pakai Prediksi rencana query sebelum dijalankan namun kelebihannya Cepat, tidak perlu run query.
• Actual dipakai Rencana nyata setelah query dijalankan namun kelebihannya Data akurat, ada actual rows
• Live di pakai Saat query berjalan kelebihannya Real time monitoring

A. Estimated Execution Plan

Melihat rencana query SEBELUM dijalankan:

USE DP300;
GO

SET SHOWPLAN_ALL ON;
GO

SELECT

c.CustomerID,

p.FirstName,

p.LastName,

soh.OrderDate,

soh.TotalDue

FROM Sales.Customer c

JOIN Person.Person p

ON c.PersonID = p.BusinessEntityID

JOIN Sales.SalesOrderHeader soh

ON c.CustomerID = soh.CustomerID

WHERE soh.TotalDue > 1000;
GO

SET SHOWPLAN_ALL OFF;
GO

B. Actual Execution Plan

Melihat rencana query SESUDAH dijalankan:

USE DP300;
GO

SET STATISTICS PROFILE ON;
GO

SELECT c.CustomerID,

p.FirstName,

p.LastName,

soh.OrderDate,

soh.TotalDue

FROM Sales.Customer c

JOIN Person.Person p

ON c.PersonID = p.BusinessEntityID

JOIN Sales.SalesOrderHeader soh

ON c.CustomerID = soh.CustomerID

WHERE soh.TotalDue > 1000;
GO

SET STATISTICS PROFILE OFF;
GO

C. Live Query Statistics

Melihat execution plan SECARA REAL TIME saat query berjalan:

SELECT DISTINCT

p.FirstName,

p.LastName,

pc.Name AS ProductCategory,

SUM(sod.LineTotal) AS TotalPurchase

FROM Sales.Customer c

JOIN Person.Person p

ON c.PersonID = p.BusinessEntityID

JOIN Sales.SalesOrderHeader soh

ON c.CustomerID = soh.CustomerID

JOIN Sales.SalesOrderDetail sod

ON soh.SalesOrderID = sod.SalesOrderID

JOIN Production.Product pr

ON sod.ProductID = pr.ProductID

JOIN Production.ProductSubcategory ps

ON pr.ProductSubcategoryID = ps.ProductSubcategoryID

JOIN Production.ProductCategory pc

ON ps.ProductCategoryID = pc.ProductCategoryID

GROUP BY p.FirstName, p.LastName, pc.Name
ORDER BY TotalPurchase DESC;
GO

1.2 Different Types of Loops & Scan vs Seek

Execution plan memiliki 3 algoritma join utama.

A. Types of Joins/Loops dalam Execution Plan

1. Nested Loop Join → Untuk data kecil

USE DP300;

GO -- Contoh Nested Loop (tabel kecil)

SELECT TOP 100

p.FirstName, p.LastName, e.JobTitle

FROM Person.Person p

JOIN HumanResources.Employee e

ON p.BusinessEntityID = e.BusinessEntityID

WHERE e.JobTitle = 'Sales Representative';

GO

Kelebihannya cepat untuk data sedikit kekuranggannya lambat pada table besar.

2. Hash Match Join → Untuk data besar tanpa index

-- Contoh Hash Match (data besar)

SELECT

p.FirstName,

p.LastName,

COUNT(soh.SalesOrderID) AS TotalOrders,

SUM(soh.TotalDue) AS TotalAmount

FROM Person.Person p

JOIN Sales.Customer c

ON p.BusinessEntityID = c.PersonID

JOIN Sales.SalesOrderHeader soh

ON c.CustomerID = soh.CustomerID

GROUP BY p.FirstName, p.LastName

HAVING SUM(soh.TotalDue) > 5000

ORDER BY TotalAmount

DESC;

GO

Pada Query yang di jalan kelebihannya bagus untuk table besar namun kekurangannya adalah memorinya dibutuhkan lebih besar.

3. Merge Join→ Untuk data yang sudah terurut

-- Contoh Merge Join (data terurut)

SELECT

soh.SalesOrderID,

soh.OrderDate,

sod.ProductID,

sod.OrderQty, sod.LineTotal

FROM Sales.SalesOrderHeader soh

JOIN Sales.SalesOrderDetail sod

ON soh.SalesOrderID = sod.SalesOrderID

ORDER BY soh.SalesOrderID;

GO

Pada Query yang di jalankan kelebihannya sangat efisien untuk data besar yang sudah terurut namun syaratnya table memiliki index pada join coumn tersebut. 

B. Scan vs Seek

TABLE SCAN / INDEX SCAN → Baca semua data ❌ Lambat

USE DP300;
GO -- Contoh yang menyebabkan TABLE SCAN (tanpa index)-- Lihat execution plan, akan terlihat "Table Scan"

SELECT * FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2013;
GO

INDEX SEEK→ Langsung ke data yang dicari ✅ Cepat

-- Lihat execution plan, akan terlihat "Index Seek" .

SELECT * FROM Sales.SalesOrderHeader
WHERE SalesOrderID = 43659;
GO

Perbandingan Scan vs Seek:

Table/Index Scan

• Cara Kerja : Baca semua rows
• Kecepatan : Lambat
• CPU Usage : Tinggi
• Kapan Terjadi : Tidak ada index
• Solusi : Tambah Index

Table/Index Seek

• Cara Kerja : Langsung mengarah data
• Kecepatan : Cepat
• CPU Usage : Rendah
• Kapan Terjadi :Ada Index yang tepat
• Solusi : Sudah Optimal

1.3 Identify Problem Areas in Execution Plans

A. Cek Query yang Paling Berat

USE master;
GO -- Top 10 Query Paling Lambat

SELECT TOP 10

total_elapsed_time / execution_count / 1000 AS AvgTimeMS,

execution_count AS ExecCount,

total_logical_reads / execution_count AS AvgLogicalReads,

SUBSTRING(st.text, 1, 200) AS QueryText

FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY AvgTimeMS
DESC;
GO

B. Cek Missing Index

-- Cek Index yang Kurang/Missing
SELECT TOP 10

mid.statement AS TableName,

migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS IndexBenefit,

mid.equality_columns AS EqualityColumns,

mid.inequality_columns AS InequalityColumns,

mid.included_columns AS IncludedColumns

FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig
ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs
ON mig.index_group_handle = migs.group_handleWHERE mid.database_id = DB_ID('DP300')

C. Cek Index yang Tidak Terpakai

-- Index yang tidak pernah dipakai (buang-buang resource)
SELECT

OBJECT_NAME(i.object_id) AS TableName,

i.name AS IndexName,

i.type_desc AS IndexType,

ius.user_seeks AS Seeks,

ius.user_scans AS Scans,

ius.user_lookups AS Lookups,

ius.user_updates AS Updates

FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius

ON i.object_id = ius.object_id

AND i.index_id = ius.index_id

AND ius.database_id = DB_ID('DP300')

WHERE OBJECT_NAME(i.object_id) NOT LIKE 'sys%' AND i.type > 0
ORDER BY ius.user_seeks ASC;
GO

D. Cek dan Fix Query Bermasalah

-- Query BERMASALAH (Lambat - Full Scan)
SELECT * FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod

ON soh.SalesOrderID = sod.SalesOrderID

WHERE YEAR(soh.OrderDate) = 2013 AND MONTH(soh.OrderDate) = 6;
GO
-- Query OPTIMAL (Cepat - Index Seek)

SELECT

soh.SalesOrderID,

soh.OrderDate,

soh.TotalDue,

sod.ProductID,

sod.OrderQty,

sod.LineTotal

FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod

E. Tambah Index untuk Fix Problem

-- Buat index untuk mempercepat query
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_OrderDate
ON Sales.SalesOrderHeader (OrderDate)
INCLUDE (TotalDue, CustomerID, SalesPersonID);
GO
-- Bandingkan sebelum dan sesudah index
SET STATISTICS IO ON;
GO

-- Jalankan query lagi setelah ada index
SELECT soh.SalesOrderID,

soh.OrderDate,

soh.TotalDue

FROM Sales.SalesOrderHeader soh
WHERE soh.OrderDate >= '2013-06-01'

SET STATISTICS IO OFF;
GO

Ringkasan Problem Areas yang Sering Ditemui: