System and method for an asynchronous queue in a database management system

• US 20060294058A1
• Filed: 06/28/2005
• Published: 12/28/2006
• Est. Priority Date: 06/28/2005
• Status: Abandoned Application

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Elmore, Keith, Friedman, Marc T., Zabback, Peter, Cunningham, Conor J.
• Application Number:
  US11/169,064
• Publication Number:
  US 20060294058A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/2462 Approximate or statistical queries

Abstract

A method for performing asynchronous statistics updates in a database management system includes receiving a first query against the database, determining if present statistics related to the first query are stale and entering on a queue a request to acquire updated statistics if the present statistics are stale. The queue jobs are executed asynchronously with respect to the query request. As a result, a first query plan may be developed using the present statistics related to the first query. Thus, no delay in processing the query due to statistics updates is incurred. The first query plan may be executed and results given to the requester. At some later time, the request to acquire updated statistics related to the first query is processed asynchronously from the query request. If subsequent queries are received, the queue can delete duplicate requests to update the same statistics. Those subsequent queries can benefit from the updated statistics.

Claims

1. A method for performing asynchronous statistics updates in a database management system, the method comprising:

receiving a first query against a database;

determining if present statistics related to the first query are stale;

entering on a queue, a request to acquire updated statistics related to the first query if the present statistics related to the first query are stale;

developing a first query plan using the present statistics related to the first query;

executing the first query plan; and

processing the request to acquire updated statistics related to the first query asynchronously with respect to the input query.

2. The method of claim 1, wherein determining if present statistics related to the first query are stale comprises determining if the number of changes to a table associated with the first query exceed a threshold.

3. The method of claim 1, wherein the present statistics related to the first query comprise a first histogram.

4. The method of claim 1, further comprising:

receiving a second query against the database;

determining if present statistics related to the second query are stale;

entering on a queue, a request to acquire updated statistics related to the second query if the present statistics related to the second query are stale; and

eliminating duplicate requests on the queue if tables associated with the first query and the second query are similar.

5. The method of claim 1, further comprising:

receiving a second query against the database after processing the request to acquire updated statistics related to the first query asynchronously with respect to the input query;

determining if present statistics related to the second query are stale;

developing a second query plan using the updated statistics related to the first query if the second query is associated with columns of tables used in the first query such that the updated statistics related to the first query may be used for the second query plan; and

executing the second query plan.

6. The method of claim 5, wherein the updated statistics comprise a second histogram.

7. The method of claim 1, further comprising:

stopping the processing of the request to acquire updated statistics related to the first query asynchronously by killing a job representing the processing.

8. The method of claim 1, further comprising:

determining if there is a cached plan for the first query before determining if present statistics related to the first query are stale; and

using the cached plan with the present statistics related to the first query if there is a cached plan for the first query and newer statistics related to the first query are unavailable, whereby the step of developing a first query plan is avoided.

9. A system for performing database statistics updates, the system comprising;

a database management system (DBMS) for accepting and evaluating queries against a database, the DBMS having a query optimizer;

a queue for accepting asynchronous jobs entered from the optimizer;

a processor having access to memory, the memory having instructions which when executed, perform a method comprising;

receiving a first query against the database;

determining if present statistics related to the first query are stale;

entering on the queue, a request to acquire updated statistics related to the first query if the present statistics related to the first query are stale;

developing a first query plan using the optimizer and the present statistics related to the first query;

executing the first query plan; and

processing the request to acquire updated statistics related to the first query asynchronously with respect to the input query.

10. The system of claim 9, further comprising:

a counter to count the number of changes to one of a table and table column associated with the first query wherein if the counter exceeds a threshold, then the present statistics related to the first query are stale.

11. The system of claim 9, wherein the present statistics related to the first query comprise a first histogram.

12. The system of claim 9, wherein the updated statistics comprise at least one of a single histogram, single column densities, multicolumn histogram, multicolumn densities and multicolumn distinct counts.

13. The system of claim 9, further comprising the method steps:

receiving a second query against the database;

determining if present statistics related to the second query are stale;

entering on the queue, a request to acquire updated statistics related to the second query if the present statistics related to the second query are stale; and

eliminating duplicate requests on the queue if tables associated with the first query and the second query are similar.

14. The system of claim 9, further comprising:

a cache for storing execution plans;

and further comprising the method steps;

determining if there is a cached plan for the first query before determining if present statistics related to the first query are stale; and

using the cached plan with the present statistics related to the first query if there is a cached plan for the first query and newer statistics related to the first query are unavailable, and wherein the step of developing a first query plan is avoided.

15. A computer-readable medium having computer-executable instructions for performing a method for asynchronously creating indexes in a database, the method comprising:

receiving a first query against the database;

determining if an index related to the first query is missing;

entering on a queue, a request to create a first index related to the first query if the index related to the first query is missing;

developing a first query plan;

executing the first query plan; and

processing the request to create a first index related to the first query asynchronously with respect to the input query.

16. A computer-readable medium of claim 15, wherein the step of determining if an index related to the first query is missing comprises determining if an index associated with the first query is one of missing and incomplete.

17. A computer-readable medium of claim 15, the method steps further comprising:

receiving a second query against the database;

determining if an index related to the second query is missing;

entering on a queue, a request to create a second index related to the second query; and

eliminating duplicate requests on the queue if tables associated with the first query and the second query are similar.

18. A computer-readable medium of claim 15, the method steps further comprising:

receiving a second query against the database after processing the request to create a first index related to the first query asynchronously with respect to the input query;

determining if an index related to the second query is missing;

developing a second query plan using the first index related to the first query if the second query is associated with columns of tables used in the first query such that the first index related to the first query may be used for the second query plan; and

executing the second query plan.

19. A computer-readable medium of claim 15, the method steps further comprising:

stopping the processing of the request to create a first index related to the first query asynchronously by killing a job representing the processing.

20. A computer-readable medium of claim 15, the method steps further comprising:

determining if there is a cached plan for the first query before determining if an index related to the first query is missing; and

using the cached plan wherein the step of developing a first query plan is avoided.