PI Nexus+ / Reference
SQL Server Rights and Sizing
This reference lists the SQL Server rights each setup step needs, what the preparation script does, and the tempdb figures for the PI Nexus+ instance.
Overview
This reference lists the SQL Server rights each setup step needs, what the preparation script does, and the tempdb figures for the PI Nexus+ instance.
Rights per step
| Step | Run as | SQL Server rights needed |
|---|---|---|
Prepare-Database.sql, once | SQL administrator | Create a database and create logins, for example sysadmin, or dbcreator with securityadmin |
| Database Setup without preparation | Installing administrator | The same as above |
| Database Setup after preparation | Installing administrator | db_owner on the PI Nexus+ database |
| Normal operation, upgrades, Admin > SQL Server > Apply | PI Nexus+ service account | db_owner on the PI Nexus+ database |
| Turning on snapshot isolation | Database Setup, the preparation script, or the web application at startup | ALTER on the database |
| PI Vision inventory | PI Nexus+ service account | db_datareader on each PI Vision database |
| Server-wide memory figures on Admin > Support > Scale (optional) | PI Nexus+ service account | VIEW SERVER PERFORMANCE STATE (SQL Server 2022 and later) |
| Host monitoring SQL Server readings (optional) | Scanner service account | See Required SQL Server rights in Host Monitoring Reference |
| Recover-AdminAccess | Any Windows account | Write access to the PI Nexus+ database, for example db_owner |
Prepare-Database.sql
Set the variables at the top of the script, then run all of it.
| Variable | Meaning | Example |
|---|---|---|
@DatabaseName | Database to create or prepare | PINexus |
@ServiceAccount | Account of the app pool and the scanner service | DOMAIN\svc-pinexus |
@SecondServiceAccount | Split deployment only: the second server's account. Empty for one account | DOMAIN\svc-pinexus-scan |
@SetupAdministrator | Optional account or group that will run Database Setup | DOMAIN\PI Admins |
The script creates the database, turns on read-committed snapshot isolation, creates a Windows login and a database user for each named account, and adds each to db_owner. Each step is skipped when already done, and the script stops at the first error, for example an unknown Windows account.
Snapshot isolation
PI Nexus+ runs its database with read-committed snapshot isolation, so pages read the last committed data instead of waiting for a scan. It is turned on by the first of these to run:
| Turned on by | Statement |
|---|---|
Prepare-Database.sql | ALTER DATABASE [<db>] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE |
| Database Setup | The same |
| The web application at startup, before migrations | The same |
WITH ROLLBACK IMMEDIATE ends open transactions in the PI Nexus+ database. When the statement is refused, setup and startup continue, and readiness reports Read-committed snapshot isolation as a warning with the statement for a SQL administrator to run. Azure SQL Database and Azure SQL Managed Instance already have it on.
tempdb sizing
The figures cover the PI Nexus+ workload only. On a shared instance, add the other applications' needs on top.
| Estate size | Data files | Initial size per file | Total data | Log | Autogrowth |
|---|---|---|---|---|---|
| Up to 100,000 PI Points | 4 | 512 MB | 2 GB | 1 GB | 256 MB, unrestricted |
| 100,000 to 500,000 PI Points | 4 to 8 | 1 GB | 4 GB to 8 GB | 2 GB | 512 MB, unrestricted |
| 500,000 PI Points and above (including 1,000,000 objects) | 8 | 1 GB to 2 GB | 8 GB to 16 GB | 2 GB to 4 GB | 1 GB, unrestricted |
- Use equally sized data files with identical autogrowth, one per logical processor up to eight.
- Put tempdb on the fastest disk, apart from the PI Nexus+ data files where possible.
- Pre-size tempdb and keep autogrowth on as a safety net.
- Check the figures under load with
sys.dm_tran_version_store_space_usageandsys.dm_db_file_space_usagewhile a large scan runs. - For 500,000 PI Points and more, ask support for the SQL Server sizing guidance before rollout.
Row versions are bounded by the longest scan write batch, which times out after 900 seconds, not by the length of a scan. Scan staging tables live in the PI Nexus+ database, not in tempdb.
SQL Server memory
| Situation | Guidance |
|---|---|
| SQL Server shares a machine with PI Data Archive, AF, PI Vision or PI Nexus+ | Set max server memory (MB). For a 16 GB small or lab server, 6 to 8 GB is a starting point; measure and adjust |
| Dedicated SQL Server | A larger share of memory is fine |
| Machine answers ping but SQL Server, remote management and services stop responding during a large scan | Operating-system memory starvation. Restart from the console; add memory or lower the cap |
Don't use DBCC FREEPROCCACHE, DBCC DROPCLEANBUFFERS, shrinking or scheduled restarts to manage memory. PI Nexus+ never changes the instance configuration itself.
