Performance Benefits of Using Multiple Servers

My SQL Database and Reporting service are installed on the same server. I've
noticed that occasionally, the server gets pretty sluggish, which is likely
from reports being run. I have another server available which isn't really
being used much. Would there be any significant performance benefits to
separating the SQL data store and the Reporting Service onto two separate
machines? If so, how ugly would the implementation be? Any help would be
appreciated.Two things. RS uses SQL Server for its object and meta data store. My
suggestion is to still have this local to where RS is running (it doesn't
have to be but SQL Server is really good about not using much resources when
under light load which this would be). The data you are reporting against
would stay on your original server.
Note that the configuration I am stating does not cost anymore than have RS
on one server and the object/metadata store staying where it currently is.
The reason is, as soon as you move RS to another server you have to have
another SQL Server server license. So, might as well have the
object/metadata store on the same machine.
RS does all processing in RAM and when rendering a large report will use
lots of resources. But, my guess is that if the slowdown you are seeing is
RS and not other applications going against SQL Server then you might not
see a performance improvement.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"blabore" <blabore@.discussions.microsoft.com> wrote in message
news:3D31B8FD-7915-4E8D-8896-D655657EEA45@.microsoft.com...
> My SQL Database and Reporting service are installed on the same server.
> I've
> noticed that occasionally, the server gets pretty sluggish, which is
> likely
> from reports being run. I have another server available which isn't really
> being used much. Would there be any significant performance benefits to
> separating the SQL data store and the Reporting Service onto two separate
> machines? If so, how ugly would the implementation be? Any help would be
> appreciated.|||Bruce,
Thanks for the response and your suggestion. I've been told by my hosting
provider that our current bottleneck likely at the disk drives themselves,
too many read/writes. It's a SCSI drive, but not RAID. Any ideas if
separating the two will actually help this, or would I just be better off
spending my money on a RAID 5 array?
"Bruce L-C [MVP]" wrote:
> Two things. RS uses SQL Server for its object and meta data store. My
> suggestion is to still have this local to where RS is running (it doesn't
> have to be but SQL Server is really good about not using much resources when
> under light load which this would be). The data you are reporting against
> would stay on your original server.
> Note that the configuration I am stating does not cost anymore than have RS
> on one server and the object/metadata store staying where it currently is.
> The reason is, as soon as you move RS to another server you have to have
> another SQL Server server license. So, might as well have the
> object/metadata store on the same machine.
> RS does all processing in RAM and when rendering a large report will use
> lots of resources. But, my guess is that if the slowdown you are seeing is
> RS and not other applications going against SQL Server then you might not
> see a performance improvement.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "blabore" <blabore@.discussions.microsoft.com> wrote in message
> news:3D31B8FD-7915-4E8D-8896-D655657EEA45@.microsoft.com...
> > My SQL Database and Reporting service are installed on the same server.
> > I've
> > noticed that occasionally, the server gets pretty sluggish, which is
> > likely
> > from reports being run. I have another server available which isn't really
> > being used much. Would there be any significant performance benefits to
> > separating the SQL data store and the Reporting Service onto two separate
> > machines? If so, how ugly would the implementation be? Any help would be
> > appreciated.
>
>|||Good post.
If bottleneck is due to disk, how about having two disk with seperating
database storage and report storage on each disk. which will reduce
bottleneck.
Regarding your database
One of the disk you should have one filegroup and another disk you should
have one more and where less no of calls used tables can be in the other disk
and more intense used tables in first filegroup.
As far as your files you can have it on second disk. So by balancing the
disk access you can improve the performance of your reports.
I think RAID will help to some extent, but proper plan for storing in
multiple disk and using stored procedures , pagination etc.. will help in
improving performance.
Amarnath.
"blabore" wrote:
> Bruce,
> Thanks for the response and your suggestion. I've been told by my hosting
> provider that our current bottleneck likely at the disk drives themselves,
> too many read/writes. It's a SCSI drive, but not RAID. Any ideas if
> separating the two will actually help this, or would I just be better off
> spending my money on a RAID 5 array?
> "Bruce L-C [MVP]" wrote:
> > Two things. RS uses SQL Server for its object and meta data store. My
> > suggestion is to still have this local to where RS is running (it doesn't
> > have to be but SQL Server is really good about not using much resources when
> > under light load which this would be). The data you are reporting against
> > would stay on your original server.
> >
> > Note that the configuration I am stating does not cost anymore than have RS
> > on one server and the object/metadata store staying where it currently is.
> > The reason is, as soon as you move RS to another server you have to have
> > another SQL Server server license. So, might as well have the
> > object/metadata store on the same machine.
> >
> > RS does all processing in RAM and when rendering a large report will use
> > lots of resources. But, my guess is that if the slowdown you are seeing is
> > RS and not other applications going against SQL Server then you might not
> > see a performance improvement.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "blabore" <blabore@.discussions.microsoft.com> wrote in message
> > news:3D31B8FD-7915-4E8D-8896-D655657EEA45@.microsoft.com...
> > > My SQL Database and Reporting service are installed on the same server.
> > > I've
> > > noticed that occasionally, the server gets pretty sluggish, which is
> > > likely
> > > from reports being run. I have another server available which isn't really
> > > being used much. Would there be any significant performance benefits to
> > > separating the SQL data store and the Reporting Service onto two separate
> > > machines? If so, how ugly would the implementation be? Any help would be
> > > appreciated.
> >
> >
> >|||If the issue is disks then this is not a RS problem. RS does all its
processing in RAM. SQL Server has to get the data to RS but at that point it
is all in RAM. So, even if you moved RS off onto another machine you are
likely to not see an improvement if the issue is retrieving the data in the
first place.
I would look other places than the hardware. I have a datamart using just
SCSI drives. A good many active tables have several million rows and I have
a few with 20 million and one with 150 million (but very small records).
Yes, I am moving to a raid configuration but that is more for the fault
tolerance.
I suggest making sure that your tables are properly indexed and you are not
ending up with table scans. Also, don't use filters in RS, use query
parameters. Filters bring all the data over and then filters it. My guess is
that the issue is more database optimization and not hardware related.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"blabore" <blabore@.discussions.microsoft.com> wrote in message
news:D8582F9F-4E08-4CB1-BF36-4ABE7EE8ED10@.microsoft.com...
> Bruce,
> Thanks for the response and your suggestion. I've been told by my hosting
> provider that our current bottleneck likely at the disk drives themselves,
> too many read/writes. It's a SCSI drive, but not RAID. Any ideas if
> separating the two will actually help this, or would I just be better off
> spending my money on a RAID 5 array?
> "Bruce L-C [MVP]" wrote:
>> Two things. RS uses SQL Server for its object and meta data store. My
>> suggestion is to still have this local to where RS is running (it doesn't
>> have to be but SQL Server is really good about not using much resources
>> when
>> under light load which this would be). The data you are reporting against
>> would stay on your original server.
>> Note that the configuration I am stating does not cost anymore than have
>> RS
>> on one server and the object/metadata store staying where it currently
>> is.
>> The reason is, as soon as you move RS to another server you have to have
>> another SQL Server server license. So, might as well have the
>> object/metadata store on the same machine.
>> RS does all processing in RAM and when rendering a large report will use
>> lots of resources. But, my guess is that if the slowdown you are seeing
>> is
>> RS and not other applications going against SQL Server then you might not
>> see a performance improvement.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "blabore" <blabore@.discussions.microsoft.com> wrote in message
>> news:3D31B8FD-7915-4E8D-8896-D655657EEA45@.microsoft.com...
>> > My SQL Database and Reporting service are installed on the same server.
>> > I've
>> > noticed that occasionally, the server gets pretty sluggish, which is
>> > likely
>> > from reports being run. I have another server available which isn't
>> > really
>> > being used much. Would there be any significant performance benefits to
>> > separating the SQL data store and the Reporting Service onto two
>> > separate
>> > machines? If so, how ugly would the implementation be? Any help would
>> > be
>> > appreciated.
>>|||Thanks for your response. I'll see what improvements can be made just through
some DB optimization, and worry about the hardware changes later.
"Bruce L-C [MVP]" wrote:
> If the issue is disks then this is not a RS problem. RS does all its
> processing in RAM. SQL Server has to get the data to RS but at that point it
> is all in RAM. So, even if you moved RS off onto another machine you are
> likely to not see an improvement if the issue is retrieving the data in the
> first place.
> I would look other places than the hardware. I have a datamart using just
> SCSI drives. A good many active tables have several million rows and I have
> a few with 20 million and one with 150 million (but very small records).
> Yes, I am moving to a raid configuration but that is more for the fault
> tolerance.
> I suggest making sure that your tables are properly indexed and you are not
> ending up with table scans. Also, don't use filters in RS, use query
> parameters. Filters bring all the data over and then filters it. My guess is
> that the issue is more database optimization and not hardware related.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "blabore" <blabore@.discussions.microsoft.com> wrote in message
> news:D8582F9F-4E08-4CB1-BF36-4ABE7EE8ED10@.microsoft.com...
> > Bruce,
> >
> > Thanks for the response and your suggestion. I've been told by my hosting
> > provider that our current bottleneck likely at the disk drives themselves,
> > too many read/writes. It's a SCSI drive, but not RAID. Any ideas if
> > separating the two will actually help this, or would I just be better off
> > spending my money on a RAID 5 array?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Two things. RS uses SQL Server for its object and meta data store. My
> >> suggestion is to still have this local to where RS is running (it doesn't
> >> have to be but SQL Server is really good about not using much resources
> >> when
> >> under light load which this would be). The data you are reporting against
> >> would stay on your original server.
> >>
> >> Note that the configuration I am stating does not cost anymore than have
> >> RS
> >> on one server and the object/metadata store staying where it currently
> >> is.
> >> The reason is, as soon as you move RS to another server you have to have
> >> another SQL Server server license. So, might as well have the
> >> object/metadata store on the same machine.
> >>
> >> RS does all processing in RAM and when rendering a large report will use
> >> lots of resources. But, my guess is that if the slowdown you are seeing
> >> is
> >> RS and not other applications going against SQL Server then you might not
> >> see a performance improvement.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "blabore" <blabore@.discussions.microsoft.com> wrote in message
> >> news:3D31B8FD-7915-4E8D-8896-D655657EEA45@.microsoft.com...
> >> > My SQL Database and Reporting service are installed on the same server.
> >> > I've
> >> > noticed that occasionally, the server gets pretty sluggish, which is
> >> > likely
> >> > from reports being run. I have another server available which isn't
> >> > really
> >> > being used much. Would there be any significant performance benefits to
> >> > separating the SQL data store and the Reporting Service onto two
> >> > separate
> >> > machines? If so, how ugly would the implementation be? Any help would
> >> > be
> >> > appreciated.
> >>
> >>
> >>
>
>