Tell us a little about yourself. I’m the president of Intrinsic Design, Inc., a software company dedicated to writing database analysis software for Microsoft SQL Server. After receiving my electrical engineering degree from the University of Michigan back in 1989, I took that knowledge and entered the world of software development. Initially, the database world was dominated by Ashton-Tate, and dBase was the database of choice. With the release of Sybase’s SQL Server 4.2 in 1992, I realized a very needed change was taking place in the database market; a move to a client-server model of computing. The paradigm shift was a welcome one, and opened the door to the possibility of more sophisticated and scalable applications.
For the next couple of years, I was responsible for designing applications and databases that ran on both the Sybase and Microsoft flavors of SQL Server 4.2. But my attention began to readily sway toward the Microsoft solution, not only for its seamless integration with inexpensive PC’s running Windows NT, but for it’s price-point. Microsoft made it easy for small companies and individuals to acquire SQL Server, and begin to develop sophisticated database applications. It seemed a necessary extension of my career back then to seize the opportunity.
Tell us about your company, Intrinsic Design. Intrinsic Design was formed in 1995, initially as a consulting company. We developed applications for many clients using a variety of front-end tools, but primarily focused on Microsoft SQL Server as our database of choice. This was a very exciting time in the software industry. Microsoft SQL Server 6.0, a major upgrade to version 4.2, had just been released, and would soon be followed by version 6.5 a scant 10 months later. Windows 3.1 was everywhere and would soon be eclipsed by Windows 95 and NT 4.0 in corporate America, and client-server computing and development became the de-facto standard. And let’s not forget the Internet. It seemed there was no better time to start a company to tackle all this new technology. In 1999, we began to focus our attention on developing commercial software. The development of Coefficient was a natural extension of our experience in the industry and a solution to one of the major problems we observed over the years at our clients; the difficulty of performing sophisticated SQL performance analysis.
Can you provide one or more examples of how your clients are using Coefficient in the real world? We see our clients using Coefficient in basically two ways: to perform preventive maintenance and identify performance bottlenecks. Before explaining these items in more detail, I’d like to point out that Coefficient is not strictly a database administrator tool. It was designed to be used by software developers as well. Experience has taught us that while database administrators (DBAs) often have good control of the database design (tables, views, basic indexes, and constraints), it’s almost impossible for them to keep track of all the SQL that accesses all the databases they manage. The problem is, without understanding the queries that access a database, it’s impossible to predict whether a database will operate efficiently. As many have heard me say before, if you execute inefficient SQL on a database, you can easily kill server performance. To me, this is rather scary. It’s hard to believe that a $30,000 piece of hardware can be crippled by a single query or stored procedure. But it happens all the time. This is where the software developers come in. They use Coefficient during development to analyze their database activity on the development servers. They can then identify and correct any potential problems. And best of all, if they choose, they can do this independently of the DBA until they are ready to present the DBA with the set of changes for review. Since software developers are involved in most of the query creation, it only makes sense they should fine tune queries early on in the development process. DBAs use Coefficient to monitor overall database activity or to identify performance problems. When working with developers, the DBA can schedule a Coefficient trace on the development/QA server to watch the developers/end-users hack away at the application before it rolls into production. The DBA documents the database activity using Coefficient. The first time, this creates a baseline. Future tests are compared to previous revisions to identify any performance changes (trend analysis). DBAs also use Coefficient in production to monitor the health of the production databases. Traces are run at various times of the day to document server load and activity. If necessary, during times of poor server response, the DBA uses Coefficient to document the activity and identify the responsible queries. The DBA would post all these analyses on the company’s intranet (or network) for easy future reference.
What factors, such as hardware, the client application, the network, SQL Server settings, database design, indexes, coding, etc, affect SQL Server’s performance the most? Anyone who’s been involved in database tuning is aware that a deficiency in any of those items can cause performance problems. In general though, the hardware and network tend to be less of an issue with overall performance. The reason I say this is because those “environments” are normally tightly controlled at a company. Purchasing hardware and designing a network are items that normally accompany close conversations with experts on the subject. PC manufacturers know how to design multi-processor servers and inexpensive clients, and there are industry standards for network design that most companies follow. So these items tend not to cause the majority of problems with SQL Server performance. I’d have to say that SQL Server settings are less of an issue these days, owing a lot to the auto-configuration Microsoft has built into SQL Server 7.0 and 2000. While it’s important to understand these settings and make minor modifications to them in certain cases, many administrators find the default settings adequate. By far the most important items that have the potential to kill your SQL Server performance are indexes and coding. I’d like to touch on these items briefly. I would define coding as the programming logic which an application uses to retrieve and process data on SQL Server, or the queries used to access data. Query performance is intimately related to the available indexes on the server. The problem stems from the fact that those individuals designing the queries and stored procedures to access and update data in SQL Server are often not the same individuals as those responsible for index and database design. SQL queries to a server must not only be properly designed, but they must also have the necessary index support on the server. A flaw in either will cause a headache for all involved. When a query is issued, SQL Server determines how to best retrieve the data, using its knowledge of the underlying table structure, data, and available indexes. SQL Server returns the requested results regardless of how difficult it is to process the query. Ideally, you want SQL Server to access as few data pages as possible, and hopefully get those database pages from memory as opposed to disk. Disk access is hundreds of times slower than memory access. As is often the case when dealing with performance problems, SQL Server cannot always access the data efficiently. This may be the result of a poorly written query, a missing index, or both. From a software developers point-of-view, he/she creates a query and checks to make sure the results are correct. On a development server, with limited access and smaller data sets when compared to a production server, these queries will often appear to operate efficiently, even if they are not. Programmers are often not trained to investigate the underlying details of the query’s execution, and therefore, may not attempt to identify any hidden problems. Imagine if you will, a query which is forced to do a table scan of a 10,000 row table on the development server. An average server can scan a table quickly enough that you may not notice the lag and identify it as a problem. Move that same query to a production server and you may be scanning a 1,000,000 row table. This is more noticeable, but still may not raise eyebrows. But compound that with the likelihood that multiple users are running the same query and you can imagine the tens of millions of rows SQL Server must continually scan to produce the correct result sets. These types of problems become systemic. The wait the end-users must endure while inefficient queries run eventually extends to all users and server performance is lost. Adding faster hardware and more memory is just a temporary stop-gap. The goal is to understand the design of individual queries, how they access the underlying database, and how they interact with each other.