SQL Profiler Event to track SQL statement

[btturner]

Need a recommendation on SQL Profiler. I have a user stating he has a poor performing BIZTALK query.

Generically asking, how can I capture the query?

What SQL Profiler EVENT(s) (eg. TSQL, Transactions, etc) should I specify?

What SQL Profiler DATA COLUMN(s) (eg. DatabaseID, etc)should I specify?

I can tune complex queries but I'm new to PROFILER and need some generic recommendation here to identify his QUERY! THX

 

[btturner]

/* SQL Profiler setup

- Start SQL Profiler and from FILE menu select NEW and then select TRACE.
You will be prompted to provide the information about the SQL Server you want to monitor.
Select server name, fill out login and password fields and then click OK.

- On the new screen, name the new trace as TRACE1.

- Check the box "Save to table" -- you will be prompted to select SQL Server, database and table where you
want to store collected information. It is a very good idea to use separate SQL Server (for example the desktop
version you have installed on your workstation) in order to avoid performance hit due to sequential inserts into
storage table.

- On the EVENTS tab first remove all counters from the right window and then select all collections of counters for
STORED PROCEDURES and TSQL. If you want to monitor any additional events, feel free to add them as required.

- On the filters tab select parameter OBJECTID from the tree and check the flag "Exclude system objects".

- Now, go back to the GENERAL tab and hit button RUN. SQL Profiler starts to collect information about all procedures
and ad-hoc queries running on SQL Server. Let it run for a few hours during operation time and now you are ready for
the analysis.

Data analysis

- There are two main counters you want to keep track of in your system: READS and DURATIOIN. For each completed
procedure or query SQL Profiler will capture this information along with the statement, user id and other parameters.

- Before running queries I suggest to create indexes for columns DURATION and READS in the storage table (TRACE1). This
will dramatically speed up your analysis.

CREATE NONCLUSTERED INDEX IND_TRACE_1 ON dbo.TRACE1(Duration)
GO
CREATE NONCLUSTERED INDEX IND_TRACE_2 ON dbo.TRACE1(Reads)
GO

- First thing you might be interested in is identifying long running procedures and queries. The following query will
identify 20 procedures, which take the most of the time to complete:

- SELECT * FROM TRACE WHERE Duration IN
(SELECT DISTINCT TOP 20 Duration
FROM TRACE ORDER BY Duration DESC)

- In order to understand why procedure takes a long time to finish, take a look on parameters READS.

- When the value of READS is low, then probably the required resources (e.g. tables or views) were locked by another
process. This indicates possible locking problems and you might want to take a look on the procedures that use same
objects.

- High READS might indicate table scans or not optimal indexes chosen by query optimizer. Usually, you want to keep
READS parameter as low as possible because operations related to accessing hard drives (reading and writing) are the
slowest ones in the system.

- Paste the statement you want to check into the Query Analyzer and select Show Execution Plan command from Query menu.

- Run the statement and tale a look at the query plan. It will show you if any table or index scans occur during the
execution. Table scans can be fixed by applying proper index to the columns participated in the WHERE clause or
joins. Index scans are much faster then table scans and some times can not be avoided, but when clustered index scan
occur you must check it the columns you use in WHERE clause or joins are in the clustered index and if this is not
the case, then creation of the separate non clustered index for this columns will boost query performance and decrease
READS volume.

- Statements that uses data a lot (READS is high) but do not take too much time to complete can by identified using the
similar query:

- SELECT * FROM TRACE WHERE Reads IN
(SELECT DISTINCT TOP 20 Reads
FROM TRACE ORDER BY Duration DESC)

*/