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.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply