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.