I'm a software developer at a rather large company with a small team of software developers. I am the only remaining developer (.NET developer) in the "reports" team of the company. The rest of the developers (7 in total) are in the transactional part of the software. There are two "report analysts" with me.
This company has three full-time DBAs. But lately I've been receiving what, in my opinion, are too many tickets from the DBAs. Most of these tickets are complaints from the DBAs that our scripts are running "too slowly". They decided that 3 minutes should be enough for any script, and anything that takes more than 3 minutes needs fixing. The other team, the transactional team, basically just does CRUD operations (mostly C, we're into "tens of millions of rows" territory). The company has been bought by a larger company and dozens of people were laid off. There is no money for new servers (production is a '09 Xeon with 24GB RAM, and 10K SAS).
The problem seems to be that the DBA expect any script to run under 3 minutes, except of course, being the reports person, I have to sift through millions of records to get the required statistics (our reports are done nightly because of that reason). The transactional team has also broken down the database to textbook normal form. This means most scripts start off 10 INNER JOINs.
The DBAs messages aren't very helpful. I'm often required to look at query plans and make sure the indices are used. They expect the developers to worry about index creation and preformance. I'm not really sure that, as a software developer, it's my job to worry about indices.
I also have no production credentials, so I have no way of knowing if certain indices are present in production (often I get tickets back that "this table has more indices than data!!!!"). I also have no SP execution permissions (big no, developers have only SELECT permissions in production). And certainly not SHOWPLAN permissions.
The main issue seems that we don't have a copy of production to test our queries against. There is a mockup with a few records, and of course any script runs really quickly against a few records but runs slow on production. So basically I have to make guesses about SQL performance tuning, send the script out to the DBA to deploy (that seems to be their job description), and wait for their ticket next day to tell me in what new way the script has failed or made things worse.
Today I received a ticket with the following description: "X sp taking longer than 3 minutes. Replace CURSOR with WHILE", so this is why I'm asking:
Is this normal procedure for DBA operations? Am I wrong to assume that, if an SP is underperforming, it should be the DBA's job to optimize it? In case it's not the DBA's job, but mine, should I have more permissions to the production DB for the required traces, query plan, SP execution, etc? Is it normal not to have a copy of the production DB to test large queries against?
I would like to present my case to the boss here, but I want to be sure I'm not wrong about where my responsibilites as a .NET developer end.