I love working with databases, but I hate the nitty-gritty, detail-oriented aspects of being a DBA. (That's why I'm a consultant instead of a DBA with a real job!) Because of my dislike of the nitty gritty, I like the handy configuration-management stored procedures that Microsoft described and published in an excellent white paper titled "Using Stored Procedures to Identify Common Administrative Issues." The 18-page white paper explains how to use more than a dozen new stored procedures to look for the little things that might go wrong with your SQL Server's configuration. The procedures help you automate dozens of configuration checks, such as
- verifying that sp_configure and sp_dboption values are set to reasonable levels
- determining whether tempdb is set to a "too small" start-up size
- checking for out-of-the-ordinary indexoption or tableoption settings
- ensuring that all tables have at least primary key or unique constraint
- checking to see whether clustered indexes have exceptionally wide keys that could be degrading non-clustered index performance
- looking for stored-procedure tools to investigate blocking problems
You can find this white paper and supporting T-SQL installation scripts on the Microsoft Developer Network (MSDN) SQL Server Developer Center Web site.
While you're there, spend a few minutes looking at the rest of the site's content, such as the following white papers:
- a technical discussion of the pros and cons of reimplementing the Duwamish Online sample site using SQL Server 2000's XML capabilities
- a review of the new features in SQL Server 2000's version of Data Transformation Services (DTS)
- a technical preview that lets you map XML Data Reduced (XDR)-based schemas to SQL Server models for integration with SQL Server's new XML capabilities
- a detailed, hands-on review of SQL Server 2000's Indexed View capabilities. (I think that indexed views are one of the most powerful tuning tools in SQL Server 2000's arsenal; you should know when and how to leverage them to your advantage.)
You'll find all of this great content on the MSDN Web site.