Sql Server Performans Çalışmaları

Tsql ile tablo ve alan açıklamalarını kopyalama

2011-11-04T14:59:00.002+02:00

Aşağıdaki sorgu ile tablo ve alan açıklamalarını (Description alanlarının) kopyalanabilmesini sağlar.

SELECT

'EXEC sys.sp_addextendedproperty @name=N''MS_Description'',

@value=N'''+replace(

CAST(exprop.value AS VARCHAR(max))

,'''','"')

+''' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''TABLE'',@level1name=N'''

+tbl.name+''', @level2type=N''COLUMN'',@level2name=N'''+clmns.name +''';'

,tbl.name,

CAST(clmns.name AS VARCHAR(35)) ,exprop.value

FROM sys.tables AS tbl

INNER JOIN sys.all_columns AS clmns

ON clmns.object_id=tbl.object_id

LEFT OUTER JOIN sys.indexes AS idx

ON idx.object_id = clmns.object_id

AND 1 =idx.is_primary_key

LEFT OUTER JOIN sys.index_columns AS idxcol

ON idxcol.index_id = idx.index_id

AND idxcol.column_id = clmns.column_id

AND idxcol.object_id = clmns.object_id

AND 0 = idxcol.is_included_column

LEFT OUTER JOIN sys.types AS udt

ON udt.user_type_id = clmns.user_type_id

LEFT OUTER JOIN sys.types AS typ

ON typ.user_type_id = clmns.system_type_id

AND typ.user_type_id = typ.system_type_id

LEFT JOIN sys.default_constraints AS cnstr

ON cnstr.object_id=clmns.default_object_id

LEFT OUTER JOIN sys.extended_properties exprop

ON exprop.major_id = clmns.object_id

AND exprop.minor_id = clmns.column_id

AND exprop.name = 'MS_Description'

WHERE (--tbl.name = @TableName and

exprop.class = 1) --I don't wand to include comments on indexes

ORDER BY clmns.column_id ASC

Kullanılmayan indexlerinizi otomatik bulup drop eden sorgu hazırlamak

2011-11-04T09:29:00.000+02:00

Aşağıdaki sorguyu kullanarak daha önceden açılmış fakat kullanılmayan indexleri bulup drop cümlesini yazacağız.
sorgudaki ilk sütün indexin koly drop edilmesi için disable ediyor ikinci sütün ise indexi drop ediyor.

SELECT

'ALTER INDEX '+i.name+' ON '+o.name+' DISABLE' as IndexDisable,
'DROP INDEX '+i.name+' ON '+o.name+' WITH ( ONLINE = OFF )' as IndexDrop,

o.name Object_Name,
i.name Index_name,
i.Type_Desc ,s.*
FROM sys.objects AS o
JOIN sys.indexes AS i
ON o.object_id = i.object_id
LEFT OUTER JOIN
sys.dm_db_index_usage_stats AS s
ON i.object_id = s.object_id
AND i.index_id = s.index_id
WHERE o.type = 'u'
--– Clustered and Non-Clustered indexes
AND i.type =2--IN (1, 2)
-- Indexes without stats
AND
(s.index_id IS NULL) OR
-- Indexes that have been updated by not used
--s.user_seeks = 0
(s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 and user_updates=0)
order by 3

Sql Serverda Index Kavramı

2011-11-04T09:28:00.000+02:00

Bir web seminerinde tuttuğum notlar:

Sql Serverda Index Kavramı

Performance Tuning

Neden Index

Daha az ver okumak daha az zamanda veri getirmek
index key vasıtası ile okumak istediğimiz kayda daha hızlı ulaşabiliriz
Table scan(tüm tablonun okunması) yerine index veriyi daha hızlı getiri
Ctrl+L Estimated query plan

SET STATISTIC IO ON
SET STATISTIC TIME ON
…
SET STATISTIC IO OFF
SET STATISTIC TIME OFF

Gereksiz indexler sıkıntı oluşturabilir(Insert Update performansı düşer)

Index Nasıl Çalışır

client İstek yollar
Protokol Layer
Parse
Query Processor
Optimize + Execute

B-Tree Yapısı

Root
Level 1 (Intermediate level)(Birden fazla olabilir...)
Level 0 (leaf Level)

(Clustered ise kayıtın kendisine degil ise referansına erişilir.)

Execute

Index Pageleri görüntüleme

DBCC IND (ELMA, TABLO, INDEX_id)

select * from sys.indexes
Index pagelerini listeler

Index level (0 Leave level) (1 Intramate level)
prevpage nextpage
PageId

DBSS PAGE

önce: DBCC TRACEON(pageId)

ID: clustered ındex key
Uniqufier index unique değil ise unique etmek için
ChildPage: aly level pageleri gösteriyor

DBCC PAGE (‘‘ELMA’,1,2344,3

Clustered Index

Fiziksel Index denilebilir. Diskte veriler mantıksal olarak sıralı tutulur(Page chain)
Bir tabloda sadece 1 tane clustered Index olabilir.
Leaf Level: Datanın kendisi(Tamamı) Arama bitince veriye ulaşırız
en yukarda root level

Intermediate level

Leaf Level: Datanın kendisi
(Non clustered index buna look up yaptığı için önemli)

Uniqufier 4 byte kullanır
Identity kolona koyulması önerilir

Update görmez

Non Clustered Index

Tefefon defterinde Mesleklere göre arama yapmak örnek olarak verilebilir
Leaf Level: Row Locator + Included Kolonlar
Row Locator Heap yada Clustered ındex olmasına göre değişiklik olur

Heap: Dosya No+ Sayfa no+Kayıt no (HEAP RID lookup yapmarak datayı getiriyor)
Clustered Clustered Index Key

Max Non clusterek index

Sql server 2005 249
sql server 2008 999

Örneklerle Index Pageler

Unique Clusterek ındex

DBCC IND( ındex leve 0 leaf
DBCC TRACE ON
BBCC PAGE..(Index uniue ise unifier alanı açılmaz.)

Included Kolon Kullanımı

Non clustered Index için Look Up yapmayı engellemek için yapılıyor.(Covering Index)
Index Seek aracılığı ile arama yapıyor(%11) Key Lookup ile datayı getiriyor(%89)
Inluded alan kullanılır ise sadece Index Seek yapıyor
Included index sadece Leaflerde bulunuyor(daha az yer kaplar
Composite tüm levellerde veri olacağından daha fazla yer kaplar)

Composite:25.21 MB
Included: 25.02 MB

Sonuç - Özet

Index Sqlserver Beygir gücü..
Clustered ve non clustered yapısını bilmeliyiz
B-Tree yapısını bilmeliyiz
Peryodik bakım yapmamız çok önemli
Gereksiz Indexler Update Delete ve Inserti çok yavaşlatmakta
Missing indexlerin DMVler ile belirlenip açılmalı

www.sqlserveronculeri.com
www.turgaysahtiyan.com

DMV ler ile insex seek ve scanlarına bakabiliriz.
Integrity kontrol.. DBCC...

Sql Server 2005 üstü için Otomatik Index Oluşturma

2011-11-04T08:52:00.000+02:00

Sql server dinamik viewleri kullanarak gerekli olan indexleri bulup create cümlererini hazırlayan bir scripti ili adımda yazalım:

1. Analizini yapacağımız veritabanının id'sini bulalım:
[DBADI yerine veritabanınızın adını yazın ve gelen sonuçta idyi ikinci adımda kullanın.]

select * from sys.databases where name ='DBADI'

2. bulduğumuz idyi @dbId parametresine aşağıdaki gibi verip çalıştıralım. gelen sonucu inceleyerek gerekli indexleri açabilirsiniz. Lütfen index açmadan önce gereksiz indexlerin veritabanının Insert ve Update işlemlerini yavaşlatabileceğini göz önüne alınız.

Not: where koşulundaki aşağıdaki satırı değiştirerek ihtiyacınız olan indexlere daha iyi odaklanabilirsiniz:

and (s.avg_user_impact>60 or user_seeks>1000 or avg_total_user_cost>50)

Sql sonucu

declare @dbId int
set @dbId=7

select

'CREATE NONCLUSTERED INDEX IX_'+
replace(
replace(
replace(
replace(statement,'[DBADI].[dbo].[','')+'_'+
isnull(replace(equality_columns,',','_'),'')+
isnull(replace(inequality_columns,',','_'),'')
,'[','')
,']','')
,' ','')
+' ON '+statement+'('+
isnull(equality_columns,'')+
case when equality_columns is null then inequality_columns
else isnull(','+inequality_columns,'') end

+')'
+
isnull ( 'include('+included_columns+') with(ONLINE = ON) ;','')

, s.avg_user_impact, s.user_seeks,s.avg_total_user_cost, statement,
s.unique_compiles,

replace(replace(statement,'[DBADI].[dbo].[',''),']',''),

d.*
, s.avg_total_user_cost

, s.last_user_seek

from sys.dm_db_missing_index_group_stats s
,sys.dm_db_missing_index_groups g
,sys.dm_db_missing_index_details d
where s.group_handle = g.index_group_handle
and d.index_handle = g.index_handle
and d.database_id=@dbId--DB name den
--and replace(replace(statement,'[DBADI].[dbo].[',''),']','') like '%tabloAdi%'
and (s.avg_user_impact>60 or user_seeks>1000 or avg_total_user_cost>50)
order by user_seeks desc--s.avg_user_impact desc