There are a variety of potential causes of QuickBooks 'diminished' performance in the multi-user environment ranging from inadequate (or improperly allocated) server memory to network infrastructure, as well as the QuickBooks application and even your Company file. But one major source maybe associated with a bottleneck involving the Hard Drive(s) of the Server hosting your QuickBooks.
QuickBooks is composed of 3 different key components:
1 – The QuickBooks application that we all think of when we say ‘QuickBooks’
2 – Our QuickBooks Company (data) file that is stored on our server’s ‘hard drive’ (that’s why we are looking at hard drive performance today)
3 – The QuickBooks Database Server that runs as a windows service and reads data from the server’s hard drive and posts it to the server’s RAM Memory for use by the application, it then writes data back from your server’s Cache memory to the server’s hard drive.
There is another player involved here that works hand in hand with the QB Database Server and that is the Windows’ Operating System’s Disk Sub-system (sometimes called the I/O Syb-system). We will see how this component is involved in just a bit.
QuickBooks provides two different versions of the Sybase database server, but in most instances of "QuickBooks running really slow" we are dealing with performance as it related to QuickBooks running under the network database server that supports multi-user operations.
So what we really are talking about today is the performance of the hard drive when the Database server attempts to read pages of data from the QuickBooks file to post to cache memory and when the Database server attempts to write pages of data from cache memory back to the QuickBooks file on the server’s hard drive.
Understanding the QuickBooks 'Data Cycle'
The QuickBooks Database Server is responsible for flow of data between the QuickBooks application and your QuickBooks company file, the Database Server also makes use of your server’s RAM Cache memory in managing that flow of data in order to attempt to provide the best possible performance.
Let’s say you want to post some changes to a QuickBooks transaction, well the QuickBooks application is on your computer but your QuickBooks Company File(s) are stored on your Server’s hard drive. So when you click on the QuickBooks Icon your computer’s processor directs the Windows Operating System to load the QuickBooks application into your computer’s memory for use.
As soon as you tell QuickBooks to open a specific Company File (even if it defaults to your last open company), your computer’s processor sends a message to the QB database server to begin loading that Company File into the server’s ‘cache’ memory. The QB Database server then sends a message to the Windows Disk sub-system advising what portion of the Company File to retrieve from the Hard Drive so it can be loaded into memory, the portions of the file that get loaded depend on several factors such as the data last or most commonly accessed (like lists) and other data for which you have ‘rights of access’.
As you call-up the specific transaction to modify in QuickBooks, your computer’s processor sends a message to the QB Database server telling it to display your selected data from the server’s cache memory. You can access the data in cache memory quickly assuming that the information you need is already in memory and that your network infrastructure can ‘flow the data’ as fast as the processor and cache memory can respond. Whether you are reading data, or writing new data, the process is pretty much the same.
But if you attempt to access some portion of the QuickBooks file that hasn’t already been loaded into cache, then the processor must send a request to the QB Database server that it can’t find the needed data in memory. In an instant the database server identifies where the data should be located and tells the Disk Sub-system to go retrieve the information from the server’s disk drive, and to load that data into ‘cache’ (memory).
But if cache memory is already full, some of the data in cache memory must be written back to the computer’s disk drive before additional data from the disk drive can be written to cache so the QB Database server assigns the data to be written back an ‘address of sorts’ and then the Disk Syb-system stores the data back on the hard drive.
Of course the exact same thing is going on simultaneously for every user running QuickBooks on the network, and for each QuickBooks file in use.
Factors Impacting Hard Drive Performance
The QuickBooks Database Server works hand-in-hand with the Windows Operating System’s Disk-subsystem to gain access to and then write data to the hard drive of the server. In some large server environments data maybe shot-gunned out across several actual hard drives in what is called a ‘Raid Configuration.’
Data Organization
We always think of our QuickBooks data being stored in various tables: transaction tables, list tables and perhaps even some ‘internal organizational’ tables, but actually the data is stored in blocks, and those blocks make up pages, and pages make up ‘extents’ of data that are written in sectors of your hard drive.
All of that is more like a library of books then filing cabinets, and there is a ‘master index’ of all the data locations just like the library use to have (when I was growing up) the ‘card catalog’ that told you the location for every book in the library.
Well in Windows computers, there is a portion of the Windows operating system called the ‘disk sub-system’ and this sub-system is what actually is responsible for finding where the data is resident on the disk, fetching it and later allocating data back to some location on the disk.
Even with the very best hard drives in your computer, the performance of some aspects of QuickBooks are impacted by the transfer of data from the hard drive to the computer’s memory via the Database Server and Windows Operating program’s disk sub-system.
I just told you that data is stored in blocks, and blocks make up pages, and pages make up extents. Well the disk-subsystem must find the correct extent in the correct sector of the disk, then select the correct page, and lastly find the correct block or blocks of data.
QuickBooks data is stored on pages of 4096 KB, which hold 64 blocks of data with each block containing 64KB in 8 superblocks of 8KB of data. (8 X 8 = 64 X 64 = 4096).
Hard Drive Speed
The speed at which the Windows disk-subsystem can find the correct blocks of data is critical to hard drive performance (and thus QuickBooks performance….) As a general rule, the faster the drive in terms of revolutions the faster the read access of the Windows disk-subsystem.
Many ‘typical’ computer hard drives are 5400 RPM, some are 7200 RPM drives, a lot of ‘high-dollar’ servers use 10,000 RPM drives, and even ‘higher-dollar’ performance workstations maybe using 15,000 RPM drives.
But hard drive performance is based on a lot of factors, the speed of the drive is only one parameter. Capacity and density are another, so let’s look at a practical way to understand these two factors.
Hard Drive Capacity and Density
Drive capacity and density are two sides to the same coin. (AKA: Bigger verses fuller, size in relationship to quantify of data). Simply put…the more ‘stuff’ you have on a hard drive, the slower the performance, especially the ‘read performance’. The disk sub-system must search through more stuff and go further to find what it is looking for.
Back to our library, you want 3 books, the card catalog tells you that one of the books is in a book case only 3 cases away to your left from the card catalog, but one of the books is 9 book cases away to your right, and the third book is 12 books cases away directly in back of you.
By the way, the first book contains 10 percent of the information you need, the 2nd book contains 35 percent of the information you need, and the 3rd book contains 55 percent of the information you need. What is the quickest route to the most information you can garner in the least amount of time? If you guessed the 3rd book, then you are right, but our computer has to get all the data, because QuickBooks users ‘want it now’, or they complain that "QuickBooks is running really slow’.
Data Access Patterns
But accessing the information isn’t the only issue involved here, or with QuickBooks. You see in our library example we were looking at simply ‘collecting’ the books, in other word doing the same thing as when the disk sub-system retrieves data. Well the Disk sub-system must also write the data back out to the disk.
So what if you had two library books that you had to put back on the shelf at the same time you were getting more books? You can quickly see how exhausted our little Database Server and OS Disk ‘I/O’ sub-systems must be. You might think you are lucky to be getting all the QuickBooks data you want in a few milliseconds after all, and in reality, you are!
Database applications, like QuickBooks typically use a random access pattern in which reads data from your hard drive take-up about 67% of the disk-related activity, and writes of data occupy about 33% of hard drive activity. But simultaneously the QuickBooks Database server is also supporting the QuickBooks transaction log file which uses a sequential disk access patter writing 100% of the time. Based upon this 'dual responsiblity' we can see how 'access characteristics' , just like the randomness of the books in our library impact performance it getting all the information we need, while also putting back books we have already used. By the way a more heavily fragmented disk can greatly contribute to seek times and disk latency.
Data Transfer Configuration
The ability of the hard drive and 'application' (QuickBooks) to effectively make use of the data transfer abilities of the disk sub-system is also a factor. Data transfer always takes place in blocks, the size of the transferred data blocks depend son the operating system disk-subsystem and the application, it typically can not be influenced by the user. The theoretical best performance occurs when the disk subsystem is transferring 64KB blocks and QuickBooks takes advantage of that block size. Remember my illustration above of 8 X 8 = 64 X 64 = 4096?
Measuring Hard Drive Performance
You can measure the performance of your hard drive(s) using the Windows PERFMON function, these are the primary performance counters you should configure in PERFMON to measure hard drive performance.
LogicalDisk\% Free Space
The counter measures the percentage of free space on the selected hard drive. If this counter falls below 15% you are running out of free space to store files, add more disk space (via another disk and then transfer data to reduce the amount of data on the disk), or buy a larger disk and replace the older disk.
PhysicalDisk\% Idle Time
This counter measures the average time in seconds (actually milliseconds) to read data from your hard drive. If the number is greater than 25 milliseconds, the disk sub-system is experiencing latency when reading from the disk. Mission critical servers hosting QuickBooks require a threshold of only 10 milliseconds or so, you are best to replace the current disk with a faster hard drive.
PhysicalDisk\Avg. Disk Sec/Read
This counter measures the average time to write data to your hard drive. Again if the number is greater than 25 milliseconds, the disk sub-system is experiencing latency when writing data. We should again expect performance around 10 milliseconds in mission critical servers supporting QuickBooks, so it may well be time to invest in faster hard drives if you are measuring (or experiencing) significant latency.
PhysicalDisk\Avg. Disk Sec/Write
The measure indicates how many disk operations are waiting for the hard drive to become available. As a general rule if the value here is larger than two times the number of spindles, that means the disk itself is the likely bottleneck.
Memory\Cache Bytes
The performance measure indicates the amount of memory being used for the file system cache. The disk is a likely bottleneck if this value is greater than 300 MB.
Murph's Take Away:
Despite all the measures you can do, which help to diagnose the problem of the hard drive as the source of the bottleneck, there are 5 easy steps as the solution:
1. Always buy the 'Biggest Disks' you can get for your QuickBooks Server,
2. Purchase the 'Fastest Disks' you can afford for your QuickBooks Server,
3. Deep the load level of your Disks on the QuickBooks Server at 50% or less,
4. Segregate Disk Functionality for Specific Applications whenever possible, this means put QuickBooks on it's own disk, and
5. Optimize your disk by 'Defragmenting' your Hard Drives often (or consider using a 'perpetual defragmentation application' like DiskKeeper software).
These five steps will substantially eliminate your server's hard drive as the cause of the bottlenecks and the source of the complaint, "QuickBooks is running really slow."