C H A P T E R 7
SQL Server Internals
SQL Server 2012 is a powerful and complex database platform. The relational engine itself is composed of many subsystems. One of these is the command parser, which takes the input from the connection, validates it, and parses it into an internal structure called a query tree. Another is the Query Optimizer, which takes the query tree and prepares the statement for execution. These preparation tasks include ensuring the user executing the query has enough access to the underlying database objects within the query and optimizing the query with respect to performance. Query optimization is cost based, and SQL Server chooses the execution plan that is best for the given statement.
As you can see, executing a query is complex, and many things are going on under the hood. As a database administrator, you can affect the performance of some of these functions based on what you find in a query’s execution plan. You will learn more about execution plans in Chapter 13.
Even though you may optimize the execution of a query, the actual performance of the hardware that SQL Server uses is an important piece of the performance puzzle. In this chapter, you will learn the architecture of SQL Server databases, how SQL Server writes data to the physical disk, and important things about the services that are installed.
Databases
When you install SQL Server, five databases are automatically attached. Four of these are visible using SSMS, and the fifth one, the resource database, is invisible. The five databases and their definitions are as follows.
Master Database
The master database stores instancewide information, such as logins, endpoints, linked server information, and the server configuration. This database also contains the definition of all the databases that are attached to the instance of SQL Server. Needless to say, if the master database is corrupt or unavailable, SQL Server cannot be available for users.
Tempdb Database
SQL Server uses the tempdb database as a work area for grouping and sorting options, cursors, and other operational tasks. As you can imagine, these tasks consume a lot of disk I/O, so if your user data and tempdb are located on the same disk, there could be a lot of contention. For this reason, as a best practice, consider moving tempdb to its own set of physical disks.
Model Database
The model database is used as a template for new databases. Any objects created within this database, or database options that you set within it, will also exist on every new database that you later create.
The model database is required and cannot be deleted, because it is used by tempdb every time the SQL Server service starts. To see how the model database works, create a table in the model database using the following code:
USE [model]
GO
CREATE TABLE Sales
(i INT)
GO
USE [master]
GO
CREATE DATABASE SmartSalesDB
GO
Now, that you have created the new database, take a look at the tables within the SmartSalesDB database, as shown in the following code:
USE [SmartSalesDB]
GO
SELECT name,type_desc FROM sys.tables
GO
The results for this query are as follows:
name type_desc
Sales USER_TABLE
Here, the Sales table was created automatically, because it was defined in the model database.
MSDB Database
MSDB is used to store information for various components of SQL Server. Some uses include database backup and restore history, maintenance plans, and maintenance plan history as well as information about the SQL Server Agent job-scheduling service.
Depending on your implementation of SQL Server, MSDB might be your most heavily used system database outside of master. With any user-defined database, any time you make changes, you need to back up the database. MSDB is no exception, since actions such as changes to SQL Server Agent jobs, changes to policy management, or the creation of an Integration Services package will change MSDB.
Another important note is that there are special database roles defined in MSDB to support the various components. For example, SQLAgentReaderRole allows users in this role to view job execution information for all jobs but have full control only for the jobs the users own. Since so many components leverage this database, take caution about adding users to it.
Resource Database
You cannot, by default, see the resource database in SSMS. The resource database stores all system procedures, catalog views, functions, and other system-related objects in a read-only mode. Each instance of SQL Server has one mssqlsystemresource.mdf file and one mssqlsystemresource.ldf file located in the Binn folder of the SQL Server instance. You do not need to worry about backing up these files, because the only way they change is when a hotfix or service pack is installed.
Repairing Corrupt System Databases
When a user-defined database has an issue, the solution is straightforward: restore the database. When a system database has an issue, things can be more challenging. The SQL Server Books Online article called “Rebuild System Databases” does a good job of explaining the process of re-creating system; you can find it at http://msdn.microsoft.com/en-us/library/dd207003(v=sql.110).aspx. In summary, the setup.exe command-line executable has a switch that allows you to re-create the system databases. When you re-create the system databases, you will have to restore databases such as model and MSDB from backup to return to the last good state.
Writing Data to Disk
When SQL Server writes data to disk, it writes to one or more data files. There are two kinds of database files: data files and log files. As part of the commitment of a transaction, data is written to the transaction log file. This log file is a string of log records consisting of events such as modifying data, creating and dropping tables and indexes, and other transactional information. The log file is very useful in disaster recovery. If something bad happened when a user was in the middle of a transaction on server reboot, SQL Server would recover the database up until the last good transaction and roll back the last corrupt or incomplete transaction. Log files are traditionally given an .ldf extension. Database performance has a lot to do with the amount of data that SQL can write to the transaction log.
A data file with an .mdf file extension is the primary data file. If you have additional data files called secondary data files, these have .ndf file extensions. Data files contain data and metadata such as indexes and other database objects.
Note Nothing requires you to use the .mdf, .ndf, and .ldf file extensions. You can use whatever extensions you want. However, these are customary to SQL Server.
In a perfect world, you would load all your data into memory and forget the whole disk issue. Disks provide cheaper long-term storage and a much larger capacity than memory. Most of all, they store data in a persistent state. Generally, the more disks you have, the better performance you will get. However, this is true only to a certain point where the capacity of the hard disk controller or host bus adapter is reached. When objects are created in the database, they are written to a filegroup. A filegroup is an abstraction of underlying database files. For example, consider the following CREATE DATABASE statement:
CREATE DATABASE [Sales] ON PRIMARY
( NAME = N'Sales', FILENAME = N'C:DATASales.mdf' ),
FILEGROUP [OlderSales]
( NAME = N'Sales2', FILENAME = N'D:DATASales2.ndf'
LOG ON
( NAME = N'Sales_log', FILENAME = N'M:LOGSales_log.ldf' )
GO
In this statement, Sales is a database file that is located on the primary filegroup on the C drive. The Sales2 data file is located on the OlderSales filegroup. This filegroup is on the D drive. The log is called Sales_log and is located on the M drive.
Once these filegroups are configured, you can tell SQL Server where to create your database objects. To create a new table in the OlderSales filegroup, you can issue the following query:
CREATE TABLE Customers
(customer_id INT PRIMARY KEY)
ON [OlderSales]
You can also add more files within the same filegroup. If more files are added, SQL Server will stripe the writes across all files within the filegroup, creating an even write pattern. Depending on the kind of hard drives you have in your configuration, striping the writes could be a performance improvement. Another technique used to increase performance with respect to improving disk I/O is table partitioning. Table partitioning descibes the situation where portions of the table reside on physically different areas, but to the application or user, they appear as one single table. By moving parts of the table to different physical disks, SQL Server can parallelize the query and obtain the results more quickly.
Note Filegroups have a default property that, when set to true, will act as a container for all the objects created that don’t explicitly define a filegroup.
SQL Server Services
When you install SQL Server, you can have about ten services installed, depending on the options you selected. Some of these services are one per server, and some can be installed multiple times on the same server. By server, I mean an instance of the Windows Server operating system.
The following are the services that you might encounter:
SQL Full-Text Engine Filter Daemon Launcher: The SQL Full-Text Engine Filter Daemon Launcher service is installed once per instance of SQL Server. Within the SQL Server database engine is the powerful Full-Text Engine. This engine makes it easy for users and applications to search for keywords within the database. The Full-Text Engine has two roles: indexing support and querying support. This service spawns a process called
fdhost.exe, which works with the word breaker functionality within Full-text Search. This service is necessary only when using the full-text features of SQL Server.SQL Server: The SQL Server service can be installed one or more times within Windows Server. Each time a SQL Server service is installed, it’s given another name called an instance name. To connect to a named instance of SQL Server, you would use the format
<servername>/<instance name>. This service is for the relational database engine itself.SQL Server Browser: The SQL Server Browser service is responsible for enumerating the available instances and port numbers back to the client that is requesting the information. There is one SQL Server Browser service per Windows Server. This service is beneficial when you have multiple named instances, because the alternative is to have clients manually input the port numbers on which the instances are listening.
Note If you try to connect to a named instance and the SQL Browser service is not running, you have to specify the port number within the connection string.
SQL Server Agent: SQL Server Agent is a job-scheduling service coupled with an instance of SQL Server. For every installation of the SQL Server service, there is one installation of the SQL Server Agent. You do not have to use this service; you can keep it disabled, but chances are unless your company has another job-scheduling tool that is the standard, you may end up using SQL Server Agent. SQL Server Agent is used to schedule maintenance jobs such as database backups and index maintenance. You will learn more about SQL Server Agent in Chapter 10.
SQL Server Integration Services 11.0: SQL Server Integration Services is an extract, transform, and load (ETL) platform for SQL Server. ETL tools are used heavily in data warehousing, because their task is to take data from all the disparate sources, transform the data into a common format, and load it into a database for further analysis. This service is installed one time per Windows Server instance, and it enables users to view current package execution status as well as view stored package information.
SQL Server Volume Shadow Copy Service (VSS) Writer: The Volume Shadow Copy Service (VSS) is a set of COM APIs that implement a framework to allow volume backups to be performed while applications such as SQL Server continue to write to the volumes. This service is what enables SQL to operate in the Volume Shadow Copy Service framework. There is one SQL Server VSS Writer service per Windows Server.
SQL Server Reporting Services: Reporting capabilities are heavily used in both business intelligence applications and the daily life of database administrators. SQL Server Reporting Services provides the core reporting services functionality, including hosting the Reporting Services service, report creation, and scheduling. You can install multiple instances of the report server on the same operating system.
SQL Server Analysis Services: When data is transformed and loaded into a database known as the data warehouse, it is primed and ready for analysis by a multidimensional engine such as SQL Server Analysis Services. This service is installed one time per Windows Server and is the workhorse for building multidimensional cubes. These cubes are used for purposes such as reporting and data mining by business analyst users.
SQL Server Distributed Replay Controller: The Distributed Replay Utility allows you to simulate a large-scale workload. In earlier versions of SQL Server, you could capture a SQL trace that contained statements from multiple clients against the database. This trace file could only replay events back to the server serially. To more accurately represent a replay workload, SQL Server 2012 introduced the Distributed Replay Utility feature. The SQL Server Distributed Replay Controller service is part of this feature and helps synchronize the clients in a distributed replay scenario.
SQL Server Distributed Replay Client: The SQL Server Distributed Replay Client service receives and instructions from the SQL Server Distributed Replay Controller service.
Single-User Mode
On a given production database system, the database accepts many requests from many different users. There comes a time when you, as a database administrator, may need to place the database in single-user mode. You may also need to start a database in that mode.
Single-user mode allows only one connection to the database. You would normally use this state when performing special maintenance functions such as issuing a DBCC CHECKDB command with the repair options enabled.
Note As soon as you place a database in single-user mode, any active connections within the database will terminate. Doing so is a really bad idea if users are not informed of this event.
Placing an Already-Started Database into Single-User Mode
To set a database that is already started to single-user mode, you can issue the following statement:
ALTER DATABASE [SmartSalesDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Three options are available when you change to single-user mode. The ROLLBACK IMMEDIATE option means connections will be closed immediately, and noncommitted transactions will be rolled back. If you want to give a delay before you do this, you can specify ROLLBACK AFTER <X> SECONDS, where <X> is an integer value. If you want to place the database only if there are no open transactions, use the NO_WAIT option.
If you want to have more than one connection to the database but need to limit the amount of users connected, you can put the database in a restricted state. This state is ideal for data imports and allows only members of the db_owner, dbcreator, and sysadmin roles to access the database. To set a database in restricted mode, you can issue the following query:
ALTER DATABASE [SmartSalesDB] SET RESTRICTED_USER
GO
Remember to change the database setting back to MULTI_USER once you are ready. You can do this with the following script:
ALTER DATABASE [SmartSalesDB] SET MULTI_USER
GO
Databases can be taken offline when you need to copy or move the database files. This is an extremely rare event, because there are other tools available, such as the Copy Database Wizard in SSMS, to move data and/or databases to new servers. To set a database offline, use the following script:
ALTER DATABASE [SmartSalesDB] SET OFFLINE
GO
Starting SQL Server in Single-User Mode
In some rare occasions, it may be necessary to start the SQL Server instance in single-user mode. If you are trying to recover a damaged master database or any other system database, you will need to put SQL Server in single-user mode. You can do this by starting the service with the -m command-line argument. To place the SQL Server instance in single-user mode, perform the following steps:
- Stop all SQL Server services, and stop SQL Server Agent. You can stop these services through the Services applet in the Control Panel, through the command shell using the
NET STOPcommand or through the SQL Server Computer Manager. For this example, you’ll use the SQL Server Configuration Manager to stop these services. To launch the SQL Server Configuration Manager, select the application from the ProgramsMicrosoft SQL Server 2012
Figure 7-1. The Sql Server Configuration Manager
If you click the SQL Server Services node in the left tree, you will see a list of all the SQL Server–related services installed on your server. To stop the SQL Server service, simply right-click, and select Stop. It is a best practice to also stop the SQL Server Agent service when you want to put SQL Server in single-user mode. To do this, select Stop on the context menu for the SQL Server Agent service.
- Modify the startup parameters. As with starting and stopping the service, there are a few ways to start the service with special parameters. You could navigate to the folder where the
sqlservr.exeexecutable is located and start it by typing sqlserve.exe -m. Alternatively, you could add the-mparameter using the Startup Parameters tab in the Properties window of the SQL Server service. To modify the startup parameters of a service, click the Properties option in the context menu of the SQL Server service. Next, click the Startup Parameters tab. This will display the Startup Parameters tab, as shown in Figure 7-2.
Figure 7-2. Properties dialog box in SQL Server Configuration Manager
The existing parameters define where the master database is located and where SQL Server should write error logs to. To add more parameters, simply add the parameter, in our case, -m to the “Specify a startup parameter” text box, and click Add. Next, click OK, and start the service by selecting Start from the SQL Server service context menu.
To test the new server state, launch SSMS and make a connection to your server. If SSMS has the Object Explorer tree open, try to click the New Query button. This will cause an error dialog box to pop up and show text similar to the following:
Login failed for user 'SERVERNAMEAdministrator'. Reason: Server is in single user mode.
Only one administrator can connect at this time.
When you set SQL Server in single-user mode, it accepts only one connection, and since Object Explorer in SQL Server is one connection, by opening a New Query you are requesting another connection to SQL Server. Thus, you receive the failure message.
Note To use SSMS with SQL Server in single-user mode, make sure only Object Explorer or a Query Editor window is open, but not both.
You can also use the command-line tool called SQLCMD to issue queries against this single-user server. You will learn more about SQLCMD in Chapter 10.
To remove the single-user restriction, go back to the Parameter Properties tab in the Properties dialog box of the SQL Server service in SQL Server Configuration Manager, and remove the -m parameter you added previously. Restart the SQL Server service, and it will allow multiple connections.
Summary
As a database administrator, you will become intimately familiar with all the system databases that are part of SQL Server. These system databases provide critical functionality to users and other components within SQL Server. Knowing special configurations, such as setting databases and the actual server instance to single-user mode, is important in certain rare situations.
When you install SQL Server, depending on the amount of components you select, you will see a bunch of new services within the Services applet in Windows Control Panel. Each of these services adds value to the user experience. Database administrators should be aware of how SQL Server sets up the accounts used for these services. A complete discussion of setting up these services is covered in the SQL Server Books Online article “Setting Up Windows Service Accounts” located at http://technet.microsoft.com/en-us/library/ms143504.aspx.
Table 7-1 lists some resources supporting the content discussed in this chapter.
