Application and database context correlation for database application developers

• US 8,307,343 B2
• Filed: 10/19/2007
• Issued: 11/06/2012
• Est. Priority Date: 10/19/2007
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Chaudhuri, Surajit, Narasayya, Vivek R., Symala, Manoj A.
• Primary Examiner(s): Kang, Insun
• Assistant Examiner(s): KRETZMER, ERIKA A
• Application Number:
  US11/875,616
• Publication Number:
  US 20090106746A1
• Time in Patent Office: 1,845 Days
• Field of Search: 717/128, 707/713, 707/759
• US Class Current: 717/128
• CPC Class Codes: G06F 11/3476 Data logging G06F11/14, G06F11/2205 take precedence
• CPC Class Codes: G06F 11/366 using diagnostics G06F11/0703 takes precedence
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/2438 Embedded query languages
• CPC Class Codes: G06F 2201/86 Event-based monitoring
• CPC Class Codes: G06F 8/20 Software design

Abstract

Infrastructure for capturing and correlating application context and database context for tuning, profiling and debugging tasks. The application context can include events such as data access events, and the database context can include events such as database server events. The events can be obtained from server tracing, data access layer tracing, and/or application tracing and written into respective log files. A data access event can indicate that an application consumed a row from a result set returned from a DBMS query. A post-processing step can correlate the application and database contexts by tokenizing strings and computing intersections between the tokenized strings. A tool inside a development environment may also suggest a query hint for the database or a data access API for the application based on the correlated context.

Claims

1. A computer-implemented system for application development, the computer-implemented system comprising:

a context component configured to obtain, during a profiling session;

application context information of an application, the application context information including data access events reflecting data access application programming interface (API) calls by the application to access a database, and

database context information of the database, the database context information including database server events exposed for profiling by a database server, the database server events reflecting execution of queries by the database server responsive to the data access API calls by the application;

an analyzer component configured to;

tokenize strings of the data access events and other strings of the database server events based on delimiters, and

compute intersections of tokens between the strings of the data access events and the other strings of the database server events to match the strings of the data access events to the other strings of the database server events to correlate the application context information to the database context information to facilitate presentation of a view of a profile of the application and the database during the profiling session; and

at least one processor configured to execute one or more of the context component or the analyzer component.

2. The system of claim 1, wherein the view of the profile is presented via the application.

3. The system of claim 1, further comprising:

a binary instrumentation module configured to instrument binary code of the application by injecting code into the application, the instrumented binary code emitting application events based on application activity,

wherein the analyzer component is further configured to correlate the application context information to the database context information using the application events emitted by the instrumented binary code of the application.

4. The system of claim 1, further comprising a presentation component configured to present a single view of the profile of the application and the database for each statement issued by the application.

5. The system of claim 1, wherein the application context information includes an application trace and the analyzer component is further configured to output a schematized view of the application trace, the schematized view reflecting both the application context information and the database context information.

6. The system of claim 1, further comprising a query component configured to provide browse, summarize and drill-down functionality on the view of the profile.

7. A method comprising:

obtaining, during a profiling session;

application context information of an application, the application context information including data access events reflecting data access application programming interface (API) calls by the application to access a database, and

database context information of the database, the database context information including database server events exposed for profiling by a database server, the database server events reflecting execution of queries by the database server responsive to the data access API calls by the application;

matching strings of the data access events to other strings of the database server events;

correlating the application context information to the database context information based on the matching to obtain correlated context information; and

based on the correlated context information, suggesting a query hint to the database that causes a query optimizer to reduce an estimated time of execution of an individual query,

wherein at least the correlating is performed by a processor.

8. The method according to claim 7, wherein the correlating further comprises:

matching individual data access events and individual database server events using a client process identification.

9. The method according to claim 8, wherein the matching the strings is performed for SQL statements issued in a single client process having multiple different threads.

10. The method according to claim 7, wherein the correlating further comprises:

identifying individual data access events that precede corresponding database server events using timestamps.

11. The method according to claim 7, wherein the matching is not exact.

12. The method according to claim 7, wherein the correlated context information comprises a view having rows that reflect both the application context information and the database context information.

13. One or more computer-readable memory devices or storage devices comprising processor-executable instructions which, when executed by one or more processors, cause the one or more processors to perform acts comprising:

obtaining;

application context information of an application, the application context information including data access events reflecting calls by the application to access a database, and

database context information of the database, the database context information including database events reflecting execution of queries by a database server responsive to the calls by the application;

matching strings of the data access events to other strings of the database events; and

correlating the application context information to the database context information based on the matching,

wherein the data access events comprise an individual event indicating the application consumed a row from a result set of a query submitted to the database by the application.

14. The one or more computer-readable memory devices or storage devices of claim 13, the database events comprising another individual event indicating completion of a structured query language (SQL) statement.

15. The one or more computer-readable memory devices or storage devices of claim 13, the database events comprising another individual event indicating a deadlock generated by the database.

16. The one or more computer-readable memory devices or storage devices of claim 13, the data access events comprising another individual event indicating the application opened a connection to the database.

17. The one or more computer-readable memory devices or storage devices of claim 13, the acts further comprising injecting code into the application.

18. The one or more computer-readable memory devices or storage devices of claim 17, wherein the injected code emits events including at least:

an identifier of a function,

a timestamp of entry to the function, and

a timestamp of exit from the function.

19. A method comprising:

obtaining, during a profiling session;

application context information of an application, the application context information including data access events reflecting data access application programming interface (API) calls by the application to access a database, and

database context information of the database, the database context information including database server events exposed for profiling by a database server, the database server events reflecting execution of queries by the database server responsive to the data access API calls by the application;

matching strings of the data access events to other strings of the database server events;

correlating the application context information to the database context information based on the matching to obtain correlated context information; and

based on the correlated context information, suggesting an appropriate individual data access API according to a best practice,

wherein at least the correlating is performed by a processor.

20. The method according to claim 19, wherein the individual data access API is used to pass parameters when using a stored procedure.