Silverlight implements real-time SQL monitoring for SQL Server Profiler

As a performance tool of the Microsoft SQL Server database series, SQL Server Profiler can track the running status of databases in real time and find slow queries or deadlocked SQL statements, to optimize the system. This article describes how to use Silverlight to monitor SQL Server Profiler in real time (the database environment uses SQL Server 2005 as an example)

 

First, let's take a look at the SQL Server Profiler, a performance tool that comes with the SQL Server database, and create a trail:

Select the default template Standard, and the rest will remain unchanged. Click Run:

You can see the SQL running status in the database, EventClass as the event type, TextData as the running SQL script, ApplicationName as the name of the execution source of an application, for example, from. the SQL script executed by the. Net application is. net SqlClient Data Provider. For example, if I run an SQL statement directly from the database client tool, the ApplicationName is "Microsoft SQL Server Management Studio-query", therefore, we can use the ApplicationName field to obtain the execution source of the program. LoginName is used as a database account. Generally, the System user is sa, including CPU, Reads, and Writes. Duration is useful, you can view the time consumed by executing the statement, which is also followed by the StartTime start time and EndTime. Of course, I will not list some hidden columns one by one. You can check it.

If you want to define a template that meets your needs, you can create or edit a template:

There are a lot of options for Event types. You can customize the Event as needed. Here I chose TSQL: SQL BatchCompleted in the simplest way:

Select a database DatabaseName in the column, which is similar to editing MyEnt. In this way, the template file will only perform SQL Execution tracking in the database named MyEnt:

Click Save.

 

In. net, in fact, SQL ServerProfiler itself is.. Net. Later, I found a Microsoft file in the installation directory C: \ Program Files \ Microsoft SQL Server \ 90 \ SDK \ Assemblies of the SqlServer2005 database. sqlServer. connectionInfo. dll, which can monitor the execution of SQL.

Start to create a project. The monitoring program is a console program, while the Silverlight client program displays real-time monitoring of SqlServerProfiler. The Silverlight client program communicates with the monitoring program through Socket. Due to restrictions on TCP communication, Silverlight must send policy requests to the monitoring program. For TCP communication, the current Silverlight port must be 4502-4530, therefore, you must set a Policy on the monitoring end. xml policy file:

<? Xml version = "1.0" encoding = "UTF-8"?>
<Access-policy>
<Cross-domain-access>
<Policy>
<Allow-from>
<Domain uri = "*"/>
</Allow-from>
<Grant-to>
<Socket-resource port = "4502-4530" protocol = "tcp"/>
</Grant-to>
</Policy>
</Cross-domain-access>
</Access-policy>

Now I will listen to the Policy request, the SL Client Connection/data sending and receiving requests, the database listens to the tracking request, and create three threads respectively:

// Create a Socket to listen for policy requests and send

This. _ policyThread = this. CreateThread (this. PolicyRequest );
// Create a Socket to listen for information requests and sending
This. _ infoThread = this. CreateThread (this. InfoRequest );
// Create a Socket to listen to the database for tracking requests and sending
This. _ traceServerThread = this. CreateThread (this. TraceServerRequest );

PolicyRequest and InfoRequest methods. For details, refer to the source code of the attachment at the end of the article. Here we mainly discuss the TraceServerRequest method:

SqlConnectionInfo conninfo = new SqlConnectionInfo ();
Conninfo. ServerName = "machine name ";
Conninfo. UserName = "database account ";
Conninfo. Password = "Database Password ";
Conninfo. UseIntegratedSecurity = false;
TraceServer trace = new TraceServer ();
Trace. InitializeAsReader (conninfo, TDF_FILE );
SqlConnectionInfo and TraceServer are referenced by Microsoft. SqlServer. ConnectionInfo. TDF_FILE is the file name of the trail template I mentioned earlier, and its file suffix is tdf.
While (trace. Read ())
{
Var eventData = new EventData ()
{
EventClass = trace ["EventClass"]. ToString (),
TextData = trace ["TextData"]! = Null? Trace ["TextData"]. ToString ():"",
ApplicationName = trace ["ApplicationName"]! = Null? Trace ["ApplicationName"]. ToString ():"",
NTUserName = trace ["NTUserName"]! = Null? Trace ["NTUserName"]. ToString ():"",
LoginName = trace ["LoginName"]! = Null? Trace ["LoginName"]. ToString ():"",
CPU = trace ["CPU"]! = Null? Trace ["CPU"]. ToString ():"",
Reads = trace ["Reads"]! = Null? Trace ["Reads"]. ToString ():"",
Writes = trace ["Writes"]! = Null? Trace ["Writes"]. ToString ():"",
Duration = trace ["Duration"]! = Null? Trace ["Duration"]. ToString ():"",
ClientProcessID = trace ["ClientProcessID"]! = Null? Trace ["ClientProcessID"]. ToString ():"",
SPID = trace ["SPID"]! = Null? Trace ["SPID"]. ToString ():"",
StartTime = trace ["StartTime"]! = Null? Trace ["StartTime"]. ToString ():"",
EndTime = trace ["EndTime"]! = Null? Trace ["EndTime"]. ToString ():"",
};
Var sendData = JsonConvert. SerializeObject (eventData );
If (string. IsNullOrEmpty (sendData ))
Continue;
Lock (_ lock)
{
This. _ queues. Enqueue (sendData );
}
}

Trace. the Read () method is used to obtain trace records. Here, I will not immediately send the trace records to the SL client through Socket. Instead, I team the trace records through a queue and retrieve data from the queue through InfoRequest, send to SL client:

While (true)
{
Lock (_ lock)
{
If (this. _ queues. Count = 0)
{
Thread. Sleep (200 );
Continue;
}
Var sendData = this. _ queues. Dequeue ();
Console. WriteLine (sendData );
Byte [] data = Encoding. GetEncoding ("gb2312"). GetBytes (sendData );
// Remove the disconnected socket
This. _ clientList. RemoveAll (o => o. Connected = false );
Foreach (Socket s in this. _ clientList)
{
If (s. Connected)
{
Try
{
// Send data
S. Send (data );
}
...
 

SL client receives data through Socket asynchronous programming

/// <Summary>
/// When receiving is complete
/// </Summary>
/// <Param name = "e"> </param>
Void ProcessReceive (SocketAsyncEventArgs e)
{
If (e. SocketError = SocketError. Success)
{
Try
{
Gb2312Encoding encoding = new Gb2312Encoding ();

String data = Regex. Replace (encoding. GetString (e. Buffer, 0, e. Buffer. Length), @ "\ 0 ","");

EventData eventData = JsonConvert. DeserializeObject <EventData> (data );
Byte [] bytes = new byte [1, 1024];
E. SetBuffer (bytes, 0, bytes. Length );
This. _ syn. Send (this. GetText, eventData );
// Execute the connection
This. _ socket. ReceiveAsync (this. _ socketArgs );
}
...
// Synchronous context call Method
Private void GetText (object data)
{
If (data = null)
Return;
EventData eventData = (EventData) data;
_ List. Add (eventData );
This. Dispatcher. BeginInvoke (new Action () =>
{
This. Maid. ItemsSource = null;
This. Maid. ItemsSource = _ list;
This. dataGridRecords. UpdateLayout ();
This. Maid. SelectedIndex = _ list. Count-1;
This. Maid. ScrollIntoView (this. Maid. SelectedItem, null );
}));
}

In this way, the Silverlight client monitors SQL Server Profiler in real time.

Two projects are simple:

Run the SqlServerProfier. Host Program first:

Sending monitoring data externally,

Then run the Web end of the host SL client program:

If it is a slow query part, I will mark it in red to determine the slow query statement and optimize the system

 

Summary:

Of course, you can also write logs or insert a specific Log table in the monitoring program, you can use the Web Client (both Silverlight and asp.net mvc can query related tracking records) to monitor Database SQL Execution.

 

Source code: SqlServerProfiler.rar