Performance Optimization - Professional SQL Server Analysis Services 2005 with MDX - page 625

Overview
As any good English dictionary will tell you, performance has several possible
meanings. In computer science, performance most often refers to the function-
ing efficiency of software and can relate to both speed and scalability. There
are established standards and benchmarks to measure and compare the per-
formance of products and services. Why care about performance? Well, con-
sider your job performance; assuming you are an employee, your job review
and, therefore, salary raise and bonus, will hinge on how well you do your job.
In order to get the best work out of you, your manager needs to know what
your interests are, what motivates you, and then assign appropriate tasks to
get the maximum performance from you. Your manager will be rewarding you
for your performance. Usually in the currency you like most, cash.
It follows that if you are a data warehouse designer using Analysis Services
you need to know how to get the best performance from the system so as to
satisfy the customers. Just like your boss can push certain buttons to motivate
you, Analysis Services provides various parameters that can be set to achieve
maximum performance. As for server products such as Analysis Services, one
can attribute performance results to how well server properties are tuned in the
context of speed and scalability requirements.
The graph shown in Figure 13-1 is a typical server scalability graph. The query
throughput lines show the server throughput (queries served per minute) as
more users are using the system concurrently for two different hardware con-
figurations. For Hardware 1, up to about 50 users, the server throughput in-
creases linearly. That means the server has sufficient resources to support 50
concurrent users. Then after about 50 users, the throughput starts to flatten
out. In the 50-to-100 user range, the server doesn't have enough resources
(CPU or memory or disk) to serve requests of all concurrent users. In this cir-
cumstance, some user requests would be queued in the system request queue
to keep the system from slowing down all the user requests. At about 100
users, the system is running at maximum capacity. The curve flattens off at
high loads because internally, the server only executes a few queries concur-
rently, and queues the rest. This is so that with many outstanding queries, new
users can still get reasonable response time while connecting to the server
and executing non-query commands.