Joe Chang : Reporting Serviceshttp://sqlblog.com/blogs/joe_chang/archive/tags/Reporting+Services/default.aspxTags: Reporting ServicesenCommunityServer 2.1 SP2 (Build: 61129.1)Reporting Services and the WriteChunkPortion stored procedurehttp://sqlblog.com/blogs/joe_chang/archive/2008/08/05/anyone-know-what-controls-writechunkportion.aspxTue, 05 Aug 2008 20:27:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:8212jchang2http://sqlblog.com/blogs/joe_chang/comments/8212.aspxhttp://sqlblog.com/blogs/joe_chang/commentrss.aspx?PostID=8212<P>Reporting Services (2005) uses the ReportServerTempDb database to store cached reports and temporary snapshots.&nbsp;Two of the stored procedure calls I see are WriteChunkPortion and ReadChunkPortion. Because I see that the MS ASP Session&nbsp;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.</P>
<P>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.</P>
<P>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.&nbsp;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&nbsp;that only a small piece&nbsp;might be&nbsp;read with each call. Just get the whole darn Chunk Content, and parse it out on the&nbsp;Report Server&nbsp;side!</P>
<P>The performance concern or impact is that when a 20KB RPC call is sent to a remote SQL Server, the network&nbsp;traffic&nbsp;is&nbsp;normally split into&nbsp;multiple&nbsp;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&nbsp;on&nbsp;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),&nbsp;I am inclined to think&nbsp;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.</P>
<P>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.</P><img src="http://sqlblog.com/aggbug.aspx?PostID=8212" width="1" height="1">PerformanceReporting Services