Trigger on SELECT

Question

Trigger on SELECT

Vishal Sinha

Ten Centuries

Points: 1174
More actions
December 17, 2010 at 9:41 am

#388462

I know triggers are not allowed on Select statements but here is my issue:
there is some code (that we dont have access to) that runs a SELECT * from TABLE and returns 200 million records. This code, as expected is killing performance of the table and database.
Is there any way I can create a database/ server level trigger or similar code that will kill or not allow any SELECT * FROM Table operations?
we have captured the exact SQL statement from profiler and I can use that if needed.
Thanks!
DBA in distress!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

The only thing I can think of, besides getting the author to change the code, would be to use a resource throttle on it. That's only available in Enterprise, if I'm not mistaken.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lowell SSC Guru Points: 323518 More actions · Answer 2

you might use soemthing like this to help track it down.

this can give you the spid of who ran the offending statement if it was recently run:

SELECT

db_name(database_id) as dbname,

user_name(USER_ID) as username,

t.text,* from sys.dm_exec_requests r

cross apply sys.dm_exec_sql_text(sql_handle) t

where t.text like 'SELECT * FROM MYTABLE%'

so from there, assume the session_id (spid) was 55; from there you can use sp_who2 to find more information about who/what ran it:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gregw_lists SSC Enthusiast Points: 130 More actions · Answer 3

I don't mean to belabor the obvious, and there are a bunch of limitations that may make this non-feasible depending on how much of the infrastructure and code you control, but have you considered renaming the 200m row table and replacing it with a view that does "select top 100 *" on the now-renamed 200m row table?

(You'd certainly need to change the code/key dependencies that you *do* control to point to the new table name so at least your functionality doesn't break. Even if the "stupid app" breaks because it now doesn't get-- and can't UPDATE-- the full data. But at least the performance issue would now be under your control, and for bonus points if you can figure out what data the "stupid app" truly needs, you could even tweak your view to supply it.)

Vishal Sinha Ten Centuries Points: 1174 More actions · Answer 4

I ended up doing something similar to this. Have a SP that looks for SPID running that command and then killing it (for now). In the meantime the developers are trying to hunt for the offending code so that we can modify it and use a view instead.

Thanks!

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

Although not pretty, you could build a SQL agent job that runs often (every 1-2 minutes) that looks for the command and kills the connection if it sees it. It wuold be best if you could pick out a specific workstation, user, and app name, so you don't accidently kill something else.. Also this job needs to be VERY lightweight if it going to run a lot, meaning that it doesn't use a lot of resources, you don't want IT to be a performance problem.

Also Resource Governor would be an option if you have Enterprise Edition. Thanks G!

Disallow access from the app? Force the developer to change it? I know not a good solutions but I have seen such decisions before..

CEWII