Have You Heard?
The largest online event for SQL Server, Business Intelligence, SharePoint and .NET is about to happen – it’s just days away! The SSWUG.ORG Ultimate Virtual Conference is next week – it’s easy to register and save your spot – there is even a quick video on the home page of the conference that gives all the details –
But you need to act now – it’s only a few days away. When was the last time you attended a conference (70 sessions, 20 speakers) with a money-back guarantee, no travel, incredible content and full access to presenters? This is the biggest online event, period – check it out!
Watch the video here or Get Registered
(You could even win an Xbox 360 or MSDN subscription!)
Select Views October 12
Check out the Interviews with Craig Utley regarding BI tools and implementations. Craig will be one of the speakers at the upcoming Virtual Conference next week. Is there a single package that meets all the BI needs for a specific industry? Find out in Select Views.
Featured Article(s)
MySQL on CentOS on Oracle Virtualbox
Everyone wants to go virtual, MySQL with Oracle’s VirtualBox can be tricky in areas but here is how.
Featured White Paper(s)
Data Protection: Evaluating Your Business Requirements & Classifying Your Data
The business of business is business, not IT. Your data protection practices, like your other business practices, should be a… (read more)
Featured Script
admin db – find out if an Agent job can be started now
An agent job can already be running when you try to start it programatically. To avoid errors when this occurs, check the job’… (read more)
Indexed Views are Evil
OK. Now that I have your attention I could say the same about anything in SQL Server. The point is that there are many great capabilities in SQL Server that when implemented incorrectly result in more problems that the on you were trying to solve.
Indexed views are one of those great features that can be abused. "How?", you may ask. Well, I have a client that has a complicated queries drawing from a couple dozen tables. He created a view to simplify the joins. As time went on the view was extended to support many different tasks; many new columns were added with numerous case statements handling different requirements. This view is called by the core process of his system at least 30 times per second.
The client heard about indexed views in a workgroup and decided that was the solution to an already bad architecture. He altered the view to add schema binding, and then created a clustered index on the view. He then created additional covering indexes on the view for the different usages. He was surprised when the performance actually dropped after creating his new indexed view.
What was the cause? The database is one with rapidly changing data supporting the view. Now that he added the indexes to his view, it has to be updated each time data is maintained in any of the 20+ tables supporting his view. So, while the underlying tables are being updated, his view is being updated too. OK, that’s how indexed views work. If his data was more static, his system would be fine. However, instead of reducing the load on his server, he increased the load in maintaining additional indexes.
The thing to take away here is that any one solution does not apply to every situation. Take the time to understand the ramifications of your choice, and be sure to test it before turning it on. Especially test it with a realistic load. This developer tested his code using the new indexed view with great elation. It works great as long as there are no updated to the underlying tables.
Got your "Indexed Views are Evil" war story to share. Send it here and I’ll post in a future newsletter.
Cheers,
Ben