[Webinar] Streamline your web hosting managementRegister Today


Using second SQL server for running reports

Posted on 2010-04-01
Medium Priority
Last Modified: 2012-08-14
I am running SQL 2005 Ent edition.  We have an application that runs on our SQL 2005 server and when users try to run reports off that database, it slows down production.  I want to use the same production database on a different server and have the users run reports off that server so it doesn't impact performance to the other users.  What would be the best way to do this.  I know you can do replication, mirroring, log shipping but i am a newbie to SQL and still learning and looking for expert advice.  Thanks in advance.
Question by:klittlejohn1
  • 2
  • 2
LVL 11

Expert Comment

ID: 29355008
Looking at the problem that you have mentioned, I would suggest you to look at the Reports.
Reports only Select Data from the DB. So instead configuring a replica of your Current Database, Check the procedures/statement used in the report and use Table hints like WITH (NOLOCK) which will not lock the tables while reading.

Author Comment

ID: 29361398
Will that help in terms of performance and not using a lot of resources?  That is another issue we run into when large reports are running.  The large reports don't neccessarily lock, it just takes up a lot of resources and i thought putting that on another server would help.
LVL 101

Expert Comment

ID: 29372162
Any ideas on why the reports are slowing down the database?

There are many things you can do to improve report performance.

Are the reports complex?

Do you use subreports?

Are you filtering so the filtering is done on the database?

LVL 11

Expert Comment

ID: 29932960
Look for table Functions used in the Stored Procs. Use Temp tables.

Accepted Solution

klittlejohn1 earned 0 total points
ID: 31764999
We decided to go with log shipping.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question