Query large table with sub-second response time

I am new to SQL Server but I have to store 20 million rows per day (1MB per row) in a table that will then be queried later on. The table has about 30 columns and the query can be based on any of these columns. The table will store data for up to 7 years before it is archived.

In order to get sub-second response time with 40 concurrent users what is the correct approach:

1) Is creating an index on every column the right way to go? 2) Based on the data size should the table be partitioned?
3) Do I need more than 8 cores/32GB Ram for the sql server? 4) Is there some other searching add-on that should be used with SQL Server?


more ▼

asked Aug 01, 2012 at 05:48 PM in Default

avatar image

10 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

There is no simple answer to this question, it all depends on a lot of things, but I give you some recommendations. I would recommend you to partition the table. By using partitioning, SQL server may use partition elimination to exclude the partitions that will not be used.

Adding an index for every column might help you a little bit, BUT it will not solve every query (and will decrease insert performance and will probably give you a lot of index fragmentation) as you'll probably get a lot of index seek + key lookup to get all the columns you need in the result. In theory, You can of course put the other columns in the include clause, but that will increase the amount of data 30 times and will decrease your insert performance. In reallity you can't to that.

Column store index implemented in SQL server 2012 for this type of scenario (adhoc querying). If you don't need to update or delete old data, I would recommend you to create a Columnstore index, with every column included. That will increase the performance very much, and it is a good reason to upgrade to SQL Server 2012 Enterprise Edition.

more ▼

answered Aug 03, 2012 at 09:56 AM

avatar image

Håkan Winther
16.6k 37 46 58

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 01, 2012 at 05:48 PM

Seen: 1193 times

Last Updated: Aug 03, 2012 at 10:01 AM

Copyright 2018 Redgate Software. Privacy Policy