NOTE: This article has been updated to use Microsoft's recommendation of rebuilding table indexes.
NOTE: Be sure you are using the very latest version of LobbyCentral (8.0.17 or higher) which contains database performance enhancements
If users receive an error message about a SQL Server Timeout or if it appears to be running slowly, you will need to rebuild the databases index. Please follow the steps below:
- Instruct users to log out of LobbyCentral.
- Shut down QMonitor and Kiosk appliances.
- Make a backup of the LobbyCentral database.
- Execute the following SQL:
use lobbycentral GO exec sp_updatestats
Log into LobbyCentral and verify timeout issue has been resolved. Go to Search, Request Search, and set a two-month date range.
In addition to rebuiding the index, verify that you are performing a full backup followed by a transaction log backup. If the database model is set to Simple, you do not need to backup the transaction log.
Database Backup Command
BACKUP DATABASE 'lobbycentral' TO DISK = 'c:\lobbycentral.bak' WITH NOFORMAT, INIT, NAME = 'LobbyCentral backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
Transaction Log Backup Command
BACKUP LOG 'lobbycentral' TO DISK = 'c:\lobbycentral_log.bak' WITH NOFORMAT, INIT, NAME = 'LobbyCentral log backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
You must perform a database backup BEFORE transaction log backup.
Adding Indexes (Only for versions of LobbyCentral below 8.0)
The following indexes were added to LobbyCentral version 8.0 and can be used in lower versions.
Connect to the LobbyCentral database and execute the SQL statements to generate these indexes:
IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_tblRequest_ordinal') BEGIN exec(' CREATE NONCLUSTERED INDEX [IX_tblRequest_ordinal] ON [dbo].[tblRequest_Service] ([ordinal]) INCLUDE ([requestID],[serviceID],[serviceDescOvrd]) ') END IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_tblRequest_requestID_ordinal') BEGIN exec(' CREATE NONCLUSTERED INDEX [IX_tblRequest_requestID_ordinal] ON [dbo].[tblRequest_Service] ([requestID],[ordinal]) INCLUDE ([serviceID],[serviceDescOvrd]) ') END IF NOT EXISTS(SELECT name FROM sysindexes WHERE name = 'IX_tblRequest_customerID') BEGIN exec('CREATE NONCLUSTERED INDEX [IX_tblRequest_customerID] ON [dbo].[tblRequest] ([customerID]) ') END