As DBA’s we have our own patterns and schedules. The way I set my day will not be the same way you set your day; nor will occurances that occur throughout the day affect me the same as it would you. Last week turned out to be a not so typical day. Everyday I come into the office I have a set of things I’d like to get done in my head; now whether that occurs or not is remained to be seen but nevertheless some form of schedule is in my mind. As the work day progressed last Friday we were told of storms rolling in, to be honest when first hearing this I didn’t think much of it. I knew I had alot of things I needed to get done for that day and accomplished before I left.

We began to notice and get calls from family members that businesses were going to be shut down and people where heading out of town to get back to their homes. I cut my day short about an hour and a half not expecting what was about to happen.

I’m going to tell my age somewhat but the last time any Tornado came through was 1974; I was not born yet at that time but I have heard stories of it. As my family and I sat and watched the storms unfold we started to hear some hail, then some rain….that was all that unfolded where I was at however 20 minutes a way there was devestation…….I know this post is a little bit different but we get so wrapped up in our routines and what we have to do that sometimes we forget what really matters most. I look at the devastation below and it could have just as easy been me and my family. Be thankful for everyday, and next time when you think that the problem at work is going to do you in remember stuff like this. In the grand scheme of things the things we worry about might not be so important. Please remember the people in Indiana during this time as they try to pick up and rebuild and for the many lives that were lost. Out of a tradgedy you will see communities bond together and buil each other back up.

How many times does it happen, you have this masterful plan of what you are going to do as soon as you get into the office and bam ~ haven’t even made it out of the car and emails start exploding and coming in.

I would gather with most that this occurance is not out of the norm. In fact it probably happens more times than not. Today, such an issue came up ~ I was anticipating working with some VLF files when a process decided to timeout, a process that we shall say is of the utmost importance. I will do my best to lay out the scenario.

The issue was narrowed down to one procedure. That procedure had one parameter being passed in. Here are initial steps that were thought of

• Check Indexes (found one to be missing so it was added; will say that Aaron Bertrand’s blog where I found the SQL Sentry utility a few months ago was very helpful in looking at the estimated and actual execution plans).

This did not improve performance. Initially the timeout period for the app has a threshold of 30 seconds. We were consistently hitting over 2 minutes.

• Check the SP – nothing really stood out. Had some views that were being selected but they were pretty straight forward.
• Check for any blocking (this was done multiple times throughout the whole assesment process)
• Check any other sytem abnormalities

So with the procedure still timing out not many other options remaining one of the team members recalled a session they sat in on ~ Parameter Sniffing. The parameter, after being read in was then set to an internal parameter to that stored procedure. Then the newly created parameter was then passed down through the procedure where needed.

Upon executing the proc it took a mere second!

I’ve done a little bit of reading on parameter sniffing and find it very interesting. I would suggest reading Greg Larson’s article ~ he has it laid out very nicely.

This is one aspect I enjoy of being part of a core DBA team. All backgrounds come together and bring different aspects to solutions. I look forward to the next problem to find that unique solution.

The title might be a little miss leading. “DBA Perspective” is solely my perspective in dealing with SSIS up to this point.

The list that I have tallied on how to proceed with configurations are as follows:

• XML Configurations – XML file that contains the SSIS configurations (can include multiples)
• Environment Variable – environment variable that contains configurations
• Registry Entry
• Parent Package Variable – This config type is more often used to update child packages
• SQL Server Table – a table inside SQL Server contains the configurations (can include multiples)

Now, the irony if you will, is that in my specific case we currently have a number of variations of packages that use many different methods – This to me = monstrosity

The two styles that seem the easiest for me are the XML Configurations or the SQL Server Table configuration. The preferred method of my choice is to store them all in a SQL table.

I have three tables that I use:

• SSIS_Configurations
• SSIS_File_Spec_Configuration
• SSIS_Log_Details

I’ll try to break down the schematics of each table:

SSIS_Configurations

• ConfigurationFilter – Name of process
• ConfiguredValue – Server Instance
• PackagePath – package connections
• ConfiguredValueType – self explanatory (string, etc.)

SSIS_File_Spec_Configuration

• Pkey – identifies multiple entries for one package
• JobIdentifier – name of package
• Apptype – what application is it a part of
• InputFileType – .txt, .csv, etc.
• InputFilePathType – is it a share, sql server, etc.
• InputFilePath – location of where the input file path is coming from
• SProcName – name of procedure utilized
• OutputFileType – .xlsx, .csv, etc.
• OutputFilePath – location of where the output file will be sent
• InputFileArchivePath – Archive location for the input file

SSIS_Log_Details

• ExecStartTime – start time of the SSIS run
• ExecEndTime – end time of the execution
• TaskName – name of Task being executed
• Status – what is the status of the package
• ErrorDescription – what was the error the package encountered
• Duration – duration of the package
• PkgName – package name
• SubPkgName – child package
• BatchId – self explanatory

This process has proved helpful for me; each DBA is different though and I can only say to find what works best for you in your particular situation and environment.

Everybody needs one; each business is different so what are you going to do? No, if you’re like me and the first thing you thought of was to call the Ghost-busters I’d advise against it (although re-living the Bill Murray glory days might not be a bad idea) . In reality, as DBA’s, we will have a need to trend patterns over time. It is inevitable and should be a way of life for us in most instances. How do we do it; or shall I say how am I going to do it. I don’t believe that I can specifically tell you how to meet your business needs; however after attending the Pass Summit this past year and getting some great ideas I think I’ve come up with something that works for me as a DBA.

I definitely want to recommend checking out Chris Shaw’s Utility Databases presentation he has made available on  his blog. It’s in attending his session at PASS that this idea spawned; I am part of a DBA team that falls into the tax / banking industry. We heavily support the tax side of things so for 6 months out of the year it is a slamming great time!! Needless to say 6 weeks is small compared to 52 weeks out of the year so capturing data is important to trend it out and see what is happening and occurring in the system.

I have chosen to capture a few things that will help me in the future troubleshoot and look at problem areas. Due to regulations I’m not going to dump my findings into my prod environment but will in turn opt to dump them into my local environment ( I will do this only because I do not have live production data in my local environment; just statistics etc.).

Needless to say I have my tables and schema set up to accommodate the following criteria (date time stamps of entries are recorded):

Server Level items

• Calculates average stalls per read, per write, and per total input/output for each database file
• CPU utilization by database
• total buffer usage by database for current instance
• Isolate top waits for server instance since last restart or statistics clear
• Signal Waits for instance
• Get CPU Utilization History for last 256 minutes
• Basic information about memory amounts and state
• SQL Server Process Address space info
• Memory Clerk Usage for instance
• Single-use, ad-hoc queries that are bloating the plan cache

Database Level:

• Individual File Sizes and space available for current database
• I/O Statistics by file for the current database
• Top Cached SPs By Execution Count
• Top Cached SPs By Avg Elapsed Time
• Top Cached SPs By Total Worker time
• Top Cached SPs By Total Logical Reads
• Top Cached SPs By Total Physical Reads
• Top Cached SPs By Total Logical Writes
• Lists the top statements by average input/output usage for the current database
• Possible Bad NC Indexes (writes > reads)
• Missing Indexes current database by Index Advantage
• Find missing index warnings for cached plans in the current database
• When were Statistics last updated on all indexes
• Get fragmentation info for all indexes above a certain size in the current database (the maintenance job we have will take care of fragmentation but I wanted to see what it was like after our jobs ran and before the next run)
• Index Read/Write stats

In the end, I’ll have a plethora of information to go back to after the “season” ends to review. If you don’t have a “utility” database I suggest you get one.