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.
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.
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).