Tuesday, March 20, 2012
Performance Benefits of Using Multiple Servers
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.
> >>
> >>
> >>
>
>
Friday, March 9, 2012
Performance - Best Practices
Hello,
I am having some pretty major performance issues with a cube I've built. My goal for this post is to give specifics related to the data and Analysis Services hardware to try and get some feedback from the community on whether or not I am having valid expectations performance-wise.
Hardware:
-Two dual-core Xeon 2.8GHz processors
-3GB RAM
Data:
The data is retail transaction data which is at the line item level. The most recent years have 50-60 million rows and I am partitioning by year. In addition, each transaction has related discount and tender (payment) data. The tender data has approximately 15-20 million rows per year while the discount data has approximately 10-15 million rows per year.
In my cube, each of these tables have their own separate fact group. I am pulling data from all three fact groups to create quite a few calculated members which build on each other to finally arrive at calculations such as GAAP Sales, sales for a particular product type, etc.
I believe I have my hierarchies and relationships set up correctly in my dimensions. In addition I have aggregations designed on each partition at about the 40% level.
Am I having reasonable expectations that data should be able to come back fairly quickly? I have some reports which take 10-15 minutes that bring back quite a lot of data for the whole company across the last two years. However, even some simple slicing and dicing in the cube browser can be slow. What is interesting to me is that even if I drop my time dimension on the rows axis before adding any measures or other dimensions, it can take 20 seconds or more to respond. All the while when reports are running or when I am browsing the cube myself, the CPU (all 8 in Task Manager [dual dual-core processors]) are spiking at 100%.
While I believe I can get some good responses from MS folks and MVPs for my own benefit, I think any thoughtful posts will benefit the community as a whole by setting performance expectations and pointing out potential pitfalls. I have found that there are not a ton of good resources out there on performance, so I appreciate any tips anyone can offer.
Thanks!
Todd
May be you can find useful information from this blog:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry
For best practices in SSAS 2005, you can find it in presentation from TechEd 2006 (BIN 316):
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!906.entry
Best Regards,
|||
Hello. You have some good links to information posted here already regarding aggregations. I would only advice you to start with checking your attribute relations in your user hierarchies. This is mentioned in Chris Webbs blog.
Mosha has a good post here regarding MDX and performance: http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx
You should also be aware of performance issues with using several measure groups in a cube. Here is a good blog post about this: http://prologika.com/CS/blogs/blog/archive/2006/06/28/1331.aspx
HTH
Thomas Ivarsson
Performance - Best Practices
Hello,
I am having some pretty major performance issues with a cube I've built. My goal for this post is to give specifics related to the data and Analysis Services hardware to try and get some feedback from the community on whether or not I am having valid expectations performance-wise.
Hardware:
-Two dual-core Xeon 2.8GHz processors
-3GB RAM
Data:
The data is retail transaction data which is at the line item level. The most recent years have 50-60 million rows and I am partitioning by year. In addition, each transaction has related discount and tender (payment) data. The tender data has approximately 15-20 million rows per year while the discount data has approximately 10-15 million rows per year.
In my cube, each of these tables have their own separate fact group. I am pulling data from all three fact groups to create quite a few calculated members which build on each other to finally arrive at calculations such as GAAP Sales, sales for a particular product type, etc.
I believe I have my hierarchies and relationships set up correctly in my dimensions. In addition I have aggregations designed on each partition at about the 40% level.
Am I having reasonable expectations that data should be able to come back fairly quickly? I have some reports which take 10-15 minutes that bring back quite a lot of data for the whole company across the last two years. However, even some simple slicing and dicing in the cube browser can be slow. What is interesting to me is that even if I drop my time dimension on the rows axis before adding any measures or other dimensions, it can take 20 seconds or more to respond. All the while when reports are running or when I am browsing the cube myself, the CPU (all 8 in Task Manager [dual dual-core processors]) are spiking at 100%.
While I believe I can get some good responses from MS folks and MVPs for my own benefit, I think any thoughtful posts will benefit the community as a whole by setting performance expectations and pointing out potential pitfalls. I have found that there are not a ton of good resources out there on performance, so I appreciate any tips anyone can offer.
Thanks!
Todd
May be you can find useful information from this blog:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry
For best practices in SSAS 2005, you can find it in presentation from TechEd 2006 (BIN 316):
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!906.entry
Best Regards,
|||
Hello. You have some good links to information posted here already regarding aggregations. I would only advice you to start with checking your attribute relations in your user hierarchies. This is mentioned in Chris Webbs blog.
Mosha has a good post here regarding MDX and performance: http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx
You should also be aware of performance issues with using several measure groups in a cube. Here is a good blog post about this: http://prologika.com/CS/blogs/blog/archive/2006/06/28/1331.aspx
HTH
Thomas Ivarsson