When a Security Center system slows down, the first suspect is usually the Archiver and the second is the network. In my experience the database is the cause more often than either, and it is the one nobody checked, because SQL Server was installed by the Genetec installer five years ago and has not been looked at since.
Security Center is a database-backed application. The Directory role keeps its entire configuration, every event, every alarm, every audit entry, and every user session in SQL Server. Each Archiver keeps a database of its own that indexes every video file it writes. The Access Manager, the LPR Manager, and the Health Monitor each have one too. When those databases are healthy the system feels instant. When they are not, the symptoms show up everywhere except the place they started: Security Desk takes twenty seconds to log in, the timeline crawls, alarms arrive late, and the Directory restarts itself at 03:00 for no reason anyone can find.
This is the database side of the tuning guide, and it is the section of that work that most often turns a slow system into a fast one without buying anything.
Scope. This covers SQL Server as Genetec uses it, on Windows, in the on-premises deployment pattern that still accounts for most enterprise systems. The architecture article covers where each role and its database should sit. This one covers what to do with the databases once they are there.
Express or Standard: the decision most systems never made
The Security Center installer will happily deploy SQL Server Express, and a large share of production systems are still running on it because nobody revisited the choice once the system went live. Express is free and it is fine for small systems. It also has hard limits that do not announce themselves.
| Limit | SQL Server Express | What it means under Security Center |
|---|---|---|
| Database size | 10 GB per database | The Directory database on a busy multi-site system reaches this within a few years of event history. When it does, writes fail and the Directory stops. |
| Memory | Roughly 1.4 GB buffer pool | Frequently accessed data no longer fits in cache. Every timeline query goes to disk. |
| Compute | Lesser of one socket or four cores | Parallel query plans are unavailable. Reports and audit searches serialize. |
| SQL Server Agent | Not included | No built-in scheduler for maintenance. Backups and index work have to be scripted through Task Scheduler. |
The 10 GB limit is the one that causes outages. It is enforced per database, so the Directory database is the one that hits it, and it hits it silently. The first sign is usually an event that fails to write, followed by a Directory role that goes unhealthy, followed by a phone call. If the Directory database is over 6 GB today and growing, plan the move to Standard now rather than during the incident.
My rule: Express is acceptable for a single-site system under roughly 150 cameras with short event retention and a documented plan for what happens at 8 GB. Anything federated, anything with access control at scale, anything with LPR, and anything where the Directory is business-critical belongs on Standard. The licence cost is small against the cost of a Directory outage on a site that depends on it.
Max server memory
SQL Server takes as much memory as the operating system will give it. On a dedicated database server that is the correct behaviour. On a Security Center server where SQL shares the box with the Directory service, the Genetec Server service, and whatever else got installed, it starves everything else and the Directory starts paging. This is the single most common configuration gap I find, and it is a one-line fix.
Set max server memory explicitly. The number depends on total RAM and what else runs on the host. A defensible starting point on a server that runs the Directory role alongside SQL:
Total RAM Reserve for OS + Genetec SQL max server memory 16 GB 8 GB 8192 MB 32 GB 12 GB 20480 MB 64 GB 16 GB 49152 MB
EXEC sys.sp_configure N’show advanced options’, N’1’; RECONFIGURE; EXEC sys.sp_configure N’max server memory (MB)’, N’20480’; RECONFIGURE;
Then watch it. If the buffer cache hit ratio stays high and Page Life Expectancy is stable, the cap is fine. If SQL is consistently at its cap and the Directory is healthy, raise it. If the Directory is paging, lower it. The goal is a cap that keeps the working set in memory without starving the application that owns the server.
Set min server memory as well, to roughly half the max, so that a memory-hungry process cannot squeeze SQL down to nothing during a backup or a video export.
Recovery model and backups
Every Security Center database should be in the Simple recovery model unless you have a specific reason to run Full, and that reason is usually log shipping or point-in-time restore, neither of which most security systems use. Full recovery without regular log backups produces a transaction log that grows until the disk fills, at which point the database stops accepting writes and the Directory stops with it. I have watched a 200 GB transaction log take down a Directory whose actual data was 4 GB.
ALTER DATABASE [Directory] SET RECOVERY SIMPLE;
Check every database, not just the Directory. Archiver databases are created with whatever the model default was at the time, and the model default is not always what you expect.
For backups, use the Server Admin backup schedule that Genetec provides for the Directory and role databases. It handles the Genetec side correctly, it is aware of the roles, and it produces backups that Genetec support will accept. Back up to a different volume than the database files and copy the result off the host. A backup on the same disk as the database it protects is not a backup.
Test the restore. Once a year at least, restore the Directory backup onto a lab server and bring a Directory up on it. A backup that has never been restored is an assumption, and Security Center configuration is exactly the kind of thing that turns out to have been silently incomplete when it is finally needed.
Autogrowth, in megabytes, not percent
SQL Server grows a data file when it fills. The default growth increment on older installs is 1 MB for data and 10 percent for the log. Growing 1 MB at a time on an event-heavy Directory database means thousands of growth operations, each one briefly blocking writes and fragmenting the file on disk. Ten percent growth on a large log produces increasingly enormous growth events, each one taking longer and blocking longer.
Set fixed growth in megabytes on every Security Center database. For the Directory, 256 MB data and 128 MB log is a reasonable start on a system of any size. For Archiver databases, which index video rather than store events, 128 MB is usually enough.
ALTER DATABASE [Directory] MODIFY FILE (NAME = N’Directory’, FILEGROWTH = 256MB); ALTER DATABASE [Directory] MODIFY FILE (NAME = N’Directory_log’, FILEGROWTH = 128MB);
Pre-size the files while you are there. If the Directory database is 5 GB and growing a gigabyte a year, size the data file to 8 GB now and let it grow in 256 MB steps from there. Growth events during operation are the thing you are trying to avoid, and pre-sizing avoids most of them.
Enable instant file initialization by granting the SQL Server service account the Perform Volume Maintenance Tasks right. Data file growth then completes without zeroing the new space first. It does not apply to log files, which is one more reason to size the log sensibly up front.
TempDB
The Directory uses TempDB heavily for report generation, audit searches, and the sort operations behind the timeline. On the default install TempDB is a single file on the system drive, which means it competes with the operating system and the Directory database for the same disk.
Move TempDB to its own volume. Give it one data file per core up to eight, all the same size, and set them to grow in fixed megabyte increments. On a four-core Directory server, four 512 MB files and a 256 MB log is a sensible baseline. This is standard SQL Server practice rather than anything Genetec-specific, and it makes a measurable difference to report and search latency on busy systems.
Index maintenance
Security Center writes events constantly and deletes them on a retention schedule. That pattern fragments indexes faster than most workloads, and a heavily fragmented Directory index turns a timeline query that should take milliseconds into one that takes seconds. The symptom operators report is “the system is slow in the afternoon,” because by afternoon the day’s events have arrived and the indexes are in their worst state.
On SQL Server Standard, schedule a weekly job through SQL Server Agent that reorganizes indexes over 10 percent fragmented and rebuilds those over 30 percent, then updates statistics. On Express there is no Agent, so the same script runs from Windows Task Scheduler through sqlcmd. Either way, run it in the quietest window the site has, which for a security system is usually not overnight, when the Archiver is at full load, but mid-morning on a weekday when recording is steady and nobody is running reports.
sqlcmd -S .\SQLEXPRESS -E -Q “EXEC sp_MSforeachdb ‘USE [?]; IF DB_ID() > 4 BEGIN EXEC sp_updatestats; END‘“
That is the minimum. A proper maintenance script that inspects fragmentation per index and acts accordingly is better, and the Ola Hallengren maintenance solution is the one I deploy on Standard installs because it does exactly that and it is free.
Event retention is a database setting wearing a Genetec costume
The Directory keeps every event, every alarm, and every audit trail entry until the retention period tells it to stop. The defaults are generous and on a busy access control site the event tables become the largest thing in the database within months. This is the growth that takes Express installs to their 10 GB limit and Standard installs to the point where index maintenance stops finishing in its window.
Set retention deliberately, per event type, in Server Admin. Decide how long you actually need access events, alarm history, and audit trails, and set those numbers. Ninety days of access events with a year of audit trail is a defensible baseline for a corporate site; a regulated facility will have a policy that sets the number for you. What is not defensible is leaving the default in place because nobody made the decision, and then discovering the decision was made for you by the disk.
The Archiver’s database is a different case. It indexes video files rather than storing them, so its size tracks the number of files rather than their duration. A system with short recording segments and many cameras produces a large Archiver database. That is normal, but it should be on the same maintenance schedule as the Directory, and it should never be on SQL Express on a large Archiver.
The failures, by symptom
| What operators report | What is usually wrong | Where to look first |
|---|---|---|
| Security Desk takes 15 to 30 seconds to log in | Directory index fragmentation, or SQL memory starved | Fragmentation on the Directory’s user and entity tables; max server memory |
| Timeline is slow to populate in the afternoon | Index fragmentation from the day’s event writes | Weekly maintenance job, and whether it is actually completing |
| Directory restarts overnight | Transaction log or data file filled the disk | Recovery model; free space on the SQL volume |
| Events stop appearing, Directory role unhealthy | Express 10 GB limit reached | Directory database size; event retention |
| Everything is slow after a reboot for an hour | Cold buffer cache on an undersized memory cap | Max and min server memory; total RAM |
| Reports time out | TempDB on the system drive, or Express core limit | TempDB placement; edition |
The checklist
This is what I check on every Security Center database during a health check, in the order that finds the worst problems fastest.
- Edition, and the size of every database against the Express limit if it applies.
- Max and min server memory set explicitly, and appropriate for what else runs on the host.
- Recovery model on every database, and the size of every transaction log.
- Autogrowth in fixed megabytes on every file. Pre-sized data files. Instant file initialization enabled.
- TempDB on its own volume with multiple equal files.
- A maintenance job that exists, runs, and finishes. The job log, not the schedule, is the evidence.
- Backups on a different volume, copied off the host, with a restore that has been tested.
- Event retention set deliberately and documented, per event type.
- Antivirus exclusions on the SQL data, log, and TempDB paths. The tuning guide has the list.
None of this needs new hardware. Most of it is an afternoon. On the systems where I have done it the difference in login time and timeline responsiveness is the kind of thing operators notice and mention, which for infrastructure work is the highest compliment available.