[Independent R & D-contributed to SQL Server personnel] Description of use of index diagnosis and optimization software (1)-overall Preview

[Independent research and development] use of index diagnosis and optimization software (1)-overall Preview

Index design isDatabaseAn important part of the design plays a vital role in the database performance. However, the index design is not so easy, and the performance is not so easy to obtain, many technical staff, due to improper index creation, finally make the results counterproductive. It can be said that "indexing is successful, and indexing is failed ".

For a long time, we are always looking for good Performance Optimization Software, we have been using scripts and complex operations to analyze problems. Like everyone else, we have gone through many similar dilemmas, and then we developed Index Diagnostic and optimization software. CompletelyIndependent R & D, Already used in the enterprise.

First, let's take a look. I will sort out the instructions and then release the free version of the software!

Software startup, LoginDatabase

Upload at on February 28
Download Attachment (38.15 KB)

After login, the main interface is as follows:

Upload
Download Attachment (136.17 KB)

The software contains three functions:

Quick diagnosis of common index problems: common issue

Advanced Diagnosis Based on baseline analysis: advance.

Auxiliary Analysis: Assistant

For example, to analyze whether there is a missing index in a database, we only need to select the database and then click "missing"Index", The result is as follows:

Upload at on February 28
Download Attachment (173.46 KB)

The software analyzes the cost of each missing index, or provides the performance improvement index if an index is created.

In addition, you can select a row in the analysis and click the index operation above. For example, if you create an index, the software will analyze the index by default and automatically generate a script, in addition, the columns in the index are also sorted based on the data selection rate. More powerful, we can also modify the statements recommended by the software to make the index creation more reasonable:

Upload at on February 28
Download Attachment (168.61 KB)

Custom write statements are also quite worry-free, because the software provides necessary reference documents for writing tsql statements,CodeStructure, Automatically highlight the code, and verify the correctness of the script:

 

 

We can decide whether to use a custom script or automatically generated by software, and then add our own indexes. We can choose to execute multiple of them together,

Upload at on February 28
Download Attachment(131.11 KB)

After execution,

Of course, the above is just a simple demonstration of the functions of the software. After that, you can download and play it!

The following describes the advanced functions.
For advanced functions, you need to establish a baseline, and then use the software for analysis. The process is not complex and the software has built in a lot of analysis.AlgorithmYou only need to master the necessary knowledge of indexing, and then learn how to view the diagrams and data in the software.

Upload at on February 28
Download Attachment(77.26 KB)

If we do not have a baseline database, advanced features will not work.

We can run the Wizard to establish a benchmark database step by step and other related objects,

Upload at on February 28
Download Attachment(51.38 KB)

Step 1: Create a database,

Upload files at on February 28
Download Attachment(64.76 KB)

Step 2: Create related data tables,

Upload files at on February 28
Download Attachment(49.61 KB)

Step 3: Create related jobs to collect data,

Upload at on February 28
Download Attachment(56.85 KB)

Later, the advanced functions can be used. However, there may not be enough data in the benchmark database, and the analysis accuracy is not very high. As the collected data increases, the accuracy will be improved!

 

After the data is collected, You need to configure some baselines. In fact, you can use the collected data to check the values of those parameters, and what will happen if the values are lower or higher than the values, this is similar to some parameter indicators in the ticket during our health check.
Configuration (we will provide the parameter indicators ):

 

For example, the index search in our database,

In addition, we also have a general analysis overview.

Upload
Download Attachment(129.64 KB)

Upload
Download Attachment(59.77 KB)

This is where you can download more features!

 

[Independent research and development] use of index diagnosis and optimization software (2)-software download and Installation

[Independent research and development] use of index diagnosis and optimization software (3)-Introduction to functional modules of the software