Hi,
I will explain how to Troubleshoot Performance problems of SQL Server in this Article Series.
SQL Server Performance Troubleshooting
It is expected to resolve performance issues by DBA ( Database Administrator ) when performance complaints in the SQL Server database begin to increase . Before You can resolve the performance issue, you need to determine what the problem is and why it occurs.
Monitor SQL Server Database via SP_WhoIsActive
One or more of the following actions should be used to identify the problem.
- Monitor Database via SP_WhoIsActive or FogLight etc Third Party Software
- Monitor Database and Instances via Activity Monitor
- Monitoring Performance Counters via PERFMON and examine load status
- Review the SQL Server Errorlog file
- Use DMV ( Dynamic Management View ) and DMF ( Dynamic Management Function ) to analyze database in detail
- Examine Real time activities in a database using SQL Server Profiler
- Review Database in detail and reporting via SQLDIAG and RML Utilities tool
- Troubleshoot Performance Problems
Monitor Database via FogLight
DBA ( Database Administrator ) should do real-time monitoring of the database for evertime. To do real-time monitoring of the database is possible via Real Time Monitoring Software like FogLight or stored procedures like SP_WhoIsActive.
Foglight provides GUI and some useful realtime monitoring and alerts. But it is not free and if you think about your company and if you have lots of critical databases, I recommend you that you can use this software to monitor database realtime.
But If there is no budget for this and your company is not big . Don’t use it because it is very expensive.
I recommend you that Create SP_WhoIsActive and monitor database via this Stored Procedure. The SP_WhoIsActive stored procedure, written by Adam mechanic, is available free of charge and provides information about the current session sessions and SQLs. After creating SP_WhoIsActive manually in the database, you can run it as follows.
You can review the following critical and important features of any session via SP_WhoIsActive.
- SQL Text
- Wait Info
- Blocking Session status
- CPU Usage
- TempDB Allocation
- Session duration and Login time
- Login name & Machine name
- Percent Complete
- Status
You can download latest SP_WhoIsActive using this link.
Download SP_WhoIsActive and copy-paste these SP into SSMS ( SQL Server Management Studio ) and execute and create SP_WhoIsActive, then you can use it.
Review the SQL Server Errorlog file
Errors that occur on the SQL Server instances can be found by examining the Error logs. You can see the error logs from the database as follows.
If you cannot access Instance , you can find the Errorlog files in the Default path below.
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log
I will continue to explain SQL Server Performance Troubleshooting in the next article.
Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.
SQL Server ( MSSQL DBA ) Database Tutorials for Beginners Database Administrators