{"id":188,"date":"2015-08-24T16:38:30","date_gmt":"2015-08-24T14:38:30","guid":{"rendered":"http:\/\/blogs.uct.ac.za\/cp\/?p=188"},"modified":"2015-08-24T16:38:30","modified_gmt":"2015-08-24T14:38:30","slug":"performance-tuning-sql-server-begin","status":"publish","type":"post","link":"https:\/\/blogs.uct.ac.za\/cp\/2015\/08\/performance-tuning-sql-server-begin\/","title":{"rendered":"Performance Tuning SQL Server – where to begin"},"content":{"rendered":"

When it comes to Performance Tuning, I have never come across DBA’s that have the same methods or step by step guide to performance tuning.\u00a0\u00a0The question I get asked all the time , where to begin.<\/p>\n

There is no right or wrong answer.\u00a0If you are a \u00a0consultant and you get called in to a new environment, this is a Level 1 call, you don’t have the time to follow the full steps , you need a quick diagnostic short term solution,\u00a0 Most DBA’s would start with steps 4 -5<\/p>\n

For Real Time solutions , most DBA’s would look at what is running now , and then try to resolve.\u00a0 One of the best ways of that is looking at your Waits in SQL Server<\/strong>. That would give you a clear indication of where the bottleneck is.<\/p>\n

Most common top waits in SQL is related to Disk Subsystem. Before you start running perfmon and pulling out stats on your Disks presented to SQL, you need to ensure that your Data, Log, and Temp DB are\u00a0 on separate Disks. This can impact\u00a0SQL performance.<\/p>\n

SQL will tell you in Activity Manager, which data files have high IO Latencies.\u00a0 This can be related to index maintenance , missing indexes or poorly written queries.<\/p>\n

Recently we had a performance\u00a0issue on one on our core\u00a0 Applications. It is highly used, average 384 batch requests per second. Wait stats showed High IO Latencies and Latching.
\nPerfmon showed the Latencies are fine for the different drives.\u00a0 After further investigation, we found our normal maintenance plan<\/strong> which covered index maintenance<\/strong> was not sufficient for the increased high work load.<\/p>\n

On another occasion ,\u00a0 we did actually have a problem on the Disk Subsystems and the SAN vendors were called in to resolve.<\/p>\n

If you no longer in crisis mode and want to get the best performance out of your SQL Server:<\/p>\n

Operating System<\/strong>:
\nMust be patched with Service Packs. Ensure no errors.<\/p>\n

SQL Infrastructure:<\/strong>
\nData and Logs files are placed on separate disks with the correct raid levels.<\/p>\n

Instance Configuration:<\/strong>
\nThe defaults is generally fine. You may want to look at min & max memory settings. SQL will consume all the memory not leaving any for the operating system.
\nMaxdop is another option to look at. You can configure this to suit your environment, OLTP vs OLAP.
\nDefault Index fill factor – default is\u00a00. you can adjust\u00a0to your environment.<\/p>\n

Maintenance Plan:<\/strong>
\nLook at your index maintenance , update of stats , Integrity checks and backups<\/p>\n

Blocking & Waits:<\/strong>
\nThis will give you an indication of bottlenecks. A great tool to use.
\nSee article by Paul Randall – Tell me where it hurts<\/a><\/em><\/strong><\/p>\n

Queries:<\/strong>
\nLook for long running queries , poor performing queries. Queries with missing indexes.<\/p>\n

Databases:<\/strong>
\nLook at individual\u00a0databases, tables, indexes,\u00a0stored procedures and finally individual T-SQL queries.<\/p>\n

Another Top SQL Server authority to follow is Pinal Dave – see his article on Performance Tuning<\/a><\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"

When it comes to Performance Tuning, I have never come across DBA’s that have the same methods or step by step guide to performance tuning.\u00a0\u00a0The question I get asked all […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"ngg_post_thumbnail":0,"footnotes":""},"categories":[4],"tags":[18,17],"yoast_head":"\nPerformance Tuning SQL Server - where to begin - Computing Platforms @ UCT<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blogs.uct.ac.za\/cp\/2015\/08\/performance-tuning-sql-server-begin\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Performance Tuning SQL Server - where to begin - Computing Platforms @ UCT\" \/>\n<meta property=\"og:description\" content=\"When it comes to Performance Tuning, I have never come across DBA’s that have the same methods or step by step guide to performance tuning.\u00a0\u00a0The question I get asked all […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blogs.uct.ac.za\/cp\/2015\/08\/performance-tuning-sql-server-begin\/\" \/>\n<meta property=\"og:site_name\" content=\"Computing Platforms @ UCT\" \/>\n<meta property=\"article:published_time\" content=\"2015-08-24T14:38:30+00:00\" \/>\n<meta name=\"author\" content=\"Bantubenkosi Nicholus\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bantubenkosi Nicholus\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blogs.uct.ac.za\/cp\/2015\/08\/performance-tuning-sql-server-begin\/\",\"url\":\"https:\/\/blogs.uct.ac.za\/cp\/2015\/08\/performance-tuning-sql-server-begin\/\",\"name\":\"Performance Tuning SQL Server - where to begin - Computing Platforms @ UCT\",\"isPartOf\":{\"@id\":\"https:\/\/blogs.uct.ac.za\/cp\/#website\"},\"datePublished\":\"2015-08-24T14:38:30+00:00\",\"dateModified\":\"2015-08-24T14:38:30+00:00\",\"author\":{\"@id\":\"https:\/\/blogs.uct.ac.za\/cp\/#\/schema\/person\/94c25fb00d36b067378984d924329de6\"},\"breadcrumb\":{\"@id\":\"https:\/\/blogs.uct.ac.za\/cp\/2015\/08\/performance-tuning-sql-server-begin\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blogs.uct.ac.za\/cp\/2015\/08\/performance-tuning-sql-server-begin\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blogs.uct.ac.za\/cp\/2015\/08\/performance-tuning-sql-server-begin\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/blogs.uct.ac.za\/cp\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Performance Tuning SQL Server – where to begin\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blogs.uct.ac.za\/cp\/#website\",\"url\":\"https:\/\/blogs.uct.ac.za\/cp\/\",\"name\":\"Computing Platforms @ UCT\",\"description\":\"Sharing what we do & how...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blogs.uct.ac.za\/cp\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-GB\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/blogs.uct.ac.za\/cp\/#\/schema\/person\/94c25fb00d36b067378984d924329de6\",\"name\":\"Bantubenkosi Nicholus\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\/\/blogs.uct.ac.za\/cp\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c595125e262d35e84ef72e5dda5af0d9?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c595125e262d35e84ef72e5dda5af0d9?s=96&d=mm&r=g\",\"caption\":\"Bantubenkosi Nicholus\"},\"description\":\"Steve Ndabeni\",\"url\":\"https:\/\/blogs.uct.ac.za\/cp\/author\/01438720\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Performance Tuning SQL Server - where to begin - Computing Platforms @ UCT","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blogs.uct.ac.za\/cp\/2015\/08\/performance-tuning-sql-server-begin\/","og_locale":"en_GB","og_type":"article","og_title":"Performance Tuning SQL Server - where to begin - Computing Platforms @ UCT","og_description":"When it comes to Performance Tuning, I have never come across DBA’s that have the same methods or step by step guide to performance tuning.\u00a0\u00a0The question I get asked all […]","og_url":"https:\/\/blogs.uct.ac.za\/cp\/2015\/08\/performance-tuning-sql-server-begin\/","og_site_name":"Computing Platforms @ UCT","article_published_time":"2015-08-24T14:38:30+00:00","author":"Bantubenkosi Nicholus","twitter_misc":{"Written by":"Bantubenkosi Nicholus","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/blogs.uct.ac.za\/cp\/2015\/08\/performance-tuning-sql-server-begin\/","url":"https:\/\/blogs.uct.ac.za\/cp\/2015\/08\/performance-tuning-sql-server-begin\/","name":"Performance Tuning SQL Server - where to begin - Computing Platforms @ UCT","isPartOf":{"@id":"https:\/\/blogs.uct.ac.za\/cp\/#website"},"datePublished":"2015-08-24T14:38:30+00:00","dateModified":"2015-08-24T14:38:30+00:00","author":{"@id":"https:\/\/blogs.uct.ac.za\/cp\/#\/schema\/person\/94c25fb00d36b067378984d924329de6"},"breadcrumb":{"@id":"https:\/\/blogs.uct.ac.za\/cp\/2015\/08\/performance-tuning-sql-server-begin\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blogs.uct.ac.za\/cp\/2015\/08\/performance-tuning-sql-server-begin\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blogs.uct.ac.za\/cp\/2015\/08\/performance-tuning-sql-server-begin\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/blogs.uct.ac.za\/cp\/"},{"@type":"ListItem","position":2,"name":"Performance Tuning SQL Server – where to begin"}]},{"@type":"WebSite","@id":"https:\/\/blogs.uct.ac.za\/cp\/#website","url":"https:\/\/blogs.uct.ac.za\/cp\/","name":"Computing Platforms @ UCT","description":"Sharing what we do & how...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blogs.uct.ac.za\/cp\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-GB"},{"@type":"Person","@id":"https:\/\/blogs.uct.ac.za\/cp\/#\/schema\/person\/94c25fb00d36b067378984d924329de6","name":"Bantubenkosi Nicholus","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/blogs.uct.ac.za\/cp\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c595125e262d35e84ef72e5dda5af0d9?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c595125e262d35e84ef72e5dda5af0d9?s=96&d=mm&r=g","caption":"Bantubenkosi Nicholus"},"description":"Steve Ndabeni","url":"https:\/\/blogs.uct.ac.za\/cp\/author\/01438720\/"}]}},"_links":{"self":[{"href":"https:\/\/blogs.uct.ac.za\/cp\/wp-json\/wp\/v2\/posts\/188"}],"collection":[{"href":"https:\/\/blogs.uct.ac.za\/cp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.uct.ac.za\/cp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.uct.ac.za\/cp\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.uct.ac.za\/cp\/wp-json\/wp\/v2\/comments?post=188"}],"version-history":[{"count":4,"href":"https:\/\/blogs.uct.ac.za\/cp\/wp-json\/wp\/v2\/posts\/188\/revisions"}],"predecessor-version":[{"id":192,"href":"https:\/\/blogs.uct.ac.za\/cp\/wp-json\/wp\/v2\/posts\/188\/revisions\/192"}],"wp:attachment":[{"href":"https:\/\/blogs.uct.ac.za\/cp\/wp-json\/wp\/v2\/media?parent=188"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.uct.ac.za\/cp\/wp-json\/wp\/v2\/categories?post=188"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.uct.ac.za\/cp\/wp-json\/wp\/v2\/tags?post=188"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}