InformationWeek
Defining The Business Value Of Technology
Februa ry 16, 1998
SQL Server Gets BetterMicrosoft's second beta for SQL Server 7.0 is filled with improvements that database administrators will appreciate
By Tom Stearns
hristmas will come early for SQL Server administrators if Microsoft meets its second-half 1998 shipping deadline for SQL Server 7.0. I tested the recently released second beta in the InformationWeek Labs and found that while the beta is not quite feature-complete and still needs to be tuned up, the final version should be an impressive package.
The bad news is that Microsoft has changed the physical layout of the server's files. This means that SQL Server customers will have to unload SQL Server and convert their databases in order for them to work with versio n 7.0. This is no minor undertaking, and Microsoft does not appear to be taking it lightly. But Microsoft representatives say the new format is a necessary step, and will prepare SQL Server for the next 20 years, offering greater flexibility, performance, and scalability.
The other loss is that management tools that use Distributed Management Objects will require changes for the new version, though Microsoft says this will affect only 1% to 2% of SQL Server users.
There is a lot of good stuff in SQL Server 7.0, but my favorite feature is what's not in it: devices. This method of assuring platform independence, by creating a chunk of file space managed by SQL Server, is long overdue for death. One benefit of this change is that database files, freed of fixed-size devices, now grow automatically. They even shrink automatically, which is useful for database applications that create temporary tables for decision-support queries. Database administrators can control the increments that SQL Server uses in growing and shrinking databases, or not allow changes to the size of the database itself.
This greater automated control reflects a major theme of the new SQL Server-freeing administrators from mundane management chores and letting them return to managing the important things: actual data and the applications that use it. Database administrators are going to love SQL Server 7.0-if the process of upgrading it isn't too much to handle.
To test this, I set up a 150-Mbyte database on SQL Server 6.5, then installed the SQL Server 7.0 beta on the same machine, a 266-MHz Pentium II with 64 Mbytes of RAM. After installation, I selected the Upgrade Wizard. The wizard stepped me through the options of which databases to upgrade, where to put them, and so forth.
I knew this would take a while, so I went to dinner. When I came back, the screen showed that the wizard had completed the upgrade in about six minutes. I found this a little difficult to swallow, so I went to start the SQL Se rver 7.0 beta to see the result.
However, SQL Server 7.0 was not on my Start Menu, so though the files were on the disk, there was no way I could start the server. Inserting the install CD again, I ran setup once more. This time, setup detected my invisible server and would not let me do another install. So I uninstalled the beta and started over.
This time, when I checked the Start Menu first, SQL Server 7.0 was there, still bearing its code name, Sphinx. I reran the Upgrade Wizard and, indeed, it converted a 150-Mbyte database, and the model database Microsoft supplies as an example, in just a few minutes. This is impressive, considering this operation requires rewriting the database in the new format.
Changes Everywhere
As for other changes, SQL Server 7.0's Enterprise Manager is now based on Microsoft Management Console and is somewhat different in appearance and function. The first thing I noticed was that I did not have a dialog pop up to register the first server . This was the first thing a user sees when using SQL Server 6.x's Enterprise Manager. Therefore, the Server Group had no servers.
That was odd, I thought, so I exited Enterprise Manager and went to the Service Manager and manually started my server. As I was doing so, I remembered the registration process, and found that after doing it in Enterprise Manager, everything was accounted for.
The new Enterprise Manager is an improvement over its predecessors, though often in small ways. Like the older models, it is two-paned, with a hierarchical view on the left and a detail view on the right. There is much more information available for browsing-database diagrams and user-defined data types (another new feature), for instance. I found its operation much more consistent than in previous versions, a worthwhile improvement.
Another mainstay of database administrators is Interactive SQL for Windows. In the SQL Server 7.0 beta, this tool has been renamed Query Analyzer, and the product is much improved.
This tool is handy for all sorts of management, development, and testing purposes. With it, you can launch SQL commands, stored procedures, and functions such as Database Consistency Check. Microsoft has upgraded it with features such as a graphical Show Plan, which illustrates how the query optimizer plans to execute a particular SQL command. SQL commands are now color-coded by syntax, much like modern programmer's editors.
The graphical Show Plan is a very useful feature. It lets you see the process it goes through to respond to your query. While the older, nongraphical, tool was handy, being able to visualize an often-complex process is much easier. Show Plan will let you see the effect that options, such as creating indexes, will have on a query. Query Analyzer will also suggest indexes to create to make a query run better.
Query Analyzer defines queries better by using a costing algorithm, which tries to be more accurate than the typical input/output method used in previous versions of SQL Server. I ran a query on three joined tables, the primary one having about 250,000 rows. The results started arriving in about a minute, a very impressive result for a query of this size.
Faster Queries
Everyone wants more performance, and my seat-of-the-pants evaluation of this beta convinces me that SQL Server 7.0 will satisfy when it hits the streets. Microsoft accomplishes this feat with a slew of tricks that, when combined, will mean that most queries will run a lot faster on SQL Server 7.0 than on previous versions.
The first of these tricks is the multi-index filter. Index filtering is using values stored in indexes, which are more quickly read, in order to limit the number of accesses to the actual table. Suppose you want a list of customers in Rhode Island. The straightforward approach would be for the query processor to read each customer's record in turn and see if that customer is in Rhode Island. This would work fine when the number of cu stomers is limited-say a few thousand.
However, a better approach would be to have customers indexed by state. The query processor could then seek the index for Rhode Island and fetch only those customer records. This would substantially reduce the number of reads in the actual data table and provide the information faster, with less stress on the server.
The SQL Server 7.0 beta offers multiple-index filtering to let it take the next step, using the indexes to speed up more-complex queries such as, "How many customers do we have who live in Rhode Island and who started with us last year?" This is done by merging the subsets of two or more indexes. In this example, we get a set of customers in Rhode Island from one index and a set of customers who started last year from the "customer since" index and indicating which records are relevant. These multiple-index operations can take place among one or more tables, and SQL Server will try to create on-the-fly indexes.
Another technique, the Hash Join, helps when the data is not ordered in the manner needed. A Hash algorithm breaks down data into specific subsets-months of the year, for example. Each record is dumped into a "bucket" (in this case based on month). Hashes are usually quick, but they do not guarantee that the data in a particular bucket is in any kind of order (in other words, the days of the month are all mixed up in the bucket).
Hash Joins can begin returning data before it's sorted by making a rough cut-a hash-and then working with the results. Hashing works well for ad hoc queries that do not represent normal ordering.
Fast Features
Microsoft has also taken some basic steps to speed up SQL Server 7.0. These include increasing page size from 2 Kbytes to 8 Kbytes; increasing I/O size to 64 Kbytes during scans; and giving a higher I/O priority to read-ahead, a form of caching.
The last major improvement is in dealing with data tables in physical, or raw order. This is done when no indexe s are available. SQL Server speeds these often slow operations by dropping unused indexes to speed up scans.
Since physical order reads are not, by definition, ordered, SQL Server 7.0 will also parallel these reads to try to improve performance. This means that different threads operating concurrently might read different parts of the table.
Data warehousing is not short-changed in the SQL Server 7.0 beta. It offers table metadata-information about the table that can be used to construct queries. And though the beta I tested did not have it, Microsoft has said support for Star schemas, a method of organizing data to make it more readily available for reporting, will be in the final release of SQL Server 7.0.
Support for very large databases also has been added. Microsoft claims that SQL Server 7.0 will work well with terabyte-size databases. Important to databases of this size is the ability to keep the data backed up, and SQL Server 7.0 will offer online backup. This means that d ata can be backed up while the server is actively running. Incremental backup will also be in the final version of SQL Server 7.0.
With all the data warehousing news, it's easy to forget that the data still needs to be put in first. Online transaction processing (OLTP) will benefit from the much-anticipated row-level locking offered in SQL Server 7.0. In previous versions, for the most part, a change in a single row required that the server lock the whole data page, which contains several rows. This meant that often-updated data ran into contention problems as multiple tasks sought to update different records on the same page. Row-level locking gives the server a more granular approach to dealing with contention by locking only the row in question. Microsoft has added to the idea by having the server run locking possibilities--row page or the entire table--through the query optimizer, to determine the most efficient method.
Server, Manage Thyself
A huge difference in performanc e can be realized in any database server by careful tuning. The problem is that this tuning has been based mostly on carefully developed instinct, and it was often difficult to be certain a change would be beneficial overall. In most cases, adding to some parameter takes from another--for example, adding procedure cache makes less memory available for queries.
The best database administrators, then, are artists who can intuitively find the best balance in this multivariate problem. However, a computer is very good at comparing the many permutations and combinations at work in this sort of situation and doing it in real time--that is, if it has a feature like SQL Server's new dynamic self-management.
This feature will allocate resources as the server determines need. There's no need, for instance, to allocate memory statically to SQL Server; it will do that automatically when required from Windows NT's available pool. In addition, an SQL Server expert agent will recommend changes to the admin istrator that are outside the dynamic manager's scope.
SQL Server also moves to the small end with Windows 95 platform support. Along with this, Microsoft has added new strategies for replicating data once the user returns. These new tools also aid in managing physically disparate servers that rely on the same data.
Merging Standards
Microsoft has been pushing database standards for several years now, most notably Open Database Connectivity (ODBC). It has also been pushing its component technology, ActiveX. Now comes the merging of the two with OLE DB. Microsoft is expecting OLE DB to be the access method of choice for SQL Server 7.0 data.
ODBC was designed to deal with relational data, like that in SQL Server, although nonrelational data sources, such as Excel spreadsheets, have ODBC drivers. Microsoft sees ODBC reverting to handling primarily relational data sources, just one of many types that an OLE DB object can contain.
By doing this, other data sources c an be exploited-E-mail, for instance. SQL Server can do heterogeneous data joins using OLE DB, making a far larger set of data available to traditional techniques. And speaking of standards, SQL Server 7.0 will support the ANSI/ISO information schema standard, thereby opening SQL Server up to further interoperability with other database servers.
This beta, while not feature-complete and suffering from some annoyances, is very impressive. There is something here for everyone, but the overall flavor is not that of Microsoft trying to please everyone. Rather, Microsoft appears to have rethought the entire product, and come up with a much better one. I look forward to the final release to see just how well it will do when it is all buttoned up.
Tom Stearns is a freelance writer and system consultant in Salt Lake City. He has coauthored several books, including LAN Times Guide To Security And Data Integrity (Osborne/McGraw-Hill).
Back to Labs
Send Us Your Feedback
Top of the Page
