-
Notifications
You must be signed in to change notification settings - Fork 0
/
README.TXT
61 lines (41 loc) · 4.08 KB
/
README.TXT
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
INTRODUCTION
These are the scripts for performing a CPU benchmark on a SQL Server database.
PREPARATION
The procedure sp_cpu_loop executes a simple loop. Therefore, you have to first execute the script create_sp_cpu_loop.sql before proceeding with the tests.
SINGLE TEST
Typically, you would use Invoke-Load for executing a single test, like:
Invoke-Load -Server Server\Instance -Concurrency 3
TEST SERIES
But what's more interesting is to run a series of tests with a random load:
Invoke-Random-Load -Server Server\Instance -MaxConcurrency 6 -iterations 1000 *> random_load.log
The command above runs 1000 tests with a random concurrency ranging from 1 to 6.
ANALYSIS
Then, you can run Show-Statistics for doing the analysis:
Show-Statistics -File random_load.log
Load AVG Iterations/s AVG SOS waits(%) AVG CV AVG ratio max min MAX Iterations/s Run id 1 MAX SOS waits(%) Run id 2 MAX CV Run id 3 MAX ratio max min Run id 4
---- ---------------- ---------------- ------ ----------------- ---------------- -------- ---------------- -------- ------ -------- ----------------- --------
12 11255.4 10.8 10.1 1.4 16771 1194 24.2 1617 18.4 2382 1.8 2382
11 10708 9.7 9.5 1.3 11330 397 12.7 1435 14.9 397 1.6 1435
10 10204 9.3 8.8 1.3 10466 1969 13.9 136 14.1 136 1.5 136
9 9751.7 7.4 8 1.3 9993 769 15.9 329 15.5 329 1.7 369
8 9316.3 5.9 7.4 1.2 12749 1191 17.9 1560 16.7 1787 1.7 2073
7 8818.8 3.9 6.6 1.2 9215 1361 21.9 1361 21.1 1361 1.7 1361
6 8310.4 0.3 5 1.2 8833 1487 24.3 1432 15.9 1432 1.4 1432
5 7678.8 0.1 4.1 1.1 7928 482 0.1 887 10.4 1788 1.3 1788
4 6783.8 0.1 3.9 1.1 7127 97 0.5 2181 12.9 543 1.3 875
3 5523.7 0.1 3.2 1.1 6035 1836 0.1 1808 16.8 2015 1.5 2015
2 4803.3 0.1 0.4 1 5066 2186 0.2 1711 1.7 317 1 317
1 3421.7 0.1 0 1 3519 937 0.4 1853 0 2398 1 2398
Calculated metrics are:
- "Iterations/s": average loop iterations per second
- "SOS waits(%)": SCHEDULER_OS_YIELD wait time overhead in %
- "CV": Coefficient of variation of the elapsed times od each session in a
single run
- "ratio max min": max(elapsed time)/min(elapsed time) in a single run
- "Average": average elapsed time per execution in a single run
All of the metrics are calculated for a given load, where the load corresponds to the number of the concurrent sessions. For each metric an average and a maximum value are calculated per load. The column right from the maximum value contains the run id, which gives you a reference for zooming into the outliers in the log file.
All of the metrics will be displayed by default. However, the scope can be limited by the Metric parameter:
Show-Statistics -File random_load.log -Metric "CV","SOS waits(%)"
CASES STUDIES
- Microsoft Azure CPU Performance: https://nenadnoveljic.com/blog/microsoft-azure-sql-server-cpu-performance/
- SOS_SCHEDULER_YIELD waits: https://nenadnoveljic.com/blog/sos_scheduler_yield-waits-during-low-cpu-utilization/