In many systems, the text identified by DPA is part of a stored procedure. Being able to identify and rollup by the procedure, especially if a procedure executes multiple statements in a transaction, would give greater insight into the sql activity causing the waits.
The stored procedure usually can be identified and populated by DPA, adding context info out of box without "naming" the specific statement. Though since many companies use a drop/create deployment methodology in their deployments, simply using database_id and object_id would not be sufficient. It would need to be a SCD bound by db_name,schema_name, procedure,version as the primary key.
The real benefit shows up here when a single statement is changed. Currently, when a statement is tuned (as a result of DPA identifying it as an issue for instance!), the audit trail is lost. Hopefully the DBA has done a miraculous job and the new statement is so blazingly fast that finding it in the top N bad queries requires a huge N! If DPA allowed grouping of all statements in a proc, the new text would:
- Immediately show up under the procedure
- create an automatic link between pre and post tuning statements
- The impact on the whole batch would be immediately apparent when viewing historical trends for the proc
#3 above is most important. Charting and trending the impact of tuning is one of the primary selling points for DPA.
