How to optimize SQL Performance? -Use Profile

After installing SQL Server 2008, we can see the performance tools: database performance engine consultant and SQL Server Profiler. This tool is also applicable to ms SQL 2000 and ms SQL 2005.

 

SQL Profiler is a graphical user interface tool for SQL Trace. It allows you to monitor SQL Server Database Engine or Analysis Services instances. With SQL Profiler, You can interactively Capture Database activities and save data about database events to a file or table. You can replay and analyze the stored data in the future.

Typical reasons for using SQL Profiler:
● Monitors the performance of Database Engine or Analysis Server instances.
● Analyze and simplify the performance of slow query execution.
● Save the Showplan result to perform query and analysis.
● Determine the cause of the deadlock
● Debug TSQL statements and stored procedures.
● Emphasize and benchmark tests are performed through replay tracking.
● Replay the traces of one or more users.
● Review and review activities on SQL Server instances.
● Aggregates the tracking results to combine and analyze similar event classes.

Start SQL Profiler

One is to Start from the Start | All Programs | Microsoft SQL Server 2005 | Performance Tools | SQL Server Profiler menu options.

Select Tools | SQL Server Profiler from the menu of SQL Server Management Studio. You can also start SQL Profiler from the Tools | SQL Server Profiler menu options of Database Engine Tuning Advisor.

The trail Properties dialog box has two tabs: General and Events Selection. General tab. Based on the actual situation, we specify the template and the columns related to the trace template set in the trace attributes.

Predefined template:

 

Model board name	Roles and events of templates
Standard (default)	Capture running stored procedures and TSQL batch processing. Purpose: monitor Common Database Server activities. Class: Audit Login, Audit Logout, ExistingConnection, RPC: Completed, SQL: BatchCompleted, SQL: BatchStarting
SP_Counts	Capture the execution behaviors of stored procedures based on time. Class: SP: Starting
TSQL	Capture the TSQL statements submitted by the client to SQL Server and the time used. Purpose: debug the client application. Class: Audit Login, Audit Logout, ExistingConnection, RPC: Starting, SQL: BatchStarting
TSQL_Duration	Capture the TSQL statements and execution time (in milliseconds) submitted by the client to SQL Server ). Group by cycle. Purpose: Determine the slow query. Class: RPC: Completed, SQL: BatchCompleted
TSQL_Grouped	Capture the TSQL Statement submitted by the client to SQL Server and the time used, and group the statements by the user or client. Purpose: query from a specific client or user. Class: Audit Login, Audit Logout, ExistingConnection, RPC: Starting, SQL: BatchStarting
TSQL_Replay	If you want to replay a trail, capture the information of the required TSQL statement. Purpose: fine-tuning performance and benchmark testing. Class: CursorClose, CursorExecute, CursorOpen, CursorPrepare, CursorUnprepare, Audit Login, Audit Logout, ExistingConnection, RPC Output Parameter, RPC: Completed, RPC: Starting, Exec Prepared SQL, Prepare SQL, SQL: BatchCompleted, SQL: BatchStarting
TSQL_SPs	Capture information about the execution of stored procedures. Purpose: analyze the component steps of the stored procedure. Class: Audit Login, Audit Logout, ExistingConnection, RPC: Starting, SP: Completed, SP: Starting, SP: StmtStaring, SQL: BatchStarting
Tuning	Capture information about stored procedures and TSQL batch execution. Purpose: generate the Trace Output of the Database Engine Tuning Advisor for adjusting the Database. Class: RPC: Completed, SP: StmtCompleted, SQL: BatchCompleted

 

Tracking activities

After the profile is started, you can view the performance record in the profile for any actions on the monitored database objects.

SQL Profiler allows you to search for and display query plan information in a trail. You can add Showplan event classes to the trail, or even save these Showplan events to an XML file.