SSRS Report Usage Queries

Download PDF

This last week I had the opportunity to do some work with SSRS, determining some stats on report usage. I ended up digging up some queries that I wrote a couple years ago against the ReportServer database, and thought they would be worthwhile to share, so here they are.  These have been tested against SQL Server 2008, and 2008R2 databases.

I hope these can save you some time if you need to track down details on SSRS report usage.

There is one place where the text DOMAIN_NAME is referenced. That is intended to be replaced with your current domain.

Find out how many reports executions are in the log, and the oldest TimeStart

-- Written by Steve Stedman http://SteveStedman.com
SELECT COUNT(*),
       MIN(ExecutionLog.TimeStart)
FROM [ReportServer].[dbo].ExecutionLog(NOLOCK);

Just looking at what is in the log.

-- Written by Steve Stedman http://SteveStedman.com
SELECT TOP 100 c.Name,
               c.[Path],
               l.InstanceName,
               l.ReportID,
               l.UserName,
               l.RequestType,
               l.Format,
               l.Parameters,
               l.TimeStart,
               l.TimeEnd,
               l.TimeDataRetrieval,
               l.TimeProcessing,
               l.TimeRendering,
               l.Source,
               l.Status,
               l.ByteCount,
               l.[RowCount]
FROM [ReportServer].[dbo].[ExecutionLog](NOLOCK) AS l
INNER JOIN [ReportServer].[dbo].[Catalog](NOLOCK) AS c ON l.ReportID = C.ItemID
WHERE c.Type = 2 -- Only show reports 1=folder, 2=Report, 3=Resource, 4=Linked Report, 5=Data Source
ORDER BY l.TimeStart DESC;

List the reports with the number of executions and time last run:

Read more ›

Posted in SSRS Tagged with: , , , , , , ,

The Walking Desk

Download PDF

Lately I was asked a question about getting steps in using my FitBit.

The answer is the walking desk. This allows me to use my computer while walking on the treadmill at 2.2 miles per hour. 2.1mph to 2.4mph for me is the optimal walking speed to still be able to use the keyboard and the mouse effectively.

I originally purchased the treadmill about 2 years ago, but didn’t use it much until I got the FitBit last February. Since getting the FitBit, it has really motivated me to use the treadmill at my walking desk.

WalkingDesk

The treadmill is a special low speed treadmill with no front console. The controls for the treadmill are on the console that sits to the right of my keyboard. The treadmill is made my TreadDesk, and is available on Amazon.com.  I found this one on craigslist. Its not your normal high performance treadmill like you might see at the gym, instead it is a low profile treadmill designed to work under a standing desk.

The desk is something that I built using 4×4 posts for the legs.  The laser printer sits to the left of the treadmill just under the desktop.

The monitor is a 40 inch 1080p television that connects to my computer that is located in the closet behind the wall. The reason for the 40 inch monitor is to make it easier to view while walking.

 

Posted in Uncategorized

2015 Review

Download PDF

What 2015 meant to me and my business.

Stedman Solutions, LLC – Startup

StedmanSolutionsRemoteSkilledDBAAfter unexpectedly leaving a regular full time employment position on March 31st 2015, the very next day April 1st 2015, Stedman Solutions, LLC was launched. Looking back, perhaps April 1st (April fools day) was not the best day to announce the big event, but I didn’t really have anything else to do.  

Stedman Solutions, LLC, my database administration consulting firm was off and running. The first 2 months were a bit slow, but since June the business has been growing strong, and I have had more than enough work to keep me busy lately. As we come to the end of 2016, just 9 months after starting the SQL Server DBA consulting firm, the only thing I regret is “why did I wait so long?” Looking back I wish I had made the leap to start my own firm years ago.

I look forward to April 1st (April fools day) 2016, as that will mark the 1 year anniversary of starting the business.

I just want to take a moment to say thanks to all my clients, who give me the the opportunity to work with a variety of SQL Server environments with a variety of great teams and great people. Thank you!

2016 Goals — Stedman Solutions, LLC – Startup

Grow the Stedman Solutions, LLC consulting firm from just me to 2 additional people for a total team of 3.

 

Database Corruption Challenge

CorruptionChallenge

In the spring of 2015 I created and hosted the Database Corruption Challenge, an online contest that involved me creating a corrupt database about once a week. I then posted it online and threw out the challenge for others to attempt to solve it. This was an amazing experience in a number of ways:

  1. It forced me to practice my skills at fixing database corruption.
  2. I was able to learn from all the solutions that were submitted by participants.
  3. I got to know some great people who participated in the contest.

I am planning to put together another database corruption challenge in 2016, but my schedule is looking pretty full for the first 3 months.

2016 Goals – Database Corruption Challenge

Host another Database Corruption Challenge in 2016.

 

Database Health Monitor

Star_of_lifeIn 2015 I had 6 major releases of the Database Health Monitor application. The biggest being the official version 2 release on July 3rd 2015 which after 4 years of beta finally took the version 2 product as a full release version.

There were 16 unsolicited comments from raving fans about Database Health Monitor, the most recent of which was the following “Excellent – It’s the first tool I open every morning to see what is going on in my production environment! Thanks for the great work!” submitted on 12/23/2015.

One of my biggest focuses on Database Health Monitor in 2015 was the instance level reports and the ability to quickly switch through the instance reports to get a good understanding of the overall health of a SQL Server on a daily basis. An example of this is the SQL Server Error log report which allows you to view, filter, and cycle the error log. This means that on a daily basis, I can review the error logs on 20 SQL Servers in a total of 30 to 45 seconds, saving time for other more useful tasks.

With just over 2600 downloads of Database Health Monitor in 2015, that is a big step up from any of the previous years. I look forward to where it goes in the next year.

I plan to continue to develop and evolve Database Health Monitor into the future. I have some some great things planned for the next several releases.

The business model for Database Health Monitor also significantly shifted for me this year. I started out the year with the goal of running a crowdfunding campaign, and eventually turning Database Health Monitor into a paid application. After starting Stedman Solutions I decided to go with a free model, which instead includes a small advertisement in the application for my consulting business. This has been proven to work, and plan to continue to give Database Health Monitor away at no cost will continue.

I never realized that it would be so difficult to explain free, I get so many questions from people asking things like this:

  • “what does it really cost if we want to use Database Health Monitor in a production environment?”
  • “what if I want to monitor more than one instance?”
  • “what is the cost to a consultant who wants to use it at a clients site?”

Its simple, free, zero, no cost at all. You don’t even have to register, however registering will dismiss the advertising display.

2016 Goals – Database Health Monitor

5000 downloads of Database Health Monitor in 2016.  At least 4 version updates, and 20 raving fans posted on the website.

 

A New Kitchen

IMAG0109Just prior to leaving the day job and starting Stedman Solutions, LLC full time, my wife Marcia and I had started working on a kitchen remodel. With the unexpected startup of my company, and not knowing for sure how the rest of the year would look, we decided to hold off on finishing the remodel work until things stabilized. The kitchen work started again in December 2015, and is scheduled to be complete in the next 2 weeks. Although I am not doing the work myself, I am very excited about the new kitchen soon.

 

 

 

Related Links

Posted in Steve Tagged with: , , , , , ,

Optimize for Ad Hoc Workloads

Download PDF

The Optimize for Ad Hoc Workloads server configuration can improve performance, and is extremely unlikely to negatively impact performance.
This was a new feature that was introduced in SQL Server 2008, and as with many new features in SQL Server, it is not turned on by default.

What Optimize for Ad Hoc Workloads Changes.

Without the Optimize for Ad Hoc Workloads feature enabled, when a plan is compiled, it is kept around in memory (cached) until something else pushes it out of the cache.

With the Optimize for Ad Hoc Workloads feature enabled, the first time a plan is compiled, it is not kept around in cache, rather a stub is kept around to indicate that they query has previously been complied. The second time the plan is compiled it is kept around in memory. Over time you end up with fewer one time use queries in memory, reducing the memory impact of the plan cache. All other multi-use ad hoc plans will need to be compiled twice on the first and second time that they are used.

What it doesn’t to

The Optimize for Ad Hoc Workloads server configuration doesn’t change the size of the plan cache in SQL Server, it does however prevent the single use ad hoc plans from using up plan cache space, making more space for other cached plans.

Read more ›

Posted in SQL Server Tagged with: , , , , ,

Jingle Bell Run – My second 5K run.

Download PDF

Today I participated in my second ever 5K race today, the Bellingham Jingle Bell Run. My running partner (Gypsy the black dog in the picture) accompanied me, and might have increased my speed at a couple of points due to her pulling on the leash. This was as event was a fund raiser for the Arthritis Foundation, which seemed like a good cause to donate to, and to get me out in the rain for a fun run.IMAG0079

This event actually encouraged people to bring their dogs, and dogs participating in the event even received a bandanna with a jingle bell on it.

5kYelp

I was part of a team organized by our local Yelp ambassador, and there was a fun Yelp event at the Old World Deli in Bellingham afterwards.

What a great day!

Posted in Epic Life Quest Tagged with:

Database Health Version 2.2 Released Today

Download PDF

Today I have the pleasure of releasing the next version of the Database Health Monitor application. Currently at version 2.2, I have been working on this since 2011, and I have introduced some new features in the latest version. Visit the Database Health Monitor Download Page to get it for free today.

The main page has been updated, with a different look and layout to the charts.

Database Health Monitor

The following changes have been made since the last release.

New Features

  • Added a DBCC CheckDB dialog to run Check DB against a database. Reports status along the way and shows what is being checked as it gets scanned.
  • Added a link to the checkDB report page from the QuickScan CheckDB message.
  • CTRL+a for select all in the edit box on the missing indexes advisor.
  • Making the ‘see more’ option stick when viewing the CPU by Hour Heatmap.
  • Added a Disk Space Report into the instance level reports.
  • Added color coding to the Last Known Good CheckDB report.
  • Added right click copy to clipboard to the many of the charts. Now you can just copy the chart to the clipboard as an image.
  • Adding filtering of the historic waits for CXPACKET.
  • Adding links to the main server overview if there is a SQL Server update available.
  • QuickScan report
    • Adding a check for orphan database users to the QuickScan report.
    • Checking file growth for big databases with many very small growth amounts.
    • Performance improvement, Limiting the amount of data the QuickScan report will pull back from the error logs.
    • Adding details for the TempDB file growth messages.
    • Adding max degree of parallelism, and cost threshold for parallelism to the quick scan report.
    • Adding quickscan report checks for single tempdb files, and for different sized tempdb files.

Bug Fixes

  • Fixed the backup status report to include databases that have never had a backup. This was missed due to the join condition.
  • Fixing button colors on the CheckDB dialog. Buttons didn’t look right on Windows Server 2012.

Related Links

Posted in Database Health Tagged with: , , , , , , ,

Bellingham SQL Server Users Group – Building a Static Data Warehouse Server

Download PDF

Please help spread the word to anyone who may want to attend this free users group meeting and presentation.

The November meeting of the Bellingham SQL Server Users Group (PASS Chapter) will be held on Wednesday November 18th at 6:00pm.

Welcoming Ron Talmage from SolidQ with his presentation on Building a Static Data Warehouse Server.
The agenda for the meeting will be:

– Welcome and Pizza
– Chapter news and announcements
– Technical presentation (Ron Talmage)
– Socialize and networking

The chapter meeting will be at the following address. Thanks to Derrick Bovenkamp for providing the location for this meeting.

709 West Orchard Drive #4
Bellingham, WA 98225

This is the office right next to Jeckyl and Hyde Ale House on Orchard Drive.
For any questions, please contact Steve Stedman (360)610-7833

Featured presentation:

Building a Static Data Warehouse Server

Sometimes users require underlying data to be static and unchanging. In this session we’ll examine steps used to create a static server from a copy of a live data warehouse server, including implementing clustered columnstore on large partitioned tables, simplifying the management of files and filegroups, and reclaiming large amounts of database space.

Presented by Ron Talmage – Mentor and Co-founder, SolidQ

Ron is a mentor and co-founder with SolidQ. He is a SQL Server MVP and current chapter leader of the Pacific Northwest SQL Server Users Group. He has authored numerous SQL Server white papers and contributes to SQL Server technical publications.

Posted in PASS Chapter Tagged with: , , , , ,

Epic Life Goal Completed: Speaking at PASS Summit – Advanced CTEs

Download PDF

Last week I had the great opportunity to speak at PASS Summit 2015. My presentation was on Common Table Expressions.

Advanced CTEs

 

Here is the sample code from the presentation.

AdvancedCTEs.zip

 

Related Links:

Posted in PASS Summit Tagged with: , , , , , ,

Advanced Common Table Expressions and Recursive Query Technique

Download PDF

Friday morning at 8:00am at PASS Summit 15 in Seattle, I will be presenting my Advanced Common Table Expressions and Recursive Query Technique session.

Advanced Common Table Expressions

Advanced Common Table Expressions

Here is the abstract:

You might have been introduced to Common Table Expressions (CTEs) and understand the WITH syntax, but want to know more. Learn how recursive queries work with CTEs and how to display hierarchical data. Did you know that you can INSERT, UPDATE, and DELETE data from CTEs? This session covers some common use cases for CTEs, including finding holes in patterns, finding and removing duplicate data, string parsing, and more. Get an in-depth understanding of the performance behind a CTE and learn when a CTE is the right (or wrong) solution. Finally, take a look at some classic recursive algorithms and how they can be implemented with CTEs.

For more details check out the session on the PASS Summit 2015 website.

http://www.sqlpass.org/summit/2015/Sessions/Details.aspx?sid=8004

There is a long history behind this presentation, it originally started as a presentation at SQL Saturday #108 in Redmond in 2012. From there I presented it at several other SQL Saturdays including Vancouver BC in 2012. After presenting on CTEs, at about 8 different SQL Saturdays, I eventually split the session into two, the Introduction to Common Table Expressions, and the Advanced CTEs. This was around the same time that I finished my book on Common Table Expressions which released the day of SQL Saturday 212 in Redmond in 2013. I found that once it was split into the Introduction to Common Table Expressions and Advanced Common Table Expressions that the Advanced session was much more popular.

I was able to practice this session recently at the 24 Hours of PASS in June of 2015, and at the Bellingham SQL Server Users Group meeting just 2 weeks ago.

The Advanced Common Table Expressions is where I cover fun things that you can do with Common Table Expressions, including some really cool formatting techniques for hierarchical data.

I hope to see you there.

Related Links:

Posted in PASS Summit Tagged with:

Transactions Rolled Back in Database

Download PDF

IF you are browsing your error log and come across an error message stating that “1 transactions rolled forward in database ‘msdb'”, or “X transactions rolled back in database ‘msdb'” you might be a bit alarmed. You might also notice similar error messages for master, tempdb or user databases.

msdb transactions rolled back in database

 

How can this be a good thing? Why are transactions being rolled back or rolled forward?

Read more ›

Posted in SQL Server Tagged with:

SQL Server Performance Tuning

Need help with SQL Server Performance Tuning, contact Steve at Stedman Solutions, LLC for help, or take a look at the performance tuning page at the SteveStedman.com blog. We can help with tuning a single query or with figuring out why your SQL Server is running slow. Free 30 minute consultation.

Newsletter Signup

Stay informed of Database Corruption Challenge events, and other things happening at SteveStedman.com with my newsletter.
Newsletter signup form.

Stedman Solutions

StedmanSolutionsRemoteSkilledDBA

Archives