When I get to a new client I usually find myself using the same set of scripts for maintenance and troubleshooting. These are all drop in solutions for various maintenance issues.
Reindexing. I use Michelle Ufford’s (SQLFool) re-indexing script. I like that it has a throttle and only re-indexes when needed. She also has a variety of other interesting scripts on her blog too.
Server Activity. Adam Machanic is up to version 10 of sp_WhoIsActive. It’s a great replacement for the sp_who* stored procedures and does so much more. If a server is acting funny this is one of the first tools I use.
Backups. Tara Kizer has a great little T-SQL script for SQL Server backups.
Wait Stats. Paul Randal has a great script to display wait stats. The biggest benefit for me is that his script filters out at least three dozen wait stats that I just don’t care about (for example LAZYWRITER_SLEEP).
Update Statistics. I didn’t find anything I liked so I wrote a simple script to update stats myself. The big need for me was that it had to run inside a time window and update the oldest statistics first. Is there a better one?
Diagnostic Queries. Glenn Berry has a huge collection of DMV queries available. He also just highlighted five of them including two I really like dealing with unused indexes and suggested indexes.
Single Use Query Plans. Kim Tripp has a script that counts the number of single-use query plans. This should guide you in whether to enable the Optimize for Adhoc Workloads option in SQL Server 2008.
Granting Permissions to Developers. This is one of those scripts I didn’t even know I needed until I needed it. Kendra Little wrote it to grant a login read-only permission to all the databases. It also grants view server state and a few other handy permissions.
What else do you use? What should I add to my list?