Queries Optimization and Performance

Overview

The goal of queries optimization is to minimize the response time for each query and to maximize the throughput of the entire database server. This can be done by reducing network traffic, disk I/O, and CPU time, although other system-level performance issues, such as memory, hardware etc. I have mentioned the following points to improve SQL server performance and query Optimization.

Query Tuning

When a query or update takes longer than expected time, use the following points.

1. The performance of a database query can be determined by using the SET statement to enable the SHOWPLAN, STATISTICS IO, STATISTICS TIME, and STATISTICS PROFILE options.

• SHOWPLAN describes the method chosen by the SQL Server query optimizer to retrieve data.
• STATISTICS IO reports information about the number of scans, logical reads (pages accessed in cache), and physical reads (number of times the disk was accessed) for each table referenced in the statement. For more information, see SET STATISTICS IO.
• STATISTICS TIME displays the amount of time (in milliseconds) required to parse, compile, and execute a query. For more information, see SET STATISTICS TIME.
• STATISTICS PROFILE displays a result set after each executed query representing a profile of the execution of the query. For more information, see SET STATISTICS PROFILE.

2. In SQL Query Analyzer, you can SQL Profiler or graphical execution plan option to view a graphical representation of how SQL Server retrieves data.

3. Is the performance problem related with slow network performance? Windows NT Performance Monitor can be used to monitor the performance of SQL Server and non-SQL Server related components.

4. Check for Up-to-date statistics. SQL Server automatically creates and updates query statistics on indexed columns. Statistics can be updated on non-indexed columns either manually, using SQL Query Analyzer or the UPDATE STATISTICS statement, or automatically, if the auto updates statistics database option is set to true. If no UPDATE operations have taken place, then the query statistics are still up-to-date. If statistics are not set to update automatically, then set them.

The query optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan. DBCC SHOW_STATISTICS

5. Check for data or index hot spots, using disk striping.

Disk striping

Windows NT and Windows NT Advanced Server disk striping (RAID Level 0) creates a disk file system called a stripe set by dividing data into blocks and spreading them in a fixed order across all disks in an array. By adding data to all partitions in the set at the same rate, disk striping offers the best performance of all Windows NT disk management strategies.

Application Design

Application design plays an important role in performance. The client determines the type of queries, when they are submitted, and how the results are processed. This decides the type and duration of locks, amount of I/O, and processing (CPU) load on the server.

Followings are the guidelines for Client Server application.

1. Eliminate excessive network traffic: Network roundtrips describe the conversational traffic sent between the client application and SQL Server. By using stored procedures, we can minimize network roundtrips. If we have no need of message like the number of rows affected then Use SET NOCOUNT ON to remove these messages and it will reduce the network traffic.

2. Use small result sets

3. Use Application design techniques: use TOP, PERCENT, or SET ROWCOUNT statements to limit the number of rows returned by a query.

4. Allow cancellation of a query in progress: An application should never force the user to restart the client computer to cancel a query. By doing this we can lead to irresolvable performance problems.

5. Always implement a query or lock time-out: Use the ODBC SQLSetStmtOption function.

6. Do not inter mix decision support and online transaction processing (OLTP) queries.

7. Do not use cursors if it is not necessary.

8. Keep transactions as short as possible

9. Use prepared execution to execute a parameterized SQL statement.

10. Design the application to avoid deadlocks.

Index Tuning

To achieve performance first step is Index designing. We can create, drop and alter indexes without affecting database schema.

Recommendations for creating indexes:

1. Create clustered index only on Integer column.

2. Create non-clustered indexes on all columns frequently used in queries.

3. Write queries that update as many rows as possible in a single statement, rather than using multiple queries to update the same rows.

4. Use the Index Tuning Wizard to analyze queries.

5. Examine column uniqueness.

6. Examine data distribution in indexed columns.

Creating a Partitioned View

A partitioned view joins horizontally partitioned tables across one or more servers, to show the data as if from one table. There is two kind of Partitioned view.

1. Local partitioned views: all participating tables and the view reside on the same instance of SQL Server.

2. Distributed partitioned views: at least one of the participating tables resides on a different (remote) server. And can be used to implement a federation of database servers. . A federation is a group of servers administered independently, but which cooperate to share the processing load.

To implementing a partitioned view, we must first partition a table horizontally. The ranges are based on the data values in a partitioning column. The range of values in each member table is enforced by a CHECK constraint on the partitioning column. For example, you are partitioning a Customer table into three tables. The CHECK constraint for these tables is:

-- On Server1:
CREATE TABLE Customers_1
  (CustomerID   INTEGER PRIMARY KEY
     CHECK (CustomerID BETWEEN 1 AND 32999), ... -- Additional column definitions)
-- On Server2:
CREATE TABLE Customers_2
  (CustomerID   INTEGER PRIMARY KEY
     CHECK (CustomerID BETWEEN 33000 AND 65999),... -- Additional column definitions)
 -- On Server3:
CREATE TABLE Customers_3
  (CustomerID   INTEGER PRIMARY KEY
                CHECK (CustomerID BETWEEN 66000 AND 99999), ... -- Additional column definitions)
After creating the member tables, you define a distributed partitioned view on each member server, with each view having the same name. Create this distributed partitioned view: 

CREATE VIEW Customers AS
    SELECT * FROM CompanyDatabase.TableOwner.Customers_1
    UNION ALL
    SELECT * FROM Server2.CompanyDatabase.TableOwner.Customers_2
    UNION ALL
    SELECT * FROM Server3.CompanyDatabase.TableOwner.Customers_3
Perform the same steps on Server2 and Server3. 

We can build the distributed partitioned views by:

1. Adding linked server definitions on each member server.

2. Setting the lazy schema validation option, using sp_serveroption, for each linked server definition used in distributed partitioned views.

Updatable Partitioned Views

A view is considered an updatable partitioned view if:

• The view is a set of SELECT statements whose individual result sets are combined into one using the UNION ALL statement. Each individual SELECT statement references one SQL Server base table. The view will not be updatable if a trigger or cascading update or delete is defined on one or more member tables.

Table Rules

Tables used in the FROM clause in each SELECT statement in the view called Member Table. Each member table must follow below rules.

• Member tables cannot be referenced more than once in the view.

• Member tables cannot have indexes created on any computed columns.

• Member tables must have all PRIMARY KEY constraints on an identical number of columns.

• Member tables must have the same ANSI padding setting.

Column Rules

The columns must follow these rules.

• All columns in each member table must be included in the select list.

• Columns cannot be referenced more than once in the select list.

• The columns must be in the same ordinal position in the select list

• The columns in the select list of each SELECT statement must be of the same type.

Optimizing Server Performance

SQL server automatically tunes many of the server configuration options. Although these configuration options can be modified by the system administrator, it is generally recommended that these options be left at their default values based on run-time conditions.

We can configure following to optimize server performance if it is necessary:

1. SQL Server Memory

2. I/O Subsystem

3. Microsoft Windows Options