PASS 2012 Elections Ballot

Top of the morning to everyone. It’s that time again, today marks the kick off for electing three new seats to the PASS Board of Directors. If you are part of the PASS SQL community I implore you to take a moment, read up on the candidates and select the ones you think will best fit the positions.

This years candidates is yet again another stellar pool. They are:

  • James Rowland-Jones
  • Sri Sridharan
  • Allen Kinsel
  • Wendy Pastrick
  • Kendal Van Dyke

You can view the candidate pages that PASS has put together here

Voting will run through October 12th

I personally would like to wish all of the candidates luck as all five of them would be a great fit.

Get out and vote!

What Is A Disaster?

One of my ex-colleagues recently approached me and asked me what really is considered a disaster recovery? There have been and will continue to be many discussions on this topic so much so that I’m sure I won’t encapsulate all that needs to be discussed, but I will express some of my views.

I believe every business needs to  have a strong disaster recovery plan in place. I have found in my own experiences that building a formidable disaster recovery plan can prove difficult as it is something most business leaders do not want to think about, but is something everyone needs to plan for. To me if I had to define out what a disaster recovery is I would state it this way. The ability to continue work after any number of catastrophic events – this could entail hacking, virus’ and up through natural disasters such as fires, floods, or tornadoes. I’ve heard people say that having a disaster recovery plan in place takes little time and effort – I tend to disagree. I believe it needs to be thought out and practiced on a routine basis.

Each business is different, but I do believe that disaster recovery must take into consideration a few things:

  1. How is the business run
  2. What are all the elements that are required to keep the business going.

Having a generic disaster recovery plan in place, while better than nothing at all I assume, is not the way I would approach it. I really do believe it should be tailored to what the business needs are. Just copying a template of someone else’s recovery plan or design can provide perhaps some great ideas but could and probably will leave out some critical aspects of what your business needs are.

I am a firm believer in planning up front; crafting an individual disaster recovery plan; mapping out the most critical aspects of the business; and then implementing on how and what the processes are to ensure process flow is maintained.

A friend of mine experienced a disaster not long ago, the company did not have a steady disaster recovery plan in place and the one they did have was years old. Needless to say they had to learn a lesson the hard way. I ask this question with the utmost respect and urgency as a DBA, in the event of a disaster, will your business have the ability to pick up the pieces, get back to work, or will things grind to a dead halt?

Do I think it is possible to plan for every event, no I do not; however I do believe wholeheartedly that it is possible to have a solid, firm disaster recovery plan in place that can make all the difference when a disaster does hit. Disaster recovery is to me in  my experiences a difficult but necessary topic of business. I hope that you never have to rely on a disaster recovery plan, but if you do at some point you will definitely be glad that you planned ahead for it.

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