As with SQL Server 2000, SQL Server 2005 is designed to balance the workload dynamically and to self-tune configuration settings. For example, SQL Server can increase or decrease memory usage dynamically based on overall system memory requirements. SQL Server also manages memory efficiently, especially when it comes to queries and user connections—and memory is just one of dozens of areas in which the configuration is automatically adjusted.
Although the SQL Server self-tuning feature works well, there are times when you will need to configure SQL Server settings manually. For example, if you are running a large database with special constraints and the database is not working the way you expect it to perform, you may want to customize the configuration. You may also need to modify configuration settings for SQL Server accounts, authentication, and auditing. Key tools you will use to configure and tune SQL Server include the following:
System Catalog Queries. Provide a direct way to determine database configuration characteristics and their related settings.
Stored Procedures. Lets you view and manage configuration settings through stored procedures, such as sp_configure and sp_dboption. Note that you can change some options of sp_configure only when Show Advanced Options is set to 1, as in the following example:
exec sp_configure "show advanced options", 1
SQL Server Management Studio. Provides an easy-to-use interface that updates the database and registry settings for you.
SQLServr.exe. Starts SQL Server from the command line, and you can use it to set configuration parameters at startup.
In this chapter, we will examine the structures available for configuring and tuning SQL Server. We start with a look at the SQL Server 2005 system catalog, and then continue with a discussion of catalog queries and stored procedures. This discussion provides the essential background for understanding how to configure and tune SQL Server 2005. The next chapter, Chapter 5, provides details about using SQL Server Management Studio and SQLServr.exe.
SQL Server 2005 uses an object-based approach to representing servers, databases, and all of their configuration characteristics and data contents. At the heart of this object-based structure is the system catalog, which describes the objects in a particular instance of SQL Server along with their attributes. For example, attributes of a database describe:
The number and names of the tables and views.
The number and names of columns in a table or view.
The column data type, scale, and precision.
The triggers and constraints that are defined on a table.
The indexes and keys that are defined for a table.
The statistics used by query optimizer for generating query plans.
In queries, you can access this and other system catalog information using:
Catalog views. Provide access to metadata stored in a database, which includes database attributes and their values. Catalog views can be used to access all user-available metadata, except for replication, backup, database maintenance plan, and SQL Agent metadata.
Compatibility views. Provide access to many of the system tables from earlier releases of SQL Server using SQL Server 2005 views. These views are meant for backward compatibility only, and they expose the same metadata that was available in the SQL Server 2000. They do not expose metadata for new SQL Server 2005 features, such as database partitioning and mirroring.
Information Schema views. Provide access to a subset of metadata stored in a database, which includes database attributes and their values. Information Schema views are based on catalog view definitions in the SQL-92 standard and do not contain metadata specific to SQL Server 2005. Applications that use these views are portable between heterogeneous SQL-92-compliant database systems.
ODBC catalog functions. Provide an interface that open database connectivity (ODBC) drivers can use to return result sets containing system catalog information. The result sets present catalog information in a way that is independent of the structure of the underlying catalog tables.
OLE DB schema rowsets. Provide an IDBSchemaRowset interface that OLE DB providers can use to access system catalog information. The rowsets present catalog information independently from the structure of the underlying catalog tables.
System stored procedures and functions. Provide Transact-SQL stored procedures and functions that return catalog information.
Catalog views and stored procedures are the methods recommended to access a database’s metadata. This is primarily because catalog views present metadata in a format that is independent of any catalog table implementation, which means that the views are not affected by changes in the underlying catalog tables. When you want to configure or manage a server, you will typically use stored procedures to help you perform the necessary tasks. Stored procedures provide the necessary functionality to view and manage the configuration of SQL Server and related databases with ease.
Catalog views contain information used by the SQL Server 2005 Database Engine. They provide the most general interface to the catalog metadata and are the most direct way to access and work with this information. All user-available metadata in the system catalog is exposed through catalog views. Catalog views do not contain information about replication, backup, database maintenance plan, or SQL Agent.
Like all structures in SQL Server 2005 databases, catalog views follow an object-based hierarchy in which lower-level objects inherit attributes of higher-level objects. Some catalog views inherit rows from other catalog views. For example, the Tables catalog view inherits all the columns of the Objects catalog view. Thus, in addition to columns that are specific to the Tables catalog view itself, the Tables catalog view has all the columns from the Objects catalog view. Table 4-1 summarizes the SQL Server 2005 catalog views and their uses.
Table 4-1. SQL Server 2005 Catalog Views
View Type | Description | Key Catalog Views |
|---|---|---|
CLR Assembly Catalog views | Describe Common Language Runtime (CLR) assemblies. | sys.assemblies sys.assembly_files sys.assembly_references |
Databases and Files Catalog views | Describe databases, database files, and backup devices associated with a SQL Server instance. | sys.backup_devices sys.database_files sys.databases sys.master_files |
Database Mirroring Catalog views | Describe witness roles that a server plays as a database mirroring partner. | sys.database_mirroring_witnesses |
Data Spaces and Full-Text Catalog views | Describe filegroups, partition schemes, and full-text catalogs. | sys.data_spaces sys.destination_data_spaces sys.filegroups sys.fulltext_catalogs sys.partition_schemes |
Endpoints Catalog views | Describe endpoints used for mirroring, service broker messaging, and Web services. | sys.database_mirroring_endpoints sys.endpoint_webmethods sys.endpoints sys.http_endpoints sys.service_broker_endpoints sys.soap_endpoints sys.tcp_endpoints sys.via_endpoints |
Extended Properties Catalog views | Describe extended properties and the class of objects from which they originate. | sys.extended_properties |
Describe linked or remote servers and their related logins. | sys.linked_logins sys.remote_logins sys.servers | |
Messages (for Errors) Catalog views | Describe system-defined and user-defined error messages. | sys.messages |
Objects Catalog views | Describe top-level database objects. | sys.allocation_units sys.assembly_modules sys.check_constraints sys.columns sys.computed_columns sys.default_constraints sys.event_notifications sys.events sys.extended_procedures sys.stats sys.foreign_key_columns sys.foreign_keys sys.fulltext_index_columns sys.fulltext_indexes sys.identity_columns sys.index_columns sys.indexes sys.key_constraints sys.numbered_procedures sys.numbered_procedure_parameters sys.objects sys.parameters sys.partitions sys.procedures sys.service_queues sys.sql_dependencies sys.sql_modules sys.stats_columns sys.synonyms sys.tables sys.traces sys.trigger_events sys.triggers sys.views |
Partition Function Catalog views | Describe partition functions, parameters, and range values. | sys.partition_functions sys.partition_parameters sys.partition_range_values |
Scalar Types Catalog views | Describe user-defined scalar types for CLR assemblies as well as other system- and user-defined scalar types. | sys.assembly_types sys.types |
Schemas Catalog views | Describe database schemas. | sys.schemas |
Security Catalog views | Describe server-level, database-level, and encryption security attributes and values. | Database-level views (sys.database_permissions, sys.database_principals, sys.database_role_members) Server-level views (sys.server_permissions, sys.server_principals, sys.server_role_members, sys.sql_logins) Encryption views (sys.asymmetric_keys, sys.certificates, sys.credentials, sys.crypt_properties, sys.key_encryptions, sys.symmetric_keys) |
Describe Service Broker endpoints and messaging components. | sys.conversation_endpoints sys.conversation_groups sys.remote_service_bindings sys.service_contract_message_usages sys.service_contract_usages sys.routes sys.service_contracts sys.service_message_types sys.services sys.transmission_queue | |
Server-Wide Configuration Catalog views | Describe server-wide configuration option values. | sys.configurations sys.fulltext_languages sys.trace_categories sys.trace_columns sys.trace_event_bindings sys.trace_events sys.traces sys.trace_subclass_values |
XML Schemas (XML Type System) Catalog views | Describe XML Schema components and values. | sys.xml_indexes sys.xml_schema_attributes sys.xml_schema_collections sys.xml_schema_component_placements sys.xml_schema_components sys.xml_schema_elements sys.xml_schema_facets sys.xml_schema_model_groups sys.xml_schema_namespaces sys.xml_schema_types sys.xml_schema_wildcard_namespaces sys.xml_schema_wildcards |
Table 4-2 provides mapping between SQL Server 2000 system tables and SQL Server 2005 system views. The entries are organized by database and view type. Mappings for the master database are followed by mappings for all databases.
Table 4-2. Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views
SQL Server 2000 System Table | SQL Server 2005 System View | SQL Server 2005 View Type |
|---|---|---|
Master Database | ||
sysaltfiles | sys.master_files | Catalog view |
syscacheobjects | sys.dm_exec_cached_plans | Dynamic management view |
syscharsets | sys.syscharsets | Compatibility view |
sysconfigures | sys.configurations | Catalog view |
syscurconfigs | sys.configurations | Catalog view |
sysdatabases | sys.databases | Catalog view |
sysdevices | sys.backup_devices | Catalog view |
syslanguages | sys.languages | Compatibility view |
syslockinfo | sys.dm_tran_locks | Dynamic management view |
syslocks | sys.dm_tran_locks | Dynamic management view |
syslogins | sys.server_principals | Catalog view |
sysmessages | sys.messages | Catalog view |
sysoledbusers | sys.linked_logins | Catalog view |
sysopentapes | sys.dm_io_backup_tapes | Dynamic management view |
sysperfinfo | sys.dm_os_performance_counters | Dynamic management view |
sysprocesses | sys.dm_exec_connections sys.dm_evec_sessions sys.dm_exec_requests | Dynamic management views |
sysremotelogins | sys.remote_logins | Catalog view |
sysservers | sys.servers | Catalog view |
All Databases | ||
syscolumns | sys.columns | Catalog view |
syscomments | sys.sql_modules | Catalog view |
sysconstraints | sys.check_constraints sys.default_constraints sys.key_constraints sys.foreign_keys | Catalog views |
sysdepends | sys.sql_dependencies | Catalog view |
sysfilegroups | sys.filegroups | Catalog view |
sysfiles | sys.database_files | Catalog view |
sysforeignkeys | sys.foreign_keys | Catalog view |
sysfulltextcatalogs | sys.fulltext_catalogs | Catalog view |
sysindexes | sys.indexes | Catalog view |
sysindexkeys | sys.index_columns | Catalog view |
sysmembers | sys.databases_role_members | Catalog view |
sysobjects | sys.objects | Catalog view |
syspermissions | sys.database_permissions, sys.server_permissions | Catalog views |
sysprotects | sys.database_permissions, sys.server_permissions | Catalog views |
sysreferences | sys.foreign_keys | Catalog view |
systypes | sys.types | Catalog view |
sysusers | sys.database_principals | Catalog view |
You can use system stored procedures to view SQL Server configuration details and to perform general administration. SQL Server 2005 has two main categories of system stored procedures:
Those meant for administrators
Those used to implement functionality for database application programming interfaces (APIs)
Naturally, you will want to work with system stored procedures meant for administration and not those that implement database API functions. System stored procedures are written using Transact-SQL (T-SQL). Most return a value of 0 to indicate success and a nonzero value to indicate failure. As an example, sp_dboption is a stored procedure for managing the configuration options of SQL Server databases (except for the master and tempdb databases). When you use sp_dboption to set a database configuration value, a return code of 0 indicates that the option was set as expected. A return code of 1 indicates that the stored procedure failed and the option was not set as expected.
The following example takes the Personnel database offline if there are no current users:
USE master; GO EXEC sp_dboption "Personnel", "offline", "TRUE"; GO
If the stored procedure returns 0, then the database was successfully taken offline. A return value of 1 indicates that there was a problem taking the database offline, which means that the database is still online. For more information on using stored procedures, see the section titled "Configuring SQL Server with Stored Procedures" later in this chapter.
Table 4-3 provides a summary of stored procedures for administration. The table entries are organized by the type of administration activity for which the stored procedure is designed.
Table 4-3. Key System Stored Procedures by Type