June 2, 2014 at 12:26 pm
Today we had an issue with a process that ran for too long (Over 4 hours instead of 20 minutes). I called our DBAs to find out what happened and why was the process taking so long. The answer that I received was that it wasn't running. After trying different things, we found out that it was "running" but suspended (I never found out why).
I've could have figured this out if I had the VIEW SERVER STATE permission granted to the login. However, DBAs won't grant it to us (mere mortal sql developers). Would there be any good reason to deny it? I can think of many benefits but I don't know if there are any drawbacks. This is a fight I can't win but I'd like to know if there's a valid reason or just envy from our supposed DBAs.
June 2, 2014 at 3:03 pm
I don't know of any drawbacks or risks to security here. It may just be that they don't wqnt to get into the habit of responding to "mere" Developer requests for deviations from "normal" security. There might also be a little paranoia that you could outshine them in a given troubleshooting effort.
And, you're correct... it's not a battle that you're likely to win unless you can somehow make them thing that 1) it was their idea and 2) it would somehow benefit them.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 2, 2014 at 3:23 pm
Luis Cazares (6/2/2014)
Would there be any good reason to deny it? I can think of many benefits but I don't know if there are any drawbacks. This is a fight I can't win but I'd like to know if there's a valid reason or just envy from our supposed DBAs.
Lack of knowledge of other holes it may open. Implicit permissions that are also required to use it correctly that may open other doors. Lack of time to research the request and implications for non-standard security access. Devs are idiots (I'm a dev, and yes, we're idiots). Ability to avoid communicating with the DBAs when there's issues on the servers they need to maintain so they don't know what's happening until it's too late. Overzealous devs telling THEM how to do THEIR jobs because they can only see through the keyhole and they don't want the argument...
... and the list goes on.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 2, 2014 at 3:45 pm
Thank you for your responses. 🙂
I understand the part of devs telling them how to do their work. On another server, where we have the permission, I've found a lot of issues that can be improved. However, I wouldn't mention that as it's not part of my job and they would feel annoyed (except for changing the recovery model to simple on a db because they won't take log backups).
It's a shame that there's nothing to do if they don't have the required skills to give proper advices and we can't have the tools to generate the advice ourselves.
I'd love to have better communication with them, but most of the time their responses come without arguments or their only argument are company's policies.
June 2, 2014 at 6:47 pm
Luis Cazares (6/2/2014)
On another server, where we have the permission, I've found a lot of issues that can be improved. However, I wouldn't mention that as it's not part of my job and they would feel annoyed (except for changing the recovery model to simple on a db because they won't take log backups).
I have found private emails to mid-level guys who'd like a chance to show off are usually my best approach to trying to get work like that passed 'up the pipe' to the admin level when I see it. I don't call anyone out in public and the admin teams get a 'win' on their board for the general IT staff. I also run into less headaches later when a lot of those pan out.
Luckily I don't have to play those politics in my current position. 😀
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 3, 2014 at 8:46 am
You're right. Private emails (or any form of communication) is better when dealing with this. People will react better than when you call them out in public and there are more chances to get the job done.
June 3, 2014 at 9:53 am
Jeff Moden (6/2/2014)
I don't know of any drawbacks or risks to security here. It may just be that they don't wqnt to get into the habit of responding to "mere" Developer requests for deviations from "normal" security. There might also be a little paranoia that you could outshine them in a given troubleshooting effort.And, you're correct... it's not a battle that you're likely to win unless you can somehow make them thing that 1) it was their idea and 2) it would somehow benefit them.
Most of the time, you can win with #2 alone. I usually attain victory with repeated e-mails for the same kind of request, each ending with, "You know, if I had [desired permission], I'd be happy to just look this up/do this myself."
Jason Wolfkill
June 3, 2014 at 12:31 pm
I thought long and hard before I replied to this string...but here it goes,
Being a DBA for 15+ years I have a hard time giving developers access to SQL production. Why?, because you guys do not understand that running code can blow the server into a state of h3!! however if developers could use their powers for good and not run code on SQL production, I am sure DBA's would be happy to give developers permissions.
On the other hand view server state permissions, you should have. I would not see why they cannot give it to you. I give my developers a SQL login that only they use. They are able to view any of their DB's. I also have given them select permissions on their databases to query results to compare. They have agreed to do a Select top 100 on tables that they query. I have a great relationship with my developers. I trust them and they trust me.
I can tell you this about DBA's, they don't like other people getting involved or touching there stuff, DBA's are do it yourself kind of people, we are a different breed and hard to figure out sometimes. We don't mean to be rude or overbearing.
Take them to lunch and get to know them more. Take a query to them asking for help. even of you know the answer. Good communication always prevails.
Good Luck!
MCSE SQL Server 2012\2014\2016
June 3, 2014 at 1:58 pm
lkennedy76 (6/3/2014)
I thought long and hard before I replied to this string...but here it goes,Being a DBA for 15+ years I have a hard time giving developers access to SQL production. Why?, because you guys do not understand that running code can blow the server into a state of h3!! however if developers could use their powers for good and not run code on SQL production, I am sure DBA's would be happy to give developers permissions.
On the other hand view server state permissions, you should have. I would not see why they cannot give it to you. I give my developers a SQL login that only they use. They are able to view any of their DB's. I also have given them select permissions on their databases to query results to compare. They have agreed to do a Select top 100 on tables that they query. I have a great relationship with my developers. I trust them and they trust me.
I can tell you this about DBA's, they don't like other people getting involved or touching there stuff, DBA's are do it yourself kind of people, we are a different breed and hard to figure out sometimes. We don't mean to be rude or overbearing.
Take them to lunch and get to know them more. Take a query to them asking for help. even of you know the answer. Good communication always prevails.
Good Luck!
As a developer, I'll say that you are probably too generous in giving your developers SELECT permissions on production databases. Even assuming the devs are 100% compliant with their "TOP(100)" agreement, a few ill-advised joins or WHERE conditions could still cause horrendous execution plans just to get 100 rows. Even I don't want SELECT (or INSERT or UPDATE) permissions on production databases - when I need to see production data, I send the query to the DBAs with a note confirming that it ran acceptably on the dev DB (which, in our case, is a near-real-time copy of prod data). I like the comfort of knowing that if somebody drove the server into the ditch with a long-running ad-hoc query or goofed up the data, it definitely wasn't me.
We devs here are generally responsible for ensuring the performance of our DB code (the stored procs and functions are too complex in their business logic for the DBAs to spend time deconstructing them for performance enhancements and we have enough data that throwing indexes at troublesome queries willy-nilly would soon get too burdensome). Because of that, I occasionally would like to have access to things like DMVs, permissions to execute stored procs, and SHOWPLAN permissions on production servers, but only temporarily when things go awry. Fortunately, that happens very infrequently because our code is thoroughly reviewed and tested on actual data before it ever gets to production. When it does, our DBAs are very easy to work with on troubleshooting (after all, they get the "the server is slow!" calls from the customer support teams, not us) and don't complain about sending us whatever performance data, execution plans, etc. we ask for.
Jason Wolfkill
June 3, 2014 at 3:36 pm
wolfkillj (6/3/2014)
lkennedy76 (6/3/2014)
I thought long and hard before I replied to this string...but here it goes,Being a DBA for 15+ years I have a hard time giving developers access to SQL production. Why?, because you guys do not understand that running code can blow the server into a state of h3!! however if developers could use their powers for good and not run code on SQL production, I am sure DBA's would be happy to give developers permissions.
On the other hand view server state permissions, you should have. I would not see why they cannot give it to you. I give my developers a SQL login that only they use. They are able to view any of their DB's. I also have given them select permissions on their databases to query results to compare. They have agreed to do a Select top 100 on tables that they query. I have a great relationship with my developers. I trust them and they trust me.
I can tell you this about DBA's, they don't like other people getting involved or touching there stuff, DBA's are do it yourself kind of people, we are a different breed and hard to figure out sometimes. We don't mean to be rude or overbearing.
Take them to lunch and get to know them more. Take a query to them asking for help. even of you know the answer. Good communication always prevails.
Good Luck!
As a developer, I'll say that you are probably too generous in giving your developers SELECT permissions on production databases. Even assuming the devs are 100% compliant with their "TOP(100)" agreement, a few ill-advised joins or WHERE conditions could still cause horrendous execution plans just to get 100 rows. Even I don't want SELECT (or INSERT or UPDATE) permissions on production databases - when I need to see production data, I send the query to the DBAs with a note confirming that it ran acceptably on the dev DB (which, in our case, is a near-real-time copy of prod data). I like the comfort of knowing that if somebody drove the server into the ditch with a long-running ad-hoc query or goofed up the data, it definitely wasn't me.
We devs here are generally responsible for ensuring the performance of our DB code (the stored procs and functions are too complex in their business logic for the DBAs to spend time deconstructing them for performance enhancements and we have enough data that throwing indexes at troublesome queries willy-nilly would soon get too burdensome). Because of that, I occasionally would like to have access to things like DMVs, permissions to execute stored procs, and SHOWPLAN permissions on production servers, but only temporarily when things go awry. Fortunately, that happens very infrequently because our code is thoroughly reviewed and tested on actual data before it ever gets to production. When it does, our DBAs are very easy to work with on troubleshooting (after all, they get the "the server is slow!" calls from the customer support teams, not us) and don't complain about sending us whatever performance data, execution plans, etc. we ask for.
I work at a small firm, me and the devs are tight. any select queries that start to get to out of hand we run on a dev server. In my previous dba position there was no way in h3!! devs were going to get select perms or even server state. It really all depends on company size, data size, trust, and communication. I get what your saying, 100% however I think the DBA's could be a bit nicer to Luis and I am sure they could, with a tool, in future instances and find out what suspended his spid.
MCSE SQL Server 2012\2014\2016
June 4, 2014 at 2:56 am
If you are asking for VIEW SERVER STATE (VSS), you might want to ask for VIEW ANY DEFINITION (VAD) also.
I work for a software vendor, our software uses SQL Server. Implementations are often long lasting projects during which there is close collaboration with the customer. Often we encounter there is no DBA at the customer or a general technical person has this role. We give detailed advice on instance configuration and backup strategy etc. Often, at least temporary, we are allowed to be sysadmin. In the cases where we cannot have sysadmin we ask for VSS to be able to better troubleshoot performance issues. But I think VAD is also very useful as VSS alone does not allow you to view e.g. sys.master_files. A query to view IO stalls by file (from Glenn Berry) would return nothing with only VSS but works fine with VAD added. You could exclude the join on master_files but this is far less readable.
-- Calculates average stalls per read, per write, and per total input/output for each database file (Query 19) (IO Stalls by File)
SELECT DB_NAME(fs.database_id) AS [Database Name]
,mf.physical_name
,io_stall_read_ms
,num_of_reads
,CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]
,io_stall_write_ms
,num_of_writes
,CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]
,io_stall_read_ms + io_stall_write_ms AS [io_stalls]
,num_of_reads + num_of_writes AS [total_io]
,CAST((io_stall_read_ms + io_stall_write_ms) / (1.0 + num_of_reads + num_of_writes) AS NUMERIC(10, 1)) AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN sys.master_files AS mf WITH (NOLOCK) ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC
OPTION (RECOMPILE);
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply