Microsoft SQL Server Management Studio is the primary graphical interface used to interact with the database engine. With this interface, you can create databases as well as the objects they contain, such as views and tables.
Management Studio provides for administration over the SQL Server security environment, server and database configuration, and statistical analysis and management, and it provides a complete set of administrative tools. Essentially, it is the only tool you need for almost all day-to-day operations in a SQL Server environment.
When you go beyond the day-to-day tasks and require more in-depth analysis, you must reach beyond Management Studio and use the other tools supplied for the purpose of investigation and problem determination. From within the Management Studio, you can access information on the current activity within the server, but often you need other tools to observe the database performance in an ongoing manner. Activity Monitor offers the first level of database-specific information.
Over time, the size of a database needs 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 direct the configuration. In troubleshooting and tweaking the performance of any installation, you need to know how the server is being used. Before you can make any alterations to improve performance, you need to gather statistical information on how the server is performing within the production environment.
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. As described in the earlier chapters in this book, you can use numerous tools and options to assist in this process.
One problem you will face on the 70-431 exam is knowing which tool to use, given a set of circumstances. Second, you need to present a course of action for monitoring and troubleshooting. Third, you 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.
This chapter focuses on gathering information and the types of information each of the tools provides. Chapter 8, "Troubleshooting and Optimizing SQL Server," takes this information further, showing you how to improve the server and provide the best of all possible operating environments.
The tools discussed in this chapter help gather information that will aid in determining whether your SQL Server system is performing optimally. You need to take data at regular intervals. You should even be collecting data when there are no particular problems or issues. Regularly collecting information will help you establish a server performance baseline. A baseline allows you to compare results of measurements with results taken earlier, and it helps you determine peak and off-peak hours of operation for scheduling of maintenance, provide production-query or batch-command response times, and determine the database backup and restore completion times.