| Author |
Topic |
|
MuadDBA
628 Posts |
 Posted - 2004-05-28 : 14:49:04
|
| We have a very poorly designed application here. Unfortunately, it's an enterprise level app and most of the company's document handling is based on it, so I can't just pull the plug on it. Some of you may remember me asking about the query governor a while ago...this is related.The app is configured so that all connections to the SQL Server come through one ODBC DSN, with a generic username, and are also filtered by a "controlling" server so that I get very very little info about who is sending this query, and I can't just disable the SQL user since anyone running the app at the whole company uses that same SQL id.Wow, talk about long winded intro...OK, so here's what the boneheads are doing:SELECT * FROM CATALOG WHERE ID LIKE '%123456'They are supposed to have the % at the end, but they are poorly trained drunken chimpanzees whose parentage is questionable, so often they mess it up. Despite repeated calls to tell them they are making the system nearly unusable to everyone else.I tried using the query governor to keep them from being able to do this query against the 70 million rows in the table. Didn't work, query governor is useless against select statements that simple. I can set it to 1 and this still executes.I could set up a job to do a dbcc inputbuffer every 5 minutes to check for some goofball doing this sort of query and kill it, but I really hate to do that since it stil allows the queries to execute and tie up my system for up to 5 minutes at a time.What I'd like to do is somehow restrict them from running wildcard queries at all, or find some other way to ensure that this query produces some sort of error and refuses to run.Can anyone help me? I can't open up the guts of the application, unfortunately, and the managers won't let me make the database unavailable until people come and beg for their priviledges back, so I am relying on you, my SQLTeam comrades, to help me try to find a solution to this.Oh, it's SQL 7.0 SP4 |
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2004-05-28 : 15:15:51
|
| Is it a stored procedure call or raw sql being passed to the sql server?- Eric |
 |
|
|
MuadDBA
628 Posts |
Posted - 2004-05-28 : 15:22:53
|
| raw SQL. If it were an RPC I could do a heck of a lot more to stop it. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-28 : 15:44:33
|
| Set up a Profiler that dumps automatically to a table. It needs to trace only that application and ObjectID (CATALOG). Capture the MAX(traceid) every minute or so and search for "SELECT * FROM CATALOG WHERE ID LIKE '%". Anywhere you find it, kill it. Then delete up to the MAX(traceid) you captured earlier. This is REALLY, REALLY, REALLY extreme and not recommended. The idiots should fix the application to not allow this. The downside to this, and with the inputbuffer, is that it could also kill other processes. There's just no way to correct this right without altering the application or killing the users.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-05-28 : 15:49:22
|
| I'm all in for killing the users. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-05-28 : 16:17:53
|
| I think you need to hire Terry Tate!Maybe you can institute a policy of some sort of punishment if you are causght sending %'s to the query? Send out some company wide memo."Three %'s and yer FIRED!!"Scare your stupid users into enlightenment!Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2004-05-28 : 18:02:10
|
| building on the profiler idea, you could filter the profiler to only accept sql statements that have "like" in them. Just set the text data like filter to "%like%" without the quotes.- Eric |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-28 : 18:05:22
|
| Profiler is going to be troublesome. What happens if your trace finds a culprit with spid 50. Then the user disconnects but you've capture this spid already. Then another user connects with spid 50 since SQL reuses them. You then KILL 50. You've just killed the wrong spid!Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-29 : 15:38:32
|
| That's what I was pointing out earlier Tara. It's not an ideal solution at all. The application needs to be fixed...period. This is one of the many reasons Tara, myself, and many others hate third-party applications being placed on our database servers.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-05-31 : 16:05:38
|
quote:
Originally posted by tduggan
Profiler is going to be troublesome. What happens if your trace finds a culprit with spid 50. Then the user disconnects but you've capture this spid already. Then another user connects with spid 50 since SQL reuses them. You then KILL 50. You've just killed the wrong spid!Tara
Well, since I would set it up to run every 5 minutes or so, this isn't likely to occur. The query, when the users execute it, takes 15 - 20 minutes (!!!) to complete, so if I do it every 5 minutes I should be "safe."And I hate 3rd party tools as much as the next guy...you'd think software companies could hire DBAs with real-world experience, huh? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-01 : 01:42:15
|
quote:
Originally posted by crazyjoeyou'd think software companies could hire DBAs with real-world experience, huh?
No need now us Software Companies have got Microsoft's "Best Practices Analyzer" <THUD!>Kristen |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-06-01 : 03:07:43
|
| I think I'd try educating the users before killing them. Maybe I'm too soft but they could respond to a gentle nudge in the right direction.-------Moo. :) |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-01 : 08:33:21
|
No need now us Software Companies have got Microsoft's "Best Practices Analyzer" <THUD!>And, it's soooo user friendly. :)I think I'd try educating the users before killing them. Maybe I'm too soft but they could respond to a gentle nudge in the right direction.Well, that's just boring. KILL EM, KILL EM!!!! MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-06-01 : 11:25:30
|
quote:
Originally posted by mr_mist
I think I'd try educating the users before killing them. Maybe I'm too soft but they could respond to a gentle nudge in the right direction.-------Moo. :)
Nope, we've "educated" them several times. Someone, somehow, is "uneducating" them and they do stupid stuff like this. I'd prefer to just kill them. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-02 : 08:02:22
|
| I think you will find that a pulblic execution will work wonders!Kristen |
|
|
|
|
I need to stop a user query....