Command prompt utility SQL Server Profiler Database Engine Tuning Advisor

Command prompt utility

SQLCMD-S * * * Connect server. Enter the Transact-SQL statement and execute.

How to use SQL Server Profiler

We click Start--Program--microsoft SQL Server 2005--Performance tool--sql Server Profiler

Such as

The following interface will then appear

We click on the file---"New trace (N) ...", which is a multi-window multi-tool, we can create a different tracking window at the same time, or it can be a different database

Then look

Here we enter information such as the server name, user name and password of our tracking database. Click Connect to go to the next screen

In the left, you can make a basic setup, use the template selection, and save the file selection. We generally use the default is OK, do not move the above things, the right side of the graph is the event selection, that is, we have to track the event has that, here can be one of the choices, basically there are some events on SQL, including you with SQL Server Management The process that studio operates the database can be traced to. Specific events and instructions you can see for yourself.

Just click Show all events to make the selection of all events.

We can also filter the statistics fields, click any of the column headings to view a description of the column as

We indicate from the above:

TextDate relies on the text value of the event class captured in the trace;

ApplicationName the name of the client application that created the SQL Server connection. This column is populated by the value passed by the application, not by the program name that is displayed;

Ntusername the Windows user name.

LoginName User's login name (SQL Server secure logon or Windows logon credentials in the format domain/user name)

CPU time (in milliseconds) that is used by the CPUs event.

Reads the number of times the logical disk was read by the server on behalf of the event.

Writes the number of times that the server writes physical disks on behalf of events.

The time that the Duration event takes. Although the server calculates the duration in microseconds, SQL Server Profiler can display the value in milliseconds, depending on the settings in the Tools > Options dialog box

ClientProcessID the process ID of the application calling SQL Server.

SPID SQL Server assigns the server process ID to the associated process for the client.

The start time of the Strattime event (if available).

The time at which the EndTime event ended. This column is not populated for event classes that indicate the start of an event, such as Sql:batchstarting or sp:starting.

BinaryData relies on the binary values of the event classes captured in the trace.

Then we click to run, of course, if you are interested, you can also rearrange and filter columns, as long as you click the appropriate button below according to the prompt operation will be OK, we are here on the default

With the above figure we can clearly track each step is a process,

Now if you do not understand the version of the SQL statement, you can refer to the above, and is always, you operate in SQL here will appear the corresponding SQL statement, but also a learning and improve the good tool

Not only that, we can also analyze the data, query, track can pause, start and stop operations, you can start multiple traces at the same time, tracking different databases and tables

If you are working with Database Engine Tuning Advisor for SQL, it's better to analyze your SQL statement performance if, and will tell you how to modify it would be better, let's have a look.

How Database Engine Tuning Advisor is used

With this tracking record, how do we use Database Engine Tuning Advisor to analyze and optimize it? Of course, the first step is to export the tracked records to a file. TRC type of file. Click File-Save As

If we save the file as 123.trc

Now let's open Database Engine Tuning Advisor We click Start--Program--microsoft SQL Server 2005--Performance Tool--Database Engine Tuning Advisor

Such as

Same as above, we need to enter the login information of the database first.

Click Connect to enter the following interface

We can look at the interface, generally do not have to choose as long as the default is OK, need to configure the above I have comments. Here you must remember to select the database and table for the workload, which means that the database to be analyzed is not successfully parsed.

You can click Start now.

Here we can see the analysis after the success of the analysis report, this does not count, we click on the above

Recommendations Tab

Here are the tables you need to optimize, how to build indexes and views to improve performance more effectively, more fun is even need to optimize the SQL statements are generated,

We'll just copy and execute.

To save a deadlock graph:

To Save the Deadlock graph event using sql Server 2005 Event Tracker

The L Deadlock graph event is saved as an XML file.

Save Deadlock Graph Event

1 . On the File menu, click New Trace to connect to the instance of SQL server.

The Trace Properties dialog box appears.

 

Note: If you select the

" start tracking immediately after connection , the < Span style= "FONT-FAMILY:CALIBRI;" > " Trace properties " dialog box, instead of starting the trace directly. To turn off this setting, in tools " menu, click option ", then clear start tracking immediately after connection is established < Span style= "FONT-FAMILY:CALIBRI;" > " check box .

2. In the Trace Properties dialog box, in the Trace name box, type a name for the trace.

3. In the use template list, select a tracking template for this trace, or blank if you do not want to use the template .

4

, click the Event Selection tab .

In the event data column, expand the Locks event category, and then select the Deadlock Graph check box. If the Locks event category is not displayed, check Show all events to display the category.

The event Extraction Settings tab is added to the Trace Properties dialog box.

Note: If you use a template, the Event Extraction Settings tab will not appear , and you will need to cancel and select the Deadlock Graph event again to see the " Event Extraction Settings tab

Deadlock Graph

Events are saved as XML files

5. On The event extraction Settings tab, click Save Deadlock XML event separately .

6. In the Save as dialog box, enter the name of the file where you want to store the Deadlock graph event.

7. Click All deadlock XML batches in single file to Save all Deadlock graph events to a single XML file, or click each deadlock XML batch in a different file Create a new XML file for each Deadlock graph event .

After you save the deadlock file, you can open it in SQL Server Management Studio to view the file

Note: Follow the steps above to manually save the tracking record for the Deadlock Graph event after the test is finished (for flexibility, there is no option to configure save files in step 3 )

Command prompt utility SQL Server Profiler Database Engine Tuning Advisor