March 20, 2009 at 1:01 pm
We have a SQL Server 2005 reporting environment, which is having performance bottlenecks with CPU utilization and memory. Apart from throwing more hardware at it was wondering if anybody has had any experience with any load balancing solutions. The users run a lot of ad-hoc queries against the databases so Analysis services or Reporting services is not the answer at the moment for improving performance. Would anybody have any ideas or suggestions?
Thanks,
Vidhya.
March 22, 2009 at 4:52 pm
SQL Server does not have native load balancing solutions ...
But please have a read at these articles ... they might give you some ideas 🙂
http://technet.microsoft.com/en-ca/library/cc966448.aspx
http://msdn.microsoft.com/en-us/library/aa479364.aspx
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 23, 2009 at 1:48 pm
I would start by identifying poorly performing queries and missing indexes before jumping into the scale-out train 😉
* Noel
March 23, 2009 at 1:53 pm
I agree with Noel, I provided the links. But if you were willing to post your queries, and execution plan I am sure someone here can help you out :).
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 23, 2009 at 7:03 pm
I agree.
I would use:
- activity monitor
- profiler
- exec plans
Look for cpu usage, also heavy read/write counts from your trace may help you identify what is doing it.
Someone said:
"Tune the code, thats where the performance is"
can't remember who but I think it was on SSC somewhere, anyway...I believe ther's a lot of truth to it.
also what else is on this box? just sql? or other stuff aswell.
Cheers,
Carlton..
March 23, 2009 at 8:04 pm
vidhya_kumar (3/20/2009)
We have a SQL Server 2005 reporting environment, which is having performance bottlenecks with CPU utilization and memory.
As suggested would start first tuning the queries. Identify the top queries that are running against the server and first tune them.
March 24, 2009 at 7:51 am
Thanks a lot for the suggestions. The server only has SQL on it. The reason for thinking about scaling out or load balancing was because there are a lot of ad_hoc queries that come in through access connections, that is what is causing the bottleneck. There is no way we can get rid of them.
March 24, 2009 at 11:49 am
Training users and limiting their access to pre-designed views that give them required information I take is out of the question?
Another option you might want to consider it get them to do reporting using a better tool like CR? or SQL Reporting...
In Access it uses Cusor API for alot of its work so that can have a huge performace impact also...
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 24, 2009 at 2:05 pm
Access uses a cursor API?!
thanks Mohit I've learned something today 🙂
This is where the resource governor for 2008 could come in handy...
Carlton..
March 24, 2009 at 2:08 pm
Carlton,
Can you educate me on the Resource governor in 2008. Maybe upgrading is an option 🙂
Vidhya.
March 24, 2009 at 2:20 pm
It is a new feature in 2008 (I think only in enterprise version mind you) that lets you create resource pools to throttle performance for certain processes eg. MAX cpu = 50% for a certain process.
http://www.sqlservercentral.com/articles/Resource+Governor/64034/
heres a good introduction, search on this site and MSDN for resource governor.
Cheers,
Carlton..
March 24, 2009 at 2:46 pm
Urg yaa I found out by chance; I was helping a developer performace tune an application.
I was like I can execute the query in sub-second speeds. But the Access applicaiton was slow so after monitoring it in Profiler I saw Cursor API calls like there was no yesterday. I didn't research it, he said look around to see if he can change the way access processes the back end call but couldn't find anything.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
May 13, 2009 at 10:47 am
You could look a replicating the data out in transactional one-way (read only for users) subscriptions to a load balanced group of servers. I do this for similar reason with a Big-IP in front of the load-balanced sql subscribers. Or you could do it on the cheap with one active-passive cluster with lots of Ram and have application send requests to it...
HTH,
Chuck Lathrope
May 13, 2009 at 12:50 pm
You can in fact load balance SQL Server by using peer to peer replication; obviously you need to split/balance your queries to the multiple servers. I've evaluated it but not used in a prod env.
Failing that you have federated servers to give you scale out via partitioned views. In fact there's quite a lot you can do depending upon the size of your database - is it under 512GB ?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 13, 2009 at 1:34 pm
I think that Access is your bigger hit. If you can restrict how this is done through a predefined set of stored procedures you may be Ok.
If you need to give your users free will access to reporting through access queries you maybe better off creating a replica for reporting needs as stated above by GrumpyOldDBA 😉
* Noel
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply