{"id":12254,"date":"2019-05-14T22:23:30","date_gmt":"2019-05-14T22:23:30","guid":{"rendered":"https:\/\/dbtut.com\/?p=12254"},"modified":"2019-05-15T21:42:36","modified_gmt":"2019-05-15T21:42:36","slug":"database-engine-tuning-advisordta-in-sql-server","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/","title":{"rendered":"Database Engine Tuning Advisor(DTA) in SQL Server"},"content":{"rendered":"<p>Database Engine Tuning Advisor (DTA) examines how queries are running in the database and provides us with suggestions. It has its own interface. You can open it via SSMS as follows.<\/p>\n<p id=\"sHDeKXV\"><img loading=\"lazy\" decoding=\"async\" width=\"780\" height=\"157\" class=\"size-full wp-image-12255  aligncenter\" src=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/img_5cdb254a5445b.png\" alt=\"\" \/><\/p>\n<p>You should select the instance you will analyze on the connection screen that appears.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-12271 aligncenter\" src=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-49.png\" alt=\"\" width=\"589\" height=\"351\" \/><\/p>\n<p>You can open DTA via Windows Search as follows.<\/p>\n<p id=\"yCoGVNI\"><img loading=\"lazy\" decoding=\"async\" width=\"457\" height=\"301\" class=\"size-full wp-image-12256  aligncenter\" src=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/img_5cdb25d4409a5.png\" alt=\"\" \/><\/p>\n<p>If the Database Engine Tuning Advisor is being opened for the first time, only a sysadmin can open it. For later use, db_owners can open DTA to monitor their database. This is because some system tables are created in the msdb database during initial startup. If you try to open DTA while SQL Server is in single user mode, you will receive an error.<\/p>\n<p>You can analyze the following with DTA;<\/p>\n<ul>\n<li>SQL Statements<\/li>\n<li>Trace Tables<\/li>\n<li>Top 1000 queries in the plan cache (to analyze the database using dmws without any trace)<\/li>\n<\/ul>\n<p>In this article, we will analyze a trace table where long running queries are recorded.<\/p>\n<p>To analyze long running queries with profiler or extended events and save it as a table, I recommend that you read the articles below.<\/p>\n<p>&#8220;<a href=\"https:\/\/dbtut.com\/index.php\/2019\/02\/12\/how-to-identify-long-running-queries-using-sql-server-profiler\/\" target=\"_blank\" rel=\"noopener noreferrer\">How To Identify Long Running Queries Using SQL Server Profiler<\/a>&#8220;,<br \/>\n&#8220;<a href=\"https:\/\/dbtut.com\/index.php\/2019\/02\/13\/how-to-identify-long-running-queries-using-extended-events\/\" target=\"_blank\" rel=\"noopener noreferrer\">How To Identify Long Running Queries Using Extended Events<\/a>&#8221;<\/p>\n<p>After connecting DTA and selecting the corresponding instance from the connection screen, we select Table as follows.<\/p>\n<p>Then click on the box with the binoculars icon on the right and select the related trace table that we have previously saved.<\/p>\n<p id=\"rnhxajJ\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-12257  aligncenter\" src=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/img_5cdb28ba3aefa.png\" alt=\"\" width=\"825\" height=\"292\" \/><\/p>\n<p>We select the related database from the &#8220;Database for workload analysis and Select databases and tables to tune&#8221; sections.<\/p>\n<p>In the Tuning Options section, we select the options we need and click Start Analysis.<\/p>\n<p>Below you can find explanation of the options in the Tuning Options section.<\/p>\n<h3>Limit tuning time<\/h3>\n<p>Limits the time Tune operation runs. Remove this limit if you want to get a healthy tune recommendation. If you still want to set a limit, you can specify the stop time of the DTA from &#8220;Stop at&#8221;.<\/p>\n<h3>Advanced Options<\/h3>\n<p>If you enter a value in MB after you select &#8220;Define max. Space for recommendations&#8221; below, you specify the maximum size of the suggestions. Do not place this limit if you want to get a healthy tune recommendation.<\/p>\n<h3>In the Online Index recommendations section;<\/h3>\n<p>If you select All recommendations are offline, it gives all index recommendations offline. But this option cause interruption in your database.<\/p>\n<p>If you select &#8220;Generate online recommendations where possible&#8221;, it will give you index suggestions online if possible. Index recommendations cannot be performed online in every case. Thats why you can select this option for less interruption. But this option may cause interruption in your database.<\/p>\n<p>If you do not want any interruptions in the database, you can select option 3. In this case, the script may receive an error. But the safest way is option three.<\/p>\n<p>As a result, you can try option 3 at first. If you encounter with a problem, you can try option 2. But you must be ready for interruption before you start analysis.<\/p>\n<p id=\"QkONynd\"><img loading=\"lazy\" decoding=\"async\" width=\"670\" height=\"395\" class=\"size-full wp-image-12260  aligncenter\" src=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/img_5cdb2f604101a.png\" alt=\"\" \/><\/p>\n<h3>Indexes and indexed views<\/h3>\n<p>You can select this option for suggestions to adding Clustered Index, Nonclustered Index, and Indexed View.<\/p>\n<h3>Indexed views<\/h3>\n<p>You can select this option only for suggestions to adding Indexed View.<\/p>\n<h3>Include filtered indexes<\/h3>\n<p>You can select this option for suggestions to adding Filtered Index. To select this option, you must choose on of the below options:<\/p>\n<ul>\n<li>Indexes and indexed views<\/li>\n<li>Indexes<\/li>\n<li>Nonclustered indexes<\/li>\n<\/ul>\n<h3>Indexes<\/h3>\n<p>You can select this option for suggestions to adding Clustered Index and Nonclustered Index.<\/p>\n<h3>Nonclustered indexes<\/h3>\n<p>You can select this option only for suggestions to adding Nonclustered Index.<\/p>\n<h3>Evaluate utilization of existing PDS only<\/h3>\n<p>You can select this option to evaluate whether existing indexes are useful. If you select this option, it does not recommend new index or indexed view.<\/p>\n<h3>No partitioning<\/h3>\n<p>You can choose this option if you do not want Partitioning suggestions. You can find details about the Partition concept in the article &#8220;<a href=\"https:\/\/dbtut.com\/index.php\/2018\/08\/10\/how-to-create-partition-on-sql-server\/\" target=\"_blank\" rel=\"noopener noreferrer\">How To Create Partition On SQL Server<\/a>&#8220;.<\/p>\n<h3>Full partitioning<\/h3>\n<p>You can select this option if you want Partitioning suggestions. You can find details about the Partition concept in the article &#8220;<a href=\"https:\/\/dbtut.com\/index.php\/2018\/08\/10\/how-to-create-partition-on-sql-server\/\" target=\"_blank\" rel=\"noopener noreferrer\">How To Create Partition On SQL Server<\/a>&#8220;.<\/p>\n<h3>Aligned partitioning<\/h3>\n<p>You should select this option if you want indexes to be aligned according to the partition when Partition is recommended. For information about the details of the Align process, please read to the article named &#8220;<a href=\"https:\/\/dbtut.com\/index.php\/2018\/08\/17\/can-not-switch-the-partition-on-sql-server\/\" target=\"_blank\" rel=\"noopener noreferrer\">Can Not Switch The Partition On SQL Server<\/a>&#8220;.<\/p>\n<h3>Do not keep any existing PDS<\/h3>\n<p>You should select this option for DTA&#8217;s delete recommendation if existing indexes, indexed views and partitions are unnecessary. It will not recommend delete if it is not unnecessary.<\/p>\n<h3>Keep indexes only<\/h3>\n<p>Unlike an item above, you should select this option if you want to keep indexes even if unnecessary. It will recommend deletion of indexed views and partitions.<\/p>\n<h3>Keep all existing PDS<\/h3>\n<p>If you do not want delete recommendation you should select this option.<\/p>\n<h3>Keep clustered indexes only<\/h3>\n<p>Unlike &#8220;keep indexes only&#8221;, you should select this option if you want to keep only clustered indexes, even if they are unnecessary.<\/p>\n<h3>Keep aligned partitioning<\/h3>\n<p>If you do not want delete recommedation of only aligned partitions, you should select this option. It will recommend other delete recommendations.<\/p>\n<p>When the analysis is finished, it will list the suggestions from the Recommendations section as below. It didn&#8217;t suggest anything in my scenario.<\/p>\n<p id=\"dQRncqM\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-12263  aligncenter\" src=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/img_5cdb3d290b260.png\" alt=\"\" width=\"689\" height=\"362\" \/><\/p>\n<p>With SQL Server 2017, DTA is now able to analyze Query Store data. You can find detailed information about the Query Store in the article &#8220;<a href=\"https:\/\/dbtut.com\/index.php\/2019\/05\/15\/what-is-query-store-in-sql-server\/\" target=\"_blank\" rel=\"noopener noreferrer\">What is Query Store in SQL Server<\/a>&#8220;.<\/p>\n\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_12254\" class=\"pvc_stats all  \" data-element-id=\"12254\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/dbtut.com\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Database Engine Tuning Advisor (DTA) examines how queries are running in the database and provides us with suggestions. It has its own interface. You can open it via SSMS as follows. You should select the instance you will analyze on the connection screen that appears. You can open DTA via Windows Search as follows. If &hellip;<\/p>\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_12254\" class=\"pvc_stats all  \" data-element-id=\"12254\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/dbtut.com\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"author":1,"featured_media":12266,"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,"footnotes":""},"categories":[3],"tags":[4339,4338,4331,4334,4312,4323,4324,4325,4311,4336,4330,4314,4313,4332,4335,4337,4321,4318,4326,4328,4327,4329,4340,4341,4333,4322,4319,4320,4317,4315,4316],"class_list":["post-12254","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-mssql","tag-advanced-tuning-options","tag-advanced-tuning-options-in-dta","tag-aligned-partitioning","tag-all-recommendations-are-offline","tag-database-engine-tuning-advisor","tag-database-engine-tuning-advisor-index-recommendations","tag-database-engine-tuning-advisor-stored-procedure","tag-database-engine-tuning-advisor-workload","tag-database-engine-tuning-advisordta","tag-define-max-space-for-recommendations","tag-do-not-keep-any-existing-pds","tag-dta","tag-dta-in-sql-server","tag-evaluate-utilization-of-existing-pds-only","tag-generate-online-recommendations-where-possible","tag-generate-only-online-recommendations","tag-how-can-check-database-performance-in-sql-server","tag-how-do-i-run-a-sql-trace-on-one-database","tag-keep-aligned-partitioning","tag-keep-all-existing-pds","tag-keep-clustered-indexes-only","tag-keep-indexes-only","tag-limit-tuning-time","tag-limit-tuning-time-in-dta","tag-online-index-recommendations","tag-sql-server-database-tuning","tag-what-is-database-tuning-advisor-in-sql-server","tag-what-is-database-tuning-in-sql-server","tag-what-is-dta-in-sql-server","tag-what-is-the-use-of-database-engine-tuning-advisor","tag-what-is-tuning-advisor-in-sql-server"],"aioseo_notices":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.9 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Database Engine Tuning Advisor(DTA) in SQL Server - Database Tutorials<\/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:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Database Engine Tuning Advisor(DTA) in SQL Server - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"Database Engine Tuning Advisor (DTA) examines how queries are running in the database and provides us with suggestions. It has its own interface. You can open it via SSMS as follows. You should select the instance you will analyze on the connection screen that appears. You can open DTA via Windows Search as follows. If &hellip;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-05-14T22:23:30+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-05-15T21:42:36+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-47.png\" \/>\n\t<meta property=\"og:image:width\" content=\"607\" \/>\n\t<meta property=\"og:image:height\" content=\"354\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"Database Engine Tuning Advisor(DTA) in SQL Server\",\"datePublished\":\"2019-05-14T22:23:30+00:00\",\"dateModified\":\"2019-05-15T21:42:36+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/\"},\"wordCount\":934,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-47.png\",\"keywords\":[\"advanced tuning options\",\"advanced tuning options in DTA\",\"Aligned partitioning\",\"All recommendations are offline\",\"Database Engine Tuning Advisor\",\"database engine tuning advisor index recommendations\",\"database engine tuning advisor stored procedure\",\"database engine tuning advisor workload\",\"Database Engine Tuning Advisor(DTA)\",\"Define max. Space for recommendations\",\"Do not keep any existing PDS\",\"DTA\",\"DTA in SQL Server\",\"Evaluate utilization of existing PDS only\",\"Generate online recommendations where possible\",\"Generate only online recommendations\",\"How can check database performance in SQL Server?\",\"How do I run a SQL trace on one database?\",\"Keep aligned partitioning\",\"Keep all existing PDS\",\"Keep clustered indexes only\",\"Keep indexes only\",\"Limit tuning time\",\"Limit tuning time in DTA\",\"Online Index recommendations\",\"sql server database tuning\",\"What is Database Tuning Advisor in SQL Server?\",\"What is database tuning in SQL Server?\",\"What is DTA in SQL Server?\",\"What is the use of Database Engine Tuning Advisor?\",\"What is Tuning Advisor in SQL Server?\"],\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/\",\"name\":\"Database Engine Tuning Advisor(DTA) in SQL Server - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-47.png\",\"datePublished\":\"2019-05-14T22:23:30+00:00\",\"dateModified\":\"2019-05-15T21:42:36+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-47.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-47.png\",\"width\":607,\"height\":354},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Database Engine Tuning Advisor(DTA) in SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Database Engine Tuning Advisor(DTA) in SQL Server - Database Tutorials","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:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"Database Engine Tuning Advisor(DTA) in SQL Server - Database Tutorials","og_description":"Database Engine Tuning Advisor (DTA) examines how queries are running in the database and provides us with suggestions. It has its own interface. You can open it via SSMS as follows. You should select the instance you will analyze on the connection screen that appears. You can open DTA via Windows Search as follows. If &hellip;","og_url":"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/","og_site_name":"Database Tutorials","article_published_time":"2019-05-14T22:23:30+00:00","article_modified_time":"2019-05-15T21:42:36+00:00","og_image":[{"width":607,"height":354,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-47.png","type":"image\/png"}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"Database Engine Tuning Advisor(DTA) in SQL Server","datePublished":"2019-05-14T22:23:30+00:00","dateModified":"2019-05-15T21:42:36+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/"},"wordCount":934,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-47.png","keywords":["advanced tuning options","advanced tuning options in DTA","Aligned partitioning","All recommendations are offline","Database Engine Tuning Advisor","database engine tuning advisor index recommendations","database engine tuning advisor stored procedure","database engine tuning advisor workload","Database Engine Tuning Advisor(DTA)","Define max. Space for recommendations","Do not keep any existing PDS","DTA","DTA in SQL Server","Evaluate utilization of existing PDS only","Generate online recommendations where possible","Generate only online recommendations","How can check database performance in SQL Server?","How do I run a SQL trace on one database?","Keep aligned partitioning","Keep all existing PDS","Keep clustered indexes only","Keep indexes only","Limit tuning time","Limit tuning time in DTA","Online Index recommendations","sql server database tuning","What is Database Tuning Advisor in SQL Server?","What is database tuning in SQL Server?","What is DTA in SQL Server?","What is the use of Database Engine Tuning Advisor?","What is Tuning Advisor in SQL Server?"],"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/","url":"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/","name":"Database Engine Tuning Advisor(DTA) in SQL Server - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-47.png","datePublished":"2019-05-14T22:23:30+00:00","dateModified":"2019-05-15T21:42:36+00:00","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-47.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-47.png","width":607,"height":354},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/14\/database-engine-tuning-advisordta-in-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"Database Engine Tuning Advisor(DTA) in SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/12254","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=12254"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/12254\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/12266"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=12254"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=12254"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=12254"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}