Up until recently, I've been creating indexes (<20s) and running heavy queries (<10s) relatively quickly on a database containing tables that have up to about 10-12 million rows each.
My newest database has tables with up to 40 million rows each and both my index creation and querying is suffering tremendously.
- Index creation is timing out (even with my Tools > Options > Designers > Table and Database Designers > Transaction Timeout being upped to 120 seconds)
- Queries that had been taking me 10 seconds are now taking 40-50 seconds in SQL Server Management Studio (which is logical), HOWEVER:
- Those same queries which had been taking 5-10 seconds each using library(RODBC) and sqlQuery() through R are now taking around 4-5 minutes each.
I'm working on an i7, recently upgraded to SQL Server Management Studio Enterprise (but the issue was occurring prior to upgrade), and I need these queries running at least semi-optimally.
Each query pulls about 200,000 values, but needs to traverse the entire table; thus, a non-clustered index appeared to make sense, but index creation is timing out.
What have I already done?:
- Upped the Transaction Timeout from 30 seconds to 120 seconds.
- Increased the Index Creation Memory (Server Properties > Memory) from 0 to 33000.
- Increased Minimum Memory per Query from 1024 to 3500.
- Created a miniature version of a populated table (re: 4 million rows):
- Query time is optimal. (retrieving 40,000 rows (10%), ~2sec)
- Index creation is optimal. (<10s)
I'm not sure where the bottleneck is occurring, any ideas?
