SSRS Cancel Report Still Running In The Background

by Posted in Business Intelligence, DBA, Reports, SQL Tips, SSRS Tagged , , , , 1 Comment

There are some people who cancel a SSRS report because its taking to long by just closing visual studio or their browser. Then they reopen SSRS/browser and try and view the report again. In the mean time the previous report they were trying to run the query is still running on the database. This takes up quite a bit of resources and people think that there query has been cancelled, but this in not the case.

This is a common problem with users accessing reports. There is a way to stop the query from running in the background by using the KILL command, however you will need a DBA to do this for you unless you have permission to do this your self.

Hope this helps other who are struggling with the same issue 🙂

Management Data Warehouse (MDW) Reports

by Posted in Business Intelligence, DBA, Management Data Warehouse, Reports, SQL Tips Tagged , , , , , , , , , 1 Comment

Management Data Warehouse reports can be very useful and you can get quite a lot of information regarding your server out of them. In this post I will be showing some of the reports and how to get access to them. The descriptions in the reports are hopefully intuitively obvious enough for the casual observer to interpret the reports.

Server Activity History
This can be accessed under Management > Right-click Data Collection > Reports > Management Data Warehouse > Server Activity History
ActivityHistory1 ActivityHistory2

System CPU Usage
You can launch this report by clicking on the green System line in the %CPU Utilization chart in the Server Activity History report.
CPUUsage1 CPUUsage2

System Disk Usage
You can launch this report by clicking on the green System line in the Disk I/O Usage chart in the Server Activity History report.
DiskUsage1 DiskUsage2

SQL Server Memory Usage
You can launch this report by clicking on the light blue SQL Server line in the Memory Usage chart in the Server Activity History report.
MemoryUsage1 MemoryUsage2

System Memory Usage
You can launch this report by clicking on the green System line in the Memory Usage chart in the Server Activity History report.
SystemMemoryUsage

SQL Server Waits
You can launch this report by clicking anywhere within the SQL Server Waits chart in the Server Activity History report.
ServerWaits1 ServerWaits2

Query Statistics History
This can be accessed under Management > Right-click Data Collection > Reports > Management Data Warehouse > Query Statistics History or by clicking on the blue SQL Server line in the % CPU Utilization chart in the Server Activity History report.

Only 10 queries are listed here. I would recommend clicking on the biggest one first to check the Query Details in order to optimize it better.
QueryStatistics

Query Details
You can launch this report by clicking on any hyperlink text in any of the reports that has them. For example, SQL Server Observed Waits, SQL Server Active Requests, and the Query Statistics reports all have links to Transact SQL queries.

You can also click on the Duration, Physical Reads and Logical Writes links to sort the plans by these dimensions.
QueryDetails1 QueryDetails2

Query Plan Detail
You can launch this report by clicking on the Query Plan line in the chart or the Plan # in the list in the Query Details report. The top part of the report is the same as the Query Details report, but the awesome demo portion is shown below.
QUeryPlanDetail
Hope this post was useful for you.

Choice to learn

Management Data Warehouse (MDW)

by Posted in Management Data Warehouse, Reports, SQL Tips Tagged , , , , , , , , , 2 Comments

Management Data Warehouse (MDW) is an awesome feature in Microsoft SQL Server that allows you to collect data about your server and view it in a readable format. MDW is a relational database that contains the data that is collected from a server that is a data collection target. This is very useful for DBA’s.

MDW provides 3 reports and you are able to create your own custom reports. The reports will provide suggestions for improving performance, based on information gathered by the Data Collector. The 3 main reports are Server Activity History, Query Statistics History and System Disk Usage.

Using a basic configuration of the MDW enables a DBA to perform tasks such as:

  • Proactive Tuning
  • Historical Query Analysis
  • Performance Baselining
  • Database Growth Forecasting
  • Storage Planning

With just a few simple clicks you can answer questions such as:

  • What are my most expensive queries in terms of a given resource measure (CPU/Duration/IO)?
  • What is my SQL Server instance waiting on typically?
  • Which are my fastest growing databases?
  • Why is query X blocking?
  • Is SQL Server busier than normal for this time of day?

Setting up MDW takes about 15 minutes, it is quick and simple!

To configure MDW you can follow these steps:

Step 1:
Expand the Management node in Object Explorer and right click on Data Collection and select Configure Management Data Warehouse. This will open up a wizard.
Step1
Step1wizard

Step 2:
Select Create or upgrade a management data warehouse. Then click Next.
Step2

Step 3:
Type in a Server Name, by default the server you are currently on will be populated in the name. You are able to set up MDW on a separate server and do the data collection for another server.
Step3

Step 4:
Click on New next to Database Name. A pop-up will appear to create a database for the MDW to collect and write the data to. Type in Database Name and click ok.
Step4

Step 5:
The Database Name will now be populated and you can click on next.
Step4db

Step 6:
Select a user to map to the MDW and set the Database membership role and click Next.
Step6

Step 7:
A summary window for the configuration will display and you can click Finish.
Step7
Step7comp

Step 8:
Next we need to set up a data collection to collect data from the server and databases you are interested in getting information from. Right click on the Data Collection node again and select Configure Management Data Warehouse. This time you will select Set up data collection and click Next.

Step8

Step 9:
Select the Server and Database where you want to store the data and click Next.

Step9

Step 10:
A summary screen will display and click Finish. Make sure SQL Server Agent is running, otherwise the setup will fail.
Step10
Step11

Once complete you will see the Data Collection sets in the Object Explorer
Step12

To access the reports, you will right-click on the Data Collection node > Reports > Management Data Warehouse
Step13

In my next post I will be going into more detail about the MDW reports and how to get the most value out of them!