3

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.

The app is in C# and uses LINQ. The evil query is exactly the same every time, with the same columns in the same order. Simple equality could be used to determine whether the query being issued is the evil one. The query is issued in its own batch without any other queries.

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.

The query

SELECT 
  [a].[Id], 
  [a].[AccountingIntegrationActionTypeId], 
  [a].[CreateDate], 
  [a].[CreatedBy], 
  [a].[FranchiseId], 
  [a].[Message], 
  [a].[ModelObject],
  [a].[ProcessId],
  [a].[RequestedBy], 
  [a].[RequestedFor], 
  [a].[Resolved], 
  [a].[UpdateBy],
  [a].[UpdateDate]
FROM 
  [AccountingIntegrationQueue] AS [a]
7
  • 1
    How does the app generate SQL? ORM, dynamic, embedded, LINQ, .. ? Jul 3 '20 at 2:02
  • Does the query have the same form (column list, tables, joins) each time? Jul 3 '20 at 2:09
  • @MichaelGreen It is a c# app using LINQ. The query has the exact same form every time. Jul 3 '20 at 7:16
  • 1
    The query could have a WHERE 1=1 clause or WHERE LIKE 'A%' OR NOT LIKE 'A%' or some other logic error. Jul 3 '20 at 7:56
  • 1
    @DarcyThomas I've added the actual query to the question. I apologize that I didn't do that initially. Jul 3 '20 at 17:40
5

Given the evil query is long-running you don't have to stress out about trapping it synchronously with its execution. It would be good if the approach was relatively lightweight and easy to remove.

I'd go with an agent job, executed on a schedule likely to overlap with the evil query. Once every few minutes should suffice given the evil query runs for thirty minutes or so.

This job examines all currently-running work by querying sys.dm_exec_requests. If start_time is more than a few minutes ago you likely have your culprit. Find the actual SQL text for each piece of work.

declare @query   nvarchar(max);
declare @victim  smallint;

select
    @query  = s.text,
    @victim = r.session_id
from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle) as s;

A little bit of SUBSTRING(), CHARINDEX() and LIKE will confirm you're dealing with a "SELECT" without a "WHERE". If the evil query is submitted in a batch with other queries you'll have to be more careful when parsing the SQL text.

It can then be ended with KILL(@victim). There is a risk that, between being identified and killed, the evil query finishes and its session id is re-allocated to a "good" query. If evil's execution time is consistently long you can use start_time to reduce this risk. You know your environment and what's acceptable.

1
  • Quite clever, and there's no reason this shouldn't work. We can accept the race condition in order to flush out the evil. I look forward to giving this a go. Jul 3 '20 at 7:28
2

For completeness, here is the T-SQL of the SQL Server Agent Job that I created from Michael Green's answer:

print 'checking for the evil query'

declare @query nvarchar(max);
declare @spid smallint;
declare @spid_str nvarchar(6);
declare @sql nvarchar(1000)

select
    @query = s.text,
    @spid = r.session_id
from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle) as s
where 
    text like '%select%AccountingIntegrationQueue%'
    and not text like '%where%';

if @spid is not null 
    begin
    print 'Found the evil query:'
    print @query
    set @spid_str = @spid;
    set @sql = 'kill ' + @spid_str;
    exec(@sql)
    print 'killed SPID ' + @spid_str;
    end
else
    print 'not found'
2
  • Did this lead you/the devs, to the offending piece of code (in the application)? Jul 6 '20 at 1:20
  • @DarcyThomas Not yet. I'll make an update when we find the culprit. Jul 6 '20 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.