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 dumps from SQL Server (Query 21) (Memory Dump Info)
2: SELECT [filename], creation_time, size_in_bytes/1048576.0 AS [Size (MB)]
3: FROM sys.dm_server_memory_dumps WITH (NOLOCK)
4: ORDER BY creation_time DESC OPTION (RECOMPILE);
5:
6: -- This will not return any rows if you have
7: -- not had any memory dumps (which is a good thing)
Figure 1: Query #21 Memory Dump Info
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.
Query #22 is Database Filenames and Paths. This query retrieves information from the sys.master_files 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.
1: -- File names and paths for all user and system databases on instance (Query 22) (Database Filenames and Paths)
2: SELECT DB_NAME([database_id]) AS [Database Name],
3: [file_id], name, physical_name, [type_desc], state_desc,
4: is_percent_growth, growth,
5: CONVERT(bigint, growth/128.0) AS [Growth in MB],
6: CONVERT(bigint, size/128.0) AS [Total Size in MB]
7: FROM sys.master_files WITH (NOLOCK)
8: ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);
9:
10: -- Things to look at:
11: -- Are data files and log files on different drives?
12: -- Is everything on the C: drive?
13: -- Is TempDB on dedicated drives?
14: -- Is there only one TempDB data file?
15: -- Are all of the TempDB data files the same size?
16: -- Are there multiple data files for user databases?
17: -- Is percent growth enabled for any files (which is bad)?
Figure 2: Query #22 Database Filenames and Paths
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.
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).