query governor cost limit option
-
Hi guys,
I need an advice on how I can set this governor cost limit option for certain users or roles that is accessing my database. I know that I can use the sp_configure to configure it machine wide or set it manually just for that session, but I can do that automatically using some sort of logon trigger to set it. We have a problem with developers sometimes running long queries locking our live database and want to do a problem prevention here.
Appreciate your help.
Thanks.
-
Much easier to implement a rule that developers DO NOT USE THE PRODUCTION DB for development. They should use a development DB to do development. You should crack down on access and eliminate this - unless, of course, you feel no responsibility as a DBA for your database.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
SET PROTECT_PRODUCTION_DATA_INTEGRITY_MODE ON
Developers running queries in production ??? That implies that they are 'developing'. A rather severe violation of protocols.
SET EXTREMIST_MODE ON
Solutions:
- Death by hanging
- Death by firing squad
- Death by lethal injection
- Death by keel hauling (my personal favorite)
- Death by disembowelment (for the more medievel crowd)
SET EXTREMIST_MODE OFF
SET PROTECT_PRODUCTION_DATA_INTEGRITY_MODE ON --> this setting should be ON at all times !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Rudy and David are right on target;
You KNOW it is only a matter of time before one of your developers runs an UPDATE or DELETE statement against production as well; whether they do it against critical data or something innoculous is irrelevant; you should lock them out immediately and make them restore a copy locally for testing;
a developer promising he won't use the production database anymore is just a lie. change passwords , drop users, lock them out. Now.
I'll bet a dollar they are connecting as SA or as an Administrator and not as a role with db_datareader only.
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! -
SET PROTECT_PRODUCTION_DATA_INTEGRITY_MODE ON --> this setting should be ON at all times !
Thanx Rudy, I hadn't laughed that much in a long time. Not that it's funny topic but if you could just say the face I imagined when I read that line (angry DBA with lasers in the eyes).
-
Glad to help brighten your day !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
I have the developers put into a role which is assigned the SELECT for tables only. However, that too put the tables into a LOCK situation when new members are not aware of the long / expensive queries they are running.
No solution or alternative to this?
Thank you.
-
Unfortunatley unlike Oracle, SQL Server does not have native features for query governor on a login basis. We could however setup a job to query sysprocesses and kill anything process belonging to a developer if it is longer than a set threshold and then schedule the job to run once a minute.
-
Setup read-only copy of production for developers to run their queries against.
-
If you set up an additional database for developers to execute 'read only' queries on the same production server you may have issues other than locking. These might include overall system slow down due to additional load and potentially tempdb size issues. Then there is the process of refreshing the database. How up to date does the 'read only' information have to be ?
Your best option still is to get the developers off of the production server. Put the 'read only' copy on a develoment server.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
"Put the 'read only' copy on a development server."....that is what I implied...I was expecting an element of mind-reading
-
Sorry Andrew ... it was a late night for me and I forgot my 'swami' headdress today
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
The suggestion from cmille19 sounds interesting and should at least kill the connection above the limit that I specify. Will write a script for that.
Thank you to all.
-
I can't help but feel you've got earplugs on, or just selective hearing.
Either way you are just not listening. Having developers actively querying the production server is a no-no.
The potential for pain for all is enormous - escpecially when compared with the cost of getting them on a separate development box so the only people they get in the way of is themselves.
Class = DB101
Lesson 1 = "Do NOT let developers near the production box - period'
You should be listening to the advice of these wise people.
-
not fixing an issue like this could cost you your job. The great thing about posting here at SSC is you can build off of other's experience, and avoid the major gotcha's that others have already tripped over. Everyone here is giving you sound advice to get developers off of production;. Take advantage of the advice! Everyone is speaking from experience.
Imagine the president of the company is updating some accounting information that it took him a while to data enter; he ends up getting a time out error because one of your developers has a lock on a table due to a long running query. the data he entered could be lost.
Other Users's Transactions can be rolled back or timed out, even if the developers have read only access because they are accessing data.; you already stated that it is a known issue about long running queries.
Why? here's a typical example: developer is going to update some table from another; he does a select * from sometable, just to see/confirm the spelling of the column names, not realizing the table has 14 million rows or something.
goos luck!
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!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply