Database Sizing Guidance for XenApp and XenDesktop Versions 7.6 Through Current Release

Disclaimer

This document contains links to Web sites controlled by parties other than Citrix. Citrix is not responsible for and does not endorse or accept any responsibility for the contents or use of these third-party websites. Citrix is providing these links to you only as a convenience, and the inclusion of any link does not imply endorsement by Citrix of the linked website. It is your responsibility to take precautions to ensure that whatever you select for your use is free of viruses or other items of a destructive nature.

Overview

A typical XenDesktop 7 deployment consists of three databases, as follows:

• Site Configuration Database
  Stores the current configuration and state of the XenDesktop deployment
• Monitoring Database
  Stores historical data for display within Director
• Configuration Logging Database
  Tracks configuration changes made to the XenDesktop deployment

By default, the Configuration Logging and Monitoring databases (the secondary databases) are located on the same server as the Site Configuration Database. Initially, all three databases have the same name. Citrix recommends that you change the location of the secondary databases after you create a Site.

A typical deployment also makes use of the Temporary Database, TempDB, provided by SQL Server.

Each database serves a different purpose and grows at a different rate.

This document provides information about each database, and highlights the major considerations to take into account when sizing databases to support XenDesktop 7.

Note: All numbers provided are estimates. Variations between deployments are to be expected.

Differences in sizing between Hosted Shared Desktops (HSD) and Virtual Desktop Infrastructure (VDI) are also noted in this document. Mixed environments will need to combine the estimates from the two desktop types to generate an estimate of the overall database size.

Document changes for XenDesktop 7.6

This document has been extended to cover 7.6 XenDesktop. This was to allow for updates on the sizing changes for features added in 7.6. The three new features that impact database sizing are:

• Connection Leasing – the compressed lease files are stored in the Site database
• App usage monitoring – details of all apps used within the environment are stored within the monitor database
• Hotfix Inventory monitoring – details of Citrix hotfixes applied to the Controllers, VDAs, and VDA images in the environment

Information on the table sizing has been updated below. Transactions per second and transaction log growth was seen to be similar in 7.6 to 7.5, so no updates were made to those sections.

High Level Considerations

Site Database

The Site Database contains configuration information for the running of the system.

Its usage is characterized by:

• Maximum size is reached during peak hours as user logons generate session and connection information to be tracked.
• Minimum size is reached when there are no active sessions and VDAs are all shut down and unregistered.
• Peak size is reached after 48 hours, as the database stores very little persistent information. This is due to a small log of connections being maintained within the Site database for 48 hours.
• The baseline size of the database grows as the configuration information for a Site grows. That is, more workers and users consume more database space.
• High levels of transactions per second occur during logon, as each user logon requires multiple individual transactions to be carried out, and scale based on the concurrent launch rate.
• Low level background noise of VDA heartbeat transactions. Each VDA provides a heartbeat once every 5 minutes and this update triggers a transaction on the database.

Impact of failure

An outage of the Site Database renders the system unable to be managed and monitored. Existing connections are maintained. In XenDesktop 7.6 Connection Leasing allows new connections and reconnections to be made. In prior versions new connections and reconnections are not possible.

Monitoring Database

The Monitoring Database contains historical information about the site. This information is used by Director to display historical information.

Its usage is characterized by:

• Maximum size is controlled by the configured retention period, as follows:
  • For non-Platinum customers the default is 7 days, with a maximum period of 7 days.
  • For Platinum customers the default is 90 days, with no maximum period.
• Peak size may take some time to reach, as the system has to reach the configured retention period.
• Low levels of transactions per second occur due to the batched nature of updates by the Monitoring Service. It is rare to see transactions per second pass the 20 transactions per second mark.
• Some background transactions caused by regular consolidation calls from the Monitoring Service.
• Overnight processing is carried out to remove data outside the configured retention period.

Impact of failure

An outage of the Monitoring Database prevents data being collected for the Site, meaning that data is not visible within Director.

Configuration Logging Database

The Configuration Logging Database contains a historical log of all configuration changes to the Site. This information is used to generate reports or to be displayed in Studio.

Its usage is characterized by:

• Maximum size is hard to predict as it depends on how much configuration activity there is.
• Any actions, for example, session reset, from Director are logged to this database so there may be some slow growth as administrators use Director.
• Minimal transactions occurring on the database when no configuration changes are being made.
• A low transaction rate during updates, as updates are batched where possible.
• The manual removal of data. Data within the Configuration Logging Database is not subject to any retention policy, and is not removed unless done so manually by an administrator.

Impact of failure

The impact of an outage of the Configuration Logging database depends on the Site configuration, as follows:

• If the Site does not allow changes when the Configuration Logging Database is unavailable, it is not possible to reconfigure the XenDesktop deployment.
• If the Site does allow changes when the Configuration Logging Database is unavailable, untracked configuration changes may be made to the XenDesktop deployment.

Temporary Database

The Temporary Database is a system-wide database provided by SQL Server. It is used as a version store for Read-Committed Snapshot Isolation. XenDesktop 7 uses this SQL Server feature to reduce lock contention in the XenDesktop databases.

The size of the version store depends on the number of active transactions. In general, however, it is no more than a few MBs.

The performance of TempDB does impact the performance of XenDesktop brokering, as any transactions that generate new data require TempDB space. XenDesktop, however, tends to have short-lived transactions, which helps keep the version store size small.

The Temporary Database is also used when queries generate large intermediate result sets.

Guidance on sizing and configuring the TempDB can be found in Microsoft’s product documentation:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver15

The main area of contention centers on the number of files to use. Older versions of SQL Server, such as SQL Server 2000, require more files than newer versions. For more information on the number of files to use, see:

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-alwayshave-one-data-file-per-processor-core/

Read-Committed Snapshot Isolation

Citrix recommends that all XenDesktop 7 databases use Read-Committed Snapshot Isolation. For more information, see How to Enable Read-Committed Snapshot in XenDesktop.

Sizing the Databases

Database sizes depend on a number of key factors, including the number of sessions and connections created during a working day.

A session is any desktop or application running for a period of time that may be disconnected and reconnected to.

A connection is any time that a user connects to a session. Disconnecting closes the connection, but not the session. When a user reconnects, this creates a new connection to an existing session.

Site Database

The maximum size of the Site Database is based on the number of VDAs and active sessions, as follows:

Each published application adds 110 KB to the database to store each unique icon.

Note:

The increased size for 7.6 is due to connection leases being stored in the database as part of the replication between Controllers.

Monitoring Database

Of the three databases, the Monitoring Database is expected to grow to the largest over time.

Its size depends on many factors, including the following:

• Number of users
• Number of sessions
• Number of connections
• VDI or HSD workers
• Retention period configured

Below are estimates for the size of the database at a number of data points. This data is an estimate based on data seen when scale testing XenDesktop. The estimates are believed to be realistic.

Customers who maintain their database, however, may find their database is smaller than the estimates.

HSD users are based on 100 users per HSD server.

Maximum retention periods

The maximum amount of data retained is controlled by license, as follows:

• Non-Platinum customers can keep up to 1 week (7 days) of data.
• Platinum customers can keep unlimited data; the default is 3 months (90 days).

Retention periods can be adjusted by using the Set-MonitorConfiguration cmdlet.

After data is older than the configured retention period it is removed from the database.

XenDesktop 7.5 Monitoring Database Sizing

Estimates with 1 connection and 1 session per user with a 5 day working week

Estimates with 2 connections and 1 session per user with a 5 day working week

Note that HSDs generate more data over time due to the logging of load balancing information, but are initially a similar size to VDI desktops.

XenDesktop 7.6 Monitoring Database Sizing

The main changes from 7.5 are:

• Hotfix information
  The data below is based on 3 hotfixes per Worker (VDI or HSD)
• Application usage history
  This is mainly relevant for HSD systems.

Estimates with 1 connection and 1 session per user with a 5 day working week

Estimates with 2 connections and 1 session per user with a 5 day working week

Configuration Logging Database

Providing guidance for sizing the Configuration Logging Database is much harder as it varies dramatically based on daily Director activity and the size of the configured Site.

Activities that have an impact on sessions or users are logged and include, for example, session logoff and reset. Passive activities, such as listing a user’s sessions, are not.

The mechanism used for deploying desktops also impacts the size of the data being logged.

In HSD environments that are not using MCS, database size tends to be between 30 MB and 40 MB.

For MCS environments, database size can easily exceed 200 MB due to the logging of all VM build data.

No significant changes were made for 7.6 to the Configuration Logging Database.

Database Activity during logon of 100k HSD Sessions

During scalability testing, simulating 100k HSD session logons, transaction log growth was measured under two logon rates, as follows:

• 100k users logging in over 1 hour
• 100k users logging in over 2 hours

These rates were chosen to provide example data points.

The environment comprised of:

• 2 Delivery Controllers
• 43 HSD VDA workers
• 3 SQL Servers, configured with the databases, held within one Always On Availability Group

Details for server configurations are provided at the end of this document.

Transaction Log Growth

Transaction log growth for all databases was monitored using the performance monitor counter SqlServer:Databases – Log File(s) Used Size (KB).

Site Database

When the system is idle the transaction log grows by 3.5 MB an hour. This is a combination of VDA and Broker Service heartbeats.

Log growth is linear over the time period being measured. This data suggests that, per user logon, the transaction Log grows by 20 KB. Per user logoff the transaction log grows by 12 KB.

Therefore, growth per day is 32 KB per user logon/logoff cycle.

Monitoring Database

When the system is idle the transaction log grows by 30.5 MB an hour. This is a combination of consolidation stored procedures and HSD VDA load index updates.

The log growth is linear over the time period being measured. This data suggests that per user logon the transaction Log grows by 7 KB. Per user logoff the transaction log grows by 2 KB.

Therefore, growth per day is 9 KB per user logon/logoff cycle.

Transactions per second

The transaction log growth for all the databases was monitored using the following performance monitor counters:

• SqlServer:Databases – Transactions/second
• SqlServer:Databases – Write Transactions/second

Site Database

When the system is idle, there are 5 transactions/second of which 1 Write Transaction/second maintains VDA and Broker heartbeats.

Note: These numbers are estimates taken from the time periods given. Exact load varies depending on the number of concurrent launches per second.

Monitoring Database

When the system is idle, consolidation stored procedures run once a minute, and generate transactions. The level of transactions, however, is small. In general, there are 2–3 transactions and 1 write transaction for each consolidation stored procedure, and 3 consolidation stored procedures are run. During active periods the overhead increases as more work is carried out.

Note: These numbers are estimates taken from the time periods given.

CPU usage

All SQL servers used for this testing were dual hex-core servers with hyper-threading enabled. The exact hardware specifications are provided at the end of this document.

The servers were known to be over-sized for the load being run. This allowed us to identify the limitations and maximums placed on the hardware. It is expected that the SQL CPU load could actually have been handled by an SQL Server with a single quad-core, rather than a dual hex-core system.

During tests the System CPU was monitored using the performance monitor counter Processor – % Processor Time – _Total.

Primary Replica

While idle CPU ran at 0-2% of the available CPU. The consolidation stored procedures caused spikes every minute for ~1s to 8-10% of the system CPU. This is expected to scale based on the amount of data being processed.

During the logon of 100,000 users in 1 hour, CPU jumped to 7% and increased linearly to 11% as more sessions and users were present in the environment. Note that the consolidation stored procedures spikes added 7% to the total CPU, causing the spikes to reach 18% of the CPU.

During logoff CPU ran at 3.5%, with 7% extra CPU for the consolidation stored procedures. Overall this suggests that <20% of a dual hex-core was needed to sustain the logon and logoff rate.

Note: The Windows Server 2012 Scheduler is bias to only use hyper-threads if it needs to, that is, until the system reaches 50% load it runs only one thread per core where possible, so a 20% load on 24 hyper-threads is running on 4.8 cores.

Given the workload, it is believed that this is a heavy stress test, and that a single quad-core SQL server would be adequate for XenDesktop deployments.

Secondary Replicas

Secondary replicas were found to configure 2% CPU during logon and 1.5% during logoff. This is to be expected as, for the most part, replicas are storing data from the primary on their disks, and only the synchronous replica is involved with transactions, as the principal replica does not commit a transaction until the secondary acknowledges it.

Based on recommendations for HA hardware to match the primary replica, this load would be handled very easily by a similarly specified server.

Temporary Database usage

The TempDB is used for many purposes, including version store, space for large query sets, and other temporary table usage.

TempDB Sizing

In this SQL configuration TempDB was configured to have 8 database files, each of a fixed 5 GB in size. This allows for better concurrent use of TempDB but also provides plenty of space and does not trigger any autogrow events. Based on the data captured it was oversized for this deployment. There was, however, plenty of disk space available.

It also keeps within general guidance of the number of TempDB database files being between a quarter and a half of the number of CPUS available, but not exceeding 8 without knowing there is actual contention.

Note that only one TempDB log file is used, as SQL Server does not benefit from multiple log files.

Version Store

TempDB contains a version store for row versions related to the Read Committed Snapshot Isolation used by XenDesktop databases.

Usage can be measured by the following performance counters:

• SQLServer:Transactions – Version Store Size (KB)
• SQLServer:Transactions – Version Cleanup rate (KB/s)
• SQLServer:Transactions – Version Generation rate (KB/s)

During a 100,000 logon over 1 hour, the version store size stayed in the range of 10 MB to 30 MB, with a saw tooth effect as versions were created and then cleaned up. During logoff, the range was 10 MB to 21 MB. When idle, the version store size ranged from 1 MB to 4 MB.

The Version Generation Rate was in the 250–500 KB range during logon; 150–400 KB/s during logoff, and 0–250 KB/s when idle.

The Version Cleanup runs once a minute and reached 2,500 KB/s during logon, 1,750 KB/s during logoff, and 400KB/s during idle periods.

Disk I/O

During the logon tests the disk I/O was measured with the following performance counters:

• PhysicalDisk – Disk Read Bytes/second
• PhysicalDisk – Disk Write Bytes/second
• PhysicalDisk – Disk Reads/second
• PhysicalDisk – Disk Writes/second

Read I/O was found to be minimal, as the SQL server was able to hold all data in memory, causing very little read activity on the system.

Due to the layout of the databases and the storage system the volumes were split, with one volume holding all the data files, and a second volume holding all the transaction log files.

The data shows a pattern that is hard to place into a table. In general the transaction log had a write bytes/second of 800 KB/s for the 1 hour test, and 400 KB/s for the 2 hour test. Once a minute, when the consolidation stored procedures run, the transaction log showed spikes to 30 MB/s.

Analysis of the consolidation stored procedures shows that sometimes the statistics make the query plan suboptimal, and a temporary table spills into TempDB. This triggers writes to the transaction log for TempDB.

This data transfer translates to a steady state of 300 write Input/Output Operations Per Second (IOPS) for the 1 hour test, and 200 write IOPS for the 2 hour test. The spikes for the consolidation stored procedures add another 2–300 Write IOPS while running. Note that in a large environment the consolidation stored procedures run for less than a second.

When each database is checkpointed, data is synced from the in-memory tables to data files on the data volume.

For more information on SQL checkpointing, see http://technet.microsoft.com/enus/.

These checkpoints are very short periods of activity, generally less than 1s.

During logon the checkpoints consumed 6–7 MB/s and 500 write IOPS. During logoff the checkpoints consumed 7 MB/s, ranging between 200–700 IOPS. The numbers vary because the Site and Monitoring databases have different amounts of data to checkpoint.

Database Maintenance

Database maintenance in a large deployment is important. If the database is not properly maintained database outages may occur due to lack of database space, for example, if the transaction log is set to autogrow and fills up the disk, or the transaction log is a fixed size and becomes full.

Transaction Log maintenance

When using SQL Server High Availability features, for example, Always On Availability Groups or Database Mirroring, the XenDesktop databases run in Full transaction logging mode.

By running in Full transaction logging mode the transaction log continues to grow until a database or transaction log backup is taken.

This can cause issues if the transaction log files are not monitored as, by default, SQL Server configures the log files to autogrow. This causes 2 issues:

1. The transaction log files can consume a lot of disk space.
2. Every time the transaction log grows it stalls all transactions until the log space has been zeroed.

Citrix recommends that the log files are backed up regularly. This can be done with scheduled jobs or maintenance plans.

Alternatively, use the SQL Server Agent to monitor when the log used size passes a threshold and run a backup job.

In scale testing a fixed size log of 4 GB was used, and an alert was set to back up the log to another file when the log file reached 80% full. This stopped the log growing and consuming all the disk space, and also stopped it zeroing the disk space and stalling the database.

An example job would run a script such as:

BACKUP LOG [CitrixXenDesktop-SiteDB] TO DISK = N'D:\LogBackup\CitrixXenDesktopSiteDB.bak' WITH NOFORMAT, NOINIT, COMPRESSION, NAME = N'Site-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD

The SQL performance counter to use for the alert is:

SQLServer:Databases - Percent Log Used - CitrixXenDesktopSiteDB

Repeat this for each of the 3 databases.

The backup of the log file was found to have minimal impact on a running XenDesktop environment, there’s a marginal increase in the brokering times, but not something we believe to be significant.

For more details on configuring jobs, see: https://docs.microsoft.com/en-us/sql/ssms/agent/create-a-job?view=sql-server-ver15

For more details on configuring alerts, see: https://docs.microsoft.com/en-us/sql/ssms/agent/alerts?view=sql-server-ver15

Index maintenance

As more data is entered into the database some of the indexes start to become less full, that is fewer records are stored in each SQL page. A SQL Page is 8 KB. This causes the database to increase its storage needs, both in memory and on disk. By maintaining the indexes the page fullness can be increased, which reduces the memory requirements of the database.

Citrix recommends that customers’ setup maintenance plans to run nightly and weekly to maintain the indexes. The maintenance plans may simply be to reorganize the indexes over night during the week, and to rebuild the indexes at weekends.

This recommendation avoids any performance impact of rebuilding any large indexes during day-today operations, especially for a large Monitoring Database.

Microsoft recommends that indexes are rebuilt if they are greater than 30% fragmented, and reorganized if less than 30%. For more information, see the Microsoft documentation.

After reorganizing indexes, statistics should also be updated. This is particularly important as the database grows; otherwise some statistics may be poor and SQL may generate suboptimal SQL query plans.

In terms of space saved, the Microsoft script below was run against a 1.2GB Monitoring Database. It improved the page filling and freed up 300 MB of space.

Third Party Scripts

Microsoft

Microsoft recommends updating the indexes for their WSUS SQL databases using the script available from:

https://learn.microsoft.com/en-us/troubleshoot/mem/configmgr/update-management/reindex-the-wsus-database

By changing the “USE SUSDB” this script can also be run against XenDesktop databases. This script follows Microsoft best practice of rebuilding indexes over 30% fragmented, and reorganizing those under 30%. It then updates statistics for the database.

Ola Hallengren

More advanced scripts are also available from:

http://ola.hallengren.com/

These scripts are well regarded in the SQL Server community. Specifically, the Index scripts available from:

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

These scripts can be used for finer control over the levels to reorganize or rebuild indexes.

Test Server Configuration

SQL Server Configuration

The SQL Availability group comprised of 3 identically specified Dell R720XD servers.

System Specification:

• 2 hex-core Intel Xeon CPU E5-2630 running at 2.30 GHz with hyper-threading enabled
• 64 GB ECC RAM
• PERC H710P Mini with 1 GB battery backed cache
• 26 300 GB 10k RPM SAS drives

The disks were split into the following volumes:

• System Volume
  • Containing the OS and page file
  • 2 disks as a RAID 1 mirror
  • Total capacity 278 GB
• Database Volume
  • Containing the SQL Server Instance and database data files
  • 16 disks as a RAID 10 mirrored stripe
  • Total capacity 2,231 GB
• Log volume
  • Containing the database log files
  • 8 disks as a RAID 10 mirrored stripe
  • Total capacity 1,115 GB
• Software:
  • Windows Server 2012 R2 standard edition, with current windows updates at time of testing (August 2014)
  • SQL Server Enterprise 2012 SP2 with Cumulative Update 1
• Configuration changes
  • SQL Server was configured to use a maximum of 61,440 MB
  • Database containment was enabled on all SQL Instances
  • SQL Server Agent service was configured to automatically start
• Availability Group setup:
  • All servers were placed within a Windows Failover Cluster
  • An Always On Availability group was configured within the cluster
  • The Secondary Replicas were configured to be Synchronous commit, requiring that the transactions commit on both replicas before the transaction completes
  • Read-only replica routing was configured and enabled for the availability group

Delivery Controller and HSD Test Servers

The Delivery Controller and HSD test servers were running on the same configuration of hardware, using HP BL460c G1 blades. 2 servers were used for the Delivery Controllers, and 43 servers provided the simulated HSD workload.

Note: While these servers are relatively old, the workload on the HSD servers is low, as the Session Simulation is mainly focused on placing load on the Delivery Controllers, rather than the HSD servers.

System Specification:

• 2 quad-core Intel Xeon L5320 running at 1.86 GHz, not hyper-thread capable
• 16 GB ECC RAM
• HP Smart Array E200I Raid card (no battery backed cache)
• A 36 GB or 72 GB SAS hard disk

Software:

• Windows Server 2012 R2 Standard edition, with current Windows updates at time of testing (August 2014)
• Citrix XenDesktop 7.6