Mencari History Query Yang Memperberat CPU

Di pagi yang cerah ini saya melihat-lihat blog dahulu. Dan ternyata, eh sudah 4 tahun lebih saya tidak membuat artikel, hmmm….

Setelah dpikir-pikir, sepertinya ini bermula saat saya mulai sibuk dalam pencarian rumah. Setelah rumah dapat, Alhamdulillah anak pun diberi/alias istri saya hamil. Namun, karena suatu kondisi penyakit sejak lahir, maka pengobatan panjang dimulai hingga anak saya berusia 1 tahun. Eh, kok malah curcol, hehe…

Oke, kita masuk kembali ke inti permasalahan yang akan saya bahas kali in. Next saya coba bagi pengalaman saya mengenai pengobatan panjang anak saya sejak lahir hingga sembuh. Namun kali ini saya coba sharing sedikit query untuk permasalahan yang saya hadapi dan mungkin juga teman-teman hadapi. Jadi, pada pekan lalu saat saya selesai bekerja, di grup kantor ramai dikarenakan ada sesuatu yang membuat CPU Server DB menjadi high hingga 90%. Nah, kita bingung ini aplikasi mana yang menyebabkan CPU Server DB high. Semua mencari mulai dari scheduler, job SQL, hingga aplikasi-aplikasi yang jalan pada saat jam tersebut. Lalu setelah terbantukan oleh paman Google, akhirnya setidaknya saya menemukan query untuk setidaknya membantu saya dalam proses pencarian query yang menyebabkan CPU high. Berikut query yang saya gunakan :

 SELECT TOP 100 deqs.last_execution_time AS [Time]
	,dest.TEXT AS [Query]
	,dest.*
	,deqs.*
	,deqs.sql_handle
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) qp
WHERE deqs.last_execution_time BETWEEN '2020-06-18 18:30:00'
		AND '2020-06-18 21:00:00'
ORDER BY deqs.total_worker_time DESC

Pada last_execution_time teman-teman cukup filter query apa saya yang dieksekusi pada jam tersebut. Pada kasus saya (dalam hal ini yang saya eksekusi sekarang tanpa filter waktu eksekusi, dikarenakan saat kejadian saya g sempat untuk capturenya), berikut hasil yang akan tampil :

Pada hasil di atas, kita dapat melihat query apa yang menyebabkan CPU saya sangat tinggi, yakni query teratas. Dimana di sini saya memakai total_worker_time untuk pengurutan. total_worker_time adalah jumlah waktu yang dihabiskan untuk menjalankan kueri sejak paket dikompilasi, misalnya : jika paket telah dijalankan 100 kali sejak dikompilasi, dan setiap kali dijalankan hingga 10 ms, total_worker_time akan menjadi 1000 ms. total_worker_time ini cocok digunakan untuk pengurutan kejadian terutama kejadian spike yang saya alami terhadap CPU Server DB saya.

Query yang saya gunakan ini disebut DMV (Dynamic Management Views). Kalau teman-teman mau tahu lebih lanjut apa itu DMV, apa saja views yg bisa digunakan, juga bagaimana cara penggunaannya, dan banyak lainnya, teman-teman bisa mengunjungi langsung artikel DMV tersebut di :

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/execution-related-dynamic-management-views-and-functions-transact-sql?view=sql-server-ver15

Mungkin sekian dulu dari saya. Semoga sedikit tulisan ini bermanfaat buat teman-teman khususnya yang menngalami kejadian yang sama dengan saya.

Terima kasih & selamat bekerja.. 😀