DCSIMG
המידע שקיבלת במסגרת בלוג זה הינו מידע כללי בלבד ואין לראות ו\או להסתמך על מידע כאמור כייעוץ ו\או תחליף לייעוץ מכל סוג שהוא ו\או להסתמך עליו לעניין כלשהוא. April 2011 - Posts - Business Intelligence,Technology, Thoughts, Thinking
Sign in | Join | Help

April 2011 - Posts

Server Management Report Samples

Published at Apr 30 2011, 08:32 AM by Ronen Chenn

The Server Management sample reports include two sets of reports:

it can be found here

Server Management reports are a set of predefined report definition files that use SQL Server metadata as a source of information for reports. You can use the sample reports to view server information or as templates for designing new reports.

  • Execution Log sample reports includes predefined report files, sample database scripts, and a sample SQL Server Integration Services package that you can use to view execution log data for a report server.
Important:
SQL Server samples and sample databases must be downloaded and installed before you can view or work with them. For more information, see Considerations for Installing SQL Server Samples and Sample Databases.

Configuring Available Memory for Report Server Applications

Published at Apr 27 2011, 07:11 PM by Ronen Chenn
Although Reporting Services can use all available memory, you can override default behavior by configuring an upper limit on the total amount of memory resources that are allocated to Reporting Services server applications. You can also set thresholds that cause the report server to change how it prioritizes and processes requests depending on whether it is under low, medium, or heavy memory pressure. At low levels of memory pressure, the report server responds by giving a slightly higher priority to interactive or on-demand report processing. At high levels of memory pressure, the report server uses multiple techniques to remain operational using the limited resources available to it.

This topic describes the configuration settings that you can specify and how the server responds when memory pressure becomes a factor in processing requests.

Memory Management Policies

Reporting Services responds to system resource constraints by adjusting the amount of memory that is allocated to specific applications and types of processing requests. Applications that run in the Report Server service and that are subject to memory management include:

  • Report Manager, a Web front-end application for the report server.

  • Report Server Web service, used for interactive report processing and on-demand requests.

  • A background processing application, used for scheduled report processing, subscription delivery, and database maintenance.

Memory management policies apply to the Report Server service as a whole, and not to individual applications that run within the process.

If there is no memory pressure on the system, each server application requests some memory at startup, in advance of receiving requests, to deliver optimum performance when requests are eventually received. As memory pressure builds, the report server adjusts its process model as described in the following table.

Memory pressure

Server response

Low

Current requests continue to process. New requests are almost always accepted. Requests that are directed to the background processing application are given a lower priority than requests directed to the Report Server Web service.

Medium

Current requests continue to process. New requests might be accepted. Requests that are directed to the background processing application are given a lower priority than requests directed to the Report Server Web service. Memory allocations for all three server applications are reduced, with relatively larger reductions to background processing to make more memory available for Web service requests.

High

Memory allocation is further reduced. Server applications that request more memory are denied. Current requests are slowed down and take longer to complete. New requests are not accepted. The report server swaps in-memory data files to disk.

If memory constraints become severe and there is no memory available to handle new requests, the report server will return an HTTP 503 server unavailable error while current requests are completing. In some cases, the application domains might be recycled to immediately reduce memory pressure.

Although you cannot customize the report server responses to different memory pressure scenarios, you can specify configuration settings that define the boundaries that separate high, medium, and low memory pressure responses.

When to Customize Memory Management Settings

The default settings specify unequal ranges for low, medium, and high memory pressure. By default, the low memory pressure zone is larger than the zones for medium and high memory pressure. This configuration is optimum for processing loads that are evenly distributed or that grow or decline incrementally. In this scenario, the transition between zones is gradual and the report server has time to adjust its response.

Modifying the default settings is useful if the load pattern includes spikes. When there are sudden spikes in the processing load, the report server might go from no memory pressure to memory allocation failures very quickly. This might occur if you have multiple concurrent instances of a memory-intensive report that start at the same time. To handle this type of processing load, you want the report server to move into a medium or high memory pressure response as soon as possible so that it can slow down processing. This allows more requests to complete. To do this, you should lower the value for MemorySafetyMargin to make the low memory pressure zone smaller relative to the other zones. Doing so will cause responses for medium and high memory pressure to occur earlier.

Configuration Settings for Memory Management

Configuration settings that control memory allocation for the report server include WorkingSetMaximum, WorkingSetMinimum, MemorySafetyMargin, and MemoryThreshold.

  • WorkingSetMaximum and WorkingSetMinimum define the range of available memory. You can configure these settings to set a range of available memory for the report server applications. This can be useful if you are hosting multiple applications on the same computer and you determine that the report server is using a disproportionate amount of system resources relative to other applications on the same computer.

  • MemorySafetyMargin and MemoryThreshold set the boundaries for low, medium, and high levels of memory pressure. For each state, Reporting Services takes corrective action to ensure report processing and other requests are handled appropriately relative to the amount of memory that is available on the computer. You can specify configuration settings that determine the delineation between low, high, and medium pressure levels.

    Although you can change the configuration settings, doing so will not improve report processing performance. Changing the configuration settings is useful only if requests are getting dropped before they complete. The best way to improve server performance is to deploy the report server or individual report server applications on dedicated computers. For more information, see Planning a Deployment Topology.

The following illustration shows how the settings are used together to distinguish between low, medium, and high levels of memory pressure:

Configuration settings for memory state

The following table describes WorkingSetMaximum, WorkingSetMinimum, MemorySafetyMargin, and MemoryThreshold settings. Configuration settings are specified in the RSReportServer.config file.

Element

Description

WorkingSetMaximum

Specifies a memory threshold after which no new memory allocations requests are granted to report server applications.

By default, the report server sets WorkingSetMaximum to the amount of available memory on the computer. This value is detected when the service starts.

This setting does not appear in the RSReportServer.config file unless you add it manually. If you want the report server to use less memory, you can modify the RSReportServer.config file and add the element and value. Valid values range from 0 to maximum integer. This value is expressed in kilobytes.

When the value for WorkingSetMaximum is reached, the report server does not accept new requests. Requests that are currently in progress are allowed to complete. New requests are accepted only when memory use falls below the value specified through WorkingSetMaximum.

If existing requests continue to consume additional memory after the WorkingSetMaximum value has been reached, all report server application domains will be recycled. For more information, see Application Domains for Report Server Applications.

WorkingSetMinimum

Specifies a lower limit for resource consumption; the report server will not release memory if overall memory use is below this limit.

By default, the value is calculated at service startup. The calculation is that the initial memory allocation request is for 60 percent of WorkingSetMaximum.

This setting does not appear in the RSReportServer.config file unless you add it manually. If you want to customize this value, you must add the WorkingSetMinimum element to the RSReportServer.config file. Valid values range from 0 to maximum integer. This value is expressed in kilobytes..

MemoryThreshold

Specifies a percentage of WorkingSetMaximum that defines the boundary between high and medium pressure scenarios. If report server memory use reaches this value, the report server slows down request processing and changes the amount of memory allocated to different server applications. The default value is 90. This value should be greater than the value set for MemorySafetyMargin.

MemorySafetyMargin

Specifies a percentage of WorkingSetMaximum that defines the boundary between medium and low pressure scenarios. This value is the percentage of available memory that is reserved for the system and cannot be used for report server operations. The default value is 80.
NoteNote

MemoryLimit and MaximumMemoryLimit settings are obsolete in SQL Server 2008. If you upgraded an existing installation or using an RSReportServer.config file that includes those settings, the report server no longer reads those values.

Example of Memory Configuration Settings

The following example shows the configuration settings for a report server computer that uses custom memory configuration values. If you want to add WorkingSetMaximum or WorkingSetMinimum, you must type the elements and values in the RSReportServer.config file. Both values are integers that express kilobytes of RAM you are allocating to the server applications. The following example specifies that total memory allocation for the report server applications cannot exceed 4 gigabytes. If the default value for WorkingSetMinimum (60% of WorkingSetMaximum) is acceptable, you can omit it and specify just WorkingSetMaximum in the RSReportServer.config file. This example includes WorkingSetMinimum to show how it would appear if you wanted to add it:

Copy Code 
      <MemorySafetyMargin>80</MemorySafetyMargin>
      <MemoryThreshold>90</MemoryThreshold>
      <WorkingSetMaximum>4000000</WorkingSetMaximum>
      <WorkingSetMinimum>2400000</WorkingSetMinimum>

About ASP.NET Memory Configuration Settings

Although the Report Server Web service and Report Manager are ASP.NET applications, neither application responds to memory configuration settings that you specify in the processModel section of machine.config for ASP.NET applications that run in IIS 5.0 compatibility mode. Reporting Services reads memory configuration settings from the RSReportServer.config file only.

תגים:, , , ,

Finding and killing SSAS Sessions

Published at Apr 27 2011, 06:45 PM by Ronen Chenn

I have found a great BLOG at http://geekswithblogs.net/ManicArchitect/archive/2008/01/22/118801.aspx 

it discuss the way to find and Kill an SSAS session:

If you have a reasonably large Analysis Services implementation with users running adhoc queries via multiple tools (ProClarity, Excel, etc...) then you have probably run into problems with long-running queries. While longer-running queries may be acceptable in many data warehousing implementations, there will occasionally be a query (maybe even more than one ;) ) that causes resource issues and runs beyond the established limits of reason for your environment. Analysis Services does not have a native tool in SQL Management Studio for monitoring session activity and/or killing connections (hint: please add one). There is some sample code you can get from Microsoft along these lines but I wanted something simpler. In digging around for info on how to do this I found pieces of how to do it but nothing comprehensive so this is what I have put together so that I can do this through the SQL Management Studio.

Open a XMLA query window connected to the Analysis Services server in question.

Command 1 (Session Monitoring/Find it!):

Old School

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

<RequestType>DISCOVER_SESSIONS</RequestType>

<Restrictions>

<RestrictionList />

</Restrictions>

<Properties>

<PropertyList />

</Properties>

</Discover>

Using ASSP:

Call ASSP.DiscoverSessions

SQL 2008 Using DMV:

Select * from $system.DiscoverSessions

Command 2 (Kill it!):

Old School (Through XMLA Window)

<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<SPID>121672</SPID>

<CancelAssociated>1</CancelAssociated>

</Cancel>

Using ASSP:

Call ASSP.CancelSpid(121672)

Using ASCMD:

ASCMD.exe –S localhost –Q “Call ASSP.CancelSPID(121672)”

Passing URL parameters to Reports in SharePoint

Published at Apr 26 2011, 11:06 PM by Ronen Chenn

Passing URL report parameters directly to a report in a SharePoint Document Library is enabled in the SQL Server Reporting Services 2008 Integration with SharePoint Add-in.

Why? Key intent is to allow specifying non-default report parameter values when rendering of a report in full-page view in SharePoint.

How do you use this feature? Simply add-on the URL report parameters onto the URL displayed in SharePoint when viewing a report in full page view.

Examples: Here are examples using the Adventureworks sample reports assumed to be stored in SharePoint document library: http://<SharePoint_site>/Reports/ReportsLibrary

· Render the AdventureWorks "Employee Sales Summary" report for 'Syed Abbas' for 'January' / '2004' with ReportViewer Toolbar showing pagination only: http://<SharePoint_site>/Reports/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/Reports/ReportsLibrary/Employee%20Sales%20Summary.rdl&rv:ToolBar=Navigation&rp:EmpID=288&rp:ReportMonth=1&rp:ReportYear=2004

· Render the AdventureWorks 'Product Catalog' report with the Document Map Area collapsed and no Header Area: http://<SharePoint_site>/Reports/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/Reports/ReportsLibrary/Product%20Catalog.rdl&rv:HeaderArea=None&rv:DocMapMode=Collapsed

ReportViewer parameters for controlling full-page report rendering

ReportViewer Parameter

Description

Values

rv:HeaderArea

Control how the ReportViewer web part’s header is displayed in full-page view.

Full, None.

Default: Full

rv:ToolBar

Control how the ReportViewer web part’s toolbar is displayed in full-page view.

Full, Navigation, None.

Default: Full

rv:ParamMode

Control how the ReportViewer web part’s header is displayed in full-page view.

Full, Collapsed, Hidden.

Default: Full

rv:AsyncRender

Control whether the report is rendered asynchronously or synchronously.

true, false.
Default: true

rv:DocMapAreaWidth

Control the display width in pixels of the ReportViewer web part’s Document Map area.

Non-negative integer

rv:DocMapMode

Control the display of the Document Map area of a report.

Displayed, Collapsed, Hidden.
Default: Dislayed

SQL Server Reporting Services 2008 R2

In the Report Viewer that ships with the SSRS 2008 R2 Add-in for SharePoint the following new Report Viewer parameters are added:

ReportViewer Parameter

Description

Values

rv:DockToolBar

Controls whether the ReportViewer web part’s toolbar is docked to the top or bottom.

Top, Bottom.

Default: Top

rv:ToolBarItemsDisplayMode

Controls turning of or on specific parts of the ReportViewer web part’s toolbar. This is an enumeration field eg. For no Actions menu rv:ToolBarItemsDisplayMode= 63 (or 0x3F); For all of Actions menu only rv:ToolBarItemsDisplayMode= 960 (or 0x3C0)

Back= 1, (0x1)

Find= 2, (0x2)

Page Navigation=4, (0x4)

Refresh= 8, (0x8)

Zoom= 16, (0x10)

AtomFeed= 32, (0x20)

Actions Menu Print= 64, (0x40)

Actions Menu Export= 128, (0x80)

Actions Menu ReportBuilder= 256,(0x100)

Actions Menu Subscribe = 512, (0x200)

Default: -1

MDX Script Performance Analyser

Published at Apr 16 2011, 09:10 PM by Ronen Chenn

Another cool project at codeplex in which I am using a lot, to analyze queries and choose the best one…

find it at http://mdxscriptperf.codeplex.com/

The MDX Script Performance Analyzer allows the user to identify how much each calculated member, named set and scoped assignment in an Analysis Services cube's MDX script contributes to the overall performance of a given MDX query.
To use the tool, all you need to do is connect to the database and cube you'll be querying and then enter an MDX query in the main textbox and hit the 'Run Query' button. When you do this, the tool does the following:

  1. Connects to the cube using ADOMD.Net and AMO
  2. Read's the cube's MDX Script and splits it up into its constituent statements, storing them in an array
  3. Executes a Clear Cache command to ensure that all queries are run on a cold cache
  4. Executes a Clear Calculations command so that for the current session the cube appears as though its MDX Script contains no commands
  5. For each statement in the array of statements from the MDX Script, it then:
    1. Executes the first statement in the MDX Script within the session, so that the cube now acts as though its MDX Script contains only this statement and all previously executed statements
    2. Runs the query you entered in the textbox
    3. Stores how long the query took to run, plus other interesting metrics
  6. Once the query has run on the equivalent of the entire MDX Script in the cube, a report is generated which contains graphs and charts illustrating the data captured earlier

Generally speaking, whenever you see a steep (and sustained) increase in the amount of time taken to run your query then you know that the accompanying MDX Script statement has had that effect and might be worth optimising.
For instance, look at the following screenshot and notice that the left graph spikes and sustains the increase:
Screenshot.gif

SQL Server Analysis Services samples and more

Published at Apr 16 2011, 09:08 PM by Ronen Chenn

Hi All,

I would like to introduce to you a project located at codeplex  that contains SQL Server Analysis Services samples contributed by Microsoft outside of the regular release cycle, MVPs, and other members of the community.

The release contains the following projects:

  • Compress M2M C# Application
  • A Solution for Collecting Analysis Services Performance Data for Performance Analysis - Updated 3/6/09 to simplify install and use
  • Scripts for Using ASCMD for Stress Testing - Updated 2/23/09
  • Script for Creating a Processing Log File
  • Powershell Scripts for Querying Analysis Services 2008 DMVs
  • AS Load Sim, including Load Testing Best Practices document
  • AS Query Generator
  • Load Test Reports
  • RSS Style Sheet
  • Activity Viewer
  • Aggregation Manager
  • AS Trace
  • Analysis Services Upgrade Verification Tool
  • SSIS Package For Collecting SSAS DMV Data
  • SSAS Monitoring Scripts For Management Data Warehouse
  • SsasHelper - Work with Visual Studio SSAS Projects using AMO, sample MSBuild task to build a SSAS project
  • ResMon - add SSAS server stats to a cube

link to download it from codeplex http://sqlsrvanalysissrvcs.codeplex.com/

A Kerberos authentication fails - error code 0X80090302 or 0x8009030f

Published at Apr 13 2011, 06:28 PM by Ronen Chenn

if your Kerberos authentication fails together with the error code 0X80090302 or 0x8009030f on a computer that is running Windows Server 2008 or Windows Vista when the AES algorithm is used, Consider the following scenario:

  • You have two computers that are running Windows Server 2008 or Windows Vista. One computer sends a Kerberos authentication message to the other computer.
  • The Kerberos authentication uses the Advanced Encryption Standard (AES) algorithm to encrypt and decrypt the authentication data.

so to solve this out, download and install this hotfix: http://support.microsoft.com/kb/969083

תגים:, , ,

Excel Services - File Access Method

Published at Apr 10 2011, 06:34 PM by Ronen Chenn

Hi friends,

We are now involved in a very big excel based dashboarding. I will share with you some of the things we have run into –> so it makes easy for you. I hope.

File Access Method – This setting determines the authentications method used by the ECS to get workbooks from trusted file locations other than SharePoint document libraries. You have two options for this setting:

Impersonation – This is default. The end-user account is used to access the workbooks.

Process Account – The ECS process account will be used to access the workbooks.

Process Account:

Excel Services allows the process account to be used to read workbooks from UNC or HTTP locations. Although this is not a classic spoofing threat, the result is similar: users are allowed to read a workbook, even though they might not have permissions to it.

You should use SharePoint document libraries rather then UNC or HTTP locations to ensure that the end user’s credentials are used to verify the permissions. For UNC and HTTP locations, you should use impersonation rather than the process account. Using impersonation requires setting up Kerberos-constrained delegation to the UNC or HTTP server, which makes this deployment more difficult.

If you must use the process account, ensure that it is acceptable for all authenticated users to have permissions to those files.

Add a trusted file location

http://technet.microsoft.com/en-us/library/cc261678.aspx

Manage Excel Services trusted file locations

http://technet.microsoft.com/en-us/library/cc263009.aspx

Impersonation

Impersonation enables a thread to run in a security context other than the context of the process that owns the thread. Select this option to require Excel Calculation Services to authorize users who try to access workbooks stored in UNC and HTTP locations.

Note: Selecting Impersonation has no effect on workbooks that are stored in Microsoft Office SharePoint Server 2007 databases. In most server farm deployments in which front-end Web servers and Excel Calculation Services application servers run on different computers, impersonation requires constrained Kerberos delegation.

Process account

If Excel Calculation Services application servers are opening workbooks from UNC shares or HTTP Web sites, the user account cannot be impersonated, and the process account must be used.

Under Connection Encryption, select Not required if you do not want to encrypt communications with the front-end of Excel Services, or select Required to use encryption for all communication with the front-end of Excel Services.

Consider deploying Internet Protocol security (IPsec) or Secure Sockets Layer (SSL) to encrypt data transmission between Excel Calculation Services application servers, data sources, client computers, and front-end Web servers. If you decide to require encrypted data transmission, you will have to manually configure IPsec or SSL. You can require encrypted connections between client computers and front-end Web servers while allowing unencrypted connections between front-end Web servers and Excel Calculation Services application servers.

תגים:, ,

SQL Server Denali–Early adapters

Published at Apr 04 2011, 11:08 PM by Ronen Chenn

 

היום יצאה לדרך תוכנית המטרו של SQL Server Denali. 

התוכנית מיועדת למי שכבר יודע שהוא רוצה להיות באוויר עם פתרון מבוסס  Denali תוך שישה חודשים מההשקה.   

מי שיתקבל לתוכנית יקבל הטבות שונות כגון גישה לביטים ושיחות ועידה עם קבוצת המוצר. 

יש עדיפות למי שעושה שימוש בחידושים הרבים ביותר מתוך Denali.  פרטים נוספים על התוכנית:

 

Blog: http://blogs.msdn.com/b/rdoherty/archive/2011/04/04/announcing-metro-early-adoption-program-for-sql-server-code-name-denali.aspx

 

C9: http://channel9.msdn.com/Blogs/rdoherty/Announcing-Metro-Early-Adoption-Program-for-SQL-Server-Code-Name-Denali

 

 

תגים:, , ,