(imported topic written by BenKus)
As discussed in other threads, SQL Server database locks will cause severe BES Console performance issues. Whenever there is a write to the database or a query for information to the database, SQL Server determines the appropriate locking behavior based on the query. By using various methods including various indexes, most of the queries in BES are optimized to go very quickly and have minimal database locking necessary. In most cases, dozens (or in some cases hundreds) of BES Consoles can use the system concurrently without and signficant performance degradation.
However, certain conditions will cause locks to be held for a very long time, which is when you start to see drastic performance issues in the BES Consoles. These conditions (as mentioned in other threads) are:
- Slow connections to the BES database – See http://support.bigfix.com/cgi-bin/kbdirect.pl?id=156
- BES Consoles without enough memory – Get more RAM.
- Outside systems querying the BES database with inefficient long running queries (this is more rare).
If your BES Consoles are performing poorly and you suspect database locking, you can check the SQL Server Enterprise Manager, which comes with SQL Server 2000 (there is a different but similar name for the SQL Server 2005 version):
- Look under the database server " > Management > Current Activity > Locks / ProcessID"
- There will be 2 process ids for each console user and other for any other open connection to the database.
- If any of the spids shows “(Blocking)”, then it means that user is holding a lock on the database.
- To see which user, look at “Process Info” information. Look up the userid and then find the “Wait Type” column. If you see “NETWORKIO”, it very likely means that the database lock is being held due to a slow connection or a console computer without enough memory (which causes the computer to pause and swap memory to disk). If this user exits the console, likely all the other consoles will speed up quickly.
- Note that this dialog doesn’t refresh automatically and you need to right click on “Current Activity” to refresh.
- Occasional short-lived locks are OK and nothing to worry about. However locks held for minutes will cause major performance problems.
Ben