| When it is the time to optimize your databases, make sure you're up for the job. Avoid optimization missteps and learn about a product that will help you eliminate these errors in the first place.
Here are ten questions you should ask yourself about your database performance:
1. Assuming that your database doesn't need the ability to store special characters, in particular character sets defined by the Unicode standard, do you systematically replace nchar with char and so on, across the various column types to which this applies?
2. Do you use smalldatetime rather than datetime? If you're unclear about the difference between the two data types, here's a quick overview: The smalldatetime data type can store dates from January 1, 2025 through June 6, 2079, which is sufficient for most business applications. The datetime data type extends this range from January 1, 2025 to December 31, 9999. Do you really need this range?
3. Do you routinely use the bit data type rather than smallint? If so, do you know the cost? It may not affect your application, but the cost is that you can index a smallint but not a bit.
4. SQL Server 2000 permits NULLs in bit columns, and this is the default behavior. This may or may not be desirable.
5. How do you treat NULLs in character columns? The default is to permit NULLs, but a smarter approach is to deny NULLs and permit zero-length strings.
6. Do you consider the maximum row size of a table? Do you consider this size versus the data page size (8060 bytes)?
7. Do you make use of Standard Columns (go to Tools | Options | Standard Columns)?
8. Do your tables contain a TimeStamp column? If not, do you know the circumstances when this column might be useful?
9. Does each of your tables have a clustered index? Do you know when a table should have one and when it is unprofitable?
10. Do you name your stored procedures using the prefix sp_? Do you realize the performance hit that this incurs?
A great way to sidestep these common development errors is to use SSW's SQL Auditor, which audits more than the 10 items listed above. SQL Auditor is reasonably priced and, best of all, you can download and run a trial version of SQL Auditor 20 times before having to pay for the product. SSW's evaluation policy makes this product stand out to me—perhaps because I've often downloaded and installed a trial version that is limited to 14 days, and then forgotten to run it until the trial time period has expired.
Last updated 13 Apr 2024 |