October 16, 2014 at 12:26 am
I have a client who constantly runs select statements without a where clause. This has been causing performance issues for some time and I have finally decided to do something about it.
Problem is the only real solution is to create a trigger but unlike other DML commands SELECT is not able to be used.
Anyone have an idea?
“Program (pro’ gram) n. a logical sequence of operations to be performed by a computer that usually results in error messages, v.t. to engage in an activity similar to banging one’s head against a wall.”
October 16, 2014 at 1:56 am
Hope he will not run delete statements without a where clause some day.
I see it's rather department administrator's problem rather then DB admin's problem.
October 16, 2014 at 4:19 am
I don't know of any mechanism of code enforcement at the server that you could do to make this happen. Sorry.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 16, 2014 at 5:59 am
I don't know of anything that would accomplish what you're after, but you may be able to limit the resources he can consume with the Resource Governor if you have Enterprise Edition. Hopefully he's using his own login and not a login used by an application.
October 16, 2014 at 8:14 am
I know this is probably incendiary, but - have you considered disabling their access through SSMS? Or - giving those users their own off-line copy of the DB?
SSMS is NOT supposed to be used for a free-for-all querying. Giving someone that kind of access is a security and performance concern for the entire organization: if they cannot play within the parameters, then they don't deserve the access.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 16, 2014 at 8:20 am
Only allow the execution of stored procedures?
Even if there is a way to enforce a WHERE clause, you can still always write WHERE 1 = 1.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 16, 2014 at 8:38 am
Koen Verbeeck (10/16/2014)
Only allow the execution of stored procedures?Even if there is a way to enforce a WHERE clause, you can still always write WHERE 1 = 1.
... OR ... WHERE 'Who do I like?' != 'YOU'
😀
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 16, 2014 at 1:49 pm
Create a job which runs every 5 seconds and KILL the processes of specific user(s) where program_name is 'Microsoft SQL Server Management Studio - Query' and that reached specified threshold, for example 30 seconds of runtime. That's probably the only solution but it's a dangerous one.
October 16, 2014 at 1:51 pm
Alexander Suprun (10/16/2014)
Create a job which runs every 5 seconds and KILL the processes of specific user(s) where program_name is 'Microsoft SQL Server Management Studio - Query' and that reached specified threshold, for example 30 seconds of runtime. That's probably the only solution but it's a dangerous one.
No need for that. Use a logon trigger to prevent them from connecting in the first place.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 16, 2014 at 2:09 pm
ScottPletcher (10/16/2014)
Alexander Suprun (10/16/2014)
Create a job which runs every 5 seconds and KILL the processes of specific user(s) where program_name is 'Microsoft SQL Server Management Studio - Query' and that reached specified threshold, for example 30 seconds of runtime. That's probably the only solution but it's a dangerous one.No need for that. Use a logon trigger to prevent them from connecting in the first place.
In that case the client won't be able to run any queries at all. That was not the original intention.
October 16, 2014 at 2:12 pm
Alexander Suprun (10/16/2014)
ScottPletcher (10/16/2014)
Alexander Suprun (10/16/2014)
Create a job which runs every 5 seconds and KILL the processes of specific user(s) where program_name is 'Microsoft SQL Server Management Studio - Query' and that reached specified threshold, for example 30 seconds of runtime. That's probably the only solution but it's a dangerous one.No need for that. Use a logon trigger to prevent them from connecting in the first place.
In that case the client won't be able to run any queries at all. That was not the original intention.
No, you'd only kill the connections for SSMS. I guess I should have explicitly stated what I thought was clearly implied by the context of the comments: "Use a logon trigger to prevent them from connecting FROM SSMS in the first place".
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 16, 2014 at 2:19 pm
I guess I may have left out one very important piece of information....an application is generating the query.
The user is presented with a grid and several parameter (where) fields.....problem is they hit the 'get data' button (essentially f5) without entering anything so the query is something like
SELECT * FROM [table1] SOME TYPE OF JOIN [table2] ANOTHER JOIN [table3].
Devs have told me they cant change this and its my problem.
“Program (pro’ gram) n. a logical sequence of operations to be performed by a computer that usually results in error messages, v.t. to engage in an activity similar to banging one’s head against a wall.”
October 16, 2014 at 2:20 pm
ScottPletcher (10/16/2014)
Alexander Suprun (10/16/2014)
ScottPletcher (10/16/2014)
Alexander Suprun (10/16/2014)
Create a job which runs every 5 seconds and KILL the processes of specific user(s) where program_name is 'Microsoft SQL Server Management Studio - Query' and that reached specified threshold, for example 30 seconds of runtime. That's probably the only solution but it's a dangerous one.No need for that. Use a logon trigger to prevent them from connecting in the first place.
In that case the client won't be able to run any queries at all. That was not the original intention.
No, you'd only kill the connections for SSMS. I guess I should have explicitly stated what I thought was clearly implied by the context of the comments: "Use a logon trigger to prevent them from connecting FROM SSMS in the first place".
And I was answering to the original question: how to let the user run ad-hoc queries without affecting the performance too much.
October 16, 2014 at 2:22 pm
Darryn_the_Tired (10/16/2014)
Devs have told me they cant change this and its my problem.
If they can change the application then they are simply lying.
October 16, 2014 at 2:29 pm
They could implement a "default" WHERE clause. Our homegrown reporting solution will insert criteria to only show the last 2 months of results if nothing is passed.
The devs are implementing the option that doesn't involve having to convince the business users that locking up the primary server isn't in the best interest of the company.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply