Contact  |  Site Map  |  Privacy      Call for more info
 

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.