To answer that question, the starting point is the way i work with SQL Server. My company is mostly working with ETL software, meaning the databases are 99% datawarehouses. That’s totally different to an OLTP system. I’ve seen those and worked with a small number of them for clients but it’s not my primary area of expertise.
The main issue in datawarehousing are incredible numbers of inserts during the night where we have to wait on GAM, SGAM stuff and index updates. So my first wish would be that for an ETL proces we can really easily dump data in an existing table where we only check on unique constraints. Delayed index updates, delayed identity updates would improve the speed of the load processes.
When we filled our datawarehouse, the next part takes place. Users trying to generate reports. They use a wide variety of tooling to generate their pie or bar charts. And when those charts take too long to appear, they start complaining. Indexing is sometimes a solution but sometimes it’s just the sheer amount of data that needs to be processed that kills the performance. If only the database would give a warning signal that the processing of the requested data is expected to last for x amount of minutes. Maybe the time could be measured in cups of coffee.
“This query you’re going to run will take three cups of coffee”.
Thanks for reading and let me know in the comments what you think 🙂