SQL Server – Her Gün 1 DMV - Gün 21 – sys.dm_exec_cached_plans
turgay , 21. Eylül 2010, 08:00
sys.dm_exec_cached_plans DMV’si ile bu saklanan Query Plan’larını sorgulayabilir, hangi obje için oluşturulduğunu, query plan’ın ne olduğunu, bu query plan’ın kaç defa kullanıldığını ve memory’de ne kadar yer kapladığını öğrenebiliriz.
Üzerinde duracağımız kolonlar ve açıklamaları şu şekildedir.
usecounts : Cache’lenen query plan’ın cache’lendikten sonra kaç defa kullanıldığını gösterir.
size_in_bytes : Cache’lenen Query Plan’ın memory’de byte cinsinden ne kadar yer kapladığını gösterir.
objtype : Cache’lenen object’in type’ını verir. Proc , Adhoc, Trigger, View gibi değerler alabilir. Full listeye BOL’dan erişebilirsiniz.
plan_handle : Cache’lenin object’in plan’ını verir. Bu kolonu sys.dm_exec_query_plan’a cross’layarak Query Plan’a, sys.dm_exec_sql_text’e cross’layarak da statement text’e erişebiliriz.
Şimdi sorgumuzu detaylandırarak örneklerimize geçelim.
Cache’lendikten Sonra Kullanılmış Query Plan Raporu
Bu sorgu ile Cache’lenmiş objelerden kullanım sayısı 1 den fazla olanları sorgulayıp kullanım sayılarına göre dizeceğiz. Ayrıca sorgumuza statement_text ve Query_plan’ı ekleyerek bu bilgileri görüntüleyeceğiz.
Cache’lenen Stored Procedure’lerin Query Planları
Benim favorim olan ve production ortamlarımda sürekli kullandığım bu sorgu ile Cache’lenen Stored Procedure’lerin Query Planlarına, statement_text’lerine ve kullanım sayılarına erişebileceğiz.
Sorgumuzu çekmeden önce veri oluşturmak açısından aşağıdaki SP’leri yanlarındaki adet kadar çalıştıralım.
exec dbo.uspGetBillOfMaterials 15,'01.01.2010' -- 4 Kez Çalıştırılacak
exec dbo.uspGetEmployeeManagers 11 -- 3 Kez Çalıştırılacak
DMV sorgu sonucumuzda uspGetBillOfMaterials ve uspGetEmployeeManagers SP’lerinin cache’lenmiş olduğunu ve 3 ve 2 kez kullanılmış olduklarını görmeyi planlıyorum.
Gördüğünüz gibi sorgu sonucunda gelen ilk 2 satır bizim az önce execute ettiğimiz SP’ler. Bu satırlardan uspGetEmployeeManagers SP’si üzerinde duracak olursak; bu SP’nin cache’lendikten sonra 3 kez kullanıldığını görüyoruz. Ayrıca bu plan’ın memory’de 128880 byte kapladığı bilgisi de gelen bilgiler arasında. Text kısmından bu SP’nin koduna erişebilmekteyiz. Ve son olarak query_plan kısmındaki XML kod’una tıklarsak eğer yeni bir sayfada bu SP için oluşturulmuş Query Plan’ı diagram olarak görmemiz mümkün.
Cache’lenen Plan’ların Obje Tipine Göre Dağılımı
SP,View,Trigger ya da Adhoc gibi sorguların cache’lendiğini söylemiştik. Aşağıdaki sorgu vasıtasıyla her tipten kaç adet Query Plan’ın cache’lendiği bilgisine erişebilirsiniz.
select cp.objtype,COUNT(*) as ObjectCount
Bu sorgu sonucundan 20 View’in , 14 Stored Procedure’ün query plan’ının cache’lenmiş olduğu bilgisine erişiyoruz.
Sizde sunucularınızda sys.dm_exec_cached_plans için hazırlamış olduğum yukarıdaki sorguları çekeren cache’lenmiş query planları sorgulayabilirsiniz. Bu sorgular vasıtasıyla hangi obje’ler için hazırlanmış query plan’ların cache’lendiğini, cache’lendikten sonra kaç kez kullanıldıkları bilgilerine erişebilirsiniz.
Tags: sql server, DMV, DMF, Dynamic Management View, Dynamic Management Function, DMO, Dynamic Management Object, sys.dm_exec_cached_plans
Kategori: ! SQL Server | Stored Procedures | DMV-DMF
SQL Server – Her Gün 1 DMV - Gün 16 – sys.dm_exec_procedure_stats ile Stored Procedure Çalışma İstatistikleri
turgay , 16. Eylül 2010, 08:00
sys.dm_exec_query_stats ile Query İstatistikleri yazımda Database Engine’in çalıştırdığı bütün query’lerinin kaynak tüketimi açısından nasıl analiz edilip kötü yazılmış query’lerin bulunabileceğinden bahsetmiştik. Bugünkü yazımda ise aynı performans kontrollerinin Stored Procedure’ler üzerinde nasıl yapılacağına bakıyor olacağız.
Stored Procedure’lerin istatistiklerini incelemek için sys.dm_exec_procedure_stats DMV’sini kullanacağız. Bu DMV ile CPU, IO gibi değerler üzerinden SP’lerin kaynak tüketimine bakabileceğimiz gibi aynı zamanda kullanılmayan SP’leride analiz edebileceğiz.
Sorgu sonucu gelen kolonlar hemen hemen sys.dm_exec_query_stats DMV’sinde gelen kolonlar ile aynı. O yüzden bu kolon açıklamalarına sys.dm_exec_query_stats makalesinden bakabilirsiniz.
En Çok CPU Tüketen İlk 50 Stored Procedure (Top 50 CPU Consume Stored Procedure)
Şimdi detaylı bir analiz sorgusu yazalım. Bu sorgumuz ile AdventureWorks DB’si üzerinde çalışan SP’leri analiz edip en çok CPU tüketen SP’ye göre sıralayacağız.
Benim bu amaç için production ortamlarımda kullandığım Query aşağıdaki gibi.
OBJECT_NAME(object_id) as SPName,
st.[text] as SPCode,
cached_time as first_execution_time,
ps.total_logical_reads as total_logical_read,
ps.total_logical_reads/execution_count as avg_logical_read,
ps.total_worker_time/1000 as total_cpu_time_ms,
ps.total_worker_time/ps.execution_count/1000 as avg_cpu_time_ms
cross apply sys.dm_exec_sql_text(ps.plan_handle) st
order by ps.total_worker_time desc
Sorgu sonucu bende boş geldi çünkü AdventureWorks’te hiç SP execute etmemişim. İstatistik oluşturmak için aşağıdaki SP’leri yanlarında yazan sayı kadar çalıştırıyorum.
exec dbo.uspGetBillOfMaterials 15,'01.01.2010' -- 3 Kez Çalıştırılacak
exec dbo.uspGetEmployeeManagers 11 -- 2 Kez Çalıştırılacak
Şimdi yukarıdaki select’imizi tekrar çekelim.
Sonucu 2 resme bölerek gösteriyorum.
Sorgu sonucunda gelen SP’ler bizim az önce execute ettiğimiz SP’ler. Bu SP’lerin ilk kez ve son kez ne zaman çalıştırıldıklarını görebiliyoruz.Aynı zamanda çalışma istatistiklerini de analiz edebiliyoruz. Örneğin uspGetEmployeeManager SP’si toplam 2 kez çalışmış, her çalışmasında ortalama 67 logical read yapmış ve her çalışması ortalama 2 milisaniye sürmüş.
Kullanılmayan Stored Procedure Raporu
Uygulama geliştirme aşamasında gerekli olan SP’ler yazılır fakat bu SP’lerin bazıları uzun vadede kullanılmamaya başlayabilir. Bu yüzden periyodik olarak SP’lerin kullanım istatistikleri incelenip kullanılmayan SP’ler yorumlanmalı ve drop edip edilmemeleri üzerinde çalışma yapılmalıdır.
SP’lerin kullanılmama raporu hazırlanırken DMV’de uzun süredir istatistiki bilgi toplandığından emin olunmalıdır. Örneğin dün restart ettiğiniz SQL Server’da toplanan DMV bilgisi size genel resmi çizmeyecektir. O yüzden service’in analiz yapmak için yeterli süredir UP olduğundan emin olunmalıdır. Ayrıca bazı SP’lerin aylık ya da yılık olarak çalışabilecekleri de göz ardı edilmemelidir.
Ben production ortamlarımda, kullanılmayan SP raporu için aşağıdaki Query’i kullanmaktayım.
p.name as ObjectName,
left join sys.objects o on p.object_id=o.object_id
and not exists(Select ps.object_id
order by SCHEMA_NAME(o.schema_id),p.name
Bu sorgu size AdventureWorks DB’si için kullanılmayan SP’leri sorgulayacaktır.
Sizde yukarıdaki sorguları kullanarak SP’lerinizin kaynak kullanım istatistiklerini analiz edip kötü yazılmış SP’lerde iyileştirme çalışmaları yapabilirsiniz. Ayrıca kullanılmayan SP raporu çekip bu SP’leri drop edip etmeme üzerinde çalışabilirsiniz.
Tags: sql server, DMV, DMF, Dynamic Management View, Dynamic Management Function, DMO, Dynamic Management Object, sys.dm_exec_procedure_stats
Kategori: ! SQL Server | DMV-DMF | Monitoring | Stored Procedures
SQL Server da Stored Procedure Kullanımı
turgay , 2. Temmuz 2009, 10:47
Bu yazımda SQL Server da Stored Procedure kullanımından bahsedeceğim.
Projelerimizde bazı sık kullandığımız işlem yapan sql cümlelerini stored procedure yapmamızda fayda var. Bu şekilde bu sql cümlesini projenin bir çok yerinde kullanmaktansa yazdığımız stored procedure ü kullanabiliriz ve herhangi bir değişiklikte kodun tamamını değiştirmektense sadece stored procedure u değiştiririz.
Mesela bir finans uygulamamız olduğunu düşünelim. Sisteme fatura girildiği zaman başka bir özet tabloya ilgili cari için borç-alacak kayıtlarını stored procedure ile oluşturabiliriz. Bu şekilde kodumuzun birden çok yerinde fatura oluşturma işlemi varsa biz bunların hepsini stored procedure ile halledebiliriz.
Şimdi örnek bir stored procedure yazalım. Çalışma alanı olarak pubs kataloğunu kullanalım. Örneğimiz de job_id si verilen job u kopyalayarak yeni bir job yapalım ve yeni job un id sini döndürelim. Reel hayatta bunu fatura kopyalama olarak düşünebilirsiniz.
Açıklamaları sql kodda vermeye çalıştım. Buyrunuz.
/*Job2Job stored procedure ün adı*/
/*Job2Job sistemde varsa drop ediyoruz.*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Job2Job]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Job2Job]
/*Job2Job u create ediyoruz.*/
/*@JobID_Source - Kopyalamak istediğimiz job un id si*/
/*@JobID_Dest - Yeni oluşan job un id si. Bunu output ile alıyoruz.*/
CREATE Procedure dbo.Job2Job @JobID_Source int , @JobID_Dest int OUTPUT
/*with enc ile kodumuzu saklıyoruz.*/
/*artık kodumuz enterprise dan girilip bakılmaya çalışıldığında gözükmeyecektir*/
as set nocount on begin tran
select job_desc,min_lvl,max_lvl from jobs where job_id=@JobID_Source
/*insert ten sonra job kataloğunda ki en son numara yeni oluşan job un id sidir.*/
declare @JobID_Dest int
exec dbo.Job2Job 1,@JobID_Dest output
Tags: sql server, stored procedure, sp