Bob Taylor came on-site for day 1 of a 5-day consulting engagement between Microsoft and Blackboard. The focus of this engagement is with regards to SQL Server 2005 Performance and Optimization. Bob arrived in the early afternoon. I started the session by reviewing the Blackboard Reference Architecture in order to provide Bob with a high-level summary of the Blackboard Academic Suite. I then discussed our most recent EDB benchmark emphasizing the changes we made in order to demonstrate our tuning and optimization process. We then spent the rest of the afternoon reviewing the system currently housing our Windows EDB benchmark. A breakdown of our discussions can be found below.
As part of the review of EDB, Bob and I jumped around the system (perf-2950-db2) reviewing the changes we had implemented as part of the EDB benchmark thus far. The first thing that came-up was the location of the transaction logs for each of the Blackboard schemas. Bob discussed a point that many others have mentioned before, that being we need to move the transaction log to another LUN. In our current deployment, data and transaction data files reside on the same physical LUN.
The second item we discussed was our recovery model. There are three different recovery modes (simple, bulk and full). All of the schemas are configured to Full, which means everything is being logged. Below are some points from a somewhat relevant, but out-dated article. I am going to discuss this in more detail with Bob tomorrow.
The simple recovery model allows you to recover data only to the most recent full database or differential backup. Transaction log backups are not available because the contents of the transaction log are truncated each time a checkpoint is issued for the database.
The full recovery model uses database backups and transaction log backups to provide complete protection against failure. Along with being able to restore a full or differential backup, you can recover the database to the point of failure or to a specific point in time. All operations, including bulk operations such as SELECT INTO, CREATE INDEX and bulk-loading data, are fully logged and recoverable.
The bulk-logged recovery model provides protection against failure combined with the best performance. In order to get better performance, the following operations are minimally logged and not fully recoverable: SELECT INTO, bulk-load operations, CREATE INDEX as well as text and image operations. Under the bulk-logged recovery model, a damaged data file can result in having to redo work manually based on the operations that are not fully logged. In addition, the bulk-logged recovery model only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes.
Review of TempDB
TempDB is one of the most critical areas for optimizing performance of SQL Server. Most complex query operations take place in this region of SQL Server. When configured incorrectly, performance can be dramatically affected. By default the space is not meant to shrink, nor is it recommended to auto-shrink the space. There is a penalty for this space growing. According to Bob, we need to do a more effective job at sizing this space from the start based on the needs of our application.
For example, in our environment TempDB is configured to start at 1GB and grow by a small amount based on an auto-growth policy. After a 5-hour 4 server cluster test, the TempDB grew to 2.1GB. The TempLog grew to 76MB in size. A quick test shows that a restart of services resizes the TempDB back to 1GB in size. Bob mentioned that sometimes clients might complain that after maintenance in which the database has been restarted that the system is slow or sluggish. Part of the problem could be the fact that the TempDB needs to grow. The second factor could be the flushing of the procedure cache during the restart operation.
A great resource that Bob pointed me onto is this blog from Craig Freedman. He’s apparently really big on describing how query processing works in SQL Server.
Bob also discussed GAM and SGAM (Global Allocation Map and Shared Global Allocation Map) which are important components of the TempDB. One nugget of information Bob gave us is that it is recommended that we create 1 TempDB data file per core (just data…no need to do log). So on our 8-core system that has a need for about 2+GB of space, we would need to create 8 files roughly 256MB in size each. Below are a couple other links worth reading:
Installation and Physical Layout Best Practices
The next area we covered was a great overview. Bob first discussed where to or better yet (where not to install) SQL Server. He mentioned not installing SQL Server on the same file system as the Operating System. He also mentioned that it would be in our best interest to configure TempDB on a third LUN. He mentioned that this could be the cause of unnecessary paging issues.
While were talking about paging problems, Bob discussed best practices for virtual memory. Rather then setting virtual memory to 1.5 and 3 times the size of RAM, he mentioned it was in our best interest to set both min/max to 3 times the amount of RAM on the box. He also mentioned defragging the disk before persisting the virtual memory change.
We jumped into physical file layout of the schemas. Bob wanted to understand why we had 10x4GB files under the PRIMARY File Group rather then 1x40GB file. I was surprised by this question, but essentially splitting the data across multiple files has no impact on performance unless they are on different File Groups and on different LUNs. The conversation jumped into clustered indexes. We looked at a few functions worth spending a little more time reviewing:
Other Links to Review