{"id":10534,"date":"2019-07-18T14:40:35","date_gmt":"2019-07-18T14:40:35","guid":{"rendered":"https:\/\/ittutorial.org\/?p=10534"},"modified":"2020-10-11T14:15:41","modified_gmt":"2020-10-11T14:15:41","slug":"using-sql-server-profiler-sql-server-performance-troubleshooting-5","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/using-sql-server-profiler-sql-server-performance-troubleshooting-5\/","title":{"rendered":"Using SQL Server Profiler | SQL Server Performance Troubleshooting -5"},"content":{"rendered":"

Hi,<\/p>\n

I will continue to explain how to Troubleshoot Performance problems of SQL Server in this Article Series.<\/p>\n

\"\"<\/p>\n

<\/p>\n\n

 <\/p>\n

SQL Server Performance Troubleshooting<\/span><\/h1>\n

 <\/p>\n

Read previous article before this.<\/p>\n

https:\/\/ittutorial.org\/sql-server-performance-troubleshooting-4-using-dmv-dynamic-management-view-and-dmf-dynamic-management-function\/<\/p>\n

 <\/p>\n

SQL Server Profiler<\/span><\/h2>\n

 <\/p>\n

Examine Real time activities in a database using SQL Server Profiler<\/span><\/h3>\n

Using the SQL Server Profiler tool, you can determine what the problem within\u00a0the SQL Server Database. The following issues can be detected with the SQL ServerProfiler tool.<\/p>\n

 <\/p>\n

    \n
  • TOP Queries<\/li>\n
  • Durations<\/li>\n
  • Deadlock<\/li>\n
  • Lock<\/li>\n
  • Trace Database<\/li>\n
  • Trace Sessions<\/li>\n
  • Monitoring the status of running Batch jobs such as Stored Procedure<\/li>\n<\/ul>\n

     <\/p>\n

    You can run SQL Server Profiler as follows and start the Trace.<\/p>\n

    Type Trace Name as follows and you can use the old template. And also you can save this Trace file to Disk or Database.<\/p>\n

    \"\"<\/p>\n

     <\/p>\n

     <\/p>\n\n

     <\/p>\n

    All events that you can trace are like following.<\/p>\n

    You can trace Database, Locks,Performance, T-SQL, Stored Procedure and more.<\/p>\n

    \"\"<\/p>\n

     <\/p>\n

    For example; If you want to trace SQL Statements, you can enable this event by clicking related CheckBox like following.<\/p>\n

    \"\"<\/p>\n

     <\/p>\n

     <\/p>\n\n

    Or If you want to trace Locks, then Click Locks section and enable related events by clicking related CheckBox like following.<\/p>\n

    \"\"<\/p>\n

     <\/p>\n

    You can also trace Stored Procedures.<\/p>\n

    \"\"<\/p>\n

     <\/p>\n

    And you can trace all TSQL Statements running at the database by clicking related CheckBox.<\/p>\n

    You can also filter this Trace result. I recommended you that make sure using Filter to see just only related results.<\/p>\n

    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.<\/p>\n

    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.<\/p>\n

    You can trace TOP SQL and duration of Statements using Filter.<\/p>\n

    \"\"<\/p>\n

     <\/p>\n\n

    When you Click OK button and start Run Trace, Statements and Stored Procedure events will be traced like following.<\/p>\n

    \"\"<\/p>\n

     <\/p>\n

     <\/p>\n

    I will continue to explain\u00a0SQL Server Performance Troubleshooting in the next article.<\/p>\n

    https:\/\/ittutorial.org\/sql-server-performance-troubleshooting-6-collect-performance-data-using-sqldiag-tool\/<\/p>\n

    <\/div>\n
    <\/div>\n

     <\/p>\n

    Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.<\/span><\/h4>\n

    SQL Server ( MSSQL DBA ) Database Tutorials for Beginners Database Administrators<\/a><\/p><\/blockquote>\n