Last time, I blogged on the first in a series of steps you need to take to verify your system is not experiencing any system errors or deadlocks. There are just a few more things you need to verify before moving on.
Start by opening the Server Properties page by right-clicking on the server name in the Microsoft SQL Server Management Studio Object Explorer and then selecting Properties. You want to validate that none of the settings have been changed from the defaults (which can be found in SQL Server Books Online). Look specifically at the Memory, Processors, and Advanced settings. There are no secret tweaks or settings that need to be changed to get better performance out of SQL Server data management software. A great deal of time and brainpower has gone into making SQL Server perform well out of the box, and you want to let that work happen. When we run the Teamcenter benchmarks (where we try to emulate the largest systems we can), we leave everything at the default setting; changing any of the settings will likely only make performance worse.
There is one exception to the above statement and it is true only in the situation where you are running SQL Server on a computer with other running applications (which is never optimal if you care about performance). SQL Server is meant to take as much memory as possible to store frequently used data–that is what we want it to do–so SQL Server can starve out another application if the other application is running on the same computer as SQL Server. Therefore, you need to determine how much memory the other applications need to run successfully, leave some memory for the operating system, and then allocate the rest of the memory to SQL Server.
The Memory Settings screen can be a bit confusing. The large number for maximum memory is the default and means all addressable memory is available to SQL Server. The Address Windowing Extensions (AWE) setting may be enabled depending on if you are running a 32-bit or 64-bit system and how much physical RAM you have installed on the computer.
Now, check that you have your database files on a different drive set than your database logs by right-clicking on the SQL Server Object Explorer database name and selecting Properties. Click on the Files option to see all the files allocated for the data and logs. SQL Server is a “double-write” system (one write to the logs, then one write to the checkpoint, and then one write to the database file), so you do not want I/O contention between the database files and the logs. This recommendation is true for every installation of SQL Server of any size, anywhere (unless you have been told not to for some reason by Siemens PLM Software).
You check tempdb to make sure it is one a different drive set. This is where temporary worktables for sorting and other operations are stored, and you do not want I/O contention between data writes, log writes, and tempdb writes. Again, this recommendation is true for every installation of SQL Server.
If these three sets of files are on the same drives, look up how to move them in SQL Server Books Online.
SQL Server added the RCSI database option specifically to help with applications that have been migrated from Oracle, which uses a different transaction model than SQL Server. Setting this option to “true” on a database causes SQL Server to store some user data in tempdb to create fewer shared locks on the main data tables. This then results in less blocking of other user processes. However, it will also cause more writes on a system (but usually not significantly more) and will have a need for larger tempdb files used to store these user data copies. Teamcenter databases normally have this option set at the database level, so verify this setting with the following command to look at all database options (including the is_read_committed_snapshot_on bit):
select * from sys.databases
If this option is set, you need to ensure that tempdb is not a bottleneck. The easiest way to prevent this is to create a tempdb file for each processor on your computer, and make sure that the files are all the same size and are big enough to handle anticipated data. Read the following article for in-depth advice from the SQL Server team on managing tempdb and ensuring you have enough space for the RCSI usage. It was written for Microsoft SQL Server 2005, but is still valid for Microsoft SQL Server 2008 and Microsoft SQL Server 2008 R2. Working with tempdb in SQL Server 2005.
Now you know the following about your system:
In the next blog post, we can start looking at looking at CPU usage.
Sharon Bjeletich is a database architect and performance and scalability expert who has worked with SQL Server since version 4.2. She is a former senior program manager in the Microsoft SQL Server product group and a former technical officer for the World Health Organization. Currently, she is an independent consultant at SQL Scalability.