John Gallardo's Weblog

DataContext.Log and Windows Phone

John Gallardo [MSFT] — Fri, 27 May 2011 23:31:11 GMT

Among the many features that are compatible between Linq To SQL on desktop Windows and on Windows Phone is the support for capturing all of the generated SQL as it is being sent to the database. This can be a great way to both gain an understanding of how L2S works, as well as optimize query complexity. One of the challenges with using this feature is actually collecting the data. Extracting the log out of isostore can be problematic. Typically I like to visualize the output of my queries in the Debug Output Window. However the problem there is that there is no TextWriter in the platform that can write there.

The solution is to simply wrap Debug.WriteLine() in your own TextWriter implementation.

I am sure this has been done many times before, but here is my implementation:

namespace PhoneHelper
{
    using System;
    using System.Diagnostics;
    using System.IO;
    using System.Text;

    public class DebugStreamWriter : TextWriter
    {
        private const int DefaultBufferSize = 256;
        private StringBuilder _buffer;

        public DebugStreamWriter()
        {
            BufferSize = 256;
            _buffer = new StringBuilder(BufferSize);
        }

        public int BufferSize
        {
            get;
            private set;
        }

        public override System.Text.Encoding Encoding
        {
            get { return Encoding.UTF8; }
        }

        #region StreamWriter Overrides
        public override void Write(char value)
        {
            _buffer.Append(value);
            if (_buffer.Length >= BufferSize)
                Flush();
        }

        public override void WriteLine(string value)
        {
            Flush();

            using(var reader = new StringReader(value))
            {
                string line; 
                while( null != (line = reader.ReadLine()))
                    Debug.WriteLine(line);
            }
        }

        protected override void Dispose(bool disposing)
        {
            if (disposing)
                Flush();
        }

        public override void Flush()
        {
            if (_buffer.Length > 0)
            {
                Debug.WriteLine(_buffer);
                _buffer.Clear();
            }
        }
        #endregion
    }
}

And the output:

Now From Windows Phone

John Gallardo [MSFT] — Tue, 24 May 2011 21:09:00 GMT

Last November I decided to leave the SQL Reporting Services team and join the Windows Phone Application Platform team. This is the team responsible for delivering:

The overall application model for windows phone (how are they installed, secured and activated)
Runtime integration (hosting of Silverlight Mobile and XNA applications within NetCF)
Phone specific frameworks (ex: Sensors)
Data and Cloud programmability (Push Notifications, Structured Storage)

I have been working mostly on data programmability since joining the team, bringing Linq To SQL down to the phone from the desktop.

You can play with the new features in the recently released Developer Tools for Windows Phone Mango Beta.

Also, Jesse Liberty has done a good job of capturing some of the best practices for Linq To SQL on Windows Phone.

RS PowerShell Gems – The WMI Provider

John Gallardo [MSFT] — Thu, 27 May 2010 00:20:48 GMT

For IT administration of a Report Server instance, you will occasionally need to use WMI. We try to ease this to some extent by exposing a command line tool rsconfig.exe as well as the RS Configuration Tool UI. That said, sometimes you just need to talk to WMI directly – and for that PowerShell is a great tool!

Here are a couple of script fragments that I have used in the past:

# Gem 1 - Enumerate All Instances
#

$computer = "JGALLA7"
$namespaces = gwmi -class "__NAMESPACE" -namespace "root\Microsoft\SqlServer\ReportServer" -computer $computer
[string]$fn = $namespaces[0].Name

# any of the namespaces can be used to enumerate all instances
$instances = gwmi -class MSReportServer_Instance -namespace "root\Microsoft\SqlServer\ReportServer\$fn\v10" -computer $computer
$instances | ft InstanceName,EditionName,Version

#InstanceName                            EditionName                             Version
#------------                            -----------                             -------
#MSSQLSERVER                             ENTERPRISE EVALUATION EDITION            ####
#ELEND                                   ENTERPRISE EVALUATION EDITION            ####
#VIN                                     ENTERPRISE EVALUATION EDITION            ####
#SAZED                                   DEVELOPER EDITION                        ####

# Gem 2 - get service account of each instance

$configurations = gwmi -class MSReportServer_ConfigurationSetting -namespace "root\Microsoft\SqlServer\ReportServer\$fn\v10\Admin" -computer $computer
$configurations | ft InstanceName,ServiceName,WindowsServiceIdentityConfigured

#InstanceName                            ServiceName                             WindowsServiceIdentityConfigured
#------------                            -----------                             --------------------------------
#MSSQLSERVER                             ReportServer                            redmond\####
#ELEND                                   ReportServer$ELEND                      NT Authority\NetworkService
#VIN                                     ReportServer$VIN                        NT Authority\NetworkService
#SAZED                                   ReportServer$SAZED                      NT AUTHORITY\NETWORK SERVICE

# Gem 3 - Update Service Account Password of each instance using a particular user name
$username = "redmond\####"
$newpassword = "mynewpassword"
foreach ($update in $configurations | where {$_.WindowsServiceIdentityConfigured -eq $username})
{
$update.SetWindowsServiceIdentity(0, $username, $newpassword)
}

Some additional useful links:

RunningJobContext.IsClientConnected

John Gallardo [MSFT] — Mon, 01 Jun 2009 20:35:11 GMT

I’ve seen a few people get confused over what this error message in the Reporting Services log file indicates. This message is generated when the Reporting Services web server detects that an HTTP request has experienced a remote disconnect. In practice, this means that we queried the property HttpResponse.IsClientConnected and it returned false.

So how does this work exactly, and are all requests subject to this kind of check? As Reporting Services is currently architected, not all operations are subject to this check. For potentially long-running operations (such as report renderings and interactivity operations – internally we refer to these as Cancelable operations) requests are registered with a central request manager, which has a thread which wakes up periodically to check whether or not the clients are still connected. If they are not, then we begin the process of aborting that request on the server side in order to reclaim any resources that it is consuming.

Basically every operation on the ReportExecution2005.asmx endpoint, as well as the UpdateReportExecutionSnapshot(), CreateReportHistorySnapshot(), GetReportParameters(), and GetUserModel() methods on the ReportService2005.asmx endpoint register themselves as potentially long running operations.

So what should you do if you are seeing a lot of these errors in the log? Generally this is an indicator that requests are being torn down for some reason. This could be due to certain set of users being particularly impatient waiting for report results to return and closing their browser. Alternatively, we have seen some cases in the wild where certain proxies would internally time out requests after waiting for some period of time, at which time they close the connection to the Report Server. If you have such a deployment topology (proxy/load balancer between clients the Report Server) definately consider checking if such a configuration knob exists for your environment.

Hiding parameter area when viewing reports

John Gallardo [MSFT] — Tue, 24 Mar 2009 00:19:36 GMT

When rendering reports, oftentimes the application would like to minimize as much of the non-report area of the viewer control as possible. This is easily accomplished when rendering the report through the built-in report viewer control hosted in the ReportServer web site (http://servername/reportserver).

There are two parameters which control the rendering of the viewer control area:

rc:Parameters
rc:Toolbar

rc:Parameters can have 3 values:

On	Displays the parameter area as in the screenshot above.
Off	Completely hides the parameter area.
Collapsed	Renders the parameter area initially minimized, so a user can expand it if they need to view/change the parameters for a report.

Here is an example URL which demonstrates rendering a report with rc:Parameters=Collapsed:

https://servername/ReportServer?%2fSamples%2fSQL+2008+Samples%2fAdventureWorks2008+Sample+Reports%2fEmployee+Sales+Summary+2008&rs:Command=Render&rc:Parameters=Collapsed

The other way of hiding the parameter area is to render the report with rc:Toolbar=false. For many applications though, there are side-effects of this approach which I don’t think are desireable. These are:

It disables the automatic ping-mechanism to keep the user’s report session alive. This can lead to rsExecutionNotFound errors if the user leaves the page inactive for several minutes.
It also removes the navigation (next page, find, etc…) UI.
By default, it will cause every page of the report to be returned to the client. For long reports, this is typically not desireable.

So if you are in a situation where you want to minimize the amount of extraneous UI initially presented to the user, while still allowing flexibility in having the end user change the parameter values, consider using rc:Collapsed=true when rendering the report.

Hiding Rendering Extensions

John Gallardo [MSFT] — Thu, 05 Feb 2009 21:15:00 GMT

This is documented behavior, but we see lots of questions on it. A good mechanism for preventing users from accidently exporting reports to a format that you don’t want (for example, you might know that the report doesn’t render quite right in a particular format) is to mark the extension as “invisible” in the Report Server config file.

This MSDN documentation explains how to configure a rendering extension:

http://msdn.microsoft.com/en-us/library/ms154516.aspx

The important one here is the Visible attribute:

Visible

A value of false indicates that the rendering extension should not be visible in user interfaces. If the attribute is not included, the default value is true.

By setting this attribute to false for a given extension, it will hide the extension from being visible in the Report Viewer control as well as in the delivery configuration pages.

RS Blogger shout-outs

John Gallardo [MSFT] — Fri, 09 Jan 2009 21:00:13 GMT

If you are interested in Reporting Services, you probably already know about these blogs … because frankly they have more interesting things to say than me! At any rate, just in case they happened to slip through the cracks for you, I thought I would point you towards their blogs:

Robert Bruckner – Robert is a developer that works on the Report Processing engine and basically knows just about everything there is to know about RS. If you have been to any of the trade shows or other various RS conferences, you probably have met him. If you have spend time on RS forums or newsgroups, you probably read some insightful response he had to a question. He has a great ‘advanced’ RS blog that covers a broad range of topics.

Brian Hartman – Brian developed the Report Viewer control and has been hanging out on the MSDN forums answering your questions for years. So if you are integrated Reporting Services with your application, you probably have used his stuff. He’s finally succumbed to the pressure of starting his own blog to answer a lot of the common questions and themes he sees related to the Report Viewer.

Avoid using HttpContext in your reports

John Gallardo [MSFT] — Fri, 09 Jan 2009 20:43:01 GMT

On one of our internal mailing lists, someone was asking trying to retrieve some HTTP Headers that their internal application was submitting to the Report Server within the report. Someone else responded with a code snippet containing the custom code that you can put into an RDL expression to do just that.

This is a bad idea.

HttpContext has the interesting property of exposing per-request state via a static property. Inherently, there is nothing really wrong this approach. It allows access to data without having to plumb the data all of the way through every single API or object on the call stack. However, one gotcha here is that HttpContext is only available on the thread that received the request, and not on any asynchronous threads that may be spawned by that request. This can lead to subtle problems in your reports under the following conditions:

When printing the report, the Report Server actually uses an asynchronous mechanism to render the report in order to return the first page quickly while continuing to render the additional pages in the background.
In the case of a subscription delivery, there is actually no HTTP Request that initiated the request.

Additionally, in the future we will more than likely be making additional portions of report execution asynchronous to improve both perceived latency as well as taking advantage of multi-core processors during non-peak load.

That said, there are a couple of scenarios I can think of where you might be tempted to examine the HTTP Request during report execution. These are:

You want to determine the path or location of the report, and you are doing so by examining the incoming request URL.
Your application is passing some value to the report via an HTTP header.

For each of these, there are built-in supported mechanisms. For (1), there is a set of values exposed by the global collections which allow to get information about the report server and the context report. For example, there are Globals.ReportFolder, Globals.ReportName, and Globals.ReportServerUrl. You can leverage these variables in report expressions rather than trying to tease apart the HTTP Request.

In the case of passing data to the report via an HTTP Header, there is already a built-in mechanism for passing data to a report execution. They are called report parameters. They are useful for more than just providing query parameters! You can use them to also pass application-specific information that some of your custom code may require.

So what is the moral of the story? Be very careful when accessing static objects that are request-specific in the context of your report via custom code. Doing so exposes you to potential back-compat issues as we continue to bring the platform forward. Wherever possible we try to expose enough context through the supported report processing and rendering object models, and if there are additions you need we would love to hear about them.

Scaling out the Viewer Control and rsExecutionNotFound

John Gallardo [MSFT] — Fri, 08 Aug 2008 19:26:14 GMT

One of the criteria that the report server uses to match the provided SessionID with a stored report is that the SessionID has to be provided by the same user that initially created the session. Usually, this is the case. Someone browses the report in IE, they click around to paginate or expand toggles, and things just work because they are the same user they were when they initially ran the report.

Sometimes though, things go wrong.

One way that this can happen is when you are hosting the ASP.Net Viewer Control in your own application and you are not impersonating the incoming user all the way to the backend report server. This is a totally supported configuration, however there is a little caveat that you have to keep in mind. Since the report server requires that the user names match across session retrievals, you have to ensure that the viewer control is impersonating the same user. Sounds easy, right? Well not so fast if you are using a machine specific account for your application pool which is hosting the viewer control. The specific topology that can get you into trouble is something like the following:

In this scenario you have the following:

Client machines accessing multiple web frontends hosting the report viewer control via a load balancer.
The web frontend machines are using a machine-specific account to communicate with the report server (for example they are using the builtin NETWORK SERVICE account).
You are not impersonating the incoming user in the viewer control when accessing the backend report server.

The sequence of operations that can lead to trouble are:

The initial request from ClientA is routed to MachineA.
The viewer control in MachineA instantiates a session with the report server. Since the $MachineA credentials are sent, the session is associated with this user. Keep in mind at this point the report server actually has no idea that there is some other logical user beyond the web frontend that is actually making the request.
The user views the first page of the report, and navigates to the second page.
The request for the second page is actually routed to MachineB by the load balancer.
The viewer control in MachineB attempts to load the user session, however this fails since the request to the report server is actually from $MachineB user and not the original $MachineA user. The report server generates an rsExecutionNotFound error and returns that, resulting in an error being displayed to the user.

There are a couple of ways to address this problem:

Architect your application such that you can flow credentials from the client all the way to the report server. -- Or --
Ensure that your web frontend nodes can impersonate the same user when accessing the report server regardless of which machine the request is routed to (so use something like domain credentials to access the report server).

Scaling Up: SSRS 2008 vs. SSRS 2005 (spoiler: 2008 wins)

John Gallardo [MSFT] — Thu, 10 Jul 2008 18:59:39 GMT

The SQL Customer Advisory Team just released a Technical Note comparing SQL Server Reporting Services 2008 vs. 2005 from a scale-up perspective. Its good to see that a lot of the work that we did over this release focusing on performance and scalability (across the board, from the core server/processing infrastructure to specific rendering extensions) has really paid off. You can read the entire article here:

http://sqlcat.com/technicalnotes/archive/2008/07/09/scaling-up-reporting-services-2008-vs-reporting-services-2005-lessons-learned.aspx

Quoting from the summary (emphasis is mine):

Reporting Services 2008 was able to respond to 3–4 times the total number of users and their requests on the same hardware without HTTP 503 Service Is Unavailable errors compared with Reporting Services 2005, regardless of the type of renderer. In stark contrast, Reporting Services 2005 generated excessive HTTP 503 Service Is Unavailable errors as the number of users and their requests increased, regardless of the report renderer.
Our tests clearly show that the new memory management architecture of the report server enables Reporting Services 2008 to scale very well, particularly on the new four-processor, quad-core processors. With our test workload, Reporting Services 2008 consistently outperformed SQL Server 2005 with the PDF and XLS renderers on the four-processor, quad-core hardware platform (16 cores) both in terms of response time and in terms of total throughput. Furthermore, with these renderers on this hardware platform, Reporting Services dramatically outperformed other hardware platforms regardless of Reporting Services version, responding to 3–5 times the number of requests than when running on either of the other hardware platforms. As a result, we recommend that you scale up to four-processor, quad-core servers for performance and scale out to a two-node deployment for high availability. Thereafter, as demand for more capacity occurs, add more four-processor, quad-core servers.

All those temporary files (RSTempFiles)

John Gallardo [MSFT] — Mon, 30 Jun 2008 21:04:34 GMT

When you install Reporting Services, we create a few directories:

LogFiles
ReportManager
ReportServer
RSTempFiles

Most of these are fairly self explanatory. LogFiles... well we put our log files in there. ReportManager contains the Report Manager application (what you get when you browse to http://<server>/reports) and ReportServer contains the Report Server application which is the proper 'Report Server' which implements our Web Service and HTTP handler endpoints... it is what you get when you go to http://<server>/reportserver.

So what about that RSTempFiles thing? Well it contains files that are temporary of course!

These temporary files can be broken down into a few categories:

Temporary Report Snapshot files.

These files are only created if you have opted into using temporary file storage (see the WebServiceUseFileShareStorage).
For RS2000/2005, snapshots are more or less completely independent and each is contained in its own directory (identified by a GUID). Each snapshot contains a set of files (internally, they are referred to as "chunks").
For RS2008, snapshots oftentimes contain some shared data. The folder-per-snapshot hierarchy used in RS2000/2005 is replaced by a single directory called "Chunks." Each chunk is a discrete file in this directory.
These files are automatically cleaned up by the Report Server on a time-based interval (same as snapshot data stored in ReportServer and ReportServerTempDB catalog databases).

Output/Intermediate Streams

These files are all created directly within the the RSTempFiles directory. There is no way to differentiate between these two sub-categories of files currently.
Output streams - these streams are generated as output from the renderers. The server spools them to disk if they grow large enough. RS has an output cache that may cause these streams to survive beyond the lifetime of the request so that subsequent accesses can be served directly from the cache.
Intermediate files - these files contain results of intermediate calculations during report processing and rendering. Generally, these contain data which is never returned to the client, but rather holds temporary results in order to relieve memory pressure. These files are cleaned up when the request completes (no caching across requests).

Conversion files

These are stored under a folder named _Conversion.
Our compression format for snapshots changed between RS2000 and RS2005. This folder would contain temporary files supporting the on-demand one-time upgrade of these persisted snapshots. Unless you are upgrading from an RS2000 instance, you should never see this directory or files created.

Shadow Copy Files

This is only for RS2008.
After the CTP6 release of RS2008, we enabled shadow copy for our ASP.Net worker domains under the new hosting model.

Joel on Martian Headsets (standards)

John Gallardo [MSFT] — Mon, 17 Mar 2008 23:00:38 GMT

I'm generally not a big fan of the "link dump" style blog. However, the latest post on Joel On Software regarding web standards was too good not to share.

Some choice quotes...

Regarding "working around" problems in an implementation (bolded by me):

And eventually some tedious bore writes a lengthy article on her blog explaining a trick you can use to make Qxyzrhjjjjukltk 5.0 behave just like FireQx 3.0 through taking advantage of a bug in Qxyzrhjjjjukltk 5.0 in which you trick Qxyzrhjjjjukltk into deciding that it’s raining when it’s snowing by melting a little bit of the snow, and it’s ridiculous, but everyone does it, because they have to solve the hasLayout incompatibility. Then the Qxyzrhjjjjukltk team fixes that bug in 6.0, and you’re screwed again, and you have to go find some new bug to exploit to make your windshield-wiper-equipped headphone work with either device.

Regarding language used in a lot of standards documents:

Those documents are super confusing. The specs are full of statements like “If a sibling block box (that does not float and is not absolutely positioned) follows the run-in box, the run-in box becomes the first inline box of the block box. A run-in cannot run in to a block that already starts with a run-in or that itself is a run-in.” Whenever I read things like that, I wonder how anyone correctly conforms to the spec.

That one I have a lot of sympathy for. In a previous life, I worked as a tester on the XML datatype implementation in SQL Server 2005. There was a quite a bit of time spent going through various W3C specs on XQuery, XPath, and XSD. IMO, the specs are insane. In some cases they go into excruciating detail about the internal semantics of a particular operation -- as an example the XQuery 1.0 Formal Semantics spec says that path expressions should have the same semantics as (ie, they should normalize to) the XQuery for statement. In other words, an expression like /foo/bar/baz should look like a set of nested loops. Unfortunately, it also defines that the normalization process of each sub-expression (a relative path expression) should return the results in document order. There are a couple of problems here (directly related to SQL Server's implementation). First of all, SQL Server uses static typing, and for breaks static typing because it implies zero-or-many cardinality. This breaks functions which require a singleton unless you really want to put (/foo/baz)[1] around everything (note if you are using untyped xml, you need to do this anyways -- sorry). AFAIK, SQL Server is not conformant in this regard (it has been a while though, so maybe I am missing something). The other big hangup I have is that the implication of document ordering at each step in the path seems to preclude the potential for a more global optimization where you defer sorting until the very last step.

One of the major takeaways that I had from Joel's post was that it is incredibly difficult to implement against a standard when that standard actually has no implementation. I don't know if it is possible to get around this. Some standards are amazingly complex -- I can't imagine there being a reference implementation which is so robust that it becomes the standard. Maybe if you go into the whole standards argument saying that the implementation _is_ the standard then you are better off, but I suppose that has its own problems as well.

More on SSRS 2008 memory management

John Gallardo [MSFT] — Wed, 12 Mar 2008 04:17:33 GMT

This is a continuation of a previous posting that I made which introduced some of the memory management functionality in SSRS 2008. In this post, I wanted to dig a bit more into the details of the system in order to illuminate some of the decisions that are happening under the covers.

In order to get the system into a memory constrained state, I have configured the server to only use ~256MB of memory. You can read about how to do this in this books online article entitled Configuring Available Memory for Report Server Applications. I also set the "appdomainmanager" trace level to 4, which is the "Verbose" setting. I wouldn't recommend having a production system run with this tracelevel since the memory management output can get pretty spammy when the system is under pressure and notifications are firing rapidly.

I then ran a pretty simple report that has 100K rows.

So lets dig into the log file a bit. The first couple of lines look like this:

appdomainmanager!DefaultDomain!aec!03/11/2008-16:56:17:: v VERBOSE: Received NotifyMemoryPressure(pressureLevel=MediumPressure, kBytesToFree=6056)
appdomainmanager!WindowsService_1!aec!03/11/2008-16:56:18:: v VERBOSE: Memory Statistics: 0 items, 0KB Audited, 0KB Freeable, 295544KB Private Bytes
appdomainmanager!DefaultDomain!aec!03/11/2008-16:56:18:: v VERBOSE: Appdomain (WindowsService) attempted to free 0 KB.
appdomainmanager!ReportServer_0-2!aec!03/11/2008-16:56:18:: v VERBOSE: Memory Statistics: 3 items, 83654KB Audited, 78534KB Freeable, 296900KB Private Bytes

This is basically telling the state of the current system. We have two worker appdomains (WindowsService and ReportServer). WindowsService is used for background processing (subscriptions) and the ReportServer appdomain handles all of the interactive requests from our HTTP endpoint (including all management operations such as publishing reports, creating folders, etc...) In my test, I wasn't running any subscriptions, and you can tell that there were "0 items" reported. However, I was running a single report interactively in the ReportServer appdomain. You can see there that we indicated that there were "3 items." What are these items? As you might remember from the previous post, there is not a direct 1:1 mapping between "memory consumers" and requests. Typically there are 3 "memory consumers" per request... this is a bit of an implementation detail about how out report processing and rendering engine divides up the large memory consuming components. Additionally, you can see that a total of ~84MB was discovered as being used by these consumers, of which ~79MB is considered freeable. We also report that we are currently using ~289MB private bytes.

appdomainmanager!DefaultDomain!aec!03/11/2008-16:56:18:: v VERBOSE: Appdomain (ReportServer) attempted to free 6056 KB.

This line is telling us that some consumer in the ReportServer domain agreed to try to release ~6MB of memory (which is equal to the amount we were trying to free in the previous request). Some additional time passes, and we recieve another memory notification.

appdomainmanager!DefaultDomain!aec!03/11/2008-16:56:19:: v VERBOSE: Received NotifyMemoryPressure(pressureLevel=HighPressure, kBytesToFree=25120)
appdomainmanager!WindowsService_1!aec!03/11/2008-16:56:19:: v VERBOSE: Skipping shrink request for appdomain (WindowsService_1) because no memory consuming requests are registered.
appdomainmanager!ReportServer_0-2!aec!03/11/2008-16:56:19:: v VERBOSE: 1057 ms has elapsed since last memory shrink attempt for this appdomain (ReportServer_0-2-128497526482384832)
appdomainmanager!ReportServer_0-2!aec!03/11/2008-16:56:19:: v VERBOSE: Memory Statistics: 3 items, 77765KB Audited, 72645KB Freeable, 313648KB Private Bytes
processing!ReportServer_0-2!aec!03/11/2008-16:56:19:: w WARN: Processing Scalability -- Memory Shrink Request Received
appdomainmanager!DefaultDomain!aec!03/11/2008-16:56:19:: v VERBOSE: Appdomain (ReportServer) attempted to free 25120 KB.

From this, we can determine that the original memory shrink was not aggressive enough, and there are other consumers which are still allocating memory. Note that the memory pressure is now "High." However, note that the audited memory has actually been reduced from the previous iteration. In general, the reason why this happens is that there are memory allocations that happen outside of the areas which are strictly controlled and audited (remember the CLR shares a single global heap for all code running in all appdomains). Since we are in a high pressure state, in addition to requesting the large consumers to reduce memory usage, we will also instruct all memory consumers to freeze their current allocation so they do not grow larger.

As time passes, the processing and rendering engines continue to work on the report, the log shows additional memory shrink notifications which I am not going to duplicate here. The majority of them look like this:

appdomainmanager!ReportServer_0-2!aec!03/11/2008-16:56:50:: v VERBOSE: Skipping shrink request for appdomain (ReportServer_0-2-128497526482384832) because no memory consuming requests have been added.

Indicating that we are not taking additional action because we have detected that no additional requests have been added to the system. Internally, we use a time based heuristic here to basically bail out of the shrink notification early if we believe that we are not going to do any productive activity.

Eventually, about 30 seconds after starting execution of the report, we finish rendering it, and the following line is output to the log file:

webserver!ReportServer_0-2!9d0!03/11/2008-16:56:52:: i INFO: Processed report. Report='/homeadvisor_rowparameters', Stream=''

Ok, well hopefully that has given a little bit of insight into the system that we are using, and shows how you can examine the log file to get some details into the decisions that are happening.

Where has John been?

John Gallardo [MSFT] — Tue, 11 Mar 2008 20:35:17 GMT

Ok, it has been a little while. But I have been pretty busy lately. His name is Christopher.

We have also been pretty busy locking down Katmai to get CTP6 (aka known as the "February CTP") out the door and working bugs. I recommend that people head over towards the "Katmai" forum on MSDN for Reporting Services if you have any questions. There are lots of people there willing to answer questions and otherwise help out.

So what now? I owe a part two post on some of the memory management stuff we are introducing in Reporting Services 2008. I'm getting all of the info put together now, and the post should be up shortly.

Memory Management in Reporting Services 2008

John Gallardo [MSFT] — Tue, 11 Dec 2007 01:14:19 GMT

One of the big pushes for SSRS 2008 has been to reduce the occurrence of OutOfMemoryExceptions caused during report execution. A lot of work has gone into making this happen throughout the report rendering stack, including significant changes in the report processing engine to move a definition based object model as opposed to an instance based one. I can't take the credit for this massive undertaking, but I did work on some of the supporting infrastructure to ensure that components of the report processing and rendering engines can make more efficient usage of the memory on the box while reducing the likelihood of OOM. This is probably going to be a multi-part discussion, with the first going over some of the internal infrastructure, and the next drilling more into specifics with examples of the memory monitoring component in action.

One of the upfront design decisions that we made was the processing and rendering engines would continue to be managed code. This decision essentially means that they continue to allocate memory from a shared pool -- the CLR managed heap. Ultimately, it is the managed heap which makes policy decisions on whether or not specific allocations are going to succeed or fail. From the perspective of our native hosting layer, SSRS 2008 only sees the managed heap as a single large pool of memory and there is no visibility into specific components, requests, or even appdomains. Given the current hosting interfaces for the CLR, this just is not a tractable problem at this point.

Given the design decision to continue to use managed code and thus a shared pool of memory, we opted to go for a two-pronged approach:

Implement/leverage a process-wide memory notification infrastructure to detect when we are approaching/experiencing memory pressure.
In response to this global notification, determine the specific memory consumers which should be trimmed.

The first was actually already done for us. SSRS 2008 builds its native hosting layer on top of shared infrastructure from the SQL Server engine which already has memory monitoring components in the form of Memory Broker and other resource monitoring technologies. These provided us with a configurable and predictive memory management infrastructure. This is the key first piece.

The second piece of the puzzle is attributing memory usage to specific consumers. You will note that I didn't say "requests" here. Internally, there is a managed infrastructure which specific components can leverage to report their memory usage and receive notifications as to how much memory they should try to free. This infrastructure is heavily utilized by our report processing and rendering engine to manage the size of data structures which grow as the amount of data in the report grows. This allows RS to strike a reasonable balance between memory utilization and stability.

Here is a rundown of the operations that occur when our native hosting layer detects memory pressure:

Native layer informs managed code that there is pressure and specifies the total amount of memory that must be shrunk.
Managed layer enumerates all memory consumers (generally 2-3 per report rendering request plus some global objects such as caches).
We determine the minimal subset of memory consumers that have to be trimmed in order to satisfy the shrink request.

Actually -- there is a a bit of special sauce here. Each component also gives the centralized memory manager information about "how difficult" it is to shrink its memory usage and this is factored into determining whom to shrink.

Step (3) is pretty actually interesting and is a key design decision for SSRS. An alternative approach would be to perform more of a "fair share" approach to trimming memory usage. The decision to minimize the number of components which are shrunk is really driven by the fact that a memory shrink operation is somewhat heavyweight. The vast majority of components which subscribe to these events store everything in memory up until the point they receive the notification. In general, these consumers are things like lists and dictionaries which are required to keep track of things that are put into them -- they can't just evict entries like a cache may be able to. So in order to begin to use less memory, these data structures have to fall back to a mixed disk/memory mode which requires serializing a portion of their state to disk. In order to keep things running as fast as possible when we are not under memory pressure, all of this serialization happens when they receive the shrink notification. So you can imagine what would happen if we went with a fair share approach where every request is asked to trim some memory. Each request currently being processed would grind to a halt as they race to serialize their state to disk. Instead, with our approach what happens is a small fraction of requests (usually the big memory hogs) are temporarily suspended while they serialize some state to disk, and other requests are to a large degree unaffected.

Ensuring up to date results through URL Access

John Gallardo [MSFT] — Wed, 24 Oct 2007 21:04:56 GMT

A pretty common pattern that I see exporting of reports through URL Access using a simple hyperlink. The hyperlink usually looks something like this:

<a href="http://localhost/reportserver/?/SalesData&rs:Command=Render&rs:Format=EXCEL">Sales Data in Excel Format</a>

At first glance, it doesn't appear that there is anything wrong with this. However, if you are working in an environment where the data is highly volatile and the user needs to click on the link multiple times and see up to date information then you could experience some problems. To understand why you encountering problems, you need to first understand that implicitly when you access a report through the Report Server, you are estabilishing a "session" with the server. Fundamentally a session is a binding between a user, the report, and the data in that report. We use session to ensure that when you navigate between pages and otherwise interact with the report that the data within the report remains consistent.

Ok, so what does this have to do with the URL above? Well, when a user clicks on that URL, we create a session for the user, and pass back a cookie their web browser which will identifier that session later on to the server so we can find the correct instance of the report with the data that was in it. If the user, within that same IE session, clicks on the report again, and assuming the session hasn't timed out (10 minutes by default), we actually render the report out of the cached data that has been bound to that user session. I know -- disaster, right? The user was expecting to see that billion dollar deal that they just closed and instead they are just reminded that they haven't meant the numbers for October yet.

So what do we do about it? Well, there is actually an rs:* parameter which tells the server "Hey, I know that you already have a session created for this user, but forget about it for right now, and create a new one", that parameter is rs:ClearSession=true. By providing the rs:ClearSession=true parameter along with the request to the report server, you are ensuring that the server constructs a new session for the user and runs the report from a clean state, ensuring (assuming you have the report configured to use live execution) that the user will see the most up to date information possible. The nice thing about the rs:ClearSession=true parameter is that if there is not already a session estabilished, it is a no-op.

Back from a blogging vacation?

John Gallardo [MSFT] — Tue, 11 Sep 2007 21:35:08 GMT

I guess it was an unintended vacation. We've been busy here in the Reporting Services team. CTP5 of SQL Server 2008 was a large milestone for us as it really started to put together all of the work we have been doing to make the processing engine more scalable as well as nail down some of the hard issues with our new native hosting layer. I know you can't get your hands on it quite yet, but for the most part the major development work has been completed except for the setup and upgrade bits.

Speaking of our native hosting layer, James Wu has just started a blog. James did a lot (all?) of the initial prototyping work for our new native hosting infrastructure including integration with SQLOS and other technologies from the SQL Engine team. Hopefully over the coming months James will provide a good bit of information about the native hosting layer and its impact on users, and maybe even a good story or two about the development process.

SQL Server 2008 (Katmai) and Reporting Services

John Gallardo [MSFT] — Mon, 04 Jun 2007 23:59:22 GMT

Today the first public release of SQL Server 2008 is shipping. Of course, Reporting Services is still there in the box. You can find general information about the release here:

https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395&wa=wsignin1.0

There are also some Katmai specific MSDN forums, and a Katmai Reporting Services MSDN forum.

EMF Rendering and Persisted Streams

John Gallardo [MSFT] — Fri, 25 May 2007 03:49:04 GMT

EMF rendering in Reporting Services has an interesting history. When SSRS first shipped, we did not support "direct printing" from within the web interface. Customers revolted. They wanted a "Print" button on the toolbar which would provide a full featured print experience. Many other web applications don't actually do this, choosing instead to present the user with a "printable" HTML page or some other format such as PDF which they then print within their PDF reader. Rather than this workaround though, it was decided that we would implement an EMF renderer and provide a client-side ActiveX control which would send the EMF to the print spooler.

Turns out EMF has an interesting characteristic though. Each page is a discrete stream. Normally, this wouldn't cause a problem, but due to how renderers were architected, the cost restarting the renderer on a specific page is a function of which page it is within the report. Specifically, when you request page N of the report from the EMF renderer, it needs to find page N, which means essentially internally walking across pages 1...N-1 in order to position itself on page N. A quick back of the envelope calculation will tell you then that the cost of printing a report by requesting each page independently has an algorithmic complexity of

Where R(i) is the time to independently render a page, and T(i) is the time it takes to "paginate through" a given page without actually writing anything to the response stream. This is not good. Adjusting the way that the EMF renderer operated would require too much of a redesign to our processing and rendering architecture, so it was decided to instead address this issue by adding a new feature to the server. This feature was "Persisted Streams." When this mode is enabled, all of the requested streams are rendered in one pass from the EMF renderer so we don't incur the cost of restarting the renderer on a particular page. The server returns the initial request when the first page is completed, and continues in the background to collect pages from the renderer and store them until they are requested by the client. From the clients perspective, you merely keep requesting pages until the server returns an empty stream, which indicates that the report has finished rendering. There are a couple of limitations to using the Persisted Streams feature though:

It is only accessible via the URL Access endpoint, and not via our SOAP APIs. Unfortunately, this is what happens when features are implemented in a service pack. It was only needed for a very narrow scenario, so from an implementation perspective we did not choose to incur the cost of making it available to our SOAP API.
It is kind of a "stateful" API, meaning that once you have read a stream, you cannot read it again. So make sure you get it right the first time because you can't go back.

Here is some sample code that demonstrates how use our URL Access endpoint to retrieve streams in this fashion:

            string requestUri =
                string.Format("https://{0}/reportserver?{1}&rs:Command=Render&rs:Format=IMAGE&rc:OutputFormat=EMF",
                m_serverName, m_reportPath);

            const string persistStreams = "&rs:PersistStreams=true";
            const string getNextStream = "&rs:GetNextStream=true";
            CookieContainer cookies = new CookieContainer();
            WebRequest request = WebRequest.Create(requestUri + persistStreams);
            (request as HttpWebRequest).CookieContainer = cookies;
            try
            {
                while (true)
                {
                    request.UseDefaultCredentials = true;
                    
                    using (WebResponse response = request.GetResponse())
                    {
                        Stream s = response.GetResponseStream();
                        BufferedStream buffered = new BufferedStream(s);
                        if (OnReceivedStream(buffered))
                        {
                            break;
                        }

                        request = WebRequest.Create(requestUri + getNextStream);
                        (request as HttpWebRequest).CookieContainer = cookies;
                    }
                }
            }
            catch (WebException ex)
            {                
                OnReceivedException(ex);
            }

Obviously, this code is not 100% complete. It is part of a larger piece of a WinForms application that I have written which excercises this functionality. It should be enough to get you started though.

Diagnosing Reporting Services Issues

John Gallardo [MSFT] — Thu, 29 Mar 2007 19:36:33 GMT

I was poking around some other SSRS blogs from folks here on the product team, and I found this post from Lukasz about troubleshooting Reporting Services. It is a great overview of some of the guidance we have given in the form of whitepapers, KB artciles, and a bunch of other sources. I highly recommend you give it a read and bookmark it so you can use it is your "playbook" when running into problems.

ReportServerTempDB IO Saturation

John Gallardo [MSFT] — Wed, 21 Mar 2007 21:09:02 GMT

Reporting Services uses a temporary database for storage of objects which are, well, temporary. For example, report snapshots which are associated with a particular user session as the result of a live execution. Cached report snapshots are also stored here because they begin life as a result of a live report execution. This means that in systems where there are a lot of live report executions happening, we are churning through a lot of data in the ReportServerTempDB. As your system scales out or up, you will undoubtedly experience a problem where you will begin to saturate the IO subsystem hosting the ReportServerTempDB.

Do I have an IO throughput issue?

There are lots of really great articles written about this topic, but in general there are a couple of things which I look at:

Perfmon counters for Physical Disk Usage:
- Avg. Disk Sec/Read
- Avg. Disk Sec/Write
Check SQL Server Wait types
- sys.dm_os_wait_stats
- Look for high counts of wait stats like IO_COMPLETION, ASYNC_IO_COMPLETION, and the PAGELATCH_* class of waits

Ideally, you want to see Avg Disk Sec/Read or Write to be < 10ms. If you start seeing these counters creep over 50ms, then you are probably getting into a situation where the disk is not keeping up with the incoming rate of IO requests and this is causing waits.

So how do I deal with it?

The answer is pretty straightforward. You start adding more disks to handle the IO load on the ReportServerTempDB. Doing this is actually pretty easy. From within Management Studio, find the ReportServerTempDB database in the Object Explorer. Right click and go to properties. From the tabs on the left side of the Database Properties dialog, you will see a "Files" section, go to that guy and start adding files. Of course, you will want to spread the files across multiple disks if you are trying to increase IO performance. Additionally, I would recommend sizing the files to an appropriate value rather than relying on autogrowth since autogrowth can cause performance issues when SQL Server actually has to grow the files. The exact value will be a function of how much data is incoming. An important fact to keep in mind though is that the ReportServerTempDB database should automatically remove "stale" data as user sessions and cached reports expire, so the data shouldn't in general continuously grow.

Once you have created the additional data files, SQL Server will automatically begin to load balance IO operations across the various files, so there shouldn't be anything else you have to do. Nice!

Aggregating rsExecutionNotFound Posts

John Gallardo [MSFT] — Thu, 01 Feb 2007 20:34:00 GMT

The posts about things dealing with rsExecutionNotFound seem to be what people find most interesting these days. In order to aggregate the posts into one place, I will be adding links that deal with this error here.

Maybe I will add a tag as well...

Careful when using rc:Toolbar=false

John Gallardo [MSFT] — Thu, 01 Feb 2007 02:47:00 GMT

I'm in the middle of reinstalling Visual Studio, and so I thought I would share an interesting story from a couple of weeks ago. An internal customer (Microsoft-speak for some other team at Microsoft) of Reporting Services was encountering seemingly random rsExecutionNotFound errors when navigating around their application. Of course, things like this are never random and there was a perfectly good explanation. After digging through their application for a little while, and with the help of Fiddler, I stumbled upon the problem. They were rendering the same report in two different contexts, one of them was specifying rc:Toolbar=false, and the other wasn't. Basically, their call to the report server looked something like this in the two places:

http://reports/reportserver/?/myreport&rs:Command=Render&rc:Toolbar=false

http://reports/reportserver/?/myreport&rs:Command=Render

Now, there is actually a subtle little problem with rendering using rc:Toolbar=false against the ReportServer virtual directory. When this happens in a default installation, the Report Server sends a cookie back to IE to identify the session. This cookie is then passed back to the report server to load up the appropriate snapshot, parameter, data source information, etc to view subsequent pages and images from the report. What would happen is the user would navigate to the page where the report being rendered w/rc:Toolbar=false was sitting, which would give IE the cookie. The user would navigate away from the page, and eventually head to the page where rc:Toolbar=false was not specified. However, even in this case if the cookie is there, we try to use it to load the user session, which would fail because the session had timed out in the time between the two requests. The user would be greeted with an "Execution Not Found" error message and would have to close and restart IE to get things working again.

The moral? Don't render against the ReportServer virtual directory using rc:Toolbar=false unless you really know what you are doing, because it is tricky to get right. If you want to render using the ReportServer virutal directory (so you don't have to roll your own Report Viewer Control ASP.Net application) then you should build a stylesheet which removes the toolbar, and use that.

Mono device makes good -- wins award

John Gallardo [MSFT] — Thu, 18 Jan 2007 09:04:59 GMT

From Miguel de Icaza's blog:

http://tirania.org/blog/archive/2007/Jan-17.html

I had no idea that the Sansa was running Mono. Personally, I think it is really great that there are multiple implementations of the CLR. It is even cooler that a Mono-based device is being so highly praised.

Good job guys.

Load Testing Reporting Services

John Gallardo [MSFT] — Tue, 16 Jan 2007 04:52:21 GMT

This question came across one of our internal discussion aliases, and it wasn't the first time I had seen it... so I thought it might be interesting to other people as well.

Oftentimes, when people configure a load testing tool they set it up so that all of the connections come from the same logical user. However, in Reporting Services we have a feature which prevents an authenticated user from performing a denial-of-service attack against the server. We do this by limiting the number of operations that any given user (based on the user's name) can be performing at any given time. When you hit this limit, you will get an error message like this:

There are currently too many requests in progress for user: "ABC\XYZ". Please wait until the current requests have finished processing before issuing any more.

At this point, there are basically two ways of resolving this issue:

Change your test scripts/application to spread the requests across multiple logical users.
Alter the rsReportServer.config file to extend the number of requests a user can have active. The setting is the <Configuration> section and it is the "MaxActiveReqForOneUser" property. The whole entry is this: <Add Key="MaxActiveReqForOneUser" Value="999999" />.

Note that this setting can also be useful if your users are not talking directly to the server, but are instead going through an ASP.Net application which is always impersonating the same user when accessing SSRS.