Masters Of SQL

SQL Server ile ilgili bilgiler, hayata dair paylaşımlar ve birazda eğlence tabii...
Deadlock’sız, Blocking’siz, Contention’sız günler diliyoruz :)
Sysadmin sizinle olsun

SQL Server - Best Practices

Performans & Optimizasyon

SQL Server üzerinde best practice olarak ayarlamamız gereken ayarları zaman zaman paylaşacağım ilk olarak yeni kurduğumuz bir SQL Server sunucusunda yapmamız gereken ayarlardan bahsediyor olacağım.
SQL Server konfigurasyonları içerisinde kritik olan "Server Properties" ekranından ve "sp_configure" den ayarlayabileceğimiz ayarlar aşağıda.

    USE master
    GO
    -- Öncelikle advanced options'ları açıyoruz
    EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE WITH OVERRIDE;
    GO

    -- Gerekli ayarları kontrol ediyoruz
    SELECT name, value, value_in_use, [description] 
    FROM sys.configurations
    WHERE name IN 
    (
    'backup compression default', -- 1 yapılmalı özellikle Backup süreleri ve storage den kazanç çok yüksek
    'clr enabled', -- clr enabled (only enable if it is needed)
    'Database Mail XPs', -- SQL üzerinden mail atıyorsak 1 yapılmalı
    'max degree of parallelism', -- OLTP sunucularında 1 yapılmalı.
    'max server memory (MB)', -- SQL Server RAM sever :) bu yüzden uygun ayarları OS a göre ayarlamak gerekir
    'min server memory (MB)', -- RAM optimizasyonu geniş bir konudur; min değerini ayarlamak yararlı demek tam doğru olamıyor analiz etmeden verilen bir değer OS çökmesine sebep olabilir.
    'optimize for ad hoc workloads', -- AdHoc query kullanan yerler ve Memory kısıtlı olan yerlerde "1"
    'xp_cmdshell' -- özellikle "0" olmalı sistem tarafında büyük bir açık oluşturuyor.
    )
    ORDER BY name 
    


Mevcut ayarlarımızı gördük artık ayarlamamız gereken değerler kaldı.

    USE master
    GO
    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE WITH OVERRIDE;;
    GO

    EXEC sys.sp_configure N'backup compression default', N'1'
    GO
    RECONFIGURE WITH OVERRIDE
    GO
 
    sp_configure 'max server memory', 20000; -- RAM miktarını kontrol ederek yapmamız gerektiğini unutmayalım.
    GO
    RECONFIGURE  WITH OVERRIDE;
    GO

    sp_configure 'min server memory', 0;
    GO
    RECONFIGURE  WITH OVERRIDE;
    GO

    sp_configure 'clr enabled', 1;
    GO
    RECONFIGURE  WITH OVERRIDE;
    GO 

    sp_configure 'max degree of parallelism', 1;
    GO
    RECONFIGURE  WITH OVERRIDE;
    GO 

    sp_configure 'optimize for ad hoc workloads', 1;
    GO
    RECONFIGURE  WITH OVERRIDE;
    GO 

    -- xp_cmdshell configure
    EXEC sys.sp_configure N'xp_cmdshell',N'0' -- yazılımı ekibinin kullandığı program olabilir !!!!!! sorup yapılmalı
    GO
    RECONFIGURE WITH OVERRIDE
    GO

    sp_configure 'show advanced options', 0;
    GO
    RECONFIGURE WITH OVERRIDE;;
    GO
    


RAM ayarları ne olacak diye soracak olursanız; ideal oranları tam vermek mümkün olmasa da yaklaşık alttaki değerleri ayarlıyorum bununla birlikte altta verdiğim değerler 64 bit (x64) işletim sistemleri için ve SADECE sunucuda SQL Server çalışıyorsa geçerlidir.

Physical RAM MaxServerMem Setting
2GB 1500
4GB 3200
6GB 4800
8GB 6400
12GB 10000
16GB 13500
24GB 21500
32GB 29000
48GB 44000
64GB 60000
72GB 68000
96GB 92000
128GB 124000

Bir yazımızın daha sonuna geldik. OS'e RAM bırakmanız gerektiğini unutmayın performansınızın düşmemesi dileğiyle :):)

DBA Toolkit (sp_WhoIsactive)

Evet sevgili SQL Server sevenler, sevmek zorunda kalanlar ve nereden bulaştım bu işe diyenler :). Yeni bir bölüme başlıyoruz "DBA Toolkit" amacımız genelde DBA ler tarafından kullanılan (ki en çok kendimizin kullandığı) veya kullanılmasını tavsiye edeceğimiz scriptleri tanıtıp paylaşacağız.

Serimize hayat kurtaran baş tacımızla :) başlıyoruz.

Tüm DBA ler için "Sistem yavaşladı" veya "Kitlendik" sözlerini duydumu yoğun arayış başlıyor demektir. Önce "Activity Monitor" açılmaya çalışılır açılırsa sorgular kontrol edilir eğer açılmazsa araştırma devam eder ve ellerinde bulunan query lerle (sp_who veya sp_who2 en çok kullanılanı) inceleme yapılmaya devam edilir ve uzun bir arayıştan sonra (bu arada kitlenme bitmiş olabilir :):)) "SQL de sorun yok" sözleriyle biter.

sp_WhoIsActive Download



İndirdiğiniz scripti direk SSMS üzerinde execute ederek sistemimize ekliyoruz, master db üzerinde çalışan bir SP olduğundan dolayı belirli yetkilere sahip olmanız gerektiğini hatırlatmama gerek yoktur herhalde :). Siz yapamıyorsanız sysadmin'e söyleyip (yararını görmesi için makaleyi okutabilirsiniz) execute ettirebilirsiniz.

Size sisteme aldığınız script için "New Query" diyerek yeni bir sayfa açıp execute etmek kalıyor.
    EXEC sp_WhoIsactive
    



Görüldüğü gibi sistem hakkında fazlasıyla bilgi içeriyor.



Kullanım parametrelerini öğrenmek için
    EXEC sp_whoisactive @help = 1
    




Çok fazla kolon geliyor ben sadece belirli kolonları istiyorum acaba "Adam" amca bunları düşünmüş mü? dediğiniz duyar gibiyiz ve cevabımız "Yapmış be adam :):) "
    EXEC sp_WhoIsActive		
     @find_block_leaders = 1
   , @get_task_info = 1
   , @get_additional_info = 0
   , @output_column_list = '[session_id][block%][login_name][host%][host_process_id][program%][sql_text][wait_info]'
   , @sort_order = '[blocked_session_count] DESC'
    




DBA ler için belki de en önemli bölümü (her türlü gideri varda :) ) sistemi bloklayan sorguyu çok hızlı bir şekilde gösteriyor olması. Bunun için kolonlar içerisinde "blocking_session_id" kontrol etmemiz yeterli oluyor. Burada yazan SPID bizi kilitleyen sorgunun ID si bu SPID yi kontrol ettikten sonra beklemeye devam edilebilir veya hiç istemesekte yapmak zorunda olduğumuz "Kill" komutunu çalıştırabiliriz.
    EXEC sp_whoisactive @help = 1
    




Sorgularınız uzun olduğunda XML olarak çıkan sonuç sizi tatmin etmeyebilir, bunun için alttaki şekilde çalıştırabiliriz.
     EXEC sp_whoisactive @format_output = 0
    


SQL Server'da gelen connectionların bazıları sleeping de beklerler bunları bulabilmek için (Yazılımcıların açık bıraktığı sorguları görmekte bire bir :) )
    EXEC sp_WhoIsActive @show_sleeping_spids = 2
    


Sorguların query_plan larını görebilmek için.
    EXEC dbo.sp_WhoIsActive @get_plans = 1, @get_task_info = 2
    




Çok fazla veri tabanıyla çalışan DBA'ler için güzel bir parametre de filtre parametresi.
    -- İstediğimiz veri tabanına göre filter koyuyoruz
    EXEC sp_whoisactive @filter_type = 'database', @filter = 'master'
    GO
    -- Görmek istemediğimiz veri tabanına göre filter koyuyoruz
    EXEC sp_whoisactive @not_filter_type = 'database', @not_filter = 'master'
    


Son olarak da sp_WhoIsActive in sorgu sonuçlarını bir tabloda toplayıp bunlar üzerinde sonradan analiz yapmaya çalışalım.

Adım 1:
Öncelikle alt yapımızı hazırlayalım.
    -- sp_WhoIsactive nin sorgu sonucuna göre birr tablo create ediyoruz
    DECLARE @History_table VARCHAR(4000) ;
    SET @History_table = 'spWhoIsActiveHistory';

    DECLARE @schema VARCHAR(4000) ;
    EXEC sp_WhoIsActive
    @get_transaction_info = 1,
    @get_plans = 1,
    @return_schema = 1,
    @schema = @schema OUTPUT ;

    SET @schema = REPLACE(@schema, '
', @History_table) ;

    -- PRINT @schema 
    EXEC(@schema) ;
    


Adım 2:
Hazırladığımız tabloya "@destination_table" parametresini kullanarak belli aralıklarla kayıt atacağız ve böylece geçmiş yönelik analiz yapabileceğiz. sp_WhoIsactive SP sini çok farklı şekilde çalıştırabildiğimizi unutmayalım dikkatli olmanız gereken nokta history tablosunu hangi parametrelerle oluşturduysak o parametrelerle ilerlememizdir. Biz burada Execution planları almak istediğimizden dolayı "get_plans" parametresiyle çalıştırdık. "EXEC sp_WhoIsActive @get_transaction_info = 1, @get_plans = 1"
    DECLARE
    @History_table VARCHAR(4000) 
    SET @History_table = 'spWhoIsActiveHistory';

    -- Anlık olarak kayıtları ekliyoruz
    EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_plans = 1,
                @destination_table = @History_table;

    -- Eklenen kayıtları kontrol ediyoruz
    select * from spWhoIsActiveHistory order by collection_time desc
    


Adım 3 :
Bu kayıt işlemini belli bir tekrarla otomatik olarak yapmak isteyebiliriz.
    DECLARE
        @History_table VARCHAR(4000) ,
        @msg NVARCHAR(1000) ;

    SET @History_table = 'spWhoIsActiveHistory';

    DECLARE @numberOfRuns INT ;
    SET @numberOfRuns = 10 ;

    WHILE @numberOfRuns > 0
        BEGIN;
            EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_plans = 1,
                @destination_table = @History_table ;

            SET @numberOfRuns = @numberOfRuns - 1 ;

            IF @numberOfRuns > 0
                BEGIN
                    SET @msg = CONVERT(CHAR(19), GETDATE(), 121) + ': ' +
                     'Lütfen bekleyin loglama devam ediyor...'
                    RAISERROR(@msg,0,0) WITH nowait ;

                    WAITFOR DELAY '00:00:05'
                END
            ELSE
                BEGIN
                    SET @msg = CONVERT(CHAR(19), GETDATE(), 121) + ': ' + 'Loglama Bitmiştir..'
                    RAISERROR(@msg,0,0) WITH nowait ;
                END

        END ;
    GO

    -- Eklenen kayıtları kontrol ediyoruz
    select * from spWhoIsActiveHistory order by collection_time desc

    


"Her yiğidin bir yoğurt yiyişi vardır" der atalarımız bu yüzden bu güzelim SP yi kullanırken Bizde onlarca kolon içerisinde nelere çok dikkat ediyoruz onlardan bahsedeyim.
Hatırlarsak bu değerlere "EXEC sp_whoisactive @help = 1" yazarak ulaşabiliriz

"[dd hh:mm:ss.mss]" - Tabii ki ilk sırada :) neye dikkat ediyorum hangi query ne kadardır devam ediyor. Her uzun süren sıkıntılıdır diyemeyiz tabii bununla birlikte sorumlusunu bulup bir mütaala :):) yapıyoruz.
"[sql_text]" - Query içeriğini buradan görebiliyoruz.
"[login_name]" - Query yi çalıştıran arkadaşı tespiti hızlandırıyor :) Seni seçtim pikachu derken ispatınız oluyor :):)
"[wait_info]" - Yine önemli kolonlardan biri özellikle blocking yapan querylerde wait süresini gözlemliyorum query analizlerinde sıkıntılı bölgeye odaklanmayı kolaylaştırıyor.
"[blocking_session_id]" - Kritik kolonlardan biri, Query leri blocklayan query'nin SPID sini gösteriyor.
"[physical_reads]" - IO baskısı yapan queryleri üzerinde çalışma yapmak için bakıyoruz. Tam bir rakam söylemek zor çünkü; bazen 10.000 yüksekken bazen de 100.000 yüksek oluyor.
"[query_plan]" - Query'nin Execution Planını görüp incelemek için kullanıyoruz.
"[percent_complete]" - özellikle Backup-restore işlemlerinde direk baktığımız yer. "Abi bitmedi mi?" sorusunun canlı cevabı :):)
"[host_name]" - Query hangi sunucudan geliyor onu buluyoruz ve kaçağı tespit ediyoruz.
"[database_name]" - Çalışan veri tabanını tespit ediyoruz.
"[program_name]" - özellikle sisteme sızan :):) kaçak kod yazan ki canlı örnekleri çoktur yazılımcı arkadaşlar kullanıcı adı ve şifresini öğrendi mi tutabilene aşkolsun :):).


Bir yazımızda böylece bitti, yeni makalelere blocking'siz select atmak üzere hoşçakalın :).

SQL 2016 yenilikleri (CTP3)



Gün geçmiyor ki Microsoft yeni bir sürümle karşımıza çıkmasın :). Daha önce Padawan'ın güzel güzel anlattığı SQL Server 2016 CTP3 kurulum makalesinden sonra yoğun baskılar sonucunda bana da yenilikleri makalesini yazmak kaldı; arkadaş eskiden bir usta çırak ilişkisi vardı :) çıraklar ustalarına böyle zorla birşeyler yaptıramazlardı ah ah nerede o eski günler :):) işin şaka ve goy goy kısmını geçtikten sonra asıl mevzuya girelim tabii detaylara fazla girmeden sadece literatürünüz gelişsin diye yazdım. :)

• SQL Server 2016 (CTP 3.0) indirmek için tıklayınız Community Technology Preview 3 (CTP 3.0), go to Evaluation Center.
• Azure hesabınız varsa SQL Server 2016 kurulu hazır Virtual Machine için tıklayınız

SQL Server 2016 Faydaları:
• Geliştirilmiş bellek performansı sayesinde 30x daha hızlı işlemler, disk tabanlı ilişkisel veritabanları ve gerçek zamanlı operasyonel analitik işlemlerde 100x daha hızlı sorgular.
• Kurum içi ve bulut içinde kullanılan sistemlerde uygulamalarda değişiklik yapmadan verilerinizi Encrypted olarak korumanıza yardımcı olur.
• Stretch Veritabanı teknolojisi sayesinde uygulamada değişiklik olmadan güvenli bir şekilde, OLTP verileriniz için Microsoft Azure da tarihsel olarak tutmanızı sağlar.
• Windows, iOS ve Android için yerel uygulamalar ile mobil cihazlarda zengin görsellik.
• PolyBase T-SQL kullanarak ilişkisel ve ilişkisel olmayan sorgularınızın yönetimini kolaylaştırın.
• AlwaysOn tarafında Azure SQL Server secondaries node kullanarak daha hızlı hibrid yedekleme, yüksek kullanılabilirlik ve felaket kurtarma senaryoları (HADR)

Genelde yenilikler SSMS üzerinde olduğundan dolayı O Services i anlattım. Ayrıca özellikle Analytics le ilgilenen arkadaşlar için yeni services olan SQL Server R Services duyuruldu bu kısımlar biraz daha olgunlaştıkça makale halinde yay��nlamaya çalışacağız.

Database Engine (SSMS)
   
Stretch Database
Server terminolojisine yeni katılmış bir özellik. Yaptığı iş aslında çok güzel (gerçi şu azure a bir alışamadım :) ) lokalde bulunan veritabanının tarihsel olarak değişimlerini azure da bulunan sql sunucuya gönderiyor. Bu sayede hem lokal db de çalışırken hem de uzak verilere kesintisiz olarak erişmemizi sağlıyor.




JSON Desteği
Evet artık geldi beklenen gün bugünmüş. Yazılım tarafına ve rakip dblere JSON geleli yıllar oldu :):) peki kullanımı nasıl?

         SELECT * FROM OPENJSON ('{"version":"SQL Server 2016 (CTP3)","build":13,"date":"28.10.2015"}')
         


            SELECT [key], value
            FROM OPENJSON('["en-GB", "en-UK","tr-TR"]')
         


Columnstore Index
SQL Server hayatına 2012 ile birlikte giren ColumnStore Index her geçen gün güçlenerek büyümeye devam ediyor. Önce OLAP mimarisine geldi sonrasında OLTP ye eklediler şimdi ise InMemory table lara ekleme yapıldı. Microsoft un sayfasında açıkladığı gibi gelen yapılar (gelecek diyelim :) ) aşağıda.  


Columnstore Index Feature

SQL Server 2012

SQL Server 2014

SQL Server 2016 Community Technology Preview 3 (CTP 3.0)

Batch execution for multi-threaded queries

yes

yes

yes

Batch execution for single-threaded queries

yes

Archival compression option.

yes

yes

Snapshot isolation and read-committed snapshot isolation

yes

Specify columnstore index when creating a table.

yes

AlwaysOn supports columnstore indexes.

yes

yes

yes

AlwaysOn readable secondary supports read-only nonclustered columnstore index

yes

yes

yes

AlwaysOn readable secondary supports updateable columnstore indexes.

yes

Read-only nonclustered columnstore index on heap or btree.

yes

yes

yes1

Updateable nonclustered columnstore index on heap or btree

yes

Additional btree indexes allowed on a heap or btree that has a nonclustered columnstore index.

yes

yes

yes

Updateable clustered columnstore index.

yes

yes

Btree index on a clustered columnstore index.

yes

Columnstore index on a memory-optimized table.

yes

Nonclustered columnstore index definition supports using a filtered condition.

yes

1 To create a readable nonclustered columnstore index, store the index on a read-only filegroup.

     

Query Store
InMemory tablolarda desteklenmeye başlandı (CTP3). SQL Server 2016 ile hayatımıza giren bir kavram ve yaptığı iş DBA ler için ileride çok işe yarayacak gibi duruyor. Peki ne olaki bu Query Store :). Sorgular esnasında oluşan Execution Planlarları saklayarak geriye doğru inceleme yapmamızı ve istediğimiz birini kullanmamızı sağlayacak.

Kısaca aşağıdaki ayarla açıp kapatacağız.
    ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;
    




Temporal Tables (System-Versioned Tables)
Yazılım ekipleri ve DBA ler yıllardır tablolarda yapılan değişiklikleri görebilmek için taklalar atmak zorunda kalmışlardı ya trigger kullanarak insert, update ve delete ler başka bir tabloya alınmış ya da yazılım katmanında history tablolar yapılarak çözüm üretmişlerdi. SQL Server 2016 ile artık tabloların tarihsel olarak geçmişine otomatik olarak ulaşabileceğiz.

    		CREATE SCHEMA History
GO
CREATE TABLE dbo.Personel 
(
    PersonelNo int NOT NULL PRIMARY KEY CLUSTERED, 
    Ad nvarchar(50) NOT NULL, 
    Soyad nvarchar(50) NOT NULL,
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, 
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,   
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)   
)
WITH 
    (
		SYSTEM_VERSIONING = ON ( HISTORY_TABLE = History.Personel ) 
    );

    


Yeni bir özellik olduğundan dolayı bazı kısıtlamaları var tabii. Bunları uzun uzun yazmayalım linkten öğrenebilirsiniz. https://msdn.microsoft.com/en-US/library/mt604468.aspx


Always Encrypted
Verileriniz artık daha güvenli “Bizede mi lolo” Şifreleme için kullanılan KEY’i olmayan veriye ulaşamayacak (ama KEY’i yedeklemeyi unutmayın).


Dynamic Data Masking
Yine 3th parti uygulamalara taş koyacak Özellikle Day1 (devan diyenlerde var) ve UAT sistemlerde gizlenmesi gereken veriler için kullanacağımız güzel bir özellik. Daha detaylı olarak bir makalede inceleyeceğiz bununla birlikte default value ları aşağıdaki gibidir.




Birazda neler yapabileceğimizi hayal edelim :)

    
    select * from Personel 
    




Sonrasında Soyad kolonuna MASKED uyguluyoruz.
ALTER TABLE Personel
ALTER COLUMN Soyad ADD MASKED WITH (FUNCTION = 'partial(2,"XXX",0)');


ve bir tane TestUser oluşturarak onunla tablomuza Select çekiyoruz.

CREATE USER TestUser WITHOUT LOGIN;
GRANT SELECT ON Personel TO TestUser;

EXECUTE AS USER = 'TestUser';
SELECT * FROM Personel;
REVERT;



Veri tabanı içerisinde MASKED uygulanmış kolonları bulmak için aşağıdaki query çalıştırılır.

SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function
FROM sys.masked_columns AS c
JOIN sys.tables AS tbl 
    ON c.[object_id] = tbl.[object_id]
WHERE is_masked = 1;

        



Replication Geliştirmeleri

Memory-Optimized tablolarda artık replication destekleniyor. Azure database i içinde replication desteği başladı.