posts - 230, comments - 423, trackbacks - 27

My Links



Follow billgraziano on Twitter

Article Categories


Post Categories


SQL Server

SQL Server Scripts I Use

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?

Print | posted on Tuesday, February 08, 2011 6:45 PM | Filed Under [ Utilities ]



# re: SQL Server Scripts I Use

This is a really nice collection of scripts. I've "starred" the post in my Google Reader because I know I'll be coming back to it to find, download and start using some of the ones I hadn't heard of, like Kizer's and Kendra's scripts.
2/8/2011 6:56 PM | Matt Velic

# re: SQL Server Scripts I Use

Nice compilation of scripts, i've put this in favorites to check em out later.
2/9/2011 11:21 AM | Chevrolet

# re: SQL Server Scripts I Use

Ola Hallengren has a maintenance script that is worth looking at too. it performs backups/integrity checks/reindexing/etc.
2/9/2011 4:25 PM | eyechart

# re: SQL Server Scripts I Use

Good collection. I wasn´t aware of Kendra´s little script regarding the permission grants. I think it´ll be useful for me too.
thx for sharing.
2/11/2011 12:55 PM | Dirk Hondong
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET