A few months ago, I was awakened to the insistent buzzing and chirping of my BlackBerry telling me I had a high-priority message. All the customers who used one of my databases were calling in complaining that our Web application was taking 20 to 30 seconds to load the pages they used the most. Performance had gradually worsened over the prior few weeks and was now at the point where just a few more people logging in would bring the system down. I had to find the source of the problem—and soon. Here's how I tracked down the trouble—which, as I discovered, stemmed from a combination of fragmentation in tables and database files and page-density inefficiencies—and the steps I took to correct it.
Narrowing Down the Problem
My first step was to open Performance Monitor to see whether one of the "Big Four"—CPU, memory, disk, and network—might be a factor in the slowdown. Processor:% Processor Time was in the normal range for our database server, SQL Server:Buffer Manager:Free Pages showed more than 2000 pages available, and Network Interface:Bytes Total/sec was only about 1/20 the capacity of a Gigabit Ethernet network. Physical Disk:Disk Bytes/sec was consistently 100 to 200 percent higher than normal for our server, though. Disk usage appeared to be the source of the problem.
In this case, apparently the hard disk had enough excessive activity for a sustained period to slow all the other operations. Because ADO.NET has a fairly aggressive algorithm for adding connections to the connection pool, even a small increase in the amount of time a user's process holds a connection multiplies the number of connections. More connections to the same data source usually increases the probability of conflicting locks on commonly accessed data, which in turn slows SQL Server's response to queries. Much as one slow car during rush hour slows down everyone on the road, a minor increase in response time in a database server can have a cascading slowdown effect on performance in a busy application environment.
Hard-Disk Constraints
Because there are physical limits to how much data can flow between memory and disk, maximizing disk performance is key to optimum overall database performance. Hard disks not only have a physical limit to how fast the heads can read the electromagnetic variations on the disk, but the rotational speed of the disk also limits when the heads have data to read. If a page that SQL Server needs isn't currently under the read/write head, the disk controller must wait until the section of the disk containing the page rotates under the head. The rotational speed of the disk, therefore, controls how quickly data is presented to the read/write head and how fast the head can read and write bits.
The rotating-disk limitations, then, make the order and length of data requests important. SQL Server's storage engine batches reads and writes as much as possible and orders the pages in the sequence they occur on the disk. The goal is to read the data in as close to page-number order as possible because doing so results in the least number of waits for the correct section to rotate under the heads. The Performance Monitor counters Avg Disk Bytes/Transfer, Avg Disk Bytes/Read, and Avg Disk Bytes/Write tell you how many bytes are being requested at each I/O operation. Drives dedicated to holding SQL Server's databases will never have fewer than 8196 bytes per transfer, but what you want is a consistent 65,536 (or more) bytes per transfer (65,536 bytes, or 64K, equal one extent). If you see a value that's less than 65,536 as an average, fragmentation is likely to be a problem.
Performance Monitor showed excessive disk usage, but I'd need another tool to find out why. FileMon is a free tool from Sysinternals (http://www.sysinternals.com) that reports all reads and writes and the number of bytes per operation for all files. When you set FileMon's filter to monitor only database files, you can easily see how many bytes SQL Server is reading from and writing to each file in a file group. When I noticed that FileMon reported only 8196 bytes per read for a file that had a 2.5GB table and about 1GB in indexes, I knew I'd probably found the culprit for my performance problems.
Remember that each I/O includes a delay while the controller waits for the disk to rotate under the read/write head; therefore, it always takes longer to read eight pages than one extent. Additionally, the storage engine can't order the reads for efficient access because it can't predict what requests will come in the future. Disk optimization comes from a combination of storing data in large chunks and accessing the disk as little as possible. FileMon's report that the storage engine was reading a 2.5GB table one page at a time meant that something was keeping the engine from batching the requests into extents. It was time to look at some tables to find out why.
Finding Fragmentation
SQL Server's DBCC SHOWCONTIG command is a useful tool for helping you identify the extent of fragmentation in a table. Unfortunately, running the command exacerbates the performance problem you're trying to fix because it creates large amounts of I/O. The FAST option has less impact on server performance than running the command without it, but even running DBCC SHOWCONTIG FAST slows the system too much when performance is already bad. The answer is to run Database Consistency Checker (DBCC) when you have time to perform a complete scan and specify TABLERESULTS, which outputs the scan results as a table, then save the results.
I wrote the uspBuildFraglist T-SQL stored procedure, which is excerpted in Listing 1, to automate running the DBCC SHOWCONTIG WITH TABLERESULTS command. UspBuildFraglist loops through the list of tables in the specified database and, for each table, runs DBCC SHOWCONTIG WITH TABLE RESULTS,ALL_INDEXES, which displays fragmentation information for the data and indexes of the specified table. The results are first stored in a temporary table, then moved into a permanent table in a database that I created for holding data generated by administrative and maintenance processes such as this one.
The permanent table has a column named LastScanTime for tracking when the table was scanned. The IF statement at callout A in Listing 1 checks that column for each table and skips the DBCC statement if the table was scanned within the last day. This check allows uspBuildFraglist to be run multiple times without duplicating the work done by previous scans.
A second feature of uspBuildFraglist is the delay after each scan. The code at callout B checks the sysprocesses table to see whether the scanning process is blocking any processes. If so, the procedure waits for 30 seconds. If not, the procedure waits for 5 seconds before it runs the next scan. The ability to pause between scans is the primary advantage of scanning each table individually instead of scanning the entire database all at once. It's a simple way to minimize potential blocking problems that DBCC can cause.
Although uspBuildFraglist performs a simple task, it lets you check fragmentation without aggravating performance problems. Be aware that the procedure will produce more accurate results if you run it after any jobs that affect database-file allocations—for example, database shrink jobs—or purge data.
Prev. page
[1]
2
3
next page 
