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