Database administrator success-MySQL-mysql tutorial

Database adjustment and optimization may be one of the most important and critical tasks of a database administrator. Unfortunately, it is not easy to adjust databases such as SQLServer and adjust the performance. SQLServer has several monitoring and debugging tools, which are powerful but difficult to master. Coefficient is similar to Profiler. However, database adjustment and optimization may be one of the most important and critical tasks of a database administrator.
Unfortunately, it is not easy to adjust databases like SQL Server and adjust their performance. SQL Server comes with several monitoring and debugging tools, which are powerful but difficult to master.


Coefficient is similar to Profiler

However, now Coefficient is available. It is a new product released by Intrinsic Design and is suitable for beginners and experienced database administrators. Coefficient can help improve the performance adjustment of SQL Server and improve the operator's ability to monitor the internal work of SQL Server. It lists metric data in a format that is understandable to almost all database administrators.
Coefficient is not a full replacement of Performance Monitor, Query Analyzer or Profiler. Coefficient is more like enhanced SQL Server Profiler. It works like Profiler, but is easier to use and understand than Profiler. (1)

Coefficient running process
Coefficient can be installed on the SQL Server or on any computer connected to SQL Servers through a network. The installation is very simple. after installation, you can immediately perform monitoring. Generally, follow these steps:
First, add an SQL Server on the homepage, just like registering an SQL Server with Enterprise Manager.
Select a database to be analyzed on the list and create a trail, that is, Trace, which is a bit like a Trace file created using SQL Server Profiler, but easier to operate than it: complex tasks such as Events, Data Columns, and Filters are not required. Instead, it is a step-by-step wizard. you only need to set a few simple parameters, including specifying the Trace table name (Coefficient is used to store the SQL Server table for tracking data) set the Trace running time (the trial version can only run for 20 minutes), set whether the analysis is executed immediately after the Trace is completed, and so on.
If you choose to run Analysis immediately after the Trace is complete, you also need to specify the location of the Analysis file and the type of the running Analysis. these are often done by selecting a template, that is, A Template is used to specify the type of analysis to be executed.
After the Wizard is completed, the Trace starts until the specified time is reached (2 ). During this period, the monitoring data is stored in the Trace table specified in SQL Server. You can perform Analysis immediately. you can also perform multiple analyses on the monitoring data in the Trace table at any time.
The analysis results are saved as many HTML files. you can easily open and click to view the content. More importantly, the analysis results can be easily published on the intranet's network servers for multiple users to view through browsers.

Analysis is the key to Coefficient
Before introducing the Analysis results, let's first understand the Template and Analysis types. When using Coefficient for analysis, you can select a predefined template or create your own template. In fact, a template is a list of many Analysis types. creating a template is to select the target analysis object. Through this list, Coefficient processes the trace data collected from the database. Coefficient can analyze up to 49 different metrics, which are related to the internal work of SQL Server. It mainly includes the following typical performance analysis:
Information about the customer's interrupted request and connection. Through attention events, you can often find out the communication problem between the client and the server.

SQL Server and NT user connection information. For example, how long is the connection time of a user.
Number of deadlocks and information that causes these deadlocks.
SQL Server errors and warnings.
Table statistics. It can help determine whether there are any omissions in statistics. This information can be used to optimize queries and increase the speed of executing program code.

Select template and analysis project

Whether the SQL statement is executed and the SQL Prepare/Execute model. Using these models can often speed up repeated SQL code execution.
The frequency of reuse of execution plans.
The recompilation frequency of stored procedures.
Invocation of storage programs and SQL statements. Including duration, number of reads and writes, and so on.
The number of SQL Server timeouts.

A Coefficient instance
Assume that you want to analyze the performance of stored procedures in the database and determine the maximum commonalities of stored procedures. When adjusting a database, one of the best solutions is to determine which stored procedure runs most frequently and then optimize these stored procedures. Even if only one stored procedure reduces the running time by 1/3, it would be a good result if the stored procedure needs to be run 10,000 times a day.
First, create a Trace and select the SPSQL template for analysis. The SPSQL template can provide a lot of data, but we are only interested in which Stored Procedure runs most frequently, so we only need to select Stored Procedure Call Frequency report (a part of the SPSQL template ).
Follow these steps to generate a monitoring report. The report contains tables and legends, which are sorted by the running frequency of stored procedures. the legends are displayed on the table (3 ).

Analysis Report

It is easy to know which stored procedure runs frequently. In this example, the stored procedure name is pcGet_SJZH_NJ and is called 682 times in the Trace cycle. You can also obtain