SQL Server 7.0 Datatypes 1 2 3 4

The first part of this session focused on the server side aspects of performance.   The design goals of SQL Server 7.0 were to create a self-managing, self-configuring, self-tuning server.  They have accomplished some of this, but it will be a long time before it can replace the skill set of a qualified DBA.  It has accomplished part of this through a variety of means: auto statistics creation, dynamic memory management, lock management, query plan caching, parallel querying, index tuning, auto grow and shrink for databases, better space management.  This relieves some of the tedious burden, but it still requires you to create solid and efficient applications that leverage the performance characteristics in SQL Server.

One of the first things that people wonder about when dealing with performance is where to focus the efforts.  You can focus on hardware, configurations, database design, or the application.  Unfortunately, many people want to start with the hardware because that is deemed to be the easiest.  While it may be easier to throw more hardware at a problem, hardware and server configurations account for only 5% - 15% of the performance that can be gained.  Making appropriate decisions for hardware and then leaving it will force you to look at the database design and application for tuning.  Database design and the application usually account for 85% - 95% of the performance that can be gained from a system.

With that in mind, we can focus on the hardware aspects of performance so that you can make a better informed decision as to what you will need.  The typical I/O path travels through the CPU, system bus, memory, PCI bus, array controllers, and finally into the disks.  You can bottleneck a system at any level through here.

The thing to keep in mind with performance tuning is that there will ALWAYS be a bottleneck in the system.  You can never eliminate all of the bottlenecks.  Your job is to identify the areas where the most benefit can be gained and tune them.  You   continue to eliminate bottlenecks until the system performs "good enough".  It is a never ending process.  A system that was tuned last month to reach an acceptable performance level might now be running at an unacceptable level.

You have two kinds of I/O in a system: sequential and random.  Sequential I/O forces the disk arm to travel very little which gives a low seek time.  This gives a much higher throughput and is generally characterized by a small number of larger writes.   Random I/O causes the disk arm to move all over the platter which produces much longer seek times.  This gives a much lower throughput and is generally characterized by a larger much of small writes.  Due to throughput, you should seek to minimize random I/O and maximize sequential I/O.

To take advantage of this, SQL Server reads data in much larger blocks than in previous versions.  Random access is normally performed by worker threads, the lazywriter, and checkpoint process and occurs in 8K blocks.  Sequential access is normally performed by such things as read ahead manager, backups, DBCCs, index creations, etc. and occurs in 64K blocks.  The log manager has a small amount of random access and generally processes in up to 60K blocks.

SQL Server 7.0 Datatypes 1 2 3 4