Recent comments

None


İçerik Ara











Yasal Uyarı
Bu sitede sunulan tüm bilgi ve dökümanlar Turgay Sahtiyan tarafından yazılmaktadır. Yazıların kaynak göstermek şartıyla kullanılması serbesttir.

© Copyright 2009-2013
Takvim
<<  Ağustos 2017  >>
PaSaÇaPeCuCuPa
31123456
78910111213
14151617181920
21222324252627
28293031123
45678910
Keywords

Uygulama geliştirme sürecinde SP'lerin adlarını değiştirmemiz gerekebilir. Bu değişikliği sp_rename ile yapmaktansa drop&create ile yapmak best practice'dir

[more]

İsim değişikliğini sp_rename ile yaptığımızda sys.sql_modules'teki definition alanında bulunan SP adı değiştirilmez. Değiştirilmediği için bu catalog view'i ya da sp_helptext system procedure'unu kullanırsanız yanlış bir code'a erişmiş olursunuz. Bu yüzden SP değişikliklerini drop&create şeklinde yapmanızı kesinlikle tavsiye ederim. (Detaylı bilgi için http://msdn.microsoft.com/en-us/library/ms188351.aspx)

Şimdi bu durumu görmek için. Önce bir SP create edelim ve daha sonra bu SP'nin adını sp_rename ile değiştirip sys.sql_modules ve sp_helptext sonuçlarına bakalım.

--bir calisma DB'si create edelim
create database dbTurgay
go
use dbTurgay
go
--Bir SP create edelim
create proc mySP 
as
	select * from sys.databases
GO
--SP'nin adini sp_rename ile mySP_New seklinde degistirelim
exec sp_rename 'mySP','mySP_New'
GO
--ve daha sonra sys.modules ve sp_helptext sonuclarina bakalım
select * from sys.sql_modules
where object_id =object_id('mySP_New')
GO
sp_helptext 'mySP_New'
GO

image

Gördüğünüz gibi SP’nin adını değiştirmemize rağmen sys.sql_modules ve sp_helptext hala eski SP adını göstermekte.

Değişikliği SSMS GUI üzerinden yapacak olursan gene aynı problemli sonuç ortaya çıkacaktır.

Problemsiz bir şekilde isim değişikliği yapmanın tek yöntemi SP’yi önce drop etmek daha sonra tekrar create etmektir.

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server 2011 Denali ile beraber gelen yeni objeler sayesinde bir T-SQL komutunun ya da SP,view gibi objelerin çalıştırıldığı zaman döndüreceği kolonların listesini ve veri tiplerini almak mümkün. Ayrıca gene bu objeleri, bir TSQL script içerisindeki parametrelerin tiplerini öğrenmek için de kullanabiliriz.

[more]

Bu objelerin tamamının en büyük özelliği kodu çalıştırmadan bu bilgileri veriyor olmalarıdır. Örneğin şöyle bir senaryo düşünelim.

Uzun çalışan bir TSQL sorgumuz olduğunu düşünelim. Bu sorgu sonucunu da çalıştırdıktan sonra bir temporary tabloya atmak istiyoruz ama tablonun tanımını yani hangi kolon ve kolon tipleri şeklinde oluşturmamız gerektiğini bilmiyoruz.

Klasik yöntemde “into” syntax’ı ile sorguyu çalıştırıp tabloyu create edebiliriz. Ya da artık SQL Server 2011 Denali ile gelen sp_describe_first_result_set ve sys.dm_exec_describe_first_result_set objelerini kullanabiliriz.

sp_describe_first_result_set

Parametre olarak verilen TSQL komutunun döndüreceği ilk result set’in metadata bilgilerini verir.

Örneğin aşağıdaki komut sys.tables system view’ine çekilecek select işlemi sonucunda dönecek kolonların bilgisini döndürür.

exec sp_describe_first_result_set N'SELECT * FROM sys.indexes'

 

1

İlk result set’in metadata bilgilerinin döneceği yukarıda belirtmiştim. Yani aşağıdaki gibi önce sys.tables’a daha sonra sys.columns’a select çeken bir sorgu için gene sys.tables’ın metadata bilgileri dönecektir.

exec sp_describe_first_result_set N'SELECT * FROM sys.indexes; SELECT * FROM sys.columns'

 

sys.dm_exec_describe_first_result

sp_describe_first_result_set ile aynı işi yapan bu DMV, ilk result set’in metadata bilgilerini almak için kullanılabilir.

Kullanımı aşağıdaki gibidir.

select * from sys.dm_exec_describe_first_result_set ('SELECT * FROM sys.indexes', null, 0)

 

2

Bu DMV’nin sp_describe_first_result_set system procedure’ünden en büyük farkı bir tablo gibi sorgulanabilmesidir. Örneğin aşağıdaki gibi bir kullanımımız olabilir.

select dfr.name,dfr.system_type_id,dfr.system_type_name,max_length,precision,scale
from sys.dm_exec_describe_first_result_set ('SELECT * FROM sys.indexes', null, 0) dfr

 

3

 

sys.dm_exec_describe_first_result_set_for_object

Yukarıda anlattığım 2 obje, parametre olarak TSQL ifadesi alıyordu. Bu objelerle aynı işi yapan sys.dm_exec_describe_first_result_set_for_object DMV’si ile parametre olarak object_id alıp bu object_id’den dönen ilk result set’in metadata bilgisini dönmektedir.

Kullanımı aşağıdaki gibidir.

CREATE PROC dbo.sp_mdSample 
AS
	SELECT * FROM sys.indexes

GO

select *
from sys.dm_exec_describe_first_result_set_for_object (object_id('sp_mdSample'), 0)
GO

4

sp_describe_undeclared_parameters

Yukarıda anlattığım objeler ile parametre olarak verilen TSQL komutu ya da Obje çalıştırıldığında dönecek sonucun metadata bilgilerinin nasıl alınacağına bakmıştık. sp_describe_undeclared_parameters sistem SP’si ise TSQL komutunda geçen parametrelerin metadata bilgilerini öğrenmek için kullanılabilir.

Örneğin aşağıdaki sorguda @param1 ve @param2 adında 2 parametre kullanılmakta. Bu parametrelerin tiplerini öğrenmek için şöyle bir komut kullanabiliriz.

exec sp_describe_undeclared_parameters 
   @tsql = N'select * from sys.columns where name = @Param1 and max_length = @Param2'

 

5

 

İyi Çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Stored Procedure(SP) ilk çalıştırıldığı zaman istatistikler göz önüne alınarak Query Optimizer tarafından en optimum Query Plan oluşturulur ve daha sonra kullanılmak üzere Plan Cache’e konulur. Aynı SP farklı bir zamanda tekrar çalıştırıldığında Cache’deki plan’ın geçerliliği kontrol edilir ve eğer plan geçerli yani güncel ise tekrar Query Plan oluşturulmak için zaman harcanmayıp plan Cache’den çağırılır ve kullanılır.

Query Plan oluşturma işlemi bazı durumlarda çok fazla CPU kaynağı tükettiği için bu şekilde bir cache’lenme mekanizması kullanılır. Fakat bazı durumlarda cache’lenen plan güncelliğini yitirmiş olabilir. Örneğin SP içinde geçen bir tabloda plan cache’lendikten sonra çok fazla data değişimi olduysa bu durumda istatistikler güncelliğini yitirecek dolayısıyla da cache’lenen plan güncelliğini yitirecektir. Ya da SP’nin içinde geçen tablolarda index ekleme,silme gibi DDL (Data Definition Language) değişiklikleri yapılırsa yine cache’lenen plan güncelliğini yitirmiş olacaktır. Böyle bir durumda SP’nin yeniden derlenip yeni bir Query Plan’ın oluşturulması gerekmektedir. İşte bu duruma ReCompilation denilmektedir.

[more]

Yukarıda anlattığım şekilde istatistik değişimi ya da DDL değişikliği gibi işlemler sonucunda recompilation gerçekleşebileceği gibi SP oluşturulurken yazılan “WITH RECOMPILE” anahtar kelimesi SP’nin her çalışmasın recompile edilmesi sağlanabilir. Örneğin aşağıdaki şekilde oluşturulan SP her çağırılışında tekrar derlenecektir.

CREATE PROC dbo.sp_1
WITH RECOMPILE
AS
	SELECT * FROM Tbl1

 

WITH RECOMPILE anahtar kelimesi ile oluşturulan SP’lerin planları Plan Cache’de saklanmaz, SP her çalıştırıldığında tekrar oluşturulur. Bu seçenek, SP’nin aldığı parametrelere göre değişik Query Plan’lar oluşturduğu durumda kullanılabilir. Örneğin SP “a” parametresi ile çağırıldığında index scan, “b” parametresi ile çağırıldığında index seek yapıyorsa yani gönderilen parametreye göre Query Plan’ı değişiyorsa WITH RECOMPILE anahtar kelimesinin kullanılması mantıklı olacaktır.

WITH RECOMPILE ile SP’nin tamamı recompile edilir. SP’nin tamamının değilde içinde geçen bazı sorgu parçacıklarının yeniden derlenmesi isteniyorsa sorguların WITH RECOMPILE anahtar kelimesi ile yazılması gerekir.

SP’ler aşağıdaki durumlardan herhangi biri oluştuğunda recompile olacak yani yeniden derlenecektir.

  • Schema değiştiğinde
  • İstatistik değiştiğinde
  • SP’nin oluşturulma kodunda “With Recompile” anahtar kelimesi kullanıldığında
  • Set opsiyonları değiştiğinde
  • Temp table değiştiğinde
  • Remote rowset değiştiğinde
  • For browse perms değiştiğinde
  • Query notification environment değiştiğinde
  • Partition view değiştiğinde
  • Cursor seçenekleri değiştiğinde
  • Statement’ta Recompile seçeneği kullanıldığında

SP’ler yukarıda anlattığım şekilde recompile olabileceği gibi recompile olması için elle de tetiklenebilir.

EXEC sp_recompile N'dbo.sp_1'

 

Yukarıdaki kod vasıtasıyla sp_1 SP’sine recompile olması için bir işaret konulur. Bu SP bir dahaki sefer çalıştırılmak istendiğinde yeniden derlenecektir. sp_recompile sistem prosedürüne parametre olarak tablo ya da view de verilebilir. Bu durumda tabloya referans olan SP’ler recompile olacaktır.

SQL Server 2005’ten önceki sürümlerde SP recompilation işlemi SP’nin tamamında gerçekleştirilmekteydi. Yani örneğin SP’nin içinde bulunan bir sorgunun recompile edilmesi gerektiğinde SP’nin tamamı recompile edilmekteydi. 2005’ten sonraki versiyonlarda ise artık sadece recompile edilmesi gereken sorgu recompile edilmektedir. Bu şekilde çok büyük SP’lerde bir sorgu için SP’nin tamamı recompile edilmektense sadece ilgili kısım recompile edilebilmektedir. Bu da performans artışı anlamına gelmektedir.

Ayrıca SP recompile edilirken blocking’lere sebebiyet vermektedir. Bu yüzden çok fazla recompile olan SP ya da sorguların analiz edilmesinde fayda vardır.

ReCompilation Olaylarının İzlenmesi

SQL Server Profiler - SQL:StmtRecompile - SP:Recompile

Recompilation olayları SQL Server Profiler ile izlenebilmektedir. Bunun için kullanılan olaylar SQL:StmtRecompile ve SP:Recompile olaylarıdır.

SP:Recompile olayı ile recompile olan SP’ler izlenebilir. SQL:StmtRecompile olayı ile ise recompile olan sorgular izlenebilir. Daha önce bahsettiğim gibi SQL Server 2005 sürümünden sonra bir sorgunun recompile edilmesi gerektiğinde SP’nin tamamı değil sadece ilgili sorgu recompile edilmektedir. Dolayısıyla recompile işlemlerini izlemek için SP:Recompile yerine SQL:StmtRecompile olayının kullanılması daha mantıklı olacaktır.

Windows Performance Counter - SQL Re-Compilation/sec

Recomplation işlemleri windows performance counter’lar ile de izlenebilmektedir. Bunun için kullanılan performance counter SQL Re-Compilation/sec performance counter’ıdır. Genelde bu counter’ın toplam batch request’in %10’undan az olması beklenir. Bu değerden daha yüksek değerlerde, recompile olan sorgu veya SP’lerin detaylı incelenmesinde, neden recompile olduklarının analiz edilmesinde fayda vardır. Çünkü tekrar etmek gerekirse compilation işlemi CPU kaynaklarının çok fazla tüketilmesine sebep olabilir.

Activity Monitor

Recompilation olan SP ya da sorguların izlenmesi için kullanılan bir diğer araç ise SQL Server 2008 ile gelen Activity Monitor ekranıdır. Bu ekranda bulunan Recent Expensive Queries kısmındaki Plan Count kolonu ilgili SP’nin ya da sorgunun kaç kez recompile edildiğini göstermektedir.

1

Sys.dm_exec_query_stats – plan_generation_num

Recompilation’ları izlemenin bir diğer yolu da sys.dm_exec_query_stats DMV’sidir. Bu DMV’de bulunan plan_generation_num kolonu ile ilgili sorgunun kaç kez recompile edildiği bulunabilir.

select
	st.text,
	qs.sql_handle,
	qs.plan_generation_num,
	qs.creation_time,
	qs.execution_count,
	st.dbid,
	st.objectid
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where qs.plan_generation_num>1 and st.objectid is not null
order by qs.plan_generation_num desc

 

2

Ayrıca gene bu DMV sonucunda gelen creation_time kolonu vasıtasıyla sorgunun en son ne zaman recompile edildiği bilgisine ulaşılabilmektedir.

Sonuç

SP’nin yeniden derlenmesi bazı durumlarda CPU kaynaklarının çok fazla kullanılmasına neden olabilir. Ayrıca yeniden derleme işlemi sırasında blocking’ler oluşabilir. Bu yüzden recompilation olayları izlenmeli, çok fazla recompile olan sorgu ya da SP’lerin recompile nedenleri analiz edilmelidir.

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bildiğiniz gibi LinkedServer tanımlayarak A server’ı üzerinden B Server’ında select,update gibi işlemleri yapabilmemiz mümkün.

Bugün anlatacağım OpenDataSource komutu ile linked server’a gerek kalmadan bu işlemleri nasıl yapabileceğimizi görüyor olacağız.

[more]

OpenDataSource kullanılabilmesi için sorguyu çalıştıran server’da Ad Hoc Distributed Queries özelliğinin enable edilmesi gerekmektedir.

Aksi halde şöyle bir hata alınır.

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Ad Hoc Distributed Queries özelliğini aktif etmek için sorguyu çalıştıran server’da şu script’i çalıştırabilirsiniz.

exec sp_configure 'show advanced options',1
ReConfigure with override
exec sp_configure 'Ad Hoc Distributed Queries',1
ReConfigure with override
exec sp_configure 'show advanced options',0
ReConfigure with override

Şimdi kullanıma bakalım.

Örneğin B server’ında bulunan bir tabloya select çekmek istiyorsunuz. Bunu OpenDataSource ile yapabilmek için önce B server’ında bulunan bu tabloya select çekme yetkisine sahip bir kullanıcı tanımlıyoruz. Daha sonrada bu kullanıcıyı kullanarak A server’ında şöyle bir view create ederek B server’ında bulunan bu tablonun A server’ından select çekilebilmesini sağlıyoruz.

A server’ında create edilecek view;

create view myView
with encryption
as
SELECT *
FROM OPENDATASOURCE('SQLNCLI','Data Source=ServerB;User ID=LoginName;Password=password'
		    ).AdventureWorks.Person.Address

 

Gördüğünüz gibi view create’in de with encryption keyword’unu kullandık. Bunun sebebi de tanımlamış olduğumuz kullanıcının kullanıcı adı ve şifre bilgilerini göstermek istemememiz.

Şimdi update işlemini nasıl yapabiliriz diye bakalım.

Bunun için gene encrypted olarak bir Stored Procedure (SP) create edeceğiz.

create proc sp_UpdPersonAddress
	@AddressID int,
	@PostalCode nvarchar(15)
with encryption
as
UPDATE OPENDATASOURCE('SQLNCLI','Data Source=ServerB;User ID=LoginName;Password=password'
		   ).AdventureWorks.Person.Address
SET PostalCode = @PostalCode
where AddressID=@AddressID

 

Bu update SP’si vasıtasıyla B Server’ında bulunan Person.Address tablosunun PostaCode kolonunu A server’ı üzerinden update edebiliriz.

 

İyi Çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SP ve Function’lar gibi bir çok yerde kullanılmakta olan bir tablom var ve ben bu tabloda kolon ekleme ya da silme gibi structure değişikliği yapmak istiyorum. Değişikliği yapmadan önce hangi objeler bu tablomu kullanıyor bilmeliyim ki sistemi nasıl etkileyeceğimi kestirebileyim.Ya da tanımladığım bir user-defined datatype’ım var. Bu datatype’ta değişiklik yapmak istiyorum ama hangi objeler bu datatype’ı kullanıyor öğrenmek istiyorum.

Bugünkü makalemde bu sorularımıza cevap bulmak için SQL Server’da Dependency sorgularının DMV’ler ile nasıl yapıldığına bakıyor olacağız.

[more]

SQL Server 2008 ile beraber Dependency kontrollerini yapabilmek amaçlı 2 DMF ve 1 Catalog View geldi. Bu DMF ve Catalog View’lar ve açıklamaları aşağıdaki gibi.

  • sys.dm_sql_referencing_entities : Parametre olarak verilen objenin hangi objelerde kullanıldığını yani hangi objelerin bu parametre olarak verilen objeye dependent olduğunu sorgular.
  • sys.dm_sql_referenced_entities : Parametre olarak verilen objenin hangi objeleri kullandığını yani hangi objelere dependent olduğu sorgular.
  • sys.sql_expression_dependencies : Bir database için tüm dependeny listesini getirir.

SQL Server 2008 database engine objelerin dependency’lerini otomatik olarak kontrol etmekte ve yapılan drop,alter,create işlemleri neticesinde bunları sytem catalog view’lerinde saklamaktadır. Bizde yukarıdaki DMF’leri kullanarak bu bilgilere erişebilmekteyiz.

Şimdi gelin bir çalışma ortamı hazırlayalım ve dependency’leri nasıl sorgulayabildiğimize bakalım.

Çalışma ortamımız için 1 datatype, 1 table ve bu objeleri kullanan 1 SP ve 1 function create edeceğiz.

--Çalışma ortamı için bir DB Create edelim
CREATE DATABASE DBDependency
GO

USE DBDependency
GO

--FullName VarChar(200) şeklinde yeni bir data type tanımlayalım.
CREATE TYPE dbo.FullName FROM VARCHAR(200) NOT NULL
GO

--Customer tablosunu create edelim.
CREATE TABLE dbo.Customer (ID int, Name varchar(100), SurName Varchar(100))
GO

--Parametre olarak verilen FullName bilgisine uyan kaç kayıt olduğunu döndüren function
--Customer tablosuna ve FullName dateype'ına dependent
CREATE FUNCTION dbo.FullNameSearchCount
	(@ParamFullName dbo.FullName)
RETURNS INT
AS
BEGIN
  DECLARE @ReturnValue int=0
  
  SELECT @ReturnValue = Count(Name)
  FROM dbo.Customer WHERE Name + ' ' + Surname = @ParamFullName  
  
  Return(ISNULL(@ReturnValue,0))
END

GO

--ID parametresi ile verilen müşterinin Full adını döndüren Stored Procedure
--Customer tablosuna ve FullName dateype'ına dependent
CREATE PROCEDURE dbo.CustomerFullName 
	@ID int
AS
  DECLARE @ResultFullName dbo.FullName
   
  SELECT @ResultFullName = Name + ' ' + SurName 
  FROM dbo.Customer WHERE ID = @ID

  SELECT @ResultFullName
GO  

 

Çalışma ortamı tanımlamalarımız bitti. Yaptığımız tanımlamaları bir daha gözden geçirelim.

FullName adında bir datatype, Customer adında bir tablo create ettik. Daha sonra FullNameSearchCount adında bir function create ettik ve bu function’da FullName ve Customer objelerini kullandık. Aynı şekilde CustomerFullName adında bir SP create ettik ve bu SP’de de FullName ve Customer objelerini kullandık.

Şimdi dependency sorgulamalarımıza geçelim.

 

Bir DataType’a Dependent Olan Objeler

Bu sorgumuzda FullName DataType’ına dependent olan objeleri sorgulayacağız.

select re.referencing_class Class,
    re.referencing_class_desc as ClassDesc,
	o.type_desc as ObjectType,
	re.referencing_schema_name as SchemaName,
	re.referencing_entity_name as ObjectName,
	re.referencing_id as ObjectID
from sys.dm_sql_referencing_entities ('dbo.FullName', 'type') re
left join sys.objects o on o.object_id = re.referencing_id

 

1


Sorgu sonucunda gördüğünüz gibi CustomerFullName SP’si ve FullNameSearchCount Function’ı FullName DataType’ına dependent’mış. Yani bu objelerde FullName DataType’ı kullanılmış.

 

Bir Table’a Dependent Olan Objeler

Bu sorgumuzda Customer Table’ına dependent olan objeleri sorgulayacağız.

select referencing_class as Class,
    re.referencing_class_desc as ClassDesc,
	o.type_desc as ObjectType,
	re.referencing_schema_name as SchemaName,
	re.referencing_entity_name as ObjectName,
	re.referencing_id as ObjectID
from sys.dm_sql_referencing_entities ('dbo.Customer', 'Object') re
left join sys.objects o on o.object_id = re.referencing_id

 

2


Sorgu sonucunda Customer tablosunu CustomerFullName SP’sinin ve FullNameSearchCount Function’ının kullanmakta olduğunu görüyoruz. Dolayısıyla bu tabloda bir değişikliğe gitmeden önce bu objeleri kontrol etmekte fayda olduğu sonucuna varıyoruz.

 

Bir SP’nin Dependent Olduğu Objeler

Şimdi sorgumuzu tersten çekeceğiz. Bu sorgumuzda CustomerFullName SP’sinin hangi objeleri kullandığına yani hangi objelere dependent olduğuna bakacağız.

select re.referenced_class_desc,
	   re.referenced_schema_name,
	   re.referenced_entity_name,
	   re.referenced_minor_name	   	   
from sys.dm_sql_referenced_entities('dbo.CustomerFullName','object') re

 

3


Sorgu sonucunda gördüğümüz gibi CustomerFullName SP’si Customer Table’ını ve FullName DataType’ını kullanmakta. Ayrıca Customer Table’ından hangi kolonları spesifik olarak kullandığını da görmemiz mümkün.

 

Bir Database’deki Tüm Dependency’leri Sorgulama

Yukarıdaki sorgularda DMF’leri kullandığımız için parametre vererek sorgu çekmek durumunda kalmıştık. Bir database’de bulunan bütün Dependency’leri ise sys.sql_expression_dependencies catalog view’ına select çekerek sorgulayabiliriz.

select referencing_class_desc,
	   o1.type_desc as referencing_object_type,   
       SCHEMA_NAME(o1.schema_id)+'.'+o1.name as referencing_entity_name,
	   referenced_class_desc,
	   o2.type_desc as ReferencedObjectType,   
	   ed.referenced_schema_name+'.'+ed.referenced_entity_name as referenced_entity_name
from sys.sql_expression_dependencies ed
left join sys.objects o1 on o1.object_id = ed.referencing_id
left join sys.objects o2 on o2.object_id = ed.referenced_id

 

4

Bu sorgumuzda da çalışma ortamı database’imizde bulunan bütün Dependency’leri sorguladık. Sorgu sonucunda örneğin CustomerFullName SP’sinin Customer Table’ına ve FullName DataType’ına Dependent olduğunu görebiliyoruz.

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server execute edilen procedure, trigger ya da adhoc query’ler için oluşturduğu Query Plan’larını saklar ve daha sonraki kullanımlarda bu query plan’ları kullanarak işlemin daha hızlı sonuçlanmasını sağlar.

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.

[more]

select * from sys.dm_exec_cached_plans 

 

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

select cp.usecounts,
	   st.text,
	   qp.query_plan,	   
	   cp.cacheobjtype,
	   cp.objtype,
	   cp.size_in_bytes
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) qp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where cp.usecounts>1
order by cp.usecounts desc

 

1

 

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.

Sorgumuzu çekelim.

select DB_NAME(st.dbid) as DBName,
	   OBJECT_SCHEMA_NAME(st.objectid,st.dbid) as SchemaName,
	   OBJECT_NAME(st.objectid,st.dbid) as ObjectName,
	   st.text,
	   qp.query_plan,
	   cp.usecounts,
	   cp.size_in_bytes
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) qp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where st.dbid<>32767 --Resource DB'yi exclude ediyoruz.
   and cp.objtype='Proc'

 

2


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.


3

 

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
from sys.dm_exec_cached_plans cp
group by cp.objtype
order by 2 desc

 

4


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.

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


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.

[more]

select * from sys.dm_exec_procedure_stats

 

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.
http://www.turgaysahtiyan.com/post/SQL-Server-e28093-Her-Gun-1-DMW-Gun-4-sysdm_exec_query_stats-ile-Query-Istatistikleri.aspx

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.

select top 50 
   DB_NAME(database_id) as DBName,
   OBJECT_NAME(object_id) as SPName,
   st.[text] as SPCode,
   qp.query_plan,
   cached_time as first_execution_time,
   last_execution_time,
   execution_count,
   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
from sys.dm_exec_procedure_stats ps
cross apply sys.dm_exec_sql_text(ps.plan_handle) st
cross apply sys.dm_exec_query_plan(ps.plan_handle) qp
where DB_NAME(database_id)='AdventureWorks'
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.


1

2


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.

select SCHEMA_NAME(o.schema_id) as SchemaName,
       p.name as ObjectName, 
       p.create_date,
       p.modify_date
from sys.procedures p
left join sys.objects o on p.object_id=o.object_id
where p.type='P'
	and not exists(Select ps.object_id 
					from sys.dm_exec_procedure_stats ps 
					where ps.object_id=p.object_id
					  and ps.database_id=DB_ID('AdventureWorks')
				  )
order by SCHEMA_NAME(o.schema_id),p.name

 

Bu sorgu size AdventureWorks DB’si için kullanılmayan SP’leri sorgulayacaktır.


3


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.

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bugün size bir çoğumuzun bilip hali hazırda kullanmakta olduğunu düşündüğüm bir konudan bahsetmek istiyorum. Belki hala bu özelliği kullanmayan vardır diyerek konuya giriyorum.

Bugünkü konumuz SQL Server Management Studio Object Explorer da bulunan filter özelliği.

[more]

Object Explorer filter özelliği, DB objeleri üzerinde çalışırken objeleri daha rahat bulmaya yarayan bir Management Studio özelliğidir.

Örneğin bir production DB nizde 10 bin den fazla table var ve siz table listesinden bir table a erişmek istiyorsunuz.

Zaten hali hazırda bu listeyi görüntüleyebilmek bile her makinanın harcı değilken birde bu listeden table ı bulmaya çalıştığınızı düşünün.

image

Object Explorer filter özelliği ile table listesinde ada yada schema adına göre filtreleme yaparak sorgu sonucunu kısaltıp erişmek istediğiniz objeye daha hızlı erişebilirsiniz.

Örneğin AdventureWorks DB sinde Production.Location tablo suna filter özelliği ile hızlıca erişmek istiyorum.

Bunun için Object Explorer da Tables node unda sağ tuşa tıklayıp Filter >> Filter Settings kısmına geçiyoruz.

image

Gelen ekranda aşağıdaki bilgiler ile arama yada filtreleme yapabiliyoruz.

  • Name – Aranan objenin adına göre filtreleme. Equal(Eşit), Contains(İçeren) yada Does Not Contain (İçermeyen) parametreleri ile sorgulama yapılabilmekte.
  • Schema – Aranan objenin schema adına göre filtreleme. Equal(Eşit), Contains(İçeren) yada Does Not Contain (İçermeyen) parametreleri ile sorgulama yapılabilmekte.
  • Owner – Aranan objenin owner ına göre filtreleme. Equal(Eşit) yada Does Not Contain (İçermeyen) parametreleri ile sorgulama yapılabilmekte.
  • Creation Date – Arana objenin oluşturulma tarihine göre filtreleme.

Biz örneğimizde Production.Location table ını arıyoruz. Bunun için filter ekranını aşağıdaki gibi dolduruyoruz.

image

Daha sonra Ok e bastığımızda table kısmını filterelemiş ve obje mize eriştiğimizi göreceğiz.

image

Yapılmış olan filtreyi Sağ tık ta açılan pencereden Filter >> Remove Filter dan kaldırabiliriz.

image

Basit ama önemli bir upucu olduğunu düşünüyorum.

Bu tarz filtreleme işlemlerinin Table, View, Stored Procedure, Function gibi objelerin tamamında uygulanabildiğini not düşerek yazıma son veriyorum.

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


http://www.turgaysahtiyan.com/post/SQL-Server-e28093-SP%28Stored-Procedure%29-lere-Topluca-Yetki-Verme.aspx adresindeki yazımda Stored Procedure lere topluca nasıl yetki verilebileceğini anlatmıştım. Şimdi ise geçenlerde yazdığım bir başka SP den bahsedeğim. Bu SP ile sadece SP lere değil server da ki tüm objelere (table,function,view vs) topluca yetki verebilirsiniz.

SP şu şekilde.

CREATE PROC GivePermissionForAllObjects
	@UserName varchar(max) = '' --Yetki verilmek istenen user ya da role
	,@Databases varchar(Max) = '' --Yetki verilmek istenen DB ya da DB ler, Boş bırakılırsa bütün DB ler
	,@GrantDeny varchar(Max) = 'DENY' --GRANT, DENY, REVOKE
	,@PermissionType varchar(Max) = 'EXECUTE' --Alter, Execute, View Definition vs.
	,@ObjectType varchar(2)='' --SP,View,Function vs.
AS
if (isnull(@ObjectType,'')='') or (isnull(@ObjectType,'')='?') begin
print '
--Object Types
--FN	SQL_SCALAR_FUNCTION
--C 	CHECK_CONSTRAINT
--UQ	UNIQUE_CONSTRAINT
--SQ	SERVICE_QUEUE
--U 	USER_TABLE
--D 	DEFAULT_CONSTRAINT
--PK	PRIMARY_KEY_CONSTRAINT
--V 	VIEW
--S 	SYSTEM_TABLE
--IT	INTERNAL_TABLE
--P 	SQL_STORED_PROCEDURE
--TF	SQL_TABLE_VALUED_FUNCTION
--TR	SQL_TRIGGER
--PC	CLR_STORED_PROCEDURE
--
'
Return
end
SET NOCOUNT ON
--variables
declare @DatabasesTmp varchar(max)=@Databases
declare @DBName sysname,@ProcName sysname,@SchemaName sysname
declare @sql varchar(max)
Declare @Tbl_DB Table(DB varchar(Max))
Create Table ##Prodecures(DBName sysname, ProcName sysname, SchemaName sysname)
--

--Split Databases
if @DatabasesTmp<>'' begin
	while @DatabasesTmp <> '' begin
		if CHARINDEX(',',@DatabasesTmp)>0 begin
			insert @Tbl_DB select SUBSTRING(@DatabasesTmp,1,CHARINDEX(',',@DatabasesTmp)-1)
			set @DatabasesTmp = SUBSTRING(@DatabasesTmp,CHARINDEX(',',@DatabasesTmp)+1,LEN(@DatabasesTmp))
		end else begin
			insert @Tbl_DB select @DatabasesTmp
			set @DatabasesTmp = ''
		end
	end
end else begin
	insert @Tbl_DB select name from sys.databases where database_id>4
end
--

--code
declare CursorX cursor for 
select DB from @Tbl_DB
open Cursorx
fetch from Cursorx into @DBName
while @@FETCH_STATUS=0
begin
	set @sql = 'Use ' +@DBName + '; '+CHAR(10)+'
		insert ##Prodecures
		select '''+@DBName+''',name,SCHEMA_NAME(schema_id) from sys.objects where type='''+@ObjectType+''' '
	exec(@sql)
	fetch next from Cursorx into @DBName	
end
close Cursorx
deallocate Cursorx

declare CursorY cursor for 
select DBName,ProcName,SchemaName from ##Prodecures
open CursorY
fetch from CursorY into @DBName,@ProcName,@SchemaName
while @@FETCH_STATUS=0
begin
	set @sql = 'use ['+ @DBName + ']; ' +
		@GrantDeny+' '+@PermissionType+' ON ['+@SchemaName+'].['+@ProcName+'] TO ['+@UserName+'] AS [dbo]; '
	exec(@sql)
  fetch next from CursorY into @DBName, @ProcName, @SchemaName
end
close CursorY
deallocate CursorY

Drop Table ##Prodecures

SET NOCOUNT OFF
--code

 

Object Type için kullanılabilecek parametreler aşağıdaki gibidir.

FN SQL_SCALAR_FUNCTION
C CHECK_CONSTRAINT
UQ UNIQUE_CONSTRAINT
SQ SERVICE_QUEUE
U USER_TABLE
D DEFAULT_CONSTRAINT
PK PRIMARY_KEY_CONSTRAINT
V VIEW
S SYSTEM_TABLE
IT INTERNAL_TABLE
P SQL_STORED_PROCEDURE
TF SQL_TABLE_VALUED_FUNCTION
TR SQL_TRIGGER
PC CLR_STORED_PROCEDURE

 

Bir kaç örnek vermek gerekirse;

turgay user ına bütün AdventureWorks ve AdventureWorks2008 DB lerinde ki bütün table lar için SELECT yetkisi vermek.

exec GivePermissionForAllObjects 'turgay','AdventureWorks,AdventureWorks2008','GRANT','SELECT','U'

 

turgay user ıne bütün DB lerde ki Stored Procedure ler için EXECUTE yetkisi vermek.

exec GivePermissionForAllObjects 'turgay','','GRANT','EXECUTE','P'

 

turgay user ına bütün DB lerde verilmiş View SELECT hakkını geri almak. (REVOKE)

exec GivePermissionForAllObjects 'turgay','','REVOKE','SELECT','V'

 

turgay user ı bütün DB lerde Sistem Table larını Inherit alsa dahi SELECT çekemesin

exec GivePermissionForAllObjects 'turgay','','DENY','SELECT','S'

 

Bu şekilde istediğiniz DB de ki objelere topluca yetki verebilir yada geri alabilirsiniz.

 

İyi çalışmalar.

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Geçenlerde yazmış olduğum bir SP yi sizinle paylaşmak istiyorum.

SP nin yaptığı iş, çok fazla sayıda DB içeren bir server da verdiğiniz kelimeyi SP name lerinde arayıp hangi DB lerde bu isme uyan SP ler varsa bunları listelemek.

CREATE PROC SearchSPsByName 
    @NameLike varchar(max) 
AS 

declare @DBName sysname,@sql varchar(max) 
create table ##TblResult(DBName sysname,SPName sysname) 

declare CursorX cursor for 
select name from sys.databases where database_id>4 
open Cursorx 
fetch from Cursorx into @DBName 
while @@FETCH_STATUS=0 
begin 
    set @sql = 'insert ##TblResult 
        select '''+@DBName+''',name from '+@DBName+'.sys.procedures where name like '''+@NameLike+''' ' 
    exec(@sql) 
    fetch next from Cursorx into @DBName    
end 
close Cursorx 
deallocate Cursorx 

select * from ##TblResult 

drop table ##TBlResult 
-- 

GO 

--Kullanımı

exec INTERFACE.dbo.SearchSPsByName 'CAD_RECONCIL_INTERFACE_PREPARE_REPORTS'

 

Kolay gelsin.

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Merhaba arkadaşlar,

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.

Stored Procedure:

/*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]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
/*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*/
With Encryption
as set nocount on begin tran  
  /*insert işlemi*/
  insert jobs
    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.*/
  set @JobID_Dest=IDENT_CURRENT('Jobs')
commit tran
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Kullanımı ise şu şekilde:

declare @JobID_Dest int
select @JobID_Dest=0
exec dbo.Job2Job 1,@JobID_Dest output
select @JobID_Dest

Sormak istediğiniz bir şey olursa yorum kısmını kullanabilirsiniz.

Kolay gelsin

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan