This post is way overdue. Since I’ve been getting a lot of requests about this specific presentation regarding SharePoint databases, I decided to do two things. First, I recorded this presentation for all my attendees to use as a reference. Now, you might be thinking, “If you’ve already recorded your presentation, wouldn’t that affect attendance in your events?” Yes and No. Yes, because those who have seen the video will no longer attend my presentation. For me, this is a great opportunity to help those individuals to plan ahead and maximize their time while attending events. As IT professionals, we’re busy, stuck in our day-to-day work and don’t even have time to look into some of these best practices that need to be applied in our environment. Often times, we are forced into the let’s-do-things-quick-and-fix-it-later corner because of the constant demand for our time. If the drop in attendance in my presentation/events would mean helping those individuals maximize their time, then, I’m all for that. This also includes those who really wanted to attend my presentations but do not have the means to do so (those in different time zones, different countries, no budget, etc.) And the flip side? No, it wouldn’t affect attendance in my events. Everyone who has seen me deliver a presentation can tell you a thing or two about why they’ve decided to attend my presentations even though they’ve already seen me (or the same presentation) at a previous event. I really work hard to prepare my presentations – the proper use of pictures, colors, fonts, and stories all are done with intent – even if it’s the same presentation delivered at a different event. This is my way of saying, “thank you for taking time off your hectic and tight schedule to attend my presentation.” In addition to that, I wanted them to have a resource that they can use as a reference when they go back to work. I want them to become valuable and continue to grow as an IT professional. After all, that’s my primary mission statement.

So, here it is, a video recording of my most requested topic at SharePoint conferences and events – Database Configuration for Maximum SharePoint 2010/2013 Performance

And, you’ve probably seen the corresponding slide deck.

But this is just the first of the two things I mentioned. Here’s the second one. I’ve written a PowerShell script to check the SQL Server instance that you use for your SharePoint databases. This is the PowerShell script that I use when delivering my presentation on Windows PowerShell for the SharePoint Administrators. It’s also the same PowerShell script that I use when I work with customers who request for my services to review and evaluate their SharePoint databases. The script checks for best practices configuration recommended for SharePoint databases – stuff like MAXDOP =1, disabled autoupdate and autocreate statistics, etc. As SQL Server DBAs, we hate some of these configuration. However, these are all documented and supported. Which means they have to be applied to your SQL Server instances and databases used by SharePoint. In addition, I have also included checks that we SQL Server DBAs consider best practices – separation of MDF and LDF files, regular DBCC CHECKDB execution, backup compression enabled, etc. You can download the PowerShell script from here. Keep in mind that this is not the best way to write PowerShell scripts. I didn’t apply best practices here so that would probably be in my next project.

Feel free to use this script as you wish. It has only been tested on default instances of SQL Server 2008 and higher (named instances have not been considered yet) running on Windows Server 2008 and higher. High availability checks like failover clustering, database mirroring and Availability Groups have not been included yet on this version. Comment on the script for bugs and fixes that you want included, keeping in mind that this is specifically for SharePoint databases. Don’t expect any indexing improvements nor identifying the TOP I/O consumers because there is no way for us to modify those queries without breaking your SharePoint support contract (and I am in no way a lawyer to even argue about the contents of the EULA.)

Enjoy.