Introduction

This chapter investigates the tools that are provided with the SQL Server product to allow the database developer or administrator to inspect, optimize, and tune an installation to provide for the best implementation.

The Enterprise Manager Console (EMC) is the primary graphical tool that performs many of the administrative tasks needed in SQL Server. With this tool, databases can be created as well as objects contained in them, such as views and tables. The EMC provides for administration over the SQL Server security environment, server and database configuration, and statistical analysis and management, as well as providing a complete set of administrative tools. Essentially, it is the only tool needed for day-to-day operations of a SQL Server environment.

When you go beyond the day-to-day tasks, however, and require more in-depth analysis of the database environment, you must reach beyond the EMC and walk into the realm of the other tools supplied for the purpose of investigation and problem determination. Over time, the size of the database will need to be adjusted to accommodate new data or data removal. The configuration of the server in any given installation may vary greatly. The applications that a server is intended to handle will direct the configuration. In troubleshooting and tweaking the performance of any installation, you will need to know how the server is being used.

Troubleshooting, performance tuning, and resource optimization require in-depth knowledge of how SQL Server operates, as well as knowledge of the applications to which the database server is being applied. You have seen numerous tools and options that can be used to assist in this process.

The first problem you will face on the exam will be which tool to use, given a set of circumstances. Second, you will need to present a course of action for monitoring and troubleshooting. Third, you will need to read and diagnose the output and then select an appropriate solution. A person who implements databases needs to be comfortable in all these areas, and you will find a few of each type of question on the exam.

Troubleshooting at the server level will require the use of the operating systems' tools. At the database server and database level you should be using the SQL Profiler. Digging deeper and into more granular inspection will require the use of the Query Analyzer and some knowledge of commands and options to provide a look at individual tables and processes.

Various tools, utilities, and information sources are built into SQL Server to help you optimize SQL Server operations. These mechanisms can be used to diagnose, maintain, and optimize all operations. To obtain immediate information about the server, you can observe the current activity using the Enterprise Manager or stored procedures. To observe information from a historical perspective, you can view the SQL Server logs from the Enterprise Manager.