Using sp_BLITZ to verify SQL configuration
I’m not an SQL expert so I imagine I make a lot of common mistakes when setting up databases. I have just discovered sp_BLITZ by Brent Ozar, which is a set of scripts that will assess the configuration, security, health and performance of an SQL database. I’ve downloaded the stored procedure and about to run it on the SQL 2012 Express instance installed by SharePoint 2013 Foundation. Here are the steps if you’d like to do the same:
1. Download the script from http://www.brentozar.com/blitz/
2. Open Microsoft SQL Management Studio
3. Connect to the SharePoint database instance
4. Create a new query and paste the script
5. Scroll to line 3831 and uncomment the section that will execute the script
6. Execute the script
7. Review the results
The results are listed in priority order, so the most important issues are listed at the top. Helpfully, links to documentation are provided with each issue identified.
So, based on the results it seems I have quite a few things to fix. Most of them are not critical and shouldn’t cause performance issues but some are security related so I’ll get them fixed first.