SQL Server 2012 Best Practices – The Book

 People who I have spoken with over a period of time know that I have dubbed this as “The Book“. There are several reasons why I am stoked to get my hands on a copy of this book (should be in transit to me as I type!), but one of the main reasons is I get to check out what 4 of the authors have to say of whom I follow daily from a DBA standpoint.

I am certain that all of the authors deserve kudos for their efforts in getting their respective pieces completed; for me personally I wanted to give a big shout out to Grant Fritchey, Gail Shaw, Jason Strate, and Chris Shaw. These 4 people have helped me tremendously in my career thus far by their efforts in the community and sharing their knowledge.

Looking forward to seeing what the book has to offer. I suggest you give the book a shot if you haven’t thought about it buying it yet.

You can find the book on Amazon here.

Filtered Indexes 2012 Style

I recently was approached by some fellow DBA friends of mine inquiring about filtered indexes in SQL 2012 (never really got into them too much in 2008; just on a minimal basis). In researching I found that they could be very useful for what I encounter on a daily basis.

What exactly is a filtered index? The best way that I can describe it is an optimized non clustered index that is used to cover queries pulled from a very unique and defined subset of data. In researching, I have found that it uses a filter predicate that indexes a portion of rows in a table; not only can it improve query performance, which we all want, but is also can reduce the index maintenance and storage cost.

Filtered vs. Full-Table Indexes

Accuracy is key here. Because the filtered index only covers the rows in the filtered index the statistics will be more accurate; it also is smaller than a full table non clustered index

Reducing Index Storage Costs

Everyone always talks about disk storage and how important it is. A filtered index can greatly reduce the storage, especially when a full table index is not necessary.

Reduction in Maintenance Costs

A filtered index is only changed when the data within that filter changes; and if you think about because of the smaller size of the index it reduces the cost of updating the statistics.

What kind of Permissions are needed

Filtered indexes require Alter permissions on the table or view. The user must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.

Limitations / Restrictions

With everything else filtered indexes do have their limitations, restrictions, and yes even considerations in how you design them.

Some of the limitations are noted below:

  1. A filtered index cannot be created on a view. However there is a gotcha to this which when research is done will show you how the query optimizer can benefit from a filtered index on a table that utilizes a view
  2. Filtered indexes are defined on one table

Other Notes

With any new trick so to speak you always need to try it out in a sandbox or a local environment. I behoove you to conduct your own research prior to implementing. This post just starts to scratch the surface with filtered indexes. I’ve enjoyed the research and testing I’ve done thus far with them on 2012; I think it is definitely worth checking out if you are looking for some new ways to incorporate indexing