none
SQL Server 2000 Error : Process Blocking RRS feed

  • Pertanyaan

  • Dear All,

    Saya mencoba mencari tahu query mana yang menyebabkan menurunnya performance SQL Server kantor kami menggunakan server lain dan SQL Profiler. Dan baru berhasil mendapatkan salah satu query yang bermasalah.

    Saat satu query ini diexecute, Log SQL Server dan Event Viewer langsung dipenuhi oleh pesan:

     "1229 : Process ID xx:xxx owns resources that are blocking processes on Scheduler xx"

    (Saat diexecute, benar2 hanya query ini yang tercatat di Activity Monitor)

    Query saya upload di:
    http://upload.ugm.ac.id/943Query%20spRptLapMKT_UICPerCustomer_Temp.txt

    Apakah query ini sangat bermasalah? Mohon petunjuk senior sekalian...

    Terima kasih

    Beta

    Selasa, 30 Oktober 2012 07.07

Jawaban

  • Dear all,

    Saya sudah coba mengexecute hanya 1 query di atas pada Query Analyzer dan tetap mendapatkan hasil : "Transaction (Process ID xx) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

    Saat query diexecute, saya coba capture dengan sp_who2 namun tidak ada process id yang terblock maupun diblock.

    Saya juga memonitor executionnya dengan Performance Monitor (SQLServer:Locks - Number of Deadlocks), namun juga tidak mendapatkan hasil yang menunjukkan adanya deadlock.

    Saya mendapatkan informasi bahwa hal tersebut kemungkinan diakibatkan oleh setting SQL Server yang menggunakan seluruh processor yang tersedia untuk Parallelism.

    (Server baru yang digunakan memiliki total 32 processor, dan seluruhnya digunakan untuk parallelism)

    Dan setelah disetting SQL Server menggunakan 32 processor dan untuk parallelism hanya menggunakan 8  atau 16 processor, query tersebut bisa diexecute dengan sukses...

    Beta

    • Ditandai sebagai Jawaban oleh Stan Beta Jumat, 09 November 2012 08.48
    • Tanda sebagai Jawaban dihapus oleh Stan Beta Jumat, 09 November 2012 08.51
    • Ditandai sebagai Jawaban oleh Andy NugrahaModerator Senin, 12 November 2012 03.18
    Jumat, 09 November 2012 08.48

Semua Balasan

  • Anda menggunakan tabel temporary yang berupa tabel riil. Hal ini bisa berakibat fatal jika ada dua user mengakses SP tersebut dalam waktu yang sama.

    Anda bisa ganti dengan temporary tabel sebagai berikut.

    CREATE  PROCEDURE spRptLapMKT_UICPerCustomer_Temp  
    AS    
     Select Distinct  e.VCH_LOCNM 
    	,a.VCH_NOKONCETAK   
      	,Right(a.CHR_TGLMULAI ,2) + '/' + SubString(a.CHR_TGLMULAI ,5,2) + '/' + Left(a.CHR_TGLMULAI,4) As TglMulai  
      	,Right(IsNull(g.CHR_TGLAKHRPPJ ,a.CHR_TGLAKHIR) ,2) + '/' +   
       	SubString(IsNull(g.CHR_TGLAKHRPPJ ,a.CHR_TGLAKHIR),5,2) + '/' +   
       	Left(IsNull(g.CHR_TGLAKHRPPJ ,a.CHR_TGLAKHIR) ,4) As TglAkhir  
      	--,a.CHR_TGLAKHIR --,a.MNY_NILAIKON   
      	,b.CHR_NOUNITSEWA
    	,b.VCH_NOPOLSEWA 
    	,d.TIPE_UNIT + ' ' + IsNull(l.CHR_THNBUAT ,'') As TIPE_UNIT 
    	,IsNull (l.vch_nochasis ,'') As NoChasis 
    	,IsNull (l.vch_nomesin ,'') As NoMesin  
      	--,Replace(b.VCH_NOPOLSEWA ,'-','') As NoPolSewa ,Replace(b.VCH_NOPOLGANTI ,'-','') As NoPolGanti   
      	,b.VCH_NOPOLGANTI 
    	,IsNull (m.vch_nochasis , '') As NoChasisGanti 
    	,IsNull (m.vch_nomesin ,'')As NoMesinGanti  
      	,IsNull(F.TIPE_UNIT  ,'') + ' ' + IsNull(m.CHR_THNBUAT ,'')  As TipeUnitGanti  
      	,h.MNY_NILTGHUNIT /h.SIN_LAMAPRDUNIT As SewaPerBulan 
    	,i.VCH_NMCABANG  
      	,IsNull(Ltrim(j.VCH_TITLEKONS + ' ') + j.VCH_NMKONS 
    	,Ltrim(k.VCH_TITLEKONS + ' ') + k.VCH_NMKONS) As NamaKonsumen  
       ,Isnull(n.VCH_NMMC, '') As VCH_NMMC  
      	,a.CHR_COMPID 
    	,a.CHR_AREAID 
    	,a.CHR_LOCID 
    	,a.VCH_KDKONS
    	--,b.VCH_NOPOLSEWA   
      	,IsNull(g.CHR_TGLAKHRPPJ ,a.CHR_TGLAKHIR) As CHR_TGLAKHIR  
      
     Into #TempLaporanUICPerCustomer_Detail  
      
     From LT_KON_DGN_BIAYA_HDR a   
     Inner JoiN LT_KON_DGN_BIAYA_DTL_UNIT b 
    	On a.CHR_COMPID = b.CHR_COMPID 
    	And a.CHR_AREAID = b.CHR_AREAID   
      	And a.CHR_LOCID = b.CHR_LOCID 
    	And a.VCH_NOKON = b.VCH_NOKON  
      	And b.BIT_DELETE = 0  
    --Cari unit Sewa  
     Inner Join V_TIPEUNIT d 
    	On b.VCH_KDUNITSEWA = d.VCH_KDUNIT  
     Inner Join MF_LOCATION e 
    	On a.CHR_COMPID = e.CHR_COMPID 
    	And a.CHR_AREAID = e.CHR_AREAID 
    	And a.CHR_LOCID = e.CHR_LOCID  
    --Cari marketing  
     Inner Join MF_MC n 
    	On a.CHR_COMPID = n.CHR_COMPID 
    	And a.CHR_AREAID = n.CHR_AREAID  
    	And a.CHR_LOCID = n.CHR_LOCID 
    	And a.VCH_KDMC = n.VCH_KDMC  
    --Cari Harga Sewa Per Bulan  
     Inner Join  
      (Select Min(SIN_NOURUTTGH) As NourutTgh 
    			,CHR_AREAID 
    			,CHR_LOCID 
    			,VCH_NOREGISTRASI 
    			,SIN_NOURUTUNIT 
    			,MNY_NILTGHUNIT   
       		,SIN_LAMAPRDUNIT 
    			,CHR_COMPID  
      From LT_HIS_KON_DGN_BIAYA_TGH_UNIT  
      Group By CHR_AREAID 
    				,CHR_LOCID 
    				,VCH_NOREGISTRASI 
    				,SIN_NOURUTUNIT 
    				,MNY_NILTGHUNIT 
    				,SIN_LAMAPRDUNIT 
    				,CHR_COMPID 
    	) h  
      On a.CHR_AREAID = h.CHR_AREAID 
    	And a.CHR_LOCID = h.CHR_LOCID 
    	And a.VCH_NOREGISTRASI = h.VCH_NOREGISTRASI  
       And b.SIN_NOURUTUNIT = h.SIN_NOURUTUNIT 
    	And a.CHR_COMPID = h.CHR_COMPID  
     --Cari unit Ganti  
     Left Join V_TIPEUNIT F 
    	On b.VCH_KDUNITGANTI = F.VCH_KDUNIT  
     --Lokasi Customer  
     Left Outer Join MF_KONSUMEN_PERUSAHAAN_DTL_CABANG i 
    	On a.CHR_COMPID = i.CHR_COMPID 
    	And a.VCH_KDKONS = i.VCH_KDKONS  
      	And a.CHR_NOURUT_CABANG = i.CHR_NOURUT_CABANG  
     --Cari Nama Konsumen  
     Left Outer Join MF_KONSUMEN_PERUSAHAAN_HDR j 
    	On a.CHR_COMPID = j.CHR_COMPID 
    	And a.VCH_KDKONS = j.VCH_KDKONS  
     Left Outer Join MF_KONSUMEN_INDIVIDU k 
    	On a.CHR_COMPID = k.CHR_COMPID 
    	And a.VCH_KDKONS = k.VCH_KDKONS  
     --Cari Thn Pembuatan Unit  
     Left Outer Join MF_UNIT_DATA l 
    	On l.CHR_COMPID = b.CHR_COMPID 
    	And l.CHR_NOUNIT = b.CHR_NOUNITSEWA   
     Left Outer Join MF_UNIT_DATA m 
    	On m.CHR_COMPID = b.CHR_COMPID 
    	And m.CHR_NOUNIT  = b.CHR_NOUNITGANTI   
     --Cari Perpanjangan  
     Left Outer Join  
      (Select a.CHR_COMPID
    		,a.CHR_AREAID 
    		,a.CHR_LOCID 
    		,a.VCH_NOKON 
    		,b.VCH_KDKONS 
    		,Max(b.CHR_TGLAKHRPPJ)As CHR_TGLAKHRPPJ  
      From LT_PERPANJANGAN_KON a  
      Inner Join LT_RNC_PERPANJANGAN_KON b 
    	On a.CHR_AREAID = b.CHR_AREAID 
    	And a.CHR_LOCID = b.CHR_LOCID  
       And a.CHR_COMPID = b.CHR_COMPID 
    	And a.VCH_NODOK = b.VCH_NODOK   
      Group By a.CHR_COMPID
    		,a.CHR_AREAID 
    		,a.CHR_LOCID 
    		,a.VCH_NOKON 
    		,b.VCH_KDKONS 
    	)g  
      On a.CHR_COMPID = g.CHR_COMPID 
    	And a.CHR_AREAID = g.CHR_AREAID 
    	And a.CHR_LOCID = g.CHR_LOCID 
    	And a.VCH_NOKON = g.VCH_NOKON  
     Where a.BIT_TUTUP = 0   
    --  And a.VCH_KDKONS = Case Ltrim(@KdKonsumen) When '' Then a.VCH_KDKONS Else @KdKonsumen End  
    --  And a.CHR_COMPID = @CompID  
    --  And a.CHR_AREAID = Case Ltrim(@AreaID) When '' Then a.CHR_AREAID Else @AreaID End  
    --  And a.CHR_LOCID = Case Ltrim(@LocID) When '' Then a.CHR_LOCID Else @LocID End  
      
    Order By a.CHR_AREAID 
    		,a.CHR_LOCID 
    		,b.VCH_NOPOLSEWA 
    		,IsNull(g.CHR_TGLAKHRPPJ 
    		,a.CHR_TGLAKHIR)
    Jika masih terdapat blocking, Anda bisa coba tampilkan query execution plan (Ctrl+L atau Ctrl+M) di SQL Management Studio untuk melihat seberapa bagus query Anda.

    Ahmad Masykur http://www.masykur.web.id/

    Selasa, 30 Oktober 2012 07.42
  • Dear Mas Ahmad,

    SP ini diexecute by schedule jam 2 pagi dan hanya 1x sehari, sehingga tidak ada beberapa user yang mengexecute SP ini secara bersamaan.

    Saya pun mencoba pada 1 server terpisah, tanpa ada transaksi lain pada server tersebut, namun tetap saja pesan error itu muncul dan setiap 5 detik bisa tercatat 50pesan error yang sama pada Event Viewer dan SQL Server Log, dan execution timenya sangat lama bahkan akhirnya failed juga.

    Kriteria suatu query dikatakan bagus itu terlihat dari mana ya mas kalau kita lihat dari query execution plannya?

    Beta

    Selasa, 30 Oktober 2012 08.29
  • Query yang bagus adalah query yang mengkses index seek. Jika ada query yang table scan, maka ada query atau desain tabel yang kurang baik.

    Jika query Anda tidak ada lagi yang table scan, maka eksekusi query akan jauh lebih cepat.

    Anda bisa coba upload hasil query execution plan ke sini untuk bisa dianalisis.


    Ahmad Masykur http://www.masykur.web.id/

    Selasa, 30 Oktober 2012 08.41
  • Dear Mas Ahmad,

    Saya sudah coba query yang diberikan, dan ternyata hasilnya masih tetap sama. Saat coba di Query Analyzer juga muncul error 

    "Transaction (Process ID xx) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

    Tapi jika selectnya diganti dengan select *, justru hasilnya malah bisa muncul...

    Dan yang saya lebih bingung lagi, di server production sekarang, prosesnya lancar2 saja...

    Apakah ada setting2 khusus yang bisa membuat sql server meng-ignore atau menghandle deadlock?

    Rabu, 31 Oktober 2012 02.43
  • Kalo select * bisa, Anda coba dulu dengan menghilangkan satu per satu kolom setelah select untuk mempersempit analisis.


    Ahmad Masykur http://www.masykur.web.id/

    Rabu, 31 Oktober 2012 23.32
  • Untuk menghindari blocking, Anda bisa gunakan query hint NOLOCK. Sebagai contoh

    SELECT field1, field2 FROM dbo.Table1 WITH (NOLOCK)
    WHERE ID=@id


    Ahmad Masykur http://www.masykur.web.id/

    Rabu, 31 Oktober 2012 23.40
  • HI Beta

    apakah masalah deadlocknya sudah selesai?

    Ini ada beberapa acuan sesuai dengan error code

    "1229 : Process ID xx:xxx owns resources that are blocking processes on Scheduler xx"

    dan ini ada referensi untuk menghindari deadlock

    http://support.microsoft.com/kb/319892

    http://support.microsoft.com/kb/169960

    segera membalas forum ini, kita ingin tahu update yang sudah dilakukan beta


    Andy Nugraha MSFT] Technet Community Support |

    Selasa, 06 November 2012 09.26
    Moderator
  • Dear Mas Andy, 

    Saya sudah membaca kedua referensi tersebut sebelumnya, namun kurang dapat memahami isinya.

    Saya saat ini menggunakan flag #T1261 pada server Production karena beberapa bulan lalu SQL Error Lognya bengkak karena pesan error di atas, namun apa penyebabnya belum bisa didapatkan...

    Juga bingung karena di server production tanpa No Lock pun query di atas masih bisa diexecute dan sukses, sedangkan di server testing, sama sekali tidak bisa dijalankan...
    Selasa, 06 November 2012 10.30
  • Coba saat query dijalankan, buka jendela baru dan jalankan script berikut sp_who2 active untuk mengetahui proses mana yg menyebabkan blocking

    Ahmad Masykur http://www.masykur.web.id/

    Selasa, 06 November 2012 10.40
  • Ya Beta

    Betul seperti yang dikatakan Ahmad, coba gunakan scirpt sp_who2 untuk mengetahui proses mana yang menyebabkan blocking, kemudian segera share di forum ini untuk hasilnya, nanti coba kita pelajari bersama

    Terima Kasih


    Andy Nugraha MSFT] Technet Community Support |

    Jumat, 09 November 2012 02.38
    Moderator
  • Dear all,

    Saya sudah coba mengexecute hanya 1 query di atas pada Query Analyzer dan tetap mendapatkan hasil : "Transaction (Process ID xx) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

    Saat query diexecute, saya coba capture dengan sp_who2 namun tidak ada process id yang terblock maupun diblock.

    Saya juga memonitor executionnya dengan Performance Monitor (SQLServer:Locks - Number of Deadlocks), namun juga tidak mendapatkan hasil yang menunjukkan adanya deadlock.

    Saya mendapatkan informasi bahwa hal tersebut kemungkinan diakibatkan oleh setting SQL Server yang menggunakan seluruh processor yang tersedia untuk Parallelism.

    (Server baru yang digunakan memiliki total 32 processor, dan seluruhnya digunakan untuk parallelism)

    Dan setelah disetting SQL Server menggunakan 32 processor dan untuk parallelism hanya menggunakan 8  atau 16 processor, query tersebut bisa diexecute dengan sukses...

    Beta

    • Ditandai sebagai Jawaban oleh Stan Beta Jumat, 09 November 2012 08.48
    • Tanda sebagai Jawaban dihapus oleh Stan Beta Jumat, 09 November 2012 08.51
    • Ditandai sebagai Jawaban oleh Andy NugrahaModerator Senin, 12 November 2012 03.18
    Jumat, 09 November 2012 08.48
  • Hi Beta

    Selamat karena Beta telah menemukan solusi dari masalahnya, benar benar pembahasan yang menarik.

    Berikut saya share beberapa artikel menarik tentang parallelism, karena penggunaan setting parallelism sedikit berbeda untuk beberapa tipe SQL Server

    http://support.microsoft.com/kb/2023536

    http://support.microsoft.com/kb/329204


    Andy Nugraha MSFT] Technet Community Support |

    Senin, 12 November 2012 03.18
    Moderator