Optimizing SSRS Rendering and Performance

I often run into the situation of SSRS taking too long to render or a long delay before processing starts. Here are 3 links provided by Microsoft that might help in solving those inefficiencies.

Troubleshooting Reports: Report Performance

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

Exporting Reports

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

 

Understanding Rendering Behaviors

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

 

 

 

First StreamInsight attempt using Performance Counter

As my first attempt with StreamInsight, I decided to use the Performance Counter as my stream of data. Here is a brief overview of implementing the StreamInsight portion

3 important downloads to get started, with StreamInsight:

Download StreamInsight: http://www.microsoft.com/en-us/download/details.aspx?id=26720

Download Reactive: http://www.microsoft.com/en-us/download/confirmation.aspx?id=28568
Download Examples: http://streaminsight.codeplex.com/releases/view/90143


First I created an class to represent the data I wanted to capture in my stream. Not all data types can be used with StreamInsight (ie: enum). Here is a list of supported data types: http://msdn.microsoft.com/en-us/library/ee378905.aspx

Code Snippet

205. // Input events of CounterSample of supported data types
206. public class StreamableCounterSample
207. {
208.     public float CpuUtilization { get; set; }
209.     public long TimeStamp { get; set; }
210. }

The main portion of the program.
Here starting on line 86, I create my StreamInsight server embedded in memory. The server name is the instance name that you give after installing StreamInsight. With StreamInsight 2.1, they added IQStreamable which allows you to query the stream. Lines 104 & 105 is when the stream will be queried for the results.

Code Snippet

81. private float CalculatePerformance(BackgroundWorker worker, DoWorkEventArgs e)
82. {
83.     CreateCounters();
84.  
85.     //embedded (in-memory)
86.     using (Server server = Server.Create("StreamInsight21"))
87.     {
88.         Microsoft.ComplexEventProcessing.Application application = server.CreateApplication("app");
89.  
90.         //A query for reading events from a stream
91.         IQStreamable<StreamableCounterSample> inputStream = null;
92.  
93.         inputStream = CreateStream(application);
94.  
95.         while (true)
96.         {
97.             if (worker.CancellationPending)
98.             {
99.                 e.Cancel = true;
100.                 break;
101.             }
102.             else
103.             {
104.                 perf = inputStream.ToObservable().ToEnumerable().Last().CpuUtilization;
105.                 timestamp = DateTime.FromFileTime(inputStream.ToObservable().ToEnumerable().Last().TimeStamp);
106.                 worker.ReportProgress((int)perf);
107.             }
108.         }
109.  
110.         return inputStream.ToObservable().ToEnumerable().Last().CpuUtilization;
111.  
112.     }
113. }

When creating the stream in line 93, I'm setting up my observation. In this case CollectSamples is my source which is then converted to a temporal stream via ToPointStreamable, which inserts a single event instance with a datetime.

Code Snippet

150. static IQStreamable<StreamableCounterSample> CreateStream(Microsoft.ComplexEventProcessing.Application application)
151. {
152.     // Live data uses IQbservable<>
153.     return
154.         application.DefineObservable(() => CollectSamples()).ToPointStreamable(
155.         r => PointEvent<StreamableCounterSample>.CreateInsert(DateTime.Now, r),
156.         AdvanceTimeSettings.StrictlyIncreasingStartTime);
157. }

The collect samples, is where my data will be pulled. In this case I'm pulling my data from the performance counter, but returning an observable interval representing this temporal event that occurs every 1 second.

Code Snippet

137. private static IObservable<StreamableCounterSample> CollectSamples()
138. {
139.     List<StreamableCounterSample> data = new List<StreamableCounterSample>();
140.     
141.     data.Add(new StreamableCounterSample
142.     {
143.         CpuUtilization = perfCounter.NextValue(),
144.         TimeStamp = DateTime.Now.ToFileTime()
145.     });
146.  
147.     return ToObservableInterval(data, TimeSpan.FromMilliseconds(1000), Scheduler.ThreadPool);
148. }

Code Snippet

121. private static IObservable ToObservableInterval(IEnumerable source, TimeSpan period, IScheduler scheduler)
122. {
123.     return Observable.Using(
124.         () => source.GetEnumerator(),
125.         it => Observable.Generate(
126.             default(object),
127.             _ => it.MoveNext(),
128.             _ => _,
129.             _ =>
130.             {
131.                 //Console.WriteLine("Input {0}", it.Current);
132.                 return it.Current;
133.             },
134.             _ => period, scheduler));
135. }

In the end, the final product is a simple winform showing the current CPU Utilization and the last time it ran.

The program can be downloaded from:

View overall I/O statistics for all databases in your server

A sql script to find the I/O statistics for all databases on the server. This can be helpful for

"drafting a server consolidation strategy, or pointing to eventual need to check perf counters if I/O is waiting more than expected"

- From "Blog do Ezequiel" http://blogs.msdn.com/b/blogdoezequiel/archive/2012/03/08/the-sql-swiss-army-knife-3-view-i-o-per-file-updated.aspx

Code Snippet

-- 2012-03-08 Pedro Lopes (Microsoft) pedro.lopes@microsoft.com (http://blogs.msdn.com/b/blogdoezequiel/)
--
-- Checks the cumulative IO per database file and related information
--
SELECT f.database_id, DB_NAME(f.database_id) AS database_name, f.name AS logical_file_name, f.[file_id], f.type_desc,
    CAST (CASE
        -- Handle UNC paths (e.g. '\\fileserver\readonlydbs\dept_dw.ndf')
        WHEN LEFT (LTRIM (f.physical_name), 2) = '\\'
            THEN LEFT (LTRIM (f.physical_name),CHARINDEX('\',LTRIM(f.physical_name),CHARINDEX('\',LTRIM(f.physical_name), 3) + 1) - 1)
            -- Handle local paths (e.g. 'C:\Program Files\...\master.mdf')
            WHEN CHARINDEX('\', LTRIM(f.physical_name), 3) > 0
            THEN UPPER(LEFT(LTRIM(f.physical_name), CHARINDEX ('\', LTRIM(f.physical_name), 3) - 1))
        ELSE f.physical_name
    END AS NVARCHAR(255)) AS logical_disk,
    fs.size_on_disk_bytes/1024/1024 AS size_on_disk_Mbytes,
    fs.num_of_reads, fs.num_of_writes,
    fs.num_of_bytes_read/1024/1024 AS num_of_Mbytes_read,
    fs.num_of_bytes_written/1024/1024 AS num_of_Mbytes_written,
    fs.io_stall/1000/60 AS io_stall_min,
    fs.io_stall_read_ms/1000/60 AS io_stall_read_min,
    fs.io_stall_write_ms/1000/60 AS io_stall_write_min,
    ((fs.io_stall_read_ms/1000/60)*100)/(CASE WHEN fs.io_stall/1000/60 = 0 THEN 1 ELSE fs.io_stall/1000/60 END) AS io_stall_read_pct,
    ((fs.io_stall_write_ms/1000/60)*100)/(CASE WHEN fs.io_stall/1000/60 = 0 THEN 1 ELSE fs.io_stall/1000/60 END) AS io_stall_write_pct,
    ABS((sample_ms/1000)/60/60) AS 'sample_HH',
    ((fs.io_stall/1000/60)*100)/(ABS((sample_ms/1000)/60))AS 'io_stall_pct_of_overall_sample'
FROM sys.dm_io_virtual_file_stats (default, default) AS fs
INNER JOIN sys.master_files AS f ON fs.database_id = f.database_id AND fs.[file_id] = f.[file_id]
ORDER BY 18 DESC

Concat strings in Javascript performance.

An interesting post to read: http://blogs.msdn.com/jscript/archive/2007/10/17/performance-issues-with-string-concatenation-in-jscript.aspx

So basically they are fixing the performance speed it takes to concat strings in javascript for the next release of IE. The improvement is very significant.

Optimizing SQL Server CPU Performance

http://www.microsoft.com/technet/technetmag/issues/2007/10/SQLCPU/default.aspx

Some interesting FYI facts from the article:

• In performance value: high-end dual-core processor > RAM > fibre optics > disk drive
• A data page in SQL Server is 8KB.
• An extent in SQL Server is made up of eight 8KB pages, making it equivalent to 64KB.
• Pulling a data page that is already cached from the buffer pool, at peak performance, should take under half a millisecond; retrieving a single extent from disk should take between 2 and 4 milliseconds in an optimal environment.

Check CPU utilization with PerfMon by monitoring: % Processor Time (<80%),>

Some optimizations are:
Query plan reuse
Reducing compiles and recompiles
Sort operations
Improper joins
Missing indexes
Table/index scans
Function usage in SELECT and WHERE clauses
Multithreaded operation

Resources for information on query plan reuse:
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 (microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx)
Optimizing SQL Server Stored Procedures to Avoid Recompiles (sql-server-performance.com/rd_optimizing_sp_recompiles.asp)
Query Recompilation in SQL Server 2000 (msdn2.microsoft.com/aa902682.aspx)