SQL Server Profiler Tool

SQL Server Profiler Tool -- 

This tool give the graphical representation of the database server activity where user can trace the various SQL server issues and bugs like long duration queries, blocking queries, dead lock queries and more...

To trace the activities and monitoring the SQL Server are providing 180 events in the profiler tool.

Use of profiler –

-          Monitor the activity of SQL Server engine and analysis services.

-          Performance related problems of T- SQL

-          Providing graphical execution plan for the performance issues.

-          Identify SQL Server error and warning

-          Trace of activity to replicate the problem

-          Debug T-SQL code and store procedure.

Eight activity details have mentioned .Set the events and filters in the trace page to resolve and monitor each activities.

1.        How to find out the long duration query?

After set the events and filters it will give all the long duration queries .

Events –

-          ShowXML paln

-          RPC:Completed

-          SP:stmtcompleted

-          Sql:Batchcompleted

-          Sql:batchstarting

Filter –

Duration - Greater than or equal 10

2.       Blocked Process Query

By default blocked process events are not enabled in SQL Server .For find out the blocked query need to enable the Blocked process events in SQL Server

Sp_Configure ‘Show advance option’ 1;

GO

Reconfigure;

GO

Sp_Configure ‘Blocked process threed hold’;

GO

Reconfigure;

Events –

-          Blocked process report

3.       Dead locking issues

When two spid blocked each other that scenario in SQL Server dead lock will occur.

Events –

-          Deadlock graph

-          Lock:deadlock

-          Lock:deadlockchain

-          Rpc:Completed

-          Sp:stmtcompleted

-          Sql:batchCompleted

-          Sql:batchstarting

Filter

-          Database Name

4.       Memory warning

It will trace the memory warning issues

-          Execution warnings

-          Sort warnings

-          Server Memory changed

Events –

-          Execution warnings

-          Sort warnings

-          Showplan XML

-          Server Memory changed

-          RPC:Completed

-          Sp:stmtcompleted

-          Sql:batchcompelted

-          Sql:Batchstarting

Filter –

-          DatabseName

5.       Excessive auto stats activity

It will trace out all the auto start activity at the time of query firing what are the

Events –

-          Auto stats

Filter –

-          Database Name

6.       Excessive Table /  Index scan

For which queries case excessive table or index scan are happeing

Events –

-          Show XMl plan

-          Scan:started

-          Rpc:completed

-          Sp:stmtcompleted

-          Sql:BatchCompleted

-          Sql:batchstarting

Filter –

-          Application Name

-          Database name – not like ..

7.       Excessive SP Complications

For which queries excessive store procedure compilation are happenings at the time of running.

Events –

-          Sql:stmtcompleted

-          Sql :stmtrecompile

-          Sql:stmtstarting

Filter –

-          Database name

8.       Plan cache activity

It will show the details about the plan cache.

-          Catch Hit – Plan Found

-          Cache miss – Plan Not found

-          Cache Insert – Plan added

-          Cache Remove – Plan removed 

Events –

-          Rpc:Completed

-          Sp:CacheHit

-          Sp:CacheInsert

-          Sp:CacheMiss

-          Sp:CacheRemove

-          Sql:BatchCompleted

-          Sql:Batchstarting

Filter –

-          Database Name

n  Complete SQL profiler trace with performance monitor counter