Dirty Reads ...

Using Policy-based Management to Check Sysadmin Membership

I was sent an email recently from a reader of my simple-talk article Policy-based Management and Central Management Servers asking about the usage of the @WindowsUsersAndGroupsInSysadminRole property to detect the existence of sysadmins outside a known base; in other words, a scheduled check to see if unauthorised users had gained access to the sysadmin role.

As much as I love Policy-based Management, the documentation and error messages leave a little to be desired, so it wasn't immediately obvious how to use this property to achieve the desired result.

Here's the solution...

The trick is using the Array function as shown below. "Field" is simply @WindowsUsersAndGroupsInSysadminRole, but "Value" uses the Array function to convert a CSV list of domain accounts (AD groups or users) into an array data type for comparison with the array data type returned by the @WindowsUsersAndGroupsInSysadminRole function.

On my StrataDB development environment, the results of this policy execution is shown below;

The policy failed because the actual list (array) of sysadmin accounts includes more than the expected list (array).

Hope this helps.

Cheers,
Rod.

Built to last .... for how long?

One of the enduring memories from my childhood was my father bemoaning the design and build quality of contemporary objects. Whether it was the aesthetics of a newly constructed building or the durability of a recently acquired television, the song remained the same … “They don’t build ‘em like they did in my day”.

In many ways he was right, although what he meant by “my day” was never well defined. Presumably “his day” included the construction of the pyramids and the Sistine Chapel! His point, while imbued with plenty of artistic license, raises a good question; Are we less concerned with build quality today?

In exploring this question, let’s use a 2 year old cell phone as an example. If my 2 year old phone stops working, do I send it away for repair? No, I buy a new one because a) it will probably be cheaper than repairing the old one and b) the technology has improved so much that I’ll get a much better phone anyway. The manufacturers know this, so they have no real incentive to engineer products that outlast the warranty period.

In that context, when it comes to database design, there are a number of important questions we need to ask ourselves as database professionals. Firstly, how much time should we spend engineering designs that achieve maximum performance when modern hardware can easily mask the effects of poor design, and secondly, with the increasing emphasis on timely delivery of working solutions, how much time should we spend on flexible designs that cater for unknown future usage scenarios?

The answer to both of those questions is “it depends”. A purist would argue otherwise, but a pragmatic design approach suggests that we take advantage of modern hardware and spend less time wringing every last drop of performance from our designs. In a similar manner, a certain element of design flexibility can deliver future cost savings, but should not be used as a substitute for gathering business requirements at the commencement of a development project.

Flexible and high performance database designs are often the unconscious side effect of using an experienced designer, someone whose default approach to database design has been shaped over many years of experience. Those same people often cringe when they see the work of less experienced practitioners whose designs only work due to the modern hardware they run on.

In terms of design flexibility, an area I often target is the relationships between entities. For example, a classic entity relationship is between a product and a product category. There’s a number of ways of representing this relationship. We could store the category as a column within the product table, or create a separate table for categories and include a foreign key in the product table. In both cases, there are inherent design limitations here; What if we need to include subcategories, or add a product to more than one category? With a little bit more thought, we can create a many to many table between products and categories, and design a parent-child relationship within the categories table. Such a design provides a lot more flexibility, and the cost of implementing the design up front is orders of magnitude cheaper than retrofitting the design at a later point.

Performance can be seen in a similar light; the system may perform fine with the current production load, but what happens if the user base triples overnight? Depending on the application design, throwing more hardware at the problem may not be a viable solution.

While this post doesn’t provide any answers, I hope that these questions make you think about the database design process a little more deeply, and perhaps help you to build a solution that stands the test of time, and maybe even make my father stand back and admire your creation :-)

Cheers

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.

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

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.

DBAs Behaving Badly (4/10): Storage Configuration

Performance tuning SQL Server applications involves finding and addressing performance bottlenecks. While there will always be a bottleneck somewhere, the goal is to reduce the bottlenecks until application performance meets or exceeds the usage requirements, typically defined in a service level agreement (SLA).

Although it’s undeniable that the largest performance gains usually come from good application design, inadequate hardware makes resolving performance problems much more difficult. Poorly designed storage systems account for arguably the largest percentage of hardware-based performance problems for SQL Server solutions, and fixing them is usually more complicated than a simple memory or CPU upgrade. It follows that a well-designed storage system removes the biggest hardware-based performance obstacle, and that storage design should therefore lead the way in sizing servers for use in SQL Server environments.

Let’s take a look at three common storage configuration problems, beginning with the capacity-centric design.

Capacity-centric design

When designing a storage system for SQL Server, one can take a capacity-centric or a performance-centric approach. To highlight the difference between these approaches, consider a requirement for a 200GB database. How many disks do you need? A storage system with a capacity-centric design would possibly have three 73GB disks, or perhaps more in a RAID configuration. In contrast, a system designed using the performance-centric approach would be vastly different, and wouldn’t even consider the space requirement until much later in the process.

Performance-centric designs start with consideration of the expected workload, and the IO per second (IOPS) capacity of the disks. For example, let’s assume that our application produces a peak load of 1200 reads and 400 writes per second, figures obtained during a profiling exercise in a test environment. Using these figures, we’d calculate the required number of disks as follows;

Required # Disks = (Reads/sec + (Writes/sec * RAID adjuster)) / Disk IOPS

For our example, assuming an average IOPS of 125 and a RAID 10 design, this equates to;

Required # Disks = (1200 + (400 * 2)) / 125

... giving us a total of 16 disks. You’ll note that we haven’t addressed storage capacity requirements yet. This is a deliberate decision to ensure the storage system is designed for throughput and performance as the highest priority. In this example, assuming we used 73GB disks, we’d have a total available capacity of 1.1TB. Usable space, after RAID 10 is implemented, would come down to around 500GB, more than enough for our 200GB database.

A common attribute of storage systems designed using a capacity-centric approach are throughput bottlenecks. For example, in the above scenario, while three disks would meet the capacity requirements of our 200GB database, the throughput would be severely constrained due to the low spindle (disk) count. In contrast, the performance-centric approach exceeds both the capacity and throughout requirements as a consequence of the design process.

In summary, spindle count is vitally important, and chapter 2 of my soon to be published book is dedicated to this topic, including the impact (good and bad) of storage area networks (SANs) in this regard.

Not validating the I/O chain

There are a vast number of components in a storage system’s I/O chain. The operating system, I/O drivers, virus scanners, storage controllers, read cache, write cache, switches, and various other components all pass data to and from SQL Server, and each one of these components has the potential to corrupt data along the way.

One of the really ugly aspects of being a DBA is dealing with physical data corruption. The only thing worse than finding corruption is then discovering the backups are also corrupt (or don’t exist!). Given the critical importance of data, it’s quite scary the number of DBAs who put a system into production without validating the numerous components in the I/O chain.

One of the inevitable outcomes of data corruption is the heated blame game that usually follows. The DBA blames the storage system, the storage administrator blames SQL Server and so on. Worst of all, this usually happens while production is down and people are screaming.

SQL Server has a number of specific I/O requirements, detailed here. The best way of validating them is using the SQLIOSIM tool before the system goes into production. Available as a free download, SQLIOSIM simulates the various I/O patterns used by SQL Server and ensures the data is written and read in a consistent manner. If any of the components in the I/O chain alters the data in any adverse way, SQLIOSIM will detect it, providing you the opportunity to correct the fault in a calm and prepared manner, before the system is implemented in a production capacity. In a similar manner, SQLIOSIM can be used to validate any modifications to the storage system (ideally in a identical test environment first), such as the upgrade of the firmware, drivers and so forth.

Unaligned partitions

A frequently overlooked configuration task is not aligning disk partitions. As shown in this whitepaper from the SQLCAT team, the latency impact of unaligned partitions can be as high as 30% or more. I’m not going to go into any more detail on creating aligned partitions, other than to say it’s a very simple and quick process which has long lasting and significant benefits; if you’re not familiar with the alignment problem, I urge you to read this whitepaper, which includes discussions around this graph...

Source: Disk Partition Alignment Best Practices for SQL Server, Microsoft Corporation

In the next post in this series, we’ll continue with the storage theme, but from a data/log/backup file configuration perspective.

Sysadmin Lockout

I ran across this issue today and thought I'd throw a quick post together.

On a particular SQL Server 2008 instance, all of the windows accounts/groups defined as SQL Server sysadmins had been removed, and the server was operating in windows integrated security mode. In SQL Server 2008, the BUILTIN\Administrators group is no longer created by the installation process, and in earlier versions it was best practice to remove it.

In my particular case, no one could connect to the server as sysadmin. After a bit of excitement and crazy talk about reinstalling SQL Server and reattaching databases, sense prevailed, and we started SQL Server in single user mode using the "-m" startup option. After starting SQL Server in this mode you can connect as a SQL sysadmin if you're a member of the server's local administrators group (even if you're not defined with a SQL Server sysadmin login).

One thing to be careful of here is SQL Server Agent will grab the single user connection if it's set to auto-start, so you'd need to shut down the agent service to get the single connection.

The moral of the story here is that you can't prevent Windows administrators from being SQL Server sysadmins .... damn.

DBA's Behaving Badly (3/10): Change Control

“As we know, there are known knowns.

There are things we know we know.

We also know there are known unknowns.

That is to say, we know there are some things we do not know.

But there are also unknown unknowns, the ones we don't know we don't know”

Donald Rumsfeld Feb. 12, 2002, Department of Defense news briefing

Thanks Donald. Yes, the unknown unknowns are scary beasts indeed. Little can be done about them until they happen. What we can do, however, is concentrate on those things we do know about, or things which have a reasonable chance of happening.

As a DBA, I know that bad stuff happens. I also know that it usually happens at 3am on the morning of my wife’s birthday. In almost all cases, unexpected problems are due to uncontrolled change and/or insufficient testing.

In the last post, we covered the importance of a Service Level Agreement (SLA). A sure fire way of blowing an SLA is to operate in an environment with insufficient testing environments and uncontrolled change. In this post, let’s take a look at 3 worst practices for change control.

Insufficient Testing Environments;

Brent Ozar’s blog from earlier this year addressed this topic nicely. Whilst the exact shape and number of testing environments will vary, one should have at least a development and test environment in which changes are developed and tested before reaching production. Here is my ideal setup.

 

The important points to note from this diagram are that each environment is used for a specific purpose, and that changes leaving one environment and entering another are signed off by a particular person or group, thereby imparting ownership of the change. Followed correctly, such a process significantly reduces the chances of unexpected problems being introduced into production.

Other than being lazy or indifferent, one of the common reasons offered by those with insufficient test environments is the lack of money and/or available servers. Enter virtualization. With the exception of the Volume Test environment, performance and throughput may not be an issue, therefore, the intermediary test environments (Integration and User Acceptance in the above diagram) are ideal candidates for virtualization.

The changes that flow from one environment to another are ideally those that emerge from a source control tool as part of a development methodology. Schema comparison tools such as Redgate’s SQLCompare are commonly used as part of this process. From a DBA perspective, another common set of changes are server and database configuration changes, and it’s these changes that can cause a significant headache without an appropriate change log.

No Change Log;

One of the great things about SQL Server is that the default settings are almost always the best, so much so, that non-default settings are frequently enabled for the wrong reasons. A classic mistake made by inexperienced DBA’s is enabling options like “Boost SQL Server priority”. Another one is to carpet bomb tables with indexes. In most cases, changes such as these are made in the hope of improving performance, usually without any record of the change and/or follow up observations of the performance improvement/degradation. The end result are servers and databases with massive deviations from best practices, leading to more problems in the future.

A crucial component of any successfully managed database environment is the Change Log. At a very minimum, such a log includes the following details;

• Date and time of the change
• What was changed, ideally with a reference to a script used to make the change
• Person that executed the change
• Observed performance difference

One of the very first steps in most performance tuning exercises is looking for non-default settings, or settings that deviate from best practice. In environments with uncontrolled changes, such settings are common, and usually accompanied by a lack of detail surrounding the setting change. Without such detail, changing the setting back to the default/recommended value is risky, particularly when test environments are not available to measure the impact of the change. In such environments, testing in production, is usually the only option, bringing with it the inevitable consequences.

In closing this post, let’s address one final topic, making multiple changes at once.

Multiple Changes at Once;

An essential component of any performance testing methodology is a baseline of known performance and a repeatable testing process in order to measure the impact of a proposed change. A common error is to make multiple changes at once, the implication being that one or more of these changes may be, at best, unnecessary, and possibly lead to future problems.

In opening this post, we examined the concept of the known unknowns and the unknown unknowns. As DBA’s, we know that there are many things that can go wrong, leading to unknown consequences, for example, we know that a spike in users during peak times will lead to an unknown performance degradation. Whilst the consequences of the unknown unknowns are beyond reach, the known unknowns are very much in play, and test environments, change logs and a controlled change strategy are crucial components of controlling their impacts and getting a good night’s sleep.

In the next post, we’ll address worst practices surrounding storage configuration.