After initially viewing my response, I was a bit harsh. But so many factors could be at play, if this is SERVER related, it could be fragmentation, disk caching, poor SQL setup, a milieu of things on their end they need to analyze.

How many reports cause this MEMORY peak, which tables are you hitting (financial/auditing/log). Sometimes it is as simple as adding a single field to a join that will allow sql to utilize a more efficient index.

---

1. Determine if other reports also show spikes, 3.4g is kind of ridiculous. If other reports also have MEM util greater than 1g, there are other things going on.

2. Copy your sql into mgmt studio and analyze it, it will tell you if bad indexes or unindexed tables are slowing things down.

3. Evaluate the server setup that holds your database, there are so many times netcom has alluded to bad programming, when in reality with new VM's and servers, there are so many minute configs that can affect SQL performance the problem is theirs.

4. Work together, with the IT/netcom team, have them analyze if SAN storage caching is f'n up or maybe even a SQL setting isn't maximized.

-----

It will be a process, but don't let the blame get thrown directly at you; programmers get the raw end of the deal, when the fact is, most hardware people think everything is plug and play and don't take time to adequately setup SANS/SQL PARMS/SERVER PARMS/CLUSTERS/VMs

In the end sometimes a third party SQL consultant may be necessary, but the blame probably doesnt single handedly rest on the report.

You would not believe the speed benefits we got when we took our production SQL off of a VM (high end mind you) and was given it's own server, and the hardware guys let the programmers involve themselves in the setup.

Fact is: the more automated these systems have become the less time hardware people have invested in truly understanding the equipment they work with. SQL/Crystal is finicky and you can't just throw more power at it and expect it to get better,

Analyze Analyze Analyze, see if they can run some performance monitoring on their end to determine where holdups are occuring, for all you know it could be cache settings of the VM, holding large transactions in limbo while the report is running because of a minor config setting on the VM.

You would be surprised how many times a "bad report" was an incorrectly configured VM setup recently that didn't allocate enough power, or was configured on systems that ran equally power applications that just mess everything up.

-----

Are the tables correctly indexed, or do the systems bog down on the client end.

I would run the report in Mgmt Studio, and analyze the query.

See if you can make some changes, add tables indexes, whatever.

Lockwelle will want you to try stored procedures.

I would evaluate the fields you are pulling, do you use sql expressions, are you limiting their variable size to appropriate lengths.

Without the report I can't help much beyond this, but this is all on you bud, increase your disk caching if you want to stay within the same format.


Edited by comatt1 - 30 Nov 2012 at 10:21am