Product Documentation

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

Aug 09, 2016

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 MSDN:

http://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx

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:

Users Applications Type Expected Peak Size 7.5 (MB) Expected Peak Size 7.6 (MB)

1,000 

50

HSD

30

31

10,000

100

HSD

60

198

100,000

200

HSD

330

752

1,000

N/A

VDI

30

30

10,000

N/A

VDI

115

121

40,000

N/A

VDI

390

426

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

Note that 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 is dependent 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

Users Type 1 week (MB) 1 month (MB) 3 months (MB) 1 year (MB)

1,000

HSD

151

70

230

900

10,000

HSD

2,830

600

1,950

7,700

100,000

HSD

1,500

5,900

19,000

76,000

1,000

VDI

15

55

170

670

10,000

VDI

120

440

1,400

5,500

40,000

VDI

464

1,700

5,400

21,500

 

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

Users Type 1 week (MB) 1 month (MB) 3 months (MB) 1 year (MB)

1,000

HSD

30

100

330

1,300

10,000

HSD

240

925

3,000

12,000

100,000

HSD

2,400

9,200

30,000

119,000

1,000

VDI

25

85

280

1,100

10,000

VDI

200

750

2,500

9,800

40,000

VDI

800

3,000

9,700

38,600

 

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

Users Type 1 week (MB) 1 month (MB) 3 months (MB) 1 year (MB)

1,000

HSD

151

605

1,966

7,865

10,000

HSD

2,830

11,301

36,712

146,834

100,000

HSD

7,194

28,585

92,758

370,841

1,000

VDI

13

49

157

622

10,000

VDI

117

409

1,287

5,090

40,000

VDI

460

1,610

5,058

19,999

 

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

Users Type 1 week (MB) 1 month (MB) 3 months (MB) 1 year (MB)

1,000

HSD

159

635

2,063

8,251

10,000

HSD

2,904

11,599

37,684

150,718

100,000

HSD

7,940

31,572

102,465

409,672

1,000

VDI

21

79

253

1,008

10,000

VDI

191

708

2,258

8,974

40,000

VDI

759

2,805

8,941

35,532

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 and 40MB.

For MCS environments, database size can easily exceed 200MB 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.5MB an hour. This is a combination of VDA and Broker Service heartbeats.

Test Total Logon Growth (MB) Total Logoff Growth (MB)

100k over 1 hour

1,900

1,150

100k over 2 hours

1,900

1,150

 

Log growth is linear over the time period being measured. This data suggests that, per user logon,

the transaction Log grows by ~20KB. Per user logoff the transaction log grows by ~12KB.

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

Monitoring Database

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

Test Total Logon Growth (MB) Total Logoff Growth (MB)

100k over 1 hour

670

190

100k over 2 hours

650

220

 

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

Therefore, growth per day is ~9KB 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/sec
  • SqlServer:Databases – Write Transactions/sec

Site Database

When the system is idle, there are ~5 transactions/sec of which ~1 Write Transaction/sec 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.

Test Logon Logoff
Transactions Per Sec  Write Transactions per Sec Transactions per Sec  Write Transactions per Sec
100k over 1 hour  870 310 250 100
100k over 2 hours  475  170  140  60

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.

Test Logon Logoff
Transactions Per Sec  Write Transactions per Sec Transactions per Sec  Write Transactions per Sec
100k over 1 hour  4 2 4 2
100k over 2 hours 4 2 3.5 2

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 100K 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 5GB 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 100k logon over 1 hour, the version store size stayed in the range of 10MB to 30MB, with a saw tooth effect as versions were created and then cleaned up. During logoff, the range was 10MB to 21MB. When idle, the version store size ranged from 1 to 4MB.

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

The Version Cleanup runs once a minute and reached 2,500KB/s during logon, 1,750KB/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/sec
  • PhysicalDisk – Disk Write Bytes/sec
  • PhysicalDisk – Disk Reads/sec
  • PhysicalDisk – Disk Writes/sec 

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/sec of ~800KB/s for the 1 hour test, and 400KB/s for the 2 hour test. Once a minute, when the consolidation stored procedures run, the transaction log showed spikes to ~30MB/s.

Analysis of the consolidation stored procedures shows that sometimes the statistics make the query plan sub-optimal, 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 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/library/ms189573.aspx.

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

During logon the checkpoints consumed ~500 write IOPs and 6-7MB/s. During logoff the checkpoints consumed ~7MB/s and 200 or 700 IOPs. This variance is 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 4GB 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:

Code Copy

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: http://msdn.microsoft.com/en-us/library/ms187880.aspx

For more details on configuring alerts, see: http://msdn.microsoft.com/en-us/library/ms191508.aspx

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 8KB in size. 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 Reorganize and Rebuild Indexes in the Microsoft TechNet library.

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 sub-optimal 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 300MB of space.

3rd Party Scripts

Microsoft

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

http://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61

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.30GHz with hyper-threading enabled
  • 64GB ECC RAM
  • PERC H710P Mini with 1GB battery backed cache
  • 26 300GB 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 278GB
  • Database Volume
    • Containing the SQL Server Instance and database data files
    • 16 disks as a RAID 10 mirrored stripe
    • Total capacity 2,231GB
  • Log volume
    • Containing the database log files
    • 8 disks as a RAID 10 mirrored stripe
    • o Total capacity 1,115GB
  • 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 61440MB
    • 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.86Ghz, not hyper-thread capable
  • 16GB ECC RAM
  • HP Smart Array E200I Raid card (no battery backed cache)
  • A 36GB or 72GB SAS hard disk

Software:

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