The Complete Weekly Roundup of SQL Server News

In this issue:

Vendors/3rd Party Products

Tech News : General Interest

Tech News : Security

Microsoft News : General Interest

Blogs : Administration

Blogs : Analysis Services / BI

Blogs : Backup and Recovery

Blogs : Computing in the Cloud

Blogs : Database Design, Theory and Development

Blogs : Developer Tools

Blogs : High Availability/Disaster Recovery

Blogs : NOSQL

Blogs : Performance and Tuning

Blogs : Professional Development

Blogs : Reporting Services

Blogs : Security and Auditing

Blogs : SQL Server 2014

Blogs : T-SQL

Blogs : Virtualization

Database Weekly - www.databaseweekly.com

The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

SQL Server News for 2013-12-09

SQL DBA Bundle The seven tools in the SQL DBA Bundle support your core SQL Server database administration tasks.
Make backups a breeze! Enjoy trouble-free troubleshooting! Make the most of monitoring! Download a free trial now.
ANTS Memory Profiler Troubleshooting unmanaged memory in .NET
Kicking off a series on real-life .NET memory problems, Clive and Chris dig into how unmanaged memory works in .NET, the problems it causes, and how to fix them. Find out more
SQL Monitor Check SQL Server performance at a glance
We consulted 1000 SQL Server professionals to make SQL Monitor’s UI as clear as possible. Start monitoring with a free trial.
Editorial - Relationally Divided over EAV

The EAV (Entity-Attribute-Value) data model gets bad press in the world of the relational database, and with some justification. I'd read a lot of the evidence against it and even published on Simple-talk a tale of EAV gone wrong that is not for the faint-hearted. I regarded EAV designs largely as an "anti-pattern" arising from misguided attempts to transpose the world of object-orientation and loosely-typed languages directly into the relational model.

If EAV design has a "avoid at all costs" reputation among many, the fact remains that sometimes you just can't know in advance all the required attributes, and in such cases there are few alternatives. Peter Larsson, in his EAV session at the recent SQL Rally event in Amsterdam, described one such case, a database application to return medical insurance documentation, where there was simply no way to predict, over time, exactly what sort of data may need to be stored.

When Peter arrived on the project, the database contained 20 million rows, and one of the most important document search algorithms took 1 minute to return its data. When the table reached 1 billion rows, which would happen quite quickly at current growth rates, they estimated, with a high degree of confidence, that the same document search would take 134 days.

Peter then explained – and proved – that after fixing some flaws in their database design, and in their inefficient search algorithm, the same document search query, on a billion rows, returned its data in milliseconds. I nearly fell off my chair. The remainder of the session was going to explain how he did it, and it's safe to say he had his audience's attention.

I wondered briefly if by "tweaking the database design" he really meant "replace it with a proper relational model", but no. He had applied some sensible normalization but the model was a hybrid, with an EAV table containing unique attribute-value pairs alongside the normalized tables. In the EAV table, each row comprises three columns that describe an entity, an attribute, or characteristic, of that entity, and a value for that attribute.

He explained that the key to efficient querying of such a model was a technique called relational division. Let's say you live in a dorm with a number of other students who all own random number of socks of various colors. Your socks are red, green and blue and you want to know which other students have a matching set of socks. In relational division, you query the table (the dorm) for each entity (student) and their attributes (socks) that match your values (colors). The dorm is the dividend and your socks are the divisor. If student A owns yellow, black and green socks, the quotient is zero (not fulfilled). However, if Student B has yellow, red, black, blue, purple and green socks then the quotient is 1, since you and student B own the same subset of red, green and blue socks.

With the right algorithm, Peter proved that relational division can be highly efficient. Coupled with a clever approach to indexing, to facilitate "ordered index scans", and sensible statistics management, he was able to achieve formidable results – at least, I think we can call 134 days to a few milliseconds, for a billion rows, formidable.

Clearly EAV models are "difficult". They can and frequently do cause bad performance and maintenance problems, as the database grows. However, I also appreciated the lesson in why you should rarely form closed opinions on 'good' or 'bad' practices in database and query design. Sometimes there is no alternative to an EAV design, and the techniques do exist to make them work in a relational world.

Cheers,

Tony.

Further reading: Check out Peter's The E, the A and the V PDF

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


The Weekly News

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.

Vendors/3rd Party Products

New SQL Monitor Custom Metric: Default TCP Port - This metric checks whether your SQL Server installation is using the default TCP port. It’s widely known that SQL Server 2005 and 2008 listen on TCP port 1433. Keeping this default gives hackers a potential way of attacking your server....(more)

Tech News : General Interest

Thoughts on Amazon Prime Delivery Drones - This past weekend, just in time for Cyber Monday, Amazon CEO Jeff Bezos revealed plans on 60 Minutes for delivery drones for Amazon Prime shipments weighing up to five pounds. ...(more)

Tech News : Security

Two million log-ins stolen from Facebook, Google, ADP payroll processor - The attackers are using the 'Pony' botnet command-and-control server software....(more)

Introducing “Have I been pwned?” – aggregating accounts across website breaches - As I analysed various breaches I kept finding user accounts that were also disclosed in other attacks – people were having their accounts pwned over and over again. So Troy Hunt built haveibeenpwned.com allowing you to check if you have an account that has been compromised in a data breach....(more)

Microsoft News : General Interest

Microsoft Approaches 'Threshold' of a Unified Windows Ecosystem - The software giant is rumored to be paving the way for a unified Windows platform that spans multiple devices, including smartphones and the Xbox One. - See more at: http://www.eweek.com/mobile/microsoft-approaches-threshold-of-a-unified-windows-ecosystem.html#sthash.rDOk1sx9.dpuf...(more)

Why Microsoft Is Banking Heavily on StorSimple for 2014 - A StorSimple appliance can be operated by anybody who is familiar with Windows, and that takes in just about everybody on Earth. It uses drop-down menus and wizards and requires a minimum of clicks. It classifies all content to determine whether it is a working copy or an older version. It keeps all working copies on local drives and automatically migrates all other content onto the cloud....(more)

Microsoft's Windows future: One core, many SKUs - Is Windows RT dead? Is the desktop going away ? What's 'Threshold' got to do with it? Here's Mary Jo Foley's latest attempt to try to make sense of where Microsoft's Windows roadmap will lead in the next couple years and beyond....(more)

Blogs : Administration

SQL Server Error Log, A Little Deeper - Chris Shaw explains how he uses xp_readerrorlog for tabling your filtered error logs....(more)

Performance and Stability Related Fixes in Post-SQL Server 2012 SP1 Builds - If you are running SQL Server 2012, I really think you should be running the latest SQL Server 2012 Service Pack and Cumulative Update. Right now, that means Service Pack 1, CU7 (Build 11.0.3393)....(more)

SQL Server Error Log - If your server has been on line for quite some time, the error log can grow to extremely large sizes. Depending on a few settings that may have been made elsewhere many of these log entries are not really much assistance in working though issues. Chris Shaw offers a few tips....(more)

General Database Server Build and Deployment Instructions - Having a standardized build checklist for a new SQL Server instance can help ensure that you do not forget any important steps or configuration settings as you install SQL Server, get it updated, and get it properly configured. This is very important in order to make sure you get the best performance and scalability out of your SQL Server instance....(more)

Keeping Track of your SQL Servers with a DBA Configuration Repository - SQL Server DBAs are often responsible for managing large number of database instances. Busy 24/7 operational DBA teams may look after hundreds of instances with widely different characteristics. As systems get added, modified or decommissioned from the fleet, database administrators need to keep track of everything they manage. This article talks about the concept of a Configuration Repository where DBAs can manage all the information related to their SQL Servers....(more)

10 Tips for the Minimalist DBA - It seems that there’s so much to learn when you are first working as a production DBA. What do you focus on first? How should you prioritize your learning? What things should you automate and measure? What skills are core to your job, no matter how long you’ve been a DBA. These are the things that we think that all production DBAs need to know and continue to build upon....(more)

How to find user who ran DROP or DELETE statements on your SQL Server Objects - Someone has dropped a table from your database and you want to track who did it. Or someone has deleted some data from a table, but no one will say who did. In this tip, we will look at how you can use the transaction log to track down some of this information. ...(more)

SQL Server tablediff utility - Rob Sheldon continues on his quest to explain all those command-line tools such as SQLCMD, Logparser, SQLIO and tablediff that are part of SQL Server. TableDiff is great for comparing tables, as when you run automated tests that check a result against a table of expected values. The best way to learn TableDiff is to see it in action and Rob talks you through several examples....(more)

Blogs : Analysis Services / BI

Strategies and Languages for Generating Biml - What are the best methods for generating Biml and what language should you use? I’m going to briefly discuss some concepts involved in the process and common ways I see it being generated. I’m also going to make recommendations that I’ve found work well in most circumstances....(more)

PowerQuery, ExpandTableColumn and the parent data - David Mauri has been playing with PowerQuery in the last days and needed to access “parent” object data when using the ExpandTableColumn function. The solution was more tricky then he imagined......(more)

Blogs : Backup and Recovery

Accidently Kicking a Database into the Restoring State - Steve Jones on how a log backup "WITH norecovery" leaves a database in a restoring state....(more)

Blogs : Computing in the Cloud

Which Windows Azure Cloud Architecture? PaaS or IaaS ? - Those who have been associated with cloud computing for some time knows how to select a cloud deployment architecture. However, a few ISV partners our team works with consistently asks questions about the cloud architecture selection criteria and hence this post. ...(more)

Working with 154 million records on Azure Table Storage – the story of “Have I been pwned?” - When Tory Hunt came to build HIBP, he had to make querying 154 million email addresses as fast as possible. The answer was Azure Table Storage....(more)

Automation–Automating Hybrid Clouds with Windows Azure and PowerShell (Part 4): Public Cloud Environment Deprovisioning PowerShell Examples - Charles Joy concludes his series and thinks you will be surprised to see how easy it is to remove everything you added....(more)

Blogs : Database Design, Theory and Development

Primary Key Primer for SQL Server - Every database developer uses keys, but without always understanding all the ramifications. They come with few hard and fast rules, but if you get them right from the start with a database design, the whole process of database development is simpler, and the result is likely to perform better. We asked Phil for advice, little knowing that the explanation might take a while....(more)

Blogs : Developer Tools

Git for Grown-ups - An article about the popular version control system, Git that doesn't just give you the top five commands that you need to memorize....(more)

Blogs : High Availability/Disaster Recovery

Recovery On Secondary Lagging – Shared Redo Target - There are various reasons a secondary may not be keeping up (slow network, blocked redo worker by a long running query on a readable secondary, ….) This post is going to focus on the shared redo target....(more)

Blogs : NOSQL

Data Science Laboratory System – Graph Databases - Graph database are an intriguing alternative to the relational model. They apply graph theory to record the relationships between entries more naturally, and are a good fit for a range of data tasks that are difficult in SQL. Buck Woody gives an introduction to Graph databases and shows how to get Neo4J up and running to get familiar with the technology. ...(more)

Blogs : Performance and Tuning

Doing It Right: Performance Monitoring and Troubleshooting - Tom La Rock explains why and how one hour of proactive actions can save you up to 150 hours of being reactive....(more)

How to use Extended Events to proactively monitor your SQL Server for Deadlock issues - You can configure an extended event in a SQL Server instance, which will record deadlock graphs inside a file as soon as the deadlocks occur. The overhead of this functionality is negligible and it serves as a “black box” for any deadlocks that occur. ...(more)

Blogs : Professional Development

New book: Microsoft SQL Server 2012 Internals - Kalen Delaney's Microsoft SQL Server 2012 Internals (9780735658561) is available for purchase....(more)

Blogs : Reporting Services

SSRS – Self Referencing Reports or Drill through to same report - When looking at a report and wanting to drill through to get more detail, we often create two reports for this. This can be accomplished by just one report by creating a self-referencing action that links to the same report. This can be done even if the report has parameters on it. ...(more)

Blogs : Security and Auditing

Login failed for user on portal and SSMS when username has “@” in it. - An interesting case where login was failing even though the password was correct....(more)

Two million stolen Facebook, Twitter, Yahoo, ADP passwords found on Pony Botnet server - Trustwave's SpiderLabs found a Pony Botnet Controller server holding over two million passwords and account credentials for ADP payroll, Facebook, Twitter, Yahoo and more belonging to victims around the world. ...(more)

Blogs : SQL Server 2014

How to Use Microsoft's AMR Tool - In order to help DBAs determine which tables and stored procedures might be good to take advantage of using the new In-Memory OLTP tables in SQL Server 2014, Microsoft introduced the AMR Utility. AMR stands for Analyze, Migrate, and Report. In this article I will be exploring how to use this tool....(more)

What’s Coming in SQL Server 2014 [Video] - It’s coming…the next version of SQL Server. What surprises are in store for you? “In-memory” is a hot buzzword. Windows Azure capabilities are expanding. Data Explorer is being talked about. Jes will give you an idea of some of the new features and enhancements coming next!...(more)

Updateable columnstore index gotchas - According to BOL, row groups in deltastore can be in one of 3 possible states: open, closed or compressed. The performance difference between a fully updated columnstore index and one where part of your data that is still in OPEN or CLOSED state in deltastore is huge....(more)

SQL Server clustered columnstore Tuple Mover - It is important for your upload, initial seed and day-to-day ETL activities to achieve a healthy columnstore index, meaning no deltastores or only a few deltastores. To achieve this desired state of a healthy columnstore it is of paramount importance to understand how deltastores are created and removed....(more)

Hekaton with a twist: In-memory table-valued parameters - Table-valued parameters (TVPs) can be memory-optimized, just like permanent tables can. And with that, the wheels immediately started turning for Aaron Bertrand......(more)

Myths and Misconceptions about Hekaton - Klaus Aschenbrenner tries to clear up a few of the misinformation, myths, and misconceptions surrounding Hekaton....(more)

Blogs : T-SQL

Overly Complex Views, Procedures And Functions - In the programming world, developers have defined metrics for how complex a piece of code is. For example, Visual Studio defines several metrics that are meant to give developers some idea about how complex their code is getting. Here, I define a metric which indicates a code-smell for overly complex views, procedures and functions: “FROM” count....(more)

How Simple Parameterization works - A customer upgraded from SQL Server 2005 to 2008 and found their performance degraded greatly. An adhoc update query that was run many times in a batch was parameterized in SQL 2005 but not in SQL 2008. So the cost came from the compiling every time for the update. Eventually, we resolved the issue. But it prompts a post on simple parameterization....(more)

Learning PostgreSql: READ COMMITTED and Data Integrity - PostgreSQL's READ COMMITTED isolation level behaves very much like Sql Server's READ_COMMITTED_SNAPSHOT. As such, we need to be very careful with data integrity - lots of code that just works on Sql Server under its default isolation level, READ COMMITTED, does not work on PostgreSql under its default isolation level, which is also READ COMMITTED, but behaves differently....(more)

String Aggregation in the World of SQL Server - This articles includes examples of T-SQL capabilities that allow to perform string concatenation more flexibly and effectively through the use of other constructs....(more)

TSQL code to explore keys in a database - Phil Factor presents a set of queries to explore and investigate the keys of a table or database....(more)

Blogs : Virtualization

Designing a Hyper-V Virtual Machine - When you specify a physical server, you typically figure out the requirements of the operating system and application; determine support and performance requirements; and configure the required storage, memory, processors, networking, and so on. This doesn’t really change with Hyper-V, or any virtualization platform for that matter. In this post I will discuss some of the things you should consider when configuring a virtual machine to run on Hyper-V....(more)


Administrative