Thanks for all the help Grant, that's probably the best answer I ever got 🙂

I guess it really comes down to what edge are we on...

My biggest database has 100GB and 1000 transactions/s... The server has 2000 transactions/s..

First check the code then check the database 🙂

The main problem with our system is that we use dynamic SQL since the interface has over 20 fields to query by and they can all have values or none... (we use dynamic SQL so we don't use "@param IS NULL OR col = @param" or "col = ISNULL(@param, col)").

When I look at index recommendation I almost get an heart attack... Since every column can have a value or not there are more than 40 index recommended...

What I did, since the data is "restricted" by tenant (this is a multi tenant database), is to create a main index with the tenant columns and include the 20 that can be used to filter...

Thanks for all your help and advice...

Pedro