September 15, 2025 at 3:25 pm
Hi, we put together an extract that runs every 15 minutes against what i believe is a homegrown somewhat unstable shop floor control app that runs separately for a number of plants on separate sql and app servers. The app and data were separated in recent years to make all of this more stable.
We've changed the order of operation on the extract now once to try avoiding contention with the shop floor app itself. I can provide more details later but the core table involved is more or less an orders detail table with 10.4 million records and 15 indexes including the PK. I once worked for a company that forbade more than 4 indexes on any table.
The main predicate in the extract query asks for records with the order id (which is a non clustered covering index on order id) being >= to X which shaves the population down to 5,247,905 records. The other extract predicates are 1) 2 on the order date which is varchar(30) mm/dd/yy , yes i said varchar , which filters a cast on order date to date, 2) a company name that comes from a join to a very small customer table .
3 of the 15 indexes are on order id either with no include columns or the order id itself. A few of the other indexes are on order id and other columns.
Out dba says that even if the explain plan shows sql will use that order id index, that it still may not be able to use that index at execute time because of the cast of order date to data type date in the predicate, aka a residual predicate. He may be saying it wont act like a real seek as things are now. Our dba feels that adding a computed (persisted) column that is a cast of order date to data type date and adding yet another index on just that date would solve our issue, at least from the standpoint of the query spending too much time in this order details table.
We are going to try to sit with the main developer to understand better his most common query orders of operation and what time of day they generally occur.
I'm looking ahead here thinking we have to consider rcsi. I know from previous experience that rcsi eliminates virtually 100% the possibility of deadlocks. I read a paper on rcsi once and my head almost exploded. does the community feel that a byproduct/tradeoff of using rcsi slows all existing query performance? or maybe even just acid operations? that could be a non starter for our main app developer.
September 15, 2025 at 7:30 pm
this article helped me a bit.
-hopefully the fact that select statements read an older version of the record isnt contradictory to what a shop floor system should do.
-i have to find out what the possible impact is on tempdb. according to this link high tran apps are at risk.
-row updates and deletes have to create an old version of the record. i'll try to get a profile of those types of trans.
-i'll have to determine if row version travel is likely going to impact us
-i'll need to read about race conditions. I failed an interview on this question once. It could be that its a real problem for the oltp we currently compete with.
September 16, 2025 at 6:19 am
indeed, you'll need to address and monitor tempdb to cope with the rcsi.
Altough, I must admit, we didn't have much problems after enabling it and are using it since it got available with sql 2008r2. (600GBdb)
Applying basic tempdb guidelines ( n-files, size, disk ) will cover most of the problems.
Of course, it all depends on the usage pattern and load on your db system.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 16, 2025 at 7:25 am
Maybe I have not had enough coffee yet, but I am having difficulty understanding how RCSI/MVCC will help with a data extract. Snapshot isolation may help and the issues with tempdb etc will be similar. If you are going to switch a database to RCSI you need to check for write skew anomalies. I would be inclined to follow your DBA's advice first and see if that solves your problems.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply