Hi,
I have a development user that is semi proficient is simple sql, and getting better. this individual develops queries in the test environment where he has dbreader rights. once he is satisfied with the query he submits it to me, I run it in live db and copy and paste the results, which can be a number of tables, to excel and return it to him. to eliminate the requirement of my involvement and to allow Devo quicker access to results in response to data requests I am considering giving him dbreader access to the live database and am concerned that he will accidently write a query that eats all sql server resources or starts locking processes.
Has anyone dealt with this situation and if yes how, and were there any gotchas?
===========================
Mendy Sudranski
IT Manager
Manhattan Theatre Club
311 West 43rd Street - 8th Fl
New York, NY 10036
Phone: (212) 399-3000 ext. 4105
msudranski@mtc-nyc.org
Hi Mendy,
I am our Tessitura administrator here at the Public and I have given live db reader access to one of our Development staff for the same reasons.
I also had the same concerns as yourself. To get around this, whenever she executes any queries, I instructed her to put the following statement at the top of her query:
set transaction isolation level read uncommitted
This is the same as putting ‘nolock’ after every table and ensures that whatever she runs, it’s not going to bring the database to a halt. Haven’t had any issues as yet!
Natasha
This is just what I’m looking for. I’m going to expand on your set statement to
SET DEADLOCK_PRIORITY low
SET LOCK_TIMEOUT 1800
SET QUERY_GOVERNOR_COST_LIMIT 600
And adjust as necessary but I think these should do it.
using QUERY_GOVERNOR_COST_LIMIT without activating and maintaining sql statistics requires you to use a higher seconds count since sql server's query run time estimates are way off.