Month: November 2012

Performance testing analysis services cubes

Recently I was asked to do some performance testing and benchmarking for a SQL Server Analysis Services 2008 (SSAS 2008) cube. As it happens in most of the projects, I did not have much time and I needed to find a quick way to do this. In this post, I would like to jot down the approach I took in the hope that one day it might be useful to somebody. So here it goes.

Some background..

The cube in the question was of an average size. About 20GB in size with 10 dimensions and 5 measure groups. At any time, maximum 15 people would use it exclusively through Excel 2007. This gave me a good starting point. I wanted to test the performance of the cube for 15 people with queries which typically are like the one generated in Excel.  I also came to conclusion that in each query the response time of around 300ms is acceptable (Of course this is debatable but I leave it up to you).

Collecting the data..

Given that all the queries will be from Excel, I had two options to collect test queries. Using OLAP PivotTable Extension which is also mentioned on my Tools and Tips page or using SQL Server profiler.

Collecting queries from OLAP PivotTable extension is very straight forward. You build your pivot table and copy query from the extension’s dialogue box. However, there are two main drawback I found with this approach. One, it is one-query-at-a-time approach. Two, the pivot table would be built by me and not the actual users. I don’t necessarily know what dimension and measure groups they are most interested in and so the queries may not necessarily represent actual workload. I also did not have the option of asking each end-user to create a pivot table and send me the queries. I though that’s too much to ask.

Using SQL Server profiler solves both the issues. Since end-users are continuously using the cube, I get variety of real life queries and I can save them in batch. As it turns out there is a way to export exact MDX queries from SQL server profiler. So I ran SQL Server profiler on the server (yes, I know, you should not run profiler on live server but in this case I took the risk!) at peak time while maximum users were using the cube and exported the MDX queries. When profiler exports the queries, it exports it in a batch i.e. all the queries are in one file. Here, I resorted to manually separating each query in a separate file.

The toolset..

For testing/benchmarking exercise, I used AS Performance Workbench. It is the simplest and most intuitive tool I could find around. I am pretty sure there are other utilities and methods available but this one was just what I was looking for. It is simple to use, the report generated is pretty and easy to understand, it can simulate load and ,most importantly, it is free.

The method..

I decided to create 25 query files. They covered all the dimensions and measures. I selected more complex queries on frequently used measures. I also decided to do three runs with 10,12 and 15 users respectively. This varied the number of users nicely. I did the first run on cold cache i.e. I emptied the cache before running the first test while for second and third run I let the cache as it is. Also I made sure that nobody else is using the server in any way while I was testing. This was done to get a more realistic picture about effect of queries on CPU and IO. If any other services are running on the server, then they will be using CPU and disk, which will skew the results.

The results..

There were two aspects to this exercise – benchmarking and testing.

Benchmarking part was easy. After each run I generate the report and saved it. I also saved all the queries along with details of the hardware on which testing was carried out and a brief report. The idea is that after while when the data increases, we would run the same set of test again to see performance change.

As for testing I was most interested in three counters

a. % Processor Time

b. Avg. duration for each query

c. Current Disk Queue Length

A small table for each run like below sufficed.

Counter Expected Result Actual Result Pass/Fail
% Processor Time  Below 60%  45.96 %  Pass
Current Disk Queue Length  Less than 1  0  Pass
Avg. Query 1 duration  300 ms 175 ms  Pass
Avg. Query 1 duration  300 ms  390 ms  Fail

Hope that this post will be useful to somebody or may be in future!!

Exporting MDX queries from SQL Server profiler

I was working on a project to load test and benchmark SQL Server Analysis Services(SSAS) database (blog post to follow soon  a brief about which is here). As part of the exercise, I needed to capture MDX queries running on the given SSAS Cube.  SQL Server profiler makes this very simple. Here are the steps.

1. Start SQL Server profiler and connect to the SSAS server. If you want to you can configure the trace properties and filter for one database. Start the trace.

2. Open Excel and connect to SSAS cube.

3. Build your pivot table (or drag and drop stuff around to get a range of queries.)

4. Go back to profiler and Stop the trace.

5. Click File–>Export–>Extract SQL Server Analysis Services Event–>Extract MDX Events.

6. Save the file as MDX.

A slight issues is that this will store all the MDX queries in one mdx file. If you need to have each query in a separate file, I would suggest using notepad++ and creating a small macro do this. Works like a charm!!

Another approach to get the queries is to use OLAP Pivot Table Extension where you build the pivot table and copy the MDX from the extension. The steps are better explained on codeplex.

Changing workspace database name in SSAS 2012 Tabular

A word of caution. Before you try to implement any steps mentioned in this entry, I would like to point out that it is not recommend. Moreover, I do not take any responsibility if this causes any issues in your project. 

A tabular model workspace database is created automatically when you create a new  SQL Server Tabular Project in SQL Server Data Tools (SSDT). By default it is created on local SSAS Tabular server but you can choose a different server as well. The workspace database name consist of the project name, user name ,GUID and frankly it looks quite gibberish.  For example look at the screenshot of my SSMS on my test virtual machine. Yikes!! Now imagine this on a server with 10 concurrent users, each with 10 projects and you will get the idea.

So I was wondering if I can somehow rename them. You know something pretty. And as it turn out, I can. So here are the steps.

1. Starts SSTD (obviously!!) and create a new Analysis Services Tabular Project. At this point I would also recommend that you rename the default ‘Model.bim’ to something more meaningful to your project.

2. Right click on bim file and click properties.

3. Apart from all the other properties, you will find Workspace Database property in the bottom portion. The description of the properties is here. If you open SSMS and connect to SSAS Tabular instance, you should find a database with the same name as this property value. As you would notice, Workspace Database cannot be changed. It is read-only and that’s the one we want to change.

4. Now right click on project ,click Open Folder in Windows Explorer and close SSDT.

5. In windows explorer, there would be *.settings file. The format of this file seems to be ModelName.bim_username.settings. It might be hidden so change the folder settings to show hidden files.

6Open this file in notepad. It is an xml although everything will come on one line. I would also recommend that you don’t try to format the file.

7. Now look for <DatabaseName> </DatabaseName> tag. The value between this tag is name of the database. Change this value to something more meaningful, save and close notepad.

8. Open the project/solution in SSDT. Check the properties of bim file. The value of property Workspace Database would be what you set in Step 7.

9. You can also open SSMS and verify this.

Neat, isn’t it??

SQL Server Query Optimizer and a trivial observation

This is a very short post on a trivial observation about SQL Server query optimizer. Look at the following query on AdventureWorksDW2012 database.

SELECT FinanceKey
,Amount
,DATE
FROM [dbo].[FactFinance] FF
INNER JOIN dbo.DimDate DD ON FF.DateKey = DD.DateKey
INNER JOIN dbo.DimOrganization DO ON FF.OrganizationKey = Do.OrganizationKey

And now look at the execution plan
Query Execution Plan

The joins to the dimension tables are missing. Now look at the query again. The optimizer was intelligent to recognize that the join would not have any effect on the final result set. We are not selecting anything from the dimension table neither there is a filtering condition. Hence the optimizer removed it from the plan altogether to make it more efficient.
Now lets add WHERE clause
SELECT FinanceKey
,Amount
,DATE
FROM [dbo].[FactFinance] FF
INNER JOIN dbo.DimDate DD ON FF.DateKey = DD.DateKey
INNER JOIN dbo.DimOrganization DO ON FF.OrganizationKey = Do.OrganizationKey
where DD.CalendarYear = 2005

and look at the execution plan. The WHERE clause adds a clustered index scan on DimDate but DimOrganization is still missing since nothing is selected or filtered on the table. Clever!!

Export file names from a folder

Lets say you want to export names of all the files and folders within a folder. Doing it one at a time will be pretty time consuming. This little trick will come handy in such situations. Open up cmd prompt and navigate to the folder from which you want to export file names.

Let me introduce you to “>>” operator. What this will guy will do is transfer the output of the DOS command you are executing to the file specified. This is very handy if you are executing SSIS packages through a batch command. Put “>>” at the end of every command, specify the file name and all the output messages will be transferrred to the file. In the following cases, the file is called ‘files.txt’ and will be created where ever your command promt location is. You can always specify a different location.

1. To get just the names of files and folders 

 dir /B  >> files.txt

2. To get the full path of files and folders 

 dir /B /S >>files.txt

3.To get only specific files for e.g. csv

dir /B /S *.csv >>files.txt

dir /B /S *.csv  >>files.txt

dir /B /S *.txt >>files.txt

dir /B /S *.xls >>files.txt