The SQL Server DBA’s essential toolkit list

This week, I attended the SQLSaturday 2014 in Paris. During the Pre-Conference on Thursday, I followed Isabelle Van Campenhoudt for her SQL Server Performances Audit session. This conference took the form of an experience sharing between attendees. Indeed, we tried to list together the most important software, tools, features or scripts which will help an SQL Server DBA during his work. In this blog, I want to share our final list with you.
 

Windows Server Level: Hardware & Applications

CrystalDiskMark

CrystalDiskMark is a free disk benchmark software. It can be downloaded here.

SQLIO

SQLIO is another free disk benchmark software. It can be downloaded here.

Windows Performance Monitor (PerfMon)

PerfMon is a Windows native tool which collects log data in real time in order to examine how programs running on the computer affect the performance.
PerfMon provides a lot of counters which measure the system state or the activity.
You can learn more on TechNet.
You can find the most important counters for SQL Server here.

Performance Analysis of Logs (PAL)

PAL is an Open Source tool based on the top of PerfMon. It reads and analyses the main counters looking for known thresholds.
PAL generates an HTML report which alerts when thresholds are reached.
PAL tool can be downloaded on CodePlex.

Microsoft Assessment and Planning (MAP)

MAP is a Microsoft toolkit which provides hardware and software information and recommendations for deployment or migration process for several Microsoft technologies (such as SQL Server or Windows Server).
MAP toolkit can be downloaded on TechNet.
 

SQL Server Level: Configuration & Tuning

Dynamic Management Views and Functions (DMV)

DMV are native views and functions of SQL Server which returns server state information of a SQL Server instance.
You can learn more on TechNet.

sp_Blitz (from Brent Ozar)

It is a free script which checks SQL Server configuration and highlights common issues.
sp_Blitz can be found on Brent Ozar website.

Glenn Berry’s SQL Server Performance

It provides scripts to diagnostic your SQL Server since SQL Server 2005.
These scripts can be downloaded here.

Enterprise Policy Management (EPM) Framework

EPM Framework is based on Policy-Based Management. It is a reporting solution which tracks SQL Server states which do not meet the specified requirements. It works on all instances of SQL Server since SQL Server 2000.
You can learn more on CodePlex.
 

SQL Server Level: Monitoring & Troubleshooting

SQL Profiler

SQL Profiler is a rich interface integrated in SQL Server, which allows to create and manage traces to monitor and troubleshoot an SQL Server instance.
You can learn more on TechNet.

Data Collector

Data Collector is a SQL Server feature introduced in SQL Server 2008, and available in all versions.
It gathers performance information from multiple instances for performance monitoring and tuning.
You can learn more on TechNet.

Extended Events

Extended Events is a monitoring system integrated in SQL Server. It helps for troubleshooting or identifying a performance problem.
You can learn more on TechNet.

SQL Nexus

SQL Nexus is an Open Source tool that helps you for identifying the root cause of SQL Server performance issues.
It can be downloaded on CodePlex.
 

SQL Server Level: Maintenance

SQL Server Maintenance Solution

It a set of scripts for running backups, integrity checks, and index statistics maintenance on all editions of Microsoft SQL Server since SQL Server 2005.
This solution can be downloaded on Ola Hallengren’s website.
 

Conclusion

This blog does not pretend to make a complete list of DBA needs, but it tries to cover most parts. You will notice that all softwares are free and recognized by the DBA community as reliable and powerful tools.
I hope this will help you.

For information, you can learn how to use these tools in our SQL Server DBA Essentials workshop.