SQL 2008 Indexing issues on a large table with many searchable fields
Posted on 2013-11-22
I am working on a project where there is a very large table (28,827,861 rows w/ 32,160.711 MB data space)
90% of the 132 fields in this table are queriable (unfortunately, please do not ask why). I spend last 3 days trying to get the most out of indexing. I realize I can't capture verey scenario, but if I can get most I will be happy. I am not sure how to go about asking for help here as I do not want to put the layout of my table. And I do not want to rename 132 fields to display here. But basically I been using non-clustered indexing. Clustered did not help and takes forever to index. If I create an optimal index per specific query it's great instant run. However I can't do that for every query. I tried creating a non-clustered index where I placed 16 (max allowed) fields to ON clause, and added about 100 fields to INCLUDE clause of the indexing clause. This took about an hour to create index. and helped some queries, but not all my test case queries. In any case, I am wondering from our DB guru's is this an impossible task, or what is recommended for large table with many selectable fields? If you can respond with plain easy to understand explanations I would appreciate it, or I would be spending a day deciphering your answers as I am not a DB expert, just sufficient knowledge to get by. Thanks in advance for your help.