June 19, 2014 at 5:05 am
Awesome one, Andy, thank you for the post. (I got it wrong, but gaining good amount of knowledge)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
June 19, 2014 at 5:21 am
Toreador (6/19/2014)
GilaMonster (6/19/2014)
The problem, however, is that statistics IO lies any time there are scalar UDFs in use. The profiler trace doesn't.This.
+1
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
June 19, 2014 at 5:29 am
Koen Verbeeck (6/19/2014)
I think the motive behind the question is something like this:
the developer has no clue what actual permissions he needs, so he aims for the big guns and asks for profiler.
However, the question clearly states that he only wants to see reads/writes (in other words, IO). The question then says he has only read permissions on the database.
So the experienced DBA teaches the developer SET STATISTICS IO. It meets the requirements and doesn't require extra permissions.
It's a classic bad question and answer. The question is deliberately misleading and the answer illustrates one of the worse traits in IT admins generally - give the user the minimum we decide they need rather than what they ask for or what they actually need. The dev's real requirement (buried in the question) is to optimise a query (and the underlying unstated requirement is to solve a performance problem - which the dev thinks is down to a particular query).
The dev asks for profiler because that's the tool they have used to do this, and they justify by saying they need to see reads/writes because that is probably the first thing they would look at. Not the only thing. Sooner or later they will be back asking to see execution plans and other info from profiler that they need.
The real issue is why can't we give them permissions to profiler ? Apparently he could see transactions on other databases and might slow the server down. On a dev server, does it matter ? If he does impact other devs, then, trust me, they have ways of resolving that between themselves, and if they can't, then get the dev leads to give you the list of all devs who are responsible enough to have profiler access and refer all other requests for it back to that list. Sorted.
Oh, but wait, what's this in the comment on the last possible answer: "getting you fired when he drops a production table". HOW does he do that from the dev server ?
Oh, we've got devs playing with queries and "experimenting" (see question) on a production server/instance. Have I seen that in practice ? Oh yes. Have I seen it anywhere there was a competent DBA ? Hmmm.... nope.
So, really this isn't an experienced DBA properly granting minimum needed permissions, it's a DBA refusing to grant permissions in order to cover up their own failure to do their job properly.
June 19, 2014 at 5:35 am
I don't like this question and answer at all.
There's another problem too: either this is apparently going to happen on a production database which needs to be locked down, otherwise what's all the permission fuss about. So the database is live, there are all sorts of things going on in it, any additional queries the developer runs are on top of existing workload and thie interaction with the production workload is likely to alter the number of physical IOs required for queries (not only queries in this database), which (a) may be damaging to production performance and (b) means that the results of set statistics io are not what happens in the production environment when it doesn't have interference from extra load bunged in by the developer. The latter may or may not be important, depending on what the workload patterns are like, but it isn't safe to assume that it is unimportant. The former is of course a total flaw in the whole scenario - the developer can wreck the production system any time by trying to run queries which just hammer the cache and the CPU and the discs - there is no way if he is a non-trusted developer that he should have permission to run queries on a production database unless it is running on a separate instance which has separate resources assigned to it.
So I don't like this question: it describes impossible objectives (set statistics io won't give the developer information about what his queries do in their normal workload context, but anything stronger breaks security requirements) in an environment which can only (given its apparent security objectives and the access this developer has) have been set up by a lunatic. That's on top of the flaw that Gail pointed out.
Tom
June 19, 2014 at 6:02 am
Carpe Datum (6/19/2014)
Koen Verbeeck (6/19/2014)
I think the motive behind the question is something like this:
the developer has no clue what actual permissions he needs, so he aims for the big guns and asks for profiler.
However, the question clearly states that he only wants to see reads/writes (in other words, IO). The question then says he has only read permissions on the database.
So the experienced DBA teaches the developer SET STATISTICS IO. It meets the requirements and doesn't require extra permissions.
It's a classic bad question and answer. The question is deliberately misleading and the answer illustrates one of the worse traits in IT admins generally - give the user the minimum we decide they need rather than what they ask for or what they actually need. The dev's real requirement (buried in the question) is to optimise a query (and the underlying unstated requirement is to solve a performance problem - which the dev thinks is down to a particular query).
The dev asks for profiler because that's the tool they have used to do this, and they justify by saying they need to see reads/writes because that is probably the first thing they would look at. Not the only thing. Sooner or later they will be back asking to see execution plans and other info from profiler that they need.
The real issue is why can't we give them permissions to profiler ? Apparently he could see transactions on other databases and might slow the server down. On a dev server, does it matter ? If he does impact other devs, then, trust me, they have ways of resolving that between themselves, and if they can't, then get the dev leads to give you the list of all devs who are responsible enough to have profiler access and refer all other requests for it back to that list. Sorted.
Oh, but wait, what's this in the comment on the last possible answer: "getting you fired when he drops a production table". HOW does he do that from the dev server ?
Oh, we've got devs playing with queries and "experimenting" (see question) on a production server/instance. Have I seen that in practice ? Oh yes. Have I seen it anywhere there was a competent DBA ? Hmmm.... nope.
So, really this isn't an experienced DBA properly granting minimum needed permissions, it's a DBA refusing to grant permissions in order to cover up their own failure to do their job properly.
90% agree. But you've omitted the point that hiring developers you can't trust is generally a mistake in the first place. Developers have to be trusted to handle third-line support, for example.
Tom
June 19, 2014 at 6:32 am
What gives? I thought all of us developers got admin rights!?!? :laugh:
June 19, 2014 at 6:44 am
BWFC (6/19/2014)
I got one of Andy's right!!!!!!!
Congratulations man, I am happy 4 u
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
June 19, 2014 at 6:46 am
OCTom (6/19/2014)
What gives? I thought all of us developers got admin rights!?!? :laugh:
You wish
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
June 19, 2014 at 7:04 am
'SET STATISTICS IO', ok good learnt somthing new today.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
June 19, 2014 at 7:34 am
OCTom (6/19/2014)
What gives? I thought all of us developers got admin rights!?!? :laugh:
On dev servers, I'm very happy to give developers db_owner of the DB they're working in, alter trace, show plan and view server state. I will teach them the best ways to use Profiler (hint, server side trace), I will teach them DMVs, I will explain execution plans. Then they don't have to come to me for every single thing they want to do on dev. Having to do so irritates developers and wastes my time.
Production, same rights as normal users, unless there's a good reason.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2014 at 7:35 am
got that one wrong. As a one man developer/DBA/Server Admin, I have god rights and don't have to think about permissions much.
June 19, 2014 at 8:08 am
GilaMonster (6/19/2014)
The problem, however, is that statistics IO lies any time there are scalar UDFs in use. The profiler trace doesn't.
+1
At least it provides an additional teaching moment :hehe:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 19, 2014 at 8:08 am
Koen Verbeeck (6/19/2014)
rhythmk (6/19/2014)
Another confusing question 🙁A newly hired developer who works remotely has asked for permission to run Profiler so he can see the reads/writes used by his queries
Permission to run profiler :unsure:
(Here answer should be Alter Trace)
OR
Require the smallest amount of additional permissions to be granted
:unsure:
However even I am not sure if SET STATISTICS IO is enough to check a best performing query.
I think the motive behind the question is something like this:
the developer has no clue what actual permissions he needs, so he aims for the big guns and asks for profiler.
However, the question clearly states that he only wants to see reads/writes (in other words, IO). The question then says he has only read permissions on the database.
So the experienced DBA teaches the developer SET STATISTICS IO. It meets the requirements and doesn't require extra permissions.
+1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 19, 2014 at 9:56 am
GilaMonster (6/19/2014)
OCTom (6/19/2014)
What gives? I thought all of us developers got admin rights!?!? :laugh:On dev servers, I'm very happy to give developers db_owner of the DB they're working in, alter trace, show plan and view server state. I will teach them the best ways to use Profiler (hint, server side trace), I will teach them DMVs, I will explain execution plans. Then they don't have to come to me for every single thing they want to do on dev. Having to do so irritates developers and wastes my time.
Production, same rights as normal users, unless there's a good reason.
I'm kind of torn on this one.. would you do this if there was a development team of 100+ devs? Trying to keep track of who is trustworthy enough for whatever access can be challenging. Additionally, one hour of downtime for the DEV environment is 100+ hours of downtime for all development put together.
Be still, and know that I am God - Psalm 46:10
June 19, 2014 at 11:02 am
david.gugg (6/19/2014)
GilaMonster (6/19/2014)
OCTom (6/19/2014)
What gives? I thought all of us developers got admin rights!?!? :laugh:On dev servers, I'm very happy to give developers db_owner of the DB they're working in, alter trace, show plan and view server state. I will teach them the best ways to use Profiler (hint, server side trace), I will teach them DMVs, I will explain execution plans. Then they don't have to come to me for every single thing they want to do on dev. Having to do so irritates developers and wastes my time.
Production, same rights as normal users, unless there's a good reason.
I'm kind of torn on this one.. would you do this if there was a development team of 100+ devs?
Yes, but I'd make sure there were multiple databases, probably one per project/feature. Highly unlikely there will be 100 people working on the same project/feature. I will also make it clear that it's their server and if any of them break it it's their problem (I will fix it if necessary, but I want them to be taking responsibility, that's why they have the permissions)
Accidents happen. Hell, I've taken production servers down middle of business day because of a mistake. DBA doesn't mean unable to make mistakes, so why should I expect the devs won't make mistakes. They only have db_owner, so they only really have permissions to break their own and their team's stuff. That happens once, mistakes happen. That happens multiple times, I trust that the dev team will work it out, just as a DBA team would work it out if one of the members was careless on prod more than once.
If there's malicious intent, that's a problem for their manager, or HR.
The devs aren't children, they're not stupid. Treating them as such will just result in an antagonistic relationship as well as wasting vast amounts of time, theirs and mine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply