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.
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
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.
Reference: Pinal Dave (http://blog.SQLAuthority.com)