I am using SQL Server 2005. I have a table that is used for reporting. This table is very big, and will probably grow to be the same size as a transaction table. This table has 29 keys. Out of which 1 is primary key, 20 is sorting keys and 9 are keys with sumindex. I have a report that takes about 3 hours to run based on this table. I am in the midst of trying to see what I can do to improve performance with this table. Some of the suggestions I gather from searches on this website are:
1) Do a table optimise on this table (This is the file --> DAtabase --> Information --> Tables --> Optimise)
--> I have done this, but after doing table optimise, I have found out that it takes longer for the report to complete. What use to take 3 hours, is now taking longer. It took 6 hours to run, and as of now, is still running. The report I am running only reads from this table. It does some writes too, but to some temporary tables like Excel buffer etc. But it only reads from this table, and writes to other temporary tables that it uses for it's internal process.
2) Set the non sumindex keys in the table to MaintainSQLIndex = FALSE
--> I have not done this yet. But will try later. I understand that keys that are used for sorting only, should set MaintainSQLIndex to false for performance. Does this include the primary key?
3) Rebuild Index regularly on this table (This is the rebuild index from SQL Management Studio)
--> I have not done this. But shouldn't optimise table be doing the same thing as this?
Any advice is welcome. thanks.