0

I have some tables on my SQL Server database that I only run inserts / updates nightly from our services and they are Read-only during the day for our clients (there is no update / insert / delete from the end user).

What is the best way I can setup / config this table so that that I can I have these inserts / updates faster at night (while no "client" uses our service) and faster reads during the day (when clients actually use the table for read purposes) ?

These tables are over 100m rows.

Improve this question
1
  • I went to a mechanic the other day and asked him how to make my car go faster, but forgot to bring it with me so he could see it. Like Tibor mentioned, seeing the queries and ideally what they're doing under the hood - their execution plans, goes a long way in us being able to advise on how to make them go faster.
    – J.D.
    Sep 24, 2022 at 21:35

1 Answer 1

Reset to default
2

You don't give us much to go on regarding:

  • The types of queries during daytime. Analytical queries, that accesses lots of data and does GROUP BY and similar?
  • How the modifications are being performed night-time.

Possibly having a clustered columnstore index can be beneficial.

It might help your day time queries due to compression and ability for the execution plans to go batch mode. (Batch mode is a possibility also without columnstore indexes in 2019 with database compatibility level 2019 and Enterprise Edition, however.)

How it will affect your nightly load is difficult to say without any insights. Perhaps a compression delay for the columnstore index can be beneficial if you after the load will modify the rows. (I.e., after the load with compression delay the data will stay row-based for the time period specified. Else, if you do an UPDATE, the before-image compressed row will stay in there, and flagged in a delete bitmap and the after-image will be in the row based delta-store until approx 1 million rows have been inserted/updated and that delta-store is converted to a compressed rowgroup.)

Columnstore indexes - Data loading guidance

Improve this answer
2
  • the big tables do have columnstore indexes on them already, the thing that I want to know is, these tables, the things that writes into them is our nightly jobs, so i want to know if theres any way i can tune my database to have better performance at these jobs
    – beto
    Sep 26, 2022 at 18:42
  • You still didn't provide any details, so all we can do is to point to a generic article about data loading into a table with a columnstore index. I've added it to my reply above. And, again, look into compression delay if you UPDATE the rows after you have loaded them.
    – Tibor Karaszi
    Sep 27, 2022 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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