Part II: Administering a SQL Server
Chapter 5: Maintaining a SQL Server 2005 Express System
Maintaining a SQL Server
2005 Express System
In This Chapter
䊳Taking advantage of user instances
䊳Customizing your database server’s operation
䊳Monitoring and improving performance
A
lthough SQL Server 2005 Express comes with an attractive price (free!), and a truthful promise of low maintenance requirements, you’re still not completely off the hook with your babysitting responsibilities. In this chap- ter, I show you how to get these chores done with the least amount of effort. To begin, you find out how SQL Server 2005 Express allows you to easily run separate user instances, thereby granting normal database users extra powers within their own workspace. With that out of the way, I show you how to configure the database server, followed by some helpful performance tips.Master of the Database Domain
User instancesare a handy feature offered by SQL Server 2005 Express that let regular users run their own, separate instance of the database server. When you request a user instance, the primary SQL Server 2005 Express service spawns a unique process containing the user instance.
Even though these users are not administrators, within the context of their own instance they have much higher privileges. However, because they’re in their own instance, they can’t damage or otherwise disrupt the parent instance of SQL Server 2005 Express. You enable user instances by setting a configuration parameter, a task that I describe in the next section.
Setting SQL Server 2005
Express Parameters
You may be tempted to think of SQL Server 2005 Express as a monolithic, unchanging application. That would be the wrong assumption, however. In fact, dozens of settings control all sorts of engine behavior, from the number of user connections allowed to how the database interacts with different pro- gramming languages to how it deals with remote connections. As an adminis- trator, you have the power (in most cases) to change these settings at will. In this section, I describe some common parameters, along with how you can easily modify them.
The database engine automatically tunes many of the SQL Server 2005 Express parameters. In some cases, you know better than the engine; in most cases, you’re better off leaving the parameters alone and letting the server do its job. To keep things clear and prevent inadvertent changes, very few of the parameters I describe are self-tuning.
How to configure your server
Like so many of the Microsoft products, SQL Server 2005 Express offers several alternate paths to achieving your goal; in this case, setting database server configuration values. Here are three of the most commonly used options:
⻬The SQL Server Surface Area Configuration utility: This tool, which is available by choosing Start➪All Programs➪Microsoft SQL Server 2005➪
Configuration Tools➪SQL Server Surface Area Configuration, lets you decide which protocols and features to expose. Although it’s tradition- ally used just after you install the product, you could also put it to work when you’re in production.
⻬SQL Server Management Studio Express: You can use this powerful and flexible database management utility (available via download from Microsoft) for all sorts of useful tasks, including configuring and cus- tomizing your server. Figure 5-1 shows a list of all the pages at your dis- posal for viewing and setting database properties. In this case, I’ve selected the Memory page.
⻬The sp_configuresystem stored procedure: No matter what your database configuration, you should have access to the character-based SQLCMD utility. SQLCMD — and sufficient permission — is all you need to run this stored procedure. Because all installations have the ability to run this stored procedure, I focus on it for the balance of this section.
70
Part II: Administering a SQL Server 2005 Express System
First, a word or two about how to launch sp_configure. Just follow these steps and you’ll be ready to make your changes:
1. Launch SQL Server Management Studio Express.
You can get things going by choosing Start➪All Programs➪Microsoft SQL Server 2005➪SQL Server Management Studio Express.
2. Connect to your database server.
3. Click the New Query icon on the Standard toolbar. 4. Run the sp_configurestored procedure.
You need to pass in parameters that indicate the setting you’re modify- ing, along with the value that you want. For example, here’s how to allow remote access:
sp_configure ‘remote access’, ‘1’ go
This setting is binary: Either remote access is on, or it’s off. On the other hand, here’s how to change the numeric value for the remote query timeout setting, expressed in seconds:
sp_configure ‘remote query timeout’, ‘300’ go
If you run sp_configurewithout any parameters, you get a list of all changeable settings. Figure 5-1: Setting memory options with SQL Server Manage- ment Studio Express.
71
Chapter 5: Maintaining a SQL Server 2005 Express System
5. After you run the sp_configurestored procedure, run the RECON- FIGUREcommand for the change to take effect.
Depending on the setting you’ve changed, you may need to restart your database server for the alteration to take effect. SQL Server 2005 Express sends a message telling you what to do next.
Common server parameters
Here are some of the more frequently modified database server parameters. You should note that for most installations, the default values are just fine; you should plan on tuning only if you’re experiencing poor performance or other suboptimal behavior.
Before you embark on your database configuration adventure, note that SQL Server 2005 Express considers some parameters to be advanced, which means that you need to run sp_configurebefore even attempting to make any alter- ations. I describe how to run this stored procedure in the preceding section. Here’s what you need to do to gain access to these advanced parameters:
sp_configure ‘show advanced options’, ‘1’ go
That’s it: You’re now ready to view and change advanced configuration options. With these options now showing, you can now turn your attention to customizing SQL Server 2005 Express to your liking. Here are some of the major functional areas that you can control:
⻬User instances: As I describe earlier in this chapter, user instances let non-administrators have their own running copy of a SQL Server 2005 Express database. In this instance, the user has full privileges. To turn on this feature, be sure that the User Instances Enabled option is set to
1, which is its default.
⻬User connections: If you’re intent on overriding the automatically tuned User Connections parameter, you can use it to stipulate the upper limit of concurrent user connections to your SQL Server 2005 Express data- base server.
⻬Tracing database activity: If you’re the curious type, you can command SQL Server 2005 Express to trace events that happen in your database. These events are then written into a log file. I describe log monitoring in the upcoming “Monitoring performance” section; for now, you can request log file updates by setting Default Trace Enabled to 1.
⻬Configure trigger behavior: In Chapter 15, I show you how to employ triggers to enforce business rules, protect your data, and provide other application customizations. Depending on how you’ve written your trig- gers, they might return a result set(that is, a set of rows generated by the
72
Part II: Administering a SQL Server 2005 Express System
trigger). If your application isn’t equipped to handle the results, you may experience unanticipated (and unwanted) behavior. To prevent these results from gumming up the works, set the Disallow Results from Triggers option to 1.
⻬Control process behavior: Usually, all your applications that work with SQL Server 2005 Express get along just fine. Occasionally, however, two or more processes find themselves in conflict for the same resource at the same time. If you want to be notified when this quarrel happens, try tuning the blocked process threshold setting. Measured in seconds, it can range from 0 all the way up to 86,400.
⻬Allow other programming languages: In Chapter 16, you see how to use Microsoft’s Common Language Runtime feature to leverage additional programming languages to build your stored procedures, functions, and triggers. However, you need to set the CLR Enabled parameter to 1.
⻬Configure remote access settings: If your SQL Server 2005 Express instance is like many others, you probably have remote users and processes connecting to your database server. You have several tunable options at your disposal. Three of the more interesting options are remote access (which determines whether non-local users and processes can con- nect to your server), remote login timeout (which specifies the number of seconds to wait before aborting a login attempt from another computer), and remote query timeout (which sets a delay threshold before SQL Server 2005 Express aborts a query from a remote user or process).
⻬Set user options: You can control the default behavior for users’ SQL Server 2005 Express sessions either individually with the SEToption or globally via the User Options setting. You compose this numeric value by adding unique integers, producing a distinct value. Table 5-1 lists all the user option parameters.
How do you use these numbers? An illustration can make things much clearer. Suppose that you want all users to have the following behavior in their sessions:
• Close open cursors when a transaction commits.
• Don’t report the number of rows affected by any statements. • Rollback a transaction if an error happens.
It’s actually quite simple. For each of these three requirements, consult Table 5-1 and determine the numeric value for that setting. Then, add the three numbers together (4 + 512 + 16384 = 16900 in this case) and run the sp_configurestored procedure as follows:
sp_configure ‘user options’, ‘16900’
Unless you want to change the default behavior for all users, you never need to make these types of requests. In fact, most environments can simply use the out-of-the-box settings offered by SQL Server 2005 Express.
73
Chapter 5: Maintaining a SQL Server 2005 Express System
Table 5-1
Key User Configuration Parameters
Value Purpose
1 Check constraints right now, rather than waiting 2 Enable default transactions for certain types of
connections
4 Close open cursors when a transaction commits 8 Follow the ANSI standard when warning users
about truncation
16 Follow the ANSI standard when padding a fixed- length variable
32 Follow the ANSI standard when encountering a NULL
64 Halt a query should a divide-by-zero occur 128 Return NULLshould a divide-by-zero occur 256 Decide on whether to use a single or double
quote
512 Don’t report on the number of rows affected by a statement
1024 Unless otherwise specified, let new columns accept NULLvalues
2048 Unless otherwise specified, don’t let new columns accept NULLvalues
4096 Respond with NULLif you attempt to combine NULLwith a string
8192 Respond with an error if a math expression loses precision
16384 Rollback a transaction should an error occur
Picking Up the Pace
No matter how fast your database server and applications may be, your users will probably always want things to run more quickly. In this section, I offer you an assortment of recommendations to help squeeze some additional speed out of your SQL Server 2005 Express database.
74
Part II: Administering a SQL Server 2005 Express System
Monitoring performance
Before you can even start on your journey, taking stock of where you are is a good idea. Several tools are at your disposal to help you get a good idea of database and system activity and response levels. These tools belong to two main classes: Operating system and database-specific utilities.
Operating system performance tools
The Windows operating system offers two very helpful utilities that you can use to measure what’s happening on your computer.
⻬Windows Task Manager: If you’ve ever tried to figure out an unex- plained system slowdown, I bet you’re already on good terms with this utility. You can launch it by either right-clicking the taskbar and choos- ing Task Manager, or by pressing the famous Ctrl+Alt+Delete key sequence and clicking the Task Manager button in the dialog box. Figure 5-2 shows the kinds of details that it reports. In this example, I’m tracking the CPU’s workload over time.
⻬Microsoft Management Console (MMC) Performance Snap-in: Unless you’re a professional system administrator or just naturally curious, you probably don’t even know that this utility exists. A general-purpose man- agement application, you can launch its performance-centric view by choosing Start➪Control Panel➪Administrative Tools➪Performance. It offers hundreds of indicators for all sorts of system information. Figure 5-3 shows the SQL Server metrics alone. Each performance object offers its own set of statistics.
Figure 5-2: The Windows Task Manager.
75
Chapter 5: Maintaining a SQL Server 2005 Express System
Curious administrators should explore this great example of statistical gathering software in depth. Because you’re merely gathering informa- tion, you can experiment to your heart’s content, secure in the knowl- edge that you won’t negatively impact your system.
Database performance tools
You can take advantage of the built-in Activity Monitor from SQL Server Management Studio Express. It reports on all kinds of important performance information: ⻬Databases in use ⻬Active commands ⻬Applications Figure 5-3: Microsoft Management Console’s SQL Server performance objects.
76
Part II: Administering a SQL Server 2005 Express System
⻬Memory utilization
⻬Locks
⻬Object access
To access the Activity Monitor, just follow these simple steps:
1. Launch SQL Server Management Studio Express. 2. Expand the Management folder.
3. Double-click the Activity Monitor icon.
Figures 5-4 and 5-5 give you an idea of what you can learn from the Activity Monitor. Figure 5-4: Viewing user actions in the Activity Monitor.
77
Chapter 5: Maintaining a SQL Server 2005 Express System
If SQL Server Management Studio Express isn’t to your liking, copious third- party database monitoring and management tools are on the market. Most of these offerings include their own views of performance management. If you’re curious about some of these interesting products, have a look at Chapter 22 for more.
SQL Server 2005 Express also does a good job of tracking system activity over time. You can view its logs to get a better idea of what’s been happening on your database server. To launch the log viewer, follow these steps:
1. Launch SQL Server Management Studio Express. 2. Expand the Management folder.
3. Expand the SQL Server Logs folder.
You see a collection of log files, both current and archived.
4. Double-click any of these log files to launch the viewer.
Figure 5-6 shows a sample of what’s contained in a log file.
Figure 5-5:
Viewing locks in the Activity Monitor.
78
Part II: Administering a SQL Server 2005 Express System
Enhancing your computer
To squeeze some additional throughput from your database server, try any of these suggestions:
⻬Upgrade to a faster CPU: Computers continually get faster; just about anyone who has bought a new computer soon faces the depressing real- ization that the shiny new box is practically obsolete. What’s true for a personal computer is also true for database servers. If you have the budget, one of the fastest ways to quicker performance is simply to upgrade to a newer and peppier CPU.
Before breaking out the credit card, remember two important caveats: • SQL Server 2005 Express can only leverage one CPU; installing
multiple CPUs within the same computer won’t gain much performance.
• A slightly faster computer won’t yield tremendous response improvements. Typically, you won’t see dramatically faster com- puters hit the market for two or three years.
Figure 5-6:
Viewing log files.
79
Chapter 5: Maintaining a SQL Server 2005 Express System
⻬Add memory: Because so much database work takes place in memory, providing more of this valuable, yet relatively inexpensive commodity helps augment performance.
Trees don’t grow to the sky, and unlimited memory doesn’t shorten your applications’ response time to nanoseconds. A point comes where diminishing returns set in, so it’s best to take an evolutionary approach when increasing memory.
⻬Adjust virtual memory: The Windows operating system uses the page file as a disk-based substitute for memory. This virtual memory comes in handy when the processing load on your system overwhelms the avail- able physical memory. However, this virtual memory operates at about one-tenth of the speed of true memory. In most cases, Windows auto- matically manages this setting. If you’re inclined to tinker with it, just follow these simple steps:
1. Right-click My Computer and choose Properties.
2. On the Advanced tab, click the Settings button within the Performance section.
3. On the Advanced tab of the Performance Options dialog box, click the Change button within the Virtual Memory section. 4. Adjust your memory settings (as shown in Figure 5-7) and click OK.
Figure 5-7:
Changing virtual memory settings.
80
Part II: Administering a SQL Server 2005 Express System
You may need to restart your computer for these alterations to take effect.
⻬Defragment your disks: Like your closet and desktop, over a period of time your disk drives become cluttered and disorganized. The damage here is not aesthetic: A disorderly disk drive hurts performance by forc- ing your computer to jump around to locate necessary information. Luckily, Windows includes a handy disk defragmenter that you can use to set things right again. Here’s how to use it:
1. Launch the Windows Disk Defragmenter by choosing Start➪
Programs➪Accessories➪System Tools➪Disk Defragmenter. 2. Click the Analyze button.
The Disk Defragmenter analyzes your disk and returns a recommen- dation. If you’re lucky, you won’t even need to move on to Step 3.
3. If the Disk Defragmenter recommends that you defragment your disk, click the Defragment button.
This step may take some time to complete, but be patient; it’s worth it.
⻬Leverage multiple computers: If you find that your database server is overloaded, consider bringing additional computers into the picture. Although SQL Server 2005 Express doesn’t have the full distributed com- puting capabilities of its more expensive siblings, you can still gain bene- fits by offloading work onto ancillary processors. If you’re curious about using SQL Server 2005 Express in a distributed environment, take a look at Chapter 6.
Speeding up the database
While you may not have any control over your hardware platform, chances