Move with the Times

As database administrators, we often resist any meddling in how our systems are run, but we have to recognise when change is a good thing and when the business would benefit from an upgrade.

Now that SQL Server 2008 is out in the real world I thought I’d revisit its performance features and see how an upgrade to SQL Server 2008 could benefit your database solution.

Organisations store a mixture of structured and unstructured data, with data stored in databases as well as in Office products, file systems, mail systems and in a lot of other formats. From a structured data perspective, we database administrators need to ensure that our data is stored safely but that the right people can access it at the right time and for the right reasons. Traditionally this has been achieved by allowing data access for our line-of-business applications, but there is increasing pressure on us to ensure data is available for actions such as e-discovery or other types of investigations and audits.

Access all areas
Underpinning this safe and secure storage requirement is the need for fast data access. Users expect sub-second data access, sometimes to data that was archived a long time ago. SQL Server 2008 is even better at fast data access than previous versions as a lot of effort has been made to optimise core database engine performance.

The Transaction Processing Council (TPC) is one of the most widely quoted performance test houses for relational databases. The TPC-C benchmark test has, to a certain extent, been superseded by the TPC-E test, which is supposed to replicate the intensive OLTP processing required of a typical brokerage house. Unfortunately, Microsoft is the only database vendor that has released its results of this test so we can’t compare vendors’ performance claims. SQL Server 2008 TPC tests can be accessed via the TPC web site at www.tpc.org.

Performance features
SQL Server performance often comes down to quickly moving chunks of data from one place to another. This I/O can be very costly in terms of processing, and a lot of effort has been put into SQL Server’s database engine design to reduce the amount of data that needs to be read from, and written to, a server’s hard disk. The SQL Server query processing engine addresses this by using a read-ahead mechanism, so that data can be intelligently pre-fetched and pages put into the data cache on the basis that the query processor is likely to need them. Couple this with the data compression feature and you end up with fewer pages being accessed directly from the disk when queries are executed.

Server consolidations are driving many organisations to accommodate more than one SQL Server database on each of their physical servers. While this makes sense in lots of situations, it is feasible that a single server operating like this may end up trying to juggle multiple demands on its resources, sometimes rather inefficiently.

To address this Microsoft has introduced the Resource Governor in SQL Server 2008. This can be used to determine the status of workloads and assign them relative priorities. This can prevent a long running query in one area blocking an urgent query being submitted by another user. Typically a workload consists of applications, databases and users in varying ratios. There may be regular report workloads that happen every week that can be assigned a lower priority than an immediate ad-hoc request from the finance director. Obviously SQL Server can’t make its own decisions at this point so as database administrators we need to make business-led decisions to help prioritise the workload and this is where the Resource Governor can help.

For a well-designed and maintained SQL Server database, a lot of performance data will need to be collected, collated and analysed. The best place for this is in the SQL Server 2008 Performance Studio. This provides a framework that can be used to collect data across all the SQL Server instances that come under your remit. The depth and breadth of this data available for collection is huge, and you are unlikely to run out of things to track and record. The danger is that you end up becoming so engrossed with the detail of each performance feature that you lose track of the big picture, so use these toys with care.

Scaling up
Databases are like cars. Some would suggest that if you want to go faster, don’t bother tuning the engine, just get a bigger one. Scaling up, as it is more properly called, is a useful technique for improving performance, but it won’t fix a fundamentally bad database design or address the more crazy user queries your data can be faced with.

Decent hardware that scales is vital. SQL Server is now a proper 64-bit database, able to take advantage of the range of multiprocessor/multicore hardware now widely available. SQL Server is non-uniform memory access (NUMA) aware. NUMA enables processors to access memory locally rather than take the relatively long trip to a central pool of memory. Although the time difference may be fractions of a second, when added up across a very busy server a performance difference will be seen. In any case, the ability for 64-bit SQL Server to address 8TB of memory should satisfy most normal line-of-business applications.

To support scaling up, SQL Server does allow, in some circumstances, the addition of CPUs and memory while it is still running. Generally, it requires you to be running the 64-bit version of SQL Server, Windows Server 2003 Enterprise Edition or Datacenter Edition and hardware that supports memory or CPU additions. This is an expensive combination, but necessary if you need such a high level of database performance.

Better concurrency has always been a hallmark of improved database performance. By this we mean the ability for SQL Server to cope with an ever-increasing number of users and still balance their requirements to SELECT, INSERT and UPDATE data. Maintaining the concurrency of the data, balanced against the integrity of data being read, has been a problem for database vendors ever since relational databases were invented. SQL Server achieves this using a number of isolation levels alongside the capability to change the way in which locks can be escalated on tables.

Scale out
An alternative strategy to scaling up is to scale out. With this approach data is spread out across a network, in turn spreading out the data workload. For example if you have an HR department, it makes sense for them to have “their” data, such as employee records, logically close to the HR users. Likewise, customer data may be better held closer to the sales teams. This type of federated database can be very successful, but it does require planning and thoughtful implementation.

Data warehouses really come into their own with a federated database architecture. By creating read-only copies of the data that can be used for ad hoc queries by the business you can offload a lot of processing requirements from the OLTP database. SQL Server 2008 supports scalable shared databases that provide an infrastructure for scaling out these read-only reporting databases. Requests for data can be managed by the SQL Server Service Broker, which routes queries to appropriate data sources.

Any data warehouse would need to be populated with up-to-date data. This refresh of data would happen at regular intervals as dictated by the business users, and would probably be facilitated using SQL Server peer-to-peer replication. SQL Server 2008 has a new topology wizard to help design such an architecture and now allows new nodes to be added while replication is ongoing.

Some database administrators use data caching as it allows the data to sit closer to the user for a better response time. The downside is that data can age in the cache to the point of being unusable, especially in more active systems. Putting in place regular data refreshes is a way of dealing with the problem but is particularly inefficient, as there is no guarantee that the data being refreshed has actually aged. To address this, SQL Server 2008 supports query notifications, which can inform a cache that their data is out of date. The specific data can then be refreshed as and when it expires.

Database administrators are generally conservative, resisting change and guarding databases, quite rightly, from unnecessary interference. Unfortunately, we all need to move with the times and upgrade our systems when our business can best benefit from it.

As SQL Server 2008 beds down, you will undoubtedly feel the time is right to upgrade. By taking a practical and measured approach to this upgrade, you are bound to get the ongoing benefits of SQL Server into the future.