BlackBerry Forums Support Community
              

Closed Thread
 
Thread Tools
Old 04-21-2008, 09:16 PM   #1
greg2step
Knows Where the Search Button Is
 
Join Date: Feb 2007
Location: Maryland
Model: 8330
Carrier: VZW
Posts: 46
Default 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  
Old 04-21-2008, 09:36 PM   #2
hdawg
BlackBerry Genius
 
hdawg's Avatar
 
Join Date: Aug 2006
Model: hdawg
PIN: port3101.org
Carrier: hdawg
Posts: 6,632
Default

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  
Old 04-21-2008, 10:57 PM   #3
greg2step
Knows Where the Search Button Is
 
Join Date: Feb 2007
Location: Maryland
Model: 8330
Carrier: VZW
Posts: 46
Default

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  
Old 04-24-2008, 04:39 PM   #4
greg2step
Knows Where the Search Button Is
 
Join Date: Feb 2007
Location: Maryland
Model: 8330
Carrier: VZW
Posts: 46
Default

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  
Old 04-24-2008, 06:08 PM   #5
SmoothRunnings
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
Default

Quote:
Originally Posted by greg2step View Post
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  
Old 04-24-2008, 06:20 PM   #6
hdawg
BlackBerry Genius
 
hdawg's Avatar
 
Join Date: Aug 2006
Model: hdawg
PIN: port3101.org
Carrier: hdawg
Posts: 6,632
Default

Quote:
Originally Posted by greg2step View Post
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  
Old 04-25-2008, 10:28 AM   #7
sferical
Thumbs Must Hurt
 
Join Date: Feb 2006
Model: 8100
Carrier: Voda
Posts: 187
Default

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  
Old 04-25-2008, 01:37 PM   #8
ikazi
New Member
 
Join Date: Apr 2008
Model: 8100
PIN: N/A
Carrier: att
Posts: 14
Default

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  
Old 04-25-2008, 04:21 PM   #9
hdawg
BlackBerry Genius
 
hdawg's Avatar
 
Join Date: Aug 2006
Model: hdawg
PIN: port3101.org
Carrier: hdawg
Posts: 6,632
Default

Quote:
Originally Posted by sferical View Post
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  
Old 04-27-2008, 07:13 AM   #10
kapasaca
Knows Where the Search Button Is
 
Join Date: Mar 2008
Location: Netherlands
Model: 8900
PIN: N/A
Carrier: vodafone
Posts: 46
Default

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?
__________________
support engineer for Exchange 2000/2003/2007 and BES 4.0/4.1/5.0

http://www.pocketmailsupport.com
Offline  
Old 04-27-2008, 07:51 AM   #11
hdawg
BlackBerry Genius
 
hdawg's Avatar
 
Join Date: Aug 2006
Model: hdawg
PIN: port3101.org
Carrier: hdawg
Posts: 6,632
Default

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  
Old 04-28-2008, 09:08 AM   #12
chrisb009
Knows Where the Search Button Is
 
Join Date: Mar 2008
Model: 8830
PIN: N/A
Carrier: Various-BES Engineer
Posts: 16
Default 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  
Old 05-01-2008, 10:00 AM   #13
greg2step
Knows Where the Search Button Is
 
Join Date: Feb 2007
Location: Maryland
Model: 8330
Carrier: VZW
Posts: 46
Default

Quote:
Originally Posted by kapasaca View Post
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  
Old 05-01-2008, 10:04 AM   #14
greg2step
Knows Where the Search Button Is
 
Join Date: Feb 2007
Location: Maryland
Model: 8330
Carrier: VZW
Posts: 46
Default

Quote:
Originally Posted by chrisb009 View Post
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  
Old 05-08-2008, 05:36 AM   #15
kapasaca
Knows Where the Search Button Is
 
Join Date: Mar 2008
Location: Netherlands
Model: 8900
PIN: N/A
Carrier: vodafone
Posts: 46
Default

Quote:
Originally Posted by hdawg View Post
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.
Attached Images
File Type: jpg compare.JPG (61.0 KB, 14 views)
__________________
support engineer for Exchange 2000/2003/2007 and BES 4.0/4.1/5.0

http://www.pocketmailsupport.com
Offline  
Old 05-08-2008, 05:46 AM   #16
hdawg
BlackBerry Genius
 
hdawg's Avatar
 
Join Date: Aug 2006
Model: hdawg
PIN: port3101.org
Carrier: hdawg
Posts: 6,632
Default

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  
Old 05-08-2008, 08:52 AM   #17
greg2step
Knows Where the Search Button Is
 
Join Date: Feb 2007
Location: Maryland
Model: 8330
Carrier: VZW
Posts: 46
Default

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  
Old 05-08-2008, 10:28 AM   #18
hdawg
BlackBerry Genius
 
hdawg's Avatar
 
Join Date: Aug 2006
Model: hdawg
PIN: port3101.org
Carrier: hdawg
Posts: 6,632
Default

Quote:
Originally Posted by greg2step View Post
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  
Old 05-12-2008, 04:22 PM   #19
chriswight
Knows Where the Search Button Is
 
chriswight's Avatar
 
Join Date: Jun 2007
Location: Denver
Model: N/A
PIN: N/A
Carrier: N/A
Posts: 22
Default

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  
Old 05-14-2008, 01:22 PM   #20
greg2step
Knows Where the Search Button Is
 
Join Date: Feb 2007
Location: Maryland
Model: 8330
Carrier: VZW
Posts: 46
Default

Quote:
Originally Posted by hdawg View Post
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  
Closed Thread


Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


Schneider Electric Energy Server EBX510 Server For Energy Management- picture

Schneider Electric Energy Server EBX510 Server For Energy Management-

$4350.00



Wittmann Material Conveyance System Line Server picture

Wittmann Material Conveyance System Line Server

$550.00



MOXA NPort 5410 4 Port Serial Device Server picture

MOXA NPort 5410 4 Port Serial Device Server

$65.00



Server Books for Waitress PU Leather Waitress Book with Money Pocket Black Serve picture

Server Books for Waitress PU Leather Waitress Book with Money Pocket Black Serve

$13.14



MEDIA SERVER DOLBY IMS2000 MSIP-REM-DIB-IMS2000 picture

MEDIA SERVER DOLBY IMS2000 MSIP-REM-DIB-IMS2000

$799.99



Weidmüller WI-MOD-945-E (6720005015) Wireless Ethernet & Serial Device Server picture

Weidmüller WI-MOD-945-E (6720005015) Wireless Ethernet & Serial Device Server

$129.99







Copyright © 2004-2016 BlackBerryForums.com.
The names RIM © and BlackBerry © are registered Trademarks of BlackBerry Inc.