1

DISCLAIMER

I am a software developer by trade, but sometimes have to put on my 'dba' hat in the absence of a true dba.

SETUP

I am working on a project where:

  • SQL Version is SQL Server 2008 R2 (no SP installed).
  • They have 32GB of RAM, but 28,672MB is the max server memory allowed.
  • The primary usage of the database is hosting the db for an account / business management system called 'Aderant'.
  • I have added SQL Reporting Services
  • I built a few Reports with Report Builder to report on AR and open invoices,
  • I am experiencing performance issues for larger data sets including CPU spikes to 100%, some reports do not return at all.

QUESTION

Do I need to request more RAM on this server for the additional overhead of running Reporting Services?

Couldn't find anything specific to this on the MS Docs site, searching for "SQL Server Handware and Software Requirements".

Thanks!

Improve this question
3
  • 2
    Install those service packs!
    – LowlyDBA - John M
    Commented Sep 27, 2019 at 17:40
  • 1
    Better yet: make them use a supported version of SQL Server.
    – dean
    Commented Sep 28, 2019 at 8:41
  • Yes. Agreed. Sometimes that battle takes a while and includes many reminders and ultimatums. Leverage is not always easy to come by.
    – Glenn Ferrie
    Commented Oct 1, 2019 at 18:50

1 Answer 1

Reset to default
3

Report Server executes the query and stores a copy of it within its cache. This is outside of the SQL Server process and so you will need to account for it when architecting your installation. Generally, the biggest winner I have noticed when I have done installations myself is the number of CPU's. RAM helps of course, but I have found additional CPU's to have the biggest gains - no point having 1TB of RAM if your request is stuck in a queue on a 2 core machine!

There is a very good (but lengthy) white-paper on MS-Docs called Planning for Scalability and Performance with Reporting Services which outlines the difference between different hardware setups, scaling and provides some very good advice on how to architect a SSRS solution. I would highly recommend that you check it out.

Improve this answer
1
  • Thank you! I will look into the documentation.
    – Glenn Ferrie
    Commented Sep 27, 2019 at 19:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.