x

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?

Thanks.

more ▼

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

tom.browm gravatar image

tom.browm
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

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x374

asked: Aug 01, 2012 at 05:48 PM

Seen: 824 times

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