Database Reference
In-Depth Information
Relative Performance Measurements
It would be nice if absolute performance benchmarks were always provided, but that is seldom the case. Where
there are no predefined values to measure performance against, you must rely on tuning the BI solution using
relative performance.
Relative performance tuning can be somewhat tricky, because you need to establish a common point of
reference on your own by creating a benchmark.
Follow these steps to create a benchmark:
1.
Create the BI solution and then execute the actions you expect to be performed
regularly.
2.
Use tools to measure the time or resources used while performing these actions, and
document them. This is your relative benchmark.
3.
Repeat the process as the BI solution increases activity.
4.
Compare the original values to the new values.
5.
Continue monitoring the trends in the values until you isolate performance
bottlenecks.
6.
Implement performance-tuning techniques such as using RAID arrays, adding RAM,
or upgrading processors.
7.
Remeasure the counts to prove or disprove that the tuning technique has produced
the desired effect.
Measuring Performance with SQL Profiler
Microsoft provides a number of tools that can help measure performance. One very useful tool that comes with
SQL Server is SQL Server Profiler. To access SQL Profiler, navigate to Start
➤
MS SQL Server 2012
➤
Performance
Tools
➤
SQL Server Profiler. This starts the application.
Creating a Profiler Trace
Once SQL Profiler starts, you need to create a new SQL trace. Traces are collections of monitoring objects that
track activity on your SQL Server or SSAS database engines.
The first step to creating a trace is to access the File
➤
New Trace menu option. A dialog window appears
asking which database engine you want to connect to. To select the SQL Server database engine, use the “Server
type” dropdown box and choose Database Engine, as shown in Figure
18-14
.
Use the same dropdown box to
select the Analysis Server database engine.
