THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Joe Chang

Reporting Services and the WriteChunkPortion stored procedure

Reporting Services (2005) uses the ReportServerTempDb database to store cached reports and temporary snapshots. Two of the stored procedure calls I see are WriteChunkPortion and ReadChunkPortion. Because I see that the MS ASP Session state manager also use the WriteChunkPortion call, and the parameter values, I am inclined to think these particular calls when made from Report Server are temporary snapshots. There is an option to use the file system instead of a database. There is not an option to not save snapshots.

The main SQL statement in WriteChunkPortion is UPDATETEXT, and in ReadChunkPortion is READTEXT, with parameters for the ChunkPointer, offsets, and lengths, and the binary data itself. What I am seeing in Profiler is a series of calls from a given spid to WriteChunkPortion with the same ChunkPointer parameter value, but different Chunk Content values. The binary Content field might be up to 40KB in length with an average of 20KN, but this is probably dependent on the report particulars. At a different point in time. I see a series of calls from a given spid to ReadChunkPortion with the same ChunkPointer value, but different offsets and lengths. The lengths might range from 8 to 20000 bytes.

The series of WriteChunkPortion calls with a fixed ChunkPointer indicates that each Content value is overwritten without ever being read. If it is going to be overwritten, then don't both making the write in the first place. If the server fails, I will report the entire report again, I don't need to save a snapshot. My suspicion is that a large report will process much faster without making so many WriteChunkPortion calls. When the ReadChunkPortion series is called, I think it is silly that only a small piece might be read with each call. Just get the whole darn Chunk Content, and parse it out on the Report Server side!

The performance concern or impact is that when a 20KB RPC call is sent to a remote SQL Server, the network traffic is normally split into multiple ethernet packets of maximum lenght 1500 bytes, meaning 12+ packets are required for 20KB. The TCP/IP protocal has provisions for how many packets can be sent before an acknowledgement is required. For some reason, in most Windows environment, this TCP window is 2, even though this appears to be highly suboptimal. A while ago, I experimented on the Windows TCP/IP parameter settings with highly erractic results. Anyway, because of the relatively high volume of WriteChunkPortion calls between ReportServer and the database server (80 RPC/sec, 1K+ network packets), I am inclined to think that really fast network turn-around is important. That is, send one stream of packets with whole RPC before requiring an acknowledgement, not send two packets, wait for acknowledgement, only then send the next two and so on. Also good would be large internet packets or jumbo frames, but very few people enable this.

Now network scalability is a major subject with the MS Windows OS team, just look at all the sessions on this topic presented at WinHEC. Some time ago, MS released the Scalable Network Pack, which then incorporated into Windows Server 2003 service pack 2. Recently, I noticed several KB articles talking about problems with SNP and the Broadcom 5708 gE controller. This controller just happens to be embedded on recent generation Dell and HP servers. So MS finally gave up and issued a hotfix turning off SNP by default. I suppose this means SNP still works with the Intel gE controller. Unfortunately, few people buy the Intel adapter to bypass on the onboard Broadcom for this capability.

Published Tuesday, August 05, 2008 5:27 PM by jchang

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

I'm fairly certain those stored procedures have to do with SSRS's caching mechanisms.  Turn off report caching and you can turn off those calls.

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

August 5, 2008 11:22 PM
 

jchang said:

Unfortunately, because the app generates reporting dynamically, I cannot turn off caching through Report Manager. Some else has suggested this might also be RS session state management, as the MS web server session state management makes a similar call to its DB. Apparently, there are now MS reports that fixes many serious performance problems in SSRS 2005, it would have been nice to know that there were problems earlier. I will update as soon as I can run new tests

August 8, 2008 9:48 PM

Leave a Comment

(required) 
(required) 
Submit

About jchang

Reverse engineering the SQL Server Cost Based Optimizer (Query Optimizer), NUMA System Architecture, performance tools developer - SQL ExecStats, mucking with the data distribution statistics histogram - decoding STATS_STREAM, Parallel Execution plans, microprocessors, SSD, HDD, SAN, storage performance, performance modeling and prediction, database architecture, SQL Server engine

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement