SQL Server 2008 (Katmai) Reporting Services Performance: Two Steps Forward, One Step Back?

SQL Server 2008 (Katmai) Reporting Services Performance: Two Steps Forward, One Step Back?

ne of the largest changes in SQL Server 2008 Reporting Services (SSRS) is that it no longer relies on IIS. Due mostly to feedback from end users on security (most do not like to keep web hosts active on their database servers), the added benefits include reduced memory requirements and service spool-up time.

Testing the Performance of Requests
The tests used for this article were designed to time the performance of requests against the Reporting Service web service (ReportServer2005.asmx). Report rendering and data retrieval was not tested.

The performance tests were conducted using virtual servers running both SQL 2005 and SQL 2008 (CTP4). The test harness was built in VS 2008 and run from the host machine, which was then connected to each Virtual Hard Disk (VHD) (via a web service) when needed. See the sidebar for the specifications and applications.

Both virtual machine?s (VM?s) were running in parallel for the duration of each test. Web service functionality was evaluated using three separate tests:

  1. Cold Server Spool Up
  2. Warm Refresh
  3. Warm Server Spool Up

A simple ASPX test harness running on the host laptop OS made three connections to ReportServer2005.asmx:

  • Accessed the web service on the tested VHD via URL
  • Opened a connection to read a report definition from the web service
  • Looped through the list of parameters in that report
Author’s Note: SQL Server Katmai CTP4 currently uses ReportServer2005.asmx; a ?2008? version is not currently available, though it works against Katmai DBs.

In Visual Studio Web Developer Express 2008 (a free download from Microsoft), the author created a new project and a proxy to the Reporting Service 2005 web service, and named it RS2005. Listing 1 shows the code for the default.aspx.

Each test was provided three trials. If an anomaly occurred, several more tests were run to minimize the effects of running all the tests on a laptop. At one point, the SQL 2005 VHD timed out after 90 seconds on a cold boot test, but after several successful trials it did not seem fair to keep that test in. The tests were repeated until the results were consistent; you could eventually predict the length of time with fair accuracy for each test, as there was not much discrepancy.

Test 1: Cold Server Spool Up
In the Cold Server Spool Up test, each VHD was restarted and booted into an administrative screen. The ASP test harness accessed the VHD, simulating a first web service request to a freshly booted Reporting Services (RS) Server.

Perhaps due in part to the omission of IIS reliance in Katmai, SQL Server 2008 wins this test hands down. The average web service test in SQL Server 2008 took a little over 36 seconds, while web service access to a SQL Server 2005 machine took about 66 seconds, or an improvement of about 45 percent (see Table 1 and Figure 1)!

Cold Server Spool Up Test

SQL 2005

SQL 2008

Trial 1

63.19s

33.48s

Trial 2

67.20s

43.80s

Trial 3

68.15s

32.57s

Avg

65.52

36.62s

Table 1. Cold Server Spool Up: This table shows the Cold Server Spool Up test results.

?
Figure 1. Cold Start Test: The winner of the Cold Server Spool Up Test is the SQL Server 2008 (Katmai).

Test 2: Warm Refresh
Each Warm Refresh test made an additional call to the RS Web Service within a few moments of the initial test call to simulate another user accessing a report prior to a reboot or any service/application pool refresh.

After several attempts the SQL 2005 results were fairly predictable at under 0.3 seconds; however, the Katmai machine exhibited a strange anomaly: response time was either well under 0.1 seconds (twice the speed of 2005) or as high as 2.9 seconds. This was reproduced several times: two trials at 0.15 seconds, the next at 2.8, another trial at 0.1, and the next two at 2.9 and 3.0. This could have been simply the result of the VHD test environment (antivirus, etc.), but because this behavior was not observed with SQL 2005, one of the high values from the Katmai test were left in the test results (see Table 2 and Figure 2).

Warm Refresh

SQL 2005

SQL 2008

Trial 1

0.27s

0.1s

Trial 2

0.22s

43.80s

Trial 3

0.11s

0.02s

Avg

0.2s

1.026667s

Table 2. Warm Refresh: This table shows the results of the Warm Refresh tests.

?
Figure 2. Warm RefreshWinner: The SQL Server 2005 wins the Warm Refresh Test?

Test 3: Warm Server Spool Up
The Warm Server Spool Up test was meant to simulate a user accessing the web service after it had been idle on the server for some period of time. The RS?s reliance on IIS means it is subject to that system’s control of system cache and application pool resource management. After periods of inactivity it is common for a host to wind down. Still, a user request on this host may not take as long as a cold spool up. To simulate this, the application pool controlling the web service in IIS Manager was recycled.

There was little reason to test the SQL 2008 machine as IIS was absent, so presumably that same refresh should never occur. As there is no IIS control, the author predicted that this service would never actually stop except due to error or server reboot. Just for fun, the author wound down the Windows service controlling the SQL Server 2008 Report Server anyway. Even with this imposed handicap, Katmai was still a little over 1 second faster, an improvement of about 11.5 percent. The real story here is the nearly complete elimination of the time lag due to application pool reset (see Table 3 and Figure 3).

Warm Server Spool Up Test

SQL 2005

SQL 2008

Trial 1

9.85s

8.42s

Trial 2

7.69s

8.25s

Trial 3

10.55s

8.17s

Avg

9.36s

8.28s

Table 3. Warm Server Spool-up Test: This table shows the results for the Warm Server Spool-Up.

?
Figure 3. (Warm) Service Reset: SQL Server 2008 Wins the Warm Server Spool Up Test.

Test Performance Conclusion
Overall, the clear winner here is Katmai, SQL Server 2008. If performance, scalability, or IIS security held you back from SQL Server in the past, now is the time to begin testing with Katmai SSRS.

Comparing SSRS Development Efficiency in SQL Server 2008 Katmai to the SSRS 2005

?
Figure 4. Report Designer: Here is the Report Designer Preview?with ribbon!

Unfortunately, all is not peachy in the land of Katmai. The next test included development in CTP4 using the Report Designer Preview. While still clearly a prerelease product (there is no expression builder yet so you have to write code in a simple text box), it hints at the new direction that the RS team is taking. This new version takes bits from BIDS, Office 2007 (ribbon included), and Report Builder 2005, and mashes them together for a single development interface (see Figure 4). The new interface should help capture hard-core report developers who felt Report Builder was too simplistic.

Report Builder will not be going away (at least not yet) but it is clear that Microsoft intends business analysts and non-techies to use the ?Report Designer.? and this is where Katmai falls short.

New forms and pop-ups take an Office 2007 and Vista approach; they are clean and functional. But the functions that work for single clicks (like bolding in a Word document), do not easily translate for development with much greater form interaction.

A case in point is the sheer number of additional clicks it takes to build a report in RS 2008. Creating parameters used to take place on a single form, now a popup modal form appears for each new parameter. That popup has very nice visual appeal, but the design loses tons of efficiency. All properties used to be set on a single interface (parameter name, defaults, valid list of values, etc.).

To call the previous form design of RS 2005 ?Spartan.? would be an overstatement, but it got the job done. Conversely, to set values on RS 2008 Report Designer you must click into each section: General, Available Value, Default Values, and the new Advanced. There is no shortcut key, so you will be jockeying from keyboard to mouse for these extra steps.

?
Figure 5: RS 2005 Parameters: This figure shows a simple grid that easily allows entering values.

As previously mentioned, the Katmai version is clean. But after you get past the color gradations a few challenges arise. For example, the list of parameters display only on the General tab. If you are working on the Available Values tab you can no longer see your parameters. Trivial? Perhaps. But many people like that kind of visibility.

Further adding insult to injury, it may take up to five times longer to provide valid lists of “Available Values” than it did in the earlier version of Reporting Services. In RS 2005 always presented you with a simple grid in which you could easily enter values (see Figure 5). And the grid dynamically resized; for each new value, a row was automatically created.

?
Figure 6: The Grid is Gone in 2008: This figure shows adding Report Parameters in SQL Server 2008 (Katmai).

In SSRS 2008, as Figure 6 shows, the grid is gone. Now you must add each possible value by clicking an Add button, and then clicking into the row to provide the detailed elements. A significant level of efficiency is lost. Katmai uses this long-winded approach for every input, from parameters to data sets and properties. Get used to it.

Dropping the automatic grid function on development UI components in favor of the new Add button concept takes a huge step backwards in productivity. Of course, you are not required to manually enter values if you derive them from a query. But sometimes values are entered during the prototype phase or when it is unnecessary to roundtrip to a database.Please keep in mind that many items may change before the retail release. However, it does seem clear that the behavior described in this article is expected in the upcoming version.

See sidebar 2 for the Author?s Parameter entry wish list for future versions.

SSRS Reaches Adolescence
SSRS is growing up, that?s clear. With this third version the product keeps maturing. Benefits will be realized in performance and scalability and similar improvements are expected in SSIS. However, the new development UI does little to embrace the developers who have been using this tool since the 2000 version. No doubt, new converts to SSRS will adjust nicely to the Office 2007 look-and-feel, but business users and other non-techies might feel as though they have been left hanging.

devx-admin

devx-admin

Share the Post:
USA Companies

Top Software Development Companies in USA

Navigating the tech landscape to find the right partner is crucial yet challenging. This article offers a comparative glimpse into the top software development companies

Software Development

Top Software Development Companies

Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in

India Web Development

Top Web Development Companies in India

In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India,

USA Web Development

Top Web Development Companies in USA

Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner

Clean Energy Adoption

Inside Michigan’s Clean Energy Revolution

Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the

Chips Act Revolution

European Chips Act: What is it?

In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor

USA Companies

Top Software Development Companies in USA

Navigating the tech landscape to find the right partner is crucial yet challenging. This article offers a comparative glimpse into the top software development companies in the USA. Through a

Software Development

Top Software Development Companies

Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in and explore the leaders in

India Web Development

Top Web Development Companies in India

In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India, and kickstart your journey to

USA Web Development

Top Web Development Companies in USA

Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner for your online project. Your

Clean Energy Adoption

Inside Michigan’s Clean Energy Revolution

Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the state. A Senate committee meeting

Chips Act Revolution

European Chips Act: What is it?

In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor supply chain and enhance its

Revolutionized Low-Code

You Should Use Low-Code Platforms for Apps

As the demand for rapid software development increases, low-code platforms have emerged as a popular choice among developers for their ability to build applications with minimal coding. These platforms not

Cybersecurity Strategy

Five Powerful Strategies to Bolster Your Cybersecurity

In today’s increasingly digital landscape, businesses of all sizes must prioritize cyber security measures to defend against potential dangers. Cyber security professionals suggest five simple technological strategies to help companies

Global Layoffs

Tech Layoffs Are Getting Worse Globally

Since the start of 2023, the global technology sector has experienced a significant rise in layoffs, with over 236,000 workers being let go by 1,019 tech firms, as per data

Huawei Electric Dazzle

Huawei Dazzles with Electric Vehicles and Wireless Earbuds

During a prominent unveiling event, Huawei, the Chinese telecommunications powerhouse, kept quiet about its enigmatic new 5G phone and alleged cutting-edge chip development. Instead, Huawei astounded the audience by presenting

Cybersecurity Banking Revolution

Digital Banking Needs Cybersecurity

The banking, financial, and insurance (BFSI) sectors are pioneers in digital transformation, using web applications and application programming interfaces (APIs) to provide seamless services to customers around the world. Rising

FinTech Leadership

Terry Clune’s Fintech Empire

Over the past 30 years, Terry Clune has built a remarkable business empire, with CluneTech at the helm. The CEO and Founder has successfully created eight fintech firms, attracting renowned

The Role Of AI Within A Web Design Agency?

In the digital age, the role of Artificial Intelligence (AI) in web design is rapidly evolving, transitioning from a futuristic concept to practical tools used in design, coding, content writing

Generative AI Revolution

Is Generative AI the Next Internet?

The increasing demand for Generative AI models has led to a surge in its adoption across diverse sectors, with healthcare, automotive, and financial services being among the top beneficiaries. These

Microsoft Laptop

The New Surface Laptop Studio 2 Is Nuts

The Surface Laptop Studio 2 is a dynamic and robust all-in-one laptop designed for creators and professionals alike. It features a 14.4″ touchscreen and a cutting-edge design that is over

5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in Japan. This innovative approach will

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial intelligence (AI) in journalism. These

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this autumn sale has already created

Cisco Splunk Deal

Cisco Splunk Deal Sparks Tech Acquisition Frenzy

Cisco’s recent massive purchase of Splunk, an AI-powered cybersecurity firm, for $28 billion signals a potential boost in tech deals after a year of subdued mergers and acquisitions in the

Iran Drone Expansion

Iran’s Jet-Propelled Drone Reshapes Power Balance

Iran has recently unveiled a jet-propelled variant of its Shahed series drone, marking a significant advancement in the nation’s drone technology. The new drone is poised to reshape the regional

Solar Geoengineering

Did the Overshoot Commission Shoot Down Geoengineering?

The Overshoot Commission has recently released a comprehensive report that discusses the controversial topic of Solar Geoengineering, also known as Solar Radiation Modification (SRM). The Commission’s primary objective is to

Remote Learning

Revolutionizing Remote Learning for Success

School districts are preparing to reveal a substantial technological upgrade designed to significantly improve remote learning experiences for both educators and students amid the ongoing pandemic. This major investment, which

Revolutionary SABERS Transforming

SABERS Batteries Transforming Industries

Scientists John Connell and Yi Lin from NASA’s Solid-state Architecture Batteries for Enhanced Rechargeability and Safety (SABERS) project are working on experimental solid-state battery packs that could dramatically change the

Build a Website

How Much Does It Cost to Build a Website?

Are you wondering how much it costs to build a website? The approximated cost is based on several factors, including which add-ons and platforms you choose. For example, a self-hosted