SQL Server Performance Forum – Threads Archive

Need your opinions

I just got the following email from an outside consultant and I need your advice if its baloney or if I should call in another consultant to deal with this problem: — =========================
I just heard from xxxxx that our SQL Server and Reporting Service are all configured using the default values. I’m glad that this issue is caught. Those values have vital impact on the success of an application. The default values definitely won’t work for any decent size of business application and xxxx is one of them. It is a value lesson to learn.
— ========================= The only performance problem we have had so far is a report crashing the reporting services because it is too large. Considering our data is growing I suggested redesigning the report rather than change the memory settings. After all, a report that large is meaningless to a human being. Thanks,
Eisen

You should check and run a performance baseline during the busy times and normal times on the system, it is not true in all cases stating default values doesn’t fit to the business. Profiler & SYSMON are best tools to capture information. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

Thanks Satya, It sounds like I should ask the consultant to prove it with the output from a performance monitoring tool. I dont think profiler will help us at all with identifying problems with reporting services, no? Thanks,
Eisen

profiler will certainly help you if your reports are bottlenecked by calls to sql server relational engine. SqlSpec – a fast, cheap, and comprehensive data dictionary generator
for SQL Server 2000 and 2005 and Analysis Server 2005 – www.elsasoft.org

quote:Originally posted by jezemine profiler will certainly help you if your reports are bottlenecked by calls to sql server relational engine.

There is no bottleneck in SQL, it is returning the data just fine. The problem is that the query is returning too much data, so the consultant is saying we should increase the memory on reporting services, which is already utilizing 2gig. I think the most it can use is 3. However, the data is going to keep growing at a good clip so I see this solution as only a temporary a band-aid.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Eisen <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by jezemine profiler will certainly help you if your reports are bottlenecked by calls to sql server relational engine. <hr height="1" noshade id="quote"></blockquote id="quote"> There is no bottleneck in SQL, it is returning the data just fine. The problem is that the query is returning too much data, so the consultant is saying we should increase the memory on reporting services, which is already utilizing 2gig. I think the most it can use is 3. However, the data is going to keep growing at a good clip so I see this solution as only a temporary a band-aid. <hr height="1" noshade id="quote"></blockquote id="quote"> If the query is returning too much data, shouldn’t you fix the query so it returns an appropriate amount? nobody will page through a report containing 100k rows for example. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> <hr noshade size="1">SqlSpec – a fast, cheap, and comprehensive data dictionary generator for SQL Server 2000 and 2005 and Analysis Server 2005 – www.elsasoft.org

Rule of thumb : Only return minimum required data back to the client (rows and columns) Roji. P. Thomas
http://toponewithties.blogspot.com

On the other hand can you specify the hardware on this system and SQL edition used. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

]]>