Chapter 4

Managing and Troubleshooting the Database Engine

WHAT’S IN THIS CHAPTER

• Configure Your Instance Using Management Studio
• Use a Dedicated Administrator Connection to Access an Unresponsive Server
• Monitor Processes Using Dynamic Management Objects (DMOs)

In this chapter, you learn how to configure and monitor your SQL Server instance. This is the key to a smooth running, database — proper configuration and active monitoring. In addition, you learn some troubleshooting methods that help you identify what is happening when the instance is not performing as expected. This chapter assumes you already know the basics of Management Studio navigation and focuses on what you need to know as a database administrator (DBA). Many other chapters in this book cover various aspects of Management Studio, so those points are not duplicated here (backing up your database, for example, is covered in Chapter 17, “Backup and Recovery”).

CONFIGURATION AND ADMINISTRATION TOOLS

After you install SQL Server or upgrade to SQL Server 2012, you will likely need to configure it for your needs. In SQL Server 2012, Microsoft maintained its policy to increase out-of-the-box security established with SQL Server 2008 by turning off features after installation, thereby reducing the software footprint. The features turned off vary based on the edition of SQL Server. For example, TCP/IP is disabled in Developer Edition by default, and every edition has CLR integration turned off. This makes the environment more usable for you as an administrator because features you don’t care about are not crowding your administration screen. It also reduces the options that a hacker or, more likely, a malicious user can use to penetrate your system. As a result of these security measures, there is even more reason to invest time in configuring SQL Server 2012 towards your specific needs. In this section, you learn how to configure SQL Server for your specific environment and security needs in a few ways: by using SQL Server Configuration Manager, startup parameters, startup stored procedures, and partially contained databases. SQL Server Configuration Manager is the best place to start.

SQL Server Configuration Manager

The SQL Server Configuration Manager configures the SQL Server services much like the Services applet in the Control Panel, but it has much more functionality than the applet. For example, the program can also change what ports SQL Server listens on and what protocols each instance uses. You can open the program (see Figure 4-1) from Start All Programs Microsoft SQL Server 2012 Configuration Tools SQL Server Configuration Manager.

FIGURE 4-1

To start configuring SQL server to fit your environment’s needs, follow these steps:

1. Select Start Microsoft SQL Server 2012 Configuration Tools SQL Server Configuration Manager SQL Server Services.

2. To configure an individual service such as SQL Server, double-click the service name to open the service Properties page. In the Log On tab, you can configure which account starts SQL Server. You should start SQL Server with a regular domain user account with minimal rights. The account should not have the privilege to Log on Locally, for example. There is no reason for the account to be a local or domain administrator in SQL Server 2012.

3. Next, create a non-expiring password so your SQL Server doesn’t fail to start when the password expires. If the SQL Server services do not need to communicate outside the instance’s machine, you could start the service with the Local System account, but the account may have more local rights than you want. (This is discussed more in Chapter 8, “Securing the Database Instance.”)

Use Configuration Manager to change Services attributes instead of the Windows Services dialogs in the Administrative Tools area. Logins used as service accounts require minimum permission levels that are granted automatically when you use the Configuration Manager.

4. On the Service tab, specify whether you’d like the service to start automatically, manually, or be disabled.

The rest is optional, but highly recommended. For instance, if you go to the Advanced tab (shown in Figure 4-2) for each service, you can configure the more interesting options. Here you can turn off Customer Feedback Reporting. This feature enables Microsoft to receive utilization reports from your SQL Server. Even if you wanted to do this, in most production environments your SQL Server may not send the report because of a lack of Internet access from production servers.

FIGURE 4-2

You can also check the Error Reporting option in the Advanced tab to e-mail Microsoft whenever a critical error has occurred. The minimal and anonymous information is sent over a secure HTTPS protocol.

Additionally, on the SQL Server 2012 Network Configuration page in the Configuration Manager, you can see a list of network protocols that SQL Server is listening on by instance. If you want to turn a protocol on or off, you can do so by right-clicking the protocol and selecting Enable or Disable. By enabling only the Shared Memory protocol, only clients that run on the same computer can connect to your instance of SQL Server.

The VIA protocol has been discontinued in SQL Server 2012.

Startup Parameters

SQL Server has an array of switches you can use to enable advanced settings for the Database Engine or help with troubleshooting. There are two ways to set these configuration options: from SQL Server Configuration Manager or via command prompt. You enable these switches via the SQL Server Configuration Manager by altering the SQL Server service’s startup parameters. Configuring settings through Configuration Manager causes SQL Server to use the configured startup parameter every time it starts. Or, you can temporarily set the switches by running SQL Server from a command prompt. To enable a particular switch from a manual start-up, perform the following steps.

FIGURE 4-3

To enable a particular switch from a command prompt start-up, perform the following steps.

Never start SQL Server with command prompt for normal use; after you log off the machine, your command prompt closes, stopping SQL Server.

The syntax for the sqlserver runtime is as follows:

 SQLServr.exe [-dmaster_file_path] [-lmaster_log_path]
        -eerror_log_path] [-sinstance_name][-c] [-f]
        [-gmemory_to_reserve] [-h] [-kcheckpoint Speed in MB/sec]
        [-m] [-n] [-Ttrace#] [-ttrace#] [-x]
        [-ystack dump on this error] [-B] [-K]
 

The startup options are useful in troubleshooting a problem or for solving quick, one-off problems. Rather than describe every switch, only the ones you are likely to use most frequently are covered.

• -d and -l switches: You can change which master database SQL Server uses by using the -d and -l switches:

   SQLServr.exe -d C:	empTempMasterDB.mdf -l C:	empTempMasterLog.ldf
  

  The -d switch specifies the database file, and the -l switch specifies the log file. This may be useful if you want to use a temporary configuration of the master database that may not be corrupt.

• -T switch: Another useful switch is the -T, which enables you to start given trace flags for all the connections for a SQL Server instance. You can use this, for example, to turn on a trace flag to monitor deadlocks in your SQL Server instance (note that the “T” is uppercase):

   SQLServr.exe -T1204
  

  

  SQL Server also includes a lowercase trace flag option: SQLServr.exe -t1204. This should be used with caution because using this flag sets other internal trace flags.

  

  If you try to start an instance of SQL Server while it is already running, you get errors. When SQL runs against a master file, it opens the file exclusively to prevent another instance from writing to the same file. One of the errors tells you that a file is in exclusive use or not available. No harm is done when this occurs.

• -f switch: This switch places SQL Server in minimal mode and only enables a single connection. By placing SQL Server in minimal mode, SQL Server starts with a minimum configuration and suspends the CHECKPOINT process, startup stored procedures, and remote connections. Use this to correct a configuration option that was inappropriately set, such as setting a memory option larger than the physical memory on the server. In such a case, SQL Server would fail to start. To fix this, start it from the command prompt with the -f option, correct the memory setting, and then restart SQL normally. Another use of this option is to repair startup stored procedures. An administrator may have defined a startup stored procedure that problematically prevents SQL Server from starting. To combat this, place SQL Server in minimal mode, remove or correct the startup stored procedure, and then start SQL Server again without the switch to repair the problem.
  

  Using SQL Server Configuration Manager, make sure you stop SQL Server Agent before placing SQL Server in single-user mode. Otherwise, SQL Server Agent takes the only available connection.

• -g switch: This switch reserves additional memory outside SQL Server’s main memory pool for use by extended stored procedures, OLE DB providers used by distributed queries, and automation objects used by Transact-SQL queries. The general recommendation is to not use this flag unless you see either of the following error messages in your SQL Server error log:

   "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE <size>"
   "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT <size>"
  

  These messages indicate that SQL Server is trying to free up memory pool space to allocate the memory in this virtual memory area. In this case, you may want to increase the amount of memory allocated to these objects by using the -g switch. If the -g switch is not used, the default of 256MB of memory is allocated to this area.

  If you have a server that uses few extended stored procedures, distributed queries, or automation objects, you may want to use this switch with a value of less than 256 to reduce the amount of memory reserved for these objects. The memory would then be available for the general memory pool.

• -m switch: This switch puts SQL Server in single-user mode (sometimes called master recovery mode) and suspends the CHECKPOINT process, which writes data from disk to the database device. This switch is useful when you want to recover the master database from a backup or perform other emergency maintenance procedures.
• -k switch: This switch is used to influence the checkpoint frequency. It forces the regeneration of the system master key if one exists. Use this with extreme care, and only if directed by PSS.
• -s switch: This switch starts a named instance of SQL Server. When you start SQL Server from the command prompt, the default instance starts unless you switch to the appropriate BINN directory for the instance and provide the -s switch. For example, if your instance is named SQL2012, you should be in the C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLBinn directory and issue the following command:

  sqlserver.exe -sSQL2012
  

• -c switch: This switch enables you to decrease startup time when starting SQL Server from a command prompt by taking advantage of the fact that the SQL Server Database Engine does not start as a service when starting from the command prompt. This switch bypasses the Service Control Manager, which is unnecessary in this situation.

You can obtain a complete list of switches by using the -? switch, as shown in Table 4-1 (complete details appear in the SQL Server documentation):

TABLE 4-1: Startup Parameters

 sqlservr.exe -?

Startup Stored Procedures

Startup stored procedures work similarly to stored procedures except that they execute whenever the SQL Server instance starts. For example, you may have a startup stored procedure that e-mails you when the instance starts. You can also use startup stored procedures to create objects in tempdb and load them with data when SQL Server starts. These stored procedures run under the sysadmin server role, and only a sysadmin can create a startup stored procedure. Errors written from a startup stored procedure are written to the SQL Server error log.

In the following steps, startup stored procedures are enabled system-wide and an example startup stored procedure is created.

Make sure that you do the examples in this section only against a development server until you’re certain you want to do this in production.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

sp_configure 'scan for startup procs', 1;
GO
RECONFIGURE;
GO

 CREATE TABLE master.dbo.SQLStartupLog
 (StartTime datetime);
 GO

USE master 
GO 
CREATE PROC dbo.InsertSQLStartupLog
 as
 INSERT INTO master.dbo.SQLStartupLog
 SELECT GETDATE();
GO

 sp_procoption @ProcName = 'master.dbo.InsertSQLStartupLog',
   @OptionName= 'startup',
   @OptionValue = 1;

 sp_procoption @ProcName = 'master.dbo.InsertSQLStartupLog',
   @OptionName= 'startup',
   @OptionValue = 0;
 
 USE MASTER
 GO
 DROP TABLE master.dbo.SQLStartupLog;
 DROP PROC dbo.InsertSQLStartupLog;

Partially Contained Databases

Partially contained databases are a new feature of SQL Server 2012 that provides an excellent configuration option for specific security scenarios. A full discussion of the security benefits is available in Chapter 8, “Securing the Database Instance.” A contained database is a concept where all of the settings and metadata for that database have no configuration dependencies on the instance of SQL Server where the database resides. Users are able to connect to the database without authenticating a login at the instance level. This level of isolation makes a database with this configuration that is more portable from instance to instance, which can be quite beneficial when deploying a database to multiple instances, such as in a development environment.

In SQL Server 2012, fully contained databases are not implemented. Only partially contained databases are available for this release, meaning that objects or functions that cross the application boundary are allowed.

An application boundary is the boundary between the application model (database) and the instance. For example, the system table sys.endpoints is outside the application boundary, because it references instance-level objects. The system table sys.indexes is within the application boundary.

By default, a 2012 instance does not have contained databases enabled. Use sp_configure (shown in the following code snippet) to enable contained databases prior to migrating an existing database to this model:

sp_configure 'contained database authentication', 1; 
GO
RECONFIGURE; GO

Before you migrate a database to a contained model, use the new DMO sys.dm_db_uncontained_entities to identify the containment level of your database. The output from the following query returns objects that can potentially cross the application boundary.

SELECT so.name, ue.*
FROM sys.dm_db_uncontained_entities ue
    LEFT JOIN sys.objects so
      ON ue.major_id = so.object_id; 

An additional option to identify uncontained events in an application is the extended event database_uncontained_usage_event. This event fires whenever an uncontained event occurs in the application. See Chapter 12, “Monitoring Your SQL Server” for more information on extended events.

If your database is a good candidate for partial containment and it is enabled, the CONTAINMENT option is used to convert a database to a partially contained database. Suitable candidates for partial containment include databases that do not use instance-level features, such as Service Broker. This is done by issuing an ALTER DATABASE command as shown in the following code snippet.

USE master
GO
ALTER DATABASE AdventureWorks SET CONTAINMENT = PARTIAL;

For more information on migrating to a partially contained database and the risks and limitations of this feature, go to http://msdn.microsoft.com/en-us/library/ff929139(v=sql.110).aspx.

TROUBLESHOOTING TOOLS

Imagine you get a call informing you that a server is not responding. You go to SSMS to connect to the server to see what is going on. Your connection request waits, and waits, and then times out. You cannot connect, you cannot debug, and you cannot see anything. In this situation, you need tools in your arsenal to help you troubleshoot and repair core issues with your instance. The Dedicated Administrator Connection (DAC) is a very reliable tool for this type of situation. An alternative troubleshooting method that this section also covers is the process of rebuilding system databases in the event of a corruption or lost database.

Dedicated Administrator Connection

The DAC is a specialized diagnostic connection that can be used when standard connections to the server are not possible. When you need to connect to the server to diagnose and troubleshoot problems, the DAC is an invaluable administration tool to have.

SQL Server attempts to make DAC connect in every situation and most of the time it does, but in very severe situations it cannot guarantee connection to an unresponsive server.

SQL Server listens for the DAC connection on a dynamically assigned port. A connection can be made on this port only by sysadmin role members from SSMS or the sqlcmd tool. To connect to the DAC using SSMS, add a prefix to the server name. For example, if the server name is SQL2012, connect to server admin:Prod. You merely add the prefix admin: to the server name.

To connect using sqlcmd, use the -A option as follows:

 sqlcmd -sSQL2012 -E -A -d master

By default, DAC is only allowed on a client running on the local server. However, by using sp_configure, you can enable remote admin connections by executing the following:

sp_configure 'remote admin connection', 1; 
GO
RECONFIGURE; 
GO

On clustered instances, Microsoft recommends that you enable a remote admin connection. You may then need to connect using the TCP address and DAC port number found in the error log, as shown here:

 Sqlcmd -S<serveraddress>,<DacPort>

If you connect locally using the IP address, use the Loopback Adapter address, as in the following example:

 Sqlcmd -S127.0.0.1,1434

The Loopback Adapter Address is the address of localhost (the local computer), and on most systems, this translates to the IPv4 address 127.0.0.1. Connecting to the Loopback Adapter Address is functionally the same as connecting to localhost.

When connecting remotely, you need to know the port that DAC has been assigned. Port 1434 is the default, but the instance might be assigned something different if the connection to the default port failed during startup. Additionally, if remote administration connections are enabled, the DAC must be started with an explicit port number. PowerShell is a good option for finding the port number, as it enables you to see assigned DAC ports across multiple instances at one time. The following PowerShell example enables you to see which port has been assigned for DAC on each instance listed in the $instances variable.

$instances = "PRODUCTION", "PRODUCTIONR2", "PRODUCTIONSQL2012"
foreach($instance in $instances)
{
get-SQLErrorlog -SQLServer $instance |
where {($_.Text -match "Dedicated admin connection")} | 
format-table  $DisplayResults -AutoSize
}

Rebuilding the System Databases

If one of your system databases becomes corrupted and your backups cannot be found, your last resort may be to rebuild the system databases. This can essentially reinstall the system databases and rid your system of anything that may be causing it to act unpredictably. The repercussion of this is that you must reinstall any service packs; and all your user-defined databases, including the Reporting Services support database, will disappear. Additionally, any logins or server configurations must be redone.

Rebuilding your system databases should not be taken lightly. It is a high-impact technical decision that you make when no other good option exists.

To rebuild your system databases, follow these steps:

1. Go to a command prompt.

2. From the command prompt, run setup.exe as if you were installing SQL Server, but pass in a few switches as shown here:

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=instance_name /SQLSYSADMINACCOUNTS=accounts /SAPWD=sa password

The switches indicate the following:

• /QUIET: suppresses the errors and warnings while the rebuild runs. You see a blank screen while the process completes. Errors will still be logged to the Error Log.
• /ACTION: indicates that the action is to rebuild the database, by providing the REBUILDDATABASE parameter.
• /INSTANCENAME: provides the name of the instance where system databases should be rebuilt. Use MSSQLSERVER for a default instance.
• /SQLSYSADMINACCOUNTS: provides windows groups or individual accounts that should be provisioned as sysadmin. Use this option when SQL Server is configured for Windows Authentication Mode.
• /SAPWD: specifies the SA password. Use this option when SQL Server is configured for Mixed Authentication Mode.

Prior to SQL Server 2008, the original installation media was required to rebuild system databases. Now, the system database and log files are copied to C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBinnTemplates as part of installation. When you rebuild, setup.exe uses the files located here.

3. After the databases are rebuilt, return to your default configuration and databases. You need to restore the master database (more on this in Chapter 17) or reattach each user-defined database and re-create the logins. The preferable option, of course, is to recover the master database from a backup rather than rebuilding. Then your logins and databases automatically appear.

When you rebuild the system databases, the databases may appear to have disappeared, but the files are still in the operating system and can be reattached or restored. Reattaching the databases is generally the lowest-impact action.

MANAGEMENT STUDIO

DBAs spend a lot of time in SQL Server Management Studio. This tool enables you to perform most of your management tasks and to run queries. SQL Server 2012 uses a version of Visual Studio 2010 as its shell. Because this is a professional-level book, it won’t go into every aspect of Management Studio, but instead covers some of the more common and advanced features that you might like to use for administration.

Reports

One of the most impressive features of the SQL Server management environment is the integrated reports available in each area of administration. These standard reports are provided for the server instances, databases, logins, and Management tree item. Each runs as a Reporting Services report inside of SQL Server Management Studio. Server-level reports give you information about the instance of SQL Server and the operating system. Database-level reports drill into information about each database. You must have access to each database you want to report on, or your login must have enough rights to run the server-level report. You also have the capability to write custom reports and attach them to many other nodes in the Object Explorer window.

Server Reports

You can access server-level reports from the Object Explorer window in Management Studio by right-clicking an instance of SQL Server and selecting Reports from the menu. A report favorite at the server level is the Server Dashboard, which is shown in Figure 4-4. The Server Dashboard report gives you a wealth of information about your SQL Server 2012 instance, including:

FIGURE 4-4

• The edition and version of SQL Server you run
• Anything for that instance not configured to the default SQL Server settings
• The I/O and CPU statistics by type of activity (for example, ad hoc queries, Reporting Services, and so on)
• High-level configuration information such as whether the instance is clustered

Most of the statistical information includes only data gathered since the last time you started SQL Server. For example, the Server Dashboard provides a few graphs that show CPU usage by type of query. This graph is not historical; it shows you the CPU usage only for the period of time that SQL Server has been online. Use caution when extrapolating information from this aspect of the Server Dashboard. Always keep in mind that what you see is a time-sensitive snapshot of server performance, not its entire history.

Database Reports

Database reports operate much like server-level reports. Select them by right-clicking the database name in the Object Explorer window in Management Studio. With these reports, you can see information that pertains to the database you selected. For example, you can see all the transactions currently running against a database, users being blocked, or disk utilization for a given database, as shown in Figure 4-5.

FIGURE 4-5

Object Explorer Details

The Object Explorer Details Pane provides a wealth of information in a consolidated GUI. You can access this feature in two ways. From the View menu, select Object Explorer Details. Alternatively, F7 opens the pane. If you have highlighted a node in Object Explorer, F7 opens the details for that object, as shown in Figure 4-6. The Synchronize button on the Object Explorer Pane synchronizes Object Explorer to Object Explorer Details.

FIGURE 4-6

The pane can be filtered and customized to your precise needs. Clicking on a column header sorts by that column, and right-clicking on the column header area gives you the opportunity to filter which details display.

Configuring SQL Server in Management Studio

There are a few ways to configure your SQL Server. Earlier you used SQL Configuration Manager. This tool helps you turn on various features and services. Now look at other configuration options and take a more detailed look at sp_configure. For the Database Engine, you have two main methods to configure the instance: the sp_configure stored procedure or the Server Properties screen. To access the Server Properties screen, right-click the Database Engine you want to configure in Management Studio and select Properties. Be careful before altering the configuration of your instance. Adjusting some of these settings could affect your instance’s performance or security. This section describes a few of the more important settings, but more are covered throughout the book.

Another option for configuring database settings is through Policy-Based Management facets. This topic is covered in-depth in Chapter 9, “Change Management.”

Using the Server Properties Screen

Using the Server Properties screen is much more user-friendly than sp_configure, but it doesn’t provide all the options available to you through sp_configure. The following sections go through each screen in detail.

General

The General tab in the Server Properties screen shows you information about your SQL Server instance that cannot be altered, such as the version of SQL Server you currently have installed and whether your instance is clustered. It also provides server information, such as the number of processors and amount of memory on the machine, as shown in Figure 4-7.

FIGURE 4-7

Although your server may have 32GB of RAM available, that doesn’t mean that all that RAM is available to SQL Server. Overhead from the operating system and other processes running on the server also use this available memory.

Memory

On the Memory page of the Server Properties screen, you can see how much memory SQL Server is configured to use. By default, SQL Server is configured to use as much memory as the operating system and the edition of SQL Server enable it to consume. Typically, it is a good idea to set the minimum and maximum amount of memory that your instances use in your environment.

Note the Configured Values and Running Values radio buttons at the bottom of the properties screens as shown in Figure 4-8. When you select Running Values, you see the values that SQL Server currently uses. When you select Configured Values, you see the values that SQL Server will use the next time it restarts. This is necessary because some values do not take effect until after a restart.

FIGURE 4-8

Processors

In the Processors page, you can restrict the SQL Server Engine to use named processors and assign some or all those processors to I/O or threading operations. This is useful typically if you have multiple CPUs and more than one instance of SQL Server. You may have one instance that uses four processors and the other instance use the other four processors. In some cases, when you have a large number of concurrent connections to your SQL Server, you may want to set the Maximum Worker Threads option. Configure this to 0 (the default) to enable SQL Server to automatically and dynamically find the best number of threads to enable on the processor. These threads can manage connections and other system functions, such as performing CHECKPOINTs. Generally, leaving this setting alone gives you optimal performance.

You can select the SQL Server Priority option to force Windows to assign a higher priority to the SQL Server process. Tweaking this setting may be tempting, but you should adjust it only after thorough testing because it may starve other system threads. Use of this option is not generally recommended unless directed by PSS.

Use the Lightweight Pooling option only on the rare occasion when the processor is highly utilized and context switching is extreme. Chapter 12, “Monitoring Your SQL Server,” contains more information on lightweight pooling and how it works.

Security

On the Security page, you can adjust whether your SQL Server accepts connections through both SQL Server and Windows Authentication or Windows Authentication only. This same question is asked during setup of the instance, and this screen gives you another opportunity to change the setting. Under the Login Auditing section, you should always have at least Failed Logins Only selected. This enables SQL Server to audit when someone mistypes a password or is trying to force their way into the instance. (Chapter 8 talks much more about the other security settings on this page.)

Connections

On the Connections page, you can adjust the default connection properties. One possible option you may want to set here would be SET NOCOUNT. This setting can prevent the (8 Rows Affected) message from being sent to the client if they do not request it. There is a small performance enhancement by doing this because this message is an additional recordset sent from SQL Server and may be unneeded traffic.

Database Settings

You will not likely change many of the settings on the Database Settings page. The default index fillfactor of 0 is recommended. You may change fillfactors for specific indexes, but probably not the default. This page also includes settings for how long you wait on a tape for backup (specify how long SQL Server waits for a tape) and how long backups are kept before they expire (default backup media retention, in days). You may change them to suit your plan. Decide whether you plan to compress database backups and set the Compress Backup option accordingly, which is highly recommended. Details for these settings are covered in Chapter 17, “Backup and Recovery.”

The last setting on this page enables you to choose the default database and log file locations. The installation default setting places the log and data files on the same drive under the %System Drive%Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERData and Log directories. Unless this SQL Server install is for playing around, do not put your data and log files on the C drive.

A best practice is to separate data and logs on different drives. This enables you to get the most out of your RAID system and ensures proper recovery, as discussed in Chapter 11, “Optimizing SQL Server 2012.”

Advanced

The Advanced page allows you to enable two features: Contained Databases and FILESTREAM. In addition, there is a catch-all area for Miscellaneous settings. In the vast majority of cases, these setting should remain at their defaults. The Network settings include the ability to set a Remote Login Timeout in seconds, which can be used to control how long remote logins can attempt to connect before they timeout. Finally, there is an area to configure Parallelism settings. A Max Degree of Parallelism of 0 means that all processors will be available for parallel queries.

Permissions

The Permissions page shows each of the logins and roles available on the instance. From here, you can explicitly grant or deny very granular permissions for each login or role from the Explicit tab. In addition, the Effective tab shows you which of these permissions are currently granted to the login or role.

Using sp_configure

sp_configure is a stored procedure that enables you to change many of the configuration options in SQL Server. Some of the more commonly adjusted settings include:

• Cost threshold for parallelism: Use to mitigate parallelism on lower costing transactions to reduce the need to modify the max degree of parallelism.
• Max degree of parallelism: For OLTP environments, this is usually set to the number of available sockets.
• CLR enabled: Enables CLR procedures to execute.
• Blocked processes threshold: Use to set time threshold before blocked process reports are generated.

When you run the stored procedure with no parameters, it shows you the options and their current settings. By default, only the basic, or commonly used, settings are returned, of which there are 15. To see additional options, you need to configure the instance to display advanced options. You can do so by running sp_configure, as shown here:

 sp_configure 'show advanced options', 1;
 RECONFIGURE;

The change does not take effect until the RECONFIGURE command is issued. SQL Server does a check for invalid or not recommended settings when you use sp_configure. If you have provided a value that fails any of these checks, SQL Server warns you with the following message:

 Msg 5807, Level 16, State 1, Line 1
 Recovery intervals above 60 minutes not recommended. Use the RECONFIGURE WITH
 OVERRIDE statement to force this configuration.

Issuing the RECONFIGURE command results in a complete flush of the plan cache. This means that nothing remains in cache, and all batches are recompiled when submitted for the first time afterward. Be cautious of using sp_configure in a production environment and make sure that you understand the impact of the cache flush on your database.

This gives you an opportunity to reconsider what may have been a bad choice, such as setting a memory option to more memory than exists on the box. The following code shows you how to issue the override for the setting:

 EXEC sp_configure 'recovery interval', 90;
 RECONFIGURE WITH OVERRIDE;
 GO

Filtering Objects

You can also filter objects in Management Studio by following a few easy steps, which is useful when you begin to have dozens of objects:

1. Select the node of the tree that you want to filter, and click the Filter icon in the Object Explorer.

2. The Object Explorer Filter Settings dialog (shown in Figure 4-9) opens; here you can filter by name, schema, or when the object was created.

FIGURE 4-9

3. Use the Operator drop-down box to select how you want to filter, and then type the name in the Value column.

Error Logs

As you probably have already experienced, when something goes wrong with an application, one of the factors that you must consider is the database. It is up to the DBA to support the troubleshooting effort and to confirm that the database isn’t the source of the problem. The first thing the DBA typically does is connect to the server and look at the SQL Server instance error logs and then the Windows event logs.

In SQL Server 2012, you can quickly look through the logs in a consolidated manner using Management Studio. To view the logs, right-click SQL Server Logs under the Management tree, and select View SQL Server and Windows Log. This opens the Log File Viewer screen. From this screen, you can check and uncheck log files that you want to bring into the view. You can consolidate logs from SQL Server, Agent, and the Windows Event Files, as shown in Figure 4-10.

FIGURE 4-10

In some situations, you may want to merge the logs from several machines into a single view to determine what’s causing an application problem. To do this, click the Load Log button, and browse to your .LOG file. That file could be a Windows error log that has been output to .LOG format or a SQL log from a different instance. For example, you can use this to consolidate all the SQL logs from every instance on a single server to give you a holistic view of all the physical machines’ problems.

Activity Monitor

The Activity Monitor gives you a view of current connections on an instance. You can use the monitor to determine whether you have any processes blocking other processes. To open the Activity Monitor in Management Studio, right-click the Server in the Object Explorer, and then select Activity Monitor.

The tool is a comprehensive way to view who connects to your machine and what they do. The top section shows four graphs (Show Processor Time, Waiting Tasks, Database I/O, and Batch Requests/Sec) that are commonly used performance counters for the server. There are four lists under the graphs: Processes, Resource Waits, Data File I/O, and Recent Expensive Queries. In all these lists, you can also apply filters to show only certain hosts, logins, or connections using greater than a given number of resources. You can also sort by a given column by clicking the column header.

On the Process Info page (shown in Figure 4-11), you can see each login connecting to your machine (also called a Server Process ID, or SPID). It’s easy to miss how much information is in this window. You can slide left to right to see loads of important data about each connection. When debugging, most of the following columns are useful:

FIGURE 4-11

• Session ID: The unique number assigned to a process connected to SQL Server. This is also called a SPID. An icon next to the number represents what happens in the connection. If you see an hourglass, you can quickly tell that the process is waiting on or is being blocked by another connection.
• User Process Flag: Indicates whether internal SQL Server processes are connected. These processes are filtered out by default. You can change the value to see the SQL Server internal processes by clicking the drop-down and selecting the appropriate value.
• Login: The login to which the process is tied.
• Database: The current database context for the connection.
• Task State: Indicates whether the user is active or sleeping.
  • Done: Completed.
  • Pending: The process is waiting for a worker thread.
  • Runnable: The process has previously been active, has a connection, but has no work to do.
  • Running: The process is currently performing work.
  • Suspended: The process has work to do, but it has been stopped. You can find additional information about why the process is suspended in the Wait Type column.
• Command: Shows the type of command currently being executed. For example, you may see SELECT, DBCC, INSERT, or AWAITING COMMAND here, to name a few. This won’t show you the actual query that the user executes, but it does highlight what type of activity is being run on your server. To see the actual command, select a row in this table, right-click, and choose Details.
• Application: The application that is connecting to your instance. This can be set by the developer in the connection string.
• Wait Time (ms): If the process is blocked or waiting for another process to complete, this indicates how long the process has been waiting, in milliseconds; it can have a value of 0 if the process is not waiting.
• Wait Type: Indicates the event you are waiting on.
• Wait Resource: The text representation of the resource you are waiting on.
• Blocked By: The Session ID (SPID) that is blocking this connection.
• Head Blocker: A value of 1 means the Blocked By Session ID is the head of the blocking chain; otherwise it’s 0.
• Memory Use (KB): The current amount of memory used by this connection, represented by the number of kilobytes in the Procedure cache attributed to this connection. This was reported in pages prior to SQL Server 2008.
• Host: The login’s workstation or server name. This is a useful item, but in some cases you may have a Web server connecting to your SQL Server, which may make this less important.
• Workload Group: The name of the Resource Governor workload group for this query.

By right-clicking any process, you can see the last query run with the connection, trace the process using Profiler, or kill the connection. You can also right-click over the graph area and select Refresh to manually refresh the data or click Refresh Interval to set how often Activity Monitor refreshes. The default refresh rate is 10 seconds. Don’t set the refresh to anything too frequent because it can affect system performance, constantly running queries against the server.

Another important function you can perform with the help of Activity Monitor is identifying a locking condition. The following steps explain how to set up a blocked transaction and how to use activity monitor to resolve this issue.

1. Run the following query in one query window while connected to the AdventureWorks database, and ensure you back up the AdventureWorks database before performing these steps:

 BEGIN TRAN
 DELETE FROM Production.ProductCostHistory
 WHERE ProductID = 707;

This query was intentionally not committed. In other words, there is a BEGIN TRAN command but no ROLLBACK or COMMIT command. This means that the rows deleted from Production.ProductCostHistory are still locked exclusively.

2. Next, without closing the first window, open a new query window, and run the following query:

SELECT * FROM Production.ProductCostHistory;

This query should hang up and wait on the DELETE from Production.ProductCostHistory because the first transaction locks row one. Do not close either window. At the top of each query window, your session ID displays in parentheses, and at the bottom your login displays. If you cannot see the SPID in the tab at the top, hover your mouse over the tab, and a small window pops up showing the entire tab title, which includes the SPID. While the query windows are open, go ahead and explore the Activity Monitor to see what these connections look like (Figure 4-12).

FIGURE 4-12

3. Open the Activity Monitor and note that one connection has a task state of Suspended. This is the query that is trying to do the SELECT. You can confirm this by comparing the session ID of the suspended session with the session ID of your query. Your wait type will be LCK_M_S, which means you are waiting on a shared lock for reading. If you hover the mouse over the Wait Resource column value, you can see more detailed information about the locks, including the object IDs of the resources. In the Blocked By column, you can also see the session ID of the process that blocks you, and it should match the SPID of your first query. You have the option to kill the blocking process; to do so, select the row for the blocking process, right-click, and choose Kill Process.

4. While the locks still exist, take a look at a standard blocking report that comes with SQL Server 2012. In Object Explorer, right-click on your server name, select Reports, Standard Reports, and choose Activity -All Blocking Transactions. You can see the report shown in Figure 4-13.

FIGURE 4-13

5. Now, back at the Activity Monitor, hover over the Wait Resource column to see the mode=X. This mode means that there is an exclusive lock on that resource. An exclusive lock means that no one else is allowed to read the data cleanly. If you see a request mode of S, then SQL Server has granted a shared lock, which in most situations is harmless, and others are allowed to see the same data. To request a dirty read of the uncommitted data, add a WITH (NOLOCK) clause like so:

 SELECT *
 FROM Production.ProductCostHistory
 WITH (NOLOCK);

The (NOLOCK) query hint should rarely, if ever, be used in a production environment. If you find that (NOLOCK) is used regularly in your application, find out why dirty reads are necessary, and if they are, address that root issue first before resorting to query hints. One option to consider as an alternative to (NOLOCK) if dirty reads are absolutely necessary is to set the transaction isolation level to SNAPSHOT.

6. This query returns the data. Before you leave this section, execute the following SQL in the query window that contains the DELETE statement:

 ROLLBACK TRAN;

MONITORING PROCESSES IN T-SQL

You can also monitor the activity of your server via T-SQL. Generally, DBAs prefer this as a quick way to troubleshoot long-running queries or users who complain about slow performance. DBAs typically prefer T-SQL because the information you can retrieve is more flexible than the Activity Monitor.

sp_who and sp_who2

The sp_who stored procedure returns what is connecting to your instance, much like the Activity Monitor. You’ll probably prefer the undocumented sp_who2 stored procedure, though, which gives you more verbose information about each process. Whichever stored procedure you use, they both accept the same input parameters. For the purpose of this discussion, we go into more detail about sp_who2. Just keep in mind that sp_who shows a subset of the information.

Database administrators who are in the know have graduated from sp_who2 to a tool called sp_whoisactive. This widely used procedure was developed by Adam Machanic, a SQL Server MVP and Boston-based independent consultant. If you’re not familiar with it, download it, and read Adam’s blog posts about what it can do to vastly improve your monitoring and troubleshooting efforts. A download file and installation instructions are available at http://tinyurl.com/WhoIsActive. While you’re at it, check out Adam’s blog series, “A Month of Activity Monitoring”, that explains exactly why you should begin using sp_whoisactive straightaway at http://tinyurl.com/WhoIsActiveDocs.

For the most part, this tool is freely offered to the community, with a few exceptions: http://bit.ly/WhoIsActiveLicensing.

To see all the connections to your server, run sp_who2 without any parameters. This displays the same type of information in the Activity Monitor. You can also pass in the parameter of 'active' to see only the active connections to your server, like so:

 sp_who2 'active';

Additionally, you can pass in the SPID, as shown here, to see the details about an individual process:

 sp_who2 55;

Although the example uses SPID 55, your process IDs may be different and can be obtained from the Current Activity listing discussed earlier.

sys.dm_exec_connections

The sys.dm_exec_connections dynamic management view (DMV) gives you even more information to help you troubleshoot the Database Engine of SQL Server. This DMV returns a row per session in SQL Server. Because it’s a DMV, it displays as a table and enables you to write sophisticated queries against the view to filter out what you don’t care about, as shown in the following query, which shows only user connections that have performed a write operation:

 SELECT * FROM
   sys.dm_exec_sessions 
 WHERE is_user_process = 1
 AND writes > 0;

In addition to the information shown in the methods described earlier to view processes, this DMV indicates how many rows the user has retrieved since opening the connection, and the number of reads, writes, and logical reads. You can also see in this view the settings for each connection and what the last error was, if any.

sys.dm_exec_sql_text

You can use the sys.dm_exec_sql_text dynamic management function (DMF) to retrieve the text of a particular query. This can be used in conjunction with the sys.dm_exec_query_stats dynamic management view to retrieve the top 10 most poorly performing queries across all databases. Listing 4-1 retrieves the number of times a query has executed, the average runtime by CPU and duration, and the text for the query.

LISTING 4-1: Top10WorstPerformingQueries.sql

SELECT TOP 10 execution_count as [Number of Executions], 
  total_worker_time/execution_count as [Average CPU Time],
   Total_Elapsed_Time/execution_count as [Average Elapsed Time],
   ( 
     SELECT SUBSTRING(text,statement_start_offset/2,
       (CASE WHEN statement_end_offset = -1 
              THEN LEN(CONVERT(nvarchar(max), [text])) * 2
      ELSE statement_end_offset END - statement_start_offset) /2)
     FROM sys.dm_exec_sql_text(sql_handle)
   ) as query_text
FROM sys.dm_exec_query_stats 
ORDER BY [Average CPU Time] DESC;

The sys.dm_exec_query_stats DMV, also, shows a great deal of other information that you can use. It shows you a line for each query plan that has been run. You can take the sql_handle column from this DMV and use it in the sys.dm_exec_sql_text function. Because this view is at a plan level, when someone changes some of the query’s text, it shows the new query as a new line.

These are just a few examples of how processes can be monitored in T-SQL. With the introduction and widespread adoption of Dynamic Management Objects, you have more options than ever to pinpoint database issues using T-SQL.

MULTISERVER MANAGEMENT

In this age of expanding data, the number of databases that need to be managed is proliferating as well. SQL Server DBAs often have to administer a large number of instances and databases. Something as simple as adding a new login or setting a server option can become quite an endeavor if it must be done on dozens of servers. Luckily, Microsoft has provided a few tools to ease the DBA’s life.

If you are one of the growing number of database administrators who manages multiple servers, make some time to learn PowerShell. This language is a powerful tool that you can leverage to create reusable scripts that can consistently perform tasks across multiple servers, instances or databases.

Central Management Servers and Server Groups

SQL Server 2008 introduced a new feature intended to ease your life: central management servers and server groups. This feature enables you to run T-SQL scripts and apply policy-based management (PBM) policies to a group of servers at the same time. PBM is covered in Chapter 10, “Configuring the Server for Optimal Performance,” but for now, take a look at executing T-SQL on multiple servers.

You may execute T-SQL on a group of servers and aggregate the results into a single result set or keep each result set separate. When you aggregate the result sets, you have the option to include an extra column that indicates from which server each row is returned. You can use this tool to do any multiserver administration and much more. If you have common error tables on each server, you can query them all in a single statement.

These capabilities are part of SQL Server Management Studio (SSMS), but before you start using them all, you must first register a central management server. In the Registered Servers dialog of SSMS, right-click and select Register Central Management Server. You can choose a configuration server from the resulting dialog. This server keeps metadata and does some of the background work for you.

After you create a central management server, you can create server groups and add server registrations to groups under the registration server. After you set up your servers, you may right-click anything — from the registration server to server groups or individual servers in groups — and select New Query, Object Explorer, or Run Policy. Then choose New Query, add T-SQL, and run the query against all the servers in the group.

Create groups of servers based on common management needs. A couple of options are to organize them by environment, such as development, QA, and production or to organize them by subject area, such as Human Resources, Accounting, and Operations. Carefully consider what management tasks are generally performed together and use that as your grouping criteria.

Following are a few items about your registration server:

• It may not be a registered group under itself.
• All queries are executed using trusted connections.
• If your servers cross domains, you must have trusted relationships between them.
• Multiple central management servers can be configured.

To set options for multiserver result sets, select Tools Options Query Results SQL Server Multi-server results.

SQL Server Utility

In SQL Server 2008 R2, Microsoft continued to reinforce its commitment to creating tools to support consolidated server management by introducing SQL Server Utility. This tool enables you to set up a server as a Utility Control Point (UCP) to manage multiple instances of SQL Server from a single location.

The benefits of implementing SQL Server Utility include the following:

• Monitor CPU and disk usage of all instances.
• Monitor utilization to identify under- and over-utilized resources.
• Set up health policies for groups of instances or for single instances.
• Manage monitoring policies for one or more of the instances you manage.

More information about SQL Server Utility can be found online at http://msdn.microsoft.com/en-us/library/ee210548(v=sql.110).aspx.

TRACE FLAGS

Trace flags give you advanced mechanisms to tap into hidden SQL Server features and troubleshooting tactics. In some cases, they enable you to override the recommended behavior of SQL Server to turn on features such as network-drive support for database files. In other cases, you can use trace flags to turn on additional monitoring. There is a set of flags that help you diagnose deadlocks, including trace flag 1204. To turn on a trace flag, use the DBCC TRACEON command, followed by the trace you’d like to turn on, as shown here:

 DBCC TRACEON (1204)

To turn off the trace, use the DBCC TRACEOFF command. This command is followed by which traces you’d like to turn off (multiple traces can be separated by commas), as shown here:

 DBCC TRACEOFF (1204, 3625)

Trace flag 3625 used in the previous code snippet limits the amount of information returned to users who are not members of the sysadmin server role by masking the parameters of some error messages. This can be enabled as a security measure.

When you turn on a trace, you are turning it on for a single connection by default. For example, if you turn on trace flag 1224, which disables lock escalation based on the number of locks, lock escalation is disabled only in the scope of the connection that issued the DBCC TRACEON command. You can also turn on the trace at a server level by issuing the command followed by the -1 switch, as in the following:

 DBCC TRACEON (1224, -1)

After you turn on the traces, you’re probably going to want to determine whether the trace is actually running. To do this, you can issue the DBCC TRACESTATUS command. One method to issue the command is to interrogate whether a given trace is running, like so:

 DBCC TRACESTATUS (3635)

This command would return the following results if the trace is not turned on:

 TraceFlag Status Global Session
 --------- ------ ------ -------
 3625      0      0      0
 
 (1 row(s) affected)

If you want to see all traces that apply to the connection, run the following command with the -1 parameter:

 DBCC TRACESTATUS (-1)

As shown in the following results of this query, two traces are turned on. Trace flag 1224 is turned on globally for every connection into the SQL Server, and trace flag 3625 is turned on for this session:

 TraceFlag Status Global Session
 --------- ------ ------ -------
 1224      1      1      0
 3625      1      0      1
 
 (2 row(s) affected)

If no traces are turned on, you would receive only the following message:

 DBCC execution completed. If DBCC printed error messages, contact your system
 administrator.

Your instance of SQL Server should not have trace flags turned on indefinitely, unless you have been instructed by Microsoft Product Support to do so. When left to run all the time, trace flags may cause your instance to behave abnormally. Moreover, the flag you use today may not be available in a future release or service pack of SQL Server. If you are in debug mode, you can turn on a trace flag from the command prompt when starting SQL Server. As mentioned earlier in this chapter, you can also start a trace when SQL Server starts at the command prompt by calling the sqlservr.exe program and passing the -T switch after it.

There is a lot to say about trace flags even though only a few are mentioned here, but as you proceed through this book, you see a number of other trace flags in practice.

Some functionality provided by trace flags, such as deadlock monitoring, can be more efficiently implemented by using Extended Events. See Chapter 12, “Monitoring Your SQL Server” for a complete discussion of implementing Extended Events in your environment.

GETTING HELP FROM SUPPORT

Whenever you get stuck on a SQL Server issue, generally you call the next layer of support. Whether that next layer is Microsoft or a vendor, a number of new tools are available to communicate with that next layer of support. Use the SQLDumper.exe and SQLDiag.exe programs to better communicate with support to give them an excellent picture of your environment and problem while you reproduce the error.

SQLDumper.exe

Beginning in SQL Server 2000 SP3, SQLDumper.exe was included to help your SQL Server perform a dump of its environment after an exception occurs. A support organization, such as Microsoft’s Product Support Services (PSS), may also request that you execute the program on demand while you have a problem such as a hung server.

If you want to create a dump file on demand, you need the Windows process ID for the SQL Server instance. You can obtain this ID in a few ways. You can either go to Task Manager and look in the SQL Server log, or go to SQL Server Configuration Manager, covered earlier in the chapter. On the SQL Server 2012 Services page of Configuration Manager, you can see each of the SQL Server services and the process ID.

By default, SQLDumper.exe can be found in the C:Program FilesMicrosoft SQL Server110Shared directory because it is shared across all the SQL Server instances installed on a server. This directory may vary, though, based on where you installed the SQL Server tools. To create a dump file for support, go to a command prompt, and access the C:Program FilesMicrosoft SQL Server110Shared directory. As with many command-line utilities, you can see the options by running the following command, and get more details about them from the SQL Server documentation:

 SQLdumper.exe -?

When you are at the command line, you can create a full dump or a minidump. If a minidump is less than a megabyte, a full dump may run 110MB on your system. To create a full dump, use the following command:

 Sqldumper.exe <ProcessID> 0 0x1100

<ProcessID> is the Process ID of your SQL instance. This outputs the full dump to the same directory that you’re in. The filename is called SQLDmpr0001.mdmp if this is the first time you’ve run the SQLDumper.exe program. Filenames are sequentially named after each execution. You cannot open the dump file in a text editor such as Notepad. Instead, you need advanced troubleshooting tools such as Visual Studio or one of the PSS tools. A more practical dump would be a minidump, which contains most of the essential information that product support needs. To create a minidump, use the following command:

 Sqldumper.exe <ProcessID> 0 0x0120

You can view the SQLDUMPER_ERRORLOG.log file to determine whether there were any errors when you created the dump file or whether a dump occurred. You need to be a local Windows administrator to run SQLDumper.exe or be logged in with the same account that starts the SQL Server service.

SQLDiag.exe

A tool that’s slightly less of a black box than SQLDumper.exe is SQLDiag.exe. This tool consolidates and collects information about your system from several sources:

• Windows System Monitor (sysmon)
• Windows event logs
• SQL Server Profile traces
• SQL Server error logs
• Information about SQL Server blocking
• SQL Server configuration information

Because SQLDiag.exe gathers so much diagnostic information, you should run it only when you’re requested to or when you prepare for a call with support. The SQL Server Profiler trace files alone can quickly grow large, so prepare to output these files to a drive that has a lot of space. The process also uses a sizable amount of processing power as it runs. You can execute the tool from a command prompt or as a service; you can use the -? switch to see available switches.

The SQL Nexus Tool, available from Codeplex at http://sqlnexus.codeplex.com can help you read and analyze SQLDiag output more efficiently.

SQLDiag.exe can take a configuration file as input. By default, this file is called SQLDiag.Xml, but you can pass in a different filename. If a configuration XML file does not exist, one will be created called ##SQLDiag.XML. This file can be altered to your liking and then later distributed as SQLDiag.XML.

Now that you know what SQLDiag.exe can do, follow this example to use the tool against a local development server. If you cannot get in front of the server, use a support tool such as Terminal Services to remote into a server because you can’t point SQLDiag.exe at a remote instance.

1. To run the tool, go to a command prompt.

2. Because the SQL install adds the appropriate directory to the PATH environment variable, you don’t need to go to the individual directory where the file is located. Instead, go to the C:Temp directory or something similar to that on a drive that has more than 100MB available.

3. The default location for the executable file is C:Program FilesMicrosoft SQL Server110ToolsBinnSQLDIAG.EXE, but you can alter that to a new location with the /O switch.

4. Type the following command (note the lack of spaces after the + sign):

 sqldiag /B +00:03:00 /E +00:02:00 /OC:	emp /C1

This command instructs SQLDiag.exe to begin capturing trace information in 3 minutes from when you press Enter and run for 2 minutes. This is done with the /B and /E switches. You can also use these two switches to start and stop the diagnostic at a given 24-hour clock time. The command also tells SQLDiag.exe to output the results of the traces and logs to the C:Temp directory, and the /C switch instructs the tool to compress the files using Windows compression. If you were running this in your environment, you would wait until you were instructed by SQLDiag.exe (in green text on your console) to attempt to reproduce the problem. In Figure 4-14, SQLDiag collects to the default directory. The results look something like what is shown in Figure 4-14. Enter CTRL+C, if you want to terminate the collection of data early.

FIGURE 4-14

5. With the SQLDiag.exe now complete, go to the C:Temp directory to zip the contents up and send them to Microsoft. In the directory, you can find a treasure chest of information for a support individual, including the following:

• ##files.txt: A list of files in the C:Program FilesMicrosoft SQL Server110Toolsinn directory, with their creation date. Use this to determine whether you’re running a patch that support has asked to be installed.
• ##envvars.txt: A list of all the environment variables for the server.
• SERVERNAME__sp_sqldiag_Shutdown.OUT: A consolidation of the instance’s SQL logs and the results from a number of queries.
• log_XX.trc: A series of Profiler trace files of granular SQL Server activities being performed.
• SERVERNAME_MSINFO32.TXT: A myriad details about the server system and hardware.

These files are not only useful to support individuals. You also may want to consider running this on a regular basis to establish a baseline of your server during key times (before patches, monthly, or whatever your metric is). If you decided to do this, you wouldn’t want the Profiler part of SQLDiag.exe to run for more than a few seconds. You can gather useful baseline information if the tool periodically runs in snapshot mode. This mode performs the same functions just described but exits immediately after it gathers the necessary information. The following command uses the /X switch to run SQLDiag.exe in snapshot mode and the /N switch (with 2 as the option) to create a new directory for each run of SQLDiag.exe:

 sqldiag /OC:	empaseline /X /N 2

The first directory created is called baseline_0000, and each new one is named sequentially after that. Many corporations choose to run this through SQL Agent or Task Manager on the first of the month or before key changes to have an automatic baseline of their server and instance.

SUMMARY

One of the most important things to remember when using SQL server is managing and troubleshooting your SQL Server. The key concepts for doing so are proper configuration, ongoing monitoring, and efficient troubleshooting. Methods for configuration include SQL Server Configuration Manager, startup parameters, and startup stored procedures. It is also important to use Management Studio and T-SQL to actively monitor your databases. The DAC provides an excellent way to connect to your instance and troubleshoot when issues arise. When you’ve exhausted your troubleshooting options, know how to provide the information that product support needs by using SQLDumper.exe and SQLDiag.exe. In the next chapter, you learn ways to automate SQL server.