xendesktop 7 database sizing - citrix.com

XenDesktop 7 Database Sizing

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

Copyright Citrix Systems, Inc. All rights reserved. Page 1

Contents Disclaimer................................................................................................................................................ 3

Overview ................................................................................................................................................. 3

High Level Considerations ....................................................................................................................... 3

Site Database ...................................................................................................................................... 3

Impact of failure .............................................................................................................................. 4

Monitoring Database .......................................................................................................................... 4


Configuration Logging Database ......................................................................................................... 4


Temporary Database ........................................................................................................................... 5

Read-Committed Snapshot Isolation .................................................................................................. 5

Sizing the Databases ............................................................................................................................... 5

Site Database ...................................................................................................................................... 6

Monitoring Database .......................................................................................................................... 6

Maximum retention periods ........................................................................................................... 6

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

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

Configuration Logging Database ......................................................................................................... 7

Database Activity during logon of 100k HSD Sessions ............................................................................ 7

Transaction Log Growth ...................................................................................................................... 8

Site Database .................................................................................................................................. 8

Monitoring Database ...................................................................................................................... 8

Transactions per second ..................................................................................................................... 8

Site Database .................................................................................................................................. 8

Monitoring Database ...................................................................................................................... 9

CPU usage ........................................................................................................................................... 9

Primary Replica ............................................................................................................................... 9

Secondary Replicas ....................................................................................................................... 10

Temporary Database usage .............................................................................................................. 10

TempDB Sizing................................................................................................................................... 10


Version Store ..................................................................................................................................... 10

Disk I/O .................................................................................................................................................. 11

Database Maintenance ......................................................................................................................... 11

Transaction Log maintenance ........................................................................................................... 12

Index maintenance ........................................................................................................................... 12

3rd Party Scripts ................................................................................................................................. 13

Test Server Configuration ..................................................................................................................... 13

SQL Server Configuration .................................................................................................................. 13

Delivery Controller and HSD Test Servers ......................................................................................... 14


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 Web sites. 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 Web site. 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

o Stores the current configuration and state of the XenDesktop deployment

Monitoring Database

o Stores historical data for display within Director

Configuration Logging Database

o Tracks configuration changes made to 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.

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 connectioninformation to be tracked.

Minimum size is reached when there are no active sessions and VDAs are all shut down andunregistered.


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 broker sessions for users. Existing

connections are maintained, but new connections are not possible.

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

Director to display historical information.


Maximum size is controlled by the configured retention period, as follows:

o For non-Platinum customers the default is 7 days, with a maximum period of 7 days.

o 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.


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-always-

have-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.

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

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

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

http://support.citrix.com/article/CTX137161


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 (MB)

1,000 50 HSD 30

10,000 100 HSD 60

100,000 200 HSD 330

1,000 N/A VDI 30

10,000 N/A VDI 115

40,000 N/A VDI 390

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

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 using the Set-MonitorConfiguration cmdlet.

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

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 20 70 230 900

10,000 HSD 160 600 1,950 7,700

http://support.citrix.com/proddocs/topic/citrix-monitor-admin-v1-xd7/set-monitorconfiguration-xd7.html



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


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.

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.

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


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


Write 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/en-

us/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.

http://technet.microsoft.com/en-us/library/ms189573.aspx

http://technet.microsoft.com/en-us/library/ms189573.aspx


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:

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.

http://msdn.microsoft.com/en-us/library/ms187880.aspx




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-to-

day 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%.

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

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

http://ola.hallengren.com/

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


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

o Containing the OS and page file

o 2 disks as a RAID 1 mirror

o Total capacity 278GB

Database Volume

o Containing the SQL Server Instance and database data files

o 16 disks as a RAID 10 mirrored stripe

o Total capacity 2,231GB

Log volume

o Containing the database log files

o 8 disks as a RAID 10 mirrored stripe

o Total capacity 1,115GB

Software:

Windows Server 2012 standard edition, with current windows updates at time of testing

(August 2013)

SQL Server Enterprise 2012 SP1 with Cumulative Update 5

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 Standard edition, with current Windows updates at time of testing

(August 2013)

Citrix XenDesktop 7.0

xendesktop 7 database sizing - citrix.com

Documents