Database administrator winning Treasure

Administrator | data | The adjustment and optimization of database database may be one of the most important and critical tasks for a database administrator.
Unfortunately, tuning and performance tuning for databases like SQL Server is not an easy task. SQL Server has several monitoring and debugging tools that are powerful but difficult to master.


Fig. 1 Coefficient similar profiler

Now, however, there is a coefficient. It is a new product released by intrinsic design company and is suitable for beginners and experienced database administrators. Coefficient can help improve performance tuning for SQL Server and increase the ability of operators to monitor the internal work of SQL Server. The most useful thing is that it can list the monitoring data in a format that almost all database administrators can understand.
Coefficient is not a complete replacement for performance Monitor, Query Analyzer, or Profiler. Coefficient is more like the enhanced SQL Server Profiler. It works very much like profiler, but it is easier to use and understand than profiler. (Figure 1)

Coefficient running Process
Coefficient can be installed on a SQL Server server, or it can be installed on any computer that is connected to a SQL Servers over a network. Installation is very simple, the installation can be immediately monitored work. In general, follow these steps:
First, add a SQL Server to the main page, just as you would register a SQL Server with Enterprise Manager.
Select a database on the list that you want to analyze, and then create a trace, usually called Trace, that is a bit like a trace file built with SQL Server Profiler, but it's easier to manipulate than it is: don't have to go through as much as events, Data Complex work such as columns and filters. Instead, it is a step by step wizard that only needs to set a few simple parameters to complete, including specifying the trace table name (The SQL Server table that coefficient uses to store trace data), setting the trace run time (the trial version can run for up to 20 minutes), Sets whether profiling is performed immediately after Trace is finished, and so on.
If you choose to run analysis immediately after Trace completes, you also need to specify the location of the profiling file store and the type of run analysis, which is often done by selecting the template, that is, the template (Template) is the specific type used to specify the analysis you want to perform.
When you complete the wizard, trace begins and runs until the specified time runs (Figure 2). During this time, monitoring of the resulting data is saved in the trace table specified in SQL Server. Analysis can then be done immediately, and users can perform multiple analyses of the monitoring data in the trace table at any time.
The results of the analysis are saved as many HTML files, and users can easily open and click to view the contents. More importantly, the analysis results can be easily published on the intranet's network server for multiple users to use the browser for lookup.

Analysis is the key to coefficient
Before we introduce the results of the analysis, let's take a look at the template (Template) and profiling (analysis) types. When performing an analysis with coefficient, you can select a predefined template or create your own template. In fact, a template is a list of many types of analysis, and creating a template is the choice of the analysis object that needs to be done. From this list, coefficient processes the trace data collected from the database. Coefficient can analyze up to 49 different metrics, all of which are related to the internal work of SQL Server. Mainly have the following typical analysis:
The customer interrupts the request and interrupts the connection information. The communication problem between the client and the server can often be detected through the attention events.

SQL Server and NT user connection information. For example, which user is connected for how long.
Number of deadlocks and information that caused these deadlocks.
SQL Server error and warning information.
Missing information for table statistics. can help to determine if there is any omission in statistics that can be used to optimize queries and improve the speed of executing program code.

Figure 3 Selecting templates and analyzing projects

Whether the SQL statement executes and the SQL Prepare/execute model. Using these models can often speed up the execution of SQL code repeatedly.
The frequency with which the execution plan is reused.
The frequency at which stored procedures are recompiled.
The invocation of the stored program and SQL statement. Include duration, number of reads and writes, and so on.
The number of SQL Server timeouts.

A coefficient instance
Suppose you want to analyze the performance of stored procedures in your database and determine the most common denominator of stored procedures. One of the best solutions when tuning a database is to determine which stored procedures run the most frequently, and then optimize the stored procedures. Even if only one stored procedure is reduced by one-third uptime, it is a welcome achievement if you need to run this stored procedure 10,000 times a day.
First, create a trace and select Spsql template for analysis. Spsql templates can provide a lot of data, but we are only interested in which stored procedure is most frequently run, so just select the stored Procedure call Frequency a section of the Spsql template.
Follow the steps above to generate a monitoring report with tables and illustrations in the table, sorted by the frequency of the stored procedure, and the legend displayed above the table (Figure 3).

Figure 4 Analysis Report

From the diagram, it's easy to know which stored procedure is running frequently. In this example, the stored procedure name is PCGET_SJZH_NJ and is called 682 times during the trace period. You can also get more information about stored procedures from tables, such as average duration (milliseconds), average quantity, read and write times, and CPU average occupancy, and so on.
Another feature of coefficient is that more details can be provided. For example, suppose that we want to understand the details of the PCGET_SJZH_NJ process, we can click on the name to open another page to see more specific data, on the next page can see general statistics, duration statistics, read statistics, record statistics, CPU statistics, Source code for stored procedure references and stored procedures (Figure 4). Alternatively, click "Execution Plan" to view the execution plans for the stored procedure. It should be emphasized that the information provided is detailed, not only to see the data sent to the stored procedure, but also to see the parameters of the stored procedure and the execution Plan (Execution).

Embed help information in a report
Coefficient produces more data, and users may worry about confusing what they represent and how they can be used for database performance tuning. In fact, there is no need to worry about it, there is a large general description of the meaning of the data under each analysis report. These instructions do not address the specific data produced, but there is a lot of basic knowledge about database performance optimization. In a sense, coefficient itself is a SQL Server database optimization manual.

Applicable crowd of coefficient
Coefficient is useful for both SQL Server programmers and database administrators. Programmers can use it in the process of development and testing of SQL Server based applications, and database administrators find it useful to adjust the developed applications because the database changes over time, Database administrators can use coefficient to instantly view the internal operation of the database. The most common is the use of coefficient to define the performance of the database benchmarks, and then compare the results of different periods of analysis to understand the operation of the database.