Best Practices Analyzer Tool for Microsoft SQL Server 2000 is a database management tool developed by the Microsoft SQL Server development team that allows you to detect whether the designed database follows the best practice guidelines for SQL Server operation and management. These guidelines are recognized to help improve database performance and efficiency and make applications easier to maintain.
2. Start using SQL BPA Best Practices Analyzer.
After the installation is completed, there will be a Word document of SQL Server Best Practices Analyzer User Guide. How to use it is clearly explained. The basic steps are as follows:
(1) Log in to SQL BPA
(2) Add analysis /Detected SQL Server instance
You need to enter the SQL Server instance name here. Friendly Name is used to associate with the Best Practice Group created later (just keep it the same as the SQL Server instance name). The default value of Database List is *, which means that it contains all databases of the current SQL Server instance. However, BPA will skip the detection of databases such as 'master', 'tempdb', 'msdb', 'pubs', and 'northwind'.
(3) To manage Best Practice Groups,
you first need to create a Best Practice Group, which actually combines some Rules and associates it with the SQL Server instance entered earlier.
(4) Analyze the SQL Server instance
and move the previously created Best Practice Group to the Best Practice Groups to be Executed list, so that it can be executed according to the previously defined Rules and generate a Report to provide suggestions and guidelines for improvement.
3. I think the Rules included in SQL BPA v1.0
are the key point, because only by understanding these best practice guidelines for SQL Server operation and management can we try to follow these Rules when designing databases and writing T-SQL scripts. , improve the performance and efficiency of SQL Server and applications.
In fact, all the Rules are here (English Version) file:///C:/Program%20Files/Microsoft%20SQL%20Server%20Best%20Practices%20Analyzer/html/RuleInformation.html#_Rule:_Explicit_Index_Creation . Please note that I use SQL BPA is installed in the default path. If you change the installation path, it will not be here.
Here are some Rules that I am interested in:
(1) Database design
rules: Tables without Primary Keys or Unique Constraints
Check the database to ensure that all tables have a Primary Key defined or a column has a Unique Constraint defined.
Rule: User Object Naming (naming of user objects)
detects user objects named with the prefix sp_, xp_, or fn_ to avoid naming conflicts with SQL Server's built-in objects. If SQL Server finds that the stored procedure is prefixed with sp_, it will first query the stored procedure in the master database, which affects performance.
Therefore, the following guidelines must be followed:
Do not use the sp_ prefix to name user-defined stored procedures;
do not use the xp_ prefix to name user-defined extended stored procedures;
do not use the fn_ prefix to name user-defined functions.
In fact, you can name it by using prefixes such as usp_, uxp_, or ufn_, and u means user defined.
(2) T-SQL
Rule: Cursor FOR UPDATE column list
detects the FOR UPDATE clause in stored procedures, functions, views and triggers. When a cursor defines a FOR UPDATE clause, it is recommended to provide explicit column columns. FOR UPDATE is used to define updateable columns in the cursor. If OF column_name is provided, only the listed columns are allowed to be modified. If no list of columns is specified, all columns can be updated unless the READ_ONLY concurrency option is specified. SQL Server can optimize operations based on specified columns.
Rule: Cursor Usage
checks whether cursor updateability is correctly defined in stored procedures, functions, views and triggers. Failure will be reported in the following situations:
when a cursor does not define the FOR UPDATE clause, but is updated through the cursor;
when a cursor defines the FOR UPDATE clause, but is not updated through the cursor.
However, generally we try to avoid using the server-side cursor, because it takes up server memory resources and affects the performance of SQL Server. Nested queries or WHILE statements can be used instead of cursor. Even if you use cursor, you should pay attention to defining some options of cursor, such as FAST_FORWARD.
Rule: Explicit Index Creation
It is recommended to use CLUSTERED or NONCLUSTERED to explicitly create the index.
Rule: INSERT Column List
requires that when using INSERT, the column list must be provided explicitly to improve the maintainability of the code.
Rule: Nested Triggers Configuration
detects triggers that are not triggered due to configuration issues with nested triggers. This one is relatively rare, so I posted it directly.
When 'nested triggers' configuration option is set to 0, any AFTER trigger defined on tables/views updated inside an INSTEAD OF trigger is not fired. This rule:
1) Checks the value of the configuration option and exits if it is not 0.
2) Scans all INSTEAD OF triggers and generates a list of tables/view being target of DML from within a trigger.
3) Checks whether any of the identified DML targets have AFTER triggers defined on them.
4) Reports non-compliance for any such case.
Rule: NOCOUNT Option in Triggers
detects triggers and ensures that SET NOCOUNT ON is written in front of triggers.
SQL Server will send a 'done' message after each statement is executed. This information can cause the application that triggers the trigger to have some unintended consequences. Therefore, it is a good design habit to add SET NOCOUNT ON in front of the trigger.
Of course, it is recommended to add SET NOCOUNT ON in front of stored procedures and functions. In this way, the number of rows affected by the execution of a series of SQL commands will not be transmitted back to the client, reducing network traffic and improving performance.
Rule: NULL Comparisons
detects equality or inequality comparisons involving NULL constants in stored procedures, functions, views and triggers. It is recommended to set ANSI_NULLS to ON and use the IS keyword to compare NULL constants.
Rule: Results in Triggers
checks triggers to ensure that triggers have no data returned to the caller. Therefore, it is not recommended to use the following statements in triggers:
PRINT statement
SELECT (without assignment or INTO clause)
FETCH (without assignment)
Rule: Scoping of Transactions
detects the transaction range in stored procedures and triggers. It is recommended that the start and end of the transaction be within the same T-SQL structure.
Generally speaking, try to narrow the scope of the transaction as much as possible to avoid taking up a lot of resources and affecting the performance of SQL Server.
Rule: SELECT *
detects the use of SELECT * in stored procedures, functions, views and triggers. Although SELECT * is more convenient, it will reduce the maintainability of the program. Changes to the table or view may cause errors or performance changes.
Therefore, it is recommended to explicitly specify the field list after the SELECT statement.
Rule: SET Options
detects the use of the following SET statements in stored procedures and triggers.
It is recommended that the following options be set to ON:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
recommends that the following options be set to OFF:
NUMERIC_ROUNDABOUT
Rule: Temp Table Usage
detects the use of temporary tables in stored procedures and triggers. When creating a temporary table, CREATE INDEX needs to be created, and after use is completed, the temporary table needs to be released.
Because temporary tables will generate a large number of disk IO operations, it is recommended to use TABLE variables to replace the use of temporary tables.
However, due to limitations of concurrent execution and maintenance of statistical information, temporary tables are still recommended when a large amount of data is inserted into temporary tables.
Rule: TOP without ORDER BY
detects the lack of ORDER BY TOP statements in stored procedures, functions, views and triggers. When using the TOP statement, it is recommended to specify sorting conditions. Otherwise, the results produced will be dependent on the SQL execution plan and lead to unexpected behavior.
Rule: Use of Schema Qualified Tables/Views
detects whether the owner is specified when tables and views are referenced in stored procedures, functions, views and triggers. Although when referencing a specific object in SQL Server, you do not need to specify server, database and owner (schema), which means that you do not need the trouble of server_name.database_name.owner_name.***, but SQL Server recommends that it be used in stored procedure, function, When referencing a table or view in a view or trigger, it is best to specify the owner of the table or view.
When SQL Server queries a table/view object without a specified owner, it first queries the default owner, and then dbo. This will result in additional operating costs for the SQL Server product. By specifying the owner, you can improve the performance of SQL Server. (First time I heard this statement)
4. Reference documents and related resource
tools download URL:
download URL: