SQL Server Reference Guide

Data professionals tend to view their work landscape in two general parts: databases they have full control over, and databases they don’t. By that I mean that your IT organization may have the opportunity to write software, and that you own the database underneath that code from the design up. You can control the structure, design and objects within the database. You have control over the data, the security, and how the system is accessed. You can affect the performance of the system in any way you like, from the hardware it runs on to the design of the database, indexes and more. Although you many not think about it very often, it’s a high degree of control.

The other databases in your work landscape may come from other vendors. In this case you don’t have control over the design, database objects, or even in some cases the indexes that the system uses. And yet you’re asked to maintain and tune those databases just like the ones you have control over. Depending on where you work, this might be the bulk of your databases.

So what can — and what should — you do to for and those vendor databases? Are they just completely out of your control, and is there anything you can do to make them perform better?

Actually, there is. There are far more areas than you might first guess where you can affect even the most locked-down system.

Understand the System

The first thing to do is to review as much documentation as you can about the system. I do this even before I install the test system. I always install a test system on a Virtual Machine to experiment with. If the vendor has strict requirements on license keys and so on, I call them up on the phone and ask for any kind of exception I can get, from evaluation keys and so on. Once I explain what the test system is for, most companies are pretty good about letting me have that installation.

I don’t just review the “official” documentation. Depending on the size and popularity of the system, there may be after-market books, articles, whitepapers and so on that deal with the databases for the software I’m putting in. During the phase when my company evaluates the software packages, I ask if I can be included on that process, and once they start locking down a vendor selection, I try and get references of other companies that are using that software. I’ve called DBAs in other countries, I’ve travelled across town and bought a data professional a lunch, and I’ve sought out people at conferences to ask what issues they’ve had with the system. I take a lot of notes about what I learn.

As part of understanding the system, you might be tempted to “reverse engineer” the database. There’s a fine line here on what you can and can’t do. In fact, in some cases that might even be illegal. It’s tempting, because you want to understand the structures that make up the database, but in a strict sense that’s “source code,” and the vendor might not allow you to make an Entity-Relationship Diagram (ERD) or other representation of the database structure. Most of the time this is allowed, but do your research to find out and stay legal.

Once you’ve done the research, follow the best practices — theirs and your own. I find that after I understand what the system does I understand a bit more about the design, or at least as much of the design as I am allowed to know. In the end, you can only do what you can do — I don’t stress if the system does not perform well once I’ve done what I can. I contact the vendor, offer to work with them to make it better, and then go from there. More on that in a moment.

Follow Guidance for the Physical and Logical Architectures

Even for a vendor database, you should follow the best practices for SQL Server. You need to focus on the “big four” — CPU, memory, I/O and networking. Ensure that you’ve set up the hardware with the latest versions, firmware, patches, and so on. I can’t over-emphasize this enough — many people aren’t aware that there’s often as many lines of code in the path between the Host-Bus Adaptor (HBA) through the Storage Area Network (SAN) fabric on to the enclosures as there is in SQL Server itself. Ensuring you’ve researched and used the latest drivers is critical to the performance, availability and stability of any system, whether you developed it or someone else did.

Once you’re familiar with the application and the data-tier needs, size the entire environment properly and follow the best practices for SQL Server in general. Evaluate where the vendor has deviated from these recommendations, find out why, and communicate back to them if you don’t agree. For instance — one of the things you can affect in a database system is the physical and logical architectures. Even the strictest requirements allow you to use separate files, if not FileGroups.

SQL Server works with the I/O subsystem by using a FileGroup. This is a logical container, which in turn points to the logical filenames that SQL Server puts in that FileGroup. The logical names then point to an actual physical file on the hard drives somewhere. All SQL Server databases have at least one FileGroup, and by default all data-bearing objects (like tables and indexes) go there.

Adding more files can benefit you in multiple ways. If you place those files on multiple spindles of hard drives, meaning not just a different drive letter but an actual physically separate drive, it allows SQL Server to access them in parallel, speeding up the system. Big caution here — even before you take this step, ensure that you understand how FileGroups work and how to implement them. Then of course, test, test, test.

This is just one example. Ensuring you’re using Instant File Initialization, setting upper and lower memory boundaries, excluding the data and log file locations from your virus scans (following strict rules for securing those first, of course) are all things you can do at the platform level even before you lay down the vendor’s database.

Maintain and Tune Where You Can

For any SQL Server database, you need to follow standard maintenance procedures. This includes backups and restore tests, of course, but also includes statistics and index maintenance. It’s important to follow whatever rules the vendor has for things like automatically creating and maintaining statistics and also for creating indexes - don’t invalidate your support.

You may be allowed to add indexes. If you are, follow the standard guidelines for creating good indexes, include the ones I have here, among others. Be especially careful with clustered indexes — they can have the most impact on good or bad performance, and are often the action that your vendor fears the most.

I had one example where a well-meaning DBA placed a clustered index (without notifying the development team at the vendor where I worked) on a column he thought would need one. Sure enough, the performance increased immensely — for a while. What the DBA didn’t know was that in certain locations in the application, a user could change a selection that eventually ended up changing the data the column contained from a sequential, increasing range of numbers to a mixed grouping of characters that could be inserted earlier in the table. That meant that the entire table was physically re-written per insert. Since this happened once every few seconds, the performance became so bad that the system slowed to a crawl. Once the DBA confessed to his actions, we were able to fix the system. Moral: don’t be that DBA.

Unless your vendor specifies otherwise, don’t query their database directly. There are obvious security implications, but something you may not be aware of are the locks you may be causing. Depending on the tool you use, you can create serious issues this way. If you need to access the data on that system, you have options. One is to take your practice-restore of the database on another system and access the data there. Another option is to use an Application Programming Interface (API) if the vendor provides one. If the API is implemented properly, it will handle the locks, security and other concerns the right way.

I’ll emphasize again that a data professional should not have a backup strategy — they should have a restore strategy. You want to start with the end in mind, so perform test restore operations frequently to validate your backups and processes.

Communicate With the Vendor

After you read and understand what the vendor wants you to know, identify any gaps or questions that you have about the data layer and communicate with the vendor. I start with the sales team, since they are the ones that want to maintain a good relationship with your organization. Explain what you’re looking for, and often they will put you in touch directly with a technical team. Even if you have to navigate the vendor’s support ladder, it’s worth it if you gain the knowledge you need.

Another valuable source of information is the blogs of the technical team at the vendor, if they have one. Make sure to ask.

An often-overlooked source of information is the vendor’s User Groups. You would be surprised to learn how many small vendors have a user group structure. Sometimes that’s no more than a message board or email list, but it’s contact nonetheless. You could end up finding out that one piece of information you need for the data layer. Even if you don’t, you might end up becoming the expert in that area, and gain access to a better relationship with the vendor that way. Win-win.

As you can see, you have more control over the data layer than you might imagine. You still own the physical hardware, network, maintenance and tuning for your system, and acting in concert with the vendor, you can ensure the best performance and reliability of the system for your organization.

InformIT Articles and Sample Chapters

Don’t know where to start with the maintenance side of things? I have some help for you in another part of the SQL Server Reference Guide called Data Management Objects: Database Maintenance.

Books and eBooks

I’ve automated most of the checks and maintenance I do for my vendor databases using PowerShell. You can learn more about that in Essential PowerShell.

Online Resources

There are lots of resources for working with standard maintenance on SQL Server, and none is better than Ola Hallengren’s site.