We have an evil query that reads every row from a table, but should not. To help the devs find the source of that query, I want to make SQL Server error out when any select against that table has no where clause.
An app is doing an evil select with no where. The select reads every row of a table having about 6,500 rows. The select is fast in SSMS (< 1 second), but when run by the app it’s slow (about 1/2 hour). We think that’s because the app is slowly iterating over the result set. It’s an active table for updates and inserts, so other queries may be blocked while this read takes place.
The developers can think of no reason that any app should read every row of that table. They suspect an oversight by a past developer.
The evil query-with-no-where is my top-runner, by far, for having the longest total duration in Query Store.
I know which app is issuing the evil query by using SQL Profiler to identify the app name, host name, and DB user name. The app’s developers have been unable to locate the source of the evil query. I want to help.
I want to cause any select against that table that has no WHERE clause (i.e. selects every row) to fail immediately. Our hope is that the app will log the errors, or possibly an end user that triggered the activity will report a 500 error to us, and this will give us another clue to find what code is issuing the evil query-with-no-where.
Our other selects against that table have specific where clauses and only return a subset of the table’s rows. Those selects must be allowed to continue.