CODEWITHSUNDEEP
sql-serveroptimization
Germstorm
Germstorm

Reputation: 9839

Database tables optimized for both read and write

We have a web service that pumps data into 3 database tables and a web application that reads that data in aggregated format in a SQL Server + ASP.Net environment.

There is so much data arriving to the database tables and so much data read from them and at such high velocity, that the system started to fail.

The tables have indexes on them, one of them is unique. One of the tables has billions of records and occupies a few hundred gigabytes of disk space; the other table is a smaller one, with only a few million records. It is emptied daily.

What options do I have to eliminate the obvious problem of simultaneously reading and writing from- and to multiple database tables?

I am interested in every optimization trick, although we have tried every trick we came across.

We don't have the option to install SQL Server Enterprise edition to be able to use partitions and in-memory-optimized tables.

Edit: The system is used to collect fitness tracker data from tens of thousands of devices and to display data to thousands of them on their dashboard in real-time.

Upvotes: 1

Views: 2421

Answers (2)

DarkScott
DarkScott

Reputation: 31

Here's some ideas, some more complicated than others, their usefulness depending really heavily on the usage which isn't fully described in the question. Disclaimer: I am not a DBA, but I have worked with some great ones on my DB projects.

  • [Simple] More system memory always helps
  • [Simple] Use multiple files for tempdb (one filegroup, 1 file for each core on your system. Even if the query is being done entirely in memory, it can still block on the number of I/O threads)
  • [Simple] Transaction logs on SIMPLE over FULL recover
  • [Simple] Transaction logs written to separate spindle from the rest of data.
  • [Complicated] Split your data into separate tables yourself, then union them in your queries.
    • [Complicated] Try and put data which is not updated into a separate table so static data indices don't need to be rebuilt.
  • [Complicated] If possible, make sure you are doing append-only inserts (auto-incrementing PK/clustered index should already be doing this). Avoid updates if possible, obviously.
  • [Complicated] If queries don't need the absolute latest data, change read queries to use WITH NOLOCK on tables and remove row and page locks from indices. You won't get incomplete rows, but you might miss a few rows if they are being written at the same time you are reading.
  • [Complicated] Create separate filegroups for table data and index data. Place those filegroups on separate disk spindles if possible. SQL Server has separate I/O threads for each file so you can parallelize reads/writes to a certain extent.
    • Also, make sure all of your large tables are in separate filegroups, on different spindles as well.
  • [Complicated] Remove inserts with transactional locks
  • [Complicated] Use bulk-insert for data
  • [Complicated] Remove unnecessary indices
    • Prefer included columns over indexed columns if sorting isn't required on them

That's kind of a generic list of things I've done in the past on various DB projects I've worked on. Database optimizations tend to be highly specific to your situation...which is why DBA's have jobs. Some of the 'complicated' answers could be simple if your architecture supports it already.

Upvotes: 3

Dbloch
Dbloch

Reputation: 2366

Way too broad of requirements and specifics to give a concrete answer. But a suggestion would be to setup a second database and do log shipping over to it. So the original db would be the "write" and the new db would be the "read" database.

Cons

  • Diskspace
  • Read db would be out of date by the length of time for log tranfser

Pro - Could possible drop some of the indexes on "write" db, this would/could increase performance - You could then summarize the table in the "read" database in order to increase query performance

https://msdn.microsoft.com/en-us/library/ms187103.aspx

Upvotes: 4

Related Questions