|
|
04-21-2008, 09:16 PM
|
#1
|
Knows Where the Search Button Is
Join Date: Feb 2007
Location: Maryland
Model: 8330
Carrier: VZW
Posts: 46
|
SQL Performance Issues
Please Login to Remove!
Does anyone with experience with a large BES environment (approaching 4000 users, 6 BES, 1 database on a dedicated SQL server.
BES console performance has become unbearably slow - user lookups, add',s removes, set EA passwords, its all too slow and sometimes times out w/ no results.
our SQL DBA is seeing some blocking on transactions, and also noticed there are no indexes on the tables in the DB...
Does anyone have any experience with this type of issue, know the implications of indexing, and have any best practice recommendations?
Also, is SQL 2005 64bit supported in 4.14 or 4.15? We are running 4.14mr5 for Exchange.
Last, I would love a good explanation of what RIMESP.DLL does.
Thanks
Greg
|
Offline
|
|
04-21-2008, 09:36 PM
|
#2
|
BlackBerry Genius
Join Date: Aug 2006
Model: hdawg
PIN: port3101.org
Carrier: hdawg
Posts: 6,632
|
Quote:
Does anyone with experience with a large BES environment (approaching 4000 users, 6 BES, 1 database on a dedicated SQL server.
BES console performance has become unbearably slow - user lookups, add',s removes, set EA passwords, its all too slow and sometimes times out w/ no results.
our SQL DBA is seeing some blocking on transactions, and also noticed there are no indexes on the tables in the DB...
|
You might want to check the indexes and reindex them.
Quote:
Also, is SQL 2005 64bit supported in 4.14 or 4.15? We are running 4.14mr5 for Exchange.
|
Maybe SP5 .... haven't seen anything official but as of last month it still isn't supported.
Quote:
Last, I would love a good explanation of what RIMESP.DLL does.
|
I don't know if I can offer a good explanation of what it does ... but it is a trigger mechanism of sorts. I don't see why this all couldn't be done within the DB / stored procedures ... if you gander through NotifyInstall.sql and can parse what it does that might explain it a bit more.
|
Offline
|
|
04-21-2008, 10:57 PM
|
#3
|
Knows Where the Search Button Is
Join Date: Feb 2007
Location: Maryland
Model: 8330
Carrier: VZW
Posts: 46
|
64bit sql 2005 is in 4.15 - reading the release notes now
thanks for the link on the article for re-indexing, I will run that by our SQL DBA in the morning.
|
Offline
|
|
04-24-2008, 04:39 PM
|
#4
|
Knows Where the Search Button Is
Join Date: Feb 2007
Location: Maryland
Model: 8330
Carrier: VZW
Posts: 46
|
We cleaned up some indexes, added RAM and CPU to our SQL server, and see some improvement but not enough.
We are still seeing timeouts on some queries in Blackberry Manager (such as user lookups) Also seeing some Blocking going on where 2 or more queries are blocking each other.
Any idea how to troubleshoot and fix this?
|
Offline
|
|
04-24-2008, 06:08 PM
|
#5
|
Talking BlackBerry Encyclopedia
Join Date: May 2007
Location: Toronto, Canada
Model: 9700
OS: 5.0.0.423
PIN: 21B694E3
Carrier: Virgin Mobile Canada
Posts: 396
|
Quote:
Originally Posted by greg2step
We cleaned up some indexes, added RAM and CPU to our SQL server, and see some improvement but not enough.
We are still seeing timeouts on some queries in Blackberry Manager (such as user lookups) Also seeing some Blocking going on where 2 or more queries are blocking each other.
Any idea how to troubleshoot and fix this?
|
4k users on a single SQL server is a lot for SQL that I would start thinking about clustering the SQL server with LoadBalancing to boost performance.
You could ask a good DBA guy if there are any ways you could tweat the SQL server itself such as throw more RAM into it or improve the disk IO speed with something like SAS or iSCSI.
A.
|
Offline
|
|
04-24-2008, 06:20 PM
|
#6
|
BlackBerry Genius
Join Date: Aug 2006
Model: hdawg
PIN: port3101.org
Carrier: hdawg
Posts: 6,632
|
Quote:
Originally Posted by greg2step
We cleaned up some indexes, added RAM and CPU to our SQL server, and see some improvement but not enough.
We are still seeing timeouts on some queries in Blackberry Manager (such as user lookups) Also seeing some Blocking going on where 2 or more queries are blocking each other.
Any idea how to troubleshoot and fix this?
|
Was this database ever on MSDE?
|
Offline
|
|
04-25-2008, 10:28 AM
|
#7
|
Thumbs Must Hurt
Join Date: Feb 2006
Model: 8100
Carrier: Voda
Posts: 187
|
RIM has a customer with 25,000 users on 1 SQL database who regularly see problems with SQL because of indexing on the tables and queries locking the database. The RIM code has many flaws in large environments..........
|
Offline
|
|
04-25-2008, 01:37 PM
|
#8
|
New Member
Join Date: Apr 2008
Model: 8100
PIN: N/A
Carrier: att
Posts: 14
|
So far the replies looks great and more tilted towards the SQL stuff. I would also look at my SQL Server hardware and monitor the following main components to see if one of those are the bottlenecks.
CPU
Memory (if more than 2GB ram you may need .3GB switch if I remember)
Disk
Network.
Hope this helps.
ikazi
|
Offline
|
|
04-25-2008, 04:21 PM
|
#9
|
BlackBerry Genius
Join Date: Aug 2006
Model: hdawg
PIN: port3101.org
Carrier: hdawg
Posts: 6,632
|
Quote:
Originally Posted by sferical
RIM has a customer with 25,000 users on 1 SQL database who regularly see problems with SQL because of indexing on the tables and queries locking the database. The RIM code has many flaws in large environments..........
|
I can honestly say I'd never recommend any BlackBerry domain ever exceed 10,000 users. Too much of a potential single point of failure for such a "black box" function.
|
Offline
|
|
04-27-2008, 07:13 AM
|
#10
|
Knows Where the Search Button Is
Join Date: Mar 2008
Location: Netherlands
Model: 8900
PIN: N/A
Carrier: vodafone
Posts: 46
|
a BES can hold upto 2000 users by license.
The theoretical max is a 1000 users for MSDE but MSDE performance needs to be monitored when there are a couple of hundred users or more than 2 Exchange servers because there is a work load governor for MSDE. It allows only 8 concurrent queries at the same time.
There is no workload governor for full SQL Server and there can be up to 5 message agents spawned and more than 8 concurrent queries.
5 message agents times 500 is 2500 users MAX.
If you have 6 BES using one database, they share the CAL's which can not exceed 2000, so how do you do that?
|
Offline
|
|
04-27-2008, 07:51 AM
|
#11
|
BlackBerry Genius
Join Date: Aug 2006
Model: hdawg
PIN: port3101.org
Carrier: hdawg
Posts: 6,632
|
Quote:
a BES can hold upto 2000 users by license.
|
Then how did I have a BES with 4000 users on it? Your information is incorrect.
Quote:
The theoretical max is a 1000 users for MSDE but MSDE performance needs to be monitored when there are a couple of hundred users or more than 2 Exchange servers because there is a work load governor for MSDE. It allows only 8 concurrent queries at the same time.
|
You're kinda right here. The recommended max is ~500.
Quote:
There is no workload governor for full SQL Server and there can be up to 5 message agents spawned and more than 8 concurrent queries.
5 message agents times 500 is 2500 users MAX.
|
You can create additional agents too ...
Quote:
If you have 6 BES using one database, they share the CAL's which can not exceed 2000, so how do you do that?
|
Again, your information is wrong here.
|
Offline
|
|
04-28-2008, 09:08 AM
|
#12
|
Knows Where the Search Button Is
Join Date: Mar 2008
Model: 8830
PIN: N/A
Carrier: Various-BES Engineer
Posts: 16
|
SQL Performance
On idea is to make sure the SQL box is not serving out any other DB's. If it is....try dedicating the cache within SQL for the BES db. Connectivity is another area of concern. Do you have a fiber back end or 10/100 switches between the boxes? Another area would be hardware....however this is usually the last resort for me.
Thanks,
Chris
|
Offline
|
|
05-01-2008, 10:00 AM
|
#13
|
Knows Where the Search Button Is
Join Date: Feb 2007
Location: Maryland
Model: 8330
Carrier: VZW
Posts: 46
|
Quote:
Originally Posted by kapasaca
a BES can hold upto 2000 users by license.
The theoretical max is a 1000 users for MSDE but MSDE performance needs to be monitored when there are a couple of hundred users or more than 2 Exchange servers because there is a work load governor for MSDE. It allows only 8 concurrent queries at the same time.
There is no workload governor for full SQL Server and there can be up to 5 message agents spawned and more than 8 concurrent queries.
5 message agents times 500 is 2500 users MAX.
If you have 6 BES using one database, they share the CAL's which can not exceed 2000, so how do you do that?
|
You can definately load more than 2000 CAL's in a database. We have the enterprise pack that comes with a CAL license code for 10000 CAL's, and we go thru a true up process every year to buy what we have expanded by. In addition we have license codes for all prior CAL's (bought before the enterprise pack)
This database was never on MSDE - we did a large user migration from a bunch of individual BES servers on MSDE to a single database when we rebuilt our BES infrastructure to 4.12. We have also nearly doubled our userbase since and added more servers to the database. No single server has over 900 users.
I have heard of companies running 17000 users on 2 database. We are at just shy of 4000 on a single database.
I suspect there are problems in the database design for large deployments, I just didn't think we would see them at 3950.....
|
Offline
|
|
05-01-2008, 10:04 AM
|
#14
|
Knows Where the Search Button Is
Join Date: Feb 2007
Location: Maryland
Model: 8330
Carrier: VZW
Posts: 46
|
Quote:
Originally Posted by chrisb009
On idea is to make sure the SQL box is not serving out any other DB's. If it is....try dedicating the cache within SQL for the BES db. Connectivity is another area of concern. Do you have a fiber back end or 10/100 switches between the boxes? Another area would be hardware....however this is usually the last resort for me.
Thanks,
Chris
|
Its a dedicated SQL box - our SQL DBA's knew better than to let it share w/ other apps.
Perfmon review has shown no disk or network bottleneck. We are now running 8gb of RAM /3gb and PAE enabled. We also went from 2 CPU's to 4, that helped some. CPU avgs at about 40-45% now during the business day.
(down from 65% or so on the 2gb setup)
The only bottleneck we can easily see is transaction blocking - review of open sessions on the DB shows 10 to 15 blocks during business hours, this is no good for SQL performance.
One option we are considering is after we get up to 4.15 is to move to new SQL servers that are running on a 64bit OS and SQL 2005 - even then thats still just addingh horsepower but not fixing the problem.
Thanks for the input - still looking for more answers, also do have a case open w/ RIM T-Support.
Greg
|
Offline
|
|
05-08-2008, 05:36 AM
|
#15
|
Knows Where the Search Button Is
Join Date: Mar 2008
Location: Netherlands
Model: 8900
PIN: N/A
Carrier: vodafone
Posts: 46
|
Quote:
Originally Posted by hdawg
Then how did I have a BES with 4000 users on it? Your information is incorrect.
You're kinda right here. The recommended max is ~500.
You can create additional agents too ...
Again, your information is wrong here.
|
i have asked RIM about the 2000 user limit for a BES and they say 2000 is the limit on a BES. Their website shows the same.
BlackBerry - BlackBerry Professional Software
BUT, I do believe you when you say you have almost 4000 on one database.
i've seen servers holding more users than licenses..and they could just keep going.
|
Offline
|
|
05-08-2008, 05:46 AM
|
#16
|
BlackBerry Genius
Join Date: Aug 2006
Model: hdawg
PIN: port3101.org
Carrier: hdawg
Posts: 6,632
|
Wirelessly posted (banana)
Let me rephrase. 4000 users, 1 BES. Not 2 not 4, 1 BES, 4000 users. 2000 is RIMs recommended limit based on their seen usage / performance.
I assure you, a BES can hold > 4000 users, depending on how the environment is configured.
|
Offline
|
|
05-08-2008, 08:52 AM
|
#17
|
Knows Where the Search Button Is
Join Date: Feb 2007
Location: Maryland
Model: 8330
Carrier: VZW
Posts: 46
|
Well, I beleieve our SQL issus are fix - we ran notifyuninstall.sql - removed rimesp.dll.
transaction blocking is gone. slow user searches are now very fast. cpu utilization is largely under 10% on the SQL server.
Microsoft PSS worked with us and found rimesp.dll to be the source of the problem. What does that thing do anyway? its bloatware and a waste of resources. The one thread in the forum says you can live w/o it if your BES has high bandwidth connection to your SQL server - which we do.
|
Offline
|
|
05-08-2008, 10:28 AM
|
#18
|
BlackBerry Genius
Join Date: Aug 2006
Model: hdawg
PIN: port3101.org
Carrier: hdawg
Posts: 6,632
|
Quote:
Originally Posted by greg2step
Well, I beleieve our SQL issus are fix - we ran notifyuninstall.sql - removed rimesp.dll.
transaction blocking is gone. slow user searches are now very fast. cpu utilization is largely under 10% on the SQL server.
Microsoft PSS worked with us and found rimesp.dll to be the source of the problem. What does that thing do anyway? its bloatware and a waste of resources. The one thread in the forum says you can live w/o it if your BES has high bandwidth connection to your SQL server - which we do.
|
Do you know which version of the DBNS you had installed?
|
Offline
|
|
05-12-2008, 04:22 PM
|
#19
|
Knows Where the Search Button Is
Join Date: Jun 2007
Location: Denver
Model: N/A
PIN: N/A
Carrier: N/A
Posts: 22
|
Speaking of Database Notification Service (DBNS) could anybody explain how this works, and how you can even tell it's working? From what I understand it's supposed to improve DB performance by reducing the number of db queries (and number of connections) to the db?
And a question to greg2step: did you have the Policy service enabled , and if so was it configured to do anything? Did you have SQL on a separate box? The reason I ask is that we're seeing similar slow response since enabling the policy service. Thanks.
__________________
Chris Wight
BES Admin: 3,300 bb users and counting
|
Offline
|
|
05-14-2008, 01:22 PM
|
#20
|
Knows Where the Search Button Is
Join Date: Feb 2007
Location: Maryland
Model: 8330
Carrier: VZW
Posts: 46
|
Quote:
Originally Posted by hdawg
Do you know which version of the DBNS you had installed?
|
First the version that came w/ 4.12 (i think, my precedessor installed that) When it was upgraded to the version w/ 4.14 the problem got alot worse. We then removed it using notifyuninstall.sql, and our database, BES console searches are both much faster. Also CPU utlilization dropped from approx 45% avg to under 15% avg during peak business hours.
yes our SQL is a separate server - nothing else on that server but SQL 2000 sp4.
|
Offline
|
|
|
|