SQL Server 2008 R2 and Hyperthreading

So I have read about the debate about hyperthreading on a SQL Server or not.

Well, I decided to put it to the test.

I have an ETL process that runs about 8 hours, moving data from a DB2 source, to a staging area on SQL Server.
Then it is moved into an operational data store, and then finally into a data mart.

The system has four 6-core Xeon processors.

I turned off hyperthreading, and the ETL process decreased by more than an hour!

So there it is.. My totally unscientific, unique results!

Your mileage may vary, but at least give it a try on a Xeon processor.

peace

SSRS Reporting in Java

So we are in the final testing stages of our SSRS reporting interface in a Java web front-end.

The results are really fantastic! However, going thru this process has taught me a lot about how SSRS works internally, things that I took for granted when using report manager to render reports. These things will probably help my report design in the future, so I thought I would write them down and pay it forward!

First, a bit about how we implemented this:

In our reports, we force exact pagination by having a caption at the top of the report that reads:  This report contains xxx pages.  Since the report wants this number, SSRS will go ahead and process the report to calculate the page count.  If we had left this out, SSRS would render the first page and not worry about how many subsequent pages were coming.

In the front end, we render page 1 of the report, get the total page count, and then display the first page in a viewer similar to report manager. Note that we are NOT using the report viewer control since this is a Java application. We render all pages to HTML4.0 and display as is on the web page. Since SSRS embeds all of the formatting, the front end is responsible for none of that.

When the user clicks to go to the next page, we again call render with the page number in the device info XML.

In parallel, I created a small C# app that calls a report and renders it much the same way.  I did this so I could make sure I fully understood the process and could assist the Java developers.

We ran into issues when our reports took much longer to render in our front end than it did in report manager or my C# app.  So I started watching stats using the ExecutionLog3 view on the report database.  I paid special attention to TimeDataRetrieval, TimeProcessing, and TimeRendering.

From my app I was seeing the data retrieval occurring on the first render, and some time processing and then some time rendering.  These numbers were all pretty much in-line with what I would have expected. For a 110 page report, I had 3000-3500ms for data, 750-1000ms for processing, and 1000-1500 for rendering. On this first page, the Source field was Live.

On moving to the next page though, the Source field was Session, I was seeing no data retrieval at all, and minimal processing and rendering: 0ms for data, 35-40ms for processing, and 100ms for rendering. Very acceptable numbers for a reporting platform! So this tells me that SSRS processes the dataset all at once, does a bit of pre-rendering to get the page count, and fully renders the first page.

From our front end however, I saw something completely different! Every request had a Source of Live, and each request was pulling data and rendering, sometimes taking up to 15-20 seconds to run the report.

I put WireShark on the SSRS server and started watching network packets. I was able to watch every command that came into the SSRS server, and the response from the server.  It quickly became apparent what was happening.  The front end was not sending the page number for the first Render call, and then calling SetExecutionParameters between each page. The first Render call was causing the server to fully render the report for the first page. The SetExecutionParameters call was invalidating the session and causing the report to be full re-rendered, including data retrieval, for each page. Obviously this was the cause behind the huge difference in execution times!

So I worked with the Java developer and was able to remove any extraneous SSRS calls, and now the performance is amazing!

Our report template it pretty complex, we have a lot of background features that require processing, so to have execution times in the low 100ms range is great!

Speaking of our report template, we have a parameter called Date Range.  A user can select a value in this drop down and select ranges such as last month, last year, etc. When the user selects one of these, we call the SQL Server to return a start and end date based on the selection. In report manager, once you select one date range, and then try to select a second, the start and end dates will not update.  This is a “feature” according to Microsoft. Since we wrote our own front end, we were able to work around this. So you can select Last 2 Years and the start and end dates will populate, and then you can change to rolling 13 months and the start and end dates will update accordingly!

That’s all for now… I welcome any and all questions…

peace