Tuning SQL Statements on Microsoft SQL Server 2000 – Kevin Kline, Director of Technology, SQL Server Solutions Group and Claudia Fernandez, Product Manager, Quest Software, Inc … NTRODUCTION This paper covers the basic techniques used to tune SELECT statements on Microsoft’s SQL Server 2000 relational database management system. We discuss the techniques available using Microsoft’s graphical user interfaces provided in Microsoft SQL Enterprise Manager or Microsoft SQL Query Analyzer, as well as providing a brief overview …

This paper covers the basic techniques used to tune SELECT statements on Microsoft’s SQL Server 2000 relational database management system. We discuss the techniques available using Microsoft’s graphical user interfaces provided in Microsoft SQL Enterprise Manager or Microsoft SQL Query Analyzer, as well as providing a brief overview of Quest Software’s query tuning tools. In addition to tuning methods, we’ll show you several best practices you can apply to your SQL statements to improve performance. [All examples and syntax are verified for Microsoft SQL Server 2000.] After reading this paper, you should have a basic understanding of query tuning tools and techniques available with the Microsoft tool kit. We will cover a variety of querying techniques that improve performance and speed data read operations. SQL Server provides you with capabilities to benchmark transactions by sampling I/O activity and elapsed execution time using certain SET and DBCC commands. In addition, some DBCC commands may be used to obtain a very detailed explanation of any index statistic, estimate the cost of every possible execution plan, and boost performance. The SET and DBCC commands are fully detailed in the Quest white paper entitled “Analyzing and Optimizing T-SQL Query Performance on Microsoft SQL Server using SET and DBCC,” the first white paper in a four part series on performance tuning….. Tuning SQL Statements on Microsoft SQL Server 2000 – Kevin Kline, Director of Technology, SQL Server Solutions Group and Claudia Fernandez, Product Manager, Quest Software, Inc. 6 Additionally, we execute a system stored procedure to obtain table size statistics for our analysis: sp_spaceused employees Results: name rows reserved data index_size unused ———- —- ——— ——- ———– ——- Employees 2977 2008 KB 1504 KB 448 KB 56 KB What can we tell by looking at this information? • The query did not have to scan the whole table. The number of data in the table is more than 1.5 megabytes, yet it took only 53 logical I/O operations to obtain the result. It indicates that the query has found an index that could be used to compute the result, and scanning the index took fewer I/O than it would take to scan all data pages. • Index pages were mostly found in data cache since the physical reads value is zero. This is because we executed the query shortly after other queries on employees and the table and its index were already cached. Your mileage may vary. • Microsoft has reported no read-ahead activity. In this case, data and index pages were already cached. For a table scan on a large table read-ahead would probably kick in and cache necessary pages before your query requested them. Read-ahead turns on automatically when SQL Server determines that your transaction is reading database pages sequentially and believes that it can predict which pages you’ll need next. A separate SQL Server connection virtually runs ahead of your process and caches data pages for it. [Configuration and tuning of read-ahead parameters is beyond the scope of this paper.] In this example, the query was executed as efficiently as possible. No further tuning is required. SET STATISTICS TIME Elapsed time of a transaction is a volatile measurement, since it depends on activity of other users on the server. However, it provides some real measurement, compared to the number of data pages that don’t mean anything to your users. They are concerned about seconds and minutes they spend waiting for a query to come back, not about data caches and read-ahead efficiency. The SET STATISTICS TIME ON command reports the actual elapsed time and CPU utilization for every query that follows. Executing SET STATISTICS TIME OFF suppresses the option.

Download Tuning SQL Statements on Microsoft SQL Server 2000.pdf