Abstract: Described is a test framework for testing transformation rules of query optimizers. Rule patterns obtained as tree structures from a query optimizer are used to generate queries that are used to test the rule optimizer's transformation rules. The test framework tracks which rules are exercised for each query, and also determines the correctness of the transformation rule by comparing the results of the query processing with the rule and without the rule (by turning off the rule). The test framework creates a composite pattern corresponding to two or more rules, such as to test rules in a set (e.g., as pairs). Also described is the efficient execution of a test suite for correctness testing, in which queries of the test suite are selected based upon cost information.

Abstract: This patent application relates to foreign-key detection. One implementation obtains a set of data tables. This implementation automatically determines foreign-key relationships of columns from separate tables of the set.

Abstract: A tool facilitating static analysis for database applications, such that the static analysis tool (SAT) can significantly enhance the ability for developers to identify security, correctness and performance problems in database applications during the development phase of an application lifecycle. A static analysis tool for database applications presents a framework for database applications using the ADO.NET data access APIs. The SAT framework consists of a core set of static analysis services upon which verticals such as workload extraction, SQL injection detection, identifying data integrity violations, and SQL performance analysis are built using the core services.

Abstract: The described implementations relate to filtered index recommendations. In one case a filtered index recommendation (FIR) tool is configured to recommend a final set of filtered indexes to use with a workload. The final set is selected from a first set of candidate filtered indexes and a second set of merged filtered indexes.

Abstract: The subject disclosure pertains to exploitation of sequence information in a workload for the purpose of performance tuning. An optimal or exhaustive approach to tune sequences by mapping to a shortest path problem is provided as well as powerful techniques that result in optimal or nearly optimal solutions. Also disclosed is greedy approach that is much more efficient than the optimal approach and yet also generates solutions of comparable quality. Further yet, systems and methods are provided that facilitate extraction of sequence information and implementation of recommendations.

Abstract: The subject disclosure pertains to logical schema tuning for databases. Mechanisms are provided for automatically recommending one or more logical schema transformations to improve database performance with respect to workload and data characteristics. Furthermore, several potential schema transformations including desirable properties are presented including mapping tables, vertical partitioning, frequent value elimination and type and attribute conversion. Still further yet, disclosed is a mechanism for rewriting database operations (e.g. queries, updates . . . ) for an optimized schema to ensure correct evaluation.

Abstract: An outlier index for a database and a given workload is generated by identifying sub-relations of tuples in the database induced by selection and group by conditions in queries in the workload. A variance is then generated for values in each sub-relation. Sub-relations having higher variances are selected, and outliers from such sub-relations having higher variances are generated.

Abstract: Infrastructure for capturing and correlating application context and database context for tuning, profiling and debugging tasks. The infrastructure extends the DBMS and application profiling infrastructure making it easy for a developer to invoke and interact with a tool from inside the development environment. Three sources of information are employed when an application is executed: server tracing, data access layer tracing, and application tracing. The events obtained from each of these sources are written into a log file. An event log is generated on each machine that involves either an application process or the DBMS server process and the log file receives log traces from different processes on a machine to the same trace session. A post-processing step over the event log(s) correlates the application and database contexts. The output is a single view where both the application and database profile of each statement issued by the application are exposed.

Abstract: Assignment algorithm for automatically making assignments between documents and document reviewers for a review process. If the automated assignments need adjusting, a coordinator can manually refine the assignment(s). The assignment algorithm facilitates the automated assignment process based on inputs related to a constraint and/or a preference. The constraints and preferences include, but are not limited to, a conflict of interest, a minimum number of reviews, a maximum number of submissions, a partial assignment, bidding preferences, and health metrics. Once the assignments have been made, histograms can be generated that present an overview of certain health metrics, further allowing refinement of the assignment process.

Abstract: A proactive monitoring mechanism for correcting the choice of access methods (available query plans) for a given query, based on execution feedback from the same query. The mechanism exploits bypassing predicate short-circuiting inside the database server's predicate evaluation module to obtain expression cardinalities. The mechanism can also modify a plan to obtain expression cardinalities. These techniques are used judiciously by the query optimizer and/or a database administrator (DBA) so that the execution overheads are within acceptable limits.

Abstract: A method of estimating results of a database query, the results are estimated by performing a sampling of weighted tuples in a database based on a probability of usage of tuples required in executing a workload. A probability is associated with each tuple sampled. An aggregate is computed over values in each sampled tuple while multiplying by the inverses of the probabilities associated with each tuple sampled.

Abstract: A monitoring component of a database server collects a subset of a query workload along with related statistics. A remote index tuning component uses the workload subset and related statistics to determine a physical design that minimizes the cost of executing queries in the workload subset while ensuring that queries omitted from the subset do not degrade in performance.

Abstract: Systems and methodologies that split a table into a plurality of sub-tables, and vertical partitions. By analyzing an associated work load to determine frequently referenced columns, the subject invention supplies a compromise among various vertical partitioning strategies (e.g., candidate selection for table spilt) via a merging act, such that the table is split optimally for the work load taken as a whole Accordingly, an incoming query can optimally reference only required columns.

Abstract: Aggregation queries are performed by first identifying outlier values, aggregating the outlier values, and sampling the remaining data after pruning the outlier values. The sampled data is extrapolated and added to the aggregated outlier values to provide an estimate for each aggregation query. Outlier values are identified by selecting values outside of a selected sliding window of data having the lowest variance. An index is created for the outlier values. The outlier data is removed from the window of data, and separately aggregated. The remaining data without the outliers is then sampled to provide a statistically relevant sample that is then aggregated and extrapolated to provide an estimate for the remaining data. This sampled estimate is combined with the outlier aggregate to form an estimate for the entire set of data.

Abstract: The subject disclosure pertains to logical schema tuning for databases. Mechanisms are provided for automatically recommending one or more logical schema transformations to improve database performance with respect to workload and data characteristics. Furthermore, several potential schema transformations including desirable properties are presented including mapping tables, vertical partitioning, frequent value elimination and type and attribute conversion. Still further yet, disclosed is a mechanism for rewriting database operations (e.g. queries, updates . . . ) for an optimized schema to ensure correct evaluation.

Abstract: Aggregation queries are performed by first identifying outlier values, aggregating the outlier values, and sampling the remaining data after pruning the outlier values. The sampled data is extrapolated and added to the aggregated outlier values to provide an estimate for each aggregation query. Outlier values are identified by selecting values outside of a selected sliding window of data having the lowest variance. An index is created for the outlier values. The outlier data is removed from the window of data, and separately aggregated. The remaining data without the outliers is then sampled to provide a statistically relevant sample that is then aggregated and extrapolated to provide an estimate for the remaining data. This sampled estimate is combined with the outlier aggregate to form an estimate for the entire set of data.

Abstract: This disclosure describes leveraging workload information associated with executed database queries for estimating the result of a current database query. The workload information is analyzed to determine the usage of tuples in a database during query execution, such as how often a tuple is accessed and the number of different queries that accessed the tuple. A tuple is assigned a weight value that is based on the analyzed workload information. The particular tuples sampled for estimating a result for the current query is based on each tuple's weight value. The workload information may also be leveraged to generate an outlier index that identifies outlier tuples associated with the executed queries or that identifies outlier tuples associated with particular queries that are executed more frequently than other queries. The result for the current query can also be estimated using the sampled values along with the outlier tuples from the outlier index.

Abstract: Aggregation queries are performed by first identifying outlier values, aggregating the outlier values, and sampling the remaining data after pruning the outlier values. The sampled data is extrapolated and added to the aggregated outlier values to provide an estimate for each aggregation query. Outlier values are identified by selecting values outside of a selected sliding window of data having the lowest variance. An index is created for the outlier values. The outlier data is removed from the window of data, and separately aggregated. The remaining data without the outliers is then sampled in one of many known ways to provide a statistically relevant sample that is then aggregated and extrapolated to provide an estimate for the remaining data. This sampled estimate is combined with the outlier aggregate to form an estimate for the entire set of data. Further methods involve the use of weighted sampling and weighted selection of outlier values for low selectivity queries, or queries having group by.

Abstract: Aggregation queries are performed by first identifying outlier values, aggregating the outlier values, and sampling the remaining data after pruning the outlier values. The sampled data is extrapolated and added to the aggregated outlier values to provide an estimate for each aggregation query. Outlier values are identified by selecting values outside of a selected sliding window of data having the lowest variance. An index is created for the outlier values. The outlier data is removed from the window of data, and separately aggregated. The remaining data without the outliers is then sampled in one of many known ways to provide a statistically relevant sample that is then aggregated and extrapolated to provide an estimate for the remaining data. This sampled estimate is combined with the outlier aggregate to form an estimate for the entire set of data. Further methods involve the use of weighted sampling and weighted selection of outlier values for low selectivity queries, or queries having group by.

Abstract: Aggregation queries are performed by first identifying outlier values, aggregating the outlier values, and sampling the remaining data after pruning the outlier values. The sampled data is extrapolated and added to the aggregated outlier values to provide an estimate for each aggregation query. Outlier values are identified by selecting values outside of a selected sliding window of data having the lowest variance. An index is created for the outlier values. The outlier data is removed from the window of data, and separately aggregated. The remaining data without the outliers is then sampled in one of many known ways to provide a statistically relevant sample that is then aggregated and extrapolated to provide an estimate for the remaining data. This sampled estimate is combined with the outlier aggregate to form an estimate for the entire set of data. Further methods involve the use of weighted sampling and weighted selection of outlier values for low selectivity queries, or queries having group by.