Output for the simulate option to optdiag prints a row labeled “simulated” for each row of statistics, except histograms. You can modify and load the simulated values, while retaining the file as a record of the actual values.
If you specify binary mode, there are three rows of output:
A binary “simulated” row
A decimal “simulated” row, commented out
A decimal “actual” row, commented out
If you do not specify binary mode, there are two rows:
A “simulated” row
An “actual” row, commented out
Here is a sample of the table-level statistics for the authors table in the pubtune database:
Table owner: "dbo" Table name: "authors" Statistics for table: "authors" Partition count: 3 Statistics for partition: "authors_1376004902" Data page count: 74 Empty data page count: 0 Data row count: 1666.0000000000000000 Forwarded row count: 0.0000000000000000 Deleted row count: 0.0000000000000000 Data page CR count: 10.0000000000000000 OAM + allocation page count: 3 First extent data pages: 0 Data row size: 85.2623049219687914 Parallel join degree: 0.0000000000000000 Unused page count: 5 OAM page count: 1 Derived statistics: Data page cluster ratio: 1.0000000000000000 Space utilization: 0.9521597490347491 Large I/O efficiency: 1.0000000000000000
In addition to table and index statistics, simulate optdiag output includes:
Partitioning information for partitioned tables. If a table is partitioned, the simulated and actual information is printed for each partition of the table. The “Pages in the largest partition” line is not relevant:
Pages in largest partition: 390.0000000000000000 (simulated) # Pages in largest partition: 390.0000000000000000 (actual)
Settings for the parallel processing configuration parameters:
Configuration Parameters: Number of worker processes: 20 (simulated) # Number of worker processes: 20 (actual) Max parallel degree: 10 (simulated) # Max parallel degree: 10 (actual) Max scan parallel degree: 3 (simulated) # Max scan parallel degree: 3 (actual)
Cache configuration information for the default data cache and the caches used by the specified database or the specified table and its indexes. If tempdb is bound to a cache, that cache’s configuration is also included. Here is sample output for the cache used by the pubtune database:
Configuration for cache: "pubtune_cache" Size of 2K pool in Kb: 15360 (simulated) # Size of 2K pool in Kb: 15360 (actual) Size of 4K pool in Kb: 0 (simulated) # Size of 4K pool in Kb: 0 (actual) Size of 8K pool in Kb: 0 (simulated) # Size of 8K pool in Kb: 0 (actual) Size of 16K pool in Kb: 0 (simulated) # Size of 16K pool in Kb: 0 (actual)
To test how queries use a 16K pool, alter the simulated statistics values above to read:
Configuration for cache: "pubtune_cache" Size of 2K pool in Kb: 10240 (simulated) # Size of 2K pool in Kb: 15360 (actual) Size of 4K pool in Kb: 0 (simulated) # Size of 4K pool in Kb: 0 (actual) Size of 8K pool in Kb: 0 (simulated) # Size of 8K pool in Kb: 0 (actual) Size of 16K pool in Kb: 5120 (simulated) # Size of 16K pool in Kb: 0 (actual)