{"id":1033,"date":"2016-01-10T15:25:37","date_gmt":"2016-01-10T23:25:37","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1033"},"modified":"2017-04-13T12:28:45","modified_gmt":"2017-04-13T19:28:45","slug":"sql-server-diagnostic-information-queries-detailed-day-10","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-10\/","title":{"rendered":"SQL Server Diagnostic Information Queries Detailed, Day 10"},"content":{"rendered":"

For Day 10 of this series, we start out with Query #21<\/strong>, which is Memory Dump Info.  This query retrieves information about any SQL Server memory dumps that have occurred from the sys.dm_server_memory_dumps<\/a> dynamic management view. Query #21 is shown in Figure 1. <\/p>\n

\n
\n
   1:<\/span> -- Get information on location, time and size of any memory dumps from SQL Server  (Query 21) (Memory Dump Info)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> [filename], creation_time, size_in_bytes\/1048576.0 AS<\/span> [Size<\/span> (MB)]<\/pre>\n
<\/p>\n
   3:<\/span> FROM<\/span> sys.dm_server_memory_dumps WITH<\/span> (NOLOCK) <\/pre>\n
<\/p>\n
   4:<\/span> ORDER<\/span> BY<\/span> creation_time DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
   5:<\/span>  <\/pre>\n
<\/p>\n
   6:<\/span> -- This will not return any rows if you have <\/span><\/pre>\n
<\/p>\n
   7:<\/span> -- not<\/span> had any<\/span> memory dumps (which is<\/span> a good thing)<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #21 Memory Dump Info<\/strong><\/p>\n
What you want to see when you run this query is no results, which means you have not had any SQL Server memory dumps. Memory dumps occur when SQL Server runs into problems or severe errors that are not quite bad enough to cause the entire SQL Server process to crash. If you have any of these, you can sometimes open the accompanying text file and get a rough idea what might have happened. Ultimately, you will need someone who knows what they are doing to look at the dump file with a debugger and the public symbols for your version and build of SQL Server (which might be Microsoft Support). Being on the latest Service Pack and Cumulative Update for your version of SQL Server will tend to reduce the number of memory dumps that you see.<\/p>\n
 <\/p>\n
Query #22<\/strong> is Database Filenames and Paths. This query retrieves information from the  sys.master_files<\/a> catalog view about the filenames and paths for all of the database files for all of the user and system databases on your instance. Query #22 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> -- File names and paths for all user and system databases on instance  (Query 22) (Database Filenames and Paths)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> DB_NAME([database_id]) AS<\/span> [Database<\/span> Name], <\/pre>\n
<\/p>\n
   3:<\/span>        [file_id], name, physical_name, [type_desc], state_desc,<\/pre>\n
<\/p>\n
   4:<\/span>        is_percent_growth, growth,<\/pre>\n
<\/p>\n
   5:<\/span>        CONVERT<\/span>(bigint, growth\/128.0) AS<\/span> [Growth in<\/span> MB], <\/pre>\n
<\/p>\n
   6:<\/span>        CONVERT<\/span>(bigint, size<\/span>\/128.0) AS<\/span> [Total Size<\/span> in<\/span> MB]<\/pre>\n
<\/p>\n
   7:<\/span> FROM<\/span> sys.master_files WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   8:<\/span> ORDER<\/span> BY<\/span> DB_NAME([database_id]) OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
   9:<\/span>  <\/pre>\n
<\/p>\n
  10:<\/span> -- Things to look at:<\/span><\/pre>\n
<\/p>\n
  11:<\/span> -- Are data files and log files on different drives?<\/span><\/pre>\n
<\/p>\n
  12:<\/span> -- Is everything on the C: drive?<\/span><\/pre>\n
<\/p>\n
  13:<\/span> -- Is TempDB on dedicated drives?<\/span><\/pre>\n
<\/p>\n
  14:<\/span> -- Is there only one TempDB data file?<\/span><\/pre>\n
<\/p>\n
  15:<\/span> -- Are all of the TempDB data files the same size?<\/span><\/pre>\n
<\/p>\n
  16:<\/span> -- Are there multiple data files for user databases?<\/span><\/pre>\n
<\/p>\n
  17:<\/span> -- Is<\/span> percent<\/span> growth enabled for<\/span> any<\/span> files (which is<\/span> bad)?<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #22 Database Filenames and Paths<\/strong><\/p>\n
This query lets you pretty easily see how your various database files are laid out across your file system, which is very useful to understand as you are investigating your storage subsystem performance. For example, it is fairly common to see high write latency for your tempdb database data files. Knowing that these files are located on your T: drive (for example), will help you understand the results from some of the upcoming queries more easily.<\/p>\n
Depending of what kind of storage you have and how it is configured, you might want to separate your database data files from your log files (on separate LUNs). This old rule of thumb is not always the best thing to do when you have a small number of magnetic spindles. This query also lets you see how tempdb is configured, and whether any of your database files are using percent growth (which is a bad thing for performance).<\/p>\n","protected":false},"excerpt":{"rendered":"
For Day 10 of this series, we start out with Query #21, which is Memory Dump Info.  This query retrieves information about any SQL Server memory dumps that have occurred from the sys.dm_server_memory_dumps dynamic management view. Query #21 is shown in Figure 1. 1: — Get information on location, time and size of any memory […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[273],"tags":[270],"class_list":["post-1033","post","type-post","status-publish","format-standard","hentry","category-dmv-diagnostic-queries-detailed","tag-sql-server-diagnostic-information-queries-detailed"],"yoast_head":"\nSQL Server Diagnostic Information Queries Detailed, Day 10 - Glenn Berry<\/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:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-10\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Diagnostic Information Queries Detailed, Day 10 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"For Day 10 of this series, we start out with Query #21, which is Memory Dump Info.  This query retrieves information about any SQL Server memory dumps that have occurred from the sys.dm_server_memory_dumps dynamic management view. Query #21 is shown in Figure 1. 1: -- Get information on location, time and size of any memory […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-10\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-10T23:25:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:28:45+00:00\" \/>\n<meta name=\"author\" content=\"Glenn Berry\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Glenn Berry\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-10\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-10\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 10 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-10T23:25:37+00:00\",\"dateModified\":\"2017-04-13T19:28:45+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-10\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-10\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-10\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 10\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\",\"name\":\"Glenn Berry\",\"description\":\"Semi-random musings about SQL Server performance\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\",\"name\":\"Glenn Berry\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"caption\":\"Glenn Berry\"},\"sameAs\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Diagnostic Information Queries Detailed, Day 10 - Glenn Berry","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:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-10\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 10 - Glenn Berry","og_description":"For Day 10 of this series, we start out with Query #21, which is Memory Dump Info.  This query retrieves information about any SQL Server memory dumps that have occurred from the sys.dm_server_memory_dumps dynamic management view. Query #21 is shown in Figure 1. 1: -- Get information on location, time and size of any memory […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-10\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-10T23:25:37+00:00","article_modified_time":"2017-04-13T19:28:45+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-10\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-10\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 10 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-10T23:25:37+00:00","dateModified":"2017-04-13T19:28:45+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-10\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-10\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-10\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/glenn\/"},{"@type":"ListItem","position":2,"name":"SQL Server Diagnostic Information Queries Detailed, Day 10"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/","name":"Glenn Berry","description":"Semi-random musings about SQL Server performance","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7","name":"Glenn Berry","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","caption":"Glenn Berry"},"sameAs":["https:\/\/www.sqlskills.com\/blogs\/glenn\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1033","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/comments?post=1033"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1033\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1033"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1033"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1033"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}