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
<<  Aralık 2017  >>
PaSaÇaPeCuCuPa
27282930123
45678910
11121314151617
18192021222324
25262728293031
1234567
Keywords

Geçenlerde bana sorulan güzel bir soru üzerine bu makaleyi yazmaya karar verdim. Soru şu şekildeydi;

“En çok CPU tüketen sorgularımı kontrol etmek için sys.dm_exec_query_stats DMV’sine sql_handle kolonu üzerinden sys.dm_exec_sql_text’i join’lediğim zaman bazı satırların database bilgisi null gelmekte. Bunun sebebi nedir?”

[more]

Soruda ne demek istendiğine bir bakalım.

Daha önce şu makalemde anlattığım “en çok CPU tüketen ilk 50 sorgu” sorgusunu instance’ımda çalıştırdığımda

1

Bazı satırların database_name kolonu boş gelmekte. Bunun sebebi nedir?

Yaptığım araştırmalar neticesinde bunun sebebinin sql_handle’dan kaynaklandığı sonuca vardım.

SQL Server Database Engine farklı database’lerde çalıştırılmış aynı Ad-Hoc query’ler için aynı sql_handle’ı üretmekte. Yani sql_handle içinde hangi DB üzerinde çalıştırıldığı bilgisi tutulmamakta. Dolayısıyla sql_handle’ı kullanarak database_name bilgisini almaya çalıştığımızda bu bilgi tutulmadığı için null değer dönmekte.

SP kullanımında ise bir sıkıntı gözükmemekte. SP’ler için oluşturulmuş sql_handle’lardan database adına erişebilmekteyiz.

Bir örnek yaparak ne demek istediğimizi daha net hale getirelim.

Üzerinde çalışmak için 2 tane DB create ediyoruz.

--2 Test DB'si create edelim.
create database Deneme1
GO
create database Deneme2
GO

 

Her DB üzerinde 1 table ve 2 SP create ediyoruz. Bu SP’lerden ilkini normal şekilde execute edeceğiz, diğerini ise dynamic olarak execute edeceğiz ve dynamic execute’de herhangi bir farklılık olup olmadığını analiz etmeye çalışacağız.

--1. DB'de 1 tablo ve bu tabloya select çeken 2 tane SP
use Deneme1
GO
Create table TblDnm1 (Col1 varchar(10))
GO
Create Proc spDeneme1 @Col1 varchar(10)
as
  Select * from TblDnm1 where Col1=@Col1
GO
Create Proc spDeneme2 @Col1 varchar(10)
as
  Select * from TblDnm1 where Col1=@Col1
GO
--2. DB'de ayni islemler
use Deneme2
GO
Create table TblDnm1 (Col1 varchar(10))
GO
Create Proc spDeneme1 @Col1 varchar(10)
as
  Select * from TblDnm1 where Col1=@Col1
GO
Create Proc spDeneme2 @Col1 varchar(10)
as
  Select * from TblDnm1 where Col1=@Col1
GO

 

Her 2 DB’de de tabloya select, 1.SP’ye execute ve 2.SP’ye dynamic execute yapıyoruz.

--1.DB
Use Deneme1
GO
Select * from TblDnm1
GO
exec spDeneme1 'a'
GO
sp_executesql N'exec spDeneme2 ''a'''
GO

--2.DB
Use Deneme2
GO
Select * from TblDnm1
GO
exec spDeneme1 'a'
GO
sp_executesql N'exec spDeneme2 ''c'''
GO

 

Ve şimdi sys.dm_exec_query_stats DMV’sine bakıyoruz. DBName’i hem sys.dm_exec_sql_text’ten hem de sys.dm_exec_query_plan’dan almaya çalışıyoruz.

--sys.dm_exec_query_stats'i sorgulayalim.
select st.text,qs.sql_handle,qs.plan_handle 
   ,DB_NAME(st.dbid) as DBName_sql_text
   ,DB_NAME(qp.dbid) as DBName_query_plan
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
where (st.text not like '%select * from sys.dm_exec_query_stats%')
  and (st.text like '%Select * from TblDnm1%'
  or st.text like '%exec spDenem1%'
  or st.text like '%exec spDenem2%')

 

2

Gördüğünüz gibi farklı DB’lerde çalışan “Select * from TblDnm1” Ad-Hoc sorgusu için bire bir aynı sql_handle create edildi. Ve bu sorgular için ne sys.dm_exec_sql_text’ten ne de sys.dm_exec_query_plan’dan database adını ulaşamadık.

SP’lerde ise normal çalıştırılsa da dynamic olarak çalıştırılsa da bir sıkıntı olmadığını görüyoruz.

Peki Ad-Hoc query’ler için database adını öğrenebilmekten feragat mı edeceğiz. İşte burada yardımımıza sys.dm_exec_plan_attributes DMF’si yetişmekte.

Bu DMF’yi kullanarak istediğimiz sonuca erişebiliriz. Üstteki sorgunun sys.dm_exec_plan_attributes eklenmiş hali aşağıdadır.

select st.text,qs.sql_handle,qs.plan_handle 
   ,DB_NAME(st.dbid) as DBName_sql_text
   ,DB_NAME(qp.dbid) as DBName_query_plan
   ,DB_NAME(cast(isnull(pa.value,0) as int)) as DBName_plan_attributes
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
outer apply sys.dm_exec_plan_attributes(qs.plan_handle) pa
where (pa.attribute='dbid') 
  and (st.text not like '%select * from sys.dm_exec_query_stats%')
  and (st.text like '%Select * from TblDnm1%'
  or st.text like '%exec spDenem1%'
  or st.text like '%exec spDenem2%')

 

3

Gördüğünüz gibi Ad-Hoc query’ler için de database adı bilgilerine erişebildik.

Dolayısıyla daha önce şu makalemde yazdığım “En çok CPU tüketen ilk 50 sorgu” sorgusuda şu şekilde değişmiş oldu.

select
     q.[text],
     SUBSTRING(q.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(q.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS statement_text,       
     qs.last_execution_time,
     qs.execution_count,
     qs.total_logical_reads as total_logical_read,
     qs.total_logical_reads/execution_count as avg_logical_read,
     qs.total_worker_time/1000000 as total_cpu_time_sn,
     qs.total_worker_time/qs.execution_count/1000 as avg_cpu_time_ms,
     qp.query_plan,
     COALESCE(DB_NAME(q.dbid),DB_NAME(CAST(pa.value as int))+'*') AS database_name,
     q.objectid,
     q.number,
     q.encrypted
from
    (select top 50
          qs.last_execution_time,
          qs.execution_count,
          qs.plan_handle,
          qs.total_worker_time,
          qs.total_logical_reads,
          qs.statement_start_offset,
          qs.statement_end_offset
    from sys.dm_exec_query_stats qs
    order by qs.total_worker_time desc) qs
cross apply sys.dm_exec_sql_text(plan_handle) q
cross apply sys.dm_exec_query_plan(plan_handle) qp
outer apply sys.dm_exec_plan_attributes(qs.plan_handle) pa
where (pa.attribute='dbid')
order by qs.total_logical_reads desc

 

4

Yanlarında * olan database adları sys.dm_exec_plan_attributes vasıtasıyla bulunan değerlerdir.

Son olarak yazımın başında create ettiğim DB’leri drop ederek yazımı noktalıyorum.

--create ettigimiz DB'leri drop ediyoruz.
drop database Deneme1
GO
drop database Deneme2
GO

 

İ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


28 Aralık Salı günü yapacağım webcast’in duyurusunu yapmak istiyorum.

DMV(Dynamic Management View)’ler ile SQL Server Database Sunucularını İzleme başlıklı webcast’im 28.12.2010 tarihinde 21:00-22:00 saatleri arasında gerçekleşecek.

Aşağıdaki url’i kullanarak vereceğim bu webcast’e katılabilirsiniz.

https://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032468645&EventCategory=4&culture=tr-TR&CountryCode=TR

Hepinizi bekliyorum.

[more]

DMV(Dynamic Management View)’ler ile SQL Server Database Sunucularını İzleme

Ajanda

  • DMV-DMF Nedir?
  • Ne Amaçla Kullanılır?
  • DMV-DMF Farkı Nedir?
  • Nasıl Bir Yetki Gereklidir?
  • Tüm DMV – DMF Listesi
  • Örnekler
  • Soru – Cevap

DMV – DMF Nedir?

SQL Server da Dynamic Management View(DMV) ve Dynamic Management Function’lar (DMF), sistemi monitor etmek, herhangi bir problemi teşhis etmek veya sistemi daha performanslı çalışması için tune etmek için kullanılan, sistem durumu hakkında bilgi dönen view ve function’lardır.

Ne Amaçla Kullanılır?

  • Şu an devam eden işler ve çalışan query’ler nedir?
  • Genel olarak SQL Server’ın işi yapmak için beklediği noktalar nereler?
  • Network IO’larında bekleme var mı?
  • Disklerin response time’ları nasıl?
  • Şu an diskte bekleyen herhangi bir IO işlemi var mı?
  • Eksik index’lerimden dolayı mı performans sıkıntım var?
  • Bu eksik index’ler nelerdir?
  • Index’lerimin scan,seek yani kısaca kullanım oranları nelerdir?
  • En çok CPU ya da IO tüketen sorgularım hangileri?

Geri kalan kısmı webcast’te :)

 

İ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


İş hayatının vazgeçilmezi(!) toplantılarda neler yapılmamalı?

Aşağıdaki listede sorumuza cevap buluyoruz.

What not to do during meetings?

  • Resenting questions
  • Monopolizing the meeting
  • Playing the role of comic
  • Publicly chastising participants
  • Allowing interruptions
  • Losing control of the agenda
  • Being unprepared

 

İ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


turgay , 26. Aralık 2010, 10:00

Toplantınzın verimli geçmesini istiyorsanız zamanlamasını çok iyi ayarlamanız gerekiyor. Kendini toplantıya vermeyen ya da veremeyen bir toplulukla sonuca erişmek neredeyse imkansız gibi.

İyi bir toplantı zamanlamasının nasıl olması gerektiğini aşağıdaki maddeler eşliğinde sıralayabiliriz.

The Timing of Meetings

  • Meet when participants are at their best-not on Monday mornings or Friday afternoons or just after lunch
  • Start at an unusual time to stop at a natural break point such as 11.40 for a 20 min. meeting
  • Allow sufficient time for participants to prepare
  • Avoid surprise meetings, nobody likes interruptions and they will enter in with a negative attitude.
  • If your meeting is crowded hold it short if else vice versa.
  • If your meeting exceeds two hourse include breaks and make up after the break
  • If the meeting has to go all day include a 1.5 or 2 hour lunch and keep afternoon sessions shorter

 

İ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


turgay , 26. Aralık 2010, 09:30

İllaki hepimizin başına en az bir kez gelmiştir. Uzun saatler boyunca toplantı yapılır ama bitiminde bir de bakarsın ki hiç bir sonuca erişilememiş.

Management dersinde gördüğümüz kadarıyla bir toplantının fail etme sebepleri şunlarmış;

Why Meetings Fail?

  • Lack of notification-No in-advance notice, limited time for preperation
  • No agenda-No in-advance hand-outs, no precise purpose(s)
  • Wrong Attendance-People attending are not relevant
  • Lack of control-No influence over decision making process
  • Political Pressures-Feeling pressure abour expression of critical accounts
  • Hidden Agendas-There are underlying issues which take over the meeting so that meeting is made to fail.

Bana çok mantıklı geldi. Ne dersiniz?

 

İ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


turgay , 26. Aralık 2010, 09:00

Birçoğunuzun bildiği gibi hali hazırda İTÜ Executive MBA programına devam etmekteyim ve dün itibarıyla ilk dönemi bitirdik.

Bugün itibarıyla bu eğitim programında gördüğüm bazı güzel şeyleri, yaptığım ödevleri vs. paylaşmaya başlayacağım.

Umarım aralarından işinize yarayacak birşeyler çıkar.

 

İ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


Microsoft SQL Server 2005 SP4 RTM’i yayınlandı.

Bu SP, SP3 ve daha sonra çıkan 11 tane cumulative update’i içermekte.

Bütün edition’lar için kullanılabilecek setup dosyasını aşağıdaki url’den indirebilirsiniz.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b953e84f-9307-405e-bceb-47bd345baece

 

İ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


turgay , 17. Aralık 2010, 08:30

13 Haziran 2010 tarihinde gene bu sayfalardan 100. Makale duyurusunu yapmıştım.

Bugün itibarıyla ise 200. SQL Server makalemi yayınlamış bulunuyorum.

Bu 6 aylık süreç zarfında makalelerime ilham olacak sorular soran, değerlendiren, yorum yapan herkese teşekkür ediyorum.

 

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


Merhaba arkadaşlar

Bu makalede içerisinde transaction bulunan bir insert (update ya da delete’te olabilir) stored procedure’ün tetiklediği cümlelerin ne zaman çalışacaklarını inceleyeceğiz.

[more]

Bilindiği gibi trigger’lar for ve instead of olmak üzere ikiye ayrılırlar. For trigger’ı tablo üstünde işlem yapıldığı anda çalışır. Instead of trigger’ı ise For’dan farklı olarak, tablo üstünde yapılmak istenen işlem yerine tetiklenir.

Instead Of trigger’ına örnek vermek gerekirse;

Bir adet x tablosuna kayıt yapan stored procedure’ümüz bir adette x tablosu için instead of trigger’ımız olsun.

Kayıt yapan stored procedure’ü çalıştırdığımız zaman stored procedure içindeki insert kodu yerine instead of trigger’ı içindeki kod çalışır. Dolayısı ile bu stored procedure’ümüz sadece instead of trigger’ını tetiklemeye yarayacaktır.

Kısa bir özetten sonra konumuza dönecek olursak; basit bir örnek yapalım. İki adet tablomuz ve şu şekilde bir stored procedure’ümüz olsun;

INSERT INTO TEST2 (test,DTDATE) VALUES ('TR DEN ÖNCE',GETDATE())

BEGIN TRANSACTION trTest

INSERT INTO TEST (test,DTDATE) VALUES ('TR ARASINDA SP DE',GETDATE())

COMMIT TRANSACTION trTest          

INSERT INTO TEST2 (test,DTDATE) VALUES ('TR DEN SONRA',GETDATE())

 

Test tablomuzun iki adette trigger’ı olsun biri For diğeri de Instead Of ve içerikleri de

INSERT INTO TEST (test,DTDATE) VALUES ('INSTEAD OF',GETDATE())

INSERT INTO TEST (test,DTDATE) VALUES ('FOR’,GETDATE())

 

Çıkan sonuç aşağıdaki gibidir.

makale1

makale2

Görüldüğü gibi TEST2 tablosuna ‘TR DEN ÖNCE’ ve ‘TR DEN SONRA’ cümleleri kayıt oldu. TEST tablosuna ise INSTEAD OF trigger kullandığımız için stored procedure içindeki cümle kayıt olmadı. Bunun yerine triggerlardan gelen önce INSTEAD OF trigger’ı içindeki sonra da FOR trigger’ı (ikisinin aynı anda kayıt olduğunu da gözlemlemiştim) içindeki cümle kayıt oldu.

Burada iki adet tablo kullanmamızın sebebi; hem Instead of hem de For Trigger’ı kullandığımız için, bu iki trigger içerisinde de insert cümleleri bulunduğundan dolayı birbirlerini tetikleyecekler ve stored procedure içindeki cümle çalıştırılmadan aşağıdaki gibi kayıt yapacak.

makale3

 

İyi Çalışmalar

Yunus Emre Kırkanahtar

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 2005 kurulu sunucularımızdan birinde SSMS üzerinden error log’a erişmeye çalıştığımızda aşağıdaki hatayı almaktaydık.

Error: 18456, Severity: 14, State: 11.

Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. [CLIENT: <local machine>]

[more]

err1

SQL Server Service’i 'NT AUTHORITY\NETWORK SERVICE' account’u ile çalışmaktaydı. Yaptığımız incelemeler sonucunda bu account’un SQL Server’da sysAdmin olarak yetkili olmadığını gördük.

'NT AUTHORITY\NETWORK SERVICE' account’unu SQL Server’a sysAdmin olarak ekledikten sonra error log’u problemsiz olarak görüntülemeye başladık.

 

İ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 – Her Gün 1 DMV - Gün 15 – DMV’ler ile Eksik Index Sorgulama makalemde sistemde olmayan ama olması tavsiye edilen indexleri nasıl sorgulayabileceğimizi incelemiştik.

Bugünkü makalemde ise belirlediğimiz bu missing index’leri adım adım analiz edip create edip etmemeye karar vereceğiz. Ve create ettikten sonrada nasıl monitor edebileceğimizi görüyor olacağız. Yani baştan sona bir missing index operasyonunun nasıl yapıldığını görüyor olacağız.

[more]

Makale şu alt başlıklar altında yer alacaktır;

  1. Missing Index’lerin sorgulanması ve create edilecek index’e karar verilmesi.
  2. Create edilmesi düşünülen index’in ve bulunduğu tablonun analizi
    1. Tablo kayıt sayısı ve boyut analizi
    2. Tablonun üzerinde bulunan diğer index’lerin kullanım istatistikleri
    3. Tablonun üzerinde bulunan index’lerin hangi kolonlar üzerinde bulunduklarının kontrolü
    4. Tablonun üzerinde bulunan index’lerin Include Column içerip içermediklerinin kontrolü
  3. Tablonun query stats’larına bakılıp ilgili missing index’e sebep olan script’in bulunması.
  4. Bulunan script’in şu anki IO değerlerinin sorgulanması.
  5. Index’in create edilmesi
  6. Sorgunun IO değerine tekrar bakılması ve ne kadar düştüğünün incelenmesi.
  7. Create edilen index’in kullanım istatistiklerinin monitor edilmesi

 

1. Missing Index’lerin sorgulanması ve create edilecek index’e karar verilmesi

Missing Index’lerin nasıl sorgulanabileceği ile ilgili detay bilgiye şu makaleden erişebilirsiniz. Bu makalemde detaya girmeden direk missing index sorgusunu çalıştıracağız.

Daha öncesinde bir Missing Index durumu oluşturmak için AdventureWorks te bulunan Person.Address tablosuna City ve PostalCode üzerinden select çekelim. Bu kolonlar üzerinde tanımla olan bir index olmadığı için bir missing index kaydı oluşacaktır.

--Aşağıdaki select'i birden fazla kez çalıştırınız
select * from Person.Address where City='Duvall' and PostalCode='98019'

 

Şimdi AdventureWorks DB’si için Missing Index sorgumuzu çalıştıralım.

select DB_NAME(id.database_id) as databaseName,
	id.statement as TableName,
	id.equality_columns,
	id.inequality_columns,
	id.included_columns,
	gs.last_user_seek,
	gs.user_seeks,
	gs.last_user_scan,
	gs.user_scans,
	gs.avg_total_user_cost * gs.avg_user_impact * (gs.user_seeks + gs.user_scans)/100 as ImprovementValue		
from sys.dm_db_missing_index_group_stats gs
INNER JOIN sys.dm_db_missing_index_groups ig on gs.group_handle = ig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details id on id.index_handle = ig.index_handle
where DB_NAME(id.database_id) = 'AdventureWorks'
order by avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)/100 desc

 

Sorgu sonucunda aşağıdaki gibi bir sonuç çıkacaktır.

1

Gördüğünüz gibi Person.Address tablosu üzerinde City ve PostalCode kolonları için bir missing index önerisi çıktı. Bu index olsaydı 28 kez seek edileceğini ve en son 10.12.2010 14:33 tarihinde seek edildiği bilgilerine de bu sorgu sonucunda erişebiliyoruz.

Dolayısıyla üzerinde çalışacağımız ve create etmek istediğimiz index’i belirledik. Person.Address tablosu için City ve PostalCode kolonları.

Şimdi gerçekten böyle bir index’e ihtiyaç var mı diye detay analizlerimize başlayalım.

 

2. Create edilmesi düşünülen index’in ve bulunduğu tablonun analizi

Bu başlık altında aşağıdaki detay incelemeleri yapacağız.

  • Tablo kayıt ve boyut analizi = Tablonun kayıt sayısına bakarak index’in gerekliliğinin ilk sorgulamasını yapıyoruz. Eğer ufak bir tablo ise index’in create edilmesinin çok gerekli olmadığını düşünebiliriz. Ya da boyutu çok büyükse oluşturacağımız index’in de iyi bir yer kaplayacağını düşünerek create edip etmemeyi tekrar değerlendirebiliriz. Ama ben kendi çalışmalarımda bu adıma sadece kontrol için bakıyorum. Çoğu durumda bu adım index create edip etmeme kararıma çok fazla etki etmiyor.
  • Tablonun üzerinde bulunan diğer index’lerin kullanım istatistikleri = Tabloda bulunan diğer index’lerin kullanım istatistiklerine bakıyoruz. Bu kontrol sırasında diğer index’lerin kullanım rakamlarına göre bu yeni oluşturacağımız index’in cluster index ya da non-cluster index olup olmayacağına karar verebiliriz.
  • Tablonun üzerinde bulunan index’lerin hangi kolonlar üzerinde bulunduklarının kontrolü = Tablonun hali hazırda sahip olduğu index’lerin hangi kolonlar üzerine tanımlandıklarına bakıyoruz. Belkide bizim oluşturmak istediğimiz index’i başka bir index farklı kolon sıralaması ile cover etmekte. Eğer böyle bir durum var ise index create etmemeyi düşünebiliriz.
  • Tablonun üzerinde bulunan index’lerin Include Column içerip içermediklerinin kontrolü = sp_helpindex sistem procedure’ü included column’ları vermemekte. Dolayısıyla son adım olarak bu kontrolüde yapıyoruz.

 

Yukarıdaki analizlerin tamamı için aşağıdaki sorgu bloğunu kullanabiliriz. Parametre olarak hangi tabloyu analiz etmek istediğimizi belirtiyoruz.

declare @tablename varchar(100)='Person.Address'

--1. Tablonun kayıt sayısına ve size ına bakılır.
exec('sp_spaceused '''+@tablename+''' ')

--2. Tablonun diğer index lerinin Index Usage Statistics lerine bakılır.
select OBJECT_SCHEMA_NAME(us.object_id),OBJECT_NAME(us.object_id) as tableName,
    i.index_id,
	i.name as indexName,
	us.last_user_seek,
	us.user_seeks,
	CASE us.user_seeks WHEN 0 THEN 0
		ELSE us.user_seeks*1.0 /(us.user_scans + us.user_seeks) * 100.0 END AS SeekPercentage,
	us.last_user_scan,
	us.user_scans,
	CASE us.user_scans WHEN 0 THEN 0
		ELSE us.user_scans*1.0 /(us.user_scans + us.user_seeks) * 100.0 END AS ScanPercentage,
	us.last_user_lookup,
	us.user_lookups,
	us.last_user_update,
	us.user_updates,
	CASE us.user_scans + us.user_seeks WHEN 0 THEN 0
		ELSE us.user_updates*1.0/(us.user_scans + us.user_seeks)*100.0 END as UpdatesPercentage	
FROM sys.dm_db_index_usage_stats us
INNER JOIN sys.indexes i ON i.object_id=us.object_id and i.index_id = us.index_id 
WHERE us.database_id = DB_ID() and OBJECT_SCHEMA_NAME(us.object_id)+'.'+OBJECT_NAME(us.object_id) = @tablename

--3. Diğer index'lerin hangi kolonlar üzerinde olduğuna bakılır.
exec('sp_helpindex '''+@tablename+''' ')

--4. Diğer index lerden included column içeren var mı yok mu diye bakılır.
select * from sys.index_columns where OBJECT_ID(@tablename)=object_id and is_included_column!=0

 

Sorgu sonucu şu şekilde bir sonuç olacaktır.

2

 

3. Tablonun query stats’larına bakılıp ilgili missing index’e sebep olan script’in bulunması

Missing index’in hangi sorgu sonucunda oluştuğunu bilmek istiyorum. Çünkü bizim yaptığımız örneğin tersine reel de ben hangi sorgudan bu index’in missing olarak belirlendiğini bilmiyorum. Bunun için sys.dm_exec_query_stats dmv’sini kullanıyorum.

select 
     st.[text],
     SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS statement_text,        
     qs.last_execution_time,
     qs.execution_count,
     qs.total_logical_reads as total_logical_read,
     qs.total_logical_reads/execution_count as avg_logical_read,
     qs.total_worker_time/1000000 as total_cpu_time_sn,
     qs.total_worker_time/qs.execution_count/1000 as avg_cpu_time_ms,
     qp.query_plan,
     qs.last_logical_reads,
     qs.plan_generation_num
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
where SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) like '%Person.Address%'

 

Sorgu sonucu aşağıdaki gibi bir sonuç olacaktır.

3

Gördüğünüz gibi ilk satırda bizi ilgilendiren script’i bulduk. Bu script 28 kez çalıştırılmış ve her çalışmada 236 logical read yapmış.

 

4.Bulunan script’in şu anki IO değerlerinin sorgulanması

Tanımlayacağımız index’in logical read değerlerinde bir değişiklik yapıp yapmadığını görmek için şu anki yaptığı IO değerine bakmak istiyorum. Bir önceki adımda ilgili script’i bulmuştum. Şimdi bu script’in IO değerlerine bakıyorum.

set statistics IO ON
set statistics profile ON
select * from Person.Address where City='Duvall' and PostalCode='98019'

 

4

Görüldüğü gibi index creatinden önce bu sorgu 236 logical read yapmakta.

 

5. Index’in create edilmesi

Bütün analizlerimizi yaptık ve index’i create etmeye karar verdik.

USE [AdventureWorks]
GO
CREATE NONCLUSTERED INDEX [IX_Address_City_PostalCode]
ON [Person].[Address] ([City],[PostalCode])

 

6.Sorgunun IO değerine tekrar bakılması ve ne kadar düştüğünün incelenmesi

Index’i create ettikten sonra tekrar IO sorgumuzu çekelim ve yapılan IO da bir iyileştirme olup olmadığını kontrol edelim.

set statistics IO ON
set statistics profile ON
select * from Person.Address where City='Duvall' and PostalCode='98019'

 

5

Gördüğünüz gibi index create’inden önce 236 olan logical read index create’inden sonra 22’ye düşmüş durumda. Bu durumda 10 kat iyileştirme yaptığımızı düşünebiliriz.

 

7.Create edilen index’in kullanım istatistiklerinin monitor edilmesi

Missing Index’leri create ettikten sonra periyoduk olarak (ben 1 haftada bir yapıyorum) create ettiğimiz indexlerin usage statistic’lerine bakıp ne kadar seek,scan aldığına, ne kadar update gördüğüne bakmakta fayda var. Bu şekilde create edip kullanılmadığını gördüğümüz index’leri drop etmeyi düşünebiliriz.

select OBJECT_NAME(us.object_id) as tableName,
	i.name as indexName,
	us.last_user_seek,
	us.user_seeks,
	CASE us.user_seeks WHEN 0 THEN 0
		ELSE us.user_seeks*1.0 /(us.user_scans + us.user_seeks) * 100.0 END AS SeekPercentage,
	us.last_user_scan,
	us.user_scans,
	CASE us.user_scans WHEN 0 THEN 0
		ELSE us.user_scans*1.0 /(us.user_scans + us.user_seeks) * 100.0 END AS ScanPercentage,
	us.last_user_lookup,
	us.user_lookups,
	us.last_user_update,
	us.user_updates
FROM sys.dm_db_index_usage_stats us
INNER JOIN sys.indexes i ON i.object_id=us.object_id and i.index_id = us.index_id 
WHERE us.database_id = DB_ID('AdventureWorks') and OBJECT_NAME(us.object_id) = 'Address'

 

6

Gördüğünüz gibi create ettiğimiz index çok güzel bir şekilde seek alarak kullanılmakta. Eğer seek+scan değeri update den küçük olsaydı bu index’i drop etmeyi düşünebilirdik.

 

ÖZET

Adım adım missing index operasyonunu nasıl gerçekleştirdiğimizi incelemeye çalıştık. Index kullanımı büyük projeler için çok önemli olduğundan bu kontrollerin periyodik olarak production ortamları için yapılmasının önemine tekrar vurgu yaparak yazımı noktalamak istiyorum.

 

İ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 bana gelen bir url’i sizinle paylaşmak istiyorum. Aşağıdaki süper manyak über :) şekilde hazırlanmış PDF dosyasıyla SQL Server 2008 ve R2 ile gelen system view’lerinin MAP’ine ulaşabilir, birbirleriyle olan relationlarına erişebilirsiniz.

Kesinlikle incelemenizi tavsiye ediyorum.

Overview

The Microsoft SQL Server 2008 R2 System Views Map shows the key system views included in Microsoft SQL Server 2008 and SQL Server 2008 R2, and the relationships between them. The map is similar to the Microsoft SQL Server 2008 version and includes updates which are also applicable for the Microsoft SQL Server 2008.

http://download.microsoft.com/download/0/6/9/06952611-7528-4CEE-BBD5-29DC44315DC7/SQL_Server_2008R2_System_Views_Map.pdf

map1

 

İ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


Bildiğiniz gibi Autogrowth özelliği aktif olan DB’lerde ihtiyaç olduğunda diskten yeni bir alan allocate edilir ve mdf-ndf dosya boyutunda bir artış olur. Allocate edilen bu alandan SQL Server gerekli gördüğü yeri reserved eder ve bu alandan da kullanılmayan alan olabilir.

Yani toparlayacak olursak File Allocation- Reserved Size – Unused size olmak üzere 3 farklı boyuttan bahsetmemiz mümkündür. File boyutu olarak 10 GB yer kaplayan bir DB aslında bu 10 GB’ın belkide sadece 3 GB ını rezerve etmiştir ve bu 3GB ın da 1 GB’ı belkide unused dır. Dolayısıyla diskten gereksiz bir kullanım söz konusudur ve bu durum analiz edilerek shrink vasıtasıyla DB nin boyutunun düşürülmesi çözümüne gidebilir.

Bugünkü yazımda bir server da bulunan bütün DB’lerin File Allocation- Reserved Size – Unused size bilgilerini sorgulayabileceğimiz script üzerinde duracağız.

[more]

Aşağıda yazdığım script ile server da bulunan bütün DB’ler için File Allocation- Reserved Size – Unused Size bilgilerine ulaşılabilir.

create table #tmp1 (DBName varchar(100),reservedpages float,usedpages float,pages float)

select DB_NAME (dbid) as DBName,
	   Cast(sum(Size)/128. as numeric(18,2)) as FileSize_MB
into #tmp2	   
from sys.sysaltfiles 
where dbid > 4  and dbid<1000 and groupid <> 0
group by DB_NAME (dbid)

exec sp_msforeachdb 'use ?
insert #tmp1
select ''?'',sum(a.total_pages) as reservedpages,  
	sum(a.used_pages) as usedpages,  
	sum(  
	CASE  
		 When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0  
		 When a.type <> 1 Then a.used_pages  
		 When p.index_id < 2 Then a.data_pages  
		 Else 0  
	END ) as pages
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id  
left join sys.internal_tables it on p.object_id = it.object_id  
'
  
select  
	t1.DBName,
	t2.FileSize_MB,
	Cast(Round(t1.reservedpages * 8192 / 1024. /1024.,2) as Numeric(18,2)) as reserved_size_MB, 
	Cast(Round(t1.pages * 8192 / 1024. / 1024.,2) as Numeric(18,2)) as data_size_MB,
	Cast(Round((t1.usedpages - t1.pages) * 8192 / 1024. / 1024.,2) as Numeric(18,2)) as index_size_MB,
	Cast(Round(t1.usedpages * 8192 / 1024. / 1024.,2) as Numeric(18,2)) as total_used_size_MB,
	Cast(Round((t1.reservedpages - t1.usedpages) * 8192 / 1024. / 1024.,2) as Numeric(18,2)) as unused_from_reserved_size_MB,
	t2.FileSize_MB - Cast(Round(t1.reservedpages * 8192 / 1024. /1024.,2) as Numeric(18,2)) as unused_from_file_size_MB
from #tmp1 t1
join #tmp2 t2 on t2.DBName=t1.DBName
order by t1.DBName

drop table #tmp1
drop table #tmp2

 

Sorgu sonucu şu şekilde olacaktır.

1

  • DBName = İlgili Database Adı
  • FileSize_MB = Data dosyalarının diskte kapladığı toplam alan.
  • Reserved_size_MB = FileSize_MB boyutunun ne kadarının rezerve edildiği.
  • Data_size_MB = reserved_size_MB ‘ ın ne kadarının Data tarafından kullanıldığı.
  • Index_size_MB = reserved_size_MB ‘ ın ne kadarının Index tarafından kullanıldığı.
  • Total_used_size_MB = reserved_size_MB ‘ ın toplamda ne kadarının kullanıldığı.
  • Unused_from_reserved_size_MB = reserved_size_MB ‘ ın ne kadarının kullanılmadığı.
  • Unused_from_file_size_MB = FileSize_MB ‘ in ne kadarının kullanılmadığı.

AdventureWorks DB’si için bakarsak 1218 in FileSize ‘ ın 1032 sinin kullanılmadığını görüyoruz. Dolayısıyla bu DB yi shrink ederek diskte kapladığı alanı düşürebiliriz.

USE [AdventureWorks]
GO
DBCC SHRINKDATABASE(N'AdventureWorks', 10 )
GO

 

Sorguyu tekrar çalıştırırsak FileSize’ın ve unused size’ın düştüğünü göreceğiz.

2

Yukarıdaki script’in cursor versiyonlu haline şuradan erişebilirsiniz.

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ünkü yazımda FOR XML syntax’ı ile sorgu sonuçlarını XML olarak görüntülemek için birkaç örnek yapacağız.

Örneklerimde for xml auto,type - for xml auto,elements - for xml path type’larını kullanacağım.

[more]

1-for xml auto,type

select top 100 * 
from sales.salesorderheader
for xml auto,type 

 

1

 

2-for xml auto,elements

select top 100 * 
from sales.salesorderheader
for xml auto,elements 

 

2

 

3-for xml path

select cust.customerID "@CustID" 
     ,cust.customertype "@CustType"
     ,(
	   select o.salesorderID as '@salesID'
			 ,o.OrderDate "@Date"
			 ,(
				select det.ProductID '@ProductID'
					  ,det.UnitPrice '@UnitPrice'
					  ,det.OrderQty '@OrderQty'
				from sales.salesorderdetail det
				WHERE det.SalesOrderID=o.SalesOrderID
				for xml path('OrderDetail'), TYPE
			  )     
	   from sales.salesorderheader o
	   WHERE o.customerID=cust.customerID
	   for xml path('Order'), TYPE
	  )      
FROM sales.customer cust
order by cust.customerID
for xml path('Customer')

 

3

 

İ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, 09.12.2010 tarihinde Swissotel’de düzenlenen IDC Business Intelligence Roadshow 2010 seminerinde Obase Genel Müdürü Bülent Dal’ın sunumunda gösterdiği Mobile Suite video’sunu paylaşmak istiyorum.

Bu video’da MicroStrategy firmasının iPad ve iPhone ‘ lar için geliştirdiği Mobile BI çözümlerinin ne boyutlara geldiğini görebilirsiniz.

http://www.microstrategy.com/mobile/video/

Mobile Suite’ın Free versiyonunu aşağıdaki adresten indirip deneyebilirsiniz.

http://www.microstrategy.com/freemobilebi/

MicroStrategy hakkında kısa bilgi;

1989 yılında kurulan MicroStrategy iş zekası (BI) teknolojisinde bir dünya lideridir. MicroStrategy sağlamış olduğu raporlama ve analiz yazılımları ile dünya çapında önde gelen kuruluşlara hergün alınması gereken iş kararlarında daha kesin sonuçlar için yardımcı olur. Şirketler MicroStrategy’i kullanım kolaylığı, gelişmiş analitik, üstün veri ve kullanıcı ölçeklenebilirliğinden dolayı seçmektedirler. MicroStrategy şirketlerin iş zekâsından etkin faydalanmasına destek vermek amacıyla indirilebilir ücretsiz raporlama yazılımı (http://www.microstrategy.com/freereportingsoftware) sunmaktadır. MicroStrategy hakkında daha fazla bilgi (Nasdaq: MSTR) www.microstrategy.com adresinde mevcuttur. www.microstrategy.com

 

İ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 2005 SP4 CTP yayınlandı. Aşağıdaki adreslerden download edebilirsiniz.

SQL Server 2005 Express Edition SP4 CTP
Microsoft SQL Server 2005 Express Edition is a free, easy-to-use, lightweight version of SQL Server 2005. It is fast and easy to learn, allowing you to quickly develop and deploy dynamic data-driven applications.

SQL Server 2005 SP4 CTP
Download Service Pack 4 for Microsoft SQL Server 2005.

 

İ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