SQL Server Faq

Following is the powershell script to script out all the Jobs in one go.

param($sqlserver)
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | Out-Null
$srv = New-Object “Microsoft.SqlServer.Management.Smo.Server” $sqlserver
$srv.JobServer.Jobs | foreach {$_.Script()}

You can copy the above code in a notepad and save it as .ps1 file in some location on server.

Next from the Object Explorer in SQL Management Studio, right click the SQL Server Name and Click on Start Powershell as shown below

This will open up powershell prompt. In the Powershell prompt you can run the above code as below

<exact path of file>\scriptjobs.ps1 <<Instance Name to script>>   >     <<path of the file to redirect the script output>>

For e.g.

PS SQLSERVER:\SQL\PARIKS-MSFT\DEFAULT> C:\Users\pariks\Desktop\scriptjobs.ps1 PARIKS-MSFT\SQLR2 > C:\Users\pariks\Desktop\Job.sql

The Job.sql file will be created which will have all the Jobs scripted for that instance.

Parikshit Savjani
Premier Field Engineer

In SQL Server, our Auto Update Statistics algorithm still relies on the default 20% modification on a given column to trigger update statistics. However different customers have different workloads and for some this threshold value triggers Update stats less frequently (e.g SAP databases) while for some workload the default 20% setting triggers Auto Update Stats more frequently.

To resolve the issue of less frequently updated stats TF 2371 was introduced as described in detail by Juergen Thomas’s blog

But some customers don’t like Trace Flags since it is considered not a best practice to run a product with Trace Flags.

Further According to me customers or more specifically DBAs/Application Vendors are the best judge to decide when update stats should be triggered and hence a configuration setting should be introduced to allow the customers to configure the thresholds for Auto update stats.

So I filed this suggestion as Product Feedback in Connect site. So if guys agree with me on this. Make sure you Vote for this feature in the following link

https://connect.microsoft.com/SQLServer/feedback/details/714770/need-sp-configure-parameter-and-database-properties-setting-to-control-the-threshold-to-trigger-auto-update-stats#details

Parikshit Savjani
Premier Field Engineer

As we all know, Report Server Execution Log plays a significant role and is the first place to look into in order to analyse & optimize the Performance of Reporting Services. Hence in each version of SQL Server, the ExecutionLog view in Report Server database is enhanced to gather more and more information.

If you need more information on Report Server Execution Log you can check the following link

http://blogs.msdn.com/b/sqlserverfaq/archive/2011/12/07/what-s-execution-log-in-reporting-services.aspx

However, in this blog post I intend to explain you how to analyse the report server execution log.

The Execution Log contains the following information viz

1. User executed the report
2. Report name and Report Path
3. Instance which executed the report (useful in scale out deployments)
4. Total execution time (Start Time and End Time)
5. Data Retrieval Time
6. Report Processing Time
7. Report Rendering Time
8. Source of the Report (Live,Cache,Snapshot,Session cache etc)
9. RequestType (Interactive,Subscription etc)
10. Status (Success or any error)

For analytics, I can interpret the above information as the following dimension model

Measures

1. Count of the number of executions of the report
2. Avg Execution Time taken by the report
3. Avg Data Retrieval time taken by the report
4. Avg Report Processing time taken by the report
5. Avg Report Rendering time taken by the report

The above measures needs to be sliced across the following dimensions

1. Report Catalog (DimReport)
2. User (DimUser)
3. Instance (DimInstance)
4. Source of Report (DimSource)
5. RequestType (DimRequest)

For the above dimension model, I can create a separate DW database defining the dimension and fact table and then design an SSIS package to load the data periodically from Execution Log table to DW and further design a cube and add reports to interpret and analyse the data to identify

• Most frequently accessed reports ( which can be a good candidate for caching/snapshot)
• Reports where data retrieval takes long time ( which requires SQL query tuning)
• Reports which takes long time for processing (which are too complex and should be simplified)
• Reports which takes too long (which are slow and needs optimization)

Although the above reports are helpful and important but creating a separate Dimensional Model, SSIS package and Reports to analyse the report execution log might not be good idea due to following

• The time required to design the DW,SSIS packages and reports is more.
• A separate sql instance or adding the above database to an existing instance adds an overhead to the server.
• Only 1 or 2 users who are Report Administrators need this report and it is not required for wide variety of users.

So the cost of implementing this solution might far far exceed its benefits.

This is where SELF SERVICE BI (Powerpivot or PowerView (soon to be released)) plays an important role and help us achieve the same results without spending much time and infrastructure.

So to use Powerpoint, one needs to install Excel 2010 with shared Office components alongwith powerpivot add-in for excel 2010.

Once installed, you can open Powerpivot window from Excel and import the following tables from the SQL Server instance hosting the Report Server database

• ExecutionLog3 (in case of SQL 2008 R2, ExecutionLog2 in case of SQL 2008)
• Catalog

This will create 2 tables in your BISM model of your powerpivot worksheet.

Delete the undesired columns from ExecutionLog3 such as itemaction,format,bytecount which are not our measurable.

From the Catalog table, you can keep path and name column and delete the remaining columns.

This is done to ensure we don’t bring more data than required and thereby improve the performance of our report.

In the Design tab of the powerpivot window, you can click Create Relationships and create a relationship between ExecutionLog3 and Catalog table such that Itempath column of ExecutionLog3 refers to Path column of Catalog

 
Once the relationship is created, you can create a Pivot Chart from the Home Tab of Powerpivot window in the same worksheet or new worksheet.

You will now observe a empty PivotChart worksheet alongwith a powerpivot Field list window aligned on the right as shown below.

You can expand the ExecutionLog3 table and drag an drop ( or simply check) the following column to the value section of the Powerpivot window

• TimeDataRetrieval
• TimeProcessing
• TimeRendering
• Executionid

You will see that they get added as Sum which is a default aggregate for all values. However you can right click these columns in values section and change the aggregate to Average as shown below

if you notice, for executionid column the default aggregate added was Count which is correct and shouldn’t be change as we need to plot the number of time the report was fetched.

From the catalog table, you can drag the name the column into the Axis Field Section of the PowerPivot window so that report name forms the X-axis of the chart.

This is the reason to import catalog table in the powerpivot since ExecutionLog3 view stores the Path of the report and not the actual report Name.

Further we can add a RequestType column as ReportFilter since we are interested to analyze only the performance of Interactive Report and least bothered for subscription report which comparatively takes lesser priority

Now, we can add the Horizontal and Vertical Slicers which can help us analyse the data more interactively.

In our case, the fields InstanceName and UserName forms the Vertical Slicers while Source and Status forms the Horizontal Slicers as shown below

These Slicers will help me identify and slice the data in the following manner,

• in which instance the requests are more than the other ( useful for scale out deployments)
• Which User queries which report more
• Which report request failed or aborted (status column)
• How many requests were catered from Cache,Snapshot,Live,Session

After adding the above, my Pivot Chart Report looks as shown below

If I want to find the reports which were aborted maximum number of times during processing, I can simply click on the Horizontal slicer for Source and click on rsProcessingAborted and the graph changes to as shown below

This shows be 2 of my reports has gone for rsProcessingAborted status.Similarly, you use various other slicers to quickly interpret and analyse the report execution log.

Finally, you use the RequestType ReportFilter to filter only Interactive report by clicking on ReportType at the top of the chart as shown.

I was able to create this report in 30 mins without any knowledge of Dim or Fact tables and with the help of Powerpoint add-in which is free downloadable tool

Using PowerPivot to analyze the Report Server Execution Log to me is most cost-effective,efficient and smart way to analyze the logs.

Further this excel sheet can be published in the sharepoint if the same report needs to be accessed and seen by more than 1 users and further the access to the report can be controlled by sharepoint security.

This is one of the example of Self Service BI which can be acheived using Powerpoint and whose visualization is further enhanced by using Powerview which will be launched in SQL 2012.

Hope this helps !!!

Parikshit Savjani
Premier Field Engineer

Real Quick Post on one of the issue I faced today while connecting to SQL Analysis Services on my own laptop.

Today, when I tried connecting to named instance of SQL Analysis Services on my laptop using SQL Management Studio, I received following error

TITLE: Connect to Server
——————————

Cannot connect to *********\sqlr2.

——————————
ADDITIONAL INFORMATION:

A connection cannot be made to redirector. Ensure that ‘SQL Browser’ service is running. (Microsoft.AnalysisServices.AdomdClient)

——————————

No connection could be made because the target machine actively refused it 127.0.0.1:2382 (System)

——————————
BUTTONS:

OK
——————————

This stimulated my troubleshooting side of the brain and I started researching the error message in Bing. I found the following article for troubleshooting connectivity issues to SSAS

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

However although I didn’t get the exact solution from this article, it gave me pointers to the possible workaround for the issue.

As you see in the above error message, the SSMS is unable to connect to the SQL Browser service on the local computer running on port 2382.

So, my first guess was that the SQL Browser service might not be running. However I checked and verified from Configuration Manager that SQL Browser service was up and running fine.

However SQL Browser service was running under Local System account. So I tried to change  the SQL Browser Service to domain account who is also a Local Administrator on the box but that didn’t help.

So after restarting SQL Browser Service a couple of times, I decided to check event log for any errors or information and voila I found the following Warning message

“The SQL Server Browser service was unable to establish Analysis Services discovery”

I didn’t knew why this error message has occurred but this error message clarified that SQL Browser was unable to establish AS discovery and hence it was unable to listen on port 2382 where SSMS is looking for SQL Browser for SSAS.

I further verified using netstat –aonb and couldn’t find SQLBrowser running on 2382. I am not sure of what has changed since the last time I installed SQL 2008 R2 on my laptop but this definitely appears to be a bug or anomalous behaviour of SQL Browser.

I am not sure on how to fix SQL Browser without reinstalling it. However to workaround the issue I ran named instance of Analysis Services on static port by specifying the static port in <Port> </Port> section of msmdsrv.ini file located in following location in my case

C:\Program Files\Microsoft SQL Server\MSAS10_50.SQLR2\OLAP\Config

Following the restart of SSAS service, I ensured that ssas is running on the static port specified by me by using netstat –aonb

Finally I connected the SSAS instance by specifying the connection string as MachineName: <Port>

Hope this helps !!!

Parikshit Savjani
Premier Field Engineer