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