SQL Server Tuning Wizard, or How to take a web site down

SQL Server Tuning Wizard, or How to take a web site down

So today I was getting impatient with some of the load times on SCCAForums.com, so I decided to run the SQL profiler on the database to see if there were any suggested improvements to speed it up.

I ran the tuning wizard and it came up with a suggest that would be a 93% improvement, sold, lets run the script!

Fast forward, an hour later, when I go to make a post on the server and boom it fails. WTF? "Hey Dave, can you post and see if my ban on your account works". He gets the same error.

Not good. Anyways, SQL server's tuning wizard created a view, and when applying an index to that view it setup a requirement for ARITHABORT ON to be set when inserting into the cs_posts table.

It took me a while to fix it, but I think everything is back up and running now. I removed the index on the view that was created :(

Lesson learned, don't just execute random SQL on your database, even if SQL server suggested it!

Recent Comments

There are currently no comments. Be the first to make a comment.

Add Comment

Please add your comment by filling out the field(s) below. Your comment may need to be approved before it becomes visible.
Enter your first name for display with the comment
Enter your last name for display with the comment.
Enter your comment here.
If you can't type DNNRocks in, you can't post, plain and simple.
Submit Comment Cancel

Chris Hammond

Chris Hammond is a father, husband, leader, software developer, photographer and car guy. Chris focuses on the latest in technology including artificial intelligence (AI) and has spent decades becoming an expert in ASP.NET and DotNetNuke (DNN) development. You will find a variety of posts relating to those topics here on the website. For more information check out the about Chris Hammond page.

Find me on Twitter, GitHub and LinkedIn.