SQL Server performance optimization tool

Example of data and workload

The following example describes how to use the SQL server performance tool. First, create the following table.

  Create     Table  Testtable (nkey1  Int     Identity  , Col2  Char  (  300  )  Default    '  ABC  '  , Ckey1  Char  (  1  )) 

Next, fill in 10,000 rows of test data in this table. You can create a non-clustered index for the data filled in column nkey1. You can create a clustered index for the data in column ckey1. The data in col2 is only filled with content, increasing each row by 300 bytes.

  Declare     @ Counter    Int     Set     @ Counter     =     1     While  (  @ Counter     <=     2000 )  Begin     Insert  Testtable (ckey1)  Values  (  '  A  '  )  Insert  Testtable (ckey1)  Values  (  '  B '  )  Insert  Testtable (ckey1)  Values  (  '  C  '  )  Insert  Testtable (ckey1)  Values  (  '  D  '  ) Insert  Testtable (ckey1)  Values  (  '  E  '  )  Set     @ Counter     =     @ Counter     +    1     End    

The Database Server performs the following two queries:

  Select  Ckey1, col2  From  Testtable  Where  Ckey1  =     '  A '     Select  Nkey1, col2  From  Testtable  Where  Nkey1  =     5000  Profiler 

SQL Server Profiler records detailed information about activities on the database server. You can configure profiler to monitor a large amount of configurable performance information and record one or more users performing queries in SQL Server. The performance information that can be recorded in profiler is: i/O statistics, CPU statistics, lock requests, T-SQL and RPC statistics, index and table scans, warnings and errors, database object creation/removal, connection/disconnection stored Procedure operations, cursor operations, and so on. For all the information that SQL profiler can record, search for the string "profiler" in SQL Server books online ".

Load profiler information to the. TRC file for index tuning wizard

Profiler and index tuning wizard are powerful tools to help database administrators create appropriate indexes in tables. Profiler records the resources consumed by the query in the. TRC file .. The TRC file can be read by the index tuning wizard. The index tuning wizard considers the. TRC information and database table at the same time, and then recommends what indexes should be created. Index tuning wizard allows administrators to choose whether to automatically create the appropriate index for the database and schedule the index so that it is automatically created later or a T-SQL script that can be viewed and executed later.

To analyze the query load, follow these steps:

Set profiler

Select Tools/SQL Server Profiler from the SQL Server Enterprise Manager menu to start profiler.

Press Ctrl + n to create a profiler trail.

Enter the trail name.

Select the capture to file: check box, and then select the. TRC file in which the profiler information is to be output.

Click OK.

Running workload

Start query analyzer (select Tools/SQL Server Query analyzer from the SQL Server Enterprise Manager menu or select start \Program\ Microsoft SQL Server 7.0 \ QUERY analyzer ).

Connect to SQL Server and set the current database in which the table will be created.

Type or copy the following queries and paste them in the query window of query Analyzer:

  Select  Ckey1, col2  From  Testtable  Where  Ckey1  =     ' A  '     Select  Nkey1, col2  From  Testtable  Where  Nkey1  =     5000    

 

Press Ctrl + e to execute the two queries. Stop profiler

Click the square in red to stop the profiler trail. Load. TRC to index tuning wizard

Select tools \ index tuning wizardsu from the profiler menu to start index tuning wizard. Click Next.

Select the database to be analyzed. Click Next.

Keep the I have a saved workload File Option Button selected, and then click Next.

Select the my workload file option, find the. TRC file created with Profiler, and click Next.

In the select tables to tune dialog box, select the table to be analyzed and click Next.

Index tuning Wizard will indicate the index to be created in the index recommendations dialog box. Click Next.

This wizard allows you to choose whether to create an index immediately, whether to schedule an index creation task that will be automatically executed later or to create a T-SQL script with the create Index Command. Select the required option and click Next.

Click Finish.

Index tuning wizard is the T-SQL generated for the sample database and workload.

  /*  Created by: Index tuning wizard */     /*  Date: 9/7/98  */     /*  Time: 6: 42: 00 pm  */     /*  Server: henrylnt2  */     /*  Database: Test  */    /*  Workload file: e: \ MSSQL7 \ binn \ profiler_load. SQL  */     Use     [  Test  ]     Begin     Transaction     Create    Clustered     Index     [  Testtable2  ]     On     [  DBO  ]  .  [  Testtable  ]  ( [  Ckey1  ]  )  If  (  @ Error     <>     0  )  Rollback     Transaction     Create    Clustered     Index     [  Testtable2  ]     On     [  DBO  ]  .  [  Testtable  ] (  [  Ckey1  ]  )  If  (  @ Error     <>     0  )  Rollback     Transaction    Commit     Transaction    

The recommended index for index tuning wizard for example tables and data is our expected index. Ckey1 has only five unique values, and each value has 2,000 rows. Assume that a sample query (select ckey1, col2 from testtable where ckey1 = 'A') requires that the table be retrieved based on a value in ckey1, it makes sense to create a clustered index in the ckey1 column. The second query (select nkey1, col2 from testtable where nkey1 = 5000) extracts a row based on the value of column nkey1. Nkey1 is unique and has 10,000 rows. Therefore, creating a non-clustered index in this column makes sense.

The profiler/index tuning wizard combination is very powerful in the actual database server environment involving many tables and many queries. When the database is performing a typical query, use profiler to record the. TRC file. Then load the. TRC file to index tuning Wizard to determine whether the correct index is created. Automatically generate and schedule an index creation job based on the prompts in index tuning Wizard to run during off-peak hours. Run profiler/index tuning wizard regularly (for example, weekly) to check whether there are major changes to the query executed on the database server. If yes, different indexes may be required. Regular use of profiler/index tuning wizard helps database administrators query workload changes and database size increases over time while keeping SQL server running in the optimal state.

Load profiler information to the SQL Server table for analysis

Another option provided by profiler is to record information in the SQL Server table. After the query is complete, you can query the entire table to determine whether some queries consume too much resources.

Record profiler information in the SQL Server table

Select Tools/SQL Server Profiler from the SQL Server Enterprise Manager menu to start profiler.

Press Ctrl + n to create a profiler trail.

Enter the trail name.

Click the capture to table check box and select the SQL Server table in which the profiler information is to be output.

Click OK.

Click the square in red to stop the profiler trail.

Use query analyzer to analyze the information recorded in profiler

After the information is recorded in the SQL Server table, you can use query analyzer to calculate which queries consume the most resources in the system. In this way, the database administrator can focus on improving the query that requires the most help. For example, you can use the following query to analyze the data recorded from profiler to the SQL Server table. This query retrieves the first three items that consume the most CPU resources on the database server. Return read and write I/O information and query duration (in milliseconds ). If you use profiler to record a large amount of information, it makes sense to create an index in this table to speed up analysis and query. For example, if the CPU is about to become an important criterion for analyzing this table, it is a good idea to create a non-clustered index in the CPU column.

Select top 3 textdata, CPU, reads, writes, duration from profiler_out_table order by CPU DESC

For more information, search for strings "viewing and analyzing traces", "Troubleshooting SQL Server Profiler", "Tips for using SQL Server", "common SQL Server Profiler scenarios"," starting SQL Server Profiler "and" Monitoring with SQL Server Profiler ".

Query Analyzer

I/O statistics

The connections Options dialog box General tab of query analyzer provides a show stats I/O option. Select this check box to obtain information about the amount of I/O consumed by queries being executed in query analyzer.

For example, when the show stats I/O option is selected, query "select ckey1, col2 from testtable where ckey1 = 'A'" except for the returned result set, the following I/O information is returned:

Table 'testtable'. Scan count 1, logical reads 400, physical reads 382, read-ahead reads 400.

Similarly, when the show stats I/O option is selected, the following I/O information is returned in addition to the returned result set when "select nkey1, col2 from testtable where nkey1 = 5000" is queried:

Table 'testtable'. Scan count 1, logical reads 400, physical reads 282, read-ahead reads 400.

Using I/O statistics is an effective way to monitor the query adjustment effect. For example, create the index tuning wizard in the example table and run the query again.

In the query "select ckey1, col2 from testtable where ckey1 = 'A'", the performance of clustered indexes is improved as follows. If the Query Needs to extract 20% of the table, the performance improvement should be reasonable:

Table 'testtable'. Scan count 1, logical reads 91, physical reads 5, read-ahead reads 32.

In the "select nkey1, col2 from testtable where nkey1 = 5000" query, creating a non-clustered index has a significant impact on the query performance. Assuming that this query only needs to extract one row from a table of 10,000 rows, it is reasonable to use non-clustered indexes to Improve the Performance:

Table 'testtable'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0.

Showplan

by displaying detailed information about the tasks being executed by query optimizer, use showplan to focus on problematic SQL queries. SQL Server 7.0 provides the showplan text and graphic versions. You can run SQL query with Ctrl + L to display the output of graphical showplan in the Results pane of query analyzer. The icon indicates the operations performed by query optimizer if the query has been executed. The arrow indicates the direction of the queried data stream. Place the cursor over the operation icon to display detailed information about each operation. Run the set showplan_all on command to display equivalent information in the text-based showplan. To skip the display of detailed information about an operation to reduce the output of the text-based showplan for the query optimizer operation, run the set showplan_text on command.