Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a windows service that is inserting 1000 rows at 1 minute intervals to a SQL Server 2008 Standard database table that looks like:

id, datetime, key_id, value

key_id is a foreign key to another table that looks like id, name

I'm building a localhost website reporting interface in PHP. I will be doing queries from this interface like:

  • Last 7 days of data for key_id's 1,2,50,377 at 1 minute intervals
  • Last 30 days of data for key_id's 40,47,732,400,43,22,18,5,14 at 1 hour intervals
  • 1 hour intervals for key_id 7,8,20,40 for all of May 2009

And I want it to be as fast as possible.

How should I tune SQL Server to be performant in this case?

share

migration rejected from stackoverflow.com Sep 30 '13 at 14:38

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as too broad by Mark Storey-Smith, Max Vernon, Mat, bluefeet, Nick Chammas Sep 30 '13 at 14:38

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

1  
is the data being inserted a balanced mix of the KEY_IDs you mentioned? Or is there 900 of one, and 3 or 4 of the others? It would be nice to see actual SQL queries. –  Max Vernon Aug 29 '13 at 22:09

1 Answer 1

Your sample queries always include both key_id and date, so it would be sensible to base an index on these columns. The main question is which column should be first and which second. This depends on the selectivity. The first column should be the one with the highest selectivity according to your query, so if you have more different days than different key_ids in your table, use the datetime column first, otherwise put the key_id in front.

If you have queries which only supply one of these columns in the WHERE clause, you could also resort to have an index for each of the columns, with the other as INCLUDED COLUMN.

Regarding the clustered index you have several options. Assuming the id column is an IDENTITY, you can use it for a clustered index. However that will lead to potentially expensive lookup operations with millions of records (i.e. a query over a month with most of the key_ids, where you also want to retrieve the value column(s)).

Another option would be to make one of the aforementioned indexes a clustered one. With the datetime in front (or as single key column) you will get almost no fragmentation, so the only drawback would be a slightly bigger size since its not unique, which forces SQL Server to add a 4-byte "uniqueifier" to each index entry. With the key_id in front you will get fragmentation which slows down both insert and range query performance (add padding and a reduced fillfactor in this case to reduce page splits and fragmentation).

Yet another option would be to partition the table in date ranges. With enterprise edition this would be built-in, but you can still do it manually with standard edition by creating multiple tables (say one for each month) and joining them with a view. The definition of the view has to be updated each time you add or remove another month table and this only makes sense performance-wise if you use date as a clustered index (maybe in combination with key_id) and add constraints to each table that restrict the date range to the respective month for the table. Then SQL Server can reliably exclude tables from the query which don't fall into the selected date range.

This last approach also gives you the opportunity to move older tables with unchanging data to read-only filegroups, greatly speeding up your backup process. Also getting rid of old data gets as easy as dropping a table (and modifying a view or two). But this only makes sense if the vast majority of your queries uses significant date restrictions.

Hope that helps.

share

Not the answer you're looking for? Browse other questions tagged or ask your own question.