SQLServerProfiler-Performance Tuning

SQLServerProfiler-performance tuning has enough reason to become a hot topic. Today's fierce competition in the business field, if users think that an application is too slow, they will immediately turn to another vendor. To meet user requirements, SQL tracking loads some event classes, which can be used to find and debug performance bottlenecks. Performance

SQL Server Profiler-performance tuning has enough reason to become a hot topic. Today's fierce competition in the business field, if users think that an application is too slow, they will immediately turn to another vendor. To meet user requirements, SQL tracking loads some event classes, which can be used to find and debug performance bottlenecks. Performance

SQL Server Profiler-Performance Tuning

Performance has enough reason to become a hot topic. Today's fierce competition in the business field, if users think that an application is too slow, they will immediately turn to another vendor. To meet user requirements, SQL tracking loads some event classes, which can be used to find and debug performance bottlenecks.

Performance monitoring technology can be roughly divided into two categories: the technology used when fault-related knowledge is known and the technology used to locate the fault (or find whether a fault exists. If you find some problems with this fault, you can obtain more information in this regard. Therefore, starting with 2nd technologies that help precisely locate the fault area, we will discuss how to conduct more detailed analysis.

When starting a new database performance tuning project, you must first find out which query has the lowest efficiency. In other words, determine the cause of the worst performance so that you can find the best tuning effect. At this stage, do not trace too much information. Generally, only the "Stored Procedures: RPC: Completed" and "TSQL: SQL: BatchCompleted" events are started. These events are all selected in the TSQL_Duration template provided by the SQL Server Performance Analyzer. We recommend that you add the read, write, and CPU columns that are not selected in the default template to these two events for a more complete description. We also recommend that you select the TextData column for the "Stored Procedures: RPC: Completed" event instead of the (default) BinaryData column-which makes subsequent data processing easier. Displays a complete set of given events.

650) this. width = 650; "title =" clip_image001 "style =" border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; "alt =" clip_image001 "src =" http://www.68idc.cn/help/uploads/allimg/151111/121ABY9-0.jpg "border =" 0 "height =" 533 "/>

If you select an event, you must set a short-term filter in milliseconds in the lifetime column. Most of the active OLTP systems that have been used have an extremely large number of 0 Ms queries, and these are obviously not the best in terms of performance bottlenecks. It usually starts from the filter set to 100 ms, and then starts to work. The method is to increase the signal-to-noise ratio in each iteration, eliminate small queries, and only retain queries with high potential for performance tuning. Based on the load of applications and servers, each iteration is usually tracked for 10 ~ 15 minutes later, view the results and moderately increase the value until only several hundred events are obtained during the tracking. This 10 ~ The number of 15 minutes is too long for some very busy applications.

Another option is to run only the initial trace and then filter the results. The simple method is to use the NTILE Window Function of SQL Server 2005, which divides the input rows into "buckets" with the same number ". If you want to view only the first 10% queries in a lifetime-based tracking table, you can use the following query:

SELECT *FROM(SELECT*,NTILE(10) OVER(ORDER BY Duration) BucketFROM TraceTable) xWHERE Bucket = 10

Note: execution of a large number of seemingly small (or even 0 ms) queries may also lead to performance failures. However, this problem usually requires removing useless interfaces, the system has a systematic solution, instead of tuning through the Transact-SQL query. If you do not know how to operate a specific application, it is also difficult to find such problems through performance analysis. Therefore, this issue is not discussed here.

If it is hard to limit the number of returned events to a controllable level (this is a common problem in a busy system ), you have to make some adjustments to the results to better aggregate the output. The results obtained from the SQL trace contain the unprocessed text data of each query, which includes all actually used parameters. To further analyze the results, the data should be loaded into a table in the database and then aggregated, for example, to obtain the average lifetime or number of logical reads.

The problem is that if the unprocessed text data returned by the SQL trace result is successfully aggregated. Knowing the actual parameters is good and useful for re-generation of performance problems, but before trying to determine which query should be processed first, it is best to aggregate these results with the query "form. For example, the following two queries belong to the same form and use the same table and column. They only differ in the parameters used by the WHERE clause, but because their texts are different, therefore, it is impossible to aggregate them:

SELECT *FROM SomeTableWHERE SomeColumn = 1---SELECT *FROM SomeTableWHERE SomeColumn = 2

To help solve this problem and reduce these queries to a common form that can be aggregated, a clr udf is provided. A slightly revised version (which can also handle NULL) is as follows:

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true)]Public static SqlString sqlsig(SqlString querystring){Return (SqlString)Regex.Replace(Querystring.Value,@”([\s,(=<>!](?![^\]]+[\]](?:(?:(?:(?:(?# expression coming)(?:([N])?(‘)(?:[^’]’’)*(‘))(?# character)|(?:0x[\da-fA-F]*)(?# binary)|(?:[-+]?(?:(?:[\d]*\.[\d]*|[\d]+)(?# precise number)(?:[eE]?[\d]*)))(?# imprecise number)|(?:[~]?[-+]?(?:[\d]+))(?# interger)|(?:[nN][uU][lL][lL])(?# null))(?:[\s]?[\+\-\*\/\%\&|\^][\s]?)?)+(?# operatoers)))#,@”$1$2$3#$4”);}

This UDF finds the values of most Image Parameters and replaces them. After processing the preceding two queries with UDF, the output should be the same:

SELET *FROM SomeTableWHERE SomeColumn = #

You can use this UDF to help you process a trail table to find the first few queries. You can start with some rows in the next query. This query aggregates every common Query Form, the average lifetime, read, write, and CPU are obtained:

SELECTQueryForm,AVG(Duration),AVG(Reads),AVG(Writes),AVG(CPU)FROM(SELECTDbo.fn_sqlsig(TextData) AS QueryForm,l.* Duration AS Duration,l.* Reads AS Reads,l.* Writes AS Writes,l.* CPU AS CPUFROM TraceTableWHERE TextData IS NOT NULL) xGROUP BY QueryForm

Here, we can further use the average value to filter out more queries.

If you decide to adjust one or more queries, you can use SQL tracking for further analysis. For example, assume that the following stored procedures that can be created in the AdventureWorks database have been isolated as the cause of failure:

CREATE PROCEDURE GetManagersAndEmployees@EmployeeID INTASBEGINSET NOCOUNT ONEXEC uspGetEmployeeManagers @EmployeeIDEXEC uspGetManagerEmployees @EmployeeIDEND

To start a session to analyze what the stored procedure is doing, first open a new query window in the SQL Server Management studio and use the @ SPID function to obtain the spid of the session. Next, open the SQL Server Performance Analyzer, connect to the Server, and select the tuning template.

650) this. width = 650; "title =" clip_image002 "style =" border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; "alt =" clip_image002 "src =" http://www.68idc.cn/help/uploads/allimg/151111/121A63617-1.jpg "border =" 0 "height =" 536 "/>

This template adds SP: StmtCompleted to get a more complete description of Server Activity event combinations. This will cause more data to be returned for each call. Therefore, use the spid collected by the previous lock to filter the trail. You may also want to add an XML statistical value performance analysis event to display the plan to cancel the query plan, including the remaining information. Displays a complete event selection screen for this type of work.

Note: adding a display plan XML or deadlock chart event will add a tab named "event extraction Settings" in the "trace performance" dialog box, this tab includes options for automatically saving any collected query plans or deadlocked graph XML to text files, and can prevent future use of them as needed.

650) this. width = 650; "title =" clip_image003 "style =" border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; "alt =" clip_image003 "src =" http://www.68idc.cn/help/uploads/allimg/151111/121AA960-2.jpg "border =" 0 "height =" 536 "/>

Next, start the tracking in the SQL Server Performance Analyzer. Although server-side tracking is usually used for most performance monitoring, when processing a single query with a single spid, the Performance Analyzer imposes very little overhead on the table. Therefore, you can make full use of the UI for this kind of work. Displays the continuous output of the Performance Analyzer after starting the trace and running @ EmployeeID = 21. Select one of the XML events to highlight this feature. Connect each statement executed by the outermost stored procedure with all called stored procedures. You can see that there is a complete graphic query plan in the Performance Analyzer UI. This makes it an ideal assistant to help users tune complex multi-layer stored procedures.

650) this. width = 650; "title =" clip_image004 "style =" border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; "alt =" clip_image004 "src =" http://www.68idc.cn/help/uploads/allimg/151111/121A64523-3.jpg "border =" 0 "height =" 519 "/>

650) this. width = 650; "title =" clip_image005 "style =" border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; "alt =" clip_image005 "src =" http://www.68idc.cn/help/uploads/allimg/151111/121A641F-4.jpg "border =" 0 "height =" 633 "/>

SQL tracking does not actually adjust the settings, but it helps to find the queries that may cause failures and the components that need to work in these queries. However, its function is far from performance tuning.

Note: SQL tracking does not actually adjust the settings. The database engine optimization Advisor (DTA) tool of SQL Server can track an input file, in this way, you can query the index, statistical value, and partition more quickly. If you use the DTA tool, make sure that the system provides sufficient samples for queries that are generally processed by the system. If the number of samples collected is too large, the result may be biased, which may lead to a low level of advice provided by DTA, it is even possible to provide suggestions that cause other queries that have not yet produced performance faults in the input set.