The Complete Weekly Roundup of SQL Server News

In this issue:

Tech News : Data Mining/Warehousing

Tech News : General Interest

Tech News : The Lighter Side

Blogs : Administration

Blogs : Big Data

Blogs : Computing in the Cloud

Blogs : Data Access / ORMs

Blogs : Database Design, Theory and Development

Blogs : DBA Tools

Blogs : DMO/SMO/Powershell

Blogs : Events

Blogs : Integration Services/ETL

Articles : Administration

Articles

Articles : Development

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 2014-05-10

Azure Management Studio Boost Windows Azure productivity with Azure Management Studio
Manage your Windows Azure storage, diagnostics and compute in one place with Azure Management Studio from Cerebrata. Begin a free trial.
Fundamentals of SQL Server Replication eBook Get to grips with SQL Server replication
In this FREE eBook, Sebastian Meine guides you through the fundamentals of SQL Server Replication, to the point where you should feel comfortable using it in production. Download the free eBook.
SQL Doc New! SQL Doc 3
Rapidly documenting your entire database schema just became even easier with SQL Doc 3. Includes a new UI, documentation of database- and server-level properties, and greater customization control. Download a free trial now.
Editorial - Just Enough know-how

SQL Server offers very simple interfaces to many of its features. Most people could open up SSMS, connect to a server, write a simple query and see the results. Even several of the core DBA tasks are deceptively straightforward. It doesn't take a rocket scientist to perform a basic database backup or run a trace.

However, appearances can be deceptive, and oftentimes it is really important that a DBA understands not just the basic of how to perform a task, but why we do a task, and how that task works. As an analogy, consider a child walking into a darkened room. Most would know that they need to turn on the light, and how to do it, so they flick the switch. But what happens if light fails to shine forth? Most would tell you immediately that you need to consider changing the light bulb. So you hop in the car and take them to the local home store and instruct them to buy a replacement. Confronted with a 40 foot display of light bulbs, how will they decide which of the hundreds of types of bulbs, of different types, fittings, shapes, colors, power and efficiency ratings, is the right choice? Most would resort to guesswork, and resolve to use their cell phone as a flashlight next time, so they don't have to ask for Dad's help.

Likewise, when the metaphorical toddlers who use our database server have issues, they will instinctively know something is wrong, and may even have some idea what caused it, but will have no depth of knowledge to figure out the right solution. That is where the DBA comes in and attempts to save the day. However, when one looks beneath the shiny UI, SQL Server has its own "40 foot display of light bulbs", in the form of the tremendous number of tools and the often-bewildering amount of information they can present to the DBA, to help us find issues. Unfortunately, we can't resort to guesswork, to trying different "bulbs" over and over, hoping to stumble on the answer. This is where the right depth of knowledge goes a long way.

If we need to write a SELECT statement, then knowing the syntax and where to find the data is not enough. Knowledge of indexes and query plans is essential. Without it, we might hit on a query that "works", but we are basically still a user, not a programmer, because we have no real control over our platform. Is that level of knowledge deep enough? Probably not, since knowledge of the underlying metadata and structures would be very useful in helping us make sense of any query plan. Understanding the structure of an index makes the "key lookup" operator sound less like what happens when someone taped your car key to the ceiling.

So is even this level of understanding deep enough? Do we need to understand the memory architecture used to process the query? It might be a comforting level of knowledge, and will doubtless come in handy at some point, but is not strictly necessary in most cases. Beyond that lies (more or less) full knowledge of SQL language and the intricacies of every step the SQL Server engine takes to process our query.

My personal theory is that, as a professional, our knowledge of a given task should extend, at a minimum, one level deeper than is strictly necessary to perform the task. Anything deeper can be left to the ridiculously smart, or obsessive, or both. For example, tasked with storing an integer value between 0 and 99999999, it's essential that I know that choosing an Integer over Decimal(8,0) will likely offer performance benefits. It is then useful that I also understand the value of adding a CHECK constraint, to make sure the values are in the desired range, and comforting that I know a little about the underlying processors, registers and computer math. Anything further, I leave to the likes of Joe Chang, whose recent blog post on the topic offers depth by the bucketful!

Louis Davidson (Guest Editor)

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

Tech News : Data Mining/Warehousing

The Goldfish Effect: How Short Attention Spans are Killing your Productivity - According to the National Center for Biotechnology Information, the average attention span was 12 seconds in 2000. By 2013, that had dropped to eight seconds. That's eight seconds of total concentration and processing power before the brain moves on to the next task. Compare that to the average attention span of a goldfish, which is nine seconds. ...(more)

Tech News : General Interest

Amazon’s latest patent is sillier than the peanut butter sandwich patent - Thought the peanut butter sandwich patent was a joke? That one doesn't even register a chuckle compared to a patent recently granted to Amazon.com. The e-commerce giant now can claim a legal monopoly on the process of photographing people and things against a white backdrop....(more)

Arduino Vs. Raspberry Pi: Which Is The Right DIY Platform For You? - Actually, Arduino and Raspberry Pi are quite different. For starters, Raspberry Pi is a fully functional computer, while Arduino is a microcontroller, which is just a single component of a computer. ...(more)

Tech News : The Lighter Side

The truth behind Open Source apps - How you think Open Source apps are maintained ..... How Open Source apps are really maintained...(more)

Blogs : Administration

Read Committed Snapshot Isolation - SQL Server provides two physical implementations of the read committed isolation level defined by the SQL standard, locking read committed and read committed snapshot isolation (RCSI). While both implementations meet the requirements laid down in the SQL standard for read committed isolation behaviours, RCSI has quite different physical behaviours from the locking implementation we looked at in the previous post in this series...(more)

How Automatic Updates to Statistics Can Affect Query Performance - In my previous post, I explored different methods to track automatic updates to statistics to determine if they were affecting query performance. In the latter half of the post I included options, one of which was to enable the Auto Update Statistics Asynchronously database setting. In this post, I want to look at how query performance changes when the automatic update does occur prior to query execution, and what happens to performance if the update is asynchronous....(more)

Error Log Management - always change the maximum number of error logs to 30 (at a minimum) and then recycle the error log each night. This way I can keep a month’s worth of logs in easily manageable portions (this does depend on the system but one day’s worth of logs in each file is generally OK)....(more)

4 Lightweight Ways to Tell if a Database is Used - We’ve all found those databases. They’re on your production SQL Server instance, but nobody seems to know if they’re being used, or what’s using them. You could run some sort of trace, but you’d probably drag down performance on your SQL Server. What you need is a quicker way to find out if the database is in use....(more)

Size Matters: Table Rows And Database Data Pages - Wouldn’t it be cool if you could quickly look into a database to see which objects were close to storing one row per page? Turns out we can! I have a script here that works for SQL2012 and later. This script will return the name of the table, the name of the index, the number of pages, the number of rows, and the ratio of rows to pages. It then orders the results by the objects storing the fewest number of rows on a page, giving you the opportunity to focus your tuning efforts on those objects first....(more)

Causes of IO_COMPLETION and WRITE_COMPLETION SQL Server wait types - I used the Extended Events code in my post How to determine what causes a particular wait type to watch for these wait types occurring and then ran a variety of operations and analyzed the call stacks. There are way too many occurrences to document them all here, so I’ll summarize my findings...(more)

Introducing sp_BlitzCache™ - sp_BlitzCache™ analyzes the top poorly performing queries for many different problems. After finding your bad habits, sp_BlitzCache™ builds a list for easy analysis. Your bad habits are outlined in a summary result set:...(more)

Entity Framework Pitfalls – Deleting Orphans - Entity Framework does not really have the notion of parents and children, only of relations. Some of these relations can be mandatory, others can be optional. It is possible to specify cascade delete rules, so that when an entity is removed from the database, all entities that depend on it are also removed. However, what happens if we remove a related (child) entity from it’s parent’s children collection?...(more)

Cannot Failover Nodes Using TempDB on Local Storage - I ran into an interesting issue while installing SQL 2012 SP1 in a failover cluster configuration and utilizing the local TempDB feature. ...(more)

Blogs : Big Data

Why big data is in trouble: they forgot about applied statistics - When you actually take the time to do an analysis right, with careful attention to all the sources of variation in the data, it is almost a law that you will have to make smaller claims than you could if you just shoved your data in a machine learning algorithm and reported whatever came out the other side....(more)

Blogs : Computing in the Cloud

Azure SQL Database: Service Tiers & Performance Q&A - A few days ago, I published a post with some anticipated questions & answers to provide details on the new service tiers for Microsoft Azure SQL Database, announced on April 24. In this follow-up post, I want to provide more information about how SQL Database performance is factored into the service tiers...(more)

Blogs : Data Access / ORMs

NoSQL? No, SQL! – How to Calculate Running Totals - We developers (or our customers) are paying millions of dollars every year to Oracle, Microsoft, IBM, SAP for their excellent RDBMS, only to ignore 90% of their database features and to perform a little bit of CRUD and a little bit of ACID with ORMs like Hibernate. We have forgotten about why those RDBMS were so expensive in the first place. We haven’t paid attention to the various improvements to the SQL standards, including SQL:1999, SQL:2003, SQL:2008, and the recent SQL:2011....(more)

Blogs : Database Design, Theory and Development

http://mattsql.wordpress.com/2014/05/07/migrating-sql-server-logins/ - Once upon a migration I would use sp_help_revlogin to migrate logins and remap the SQL server logins using sp_change_users_login. During some recent migration work I realised that SQL Server Management Studio could do most of the work of sp_help_revlogin. I still needed to use sp_change_users_login – but I came across a couple of other tips to solve a couple of issues....(more)

How to insert entities into Azure Table Storage using Powershell. - In order to insert entities into Azure Table storage while executing powershell scripts, such as when you want to log any kind of deployment information, this is one of the approach that can be followed....(more)

Losing Precision After Multiplication in SQL Server - We need to minimize the use of precision and scale in the data type that we use such that the resultant precision is not exceeding 38 Otherwise, SQL server tries to avoid truncating the integral part by reducing the scale while preserving a scale of 6 in both division and multiplication ...(more)

New release of Perl DBD::ODBC with support for MS SQL Server Query Notification - Query notification allows an application to request a notification from SQL Server when the results of a query change. Once set up you can block on an execute call waiting for the query to change. Query notifications were introduced in SQL Server 2005 and SQL Server Native Client. Query notifications allow applications to be notified when data has changed....(more)

Trees as graphs vs. Trees as data structures - We use trees all the time to define data structures as implementations of abstract data types (ADTs) or as the basis for algorithms. For sets/dictionaries/indexes we have binary search trees, 2-3 trees, B-trees and relatives; for priority queues we have heaps (a form of binary tree); and for pattern matching we have tries and suffix trees...(more)

Blogs : DBA Tools

How SQL Server 2014 impacts Red Gate’s SQL Compare - SQL Compare 10.7 successfully connects to SQL Server 2014, but it doesn’t yet cover the SQL Server 2014 features which would require us to make major changes to SQL Compare to support. In this post I’m going to talk about the SQL Server 2014 features we’ve already begun supporting, and which ones we’re working on for the next release of SQL Compare...(more)

Blogs : DMO/SMO/Powershell

Validate File Properties with PowerShell Prior to Import into SQL Server - We recently faced an issue with an internal data provider where we imported a flat file at 1AM, but in the past few months, the data provider began occasionally providing an incomplete or inaccurate file because it lacked the proper length....(more)

Blogs : Events

SQL Saturday #317 - Oslo - Call for Speakers - The call for speakers is open for SQL Saturday #317 - Oslo. This event is taking place on Aug 30, 2014 and speakers are needed. If you can attend the event and are willing to teach your fellow professionals something, consider submitting a session....(more)

Blogs : Integration Services/ETL

Using the New OData Source in SQL Server Integration Services - Microsoft recently released the OData source adaptor for Integration Services (SSIS). Using this source component, it becomes really straightforward to read data from OData sources, such as SharePoint lists. This tip will walk you through the installation and configuration of this brand new component. ...(more)

Articles : Administration

Optimizing Transaction Log Throughput - As a DBA, it is vital to manage transaction log growth explicitly, rather than let SQL Server auto-growth events "manage" it for you. If you undersize the log, and then let SQL Server auto-grow it in small increments, you'll end up with a very fragmented log. ...(more)

Articles

SQL Code Guard - Later SSMS 2012 emerged on the scene accompanied with totally new integration challenges. And don’t get me started on SSMS 2014! It turns out that I was not the only guy who was frustrated with problems when writing SSMS addins. A smart guy from Red Gate, David Pond, has developed the Red Gate SSMS Ecosystem, a framework that has made creation of SSMS addins as simple as A-B-C! It’s close to magical!...(more)

Articles : Development

Producing JSON Documents from SQL Server queries via TSQL - Although SQL Server supports XML well, XML's little cousin JSON gets no love. This is frustrating now that JSON is in so much demand. Maybe, Phil suggests, it is possible to leverage all that XML, and XPath, goodness in SQL Server to produce JSON in a versatile way from SQL Queries? Yes, it so happens that there are plenty of alternatives....(more)


Administrative