Once you install
SQL
Trace Analyzer, you can quickly
find tables and views responsible for the most IO activity that is
likely contributing to poor performance.

Step 1 of 4:When you first open a trace, the trace data is displayed in
the Data and Filters tab.

Step 2 of 4:
Click on the “Start Trace Analysis” button to begin analysis.
In the Analysis Report tab, under SQL Calls, click on either
SELECT, INSERT, UPDATE or DELETE.

Step 3
of 4: To find tables and
views with the highest Read activity, in the Analysis Report
grid, click on the “Reads” column header. This will sort the list of
objects (tables, views or table-valued
functions) by the total
number of read operations.

Here, we see the top three objects with the highest Read activity:

Rank

Table/View/Table-Valued Function

Total Reads

1

Order

16820034

2

vw_Order

8958170

3

OrderItem

8526120

Adding to or fine tuning indexes in the most heavily read
tables or indexed views significantly reduce read operations
required by SQL statements to retrieve data. It reduces resource
utilization and query execution time, improving overall database
system performance.

We can also sort these objects by average read activity:

Rank

Table/View/Table-Valued Function

Average Reads

1

vw_Order

111977.13

2

Order

68097.30

3

OrderItem

60900.86

Step 4 of 4:
To find tables and views
with the highest Write activity, in the Analysis Report grid,
click on the “Writes” column header. This will sort the list of
objects (tables, views or table-valued functions) by the total
number of write operations.

Here, we see the top two objects with the highest Write activity:

Rank

Table/View/Table-Valued Function

Total Writes

1

Order

54

2

User

12

We can also sort these objects by average write activity:

Rank

Table/View/Table-Valued Function

Average Writes

1

Order

.14

2

User

.08

Finding objects with the highest IO
activity helps you determine which tables or indexed views are most
frequently read from or written to. Overall performance can be
greatly improved by reducing IO operations performed on these
objects. For example, proper indexing reduces read activity and
allows for shorter time of SQL statement execution and object
locking, reducing object contention and improving transaction
concurrency.

Another way of improving IO is by optimizing
SQL statements that access these tables, views or table-valued
functions. You can easily find SQL statements executing against a
highlighted object by clicking on the “SQL” filter icon.

To resolve long running-locks and deadlocks
resulting from high IO activity, you can use
SQL
Deadlock Detector.