{"id":5170,"date":"2018-10-21T19:09:12","date_gmt":"2018-10-21T19:09:12","guid":{"rendered":"http:\/\/mehmetsalihdeveci.net\/?p=5170"},"modified":"2019-02-28T08:17:54","modified_gmt":"2019-02-28T08:17:54","slug":"sql-server-performance-troubleshooting-6","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-server-performance-troubleshooting-6\/","title":{"rendered":"SQL Server Performance Troubleshooting -6"},"content":{"rendered":"

Merhaba Arkada\u015flar,<\/p>\n

Bu yaz\u0131mda sizlere \u00f6nceki yaz\u0131mda ba\u015flad\u0131\u011f\u0131m SQL Server da Performance Troubleshooting kavram\u0131n\u0131 ve nas\u0131l yap\u0131ld\u0131\u011f\u0131n\u0131n devam\u0131n\u0131 anlat\u0131yor olaca\u011f\u0131m.<\/p>\n

\"\"<\/p>\n

<\/p>\n\n

Bu konunun devam\u0131 olan di\u011fer yaz\u0131lar\u0131m\u0131n linkleri a\u015fa\u011f\u0131daki gibidir.<\/p>\n

https:\/\/ittutorial.org\/2018\/10\/06\/sql-server-performance-troubleshooting-1\/<\/a><\/p>\n

https:\/\/ittutorial.org\/2018\/10\/07\/sql-server-performance-troubleshooting-2\/<\/a><\/p>\n

https:\/\/ittutorial.org\/2018\/10\/13\/sql-server-performance-troubleshooting-3\/<\/a><\/p>\n

https:\/\/ittutorial.org\/2018\/10\/14\/sql-server-performance-troubleshooting-4\/<\/a><\/p>\n

https:\/\/ittutorial.org\/2018\/10\/20\/sql-server-performance-troubleshooting-5\/<\/a><\/p>\n

https:\/\/ittutorial.org\/2018\/10\/21\/sql-server-performance-troubleshooting-6\/<\/a><\/p>\n

https:\/\/ittutorial.org\/2014\/03\/01\/sql-server-dmv-ve-dmf-kavramlari-ve-kullanimlari-1\/<\/a><\/p>\n

https:\/\/ittutorial.org\/2014\/03\/03\/sql-server-dmv-ve-dmf-kavramlari-ve-kullanimlari-2\/<\/a><\/p>\n

https:\/\/ittutorial.org\/2013\/03\/28\/sqldiag-araci-ile-veritabani-performans-verisi-toplama\/<\/a><\/p>\n

https:\/\/ittutorial.org\/2013\/03\/29\/rml-utilities-tool-u-ile-veritabani-performansini-raporlama\/<\/a><\/p>\n

 <\/p>\n

Veritaban\u0131nda fragmente olmu\u015f indexler ve bunlar\u0131n Rebuild edilmesi i\u00e7in gerekli Create scriptleri a\u015fa\u011f\u0131daki gibidir.<\/p>\n

select 'ALTER INDEX [' + i.name +'] on '+OBJECT_NAME(s.object_id)+' REBUILD WITH (ONLINE = ON);' as [Statement],\r\n\r\nobjname = OBJECT_NAME(s.object_id),\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 s.object_id,\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 index_name= i.name,\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 index_type_desc,\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 avg_fragmentation_in_percent,page_count\r\n\r\nfrom sys.dm_db_index_physical_stats(db_id(),null,null,null,null) as s\r\n\r\njoin sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id\u00a0\r\n\r\nwhere s.database_id = db_id() and s.page_count > 1000 and s.avg_fragmentation_in_percent>10 and i.name is not null\r\n\r\norder by avg_fragmentation_in_percent desc;<\/pre>\n
 <\/p>\n
\"\"<\/p>\n
 <\/p>\n
T\u00fcm indexlerin kullan\u0131c\u0131lar taraf\u0131ndan kullan\u0131lma durumlar\u0131 ve ne kadar g\u00fcncellendi\u011fini a\u015fa\u011f\u0131daki scriptle \u00f6\u011frenebiliriz.<\/p>\n
 <\/p>\n
select\u00a0 OBJECT_NAME(s.object_id), i.name, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates\r\n\r\nfrom sys.dm_db_index_usage_stats s\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 left outer join sys.indexes as i with (nolock) on s.object_id = i.object_id and s.index_id = i.index_id\r\n\r\nwhere OBJECTPROPERTY(s.object_id, 'IsMsShipped')=0 and name is not null order by user_seeks desc<\/pre>\n
 <\/p>\n
\"\"<\/p>\n
 <\/p>\n
Veritaban\u0131nda \u00e7ok az kullan\u0131lan indexler gereksiz yere kaynak t\u00fcketmeleri yerine iyice analiz edildikten sonra drop edilebilirler. Veritaban\u0131nda \u00e7ok az kullan\u0131lan indexler ve drop index scriptlerini veren sorgu a\u015fa\u011f\u0131daki gibidir.<\/p>\n
 <\/p>\n
SELECT\r\n\r\no.name\r\n\r\n, indexname=i.name\r\n\r\n, i.index_id\u00a0\u00a0\r\n\r\n, reads=user_seeks + user_scans + user_lookups\u00a0\u00a0\r\n\r\n, writes =\u00a0 user_updates\u00a0\u00a0\r\n\r\n, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)\r\n\r\n, CASE\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN s.user_updates < 1 THEN 100\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) \/ s.user_updates\r\n\r\n\u00a0 END AS reads_per_write\r\n\r\n, 'DROP INDEX ' + QUOTENAME(i.name)\r\n\r\n+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'\r\n\r\nFROM sys.dm_db_index_usage_stats s\u00a0\r\n\r\nINNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id\u00a0\u00a0\r\n\r\nINNER JOIN sys.objects o on s.object_id = o.object_id\r\n\r\nINNER JOIN sys.schemas c on o.schema_id = c.schema_id\r\n\r\nWHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1\r\n\r\nAND s.database_id = DB_ID()\u00a0\u00a0\r\n\r\nAND i.type_desc = 'nonclustered'\r\n\r\nAND i.is_primary_key = 0\r\n\r\nAND i.is_unique_constraint = 0\r\n\r\nAND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000\r\n\r\nORDER BY reads;<\/pre>\n
 <\/p>\n
\"\"<\/p>\n
 <\/p>\n
Blocking<\/strong><\/p>\n
SQL Server da bir session transaction ba\u015flat\u0131p herhangi bir tablonun bir veya daha fazla sat\u0131r\u0131 i\u00e7in update,delete gibi i\u015flemler yapacaksa ilgili tablonun bu sat\u0131rlar\u0131na lock koyar. Ba\u015fka bir session ayn\u0131 i\u015flemi ilgili sat\u0131rlara yapmaya \u00e7al\u0131\u015ft\u0131\u011f\u0131nda ise bloklan\u0131r. Lock \u0131 koyan transaction lock I kald\u0131rana kadar 2.session 1.session I bekler. Bu olay\u0131n \u00e7ok fazla ve farkl\u0131 sessionlar taraf\u0131ndan tekrarlanmas\u0131 veritaban\u0131nda probleme yol a\u00e7acakt\u0131r.<\/p>\n
Bloklaman\u0131n en b\u00fcy\u00fck sebeblerinden birisi uzun s\u00fcren sorgulard\u0131r.\u00a0 Bloklamalar veritaban\u0131nda i\u015flem yapamayacak kadar artm\u0131\u015fsa ilgili sessionlar\u0131n DBA ler taraf\u0131ndan kill edilmesi faydal\u0131 olacakt\u0131r. Yada default kurulumda pasif olarak gelen LOCK_TIMEOUT parametresi aktif edilebilir. Bu parametre aktif edildi\u011fi zaman belirlenen sure kadar bekleme yapan 2.session SQL Server taraf\u0131ndan \u00f6ld\u00fcr\u00fcl\u00fcr ve kullan\u0131c\u0131ya Error message 1222, “Lock request time-out period exceeded” hatas\u0131 d\u00f6nd\u00fcr\u00fcl\u00fcr. LOCK_TIMEOUT parametresi transaction i\u00e7inde a\u015fa\u011f\u0131daki gibi enable edilir.<\/p>\n
Set lock_timeout <Timeout_aral\u0131\u011f\u0131><\/p>\n
 <\/p>\n
Veritaban\u0131 \u00fczerinde bloklamaya neden olan Sessionlar ve bloklanan sessionlar\u0131n bilgilerini veren script a\u015fa\u011f\u0131daki gibidir.<\/p>\n
print ' a blocking report (by using DMVs)'\r\n\r\n-- sys.dm_tran_locks\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ---> request_session_id (spid\u00a0 waiting resource)\r\n\r\n-- sys.dm_os_waiting_tasks ---> blocking_session_id (spid\u00a0 blocking resource)\r\n\r\nselect t1.resource_type as [lock type] ,\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 db_name(resource_database_id) as [database],\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 t1.resource_associated_entity_id as [blk object],\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 t1.request_mode as [lock req] -- lock requested\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ,t1.request_session_id as [waiter sid] -- spid of waiter\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ,t2.wait_duration_ms as [wait time(ms)]\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ,(select text\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from sys.dm_exec_requests as r -- get sql for waiter\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cross apply sys.dm_exec_sql_text(r.sql_handle)\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where r.session_id = t1.request_session_id) as waiter_batch\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ,(select substring(qt.text,r.statement_start_offset\/2,\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (case when r.statement_end_offset = -1 then len(convert(nvarchar(max),qt.text)) * 2\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 else r.statement_end_offset end - r.statement_start_offset)\/2)\r\n\r\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0from sys.dm_exec_requests as r\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cross apply sys.dm_exec_sql_text(r.sql_handle) as qt\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where r.session_id = t1.request_session_id) as waiter_stmt -- statement executing now\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ,t2.blocking_session_id as [blocker sid] -- spid of blocker\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ,(select text from sys.sysprocesses as p -- get sql for blocker\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cross apply sys.dm_exec_sql_text(p.sql_handle)\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where p.spid = t2.blocking_session_id) as blocker_stmt\r\n\r\n\u00a0from sys.dm_tran_locks as t1,\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 sys.dm_os_waiting_tasks as t2\r\n\r\n\u00a0where t1.lock_owner_address = t2.resource_address\r\n\r\n\u00a0order by [wait time(ms)] desc;\r\n\r\n\r\n<\/pre>\n
Bloklamalar\u0131 ayr\u0131ca SQL Server Profiler danda se\u00e7ip izleyebiliriz.<\/p>\n
\"\"<\/p>\n
 <\/p>\n
TempDB nin ayarlanmas\u0131<\/strong><\/p>\n
SQL Server da Global yada Temporary table,index vb objeler Tempdb veritaban\u0131nda tutulur. Ayr\u0131ca sorgular sirasinda ihtiya\u00e7 duyulan order by, group by ve union gibi i\u015flemlerin ara sonu\u00e7lar\u0131da tempdb de tutulur. Bunun d\u0131\u015f\u0131nda DBCC CHECKDB komutunu \u00e7al\u0131\u015ft\u0131rd\u0131\u011f\u0131m\u0131z zaman olu\u015fan ge\u00e7ici de\u011ferlerde Tempdb de tutulur. K\u0131saca veritaban\u0131nda meydana gelen transactionlar s\u0131ras\u0131nda olu\u015fan ge\u00e7ici de\u011ferler Tempdb veritaban\u0131nda tutulur. Tempdb d\u00fczg\u00fcn bir performansta \u00e7al\u0131\u015fmas\u0131 Veritaban\u0131 performans\u0131 i\u00e7in \u00e7ok \u00e7ok \u00f6nemlidir. Tempdb nin performansl\u0131 \u00e7al\u0131\u015fabilmesi i\u00e7in a\u015fa\u011f\u0131daki ad\u0131mlar\u0131n yap\u0131lmas\u0131 faydal\u0131 olacakt\u0131r.<\/p>\n
    \n
  • Veritaban\u0131 verilerinin saklanmas\u0131 i\u00e7in ayr\u0131lan (mdf ve ldf dosyalar\u0131) disk (ler) ile Tempdb nin disk(ler) inin ayr\u0131 olmal\u0131d\u0131r.<\/li>\n
  • M\u00fcmk\u00fcn oldu\u011funca tempdb i\u00e7in verilecek disk(ler) in y\u00fcksek performansl\u0131 disklerden se\u00e7ilmesi gerekmektedir.<\/li>\n
  • Disk I\/O sunu d\u00fc\u015f\u00fcrmek i\u00e7in tempdb dosyalar\u0131n\u0131 e\u015fit b\u00fcy\u00fckl\u00fc\u011fe sahip dosyalara b\u00f6lmek gerekmektedir.<\/li>\n<\/ul>\n
     <\/p>\n
     <\/p>\n
    B\u00f6ylece bu yaz\u0131 dizisinin sonuna gelmi\u015f bulunmaktay\u0131m bir sonraki yaz\u0131da g\u00f6r\u00fc\u015fmek dile\u011fiyle esen kal\u0131n.<\/p>\n\n
    Oracle Exadata SQL Server Goldengate Weblogic EBS ve Linux konusunda a\u015fa\u011f\u0131daki konularda 7×24 Uzman Dan\u0131\u015fmanlara yada E\u011fitimlere mi\u00a0<\/span>\u0130htiyac\u0131n\u0131z var mehmet.deveci@gridgroup.com.tr adresine mail atarak Bizimle ileti\u015fime ge\u00e7ebilirsiniz.<\/span><\/p>\n
    – Oracle Veritaban\u0131 Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– Oracle Veritaban\u0131 Bak\u0131m ve Destek<\/span>
    \n– Exadata Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– Exadata Bak\u0131m ve Destek<\/span>
    \n– SQL Server Veritaban\u0131 Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– SQL Server Veritaban\u0131 Bak\u0131m ve Destek<\/span>
    \n– Goldengate Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– Goldengate Bak\u0131m ve Destek<\/span>
    \n– Linux Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– Linux Bak\u0131m ve Destek<\/span>
    \n– Oracle EBS Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– Oracle EBS Bak\u0131m ve Destek<\/span>
    \n– Weblogic Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– Weblogic Bak\u0131m ve Destek<\/span>
    \n– Oracle Veritaban\u0131 E\u011fitimleri<\/span>
    \n– Oracle VM Server Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– Oracle VM Server Bak\u0131m ve Destek<\/span>
    \n– Oracle EPPM Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– Oracle EPPM Bak\u0131m ve Destek<\/span>
    \n– Oracle Primavera Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
    \n– Oracle Primavera Bak\u0131m ve Destek<\/span>
    \n– Oracle E\u011fitimleri<\/span>
    \n– SQL Server E\u011fitimleri<\/span>
    \n– Goldengate E\u011fitimleri<\/span>
    \n– Exadata E\u011fitimleri<\/span>
    \n– Linux E\u011fitimleri<\/span>
    \n– Oracle EBS E\u011fitimleri<\/span>
    \n– Oracle VM Server E\u011fitimleri<\/span>
    \n– Weblogic E\u011fitimleri<\/span>
    \n– Oracle EPPM E\u011fitimleri<\/span>
    \n– Oracle Primavera E\u011fitimleri<\/span><\/p>\n
     <\/p>\n","protected":false},"excerpt":{"rendered":"
    Merhaba Arkada\u015flar, Bu yaz\u0131mda sizlere \u00f6nceki yaz\u0131mda ba\u015flad\u0131\u011f\u0131m SQL Server da Performance Troubleshooting kavram\u0131n\u0131 ve nas\u0131l yap\u0131ld\u0131\u011f\u0131n\u0131n devam\u0131n\u0131 anlat\u0131yor olaca\u011f\u0131m.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[19,23],"tags":[457,480,586,1028,1031,1120,1199,1266,1267,1623,1629,1644,1659,1661,1667,1859,1860,1861],"class_list":["post-5170","post","type-post","status-publish","format-standard","","category-sql-server","category-veritabani","tag-database-bakim-ve-destek","tag-dba-danismanlik","tag-exadata-egitim","tag-mehmet-deveci","tag-mehmet-salih-deveci","tag-oracle","tag-oracle-danismanlik","tag-oracle-egitim","tag-oracle-egitimi","tag-sql-server-bakim-destek","tag-sql-server-danismanlik","tag-sql-server-egitim","tag-sql-server-performance-troubleshooting","tag-sql-server-performans-tuning","tag-sql-server-sorun-cozme","tag-veritabani-bakim-ve-destek","tag-veritabani-danismanlik","tag-veritabani-egitim"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/5170","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=5170"}],"version-history":[{"count":1,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/5170\/revisions"}],"predecessor-version":[{"id":5786,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/5170\/revisions\/5786"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=5170"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=5170"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=5170"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}