Make use of tools and utilities that are available in SQL Server - performance & tuning

There are various tools available within SQL Server installation such as SQLDIag, SQLDumper, PSSDiag (to be downloaded as a seperate tool), SYSMON(PERFMON) and Profiler etc.

Among these SqlDiag and Sqldumper are most important to get more information for the Microsoft PSS engineers to troubleshoot & diagnose the dump issues on that troubled SQL Server instance. Bit of discussion about these 2 tools:

Sqldiag - utility used for report generation and collection of diagnostic information on database server / operating system configuration parameters. This cn be executed as a command-line operationg even the SQL Server instance is stopped. Within the log directory the following information is stored:

• Complete dump log of all SQL Server error logs, such as memory & hardware related;
• SQL Server Registry & system dll versions;
• Output of  sp_configure, sp_who, sp_lock, sp_helpdb, xp_msver and sp_helpextendedproc statements;
• sysprocesses - system table information at that point of time such as processes and connections (SPID & Deadlocks);
• In addition to the SQL Server information there are few operating system parameters (including reports about: OS version, Video display, Drivers, DMA, Memory, Services, IRQ and ports, Devices, Environment and Network);

You can find the installation of the \Program Files\Microsoft SQL Server\MSSQL\Binn directory by default.

Sqldumper - Sqldumper generates a "dump-on-demand" file for any Windows operating system application. Say for instance MS PSS might ask you to run this program to generate a dump file (a mini-dump file or a full dump file) for debugging an application problem. This tool has been introduced by SQL Development team since the Service pack 3 release and by default this Sqldumper utility is installed to the \Program Files\Microsoft SQLServer\80\Com directory. Find more information and 'how to use' about this tool from KBA917825 article.