SQL Server Performance Troubleshooting -1 Monitor Database via SP_WhoIsActive or FogLight

Hi,

I will explain how to Troubleshoot Performance problems of SQL Server in this Article Series.

 

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.

One or more of the following actions should be used to identify the problem.

 

  1. Monitor Database via SP_WhoIsActive or FogLight etc Third Party Software
  2. Monitor Database and Instances via Activity Monitor
  3. Monitoring Performance Counters via PERFMON and examine load status
  4. Review the SQL Server Errorlog file
  5. Use DMV ( Dynamic Management View ) and DMF ( Dynamic Management Function ) to analyze database in detail
  6. Examine Real time activities in a database using SQL Server Profiler
  7. Review Database in detail and reporting via SQLDIAG and RML Utilities tool
  8. Troubleshoot Performance Problems

 

 

Monitor Database via SP_WhoIsActive or FogLight etc.. Third Party Software

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.

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.