Month: Jun 2020

Query Store stopped working after Windows Update on Database Host

Firstly, This is not going to be a long, detailed blog post. I would like to share an issue with Query Store that I experienced today after Windows Updates on SQL Server hosts and a quick fix for that.

Average Wait time is too high on SQL Server

I was doing sanity check after windows updates and noticed severe wait times on a server, caused by a specific database. This very database had a problematic select query which confuses optimizer to choose a wrong execution plan. Query Store helped me to address the issue in the past, I forced the right plan and move on.

I immediately went after the same query and realized it is the one that causes high wait times and lock issues. I checked Query Store and everything looked fine. My forced plan was still there and according to query store statistics, average execution time was 10 miliseconds for problematic query. However, I can see that query takes 10-15 seconds by checking system dmvs. Obviously query was not running with right execution plan. So how can that be and what’s wrong here?

Query Store stopped collecting data

It took some time for me to realize Query Store statistics stop around 7 AM in the morning, coinciding Windows Update time. For some reason, Query Store stopped working and collecting data. That’s why, it was not reflecting what is going on currently and I was blind.

I checked Disk usage and Query Store Space, both were fine. Then I tried to unforce and force the same plan, it didn’t help either. I couldn’t restart the instance or the database so I needed something else to provide quick fix.

FIX – Purge Query Store Data!

I correctly assumed that purging query data would trigger a mechanism internally to restart Query Store and decided to go for it. Voilà! Right execution plan is picked up right away by Query Optimizer, all locks are released and database performance went back to normal. I am not sure why query store stopped working after server reboots for Windows updates. To be able to identify the relation and what was the underlying reason, one needs a proper deep-dive investigation.

You can purge query data by right clicking on database, going into “Properties” and then jump on “Query Store” tab. There you will see a button at the bottom as also shown in below image.

Purge Query Data!

Disable Print and Export Data Buttons for PowerBI Report Server

Here we are again with another Power BI Report Server post. Last time I explained how to disable Download button in this post. This time, we will take a look on how to disable Print button and Export Data options.

Disable Print Button

Just like we did for download button, this is also possible via changing server properties. Before disabling, let me show you “Print Button”

 Steps:

  1. Open SSMS, choose “Reporting Services” for Server Type and connect to Reporting Service as shown in Figure 2. You may also use Web Portal/Service URL to connect to it.
  2. Right-click on Servername in Object Explorer and click Properties.
  3. Click “Advanced” Tab
  4. Under “Other” section, Set “EnableClientPrinting” to False.
It is a dropdown menu, simply click and change from “True” to “False”

PS: You don’t need to restart Power BI Reporting Service or your server. Just refresh the page.

Voilà! Now it is gone.

Disable Export Data Options

This is a bit more complex. There isn’t a simple switch that we can change and hide “Export Drop Down menu”. Again, let me show you this button.

I spend quite some time to find a way to disable this cute floppy disk button but I couldn’t find. Nonetheless, There is another nasty method to hide options via editing Reporting Service config file.

 Steps:

  1. Locate your configuration file “RSReportServer.config”. Normally it would be under your installation path i.e. “C:\Program Files\Microsoft Power BI Report Server\PBIRS\ReportServer”
  2. Open it with any text editor(Wordpad,Notepad et cetera) and search for “<Render” [Look at the screenshot#1 below]. This is the code section that we are interested. You will see various <Extension> objects under <Render> parent tag.
  3. Add ‘ Visible=”false” ‘ at the end of each <Extension> object [Look at the screenshot#2 below]
  4. Save the changes and close configuration file
  5. Restart Power BI Reporting Services from Services vmi
Find Render section
Change Visible attribute to hide export data options

You can also copy below code if you are too lazy.

    <Render>
      <Extension Name="WORDOPENXML" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordOpenXmlRenderer.WordOpenXmlDocumentRenderer,Microsoft.ReportingServices.WordRendering" Visible="false" />
      <Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering" Visible="false" />
      <Extension Name="EXCELOPENXML" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering" Visible="false" />
      <Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering" Visible="false" />
      <Extension Name="PPTX" Type="Microsoft.ReportingServices.Rendering.PowerPointRendering.PptxRenderingExtension,Microsoft.ReportingServices.PowerPointRendering" Visible="false" />
      <Extension Name="PDF" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PDFRenderer,Microsoft.ReportingServices.ImageRendering" Visible="false" />
      <Extension Name="IMAGE" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRenderer,Microsoft.ReportingServices.ImageRendering" Visible="false" />
      <Extension Name="MHTML" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.MHtmlRenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false">
        <Configuration>
          <DeviceInfo>
            <DataVisualizationFitSizing>Approximate</DataVisualizationFitSizing>
          </DeviceInfo>
        </Configuration>
      </Extension>
      <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering" Visible="false" />
      <Extension Name="XML" Type="Microsoft.ReportingServices.Rendering.DataRenderer.XmlDataReport,Microsoft.ReportingServices.DataRendering" Visible="false" />
      <Extension Name="ATOM" Type="Microsoft.ReportingServices.Rendering.DataRenderer.AtomDataReport,Microsoft.ReportingServices.DataRendering" Visible="false" />
      <Extension Name="NULL" Type="Microsoft.ReportingServices.Rendering.NullRenderer.NullReport,Microsoft.ReportingServices.NullRendering" Visible="false" />
      <Extension Name="RGDI" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.RGDIRenderer,Microsoft.ReportingServices.ImageRendering" Visible="false" />
      <Extension Name="HTML4.0" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false">
        <Configuration>
          <DeviceInfo>
            <DataVisualizationFitSizing>Approximate</DataVisualizationFitSizing>
          </DeviceInfo>
        </Configuration>
      </Extension>
      <Extension Name="HTML5" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html5RenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false">
        <Configuration>
          <DeviceInfo>
            <DataVisualizationFitSizing>Approximate</DataVisualizationFitSizing>
          </DeviceInfo>
        </Configuration>
      </Extension>
      <Extension Name="RPL" Type="Microsoft.ReportingServices.Rendering.RPLRendering.RPLRenderer,Microsoft.ReportingServices.RPLRendering" Visible="false" LogAllExecutionRequests="false" />
    </Render>

After restarting “Power BI Report Server” service and refresh your report, you will see that button is still there but there is no dropdown menu anymore since we disabled all options.

Until next challenge, stay safe.