SQL SERVER – A Quick Look at Performance – A Quick Look at Configuration

This blog post is written in response to the T-SQL Tuesday post of Tips and Tricks. For me, this is a very interesting subject. I perfectly enjoy a discussion when it is about performance tuning. I commonly get follow-up questions regarding this subject, but most of them do not give the complete information about their environment. That is why I always start with a quick look at configuration.

Whenever I get a question which does not have complete information but is obviously requesting for my help, my initial reaction is to ask more questions. When I ask more details, I usually get more questions from them rather than the details I was asking for. Indeed, this is an endless loop. I prefer to resolve a query or a problem quickly, easily and accurately so that there is no more confusion or further problems in the future.

Here is the method I follow: I send people who request my help a couple of SQL Server scripts and ask them to run these scripts on their system. Once they send me the results,  I would then have a good idea on what the status of their system is.

Here are a couple of scripts which I usually request them to run on their respective machines and get back to me with results in Excel.

1) System Configuration

SELECT *
FROM sys.configurations
ORDER BY name OPTION (RECOMPILE);

2) Filename and Paths of Database

SELECT DB_NAME([database_id])AS [DBName],
name, physical_name, type_desc, state_desc,
CONVERT( bigint, size/128.0) [SizeinMB]
FROM sys.master_files
ORDER BY DB_NAME([database_id])

3) Capturing Wait Types and Wait Stats Information at Interva

You can review the script mentioned in my earlier blog post over here.

Honestly, there is a lot of information one needs to solve a query, but this is how I start and get all the basic information from the questioner. Once I get these, I review the results and continue to ask more questions or help right away if I am able to reach the root cause of the issue.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.