Chapter 35 -- Using SQL Query Analyzer and SQL Profiler

3 4

Chapter 35

In this chapter, we'll continue our examination of stored procedures, which we began in Chapter 21. This chapter will show you how to analyze stored procedures and other Transact-SQL (T-SQL) statements by using SQL Query Analyzer and SQL Profiler. From this analysis, you'll be able to determine whether the T-SQL statements are efficient. An efficient SQL Server query uses the appropriate sequence of operations and the appropriate indexes to reduce the number of rows processed and to minimize the number of I/O operations.

Using Query Analyzer, you can view the execution plan that the SQL Server query optimizer has chosen for a T-SQL statement. The query optimizer is an internal module that attempts to find the best execution plan for each T-SQL statement. The query optimizer analyzes each T-SQL statement, looks at a number of possible execution plans, and calculates the estimated cost of each plan, in terms of resources needed and processing time. The least expensive plan is chosen. The cost of each plan is determined based on the available statistics that the system collects about the data, which might not be current. Because you might know more about your database and your data than the query optimizer, you might be able to determine a better plan than the query optimizer can. By using the information that Query Analyzer provides, you can determine whether the query optimizer's plan for a statement is efficient, and if you determine it is not, you can then try to optimize the statement by modifying it or by using an SQL hint. In this chapter, you'll learn how to optimize T-SQL statements in addition to learning how to use Query Analyzer.

Using Profiler, you can analyze activity within your SQL Server system in order to determine which SQL statements and stored procedures are using excessive system resources. Once you know which SQL statements are using excessive resources, you can concentrate your tuning efforts on those statements and stored procedures first. In addition to showing you how to use Profiler, this chapter will show you how to most effectively use the information that Profiler provides.



Microsoft SQL Server 2000 Administrator's Companion
Microsoft SQL Server 2000 Administrators Companion
ISBN: B001HC0RPI
EAN: N/A
Year: 2005
Pages: 264

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net