Dirty Reads ...

Fun with SSIS - Part 1 - Troubleshooting

Although I still consider myself a DBA, the last few years have seen me spend more and more time in the Business Intelligence development space (SSIS development in particular). In this the first of a 2-part SSIS series, I’ll be focusing on a number of SSIS “issues” I’ve discovered the hard way.

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

PASS Presentation Evaluation Results

The results are in ....

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

MVP!

On Saturday morning I woke up to news that I was granted the MVP award for 2010 (my first MVP award). That capped a stellar year for me during which, among other things, my book was published, I spoke at the PASS summit in Seattle, and I had the honor of being involved in the tech editing of the brilliant SQL Server MVP Deep Dives book.

Each of those things was huge, but for all of them to occur in one year was massive. Receiving the MVP award is both overwhelming and exciting, and I hope that my actions in 2010 will do the award justice.

Thank you to all who have supported and encouraged me (you know who you are!) in particular Kevin Kline, Paul Nielsen, the folks at Manning Publications, and my local user group here in Australia run by fellow MVP Peter Ward.

As I write this, I’m organizing my schedule to attend the MVP Summit in February. If you’re going and are interested in sharing a room, drop me a line at rodcolledge at gmail dot com

Cheers, and have an awesome 2010!
Rod.

Business Intelligence Roadkill ... and the Politics of Change

One of the challenges of being a consultant on a Business Intelligence project is dealing with employees' perceptions of how the project will impact on their value within the organization. More on that soon ...

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.

The Great DBA Schism

Schism n. A separation or division into factions

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?

DBAs Behaving Badly (7/10): Manual Administration

Imagine for a moment it's late one Friday afternoon, and like a scene from Office Space, you try and avoid the boss on the way out the door. Before you make it out, he grabs you and tells you that you've just got another 10,000 SQL Servers to manage ....

"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.

DMVs and Autoclose

Its a well known fact that restarting SQL Server will reset the contents of most Dynamic Management Views (DMVs). What's less well known are the other ways these DMVs are reset.

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;

1. SQL Server restart,
2. AutoClose events,
3. Taking a database offline

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

DBAs Behaving Badly (6/10): Indexes

In my experience, indexing problems can typically be traced back to one of two sources.

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.

Top 5 Reasons Why Your Business Intelligence Project Will Fail

1. Lack of business support; BI projects set themselves a much higher bar compared to “normal” projects. When (and I intentionally exclude “if”) the project is delayed, or fails to fully meet the expectations it sets for itself, having the support of people with a decision making capacity is critical.
2. Poor data quality; Other than lack of business support, nothing kills a BI/Data Warehousing project quicker than poor data quality. Other systems may be able to hide poor quality data, but it’s fully exposed in a BI solution. The two most common outcomes when dealing with poor data quality are ETL processes failing, for example, duplicate primary keys from unexpected data patterns, and decisions made on the basis of incorrect data. In such cases, the reliability and accuracy of the system will be questioned, and the business will then start to question the investment in the system itself. See point 1.
3. Trying to be all things to all people; Promising the business a swift passage to the land of milk and honey often leads to disappointment when the high expectations cannot be met. Terms such as “Data Mining” and “Predictive Data Analysis” roll off the tongue, and powerpoint presentations make these things seem dead simple; the reality of course is that they’re difficult to implement properly, and very few projects deliver on the promise sold to the business.
4. Early release of Ad-Hoc reporting tools; Continuing on from the previous point, projects that try and solve every problem ASAP often fall into the trap of releasing ad-hoc reporting tools (e.g.; Microsoft Report Builder) early, convincing themselves that the users can help themselves to the data however (and whenever) they like it. The problem of course, is that trying to build report models that enable such user access whilst avoiding performance problems and user frustrations is very difficult. Further, letting users help themselves often means that the ability to capture business requirements (and therefore build an appropriate BI solution) is compromised. See my previous post on this topic, Herding Cats: Dealing with open ended reporting requirements.
5. Not employing BI specialists; BI specialists are hard to find (and they’re typically expensive). There’s a world of difference between designing a database, designing an application and designing multi-dimensional cubes (properly). Don’t fall for the trap of expecting a DBA or .Net developer to pickup the skills needed, and therefore save money on staff overhead.

Antidotes to the above;

1. Identify an influential sponsor early, keep them regularly updated of progress and risks, and give them the information they need to communicate to the rest of the decision makers.
2. Ideally, ensure the source systems have a rigorous data validation module (DVM) built into them to prevent poor data from becoming an issue in the first place. Failing that (almost always the case) ensure the BI solution’s ETL processes cater for a vast array of potential data quality issues and either prevent dubious data from entering the BI solution, or build data quality reports to communicate the problems back to the administrators of the source systems.
3. Start small. Be agile. Be realistic. Fully solve a small number of business problems properly before attempting to solve everything at once. Use your business sponsor to spread the word, and feed off the early successes.
4. Resist the temptation to release ad-hoc reporting tools as long as possible. Understand the business requirements for user reports, and build the appropriate canned reports or cubes to meet those requirements. Such solutions are much more controlled, and therefore have a much better chance of meeting the performance and functional expectations of the largest number of end users.
5. Engage with appropriately qualified BI specialists as soon as possible, ideally those that have experienced failure on a previous BI project. Their skills and experience will save you a massive amount of time (and money) in the long term, despite the short term spike in staff costs.

DBAs Behaving Badly (5/10): Data File Management

In the last post in this series, we investigated common problems with storage configuration. A closely related topic is that of data file management, something I’ll be focusing on in this post.

A well known attribute of SQL Server is its ease of use. In contrast with other database management systems, it’s a piece of cake to get up and running, and as many of you would agree, that’s not always a good thing.

SQL Server is designed with good out-of-the-box settings that strengthen security, reduce administration overhead and maximize performance, however, when it comes to individual databases, there’s a number of recommended configuration steps that SQL Server doesn’t perform, in large part due to dependencies on disk configuration and unknown future usage of the databases. In this post, we’ll concentrate on three such settings; autogrowth, file size and the recovery model.

Relying on Autogrowth

When a database is created using the default settings, it’s sized as per the model database, which by default has a 3MB data file set to autogrow in 1MB increments, and a 1MB log file with 10% autogrowth. Autogrow events occur when the files fill to capacity and automatically grow, therefore avoiding “out of space” errors. For very small test or development databases, that may be ok, but what about a large production database that’s growing rapidly? In such a case, the database would be constantly auto-growing every few seconds (or less). So what’s the problem?

Fragmentation; If a database’s files are sized up front for the expected growth over the medium term (let’s say 12 months), the disk space is allocated in one large chunk. In contrast, files that are constantly filling and growing have space allocated in small chunks from whatever disk space is free at the time. The end result is high levels of file fragmentation, a particularly nasty problem for the transaction log file.

Performance; There’s a performance overhead involved in each file expansion. You don’t need to be the sharpest tool in the shed to understand that a file that’s constantly auto-growing will incur a much larger performance overhead compared to one that’s appropriately sized for the expected growth. Instant File Initialization helps in this regard (and should be enabled in almost all cases), but can’t be used for the transaction log, which is arguably the most important file from a performance perspective.

Associated best practice; Autogrowth should not be used as a substitute for good capacity planning and proactive database maintenance. Configuring files with the autogrowth property is fine in handling unexpected surges in growth, so long as the files are appropriately sized in advance, and appropriate monitoring and alerting is in place for disk usage and autogrowth events.

Failing to presize tempdb

Those that rely on auto-growth for databases almost always fail to pre-size tempdb. The unique thing about the tempdb database is that when SQL Server is restarted, tempdb is recreated with the original file sizes. As an example, let’s say that over the course of normal production activity, the tempdb database grows to 20GB. When SQL Server is restarted, the tempdb database will be reset back to the defined size and growth increments, which by default is 8MB with 10% autogrowth. Such a setting would require close to 100 autogrowth events to return the file to the required operating size, bringing with it the corresponding performance and fragmentation overhead. In such cases, the classic outcome, particularly for databases with a heavy reliance on tempdb, is sluggish performance for a period of time after SQL Server restarts.

Associated best practice; Pre-size the tempdb database after observing the production system that’s been up and running for long enough to cover the full range of activities (index rebuilds, dbcc checks, application activity, reporting etc …) All of these things use tempdb, some more than others, and the correct value to use is therefore site specific. Of course, before the system goes into production, an educated guess is required, but a value greater than the default 8MB may be a good place to start!

Full recovery model with no transaction log backups

In the first post in this series we covered backup worst practices, the first of which was not taking backups. One of the less than obvious variations of this practice is not taking transaction log backups on the assumption that a full database backup will “take care of it”. It won’t. A database in the full recovery model (the default for the Standard and Enterprise editions of SQL Server) will retain transactions in the transaction log until an explicit transaction log backup is performed. If transaction log backups are never taken (frighteningly common), the log continues to grow forever.

The classic outcome of situations such as the one just presented are databases with a 20MB data file and a 900GB transaction log file. In most of these cases, the disk eventually fills to capacity and the system falls over with 9002 errors (out of disk space). What happens next is usually a combination of panic-induced hysteria and logic-defying feats of ignorance.

Among many other critical functions, the transaction log is used to ensure the integrity of the database by rolling forward committed transactions and rolling back uncommitted ones. Such a process is performed when the SQL Server instance is restarted. Let’s take the case of the full disk scenario presented earlier; someone notices the 900GB transaction log file and, in a moment of clarity, decides it’s not really required, so they delete it (after stopping SQL Server to remove the file lock). When SQL Server restarts, the usual roll forward/back process cannot happen, given the absence of the transaction log, therefore resulting in a potentially (probably) corrupt database containing half completed transactions.

Associated best practice; Backup the transaction log frequently, or use the simple recovery model if point in time restore is not required (and data loss since the last full backup is acceptable.)

In the next post in this series, we’ll examine indexing worst practices.