Tune SQL – WHERE by 2 fileds in one Table – 2 indexes or 1 index with 2 fields?
Posted on 2014-10-21
I have the task to tune one SQL 2008R2 DB. I have follow t-SQL
Select Table1.F1,Table1.F2, Table1.F3, Table1.F4,OtherTable.F10,OtherTable.F11 .... ,OtherTable.F12 FROM …. JOIN ... WHERE Table1.F1 <getdate() AND Table1.F2=2 and …
My question is focus on Table1 where I search by 2 criteria. I wonder I should create 2 separated indexes on every filed, or one index with 2 fields. I know the best way it to measure this via Execution plan, but I think this is a common situation with common recommended solution. I’m focused on speed; My Table1 will have less than 10 000 records, will be relative rarely write, but will be read from this SP all the time.
And I have the same question, but for other table which will have millions of records and will be heavy read and write as well. Then what is the standard solution?