SQL Server System and Database Administration 

You can expand the Views node in a given database in the Object Explorer and open the System Views node to see a list of the available DMVs. The DMVs are all listed together and start with dm_. If you expand the Column node under each DMV, you see the available columns to select from the view. You can then drag the column into a query window to be included in a SELECT statement.


To illustrate the value of the DMVs, let’s look at a performance scenario and compare the SQL Server 2000 approach to a SQL Server 2008 approach using DMVs. A common performance-related question is “What stored procedures are executing most frequently on my server?” With SQL Server 2000, the most likely way to find out is to run a Profiler trace. You must have a Profiler trace that has already been running to capture the stored procedure executions, or you must create a new trace and run it for a period of time to answer the performance question. The trace takes time to create and can affect server performance while it is running. 


With SQL Server 2008, you can use one of the DMVs in the execution category to answer the same performance question. The following example uses the sys.dm_exec_query_stats DMV along with a dynamic management function named dm_exec_sql_text. It returns the object IDs of the five most frequently executed stored procedures, along with the actual text associated with the procedure: DMV along with a dynamic management function named dm_exec_sql_text. It returns the object IDs of the five most frequently executed stored procedures, along with the actual text associated with the procedure:


select top 5 q.execution_count, q.total_worker_time,

   s.dbid, s.objectid, s.text

   fromsys.dm_exec_query_stats q

   CROSS APPLY sys.dm_exec_sql_text (q.sql_handle) s

   ORDER BY q.execution_count desc


The advantage of using a DMV is that it can return past information without having to explicitly create a trace or implement some other performance tool. SQL Server automatically caches the information so that you can query it at any time. The collection of the data starts when the SQL Server instance is started, so you can get a good cross-section of information. Keep in mind that your results can change as the server continues to collect information over time. 


Many of the performance scenarios such as those that relate to memory, CPU utilization, blocking, and recompilation can be investigated using DMVs. You should consider using DMVs to address performance problems before using other methods in SQL Server 2008. In many cases, you may be able to avoid costly traces and glean enough information from the DMV to solve your problem.


Dynamic management functions return the same type of information as DMVs. The dynamic management functions also have names that start with dm_ and reside in the sys schema. You can find the dynamic management functions listed in the Object Explorer within the master database. If you select Function, System Functions, TableValued Functions, you see the dynamic management functions listed at the top.
 

DMVs are also a great source of information that does not relate directly to performance. For example, you can use the dm_os_sys_info DMV to gather important server information, such as the number of CPUs, the amount of memory, and so on. The following example demonstrates the use of the dm_os_sys_info DMV to return CPU and memory information:


select cpu_count, hyperthread_ratio, physical_memory_in_bytes

         fromsys.dm_os_sys_info


The cpu_count column returns the number of logical CPUs, hyperthread_ratio returns the ratio between physical CPUs and logical CPUs, and the last column selected returns the physical memory on the SQL Server machine. 


System Stored Procedures
System stored procedures have been a favorite of SQL Server DBAs since the inception of SQL Server. They provide a rich set of information that covers many different aspects of SQL Server. They can return some of the same types of information as system views, but they generally return a fixed set of information that cannot be modified as you can when using a SELECT statement against the system views. That is not to say that they are not valuable; they are valuable, and they are particularly useful for people who have been using SQL Server for a long time. System stored procedures such as sp_who, sp_lock, and sp_help are tools for a database professional that are as basic as a hammer is to a carpenter. System stored procedures have names that start with sp_, and they are found in the sys schema.


They are global in scope, which allows you to execute them from any database, without qualifying the stored procedure name. They also run in the context of the database where you are working. In other words, if you execute sp_helpfile in the AdventureWorks2008R2 database, the database files for the AdventureWorks2008R2 database are returned. This same type of behavior exists for any stored procedure that is created in the master database with a name that starts with sp_. For example, if you create a procedure named sp_helpme in the master database and execute that procedure in the AdventureWorks2008R2 database, SQL Server ultimately looks for and finds the procedure in the master database.


It is often useful to create your own system stored procedures to make it easier to execute complex queries against the system views (or to provide information not provided by the built-in system procedures). For more information and tips on creating your own system stored procedures, refer to Chapter 28, “Creating and Managing Stored Procedures.”


System stored procedures are listed in the Object Explorer, in the Programmability node within Stored Procedures and then System Stored Procedures. There are far too many system stored procedures to list or discuss them all here. A quick check of the master database lists more than 1,000 procedures. SQL Server Books Online provides detailed help on these procedures, which it groups into 18 different categories. You can go here for list of details. 


Useful System Stored Procedures

You are likely to use only a handful of system stored procedures on a regular basis. What procedures you use depends on the type of work you do with SQL Server and your capacity to remember their names. Below picture contains a sample set of system stored procedures that you may find useful.​​

Many of the administrative functions performed by SSMS can also be accomplished with system stored procedures. Examples include procedures that start with sp_add and sp_delete, which can be used to add and delete database objects. In addition, more than 90 system stored procedures start with sp_help, which return help information on database objects.


You can use the sys.all_objects catalog view to search for available system stored procedures. This catalog view lists objects that are schema scoped as well as system objects. For example, the query SELECT * FROM sys.all_objects WHERE name LIKE ‘sp_help%’ returns all the system stored procedures that start with sp_help. You can turn to Books Online for detailed help on any of the system stored procedures. Just enter sp_ in the index search, and you see a list of them all.


Becoming familiar with some of the system stored procedures is well worth your while. Using them is a very fast and effective means for gathering information from SQL Server. They do not require the formation of a SELECT statement, and using them is often the easiest way to get information via a query window.

Summary
Administering SQL Server can be a complex and time-consuming job. Understanding the SQL Server internals and some of the easy ways to obtain information about
a SQL Server instance can make this job a lot easier. Taking the time to learn what makes SQL Server tick expands your knowledge of this comprehensive DBMS and helps you make better decisions when working with it. Now that you know a bit about managing SQL Server, you may need to install an instance of SQL Server to administer. Take a look at Chapter 8, “Installing SQL Server 2008,” which guides you through the installation processthe easy ways to obtain information about a SQL Server instance can make this job a lot easier. Taking the time to learn what makes SQL Server tick expands your knowledge of this comprehensive DBMS and helps you make better decisions when working with it. Now that you know a bit about managing SQL Server, you may need to install an instance of SQL Server to administer. Take a look at Chapter 8, “Installing SQL Server 2008,” which guides you through the installation process


You can use the sys.master_files catalog view to list the physical locations of the system database files as well as the user database files. This catalog view contains a myriad of information, including the logical name, current state, and size of each database file.


The folder where each of these database files is located depends on the SQL Server installation. By default, the installation process places these files in a folder named <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\. You can move these files after the installation by using special procedures that are documented in the SQL Server Books Online topic “Moving System Databases.”


The following sections describe the function of each system database.


The Master Database

The master database contains server-wide information about the SQL Server system. This server-wide information includes logins, linked server information, configuration information for the server, and information about user databases created in the SQL Server instance. The actual locations of the database files and key properties that relate to each user database are stored in the master database. SQL Server cannot start without a master database. This is not surprising, given the type of information that it contains. Without the master database, SQL Server does not know the location of the databases that it services and does not know how the server is configured to run.


The resource Database

The resource database contains all the system objects deployed with SQL Server 2008. These system objects include the system stored procedures and system views that logically appear in each database but are physically stored in the resource database. Microsoft moved all the system objects to the resource database to simplify the upgrade process. When a new release of the software is made available, upgrading the system objects is accomplished by simply copying the single resource database file to the local server. Similarly, rolling back an upgrade only requires overwriting the current version of the resource database with the older version.


You do not see the resource database in the list of system databases shown in SQL Server Management Studio (SSMS). You also cannot add user objects to the resource database. For the most part, you should not be aware of the existence of the resource database. It has database files named mssqlsystemresources.mdfandmssqlsystemresources.ldfthat are located in the Binn folder, but you cannot access the database directly. In addition, you do not see the database listed when selecting databases using system views or with system procedures, such as sp_helpdb.


The model Database

The model database is a template on which all user-created databases are based. All databases must contain a base set of objects known as the database catalog. When a new database is created, the model is copied to create the requisite objects. Conveniently, objects can be added to the model database. For example, if you want a certain table created in all your databases, you can create the table in the model database, and it is then propagated to all subsequently created databases.


The msdb Database

The msdb database is used to store information for the SQL Server Agent, the Service Broker, Database Mail, log shipping, and more. When you create and schedule a SQL Server Agent job, the job’s parameters and execution history are stored in msdb. Backups and maintenance plan information are stored in msdb as well. If log shipping is implemented, critical information about the servers and tables involved in this process is stored in msdb.


The distribution Database

The distribution database, utilized during replication, stores metadata and history information for all types of replication. It is also used to store transactions when transactional replication is utilized. By default, replication is not set up, and you do not see the distribution database listed in SSMS. However, the actual data files for the distribution database are installed by default. We will talk more about  “Replication,” for a more detailed discussion of the intricacies of replication.


The tempdb Database

The tempdb database stores temporary data and data objects. The temporary data objects include temporary tables, temporary stored procedures, and any other objects you want to create temporarily. The longevity of data objects in the temporary database depends on the type of object created. Ultimately, all temporary database objects are removed when the SQL Server service is restarted. The tempdb database is re-created, and all objects and data added since the last restart of SQL Server are lost. tempdb can also be used for some of SQL Server’s internal operations. Large sort operations are performed in tempdb before the result set is returned to the client. Certain index operations can be performed in tempdb to offload some of the space requirements or to spread I/O. SQL Server also uses tempdb to store row versions that are generated from database modifications in databases that use row versioning or snapshot isolation transactions. We will discuss more when we see  “Locking and Performance,” for a more detailed discussion of transaction isolation levels and row versioning.


Maintaining System Databases

You should give system databases the same attention that you give your user databases. These databases should be backed up on a regular basis and secured in the event that one of them needs to be restored. All the system databases, with the exception of tempdb and resource, can be backed up. These same databases can also be restored to bring them back to a previous state.


Although you cannot back up the resource database using SQL Server’s BACKUP and RESTORE commands, you can make a backup copy of it by performing a file-based or disk-based backup of the mssqlsystemresource.mdf file (SQL Server must not be running at the time). Likewise, you can manually restore a backup copy of the mssqlsystemresource.mdf file only when SQL Server is not running. You must be careful not to overwrite the current resource database with a version for a different release level of SQL Server.


It’s important that you monitor the size of your system databases. The amount of data that accumulates in these databases can be significant. This is particularly true for the tempdb, msdb, and distribution databases. Large sort or index operations can increase the size of your tempdb database in a short period of time. The msdb and distribution databases contain a great deal of historical information. Consider, for example, a server with hundreds of databases that have log backups occurring every 15 minutes. The information captured for each individual backup is not significant, but the total number of databases and frequency of the backups cause many rows to be stored in the msdb database. Cleanup tasks and similar activities that remove older historical data can help keep the database size manageable.


System Tables
System tables contain data about objects in the SQL Server databases (that is, metadata) as well as information that SQL Server components use to do their job. Many of the system tables are now hidden (in the resource database) and are no longer available for direct access by end users. In SQL Server 2008, compatibility views, which are discussed later, have the same names as the system tables available in SQL Server 2000. For example, if you had a query in SQL Server 2000 that selected from syscolumns, this query continues to work in SQL Server 2008, but the results come from a view instead of a system table. The system tables that you can view are now found in some of the system databases, such as msdb or master. You can use the Object Explorer in SSMS to view the system tables in these databases. The most significant number of viewable system tables is found in the msdb system database. The system tables there support backup and restore, log shipping, maintenance plans, Notification Services, the SQL Server Agent, and more. You can retrieve a tremendous amount of information from these system tables if you know what you are looking for.


 The following query selects from the system tables in msdb to report on recent restores for the AdventureWorks2008R2 database:


select destination_database_name ‘database’, h.restore_date, restore_type,

     cast((backup_size/1024)/1024 as numeric(8,0)) ‘backup_size MB’,

      f.physical_device_name

frommsdb..restorehistory h (NOLOCK)

      LEFT JOIN msdb..backupset b (NOLOCK)

            ON h.backup_set_id = b.backup_set_id

      LEFT JOIN msdb..backupmediafamily f (NOLOCK)

            ON b.media_set_id = f.media_set_id

whereh.restore_date > getdate() - 5

      and UPPER(h.destination_database_name) = ‘AdventureWorks2008R2’

orderby UPPER(h.destination_database_name), h.restore_date desc


One
of the challenges with using system tables is determining the relationships between them. Some vendors offer diagrams of these tables, and you can also determine the relationships by reviewing the foreign keys on these tables and by referring to SQL Server 2008 Books Online, which describes the use for each column in the system table.


Microsoft does not recommend querying system tables directly. It does not guarantee the consistency of system tables across versions and warns that queries that may have worked against system tables in past versions may no longer work. Catalog views or information schema views should be used instead, especially in production code. Queries against system tables are best used for ad hoc queries. The values in system tables should never be updated, and an object’s structure should not be altered, either. Making changes to the data or structure could cause problems and cause SQL Server or one of its components to fail.


System Views
System views are virtual tables that expose metadata that relates to many different aspects of SQL Server. Several different types of views target different data needs. SQL Server 2008 offers an extended number of system views and view types that should meet most, if not all, your metadata needs. The available system views can be shown in the Object Explorer in SSMS. There are far too many views to cover in detail in this chapter, but we cover each type of view and provide an example of each to give.

 

Compatibility Views

Compatibility views were retained in SQL Server 2008 for backward compatibility. Many of the system tables available in SQL Server 2000 and prior versions of SQL Server are now implemented as compatibility views. These views have the same name as the system tables from prior versions and return the same metadata available in SQL Server 2000. They do not contain information that was added after SQL Server 2000. You can find most of the compatibility views in the Object Explorer by looking for system views that have names starting with sys.sys. For example, sys.syscolumns, sys.syscomments, and sys.sysobjects are all compatibility views. The first part of the name indicates the schema that the object belongs to (in this case, sys). All system objects are part of this sys schema or the INFORMATION_SCHEMA schema. The second part of the name is the view name, which corresponds to the name of a system table in SQL Server 2000.


To see a list of compatibility views, use the index lookup in SQL Server 2008 Books Online and look for sys.sys. The index is placed at the beginning of a list of compatibility views, starting with sys.sysaltfiles. Objects in the list that are compatibility views have the text compatibility view following the object name, so you can easily identify them and get help. You also can use the new IntelliSense feature available with SQL Server 2008 to obtain information about the compatibility views and other system views. Simply open a query window in SSMS and start typing a SELECT statement.


When you start typing the name of the view that you want to select from (for example, sys.) the IntelliSense drop-down appears listing the views that start with the letters sys. You can also determine the columns available from the view by referencing the view or alias for the view in the column selection list. When you enter the period following the view or alias, the IntelliSense drop-down shows you the available columns.


You should transition from the use of compatibility views to the use of other system views, such as catalog views. The scripts that were created in SQL Server 2000 and reference SQL Server 2000 system tables should continue to function in SQL Server 2008, but this capability is strictly for backward compatibility. The bottom table provides a list of SQL Server 2000 system tables and alternative SQL Server 2008 system views you can use instead.






 





System Administrator Responsibilities
A system administrator is responsible for the integrity and availability of the data in a database. This is a simple concept, but it is a huge responsibility. Some large corporations place a valuation on their data as high as $1 million per 100MB. The investment in dollars is not the only issue; many companies that lose mission-critical data simply never recover.


Job descriptions for system administrators vary widely. In small shops, the administrator might lay out the physical design, install SQL Server, implement the logical design, tune the installation, and then manage ongoing tasks, such as backups. At larger sites, tasks might be broken out into separate job functions. Managing users and backing up data are common examples. However, a lead administrator should still be in place to define policy and coordinate efforts. Whether performed by an individual or as a team, the core administration tasks are as follows:


  • Install and configure SQL Server.
  • Plan and create databases.
  • Manage data storage.
  • Control security.
  • Tune the database.
  • Perform backup and recovery


Another task sometimes handled by administrators is managing stored procedures. Because stored procedures for user applications often contain complex Transact-SQL (TSQL) code, they tend to fall into the realm of the application developer. However, because stored procedures are stored as objects in the database, they are also the responsibility of the administrator. If an application calls custom stored procedures, the system administrator must be aware of this and coordinate with the application developers. The system administration job can be stressful, frustrating, and demanding, but it is a highly rewarding, interesting, and respected position. As a system administrator, you are expected to know all, see all, and predict all, but you should be well compensated for your efforts.


System Databases
SQL Server uses system databases to support different parts of the database management system (DBMS). Each database plays a specific role and stores information that SQL Server needs to do its job. The system databases are much like the user databases created in SQL Server. They store data in tables and contain the views, stored procedures, and other
 database objects that you also see in user databases. They also have associated database files (that is, .mdf and .ldf files) that are physically located on the SQL Server machine. The below lists system databases and their related database filenames.

  Catalog Views

Using catalog views is the preferred method for returning information used by the Microsoft SQL Server database engine. There is a catalog view to return information about almost every aspect of SQL Server. The number of catalog views is far too large to list here, but you can gain some insight into the range of information available by looking at the following list, which shows the categories of information covered by catalog views:


  • Change Tracking
  •  Common language runtime (CLR) assembly
  • Data spaces and full text
  • Database mirroring
  • Data spaces
  • Endpoint
  • Extended properties
  • Linked servers
  • Messages (for errors)
  • Objects
  • Partition function
  • Resource Governor
  • Scalar types
  • Schemas  
  • Security
  • Server-wide configuration
  •  Service Broker
  •  SQL Server Extended Events
  •  XML schemas (XML type system)

 

Some of the catalog views return information that is new to SQL Server 2008. Examples include the Change Tracking and Resource Governor catalog views. Other catalog views provide information that may have been available in prior versions through system tables, system procedures, and so on, but the new catalog views expand on the information returned and include elements that are new to SQL Server 2008. To demonstrate the use of a catalog view, let’s compare a simple SQL Server 2000 SELECT statement that returns object information to a SELECT statement in SQL Server 2008 that returns similar information. The following example shows a SELECT statement written in SQL Server 2000 to return any stored procedure created after a given date:


select o.crdate, o.name

   from sysobjects o

   where type = ‘p’

     and crdate > ‘1/1/08’

order by crdate, name


Now, compare this SELECT statement to one that uses a SQL Server 2008 catalog view. The sys.objects catalog view is a new alternative to the SQL Server 2000 sysobjects system table. The following SELECT uses the sys.objects catalog view to return the same type of information as the preceding example:


select o.create_date, o.modify_date, name

   from sys.objects o

   where type = ‘p’

  and (create_date > ‘1/1/08’

       or o.modify_date >= ‘1/1/08’)

  order by  1, 2, 3


As you can see, the modify_date column has been added to the SELECT statement. This column did not exist with the sysobjects system table. The addition of this column allows you to identify objects that were created as well as objects that were modified or altered. Let’s look at an example of using a catalog view to return the same kind of information returned in SQL Server 2000 with a system procedure.


The handy sp_helpfile system procedure returns information about database files associated with a given database. This SQL Server 2000 procedure is still available in SQL Server 2008. An alternative to this procedure is the new sys.master_files catalog view. This view returns all the information that sp_helpfile returns and more. The following example shows a SELECT statement using the sys.master_files catalog view to return the database files for the AdventureWorks2008R2 database:


select *

    from sys.master_files

   where db_name(database_id) = ‘AdventureWorks2008R2’


You have the distinct advantage of being able to select the database files for all the databases on your server by using this catalog view. You can also tailor your SELECT statement to isolate database files based on the size of the database or the location of the physical database files. For example, to return all database files that are found somewhere on your C drive, you could use the following SELECT:


select db_name(database_id), physical_name

    from sys.master_files

    where physical_name like ‘c:\%’
 

There are plenty of catalog views that provide information about SQL Server. When you are looking to return information about SQL Server components, you should look to the catalog views first. These views provide a great deal of flexibility and allow you to isolate the specific information you need.


Information Schema Views

Information schema views provide another system table–independent option for accessing SQL Server metadata. This type of view was available in prior versions of SQL Server. Using information schema views is a viable alternative for accessing SQL Server metadata from a production application. The information schema views enable an application that uses them to function properly even though the underlying system tables may have changed.


Changes to the underlying system tables are most prevalent when a new version of SQL Server is released (such as SQL Server 2008), but changes can also occur as part of service packs to an existing version. The information schema views also have the advantage of being SQL-92 compatible. Compliance with the SQL-92 standard means that SQL statements written against these views work with other DBMSs that also adhere to the SQL-92 standard. The SQL-92 standard supports a three-part naming convention, which SQL Server has implemented as database.schema.object.


In SQL Server 2008, all the information schema views are in the same schema, named INFORMATION_SCHEMA. The following information schema views or objects are available:

  • CHECK_CONSTRAINTS
  • COLUMN_DOMAIN_USAGE
  • COLUMN_PRIVILEGES
  • COLUMNS
  • CONSTRAINT_COLUMN_USAGE
  •  CONSTRAINT_TABLE_USAG
  • DOMAIN_CONSTRAINE
  • DOMAINS
  • KEY_COLUMN_USAGE
  • PARAMETERS
  • REFERENTIAL_CONSTRAINTS
  • ROUTINES
  • ROUTINE_COLUMNS
  • SCHEMATA
  • TABLE_CONSTRAINTS
  • TABLE_PRIVILEGES
  • TABLES
  • VIEW_COLUMN_USAGE
  • VIEW_TABLE_USAGE
  • VIEWS 

When you refer to information schema views in a SQL statement, you must use a qualified name that includes the schema name. For example, the following statement returns all the tables and columns in a given database, using the tables and columns information schema views:


select t.TABLE_NAME, c.COLUMN_NAME

from INFORMATION_SCHEMA.TABLES t

   join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_NAME = c.TABLE_NAME

 order by t.TABLE_NAME, ORDINAL_POSITION


You can expand the Views node in a given database in the Object Explorer and open the System Views node to see a list of the available information schema views. The information schema views are listed at the top of the System Views node. If you expand the Column node under each information schema view, you see the available columns to select from the view. You can then drag the column into a query window for use in a SELECT statement. You can also use IntelliSense in a query window determine the columns.


Fortunately, the names of the information schema views are fairly intuitive and reflect the kind of information they contain. The relationships between the information schema views can be derived from the column names shared between the tables.
 

Dynamic Management Views

Dynamic management views (DMVs), which were introduced in SQL Server 2005, provide a simple means for assessing the state of a server. These views provide a lightweight means for gathering diagnostic information without the heavy burden associated with the tools available in SQL Server 2000. The SQL Server 2000 diagnostic tools, such as heavy Profiler traces, PerfMon, dbcc executions, and pssdiag, are still available, but oftentimes, the information returned from the DMVs is enough to determine what may be ailing a SQL Server machine. An extensive number of DMVs are available in SQL Server 2008. Some DMVs are scoped at the server level, and others are scoped at the database level. They are all found in the sys schema and have names that start with dm_. Below picture is the lists of the different types of DMVs. The DMVs in this table are categorized based on function as well as the starting characters in the DMV names. The naming convention gives you an easy means for identifying the type of each DMV.

Online learning

​fREE

SQL DBA SCHOOL

Founder Abubeker Refaw