SQL event probe solution

Generally, you can use the SQL event Probe:

1. Find the worst executed Query

For example, you can create a trail to capture events related to the tsql and stored procedure event classes (especially RPC: Completed and SQL: batchcompleted. This trail contains all data columns, groups by duration, and specifies event rules. For example, if the duration of a specified event is at least 1,000 milliseconds, you can track and exclude short-running events. You can increase the minimum value of duration as needed. If you want to monitor only one database at a time, specify a value for the database ID event criterion.

2. identify the cause of the deadlock
For example, you can create a trail to capture events with tsql and stored procedure (RPC: Starting and SQL: batchstarting) and locks (LOCK: deadlock and lock: deadlock chain) related events. This trail contains all data columns and is grouped by event class. If you want to monitor only one database at a time, specify a value for the database ID event criterion.

To view the connections involved in a deadlock, Perform one of the following operations:

Open a trail that contains captured data, group the data by clientprocessid, and expand the two connections involved in the deadlock.

Save the captured data to a trail file and open the trail file twice so that it is displayed in two separate SQL event probes. Group the captured data by clientprocessid, and expand the process ID involved in the deadlock. Each deadlock connection is in a separate window. Tiled window to view events that cause deadlocks.

3. monitor the performance of Stored Procedures

For example, you can create a trail to capture event classes (SP: Completed, SP: starting, SP: stmtcompleted, and SP: stmtstarting) and tsql event classes (SQL: batchstarting and SQL: batchcompleted. This trail contains all data columns and is grouped by clientprocessid. If you want to monitor only one database at a time, specify a value for the database ID event criterion. Similarly, if you want to monitor only one stored procedure at a time, specify a value for the Object ID event criterion.

4. Audit Microsoft % 26amp; reg; SQL Server % 26amp; #8482; Activity

You can use the SQL event probe to review activities in SQL Server. For example, if the security administrator needs to know who has logged on to the server at any time, you can create an SQL event probe trail to provide a complete view of the users who have logged on to the server or logged out from the server. Subsequently, such information can be used for legal purposes to record activities and for technical purposes to track violations of security policies.

To set an SQL event probe trace to track users who have logged on to or logged out from the server, perform the following operations:

Create a trail and select audit login event.

To return the appropriate information, specify the following data columns:
Eventclass (selected by default)

Eventsubclass

Loginsid

Loginname

Monitors the transact-SQL activity of each user.
You can create a trail to capture events related to the sessions event class, existingconnection, and tsql event class. Include all data columns in this trail. do not specify any event standards and group captured events by dbusername.

 

 

The first step to use SQL Server Profiler is to determine the reason for monitoring the SQL server instance. This topic describes how to use SQL Server Profiler to collect tracing information.

Typical solutions for using SQL Server Profiler include:

• Find the query with the worst execution condition.
  For example, you can createTsqlAndStored ProcedureEvent class (RPC: CompletedAndSQL: batchcompleted) Related event tracking. This trail contains all data columnsDurationGroup and specify event rules. For example, ifDurationIf it must be at least 10000 microseconds, you can clear short-lived events from the trail. Can be increased as neededDuration. If you want to monitor only one database at a time, specifyDatabase IDStandard event value.
• Determine the cause of the deadlock.
  For example, you can createTsqlAndStored ProcedureEvent class (RPC: StartingAndSQL: batchstarting) AndLocksEvent class (Deadlock Graph,Lock: deadlockOrLock: deadlock Chain) Related event tracking. This trail includes all data columns andEvent ClassGroup. If you want to monitor only one database at a time, specifyDatabase IDStandard event value. If you specifyDeadlock GraphEvent class. SQL Server Profiler generates a graphical deadlock. For more information, seeAnalyze deadlocks using SQL Server Profiler.
  To view the connections involved in a deadlock, Perform one of the following operations:
  • Open a trail that contains captured data, and pressClientprocessidGroup the data and expand the two connections involved in the deadlock.
  • Save the captured data to the trace file, and then open the trace file twice so that it appears in two separate SQL Server Profiler windows. PressClientprocessidGroup the captured data and expand the client process ID involved in the deadlock. Each deadlock connection will appear in a separate window. Tile the two windows to view the events that cause the deadlock. To save the graphic data of a specific deadlock to a file, right-click the deadlock event and select "extract event data" .
• Monitor the performance of stored procedures.
  For example, you can createStored ProcedureEvent class (SP: Completed,SP: Starting,SP: stmtcompletedAndSP: stmtstarting) AndTsqlEvent class (SQL: batchstartingAndSQL: batchcompleted) Related event tracking. This trail includes all necessary data columns andClientprocessidGroup. If you want to monitor only one database at a time, use the database ID filter as "Database ID" The event Standard specifies a value. Similarly, if you want to monitor only one stored procedure at a time, use the Object ID filter as "Object ID" The event Standard specifies a value.
• Audit SQL Server activities.
  For example, if the security administrator often needs to know the users logging on to the server, the SQL Server Profiler tracking can provide a complete record of the users logging on to the server or logging off from the server. Moreover, this information can be used for legal or technical purposes.
  Create a trail and selectAudit LoginEvent. To return the appropriate information, specify the following data columns:Eventclass(Selected by default ),Eventsubclass,LoginsidAndLoginname.
• Monitors the transact-SQL activity of each user.
  You can createSessions,ExistingconnectionAndTsqlEvent-related event tracking. This trail contains all data columns, does not specify any event rules, andDbusernameGroup captured events.
• Collects typical examples of events used for stress testing.
  SQL Server Profiler provides a predefinedTsql_replayTemplate. This template can be used for iterative optimization, such as benchmarking.
• Collects event examples for optimizing physical database design through database engine optimization consultants.
  SQL Server Profiler provides a predefined optimization template that collects and tracks the corresponding Transact-SQL events in the output for use as a workload in the database engine optimization advisor.

 

SQL event Probe

SQL event probe is a very effective tool to solve the performance problems of SQL Server 7.0 or later applications. The SQL event probe allows you to easily capture all events on the server under normal loads and provide information about these events. Use the SQL event probe with Microsoft Windows NT Performance Monitor and some simple queries to determine whether blocking occurs. This provides you with the information necessary to solve most performance problems.

Monitored object

1. Set the SQL event probe to capture the trail. To do this, follow these steps:

A.	Open the SQL event probe.
B.	On the Tools menu, click options ".
C.	Make sure that the selectedAll event classesAndAll data Columns.
D.	Click OK ".
E.	Create a new trail.
F.	On the File menu, point to new and click tracking ".
G.	On the General tab, specify the trail name and the file to capture data.
H.	On the events tab, add the following event types to the trail: Title Event to be added Description Cursors Cursorprepare This event indicates that the cursor has been prepared on the SQL statement using ODBC, oledb, or DB-library. Error and warning Missing column statistics This event indicates that column statistics that may be useful to the optimizer are unavailable.TextMultiple columns with missing statistics are displayed. This event togetherMisc: auto-updatestatsEvent indicates triggeredAutomatically create statistics. Misc. Attention This event indicates that the client sends a notice signal. Misc. Auto-updatestats This event indicates that it has been triggeredAutomatically update statistics. Misc. Exec prepared SQL This event indicates that ODBC, ole db, or DB-library has executed one or more previously prepared Transact-SQL statements. Misc. Execution Plan This event indicates the plan tree for executing the transact-SQL statement. Misc. Prepare SQL This event indicates that the ODBC, ole db, or DB-library application has prepared one or more Transact-SQL statements to be used. Misc. Unprepare SQL This event indicates that the ODBC, ole db, or DB-library application is not preparing one or more of the transact-SQL statements to be used. Sessions Connect This event indicates that a new connection has been created. Sessions Disconnect This event indicates that the client is disconnected. Sessions Existing connection This event indicates that a connection exists when the SQL event probe trail is started. Stored Procedures SP: Completed This event indicates that the stored procedure has been completed. Stored Procedures SP: recompile This event indicates that the stored procedure is re-compiled during execution. Stored Procedures SP: Starting This event indicates that the stored procedure has started to be executed. Stored Procedures SP: stmtcompleted This event indicates that the statement in the stored procedure has been executed. Tsql: SQL: batchcompleted This event indicates that the transact-SQL batch processing is completed.TextThe column displays the executed statements. Tsql: SQL: stmtcompleted This event indicates that the transact-SQL statement has been completed.TextThe column displays the executed statements. Tsql: RPC: Completed This event indicates that the Remote Procedure Call (RPC) has been completed.
I.	If the application receives a timeout error, a stop response (suspension), or other events that cause a problematic statement to never end running, the following events should also be included: Tsql: SQL: batchstarting This event indicates the start of the transact-SQL batch processing.TextThe column displays the statements being executed. Tsql: SQL: stmtstarting This event indicates the beginning of a Transact-SQL statement.TextThe column displays the statements being executed. Tsql: RPC: Starting This event indicates the start of a Remote Procedure Call (RPC. Stored Procedures SP: stmtstarting This event indicates that the statement in the stored procedure is being executed. This will help you to see the statement being executed when the timeout occurs.
J.	InData ColumnTab to ensure that the following columns are included: For SQL Server 2000 Start Time End Time Loginsid Spid Event Type Text Data Integer Data Binary data Duration CPU Read Write Application name NT Username DB Username For SQL Server 7.0 Start Time End Time Connection ID Spid Event Type Text Integer Data Binary data Duration CPU Read Write Application name NT Username SQL user name

For information about using SQL event probes, see SQL Server 7.0 and SQL Server 2000 books online.

　　　　　　　　　

SQL event ProbeAuthor: Zhu qianbang

SQL event probe is a graphical tool provided by SQL Server. It can be used to implement the following functions:

L monitors the performance of SQL Server instances.

2. debug the statements and stored procedures.

3. Identify slow queries.

4. In the project development stage, test SQL statements and stored procedures by executing statements in one step to confirm that the code runs as expected.

5. Solve SQL server problems by capturing events in the production system and replaying them in the test system. This is useful for testing and debugging, and allows users to continue using the production system without interference.

6. Review and review the activities that occur in the SQL server instance. This allows the security administrator to review any audit events, including logon attempts and failures, as well as access statements and object permissions.

Note that the system must have at least 10 MB of available space when running the SQL event probe. Otherwise, all functions of the SQL event probe will be stopped.

1. Create a trail

From the "Start" menu, select "event probe" from the "Microsoft SQL Server" menu to enter the main interface of the event probe. You can click "New Trail" on the toolbar or select "Trail" in "New" under "file" to start a new trail. At this time, the system prompts us to enter the server name, corresponding account, and password of the database to be tracked. Note that the account used for tracking must be a member of the "system admin" role. Click "OK". The following dialog box is displayed (figure 1 ).

There are four tabs in the upper part of the dialog box. The default option is "regular". In this option, we can set the trace name and trace template. As a beginner, we will use the default settings here.

Click the "events" tab to go to Figure 2. In Figure 2, we can see many events for monitoring. Generally, you can use the default event. Unless you really need it, we do not recommend that you select all events for monitoring.

In the next "data column" option (figure 3), we can select the information to be saved for each captured event, and the default information provided by the system is basically enough. If the system has only one user, such information as applicationname and loginname can even be removed from the default options.

2. Prepare the test code

To demonstrate the functions of the SQL event probe, I first use C # To write a small windows application. On the main interface of this applet, we put a text box and two buttons. When you click the "show" button, the application will display the company name whose customerid is "alfki" in the MERs table in the text box. When you click "save", the application will save the modified New company name. The following is the main code of the Applet:

// Load information

Private void btnload_click (Object sender, system. eventargs E)

{

// Database connection information. The database is the northwind database provided by Microsoft. The server, user name, and password are modified as needed.

String strcon = & quot; server = James; database = northwind; uid = sa; Pwd = & quot ;;

Sqlconnection Cn = new sqlconnection (strcon );

Sqlcommand cmd = new sqlcommand (& quot; select companyName from customers where customerid = 'alfki '& quot;, CN );

CN. open ();

Sqldatareader DR = cmd. executereader ();

If (dr. Read ())

{

// Display the company name in the text box

Txtcustomer. Text = dr. getstring (0). Trim ();

}

}

........................................ ...........

Generally, you can use the SQL event Probe:

1. Find the worst executed Query

For example, you can create a trail to capture events related to the tsql and stored procedure event classes (especially RPC: Completed and SQL: batchcompleted. This trail contains all data columns, groups by duration, and specifies event rules. For example, if the duration of a specified event is at least 1,000 milliseconds, you can track and exclude short-running events. You can increase the minimum value of duration as needed. If you want to monitor only one database at a time, specify a value for the database ID event criterion.

2. identify the cause of the deadlock
For example, you can create a trail to capture events with tsql and stored procedure (RPC: Starting and SQL: batchstarting) and locks (LOCK: deadlock and lock: deadlock chain) related events. This trail contains all data columns and is grouped by event class. If you want to monitor only one database at a time, specify a value for the database ID event criterion.

To view the connections involved in a deadlock, Perform one of the following operations:

Open a trail that contains captured data, group the data by clientprocessid, and expand the two connections involved in the deadlock.


Save the captured data to a trail file and open the trail file twice so that it is displayed in two separate SQL event probes. Group the captured data by clientprocessid, and expand the process ID involved in the deadlock. Each deadlock connection is in a separate window. Tiled window to view events that cause deadlocks.

3. monitor the performance of Stored Procedures

For example, you can create a trail to capture event classes (SP: Completed, SP: starting, SP: stmtcompleted, and SP: stmtstarting) and tsql event classes (SQL: batchstarting and SQL: batchcompleted. This trail contains all data columns and is grouped by clientprocessid. If you want to monitor only one database at a time, specify a value for the database ID event criterion. Similarly, if you want to monitor only one stored procedure at a time, specify a value for the Object ID event criterion.

4. Audit Microsoft & reg; SQL Server & #8482; Activities

You can use the SQL event probe to review activities in SQL Server. For example, if the security administrator needs to know who has logged on to the server at any time, you can create an SQL event probe trail to provide a complete view of the users who have logged on to the server or logged out from the server. Subsequently, such information can be used for legal purposes to record activities and for technical purposes to track violations of security policies.

To set an SQL event probe trace to track users who have logged on to or logged out from the server, perform the following operations:

Create a trail and select audit login event.


To return the appropriate information, specify the following data columns:
Eventclass (selected by default)

Eventsubclass

Loginsid

Loginname

Monitors the transact-SQL activity of each user.
You can create a trail to capture events related to the sessions event class, existingconnection, and tsql event class. Include all data columns in this trail. do not specify any event standards and group captured events by dbusername.