SQL Server Performance Troubleshooting -5 Using SQL Server Profiler

Hi,

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

 

Read previous article before this.

 

Examine Real time activities in a database using SQL Server Profiler

Using the SQL Server Profiler tool, you can determine what the problem within the SQL Server Database. The following issues can be detected with the SQL ServerProfiler tool.

 

  • TOP Queries
  • Durations
  • Deadlock
  • Lock
  • Trace Database
  • Trace Sessions
  • Monitoring the status of running Batch jobs such as Stored Procedure

 

You can run SQL Server Profiler as follows and start the Trace.

Type Trace Name as follows and you can use the old template. And also you can save this Trace file to Disk or Database.

 

 

 

All events that you can trace are like following.

You can trace Database, Locks,Performance, T-SQL, Stored Procedure and more.

 

For example; If you want to trace SQL Statements, you can enable this event by clicking related CheckBox like following.

 

 

Or If you want to trace Locks, then Click Locks section and enable related events by clicking related CheckBox like following.

 

You can also trace Stored Procedures.

 

And you can trace all TSQL Statements running at the database by clicking related CheckBox.

You can also filter this Trace result. I recommended you that make sure using Filter to see just only related results.

If you don’t use Filter, then you will see all results. Mostly I am using Duration Filter,DBUserName,DatabaseName and ApplicationName to trace just only see healthy results.

For example, I want to see all Statements that duration is greater than or equal to 5000 (ms). I will trace only statements that duration is greater than 5 sec in this case.

You can trace TOP SQL and duration of Statements using Filter.

 

When you Click OK button and start Run Trace, Statements and Stored Procedure events will be traced like following.

 

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.