The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

SQL Server, like other server systems, has become such a complex machine that it is beginning to take on some of the attributes of an android. When DBAs discuss ailing machines, something of a medical air affects the conversation. There is talk of symptoms, diagnostics, cures, viruses, leakage, dumps, and other concepts borrowed from human health.

The same errors that sometimes afflict the medical profession affect DBAs too. It is tempting to collect server symptoms randomly, in the forlorn hope that the more they collect, the clearer a picture they'll assemble of what ails the server. They should, of course, be looking at the whole patient (hang on; I think I meant 'server').

"Doctor, come quickly! Buffer Cache Hit Ratio is way down; Disk Queue Length is through the roof, Page Life Expectancy has dipped below the magic '300'. On with the white coat, alerts are going off all over the place…the patient is in terrible shape; should we call the relatives?"

In fact, of course, there is a good chance the 'patient' is just fine. Examining metrics such as these is the equivalent of the eighteenth century doctor examining only the patient's bowel movements, regardless of the reported symptoms. The chances of a correct diagnosis or, indeed, of discerning whether anything at all is wrong with the patient, beyond their diet, are slim.

Some metrics are more-or-less meaningless (BCHR). Others seem inextricably linked to 'magic thresholds' that ceased to be accurate many years ago (PLE). Many more lose meaning when applied with a uniform threshold value, across all environments. As Adam Machanic noted is his 10 commandments of Monitoring, the only good universally applicable threshold value is "server power is on". All others have a significance that is frustratingly ambiguous. Not only won't such metrics help you find the real problem, they will also distract and annoy you every time you receive an alert that a threshold is breached.

What's required in SQL Server diagnosis is a more formal approach based on good medical diagnosis where the whole patient is assessed; lifestyle, stress, baseline activity . Rather than randomly inserting thermometers and extracting blood, a doctor will sit the patient down, get a feel for their overall health, check their medical history, formulate a possible diagnosis and perform specific tests to prove or disprove it. They look for symptoms that stray significantly from "normal", for the patient as well as humanity in general.

Likewise, for each of your SQL Server environments, establishing what is "normal" is the key, and the cure for DBA hypochondria. In other words, you need means baselines, and ideally some statistical algorithms for analyzing the data over time and spotting significant trends amongst the "noise".

Who owns up to being a DBA hypochondriac? Or wants to share their "baseline formula" cure?

Cheers,
Tony.

Tony Davis from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

Tech News : General Interest

Is Microsoft driving PC makers to Google Chromebook and Android? - As Microsoft blames hardware vendors for poor Windows 8 sales, HP may be next to board the Chromebook bandwagon...(more)

Disruptive Data Science – Transforming Your Company into a Data Science-Driven Enterprise - Big Data is the latest technology wave impacting C-Level executives across all areas of business, but amid the hype, there remains confusion about what it all means. The name emphasizes the exponential growth of data volumes worldwide (collectively, 2.5 Exabytes/ day in the latest estimate I saw from IDC), but more nuanced definitions of Big Data incorporate the following key tenets: diversification, low latency, and ubiquity....(more)

The Top 50 Proprietary Programs that Drive You Crazy — and Their Open Source Alternatives - Not every proprietary program can drive a person crazy, right? Some, like Adobe Photoshop, are superb tools for anyone to use. But, the fact that these tools are proprietary can drive open source fanatics up a wall. ...(more)

Why we built a Raspberry Pi cluster - My previous blog post on how we built a Raspberry Pi cluster became an instant hit within a few hours of publishing it. Sites such as ycombinator, reddit & it was even trending on Google+. There were many positive reviews & comments, and there were a few critics as well. Most of the critics argued that the Raspberry Pi is a very slow low end platform, and for the time, effort & money spent on such a project, they could easily purchase a powerful core i7 machine & achieve the same or better performance....(more)

Do Not Pass This Way Again - A view from the anti-MySQL camp: "databases fill roles ranging from pure storage to complex and interesting data processing; MySQL is differently bad at both tasks. Real apps all fall somewhere between these poles, and suffer variably from both sets of MySQL flaws."...(more)

Tech News : Security

Mozilla to automatically block virtually all plug-ins in Firefox - Cites security, stability cited as reasons for taking drastic step to turn on 'click-to-play' for all but the latest Flash...(more)

Microsoft News : General Interest

Steve Ballmer: With 100M Users, Dropbox is a 'Little Startup' - "Well, you've got to remember, 100 million sounds like a pretty small number to me, actually," Ballmer told BusinessWeek. "We've got a lot more Office users. And actually if you even want to go to the cloud, we have a lot of Hotmail and SkyDrive users. I'm not beating on Dropbox. They're a fine little startup and that's great."...(more)

Hardware News

The long tease is over: Here's BlackBerry 10 - RIM has unveiled its highly graphical, touch-oriented Peek and Flow user experience in a bid to regain its lost relevance...(more)

Microsoft's Surface Pro: How much usable storage will it really have? - Surface Pro users will have substantially less free storage on their devices, out-of-the-box than many may have expected. Big deal or business as usual?...(more)

Community Sites

Indexes with excessive writes (Custom Metrics) - This metric measures the total number of indexes per database, where the number of writes exceed the number of reads. It provides a general indicator of possible performance factors affecting queries in your database. If indexes are being updated with new data more often than they are being used in query plans, they can cause performance issues during write-heavy operations (INSERT, UPDATE, and DELETE statements), while adding little or no benefit to read operations....(more)

Blogs : .NET

Git support for Visual Studio - Git, TFS, and VS put into Context - Brian Harry from Microsoft announced Visual Studio 2012 Update 2 (or VS2012.2) the latest quarterly update. That's interesting, but it's the announcement of Git integration with Visual Studio and TFS that is really significant, in Scott Hanselman's opinion....(more)

Blogs : Administration

Microsoft SQL Server – Bulk Delete Prescription - On the various factors which influence the choice of appropriate solution for the bulk delete problem....(more)

SQL Agent Job Frequency Intervals in Plain English - SQLPhilosopher on creating a simple process that finds each SQL Agent Job on a given instance, extracts the basic information regarding it, and compiles a list that can be presented to non-DBAs for information about what sort of “undiscovered” processes are running in a given environment....(more)

Database Filegroups: Just Like Seatbelts But With Less Chafing - Filegroups help you in so many ways and are easy to use but hardly anyone is using them. I rarely come across a customer or client that has deployed filegroups. When I ask about them the response is usually “yeah, we know about them, but we don’t see why they are useful.” Just like a seatbelt, you don’t know why it is useful until you need it, and then you’ll use it all the time....(more)

Why does SQL Server not compress data on LOB pages? - why does SQL Server not compress the data that, perhaps, would benefit most from compression? Hugo Kornelis provides the answer....(more)

Visualizing Data File Layout III - If you follow SQL Server at all, you are probably aware of the long-standing debate about whether it is wise, desirable, smart, useful, or what have you, to identify rows using GUIDs. I won’t take a position on that, but I will show here, I hope objectively, a few things that the visualizer shows about file layout vs. distributed inserts, distributed inserts being one of the main challenges around using GUIDs as clustering keys....(more)

Database Deployment Challenges - There are a number of challenges that make the deployment task more difficult. Alex reviews the common techniques for deploying new databases and upgrading existing ones, and their flaws, and argues the advantages of an automated, incremental, script-based approach to deployments....(more)

Blogs : Analysis Services / BI

Validating MDX Queries Without Running Them - Chris Webb on a way of checking whether an MDX query is syntactically correct without actually having to run it. You can do this by setting the Content connection string property....(more)

From DBA to Data Professional and back again - Rodney Landrum had been working on a Business Intelligence (BI) solution with one of his development teams. Inspired, he decided to the woo his boss with a new interactive PowerView report. This is how he almost lost his job....(more)

Blogs : Backup and Recovery

My Red Gate Hyperbac + DBCC CheckDB Script - SQLPhilospoher provides a script for automatically cycling through each database in an instance, restoring a virtual hyperbac database, performing consistency checks, and then e-mailing out the results...(more)

Backup TO DISK, TAPE and now URL - Backup On-Premises SQL Server Databases to Azure Storage - Interesting new functionality has appeared in CU2 for SQL Server 2012 SP1. The BACKUP command now supports TO URL when specifying a backup device. That allows you to specify the location of an Azure storage account as the backup destination....(more)

Blogs : Computing in the Cloud

How to backup and restore user database from SQL server to Windows Azure blob storage - On how to backup and restore to/from Windows Azure Blob storage by using Transact-SQL and SMO(Server Management Objects). ...(more)

How Does the Cloud Change a Database Administrator’s Job? - A Database Administrator (DBA) is probably one of the harder roles to think about when it comes to cloud computing....(more)

On taking a more nuanced approach to Cloud services - Although it makes a lot of sense for an IT department to use the Cloud where possible instead of using its own IT infrastructure, you can’t just move everything and expect to be left with no task more taxing than twiddling a big dial marked 'scale'....(more)

Blogs : Database Design, Theory and Development

One Step Closer to Nirvana - For years, many years, there's been the quandary: if one is updating a row, and not updating key columns, should others be able to concurrently update other non-key columns, even the one I just updated? So far as I know, Postgres is the first database to tiptoe in that direction....(more)

Blogs : Deep Into Windows

102 simple steps for installing and configuring a new Windows 8 machine - Troy Hunt: "As sure as night turns into day, sooner or later your PC will descend into an unrecoverable abyss where it no longer boots, stays booted or can’t even get booted to begin with. I’ve had memory go bad, motherboards die, CPUs fried, many mechanical disks develop bad sectors and now for the second time, an SSD gradually turn itself into nothing more than a paperweight."...(more)

Blogs : DMO/SMO/Powershell

PowerShell Basics–Objects and the Pipeline - PowerShell is not your typical Shell...it behaves in a unique way when it comes to the output generated by the commands we issue in it, this is because PowerShell is an Object based Shell....(more)

Blogs : Events

SQL Bits XI - SQL Bits XI takes place from May 2nd-4th in Nottingham in the UK. It's a great event with a day of pre-con training, a paid day of speakers, and a free day. Register today if you can attend any or all of the days....(more)

Blogs : High Availability/Disaster Recovery

How to Remove (Undo) Table Partitioning - Patrick Keisler: "I have seen plenty of articles and blog posts out there for how to setup and implement table partitioning, but very few for removing or undoing it. So I thought I would cover a few ways to accomplish this while still preserving the data."...(more)

Highway to Database Recovery - Discover the best backup and recovery articles on Simple-Talk, all in one place....(more)

AlwaysOn Part 10: Switching Scheduled tasks automatically - Jobs like eventual consistency checks that are scheduled...or jobs that extract some monitoring data or perform some update statistics on specific tables...usually fail if those run on a secondary. Hence a broader solution to automatically switch scheduled jobs/tasks from enabled to disabled is necessary....(more)

Transactional Replication Toolbox Scripts: Show Articles And Columns For All Publications - Having worked extensively with transactional replication, Kendal Van Dyke has written a handful of scripts that have found a permanent home in his "useful scripts" toolbox....(more)

SQL Server Table Partitioning Tutorial: Videos and Scripts - There’s a secret to learning about SQL Server’s table partitioning feature: you need to get your hands on some code and really play with it in a test environment. In this tutorial, Kendra Little will walk you through test scripts that set up a sample table partitioning environment. ...(more)

Best Practices + Table Partitioning: Merging Boundary Points - One of the many best practices for SQL Server’s table partitioning feature is to create “extra” empty partitions around your data. Ever wondered why that’s the case, and what might happen if you don’t follow it? Kendra Little demonstrates....(more)

Data Partitioning - Scaling-Out (Part 2: Data Sharding) - Dmitri Korotkevitch on data sharding...creating multiple databases of the same (or very similar) structure and distribute (”shard”) our data between them. The typical use-case for this situation would be the system that collects data from the multiple customers where every customer works with his/her own subset of the data....(more)

Blogs : Integration Services/ETL

SSIS 2012 – Catalog Indexing Recommendations - Phil Brammer: "Many months ago, I dove head-first into the SSISDB catalog and began to troubleshoot these performance and deadlock problems I was encountering. After a bit of looking around, it became clear that some basic indexing strategies were not implemented." ...(more)

SSIS Basics: Using the Conditional Split - The Conditional Split can route data rows to different outputs depending on whatever criteria of the data that you wish. It works rather like the SWITCH block in a procedural language. Annette shows how to add a conditional split to your data flow....(more)

Blogs : Performance and Tuning

Performance Impact from Page Splits - Tim Radney@ "Recently I was brought in to help troubleshoot performance issues on a database server. Going through my typical check list I noticed memory pressure on the box and made the recommendationof more memory which was simple to fix. What caught my attention though was that blocking was occurring on a transaction that was trying to do a simple delete of a few records."...(more)

AppDomain unloading messages flooding the SQL Server error log - The issue is very specific, but solving it is no different than any another other resource bottleneck troubleshooting. Capturing traces, performance monitor logs and other outputs and tracking down why the original bottleneck occurred. In this case “Why is the system taking so long to load the assembly or is the query timeout improperly set to something tiny?” ...(more)

Optimizing T-SQL queries that change data - Most tuning efforts for data-changing operations concentrate on the SELECT side of the query plan...but what about the data-changing side of the query plan – the INSERT, UPDATE, DELETE or MERGE operation itself – are there any query processor considerations we should take into account? The short answer is yes. ...(more)

Blogs : Security and Auditing

Consider All the Possibilities When Troubleshooting - K. Brian Kelley: "When you are troubleshooting, the rule is if you've checked the obvious possibilities and still haven't solved the problem, it's time to expand to the unlikely ones."...(more)

Blogs : Software Development

When should I make the first commit to source control? - I'm never sure when a project is far enough along to first commit to source control. I tend to put off committing until the project is 'framework-complete,' and I primarily commit features from then on. (I haven't done any personal projects large enough to have a core framework too big for this.) I have a feeling this isn't best practice, though I'm not sure what could go wrong....(more)

Git init VS - An extension for Team Explorer to provide source control integration for Git. It enables integration with local Git repositories and provides tools to work with remote repositories. Also, an official Microsoft implementation of git for Visual Studio....(more)

Blogs : SQL Server 2008 (Katmai)

SQL Server 2008 Service Pack 3 Cumulative Update 9 - Microsoft has released SQL Server 2008 Service Pack 3 Cumulative Update 9, which is build 10.00.5829. As you might expect from the build number (which is only one number higher than SQL Server 2008 Service Pack 3 Cumulative Update 8), there are very few fixes in the public fix list. Three to be exact....(more)

Blogs : T-SQL

Useful T-SQL Scripts - Raúl Alarcón provides two of his most heavily-used T-SQL scripts, one to kill xisting connections to a certain database, and one to reveal currently-running queries and their resource usage....(more)

Poor Execution Plan While Ripping Large XML in SQL Server 2008 - SQLPhilosopher on a very frustrating problem while trying to rip a large XML document into a table-structure in T-SQL on 2008....(more)

A creative use of IGNORE_DUP_KEY - Paul White: "Let’s say you have a big table with a clustered primary key, and an application that inserts batches of rows into it. The batch will sometimes contain rows that already exist in the table...the desired behaviour is that new rows in a batch should be inserted, and any duplicates silently rejected."...(more)

Using constraints to enforce uniqueness of ordered sets of rows - Even the simplest data integrity rules are not easy to implement in the database if instead of individual rows we need to deal with groups or subsets. For example, making sure that a column value is unique in a table is as trivial as creating a unique constraint or index. However, what are we supposed to do if we do not want to store a group of values twice?...(more)

Blogs : Virtualization

Hyper-V for Developers Part 1 - Ken Kilty[MSFT]: "The goal of this blog series is to share how I use the Windows 8 Client Hyper-V feature to model application infrastructure. I will share my techniques and tips to get the developer focused on getting environments up and running as quickly as possible."...(more)