Forgotten, but not gone

My first contact with Microsoft SQL Server was with version 7.0 back in the late nineties. Back then, there wasn't a whole lot more to the product than the database engine and the tools to manage it. That was plenty to be going on with, mind you: even then, there was a huge amount of information to be absorbed if you wanted to claim understanding of how to build, implement & manage databases built on SQL Server.

5 versions later, though, the amount of functionality bundled with even the Standard Edition is staggering, encompassing not just the relational database engine but reporting, BI, and advanced data management. Few can now claim with any authority to be a 'SQL Server expert'; each area within SQL Server is a lifetime's work on its own at this stage, even if new product versions with new functions and capabilities weren't arriving every few years. 'Expertise' is in many instances having enough background & experience with the product that you know in general terms what capabilities are there & where to find them, and being quickly able to get up to speed on the details of the less everyday areas of the suite and implement them successfully as the need arises.

All of which is a very long-winded way of saying that, once you've been working with SQL Server for more than a few years, you've likely forgotten more than you know about the parts of the product you're not using daily (or you've got a much better memory than I have).

A reminder came this week. I was working with a client who wanted to configure his server in such a way that another contractor could create, modify, test and schedule SQL jobs relating to the contractor's own project, but not modify other jobs (like general backup & maintenance jobs, for instance). Sysadmin permissions, then, were too broad, but any of the other fixed server roles were too restricted.

We took a look in MSDB, though, and came up with the SQLAgentOperatorRole role. This was added in SQL Server 2005, but I'd never had cause to use it in the meantime and subsequently forgotten about it.

For this purpose, though, it's almost ideal. We added the contractor's user account and a test account as members of the group, then logged in again with the test account to see what we could & couldn't do. As the test user, we could add new jobs, add job steps, modify both at will and schedule them as necessary. We weren't able to pick existing job schedules, interestingly, but were forced to create our own set (I can see the point of this, but it goes a bit far for most situations).

Better still, we couldn't modify other jobs (jobs with other owners) with one important caveat: we were able to disable and enable jobs and job schedules from other owners. It's not at all clear to me in which situations it would be desirable to block someone from, say, editing a job description while allowing them to disable the entire job, but that appears to be the situation.

We decided to use the role nonetheless, deciding that 95% was better than nothing. I dug a bit further afterwards, though, and discovered that the role SQLAgentUserRole fit our needs better. As a member of this role, you can still create, modify, run and schedule your own jobs, but neither modify or disable jobs from other owners.

Re-learning stuff like this, and getting to use it in production scenarios, is what keeps this line of work continually interesting.

Being Resourceful

A client of mine has a system in development that consists of a normal, multi-user .NET desktop application plus a number of services that manage some occasional heavy-duty background processing on the database server. The problem is, when the services are running, the application performance grinds to a standstill and users are left for minutes on end waiting for their application to react.

This obviously isn't acceptable, but lowering the impact of the background processes isn't straightforward, either. It's not as simple as just rewriting a runaway query or adding a few indexes, and re-architecting the entire solution far into development just isn't going to happen. The question was, would it be possible to limit the amount of resources these background processes consume? My customer decided to try the Resource Governor feature in SQL Server but had no useful or tangible performance improvement, so I was asked to take a look at their setup and see what, if anything, was going wrong.

My own experience with the Resource Governor was limited to what I'd read about it, as well as trying out some example test setups, but I'd never configured or observed it on a semi-live environment, so I was delighted to get the opportunity to learn more about it.

We spent almost two days in total configuring and testing, making tiny changes in the RG configuration and observing the change in behaviour and processing time for similar workloads. With the RG disabled, the background processes would take a full 100% of the server processor time for minutes on end, so the goal was to brake this enough to leave enough processor capacity for the desktop app users without overly limiting the background processing.

Our results were surprising - to me, at least. The first surprise was how far down we had to take all the settings before we saw any effect at all. The second surprise (and I'd imagine that it's not always the case),  was that we consistently observed a sort of threshold effect while changing settings; nothing would change in observed or measured load through a range of changes until suddenly, with one change up or down, there'd be a huge increase or decrease in consumed resources. After a lot of experimentation and measurement, we eventually arrived at a configuration that gave us a decent mix of resource use and allocation.

So, is the Resource Governor a worthwhile addition to the DBA's toolbox? Absolutely. You're obviously still better off trying to tackle the source of excessive resource consumption rather than artificially limiting it, but when this isn't possible the Resource Governor gives you one more option to work with before going to the boss, cap in hand, and asking for a more powerful server. It's tricky enough to configure properly and not massively well documented yet but you can definitely get results with it.

Forty-four thousand satisfied customers

A couple of years ago, I wrote an article for SQLServerCentral.com that described how SQL Server Integration Services could be (mis)used to query a SQL database, format the query results as HTML, and automatically e-mail them on to interested parties. It was more of a demonstration of what were then relatively poorly documented features of SSIS than a serious attempt to recreate a reporting solution, but it touched an unexpected chord and got a huge response on the site. It's been a featured article three times, been included in the publication "Best of SQLServerCentral Volume 6", and has a maximum five-star rating from no fewer than 44,550 readers as I write this.

All this blowing my own trumpet is well and good, but up to now this piece was only visible to registered visitors of SQLServerCentral.com. Today, however, in a shameless attempt to drive some traffic to my refurbished and improved website, I've posted it as a PDF on my own site for the enjoyment and enlightenment of one and all. It's a bit dated by now, and I can certainly think of easier and more efficient ways of achieving the same ends than by using SSIS, SQL XML queries and XSL transformations, but if that's your idea of fun then please take a look.

SQL Error Handling

Some time ago I wrote an introductory article for this site on SQL Error handling and the differences between SQL 2000 and SQL 2005 in that regard. (Download here as PDF). I had intended to expand upon this as a follow-up, but, as is so often the case, never got around to it.

Alex Kuznetsov has written a fantastic new book, "Defensive Database Programming with SQL Server", and one of the chapters from it deals with this subject in so much detail that I wouldn't dream of trying to top it. I'm not sure I'd completely agree with his contention that it may be better to implement your error trapping on the client in code, but his arguments are lucid and his examples compelling. Better yet, an article taken from the book on this subject can be found here. It's well worth your time.

Faster

A brand new laptop arrived here yesterday equipped with all the latest bits and pieces. Perhaps the most interesting component is the hard drive: instead of the traditional magnetic platter design, this one has a solid-state disk (SSD). This, for those who may not know, is basically an array of flash RAM chips much the same as the ones you use every day in your USB drives, but configured to work as a hard disk and to identify itself to the OS as such.

The disk is startlingly fast. I haven't run any proper tests on it yet, and don't have figures to prove my assertions, but what I've seen so far is that all disk-intensive operations (WinDirStat analysis, large installations) are completed in a fraction of the time of a "normal" disk drive.

This prompts me to wonder what such drives might accomplish in database servers. Disk reads and writes are often one of the major bottlenecks in poor-performing systems, and adding SSDs sounds like a cheap and easy way to boost performance. Or a mix of SSDs and magnetic disks; put tempdb on an SSD, logs (sequential writes, relatively few reads) on a magnetic disk, backups on magnetic, index filegroups on SSD... I'm really curious about what the real-world difference might be on a production system.

To @@ERROR is human, too

I started writing a short blog post about a particularly egregious example of bad transaction handling I encountered the other day, and the thing just ran away with me. First it became a long post, then a couple of examples were added and before I knew where I was I'd gone 6 pages further and it had become a full-blown article to explain the very basics of how error trapping, error handling and transaction handling works in SQL Server (PDF, 252KB).

SQL 2008 date and time values

I've been looking at SQL 2008 for well over a year in various guises, but between one thing and another it's only now that I'm actually doing any serious work with SQL 2008 databases. It's a chicken and egg situation, of course; you don't want to fool around with valuable customer database installations that are running perfectly well with SQL 2005 until you're completely confident with the new version, but you don't become completely comfortable with the new version until you've worked intensively with it for a while.

However, I was examining a problem involving frequent calculations on date values recently and SQL 2008 seemed like the obvious choice. Dates are painful in SQL 2005 and all earlier versions; a date and a time are always stored together even if you're only interested in the date or the time. You can ignore the other component, but you need to take care it doesn't come back and bite you when you're doing calculations: March 4 2009 is two days later than March 2 2009 but March 4 2009 01:12 is not two days later than March 2 2009 23:59, so the answer you get is very much dependent on how you frame the question.

There are ways around these problems, of course (I usually pass all date and time inputs through cleansing functions so they all save the same way), but it's all extra work for no good reason.

SQL 2008 though, among many other improvements, features separate date and time data types. This is a particular blessing, and works simply and beautifully. I'm also thrilled to at last have a debugger in the Management Studio, and working without it in SQL 2005 databases has rapidly become frustrating.