1: Case Sensitivity
I’ve never been particularly good at using case consistently, the perfect example of which was my publisher asking me to edit hundreds of figure captions in my recently published book. Like most people of my age who took a college course in programming, I studied C, and spent countless hours debugging problems traced back to the incorrect case of variable names (and using “=” instead of “==” !).
When I decided to specialize in SQL Server administration, I rejoiced at the lack of case sensitivity involved, although I occasionally see databases with the case sensitive option, in most cases (pardon the pun) installed my accident.
Imagine my angst when I discovered that a tool I’m spending more and more time with is rampantly case sensitive. Almost everything about SSIS is case sensitive; the expression language, variable names and one that gets me every time; lookups.
A very common SSIS data flow transformation is lookup. As the name implies, it’s most often used to lookup a key value based on a text value in the data flow. For example, in the figure below, we’re returning the supplierKey based on the supplierName value. The first screen defines the lookup source, and the second defines the join condition and what value is to be returned.
This, for all intents and purposes, in a simple join condition with a column from the joined table being returned. However, unlike T-SQL, the SSIS lookup is case sensitive.
The obvious way to address this is to ensure both the lookup column (vendorName in this example) and the matching data flow field (Supplier) are set to the same case using the UPPER (or LOWER) function. For the lookup, that’s a simple case of changing the lookup source T-SQL to use one of those functions. For the data flow field, we could use the derived column transformation using the same function, and use that column in the lookup process.
2: Strong Typing
If there was any remaining doubt that SSIS was designed for developers (and not classic DBAs) it’s confirmed in its (very) strong typing. As an example of that, let’s revisit the lookup example from above. A previous version of the database had the Supplier data type set to varchar(50) and vendorName set to nvarchar(255). When we try and connect these in the lookup, we’ll get the following error;
Another common issue with mismatched data types is when inserting rows into a table at the end of a data flow task. If the destination table’s column length is shorter than the source, you’ll get a warning such as this one;
Unless you have the luxury of an enterprise wide data dictionary (that every one adheres to), data type variances such as these are common, with the most frequent SSIS workarounds being to either CAST the columns in the data source selection (e.g.; using a view) and/or using type casts in a derived column transformation (more on that in part 2 of this blog series)
3: 64-bit Trickery
There will presumably come a day when everything works on 64-bit, and that day can’t come soon enough. In the meantime, we have to deal with a variety of annoying problems, one of which is 64-bit driver support in SSIS.
A common data source for SSIS packages are Excel files. The current (and all previous) versions of Excel do not include 64-bit drivers. This presents a problem when SSIS packages run in 64-bit environments, and the error messages returned are far from helpful (unless you consider “The AcquireConnection method call to the connection manager blah failed with error code blah” helpful).
Fortunately, there’s an easy workaround for this issue, via a property called Run64BitRuntime accessed through the Debugging page of the project’s properties window. As per the figure below, setting this property to False (it’s true by default) will use the 32-bit driver.
Note that there are a whole range of other considerations for 64-bit mode, for example when calling child packages from a parent with a different setting for this value. For a great blog post on these issues visit Todd McDermid’s post from last year.
4: Package Configurations
One of the real SSIS horror stories that I was very close to (but not responsible for!) was an SSIS package that was run against a production database by accident (blowing away gigabytes of production data). The package used a production configuration file instead of a test/development file. There’s a couple of aspects to this (all too common) problem; configuration technique and security.
Firstly, configuration technique. SSIS allows a number of ways of configuring settings such as server and database name. The most common one is to use an XML configuration file, the path to which is either stored within the package itself, or set through an environment variable.
I’ve seen problems with both of these techniques. Using a configuration file location (as per the example below) assumes that all servers on which the package runs has the same path available, which is quite often not the case.
The environment variable option can also present some problems, notably the need to reboot the server for the variable to come into effect. Depending on the environment, rebooting production servers is not the easiest thing to do.
Another alternative to both of the above is one that I discovered while listening to Greg Low’s SQLDownUnder podcast with Jamie Thompson in which Greg spoke of the idea of connecting to a special configuration database which stored the configuration settings. The settings returned were based on the calling machine’s context and then used to set the value of package variables for subsequent use.
Regardless of the configuration technique, one of the really important considerations is security. In each of the above techniques, mistakes can still be made. For example, a production configuration file can be copied over the top of a development file, or the incorrect settings stored in a configuration database.
To prevent these types of configuration errors from killing a production database, we need to ensure that the security context of an SSIS package prevents it from accessing the wrong environment. There are at least two ways of achieving this; using separate service accounts in each environment (and making sure the account only has database permissions in the appropriate environment), or better still, having the production database in a separate domain with trust permissions removed to the other domain(s).
With the appropriate security setup, even if the wrong configuration file is used, the package will fail to run due to the lack of database permissions.
5: Miscellaneous Silly Business
Finally, there’s a number of really silly things I continue to do that I shouldn’t (I’m a slow learner). Firstly, the evaluate as expression property.
A common design pattern in SSIS packages is to use a variable as the source for an Execute SQL task. The variable’s value is then set using an expression which references another variable. A common example of this is a variable that contains an update command with a where clause containing the value of a BatchID variable. The expression property contains the code that references the other variable.
If you use this type of arrangement, make sure the variable's evaluate as expression property is set to TRUE, as per the example below. I’ve spent many hours debugging problems where I’ve simply forgotten to set this properly.
To wrap up this post, I frequently want to add an existing .dtsx package (e.g.; from another project) to the current project. By default I right click the SSIS Packages node in solution explorer and choose the .dtsx package that I’ve already copied into the project directory. I’m then confused as to why the package is renamed e.g.; from Package.dtsx to Package(1).dtsx.
It turns out that the Add Existing Package menu is really designed for adding a package from a different location (i.e.; remote server), and if you use it locally, it makes a copy (and renames) the file.
For the situation I described, using the Add > Existing Item option (instead of Add Existing Package from the SSIS Packages menu) is the correct option. The figure below compares these 2 options side by side.
In the next post, I’ll talk about some important SSIS performance tuning tips.
Cheers
Following on from Jonathan, Grant, Marco, Andy & Louis, here are the results from the 40 returned evaluations of my "DBAs Behaving Badly" session at PASS 2009;
This was the first time I've spoken at PASS (hopefully not the last!), and the first time I've spoken in front of a large audience. I'm blown away by the result, and really enthusiastic about preparing my next presentation.
To everyone that came along, thanks for laughing at my jokes ... or laughing at me ... either way, thanks :-)
Cheers
The primary goal of any BI solution is to provide decision makers with the information they need to make better decisions, leading to higher profits, lower costs and improved service levels. Successful BI solutions enable this by automating the extraction and transformation of data into a shape that facilitates agile reporting and analysis.
When working with organizations looking to build their first BI solution, I've found that building them a prototype is a great way to show them the types of things that are possible with BI solutions. This process assists them in answering the questions used to document the formal business requirements upon which their real BI solution will be built.
It's interesting to observe their reactions during the demonstration of the prototype. For organizations that are used to manually collating figures and producing canned reports, the speed with which a BI solution can slice and dice the numbers often blows them away, which is nice, but it can also have a side effect of alienating the very people who are critical to the success of the project.
It's very easy to build a slick looking prototype and present it under controlled conditions. It's much harder to build a real solution that consumes real data, much of which is unstructured, inconsistent, and, in some cases, plain wrong.
One of the primary reasons BI projects fail is poor data quality, leading to inconsistent results and errors during data loads. Such outcomes chip away at the confidence in the system, and in some cases, precipitates the cancelation of the project itself. I wrote about this in more detail in my blog post titled Top 5 reasons why your Business Intelligence project will fail.
Avoiding data quality issues requires a good working relationship with the custodians of the source data. These people are often the same people that produce the existing reports that the BI system will ultimately replace, and therein lies the paradox; why would they help you to build a system that makes them redundant?
Human beings, rightly or wrongly, are resistant to change, particularly when the change has a perceived impact on their livelihood or professional value. In the context of a new BI solution, data custodians often view the solution as a threat to their value in the organization. They’re used to being in charge of large amounts of data, and making sense of it by producing reports. They feel threatened by a new system which promises to automate large parts of their work and produce much more flexible reports.
Successful BI projects acknowledge this threat/perception early. They engage data custodians and report writers in the early stages, and involve them throughout the solution lifecycle. They make them stakeholders in the solution, and value their organizational knowledge as a crucial ingredient in the overall success of the project. Perhaps most importantly, they address the value they will continue to play in the future. They map out how their role will evolve from basic data collation/reporting to more advanced analysis, and therefore provide increased value to the organization.
Some would argue that this is a mere ego stroking political exercise to be indulged on an as-needed basis. I argue that it’s a real challenge to be met early, and one that has measurable and ongoing impacts, and may just save the project from being undermined by those with a vested interest in its failure.
Cheers, and happy holidays :-)
Rod.
Perhaps "schism" is a bit strong a word for what I'm describing in this post, but there's no doubt the role of a DBA, particularly a SQL Server DBA, is simultaneously splitting and changing, albeit in a gradual and peaceful manner.
In the forward to my book, Kevin Kline wrote of institutional knowledge as the accumulated wisdom of many individual practitioners across many years. The very fact that I was able to write a SQL Server book is direct evidence of the power of accumulated wisdom. Nothing (or very little) in my book is material that cannot be deduced from a number of other sources; my aim from the start was to present a single (and easy to digest) reference that encapsulated the accumulated wisdom of the role of SQL Server DBA, and I hope that I succeeded in that task.
In years gone by, the very best SQL Server DBAs were those that knew all the back doors and secret scripts and switches to squeeze out the best performance whilst avoiding the classic mistakes of those new to the role. Those DBAs still exist, but their power and mystique is diminishing. Together with well documented and easily accessible accumulated wisdom, new and improved features in recent versions of SQL Server have smoothed out the learning curve, making the role of SQL Server DBA a lot easier and more approachable than it once was. As a result, existing DBAs are spending more time accumulating knowledge in complementary areas, and new DBAs are coming into the role with a specialist angle in mind.
I've listed below four different types of DBA role. Most DBAs I know currently perform tasks from at least two of these, but over time, I think these roles will become increasingly separated and practiced by those with an expertise in one area.
Administration DBAs
The days of having a specialist DBA on staff to administer a small number of databases are drawing to a close. These tasks will be consumed by other administration staff (Windows, Exchange etc ....) who'll be expected to pickup the required SQL Server skills as part of their "hybrid" administration role. As such, the number of part time, or "involuntary" DBAs will rise, increasing the need for (and value of) accumulated DBA wisdom.
What's becoming very clear is that those that remain (or enter) the dedicated SQL Server administration space will need to specialize in administering large numbers of servers, typically in data centres and hosted environments. Their specialty will be the mastering of proactive administration (alerts, baseline analysis, etc ...) and will be the prime beneficiaries of new SQL Server features such as Policy-Based Management, Management Data Warehouse (MDW) and Resource Governor. They'll also employ their knowledge of scripting languages such as Powershell to further streamline their administration processes.
These DBAs will also be comfortable with virtualization (ESX/Hyper-V) and consolidation techniques, and will be able to hold their own in conversations with storage administrators regarding SAN configuration. Their deep knowledge of dynamic management views (DMVs) will enable them to pinpoint and correct database performance problems, and they'll be comfortable with disaster recovery planning, implementing least privilege security and high availability principals.
Development DBAs
Development DBAs will spend most of their time with the database before it enters production. Their specialty will be logical and/or physical modelling, writing stored procedures and dealing with CLR, LINQ to SQL and Entity Framework (EF) performance and manageability issues. In addition to T-SQL programming, they'll be strong in one or more other development languages such as C#, and will spend most of their day in Visual Studio and TFS.
The emergence of geospatial data will see a sub-branch of development expertise for mapping applications.
Data Tier Architects
The Data Tier Architects will be the ones that float around in meetings scribbling clouds on whiteboards. They'll be responsible for designing the interfaces to the data, and how the data is accessed, replicated, and synchronized. Their knowledge of SQL Server replication and scale out solutions such as shared scalable databases will enable them to design high performance platforms for the benefit of the various applications that consume and populate the data.
Business Intelligence & Data Warehousing DBAs
Finally, the years to come will see a continuation (and acceleration) of the rollout of business intelligence & data warehousing projects. DBAs in this field will be those with a mastery of Integration Services (SSIS), Analysis Services (SSAS), MDX and Reporting Services (SSRS). True "data mining" projects will become more common, with the consolidation and conversion of legacy data systems.
So, the question is this ... What sort of DBA will you be in 10 years from now?
"I'm gonna need you to go ahead and come in tomorrow..."
"Oh.... and I'm gonna need you to come in on Sunday as well ... mmmkay?"
I can joke about this, but for some of you this is probably all too real. In that context, let's examine some common worst practices, beginning with ....
Manual Administration
One of the common things I come across are DBAs who start with a very small number of servers to manage, and they manage them using a very manual technique. By that I mean using Management Studio wizards, for example, to backup databases, check logs and so forth...
Sometimes those DBAs are part time or “accidental” DBAs, for example, developers that have taken on a DBA role, or sometimes they just fall into the process of doing things in a manual manner.
Now managing one or two servers like that might be fine, but the problems begin as the number of servers increase, and unless the technique changes, those DBAs often end up running around in circles responding to whoever screams the loudest, which is not cool.
The fact is, there is simply no substitute for automation - automation enables more things to be achieved with fewer mistakes in a given amount of time. And what that means, is that there’s more time in the day for more pleasurable things – training, reading the newspaper, beer, golf or whatever floats the boat.
Essentially, if anything needs to be done more than once, it should be scripted, and created in a manner that enables it to be scheduled for execution, with a monitoring and alerting component placed over the top.
The other issue at play here is that during disaster recovery situations, scripts are absolutely crucial in getting things restored as quickly as possible – The alternative, that is running around manually poking things while under pressure, is rarely a good look.
Let’s continue by taking a look at the importance of alerts.
Not defining Alerts
One of the things that becomes very clear when dealing with a large number of servers is that you end up “managing by exception”. What that means is that you spend all of your time dealing with the things that go wrong, and the more servers you have, the more things that go wrong, increasing the need for proactive management.
Now, there’s two approaches to discovering things that go wrong; The first is to sit back and wait for people to start screaming, and the second is to define alerts, which help us discover problems before users do.
Alerts enable us to define the acceptable operating conditions, and to be notified when something falls outside that range – and that’s really the only efficient option for large environments.
For example, at a minimum, we should define alerts for SQL Agent job failures and high severity errors, but also for performance conditions. For example, we could define an alert for when batches/sec or CPU utilization reaches a critical level, at which performance is known to degrade. That would enable us to take actions, such as shutting down non essential processes like ad hoc reports or batch archiving.
One other thing I wanted to mention before moving on was what type of errors we should be creating alerts for. The classic alert setup is for errors of severity 19 +, but as per this post from Paul Randal earlier this year, such a scheme often misses important events such as error 825.
One of the many really cool things that Tibor Karaszi has done is create a SQL Agent Alerts Management Pack, which is essentially a T-SQL script to create alerts for all of the major errors and events that we should be looking out for, including error 825. So in the absence of anything else, this is a great starting point.
Before finishing, let’s cover off one final thing, and that’s the creation of a task list.
No Task/Check List
Regardless of the level of automation or 3rd party monitoring, one of the things I think is important is having a basic checklist of daily, weekly and monthly tasks.
Checklists are essential in many industries, aviation for example, and provide the framework within which we can automate jobs, bring newly hired DBAs up to speed, and various other things. So included here is a checklist I like to use myself.
Obviously a lot of these checks can be automated – The important thing is having a documented starting point from which to automate.
In closing, it would be silly of me to write this post without talking about my favorite new feature in SQL Server 2008 ...
Policy Based Management (to the rescue)
Imagine for a moment you’re the new DBA of a company with thousands of servers across development, test and production and you’re asked to ensure all servers adhere to best practices.
If you were handy with scripting languages such as powershell, that would certainly help, but without those skills, you’d be basically manually checking and fixing each server – A massive task, and by the time you finished, how could you be certain that none of the servers had reverted back to a poor configuration?
Policy based management is purpose built to address this problem.
Essentially what we can do is to start moving from “exception” based management to “intent” based management – in other words – “make this production server like all those other production servers". Best of all, when something happens that causes it to deviate from the intended state, we can either prevent the change (depending on the type of change made), or be alerted to it.
For those not familiar with this new feature, check out this interview with Dan Jones. It's the only way to fly.
Cheers,
Rod.
The other night I was preparing the indexing component of my worst practices session for this year's SQLPass summit, and I was confused as to why the contents of the sys.dm_db_index_usage_stats was completely empty after I'd just ran a script to "warm up" the database by running some queries.
It turns out that I'd enabled the AutoClose option on the database in order to trigger an alert from my Policy Based Management policy I'd setup the previous night. Having the memory of a aging fish, I'd completely forgotten about it. After running the warm up script, I closed the query window, which triggered the autoclose event, as that was the only remaining connection to the database.
DMV contents are reset under the following conditions;
When assessing the contents of a DMV, be sure to think about when any of the above events last occurred.
Cheers,
Rod.
PS: My book is at the printers :-) Read Kevin Kline's forward here
The first is developers who don't create indexes either because they don't know what they are, assume the DBA will take care of it in production, or assume they're not needed thinking that production performance will be comparable to a small development database.
The common outcome from this scenario is gradually declining production performance, often leading to false assertions about the adequacy of the hardware and/or SQL Server itself.
The second source is DBAs without index analysis knowledge, who are unsure of how to go about the process of determining which indexes are missing, which are not used, and which require maintenance.
A common outcome from this second group are databases that look like they've been carpet bombed with indexes by someone hoping that performance will eventually improve with the addition of more and more indexes.
So for the first of our three indexing worst practices, let’s look at situations in which there are either too many or not enough indexes.
Too many/not enough
Not having the required indexes will cause SQL Server to read more data than necessary from disk, leading to more disk I/O and therefore less available memory - a classic double whammy.
Almost as bad, having too many indexes leads to more overhead for data modification statements – SQL Server has to maintain the indexes even though they’re never actually used.
The first thing to say about these two situations is that with an adequate load testing environment, this should never really occur in the first place. If the database is stressed with the expected level of production activity, most of these problems would be identified before they become apparent in production.
Having said that, load testing is always a bit of a guessing game as to what the expected production load will be, and depending on the load testing tools and techniques, may not produce an accurate load profile anyway.
So we need to ensure that we’re analysing index usage and fragmentation on a regular basis, and in this regard, both SQL Server 2005 and 2008 have made this process much easier.
Rather than regurgitate Books Online, I’ll point you in the direction of this blog post from Bart Duncan on discovering missing indexes and this blog post from Jason Massie on analysing index usage.
Updating Statistics after Index Rebuild
So now for our second worst practice, and I have to say one of my favourites, and that’s updating statistics after an index rebuild.
SQL Server keeps statistics on both indexes and columns which help it decide how it will process each query. Because certain index lookups require two actions for each matching row, that is, look up the index, and then look up the table entry, for queries that will return a lot of rows, it may be quicker to simply scan the table rather than use the index.
Table scans use sequential I/O, index lookups use random I/O. Because moving the disk heads around during random I/O is much slower than scanning a sequential chunk of data, it’s important that SQL Server only use random I/O index lookups for queries that return a low percentage of rows compared to the total table size.
In order for SQL Server to determine the estimated number of rows to be returned from a query, it keeps statistics on both indexes and columns. For example, it will store the fact that a given table contains a million people with the surname “Smith” and only two with the surname “Zatorsky”, and that gives it the best chance of determining the best query plan to use for a surname based lookup, i.e.; table scan or index lookup.
So getting backup to the worst practice, I refer your attention to the following screen shot of a SQL Server 2008 Maintenance Plan.
You’ll notice on the left of screen, we have 2 steps, the first to rebuild indexes, and the second to update statistics. The properties window on the right side of screen contains the default values for the update statistics task.
One of the things that happens when rebuilding an index is that the statistics for that index are updated “in full”, that is, the statistics will be perfect, given that SQL Server has to read every single row when rebuilding the index.
So after rebuilding it, there’s no need to update the index statistics. If you do, you’re effectively building the index statistics twice, and for large indexes, that’s an unnecessary and costly overhead.
You’ll also notice in the scan type section, the choice between “Full scan” and “Sample by”. The "Sample by" option is designed for speed, whereby it will sample a certain percentage of rows rather than the full set – the idea being a trade off between build time and statistics accuracy.
In earlier versions of SQL Server, this was a particular problem, as the "Full scan" option at the bottom there, wasn’t available in the maintenance plans, so people would often rebuild an index, and then proceed to replace a full set of statistics with a sample set. In 2008, the default option, as you can see, is a full scan, which means that rather than replace perfect stats with sample stats, they’re rebuilt twice – still bad, but probably better than replacing good stats with less accurate ones.
So, the recommendation here is that after rebuilding indexes, rather than update "All existing statistics", we update "Column statistics only", shown above by the red bar around the option. Column statistics are not touched by the index rebuild process, so using this option limits the work done by SQL Server, and in the case of very large databases, this option can save a massive amount of time, therefore shortening mainatenance windows.
Inappropriate Index Maintenance
As equally important as having the right amount of indexes, is making sure that indexes are appropriately maintained. Like any other disk object, indexes become fragmented over time through the normal course of inserts, deletes and updates. In addressing this, indexes in SQL Server can be either rebuilt or defragmented - now referred to as “reorganized”.
Compared to defragging or reorganizing an index, rebuilding will achieve the best result from a fragmentation perspective, however, there are a couple of downsides.
Firstly, unless you have the Enterprise edition of SQL Server, rebuilding an index is an offline operation, meaning that the index is unavailable during the rebuild.
Secondly, rebuilding an index is a fully logged operation, meaning that the transaction log will grow much more when compared to reorganizing, and for installations which use Log Shipping or Database Mirroring, this can have a significant impact, particularly when using high safety mode mirroring or when disk space is a problem.
So when I say inappropriate index maintenance, what I’m talking about is really one of two things; either not maintaining them at all, or choosing rebuild over reorganize or vice versa.
The classic rule of thumb is that for indexes with a 5 – 30 % fragmentation level, we defragment / reorganize, and indexes with more than 30% we rebuild, assuming we can either cope with the outage, or we have the Enterprise edition of SQL Server. This technique usually delivers the best bang for the buck – a minimum amount of disk usage and overhead for the maximum amount of performance gain.
From an fragmentation analysis perspective, sys.dm_db_index_physical_stats is a crucial component of any index maintenance plan. One of the great things we can do with this DMV is use it as part of an automated script, and Books Online actually provides the code to do exactly that.
Included below is a screen shot from the “sys.dm_db_index_physical_stats” article in Books Online. It shows some of the code from a cursor that loops through each index in the DMV looking at the fragmentation level, and depending on the percentage, either rebuilds it or reorganizes it.
All of this code is freely available in Books Online, and provides a much better alternative than a simple rebuild of all indexes every night, or worse, no maintenance at all. For more boutique and full featured solutions, be sure to check out Ola Hallengren's solution and Michelle Ufford's solution.
In the next post in this series, we'll examine DBCC worst practices.
Antidotes to the above;