Since I keep forgetting to check these base performance assumptions prior to digging in, I thought I would list them here with some description. Yes, this is mostly for my personal edification, so deal with it.
- SQL – These items can be done when you see Buffer Latch waits
- Max Degree of Parallelism (DOP) – This setting should be set to 1. By default it is set to 0, so it needs to be changed. This setting becomes more important as CPU cores scale up. It turns out that SQL is not good at parallelizing T-SQL queries. When set to the default of 0, CPU is artificially increased due to SQL attempting to parallelize threads. Some additional info can be found here. This affects all versions of SQL. Be sure to test first!
- T1118 Flag – This is a trace flag that much be added to the SQL startup parameters (-T1118). This allows SQL to access multiple pages. When you have a multi-core (processor) machine, you should definitely enable this flag on all instances of SQL. In addition to this change you must do the following item to see benefits. You can get more information here.
- File Partitioning – In addition to the T1118 flag, you also need to break out your files. TempDB is the most common bottleneck that you should do this to. In our testing it is at least worth while to create the number of data files (with autogrowth turned off) to 1/2 the number of cores you have. We added all our This also helps other databases that have high contention. Log files can also be broken out if lots or log contention is occurring. However, in our cases, it has typically been limited to the data files.
- Web/App Server
- Wildcarding Disabled – By enabling wildcarding you can have pretty URLs that don’t end in an extension without using a rewrite module (not free for IIS6). However, by enabling scriptmap wildcarding all files go through the asp.net handler. This is obviously a huge CPU burden on the web server, plus an even larger burden if you are backing session in the database because the database CPU and IO goes through the charts. Nothing like having session information for loading a JPG file! You can see what not to do here.
- Kernel Mode SSL – This adds ~10% CPU relief on the web servers. You can’t do anything with client certifications though, so be sure to test and read all the gotchas. Find out more here.
- Debug = “False” – For ASP.NET applications, the <compilation debug=”false”/> causes three things to happen. First, asp.net timeouts are removed. Second, the temp asp.net dlls are batch compiled and in release mode. When it is set to true, each ascx, asax, and aspx is compiled into it’s own dll in debug mode. Third, is that you can’t step line by line in the assemblies, which allows for a large performance increase.