31 Mart 2014 Pazartesi

SSMS'te kişiselleştirilmiş ortam rengi kullanımı

Bu kullanımdan genelde bihaber olunduğunu düşünüyorum. Neden bahsettiğimi biraz daha açıklayayım, SQL Server Management Studio'nun 2008 versiyonuyla birlikte, "Connect to Server" penceresinde bağlandığımız ortam için renk seçimi yapabiliyoruz. Bu özellik, bağlandığımız ortamın ne kadar kritik bir ortam olduğunu bize görsel olarak da hatırlatıyor. Bazen, bazı ortamlarda o kadar çok SQL Server Instance'ı oluyor ve o kadar çok farklı Instance'a sürekli bağlanmamız gerekiyor ki, bazen feleğimiz şaşıyor ve nerede olduğumuzu karıştırıyoruz. Özellikle böyle ortamlar için bunun çok işe yarayabilecek bir özellik olduğunu düşünüyorum.

Aşağıdaki ekran görüntüsünde gördüğünüz Options >> düğmesine tıklayın.

Connect to Server penceresi
Aşağıdaki ekran görüntüsüyle karşılaşırsınız.

Connection Properties

Bu sekmede, yukarıda işaretlediğim alanı göreceksiniz. Eğer SQL Server Management Studio kullanarak bir SQL Server Instance'ına bağlanırken bu pencereden Select... düğmesine tıklayıp bir renk seçerseniz ve Use custom color kutucuğunu işaretlerseniz bağlandığınız SQL Server Instance'ı için hemen Query Editor penceresinin alt bölümündeki Status Bar aşağıdaki gibi renk değiştirecektir.


Tabii ki bu değişikliği Registered Servers penceresinde sakladığınız bağlantılar için de yapmanız bu özelliğin kullanımını pratikleştirecektir. Aksi takdirde her seferinde bu ayarı değiştirmeniz gerekir.

27 Mart 2014 Perşembe

Bir kaydın Data Cache'te olduğunu nasıl anlarsınız?

Belli bir veritabanındaki, belli bir tablodaki, belli bir kaydın Buffer Pool / Data Cache'te mi yoksa diskte mi olduğunu nasıl anlarsınız?

Öncelikle ilgilendiğiniz kaydın Page ID'sini bulmanız gerekiyor. Bunun için aşağıdaki gibi bir komut kullanabilirsiniz:

SELECT Teslim_Alan, sys.fn_physlocformatter(%%physloc%%) FROM dbo.tabloAdi WHERE xxx_No = 8071030185

Bu komutun sonucu aşağıdaki ekran görüntüsündeki gibi olacaktır:

İlgilendiğimiz kayıt ve Page bilgileri

sys.fn_physlocformatter(%%physloc%%) ile dönen veri bize ilgili kaydın fiziksel olarak nerede durduğunu gösteriyor. Açıklaması şöyle:

1 - Page'in bulunduğu dosyanın ID'si (File ID).
2 - Page ID
3 - Slot numarası

Artık elimizde ilgili kaydın gereken tüm fiziksel bilgileri var. Bu bilgileri kullanarak, önceki bir yazımda bahsettiğim gibi DBCC PAGE komutuyla ilgili Page hakkında daha fazla bilgiye de ulaşabilirsiniz, aşağıdaki DMV ile ilgili Page'in Data Cache'te olup olmadığını da kontrol edebilirsiniz.

SELECT * FROM sys.dm_os_buffer_descriptors WHERE page_id = 8867992 AND database_id = DB_ID()

Yukarıdaki komutu çalıştırdığınızda aşağıdaki gibi bir sonuç dönecektir:

İlgili Page'in Cache'te olup olmadığının sonucu
Eğer bir sonuç dönüyorsa, ilgili kayda ait Page, Cache'te demektir. Yani bu kayıt ile ilgili bir işlem yapmak istediğinizde, SQL Server diske gidip fiziksel IO yapmak zorunda kalmayacak, çok daha hızlı bir şekilde çalışabileceği RAM'e gidecek ve işlemi orada gerçekleştirecektir.

Not: sys.fn_physlocformatter(%%physloc%%) komutuna ait resmi Microsoft dokümanları bulamazsınız, çünkü Microsoft tarafından dokümante edilmemiş bir komuttur.

Ekrem Önsoy

26 Mart 2014 Çarşamba

Identity Key sorunu

Bugün bir iş arkadaşım aşağıdaki gibi bir hata aldıklarını söyledi:


Msg 2627, Level 14, State 1, Procedure SP_adi, Line 33
Violation of PRIMARY KEY constraint 'PK_tabloAdi_log'. Cannot insert duplicate key in object 'dbo.tabloAdi_log'. The duplicate key value is (19).

Yukarıdaki hata mesajını şöyle yorumladım: dbo.tabloAdi_log isimli tabloda PK_tabloAdi_log adında bir Primary Key Constraint'i var ve PK olarak belirlenen alana, bu alanda zaten olan 19 değeri tekrar girilmeye çalışılıyor.

Önce bu tablodaki Primary Key alanının hangisi olduğunu belirledim. Sonra bu alanın IDENTITY özelliğinin açık olup olmadığına baktım. Bunları belirledikten sonra bu alana kayıt girmeye çalışan SP'yi kontrol ettim. Her şey doğru görünüyordu, SP açık olarak ilgili PK alanına bir değer INSERT etmeye çalışmıyordu, yani sıradaki Identity değeri neyse onun oluşturulması gerekiyordu.

Daha sonra aşağıdaki komut ile bu tabloda otomatik olarak oluşturulan Identity değerinin ne olduğunu kontrol ettim:

SELECT IDENT_CURRENT('dbo.tabloAdi_log')

Yukarıdaki komutun sonucu 19 olarak geldi. Yani hata mesajımda aldığım değer ile aynı. SQL Server bu tablodaki PK alanında 19 değerini oluşturmaya çalışıyordu, fakat tabloda bu değer zaten olduğu için hata alıyordum.

ve aşağıdaki komut ile de tablodaki o anda bulunan en büyük değeri buldum:

SELECT MAX(id) FROM dbo.tabloAdi_log

Yukarıdaki komutun sonucu da 13617043 çıktı.

Henüz bulamadım, ama bir şekilde bir işlem tablonun SEED'ini değiştirmişti ve yeni oluşturulan Identity değerleri 19'dan devam ediyordu. Hata da bu nedenle alınıyordu. Aşağıdaki komut ile SEED değerini 13617044'ten devam ettirdim.

DBCC CHECKIDENT (dbo.tabloAdi_log, reseed, 13617044)

Ve sorunumuz çözülmüş oldu.

19 Mart 2014 Çarşamba

IO sorunu yaşanan mantıksal ve fiziksel veritabanı kaynağını bulma

İlginç bir başlık, değil mi?

Anlık olarak çalışan işlemleri görmek isteriz, özellikle de sorun yaşanıyorken. Bu gibi durumlarda sys.dm_exec_request DMV'sini de kullanabilirsiniz, sys.sysprocesses Catalog View'ünü de.

SELECT * FROM sys.dm_exec_requests WHERE session_id>50

veya

SELECT * FROM sys.sysprocesses WHERE spid>50 and status<>'sleeping'

kullanarak o anda çalışıyor olan işlemlere ait ayrıntıları görebiliriz. Örneğin bir tablonuz var diyelim ve bu tablo büyük bir tablo ve çok önemli bir Index'i eksik olduğu için SELECT ile veri okunurken bu tabloda çok PAGEIOLATCH_SH beklemesi yaşanıyor diyelim. Bu iki sorguyu da kullanarak, o anda  IO kaynağı yüzünden beklenen bu nesneye ait ayrıntıları bulabilirsiniz. Bu nesnenin, yani tablonun ID'sini, dolayısıyla adını ve sıkıntının hangi kayıtlarda yaşandığını, File ve Page ID'lerinden bulabilirsiniz.

Size örnek bir IO beklemesini yakalamak için bir SQL Server Instance'ında aşağıdaki gibi bir kod çalıştırdım:

SELECT * FROM sys.dm_exec_requests WHERE session_id>50 AND wait_type LIKE 'PAGE%'

Bu sorgu bana aşağıdaki sonucu verdi. Benzer bir komutu sys.sysprocesses kullanarak da yazıp çalıştırabilirdim, yine bana wait_resource alanındaki sonucun aynısını verirdi. Özellikle SQL Server 2000 ve altı versiyonlar için, DMV'ler SQL Server 2005 ve sonrasında geldiğinden, sys.sysprocesses'i kullanmanız gerekecek.


Buradaki wait_time değerinin küçüklüğüne bakmayın lütfen, dediğim gibi sadece örnek olsun diye gösteriyorum, bir sorun anında bu süre çok daha uzun olacaktır.

PAGEIOLATCH_SH, özet olarak bize sorgulanan kaydın içinde bulunduğu Page'in o anda RAM'de olmadığını, diskten okunup RAM'e aktarılıyor olduğunu söyler. Tabii ki bu anda ilgili SPID de Waiter List'te Suspended olarak diskten okuma işlemin yapılmasını bekliyordur. Diskten okuma işlemi yapıldıktan sonra CPU kaynağından 4ms'lik Quantum'unu kullanabilmek için Runnable Queue'da sıraya geçecek ve daha sonra da Running olacaktır, daha da merak edenler için, eğer 4ms'lik CPU kaynağı yetmezse ve başka bir IO vb kaynağa ihtiyacı yoksa, o zaman tekrar Runnable Queue'a geçecek ve Runnable durumda CPU kullanımı için sırasını bekleyecektir, eğer IO vb. başka bir kaynağa ihtiyacı varsa da o zaman tekrar Waiter List'e geçecek ve Suspended durumda o kaynağı kullanacak ve akabinde tekrar Runnable Queue'ya geçecektir. Eğlenceli, değil mi?

Burada odaklanmanızı istediğim şey, wait_resource alanındaki değer. Burada gördüğünüz 11:5:15921857 değerini tek tek açıklayayım.

11, veritabanının ID'sidir, aşağıdaki gibi bir sorgu ile veritabanının ID'sinden adını bulabilirsiniz:

SELECT DB_NAME(11)

5, Veritabanının dosyasının ID'sidir, yani File ID. Aşağıdaki sorgu ile dosya ID'sinin hangi diskteki dosyaya ait olduğunu bulabilirsiniz.

SELECT * FROM sys.sysfiles WHERE fileid = 5

15921857, 11 ID'li veritabanının 5 ID'li dosyasında bulunan ve işlem yapmak için diskten RAM'e aktarılmasını beklediğimiz Page'in ID'sidir.

DBCC PAGE komutu, Microsoft tarafından resmen dokümante edilmemiş, fakat çok eskiden beri SQL Server'da bulunan ve kullanılan bir komuttur. Bu komutu kullanabilmek için öncelikle işlem yapacağınız oturumda 3604 numaralı Trace Flag'i etkinleştirmelisiniz, aşağıdaki gibi:

DBCC TRACEON(3604)

Daha sonra DBCC PAGE komutu ile ilgili sayfaya ait ayrıntılara ulaşabilirsiniz:

DBCC PAGE(11, 5, 15921857, 3)

Bu komutu çalıştırdıktan sonra karşınıza aşağıdaki ayrıntılar gelecek:


Metadata: ObjectId bölümünde, okuma işleminin yapıldığı mantıksal alan olan ilgili tablonun ID'sini görebilirsiniz. Bu tablonun adını da ilgili veritabanının içinde aşağıdaki komut ile bulabilirsiniz:

SELECT OBJECT_NAME(1777701681, 11)

Bu örnekte 1777701681 nesnemizin ID'si, 11 ise hatırlarsanız veritabanımızın ID'sidir.

DBCC PAGE komutu her ne kadar dokümante edilmemiş bir komut olsa da, gerek Microsoft'ta çalışmış veya çalışıyor olan kişilerce, gerekse diğer bazı araştırmacı arkadaşlarca kendisi hakkında bayağı yazılıp çizilmiştir. Eğer ilginizi çektiyse bu komut hakkında aşağıdaki 2 sayfadan daha fazla bilgi edinebilirsiniz:

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/10/625659.aspx
http://support.microsoft.com/kb/83065/en-us

DBCC PAGE komutunu çalıştırabilmeniz için System Administrator (sysadmin) yetkisine sahip olmanız gerektiğini de hatırlatmalıyım.

Ekrem Önsoy

SQL Server 2014 Nisan 1'de yayınlanıyor!

Bu duyuru Microsoft tarafından dün yapıldı.

Bu Relase ile ilgili Microsoft'un yayınladığı notlardan birkaç alıntı paylaşmak istiyorum.

Microsoft'un TAP programı vardır, henüz yeni bir SQL Server versiyonu geliştirilirken bazı büyük şirketlere yapılır bu teklif. Eğer o şirket TAP programına katılmayı kabul ederse, o zaman SQL Server'ın o yeni versiyonu daha piyasaya çıkmadan kullanmaya başlar. Yaşınılan sorunlar doğrudan Microsoft'a bildirilebilir ve düzeltmeler yapılır vs. Bu kapsamda SQL Server 2014'ü kullanan bazı şirketlere ait bazı istatistikler paylaşıldı. In-Memory hakkında olanlar aşağıda.
  • Bwin, is the world’s largest regulated online gaming company. SQL Server 2014 lets bwin scale its applications to 250K requests a second, a 16x increase from before, and provide an overall faster and smoother customer playing experience.
  • Ferranti, which provides solutions for the energy market worldwide, is collecting large amounts of data using smart metering. They use In-Memory OLTP to help utilities be more efficient by allowing them to switch from the traditional meter that is measured once a month to smart meters that provide usage measurements every 15 minutes. By taking more measurements, they can better match supply to demand. With the new system supported by SQL Server 2014, they increased from 5 million transactions a month to 500 million a day.
  • TPPa clinical software provider, is managing more than 30 million patient records. With In-Memory OLTP, they were able to get their new solution up and running in half a day and their application is now seven times faster than before, peaking at about 34,700 transactions per second.
Transaction'ları ölçme açısından size şöyle bir örnek verebilirim, çalıştığım bankalardan birinde en çok Transaction yapılan zamanlarda 6.600 Batch per second görüyorduk bu bankanın o zaman 600 tane şubesi ve 2.000 kusür ATM'si varken, tabii ki internet şubesi de dahil buna.

Ayıca aşağıdaki gibi bir not da düşmüşler, henüz pratikte nasıl kullanıldığını görmedim; ama teftiş açısından en azından şimdilik teoride kullanılabilir diyebiliriz.

  • Enhanced security – Achieve greater compliance with new capabilities for separation of duties.  For example, a database administrator can manage data without seeing the sensitive data itself.

Yine yeni bir özellik olarak Backup'ların Encrypt edilebilme özelliği gelmiş. Böylece yedekler daha güvenli bir şekilde buluttaki sunucularda konumlandırılabilecek.

  • Backup to Windows Azure enables customers to configure backups, which are compressed and encrypted, directly to Azure for greater data protection, taking advantage of Microsoft’s global datacenter.
  • Beyond SQL Server 2014, this technology will also be generally available as a standalone tool, SQL Server Backup to Windows Azure Tool, on April 1 supporting prior versions of SQL Server.
Daha fazla ayrıntı için:

Ekrem Önsoy

14 Mart 2014 Cuma

DBCC CHECKDB çalıştırdım, ne kadar sürecek acaba?

Konu başlığında yazan sorunun cevabı tabii ki bu komutu çalıştırdığınız veritabanının büyüklüğüne, DBCC CHECKDB ile birlikte kullanacağınız parametrelere (NOINDEX, REPAIR_FAST gibi) ve donanım kaynaklarınıza bağlı.

Bununla birlikte, henüz DBCC CHECKDB'yi çalıştırmadan da, eğer daha önceden bir SQL Agent Job çalıştırdıysanız ne kadar sürdüğünü o Job'ın History'sinden de öğrenebilirsiniz.

Fakat benim özellikle paylaşmak istediğim şey ise, DBCC CHECKDB'yi çalıştırdıktan kısa bir süre sonra ne kadar süreceğini bir DMV yardımıyla görebileceğinizdir, bazılarını belki tahmin etti bile: sys.dm_exec_requests!

Ortamınıza uygun (ki umarım bir test ortamıdır bu!) DBCC CHECKDB komutunu çalıştırdıktan bir süre sonra başka bir Query Editor penceresinde aşağıdaki komutu çalıştırıp "percent_complete" ile "start_time" alanındaki değerlere bakıp, işlemin yaklaşık ne kadar sürede tamamlanacağını hesaplayabilirsiniz.


SELECT [session_id], [start_time], [status], [command], db_name([database_id]) AS [db_name], [wait_type], [wait_resource], [percent_complete] FROM sys.dm_exec_requests WHERE [session_id] > 50

Sorgunun sonucu
Yukarıdaki ekran görüntüsüne göre demek ki veritabanım için bu test ortamında DBCC CHECKDB sorgulamam ortalama 3 saat sürecek.

sys.dm_exec_requests DMV'sindeki "percent_complete" alanı aşağıdaki komutların tamamında işlemlerin ne kadarının tamalandığını söyleyecektir.

  • ALTER INDEX REORGANIZE
  • AUTO_SHRINK option with ALTER DATABASE
  • BACKUP DATABASE
  • DBCC CHECKDB
  • DBCC CHECKFILEGROUP
  • DBCC CHECKTABLE
  • DBCC INDEXDEFRAG
  • DBCC SHRINKDATABASE
  • DBCC SHRINKFILE
  • RECOVERY
  • RESTORE DATABASE,
  • ROLLBACK
  • TDE ENCRYPTION

sys.dm_exec_requests DMV'si hakkında daha ayrıntılı bilgi için: http://technet.microsoft.com/en-us/library/ms177648.aspx

Ekrem Önsoy

DBCC CHECKDB kullanımı

DBCC CHECKDB'yi bir üretim ortamında kullanmanın pek de akıllıca olmadığını muhtemelen zaten görmüşsünüzdür veya bu yazıyla görüyorsunuz.

Bunu önceden biliyor olun veya yeni öğreniyor olun, eğer bu öğüdün içini doldurmadıysanız bu öneriyi kulak arkası edeceğiniz zamanlar da olacaktır.

Bu nedenle bu konuda size bir fikir vermek istedim! Test ortamlarımın birinde, sistem kaynaklarıyla ilgili bir test yaparken, testte DBCC CHECKDB'yi kullanmak istedim; çünkü bu komutun ne kadar kaynak canavarı olduğunun gayet farkındayım!

Öncelikle test ortamımdaki SQL Server Instance'ının doğru miktarda RAM kullandığından ve İşletim Sistemine ve diğer uygulamalara da yeterli RAM bıraktığımdan emin oldum.


SQL Server Instance'ının Max Server Memory ve Min Server Memory ayarlarını 6.144MB yaptım.


EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'max server memory (MB)', 6144
EXEC sp_configure 'min server memory (MB)', 6144
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE


Yapmak istediğim test özellikle RAM hakkındaydı, bu kapsamda SQL Server servisini yeniden başlattım, Performance Monitor'ü açtım, özellikle SQL Server:Memory Manager'daki Target Server Memory (KB) ve Total Server Memory (KB) sayaçlarına diktim gözümü.

Performance Monitor, SQL Server: Memory Manager object
Hemen bu iki sayacın ne olduğunu da açıklayayım:
Target Server Memory (KB): İlgili SQL Server Instance'ınızın Data Cache için kullanabileceği azami değerdir.

Total Server Memory (KB): İlgili SQL Server Instance'ınızın an itibariyle Data Cache'te depoladığı veri miktarıdır.


Çok kısa bir sürede, 2 dakika içerisinde Total Server Memory (KB) sayacının değeri 6GB'e ulaştı! Yani SQL Server maksimum kullanabileceği RAM'i kullandı, sadece 2 dakikada. Bunun Production sunucunuzda olmasını istemezsiniz. Çünkü bu, tüm Data Cache/Buffer Pool'unuzdaki Page'lerin diske yazılıp RAM'dan çıkacağı anlamına gelir ve DBCC CHECKDB işleminden sonra uygulamanız vasıtasıyla yapılacak tüm rutin işlerin verileri diskten okuyacağı, yani çok fazla IO işlemi yapılabileceği demektir. Özellikle de ihtiyaçlarınızın küçük olmadığı, örneğin gün içinde Batch Requests/Sec sayacının 1000'den fazla bir değere sahip olduğu bir ortam düşünülürse ve sitem kaynaklarınız da uçsuz budaksız değilse bu pek de iç açıcı olmazdı.

Bu nedenle önerilere kulak vermenizi ve DBCC CHECKDB'yi, Restore testlerinizi (ki umarım yapıyorsunuzdur?) yaptığınız yerde çalıştırmanızı önemle tavsiye ederim!

Ekrem Önsoy

13 Mart 2014 Perşembe

HATA: The database VeritabanıAdı is not accessible

HATA
The database VeritabanıAdı is not accessible. (Object Explorer)

AÇIKLAMA
Aslında bu hata için Blog yazmayacaktım, fakat başka ve buna benzer bir hata için yazdığım yazının en çok aratılan yazı olduğunu görünce, belki bu da çok aratılıyordur diye bunu da kayıtlara geçmek istedim.

Bu hatayı, SQL Server Management Studio'daki Object Explorer penceresindeki Databases altındaki veritabanlarından birini açmaya çalıştığınızda alabilirsiniz. Nedeni ise açmaya çalıştığınız veritabanına erişim yetkinizin bulunmamasıdır.

ÇÖZÜM
Eğer yetkiniz varsa kendiniz, eğer yoksa veritabanı yöneticisine söyleyip ilgili SQL Login'inize yine ilgili veritabanına erişim için yetki istemelisiniz. Tabii ki çoğu durumda sadece erişim yetkisi yetmeyecektir, aynı zamanda okuma (db_datareader) ve yazma (db_datawriter) gibi yetkilere de ihtiyacınız olacaktır; tabii ki ihtiyaçlar çok çeşitli olduğu için artık ne yetki gerektiğini sizin belirlemeniz gerekiyor, ben sadece çok genel hatlarıyla bir tanımlama yaptım.

Bu arada erişim yetkisi derken neyi kastettiğimi de hemen belirteyim, ilgili veritabanında bir kullanıcınızın (User) olmasından bahsediyorum. Bunu aşağıdaki şekilde oluşturabilirsiniz:

USE [VeritabanıAdı]
GO
-- Aşağıdaki kullanıcı, belirtilen Login'e Map edilerek oluşturulacaktır ve artık veritabanına erişebilir olacaktır. Bununla birlikte, okuma ve yazma gibi yetkilerin ayrı komutlarla verileceğini dikkate almalısınız. Aşağıdaki kullanıcı varsayılan olarak yalnızca Public rolüne üye olarak oluşturulacaktır. Çoğu veritabanında da Public rolüne yetki verilmez, ki doğru olanı da budur.
CREATE USER [KullanıcıAdı] FOR LOGIN [LoginAdı] WITH DEFAULT_SCHEMA=[dbo];

Ekrem Önsoy

SQL Login'lerinizin şifreleri ne kadar güvenli?

Bir SQL Server üretim ortamımızı daha güvenli hale getirmek ve bir taraftan da teftişlere hazır hale getirmek için bazı güvenlik çalışmaları yapıyordum. Bahsini ettiğim ortamda 900'den fazla SQL Login vardı. Öncelikle aşağıdaki sorgu ile bu SQL Login'lerin ne kadarının Password Policy ile kontrol edildiklerine baktım:

SELECT is_policy_checked, count(*) FROM sys.sql_logins GROUP BY is_policy_checked

Büyük çoğunluğunun herhangi bir şifre güvenliği politikasına zorlamaya tabii tutulmadığını gördükten sonra aklıma bu SQL Login'lerin birçoğunun zayıf şifreler kullanma olasılıkları geldi ve hemen şifreleri test etmeye başladım!

Öncelikle testi nasıl yaptığımdan bahsedeyim. SQL Server'da "PWDCOMPARE" adında bir Function var. Bu Function temel olarak 2 adet parametre alıyor, birincisi deneyeceğiniz şifre, ikincisi de encryption has. Bu Function hakkında daya ayrıntılı bilgiye Books Online'dan da erişebilirsiniz: http://msdn.microsoft.com/en-us/library/dd822792(v=sql.100).aspx

Aşağıdakilere benzer komutlar çalıştırdım:


SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('1234', password_hash) = 1 

SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('12345', password_hash) = 1 
SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('123456', password_hash) = 1 
SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('12345678', password_hash) = 1 
SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('1', password_hash) = 1 
SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('111111', password_hash) = 1 
SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('qwerty', password_hash) = 1 
SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('abc123', password_hash) = 1 


-- Aşağıdaki sorgu, şifresini boş bırakanları getirir
SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('', password_hash) = 1 


-- Aşağıdaki sorgu ise, kullanıcı adı ile şifresi aynı olanları getirir
SELECT name FROM sys.sql_logins WHERE PWDCOMPARE(name, password_hash) = 1 

Karşılaştığım sonuçlara gelirsek, sonuç aslında tam da beklediğim gibiydi. Yukarıda belirttiğim parametrelerle onlarca kullanıcı yakaladım.

Tüm teftişler bu kadar ayrıntılı güvenlik kontrolü yapmıyorlar, aslına bakarsanız çok azı elle tutulur şeyler soruyor ve istiyor. Birçok teftiş (bankalarınkiler dahil) sadece ellerine tutuşturulan birkaç standart soruyu soruyorlar ve verdiğimiz (güvenilirliği tartışılır) kanıtları da yorumlamaktan çoğu zaman acizler. Üzgünüm, ama senelerdir karşılaştığım gerçek böyle. Eğer teftişler gerçekten işlerini düzgün yapsalar, özellikle IT tarafında (diğer taraflardan haberim yok) hem saygınlıkları ve güvenilirlikleri artacak, hem de kurumları daha güvenli çalışmaya teşvik etmiş olacaklar.

Özellikle devraldığınız SQL Server Instance'larında buna benzer güvenlik çalışmalarını yapmanızı kesinlikle tavsiye ederim. Mümkün olan her SQL Instance'ında Login'ler için Password Policy'yi etkinleştirmeye çalışmalısınız ki belli bir güvenlik politikasını tutturabilin.

Verizon Business'in 2012 veri sızması araştırması raporuna göre 2011 senesinde sızdırılan verilerin %96'sı veritabanı sunucularından alınmış. Bunun %55'inde şifresi tahmin edilebilir hesaplar kullanılmış, %40'ında ise çalınan hesap bilgileri kullanılmış. Application Security Inc'in araştırmasına göre veritabanlarına olan ilk 10 tehdidin başında şifresi zayıf veya boş olan kullanıcılar geliyor. Yani bariz bir şekilde iki çalışma da birbirini tamamlıyor. Benim yaptığım test ile, 3 çalışma da birbirini doğruluyor. Siz de kendi ortamınızda SQL Login'lerinizi test edip ve bulgularınızı paylaşırsanız sevinirim!

Ekrem Önsoy

12 Mart 2014 Çarşamba

The transaction log for database ... is full due to 'LOG_BACKUP'


HATA:
Message
Executed as user: DOMAIN\svc_SQLService. The transaction log for database 'veritabanıAdı' is full due to 'LOG_BACKUP'. [SQLSTATE 42000] (Error 9002)  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

AÇIKLAMA:
Yedek almak istediğinizde böyle bir hata alabilirsiniz. Aslında hata mesajı hatanın ne olduğunu anlatıyor. Temel sorun, Transaction Log dosyanızın dolmuş olması. Olası senaryo, veritabanınızın Recovery Model'ı FULL. Çok uzun süren veya açık kalan bir Transaction var ya da işlem(ler) yapıldı yapıldı ve Transaction Log dosyasının içindeki boşluk doldu ve Transaction Log dosyasının otomatik büyümesi kapalı veya disk doldu.

ÇÖZÜM:
Böyle bir durumda, eğer düzenli olarak Transaction Log yedeği almayacaksanız ve Log Shipping'iniz yoksa, o zaman veritabanının Recovery Model'ını SIMPLE'a çevirmeyi düşünebilirsiniz. Eğer Transaction Log dosyanızın bulunduğu disk tamamen dolduysa, o zaman sadece Recovery Model'ını SIMPLE'a çevirmek de sizi kurtarmayabilir; böyle bir durumda başka müsait bir diske bir tane daha Transaction Log dosyası oluşturmayı düşünebilirsiniz, daha sonra ortalık durulunca bu yeni Transaction Log dosyasını kaldırabilirsiniz.

11 Mart 2014 Salı

Index'lerden Corrupt kayıtları kurtarma

Aynen öyle! Şahsen, bilmiyordum. Yeni öğrendim ben de. Bu yazıda bunun nasıl olduğunu anlatmayacağım, pek vaktim yok. Fakat yine de nasıl olduğundan özetle ve teoride bahsetmek istedim. Sonra vaktim olursa belki ayrıntılı şekilde açıklama şansım da olur.

Şöyle ki, örneğin toplamda 3500 kayıt olan, alan1 alanı Clustered Index, alan2 alanı da Nonclustered Index olan bir tablonuzun 306 isimli Data Page'i Corrupt oldu diyelim. Bu Data Page de 180 tane kayıt içeriyordu diyelim. DBCC CHECKDB'yi çalıştırdığınızda, hata alan Page ID'sini göreceksiniz zaten. Daha sonra sorun yaşanan tablodaki kayıtları sorgulayın örneğin

SELECT alan1, alan2 FROM tablo ORDER BY id ASC

Bu şekilde Corrupt olan sayfaya kadar olan kayıtlar sorgulanacak ve Corrupt sayfaya gelindiğinde bağlantı kesilecektir. Fakat SSMS'teki Results penceresinde hangi kaydın id'sine kadar gelindiğini göreceksiniz, bu kayıt sayısı da 2500 olsun. Daha sonra da bu sorgunun ters yönlüsünü çalıştırın

SELECT * FROM tablo ORDER BY id DESC

Bu sorgu da 820 kayıt getirdi diyelim. Toplamda tablomuzda 3500 kayıt vardı, ilk sorgu 2500 kayıt getirdi, ikinci sorgu da 820 kayıt getirdi, böylece Corrupt olan Data Page'imizde ne kadar kayıt sayısı olduğunu bulabiliriz.

Ayrıca eğer ilgili kayıtlar belli bir değere göre ilerliyorsa, örneğin bir Identity'ye göre yine DBCC IND ('veritabanı adı', 'tablo adı', 1) komutu ile (Undocumented bir komuttur) Root Page'i bulup, oradan da hata alınan Corrupt Page'ten sonraki Page'in içine DBCC PAGE('veritabanı adı', 1, , 3) komutuyla (Undocumented bir komuttur) bakıp Corrupt olmuş Page'in içindeki kaydın nerede sonlandığını bulabiliriz. Tabii ki aynı yöntem ile Corruption'ın başladığı kaydı da bulabiliriz.

sp_helpindex 'tablo' komutuyla tablodaki Index'lerin hangi alanları kapsadığına bakarız. Bu komut INCLUDED alanları göstermeyeceği için, Index'lerin Script'lerine bakmak iyi olacaktır. Gerçek bir senaryoda böyle bir durumda ne kadar çok Index'imiz varsa, o kadar çok kaydı kurtarabileceğiz anlamına gelir. Tabii ki uygun bir yedek alma stratejimiz yoksa, en son başvuracağımız yöntemlerden biridir bu...

Bu noktada tablo isimli tablonun Schema olarak bir kopyası oluşturulmalı ve aşağıdakine benzer komutlarla Index'lerden (eğer onlar da Corrupt olmadılarsa tabii, bizim senaryomuzda sadece veri Corrupt oldu) veriler kurtarılmalıdır:

SELECT alan1, alan2 FROM tablo WITH (Index=3) ORDER BY id ASC

Nonclustered Index'ler her zaman Clustered Index Key'lerini de barındırdığı için, sadece Nonclustered Index bile böyle bir senaryoda yetecektir...

Harika değil mi?

Ekrem Önsoy

10 Mart 2014 Pazartesi

Başarıyla alınan yedekler nasıl Error Log'a kaydedilmez?


Tek bir komut ile:

DBCC TRACEON (3226, -1)

Bu şekilde çalıştırırsanız, bu Trace Flag'i Global olarak açmış olursunuz ve SQL Server Instance'ınız yeniden başlasa bile bu Trace Flag tekrar otomatik olarak açılır ve SQL Error Log'da da aşağıdaki gibi bir kayıt görürsünüz:


DBCC TRACEON 3226, server process ID (SPID) 107. This is an informational message only; no user action is required.


Biraz kestirme oldu. Açıklayayım. Bugüne kadar yüzlerce kez Log Shipping kurmuşumdur ve özellikle kritik veritabanlarının Transaction Log yedeklerini ortalama 5 dakikada bir, hatta duruma göre 1 dakikada bir aldırmamız gerekebilir. Böyle senaryolarda, hele ki aynı SQL Server Instance'ında daha başka Log Shipping kurulmuş veya Transaction Log yedekleri alınan veritabanları varsa ve bir de bu veritabanlarının sayısı çoksa, işte o zaman o SQL Server Instance'ının SQL Error Log, Default Trace ve Windows Application Event Log'larındaki kayıtların çok büyük bir bölümünü aşağıdakine benzer kayıtlar oluşturuyor demektir.



Log was backed up. Database: , creation date(time): 2014/02/05(10:46:27), first LSN: 122:16:1, last LSN: 122:56:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\BACKUPTRN\\_20140309134500.trn'}). This is an informational message only. No user action is required.


Bir sorun yaşadığımızda, ilk bakacağımız yerlerin başında SQL Error Log, Windows Event Log'ları ve Default Trace dosyaları gelir. Eğer bu ortamların içeriğinin çoğu yukarıdaki gibi başarılı yedek alma mesajları dolduruyorsa, o zaman bunu yapmamız çok zorlaşıyor. Bu nedenle ben Log Shipping kurduğum ortamlarda bu Trace Flag'i muhakkak kullanırım. Bugüne kadar da olumsuz bir yanını görmedim.

Fakat şunu da belirtmem gerekir, örneğin geçenlerde Log Shipping kurduğum ortamlardan birindeki bir veritabanının Log Shipping'inin hata verdiğine dair mesajlar almaya başladım. Girip kontrol ettiğimde, çıplak gözle bakıldığında tüm yeni T-Log yedekleri hedefte var görünüyordu; fakat ilginç bir şekilde sıradaki T-Log Restore edilmeye çalışıldığında hata veriyordu. Hata mesajlarından, bu T-Log yedeğinden önce başka bir yedek olması gerektiği anlaşılıyordu, fakat T-Log yedek alma işinin de zaman aralığına baktığımda dosyalar gayet nizami görünüyordu. Aklıma hemen başka birisinin manuel olarak T-Log yedek alma olasılığı gelince SQL Error Log ile Default Trace'i incelemeye başladım ve tam da tahmin ettiğim gibi çıktı! Birisi benden habersiz T-Log yedeği almıştı. Eğer 3226 Trace Flag'i bu ortamda açık olsaydı, o zaman SQL Error Log'dan ve Windows Event Log'dan bu kayıtları göremezdim, ama tabii ki Best Practice olarak Default Trace'im açık olurdu ve buradan Trace Flag 3226 açık olsa da görebilirdim.

Bununla birlikte, 3226 Trace Flag'ini kullandığınızda eğer yedek alma işiniz hata verirse, SQL Error Log'da aşağıdaki gibi hata mesajlarını görmeye devam edersiniz:

BACKUP failed to complete the command BACKUP LOG . Check the backup application log for detailed messages.




9 Mart 2014 Pazar

DMV: sys.dm_db_persisted_sku_features

Bazı projelerde veya senaryolarda bazı veritabanlarını değişik SQL Server Edition'ları arasında taşımamız gerekiyor. Örneğin bazı kurumlarda bilinçsiz bir şekilde SQL Server Evaluation Edition, Enterprise Edition veya Datacenter Edition (2008 R2) Instance'ları kurulmuş olabiliyor. Bu sistemleri incelediğimizde ve ilgili yöneticilere bu konuda bilgi verdiğimizde, lisans maliyetlerini duyduklarında "bizim buna ihtiyacımız yok, daha düşük bir Edition da kullanabiliriz eğer işimizi görecekse" diyebiliyorlar. Çünkü Enterprise Edition ile örneğin Standard Edition arasındaki lisans maliyetleri arasında çok fark var ve bazı şirketler için bu farkı her ay gereksiz bir yere ödemek mantıksız geliyor, haklı olarak...

Böyle durumlarda, yani daha yüksek seviyedeki bir Edition'daki bir Instance'tan daha düşük seviyedeki bir Edition'daki Instance'a veritabanı taşıyacakken dikkat etmemiz gereken çok kritik bir konu var. Taşıyacağımız veritabanında sadece yüksek seviyedeki bir Edition'da kullanılabilecek bir özellik kullanılmışsa, bu veritabanını bu haliyle daha düşük bir seviyedeki bir Edition'a taşıyamayız. Bir örnekle devam edelim.

Örneğin ISTANBUL\Marketim isimli bir SQL Server 2012 Enterprise Edition Instance'ımızdaki Table Partitioning özelliğini de barındıran Arsiv_2013 isimli veritabanımızı bir başka SQL Server 2012 Standard Edition Instance'ımıza taşıyamayız. Çünkü Table Partitioning sadece Enterprise Edition'larda olan bir özelliktir ve Standard Edition'larda bu özellik kullanılamamaktadır.

SQL Server bize bunu, Database Restore'un bitirilme, yani Database Recovery aşamasında bildirecektir. Yani siz ilgili veritabanının, yine örneğimiz üstünden gidersek Enterprise Edition Instance'ından yedeğini alırsınız ve hedef SQL Server Instance'ında Restore etmeye de başlayabilirsiniz. Recovery aşamasına kadar sorun da çıkmaz, fakat Recovery aşamasında SQL Server, veritabanının herhangi bir üst Edition özelliği taşıyıp taşımadığına bakar ve eğer böyle bir şey varsa, o zaman Restore'unuz hata verir ve tamamlanamaz.

Bu kontrolü en son aşamada yapmasının nedeni ise, örneğin bir Enterprise Edition özelliğini Database Backup aldıktan sonra yapabileceğiniz gerçeğidir. Şöyle düşünün, bir Enterprise Edition, Evaluation Edition veya Developer Edition Instance'ında bir veritabanı oluşturuyorsunuz, bunun Full Database Backup'ını alıyorsunuz, sonra bir tablodaki veriyi sıkıştırıyorsunuz (ki bu da Enterprise Edition özelliğidir) ve sonra bu değişikliği de barındıracak bir Transaction Log yedeği alıyorsunuz. Bu Full Database Backup'ı hedef Standard Edition Instance'ınıza NORECOVERY modunda Restore ettiğinizde, henüz Recovery işlemi tamamlanmadığından sorun olmayacaktır; o son aldığınız Transaction Log yedeğini de NORECOVERY modunda Restore ettiğinizde yine hata almazsınız, çünkü veritabanı hala Recovery işlemini yapmadı; fakat veritabanını RESTORE DATABASE dba_test komutuyla Recover etmeye kalktığınız anda aşağıdaki hataları alacaksınız.


Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 909, Level 21, State 1, Line 1
Database 'dba_test' cannot be started in this edition of SQL Server because part or all of object 'table_adi' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Msg 933, Level 21, State 1, Line 1
Database 'dba_test' cannot be started because some of the database functionality is not available in the current edition of SQL Server.


İşte böyle bir senaryoda, yani bir üst Edition'dan alt bir Edition'a veritabanı taşıma işiniz olduğunda, özellikle de boyut olarak büyük veritabanlarında, taşıma işlemi öncesinde ilgili veritabanlarını sys.dm_db_persisted_sku_features isimli DMV ile sorgulamanızda fayda var. Bu DMV size, diğer Edition'larda olmayıp ilgili veritabanında kullanılmış olan özellikleri raporlayacaktır. Bu sayede, özellikle de gerçekten aktif olarak kullanmadığınız bir özellik varsa, bu özelliği kapatıp taşıma işleminizi  başarıyla gerçekleştirebilirsiniz. Eğer gerçekten vazgeçemeyeceğiniz bir özellik mevzu bahis ise de, o zaman ilgili yöneticilerle bunu masaya yatırmanız gerekecek.

Örnek sorgu:
SELECT * FROM sys.dm_db_persisted_sku_features

Ekrem Önsoy

6 Mart 2014 Perşembe

SQL Server IO Hataları

Kaçınılmaz bir şekilde her DBA bir gün Database Corruption sorunlarıyla karşılaşacaktır. İnsanın aklına ister istemez Zincirlikuyu Mezarlığı geliyor...

Konumuza dönersek! Bu hatalarla karşılaştığınızda hata kodları olarak aşağıdaki gibi kodlar göreceksiniz:

I/O Error 823: Bu hatayı aldığınızda, SQL Server, Windows İşletim Sisteminden, diskten veri okumasını ister ve Windows da SQL Server'a "HAYIR!" der, işte bu durumda 823 kod numaralı hata ile karşılaşırız.

I/O Error 824: SQL Server, Windows İşletim Sisteminden, diskten veri okumasını ister, Windows ilgili veriyi diskten okur ve SQL Server'a geri verir; fakat SQL Server getirilen veriyi denetler ve doğru yapılanmamış olduğunu görür, işte o zaman bu hatayı alırsınız.

Bu hatayı alan bağlantıların, bağlantısı kopacaktır.

Yakalanan bu hatalar hem SQL Error Log'a işlenir, hem de msdb.dbo.suspect_pages tablosuna (SQL Server 2005 ve üstü) işlenir. Ayrıca Windows Application Event Log'una da işlenir.

Eğer bu hatayı çok alıyorsanız, o zaman msdb veritabanındaki suspect_pages tablonuz dolacak ve dolacaktır, ta ki siz fark edene veya disk dolana kadar. Çünkü bu tablo otomatik olarak temizlenmemektedir. Tabii ki bu durum da başka bir sorun demektir.

Ekrem Önsoy

4 Mart 2014 Salı

SQL Server 2000 Bugs

Az önce ilginç bir şey öğrendim, en azından bana ilginç geldi; çok kısa bir bilgi de olsa ilginç olduğu için paylaşmak istedim.

Paul Randal, Microsoft'ta çalışırken bir yazılımcı çalışanına Microsoft Case'lerini çözdükleri müşteri veritabanını SQL Server 2000 için SQL Server kaynaklı Corruption hataları için kontrol ettirmiş. Kontrolün sonucunda, 2005 senesine kadar SQL Server 2000 Bug'larından kaynaklanan 50 tane Case'e rastlanmış! Şahsen ben daha fazla bir sonuçla karşılaşılmasını beklerdim.

Ekrem Önsoy

Ad-Hoc planlarla Procedure Cache'in dolması

Birçok konu gibi, bu konuda çok uzun ve derin bir konu. Fakat temel olarak bilmenizde fayda olan birkaç şeyi paylaşmaya çalışacağım sizlerle.

SQL Server'da çalıştırdığınız sorgular, sorgunun tipine göre Adhoc, Prepared, Proc... gibi Cache tipi şeklinde kaydedilir. Adhoc sorguların planları çoğu zaman sadece 1 kere kullanılır. Eğer sisteminizde çok fazla Adhoc sorgu varsa, muhtemelen hem RAM kaynağınızı hem de CPU kaynağınızı ve haliyle de IO kaynağınızı verimli kullanamıyorsunuz demektir. Bununla birlikte eğer dinamik SQL kullanıyorsanız bu sorunlara SQL Injection gibi olası güvenlik sorunları da eklenir. Önceden de dediğim gibi bu yazımda bunların ayrıntısına girmeyeceğim, size Adhoc sorguların Procedure Cache'te ne kadar yer kapladığını nasıl kontrol edebileceğinizi ve bu sorunla nasıl başa çıkabileceğinizi aktaracağım. Aktaracağım diyorum, çünkü bu konudaki birçok şeyi ben de Kimberly L. Tripp'ten öğrendim.

Adhoc sorguya örnek vermeden geçmek istemem:

SELECT * FROM Tablom WHERE AlanAdim LIKE '%xxx%'
SELECT * FROM Tablom WHERE AlanAdim LIKE  '%xxx%'
SELECT * FROM Tablom WHERE AlanAdim LIKE   '%xxx%'
SELECT * FROM Tablom T WHERE AlanAdim LIKE '%xxx%'
SELECT * FROM Tablom     WHERE AlanAdim LIKE '%xxx%'

Her ne kadar tüm bu sorgular birebir aynı işi yapıyor olsa da, hepsi için de farklı planlar oluşturulur. Adhoc sorgular içerisindeki bir boşluk farkı bile farklı plan yaratılmasına neden olur. Eğer "Optimize for Adhoc Workloads" özelliğini de kullanmıyorsanız, o zaman Procedure Cache'inizde hiç kullanılmayacak olan MB'larca ve hatta GB'larca çöp Execution Plan olabilir. İşte ben de size bunları nasıl görebileceğinizi ve bunlardan kurtulabileceğinizden bahsedeceğim.

Öncelikle "Optimize for Adhoc Workloads" seçeneğinden bahsedeyim. Bu seçenek ile, her Adhoc sorgu çalıştırılışında yeni bir plan oluşturulmasını kısmen de olsa engellersiniz. Bu sayede plan yine oluşturulur, fakat çok özet bir şekilde oluşturulur ve KB'larca ve bazen MB'larca yer kaplayacağına (evet, bir Execution Plan'ın boyutu MB'ları bulabilir ve eğer benzer Adhoc sorgular çok fazla çalıştırılırsa sıkıntı yaşarsınız) Byte'lar ölçüsünde yer kaplar. Eğer aynı sorgu birebir şekilde 2. kez çalıştırılırsa, tam Execution Plan'ı o zaman oluşur. Bu özelliği aşağıdaki şekilde etkinleştirebilirsiniz. Sistemlerin pek çoğu için bu özelliğin kullanılmasında büyük fayda vardır ve varsayılan olarak kapalıdır.

sp_configure 'optim', 1
RECONFIGURE

Not: Sanılanın aksine sp_configure ile kullandığınız parametreleri birebir yazmak zorunda değilsiniz, bu şekilde diğer seçenekler arasında eşsiz olacak şekilde kısalttığınızda da kullanabilirsiniz.

Procedure Cache'inizdeki planları Cache tiplerine göre gruplayıp, kullanım oranlarıyla ve plan boyutlarıyla listelemeyi aşağıdaki sorgu ile yapabilirsiniz:


SELECT 
[Cache Type] = cp.objtype, 
[Total Plans] = COUNT_BIG (*), 
[Total MBs] = SUM(CAST (cp.size_in_bytes AS DECIMAL (18,2))) / 1024 / 1024, [Avg Use Count] = AVG(cp.usecounts), 
[Total MBs - USE Count 1] = SUM(CAST((CASE WHEN cp.usecounts = 1 THEN cp.size_in_bytes ELSE 0 END) AS DECIMAL(18,2))) / 1024 / 1024,
[Total Plans - USE Count 1] = SUM(CASE WHEN cp.usecounts = 1 THEN 1 ELSE 0 END), 
[Percent Wasted] = (SUM(CAST((CASE WHEN cp.usecounts = 1 THEN cp.size_in_bytes ELSE 0 END) AS DECIMAL(18,2))) / SUM (cp.size_in_bytes)) * 100
FROM sys.dm_exec_cached_plans AS cp
GROUP BY cp.objtype
ORDER BY [Total MBs - USE Count 1] DESC;

Bu sorguyu bir Production ortamında çalıştırdığınızda aşağıdaki gibi bir sonuç alırsınız:



Bu ekran görüntüsünde görebileceğiniz gibi, her ne kadar bu sistemde de "Optimize for Adhoc Workloads" özelliği kullanılsa da, en çok plan Adhoc. Bunun temel nedeni de Dinamik SQL kullanımıdır ve doğru Covered Index'lerin kullanılmıyor oluşudur. Birçok sistemde (EXEC ... ile çalıştırılan sorgular Dinamik SQL'dir) dinamik SQL kullanmak kaçınılmazdır ve yerine göre doğrudur da. Fakat olabildiği kadar çok Stored Procedure ve sp_executesql kullanılmalıdır. Çünkü bu sorgular parametre alırlar ve bu sayede doğru Execution Planlar oluşturulabilir. Bu Execution Planlar ise Adhoc sorgularda olduğu gibi boşluk farkından bile tekrar kullanılamayacak planlar değildir. Eğer sisteminizde çok fazla Adhoc plan varsa, o zaman bunun için bir şeyler yapmanızda fayda var. Örneğin dinamik sorgularınızı olabildiğince Stored Procedure ve sp_executesql komutlarıyla yeniden yazabilirsiniz ve mümkün olan sorgular için Covered Index'ler oluşturabilirsiniz. Bu şekilde Adhoc planları olabildiğince azaltmış olursunuz. Eğer her şeye rağmen Adhoc sorguları yeterince azaltamıyorsanız, o zaman ne yapabileceğinizi aşağıda anlatacağım, fakat önce Adhoc sorguların gereğinden fazla olduğunu nasıl hesaplayabileceğinizden bahsedeyim biraz.

Yukarıdaki ekran görüntüsünden görebileceğiniz gibi Adhoc Plan'lar sistemde toplam 1.7GB yer kaplıyor. Bu miktarın 1.2GB'lık bölümü ise sadece ve sadece 1 kere kullanılmış olan Adhoc Plan'lara ait. Yani Adhoc planların %70'i çöp plan. Bu miktar zaman zaman çok daha fazla olabiliyor. Örneğin toplamda bir SQL Server Instance'ı için Max Server Mem ayarını 64GB yaparsanız ve Procedure Cache'teki Adhoc Planların miktarı 1-2GB ise, bu çok olarak sayılabilir. Eğer Covered Index oluşturmak, dinamik sorguları Stored Procedure ve sp_executesql ile yeniden yazmak gibi yöntemleri kullanamıyorsanız veya her şeye rağmen böyle bir sonuçla karşılaşıyorsanız o zaman DBCC FREESYSTEMCACHE ('SQL Plans') komutuyla Adhoc sorguları Procedure Cache'ten temizleyebilirsiniz. Tabii ki bunu bir sefer yapmanız tüm sorununuzu çözmeyecektir. Bunu zaman zaman uygulamak gerek. Yine Kimberly L. Tripp'in bu konuda yazdığı bir makale var. Bu makalede, çöp plan miktarı ölçülerek gerektiğinde bu DBCC komutunun bir SQL Agent Job'ı yardımıyla düzenli bir şekilde nasıl çalıştırılacağı da anlatılıyor.

Bir sistemde DBCC FREESYSTEMCACHE('SQL Plans') çalıştırıldıktan sonra Procedure Cache'in ne kadar düştüğünü aşağıdaki ekran görüntüsünden görebilirsiniz. Bunu bir Production ortamından aldım:


Gördüğünüz gibi DBCC FREESYSTEMCACHE('SQL Plans') komutunu çalıştırmadan önce Plan Cache'imin boyutu 2.6GB boyutlarındaydı. Bu komutu çalıştırdıktan ve Adhoc Planlardan kurtulduktan sonra 380MB'lara düştü. Komutun çalışmasının ardından tekrar okun yukarı doğru tırmandığını görebilirsiniz. Bunun nedeni de bu komutun sadece anlık olarak işe yaradığının ve zaman zaman çalıştırılması gerektiğinin göstergesidir. Procedure Cache'inizde gereksiz, bir daha kullanılmayacak olan planların durmasını istemezsiniz. Önceden de dediğim gibi bu sorunun kalıcı çözümü, doğru Covered Index'lerin oluşturulması, olabildiğince çok Stored Procedure ve sp_executesql kullanılmasıdır; fakat kaçınılmaz durumlarda da DBCC FREESYSTEMCACHE('SQL Plans') kullanabiliriz.

Son olarak, DBCC FREESYSTEMCACHE'i lütfen DBCC FREEPROCCACHE ile karıştırmayın. DBCC FREESYSTEMCACHE('SQL Plans') komutuyla sadece ve sadece Adhoc planlarını temizlemiş olursunuz. Stored Procedure, Trigger vs. Planlarını silmiş olmazsınız. DBCC FREEPROCCACHE komutunu ise çok gerekmedikçe kesinlikle bir üretim ortamında çalıştırmanızı tavsiye etmem. Çünkü Execution Plan oluşturulması zaten çok CPU maliyetli bir iştir ve bunun tekrar tekrar yapılmasını istemezsiniz.

Ekrem Önsoy


3 Mart 2014 Pazartesi

Sorgu kriterlerinde değişken kullanmanın sonuçları


Zaman zaman, sorgulardaki WHERE kriterlerinde parametre kullanmayı Best Practice olarak uyguladığını söyleyen arkadaşlara rastlıyorum. İstatistikler konusunda bir çalışma yaparken, çok taslak ve küçük bir test ortamı yaratıp size bu konuyla alakalı birkaç örnek göstermek istedim. Eminim faydası olacaktır.

Öncelikle değişken kullandığımız sorguların önceden yorumlanamadığını, run-time esnasında okunduğunu belirtmeliyim. Bu durumda da SQL Server o parametrenin değerinin ne olduğunu bilemiyor. Böylece ilgili İstatistik bilgilerinden ve dolayısıyla da en doğru Index'ten en iyi şekilde faydalanamıyor ve eğer tabloda herhangi bir İstatistik varsa, o İstatistikteki bazı bilgilerden (aşağıda ayrıntısını anlatacağım) faydalanarak ortalama bir kayıt sayısına göre Index/Table Scan veya Index/Table Seek işlemi yapıyor. SQL Server'ın Query Optimizer'ı sorgu masraflarını Cost Based olarak hesapladığı için İstatistikler SQL Server açısından hayati bir öneme sahip.

Şunu vurgulamakta fayda var, burada size temel olarak göstermek istediğim şey, SQL Server'ın İstatistikleri kullanarak yaptığı tahminler ve gerçekler. SQL Server yaptığı tahminleri girilen WHERE kriterindeki veriye (parametre, değişken, Literal) göre İstatistikleri kullanarak (varsa?) yapar. Örneğin bir tabloda 100.000 adet kayıt varsa ve siz sorgunuzla 30.000 kayıt döndürecekseniz SQL Server sorgularda kullanılan kriterlere göre ilgili İstatistikleri de kullanarak Index/Table Scan operatörünü kullanarak yapacaktır bu işlemi. Sorgulanacak çok fazla kayıt olacağı için doğru olan, bu operatörle daha az masraflı olacağı için bu olurdu. Eğer bu işlemi Index Seek operatörüyle yapmaya kalkarsa çok daha masraflı olacaktır. İşte tam bu noktada SQL Server tabloda, sorgunuzdaki WHERE kriterlerine göre kaç kayıt olduğunu İstatistikler yardımıyla hesaplar. Bunu SQL Server Management Studio'daki Execution Plan'ındaki ilgili operatörün üstünde beklerken çıkan Hint'te Estimated Number of Rows olarak görürsünüz. Sorgunuzu çalıştırdıktan sonra ortaya çıkan sonucu da Actual Number of Rows olarak görürsünüz. Normal şartlar altında bir Execution Plan'ı incelerken bu iki alandaki değerlerin eşit olmasını beklersiniz. Eğer değillerse ve iki değer arasında çok fark varsa, ya İstatistikler güncel değildir ya da değişken kullanıldığı için SQL Server ... WHERE ...'de girilen değeri bilemediği için Estimated Number of Rows değerini İstatistikteki (hesaplaması için aşağıdaki resme bakabilirsiniz) bazı verileri kullanarak tahmin edecektir.

Resim1: Estimated Number of Rows hesaplaması

Birkaç ekran görüntüsüyle örnekleyerek anlatmak genelde konuların anlaşılması için çok faydalı oluyor diye düşünüyorum.

Örneğimde kullandığım stat_test tablosunda 2 tane alan var. Biri (i alanı) INT, diğeri (tarih alanı) DATETIME. "i" alanı Clustered Index, "tarih" alanı için de NIX_tarih adında Nonclustered bir Index var.

Aşağıdaki ekran görüntüsüne bakarsanız Literal kullanarak yazdığım sorguyu görebilirsiniz:

Resim2

select * from stat_test where tarih = '2014-02-20 00:00:00.000'

Yukarıdaki ekran görüntüsünde Actual Number of Rows ve Estimated Number of Rows değerlerini işaretledim, lütfen dikkatle bakın. Gördüğünüz gibi iki değer de birbirinin aynısı. Yani SQL Server bu sorguyu çalıştırırken tarih alanı için sorgunun kaç tane kayıt döndüreceğini zaten biliyordu. Bu nedenle de kayıtları getirirken en doğru operatörü kullanabiliyor. 

Aşağıdaki ekran görüntüsündeki Actual Number of Rows ve Estimated Number of Rows değerlerine bakarsanız farklı değerler olduğunu görürsünüz. Bu sorguda değişken kullanıldığı için SQL Server İstatistikleri kontrol etmeden önce tarih alanı için hangi değerin kullanıldığını bilmiyor. Bu nedenle de Resim1'deki ekran görüntüsünde açıkladığım hesaplamayla yapabileceği en iyi şekilde tarih alanı için tabloda kaç tane kayıt olduğunu tahmin etmeye çalıştı ve buna göre de bir operatör seçti. Bizim örneğimizde kayıt sayısı çok fazla olmadığı için sorgunun bu haliyle de Index Seek operatörünün seçildiğini görüyorsunuz. Fakat farklı kayıt sayılarında farklı ve muhtemelen yanlış operatörler seçilecek ve IO, RAM, CPU gibi kaynakların çok kötü bir şekilde kullanıldığı gözlemlenecektir.

Resim3

Sonuç olarak, eğer tabloda aslında ilgili alan için (tarih diyelim) 100.000 adet kayıt varken, kullanılan değişken nedeniyle İstatistikten tam verimli bir biçimde yararlanılamaması nedeniyle bu kayıt sayısı 1.000 olarak tahmin edilebilir ve bunun neticesinde Index Scan yerine Index Seek operatörünün kullanılmasına karar verebilirdi SQL Server ve bu durumda da işlemi gerçekleştirmek için yanlış operatörler kullanılacak ve birçok durumda olduğu gibi sistem çalışamaz hale gelecekti.

Bu konu aslında çok derin bir konu ve normalde en az 3-4 saat aralıksız ve derinlemesine çalışılması gereken bir konu, ama size fikir olsun diye çok özetle ve temel düzeyde anlatmaya çalıştım. 

Kolay gelsin,
Ekrem Önsoy