Simple use of SQL Server Profiler

SQL Server Profiler can detect statements executed on the data, especially if some projects do not directly use SQL statements, directly using the ORM framework of the system to process the database project, when debugging SQL statements, it is very helpful.

Previously wrote the article "SQL Server Profiler Tracking database deadlock" Using SQL Server Profiler to track deadlocks, and then simply write the features commonly used in the SQL Server Profiler usage process.

Use SQL Server version R2 here

1. When installing the SQL Server database, ensure that SQL Server Profiler is installed, and all installations will be installed automatically by default.

2. Open SQL Server Management Studio, in the menu, locate the "Tools" menu and locate the SQL Server Profiler menu item as shown in.

3. Select "Event Selection" and tick the frequently used items as shown in.

4. Select "Show all Columns", then in the list, tick the "DatabaseName" item.

5. Set the filter, where the settings just filter the database name, because if there is more than one database in a DB instance, then the database name is not filtered, then the execution statements on all the databases can be monitored.

You can also set duration to filter SQL statements for different execution times, such as SQL statements that can be filtered for longer execution times, and select to optimize SQL statements.

What if the same database, multi-person access, only want to detect the statement of their own actions? You can create a database account of your own login (you can delete this user after detection), and then, in the LoginName filter, filter your logged-in database account.

After setting, click "Run" to start monitoring, the monitoring screen is as follows.

Events are expressed in different characteristics (called data columns). The data column shows the non-pass characteristics of an event, such as the class of the event, the SQL statement used for the event, the resource cost of the event, and the source of the event.

Data columns	Description
EventClass (event Class)	Event type, such as sql:statementcompleted
TextData	The SQL statement used by the event, such as SELECT * FROM person
Cpu	The CPU overhead of the event (in MS), such as for a SELECT statement, cpu=100 indicates that the statement performs a 100ms
Reads	The number of logical read operations performed for an event. For example, for a SELECT statement, reads=800 indicates that the statement requires 800 logical read operations
Writes	The number of logical write operations performed for an event
Duration	Execution time of the event (MS)
SPID	The SQL Server process identifier used for the event
StartTime	Time when the event started

These are commonly used data columns, and there are some less commonly used data columns:

• BinaryData (binary data)
• Integerdata (integer data)
• EventSubClass (Event sub-Class)
• DatabaseID (database identifier)
• ObjectID (object identifier)
• INDEXID (index Identifier)
• TransactionID (transaction identifier)
• Error (Errors)
• EndTime (end time)

For more detailed information, see: https://www.cnblogs.com/kissdodog/p/3398523.html

Simple use of SQL Server Profiler