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.

One of the many perks I have of being on a DBA Team is the number of ideas that are bounced around to find different solutions. One project that we have ran across that we are working on implementing is enforcing policies based on our newly stood up CMS server. The CMS server is nice simply because we can have rapid deployment among 90 some servers.

Not all of these servers will have all of the policies pushed out to them; some might only  have 3 some might have 23 who knows. In order to alleviate some of that pressure we split up our servers into 4 groups within our CMS Server; basically segregating out test boxes, prod boxes, some straggling 2000 boxes (due to third-party apps), and boxes the group doesn’t maintain but still nice to see what they are.

I’m big on  having a plan and road map in place then executing that plan; our team is deciding which group we’d like to tackle first, then choose the policies (standard) out of the box to push first. We can come back in a separate phase and tackle custom policies.

Another great aspect I like about the CMS server-side of things is that we now have a one location repository with all of our server information that we are responsible for. In the repository table we capture the following pieces of information:

• Server Name
• Instance Name
• Server Name \ Instance Name (reporting purposes)
• Domain
• IP Address
• Port
• Memory Size
• CPU Count
• Current Version of Windows
• Windows Name
• SQL Server Product Name
• SQL Server Version
• SQL Server Edition
• SQL Server Service Pack
• SQL Server Language (eh why not throw it in)
• SQL Server Root Directory
• SQL Server Collation
• Methods
• Business Owner
• DBA Notes (in case we need to add special info on the servers)
• Maintained (Do we maintain this server or not; in our industry some servers we do not maintain but we are aware that they are there and in place)
• Category (prod, test, dev, etc)

Having one central location with all this information has helped tremendously. In the next few posts I’ll go over the policies of what are considered some best practices. Each business is different so tailoring them to meet your needs is the fun part.

Let the games begin!!

I haven’t been able to post as much as I would like to and am working on changing that this upcoming year; building it more into my schedule. As this year begins I want to take a moment and implore my readers to learn something new this year. As a SQL DBA, I am always trying to evolve my techniques, learn new ways of doing things, enhancing productivity, streamlining processes, etc. In order to do this I have to stay on top of my game  – always reading articles, blogs, doing extra work on my own time, conferences, SQL Saturday’s.

So I ask the question, what are you going to do in 2012? Will we stagnate? I hope not….take this not as a rallying cry but more of an encouragement; there are so many opportunities out there to get involved in the community, helping others, and just learning in general.

As a SQL DBA my door is always open to help as is so many others. Good luck this year and I wish everyone the best in their endeavors.