SSRS Reports Crawling – Too Many Images?

Over the past several weeks, I have been trying to find out why the Reporting Services report was taking so long to render.  I just wrote my first Reporting Services report just a few weeks ago.  I have administered Reporting Services for a few years but have never built a report.  In this new job I took early this year, my first project with the new company was to create a cube and we would have reports executing against the cube.  We hired a contractor to help me since this was the first cube where I would help build it.

We finished the project in time but since I was new and the contractor was helping, we did not always have all the facts.  Right after he left, we deployed it to Production.  Yes it was there.  The processing of the cube is very fast, under 4 minutes average processing running over 80 stored procedures to gather the information. The cube processing time was excellent.

The data was in the cube very quick  but running the reports was like watching paint dry on the wall.  We only have 5 different reports.  No matter how simple, the report was very slow.  I could request a report and I could walk away from my desk, go get coffee or tea, talk to some other developers, return to my desk 8 minutes later and the report would still not be finished.  The report has expandable columns and if we clicked to open an expandable part of the report you would have to wait another 5 minutes before you would see the expanded part of the report.  As we all know, this is totally unacceptable.

I read all sorts of articles and blogs, nothing helped.  We had two fact tables and I combined it to one Fact table.  On the single Fact table, I added ColumnStore Index.  This should fix the slowness of the report, right? No, the report was still slow.

While testing our product manager noticed images in the report to show either a check mark or a “x” mark.  In the report we were using the gauge indicator to display the check mark or “x” mark depending upon the value in the database.  Much to our dismay, the image coming from the indicator gauge was being rendered several hundred times on several of the reports causing the slowness.  Not Good!

    Solution to our slow running reports was to remove the gauge indicator and make the check mark and “x” mark a text value instead using the WindDings font.  After changing all the reports to use text instead of a image, the reports rendered 300 times faster.  No more slow reports, issue solved.

.

Page Life Expectancy Issues

When I took this new job in January, I inherited a Windows 2003 Standard server running Sql Server 2005 with only 4 gigs of ram.  How in the world does this server even run Sql Server with only 4 gigs of ram.  As I always do, I set the min and max memory setting for Sql Server with min being 1 gig of ram and the max getting 2 gigs of ram.  If you are not aware, Windows 2003 Standard maximum memory is 4 gigs.

As I began to monitor this server, the first thing I did was ask for more ram since I was seeing Page Life Expectancy issues everyday.  If I could get a page to last 3 seconds, I was lucky.  I knew to get better performance from the Sql Server, I had to solve this issue.  Rebuilding the server was not an option since development was building a newer version of the product.  To make it even worse, the server has the web and app layer also on this server.  I know you are not suppose to do this, but remember I inherited it.  Lucky me!

Due to a great resource of BrentOzar.com, I ran sp_blitzindex on the server to see if what type of indexing issues might be occurring.  I saw several indexes not being used and missing indexes.  I also saw tables with no clustered indexes.  Last night with a change control ticket, I fixed all the missing indexes issues and on one query which was timing out some times, I added the missing clustered index.  I also dropped indexes not in use.  This morning when I came in to review the monitoring of the server, I noticed my Page Life Expectancy had changed dramatically.  The server when from 3 seconds to 2 hours 17 minutes and 51 seconds.  Wow, don’t tell my system administrators but fixing the index issues also solved memory pressure issues.  You know a DBA always wants more memory.  Yes, I knew it was going to help but not to the degree of change I am seeing now.

Check out Brent Ozar and look for the sp_blitzindex and sp_blitz scripts.  Brent and team, you make me look very smart.  Thanks for all you do for the community.

Missing mssqlsystemresource database

Last night, one of our Development servers had a service pack get pushed to the server and automatically install.  I was unaware our patching process was automatically patching all of our servers which are not in Production.  Not a good idea, I would rather control this process and deploy the patches in a uniform process.

After the server was patched, the patch required a reboot.  When the server rebooted, Sql Server would not start up.  After reviewing the Windows Application log and Sql Server log, I quickly discovered the mssqlsystemresource database was missing.  I did search for it just in case it was placed somewhere else, but to no avail.

If the database was misplaced, I could have used this article just to move it back to the proper location, http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/01/22/error-945-severity-14-state-2-database-mssqlsystemresource-cannot-be-opened.aspx.

In my situation, the database was not misplaced, but it was gone.  Next I read the blog, http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/9ddc5d52-5835-405f-ad38-ac25022373f4/.  As you can read, I needed to find a server with the same version number to copy the files from the working server to the broken server.  I opened the Sql Server error log to get the version of Sql Server.  I scrambled around the office looking for a server with the same version number of the broken server.  No luck.

What is a DBA to do?  I have the version number but what do I need to install.  Thanks to our http://sqlserverbuilds.blogspot.com/ which shows use all the versions and links to the website for the download. I started to think how could I get this server back online, create a VM, install Sql Server 2008 R2 with cumulative update 1. I could then copy the files I needed from the new server to the old server.  Do I need to save the Master and MSDB databases?  This could take a while.  I need this server back online.

Instead, I went to Add/Remove program, found the Sql Server program and did a repair installation.  After the repair completed, my server was back online.  Yea!

But I am not finished, what do I need to do to prevent this same issue in the future.  The files look like a database, why can’t I just back them up in Sql Server like other databases. Microsoft does not let you do that.  But per this article, http://msdn.microsoft.com/en-us/library/ms190190.aspx, you can backup the files for emergency recover.  If needed, you can use the backup files for recovery but be carefully as the article states.  Finally, I think I am protected here.  Time to move on to the next issue.

Executing a Sql Server 2012 SSIS package with a Proxy Account with least rights across Domains

Note:  This is for the new package deployment process in Sql Server 2012.

Situation: I need to execute a Sql Server 2012 Integration Services package from the new SSISDB database by a Sql Server Agent job.  This package needs to run across servers and across domains that has only a one way trust relationship. Due to security issues, I will not use Sql Server Authentication accounts.  Domain B Server B will pull data from Domain A Server A.  Domain B trust Domain A but Domain A does not trust Domain B.  Server A is a Sql Server 2008 R2 server and Server B is a Sql Server 2012 server. All SSIS packages will reside on Sql Server 2012 in the Integration Services Catalog SSISDB database.

Setup:

Domain A  –  Server A

Domain B – Server B

Solution:

When we create a new job to run the SSIS package, by default the package will run with the account which runs Sql Server Agent.  In our scenario, Sql Server agent is running on Server B with a domain account which resides in Domain B.  This Sql Server agent account can’t be added to Server A so it will not be able to access data on Server A.  I need the SSIS package to get data from Server A.  Because of the domain relationship, I can’t use the Sql Server Agent account running on Server B to run my SSIS package as it does not have access to Server A.

Here is how I solved the issues.  First, we need to setup a new domain account which can talk to both domains.  The new Windows Authentication account is setup in Domain B.

Add Windows Login

Add Windows Login

We added the new Windows Account into Sql Server login and added the account to the database on Server A where I need to get my data from.

Add Database User

Add Database User

In my case, we then gave the account the right to select the data from the tables we need to access on Server A. You could also give the account db_datareader but since you have gone to this extent to secure the database, I would only give it access only to the tables it needs to read. To make it easier to move between environments like Dev, QA and Prod, I would add a database role and assign all security to the database role.

Add Database Role

Add Database Role

Next we need to add the Windows Account login to the database role.

Add Database User to Database Role

Add Database User to Database Role

All security on the tables and stored procedures should be granted to the database role.

Grant Object Security to Database Role

Grant Object Security to Database Role

On Server B, we add the new Windows Account into Sql Server login.  Add Login to Server BSince this is going to running the job, we need to create a proxy setting for this account. To setup the proxy, first I added the Windows Account as a Security Credential.  For this you will need the password of the Windows Account.

Add Credential

Add Credential

After setting the Windows Account as a Security Credential account, under Sql Server Agent, I added the Windows Account to Proxies.

Add Credential to Sql Agent Proxy for SSIS

Add Credential to Sql Agent Proxy for SSIS

The new Windows Account was added under SSIS Package Execution in the Proxies area.  Next we will add principals, SqlAgentOperatorRole and db_ssisadmin to the new proxy account.  Add Proxy Account PrincipalsBoth of these principals are found under type, select the MSDB role.  In addition to the proxy principals, we also add proxy reference to each job step which uses the proxy account.

Add Job steps with Proxy account to add to this screen

Add Job steps with Proxy account to add to this screen

Next we need to add security to the SSISDB.

Add Security to SSIS DB

Add Security to SSIS DB

At the project folder level in the SSISDB, we need to add permissions of read and execute objects.  When you deployed the SSIS packages, a new folder is created under project folder called projects.  This will create a Project with the same name as your solution.

Add Security to SSIS Project

Add Security to SSIS Project

You will right click on your project, select properties. On the properties page, you will see the permission information.  Add the Windows Account and give it Read and Execute permissions. You can now create your Sql Agent Job using new security.

Add Job Step with new security

Add Job Step with new security

You should now be able to execute your job. For assistance, please contact Tom at ArmorDBA@gmail.com.

This is a new beginning, writing.

Hello my fellow warriors, this is a first for me.  I have never been able to put my thoughts into words on a blog, but I love to speak at my Sql Server user group and Sql Saturday events.  So be nice and I will try to share information with you on securing data.  I am a DBA which also must meet high security standard and audits.  Let’s see what we can learn together.  If you have questions, I will try to answer them or find an answer for you. Let’s see where this goes and make sure you put that armor around your data.

Tom