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
Cortana and Schema.org - Some definitions: Cortana – Microsoft’s digital personal assistant, competing with Apple’s Siri and Google Now. “She gets to know you by learning your interests over time. She looks out for you, providing proactive, useful recommendations. And Cortana ......(more)
The Internet of Things: enabling the era of precision manufacturing - Remember pictures from old factories where a low-level employee roamed the factory floor on a regular schedule, writing down on a clipboard readings from gauges and other instruments? Who knows when his supervisor ever looked at the results, and what ......(more)
The Case for Moving From TPC to Database Throughput Units in Database Performance Comparisons - Scientific testing is based on controls, transparency, and repeatability. Whenever we as technical professionals want to test the performance of a database system, we search for a series of tests that show the system’s metrics against a standard.
Obama panel supports warrant requirement for e-mail, cloud content - Jeff DeMaria/Flickr
On Thursday, a panel commissioned by President Barack Obama to examine the implications of "big data" concluded that cloud and e-mail content should be constitutionally protected—a recommendation that Congress is seemingly unwilling ......(more)
Tech News : Security
It’s more Pluralsight, it’s more website attacks and it’s more security - How much really changes in only three short years in the world of application security? Ok, a few sites get owned and some nasty hackers come up with some new ways of making some poor developers lives a misery but that’s about the extent of it, right? ......(more)
Data Theft a Major Concern for Organizations - Many businesses believe cyber security threats fall through the cracks of their companies’ existing security systems, a survey finds.
Judge: Microsoft Must Obey US Warrant Seeking Data Stored in Ireland -
US federal judge strikes down Microsoft’s challenge to warrant for customer’s data stored overseas Read More
Stanford’s password policy shuns one-size-fits-all security - Stanford University network engineers have unveiled a refreshingly enlightened password policy. By allowing extremely long passcodes and relaxing character complexity requirements as length increases, the new standards may make it easier to choose passwords ......(more)
Supreme Court to rule on warrantless searches of electronic devices - Elvert Barnes/Flickr
The Supreme Court on Tuesday will take on the digital-age controversy over search and seizure of smartphones and other devices. In two cases coming before the court, warrantless searches of an electronic device not only provided ......(more)
Google Chrome protection for Heartbleed-hacked sites called “completely broken” - Aurich Lawson
Update: A few hours after this article went live, Google engineer Adam Langley published a blog post taking issue with the GRC characterization that Chrome's CRLSet is "completely broken." In the post, Langley said he has always been ......(more)
SQL Server News : Bugs/KB
SQL 2012 and SQL 2014 NUMA Fix - SQL 2012 and SQL 2014 NUMA Fix
Typically a SQL Server Cumulative Update release is not a noteworthy event, however SQL Server 2012 SP1 CU9 and SQL Server 2014 CU1 include a fix that is noteworthy from a from a CAT Lab perspective. In short, if you have ......(more)
Introducing Microsoft SQL Server 2014 Book Review - Microsoft’s newly released SQL Server 2014 database is closely followed by its first book, how does it fare?...(more)
Microsoft News : Patches, Bugs
Emergency patch for critical IE 0-day throws lifeline to XP laggards, too - Brandon Weeks
Microsoft has released an emergency update for all recent Windows operating systems—including the recently decommissioned XP—fixing a critical security bug that is currently being exploited in real-world attacks.
The decision to patch ......(more)
Blogs : Administration
What is a Non-Clustered Columnstore Index? - First introduced in SQL Server 2012, the Columnstore index is a new in-memory feature that allows for the creation of indexes that are stored in a column-wise fashion. It is targeted for data warehouses and can improve query performance by 10 to 100x. ...(more)
SQL SERVER – Good Value for Page Life Expectancy – Notes from the Field #026 - [Notes from Pinal]: In the past, I have been wrong many times, but I was man enough to accept my mistakes and correct myself. Page Life Expectancy is a very similar subject for me. In the past when I had written a blog post based on Microsoft’s white ......(more)
Shrinking tempdb no longer prohibited - For the longest time the guidance around tempdb is that if you shrink it on a live system then it could cause tempdb corruption.
A few months ago I was discussing this with my good friend Bob Ward from Product Support and neither of us could remember ......(more)
Blogs : Analysis Services / BI
DAX #2 – Installing AdventureWorks DW Tabular Model SQL Server 2012 - OK, first thing first. We all know the famous Adventure Works multidimensional sample project. To learn the tubular mode/DAX, we also have a sample tabular project.
Download sample database and projects from CodePlex
You can follow this link to CodePlex ......(more)
Blogs : Computing in the Cloud
Encryption in the cloud is scarcer than you think - The Ponemon Institute, working in conjunction with encryption solutions provider Thales e-Security, has produced its third annual rep
Azure SQL Database: New Service Tiers Q&A - Earlier this month, we celebrated the launch of Microsoft SQL Server 2014, announced that the Analytics Platform System is generally available, and shared a preview of the Intelligent Systems Service. Quentin Clark summarized his keynote speech at the ......(more)
Blogs : DMO/SMO/Powershell
Powershell Goodies - If you haven’t seen some of the other blog post, or the Microsoft Powershell Blog, Microsoft recently release Script Browser for Powershell ISE. This little addin allows you to quickly search and import Powershell scripts directly from TechNet Script ......(more)
Blogs : Excel
Power Query to HDFS – Remote Name Could Not Be Resolved - I wanted to share a quick resolution I had to a problem with connecting Power Query to data stored in HDFS.
Seems easy enough. Launch Excel and through Power Query select From Other Sources –> From Hadoop Files (HDFS)
Next you’re prompted to provide ......(more)
Blogs : Performance and Tuning
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 ......(more)
The Performance of Traversing a SQL Hierarchy - Dwain Camps show that, depending on the size and characteristics of some hierarchical data, six different methods of traversal can each be the fastest at some point. He illustrates convincingly that It is dangerous to generalize from just one set of ......(more)
How It Works: Behavior of a 1 Trillion Row Index Build (Gather Streams from SORT) - I ran into this behavior working on a 1 trillion row, spatial index build but the behavior can apply to any Gather Streams operator, retaining the sort order as rows pass though it. I was just surprised a bit by the behavior until I dug deeper to understand. ...(more)
Introduction to Extended Events (video) - Extended Events were introduced with SQL Server 2008. Extended Events is a replacement for Server Side Traces and so much more – it’s a lightweight way to look at SQL Server performance, events, deadlocks, locking, blocking, and more. In this webcast, ......(more)
Blogs : PowerPivot
How to handle fact tables with different granularities in #dax #powerpivot #tabular - A common question I receive from Excel users learning Power Pivot is how to handle table that have different granularities. In reality, this terminology is not the one they use: the concept of “table granularity” is used mostly by Kimball practitioners, ......(more)
Blogs : Replication
Why Can't I Reinitialize My Subscriber? - I recently had a colleague request help with a replication problem he was having with a client. The client was running transactional replication from a SQL Server 2005 Publisher to multiple SQL Server 2005 Subscribers and was having issues after attempting ......(more)
Blogs : Reporting Services
SharePoint Adventures : Using Claims with Reporting Services - Back in February of 2011, I created a blog that walked through using Kerberos with Reporting Services. Since then, we have moved Reporting Services to a shared service within SharePoint. This changes the game and we are now in the Claims world. I’ve ......(more)
Blogs : SQL Server 2014
SQL Server 2014 Managed Lock Priority for Partition Switch and Online Reindex - Online reindex and partition switch have both been around for a while now, however one of the biggest issues which still exists is they both require a schema modification lock which is very unfriendly with other locks.
From the lock compatibility ......(more)
SQL Server 2014 Updatable Columnstore Index - I have had many calls with customers that essentially ended with your I/O subsystem cannot keep up with your workload. The obvious next question was how do we solve that? My answer was usually get a faster IO subsystem or do less IO! Now, SQL 2014 ......(more)
The Cardinality Estimator in SQL Server 2014 – Going Forward and Backward - I saw a talk from Joe Sack (b | t) on the cardinality estimator (CE) in SQL Server 2014 and found it very interesting. To be fair, some of the "how it works" isn’t something I care about much, but I did like Joe sharing some places in which you might ......(more)
Delayed Durability in SQL Server 2014 - Delayed Durability is a late-breaking but interesting feature in SQL Server 2014; the high-level elevator pitch of the feature is, quite simply:
"Trade durability for performance."
Some background first. By default, SQL Server uses a write-ahead log ......(more)
SQLAuthority News – Two Microsoft Whitepaper on In-Memory OLTP - Earlier this week, I was presenting at Great Indian Developer Summit in Bangalore. Lots of people were interested in the subject of In-Memory OLTP database. Everybody wanted to know how to do, use the new features of In-Memory OLTP feature. Microsoft ......(more)
Memory and IO in Tempdb: SQL 2014 Improvement - I was excited to read Bob Dorr’s recent post about a hidden gem in tempdb performance in SQL Server 2014. I had a copy of SQL Server 2014 Developer Edition handy, as well as a workload that beat up tempdb, so I decided to do a quick performance test. I ......(more)
Blogs : T-SQL
Video: Designing Stored Procedure Solutions - My new session for 2014, “Designing Stored Procedure Solutions”, is now up on the Sessions page: abstract, video, and code! This month, I gave this session at the Oklahoma City SQL Server User Group‘s 5 year anniversary, and at the Alaska SQL User Group, ......(more)
Find SQL Database Owner with T-SQL - One thing that on occasion bugs me about PDW or APS or whatever it happens to be called this week is the lack of a UI to do things. I’m not one of those people who has to have a UI to perform operations. I have no problem creating databases, doing backups ......(more)
DBA Myths: TRUNCATE TABLE deletes all the rows of a table instantly. - Most DBAs and developers I’ve talked to over the years have felt that TRUNCATE TABLE is an instant DELETE. However would you believe that it doesn’t actually delete the data from the pages at all? Here is an example:
-- Setup code
CREATE DATABASE TruncateTest
Stupid T-SQL Tricks – Part 1: Logarithms - Not just for math geeks, logarithms and their inverse functions (the exponentials) can have useful and often unexpected capabilities in T-SQL.
“Heeeeeere’s Johnny!” – Ed McMahon introducing Johnny Carson for the Tonight Show
If you’re old enough ......(more)
Blogs : Virtualization
Getting the most out of Hyper-V Dynamic Memory - Microsoft introduced Dynamic Memory in Windows Server 2008 R2 (W2008 R2) Service Pack 1 (SP1) to change how memory is assigned to virtual machines. Instead of assigning all of a virtual machine’s potential RAM to the virtual machine, you could opt to ......(more)
Blogs : XML, XPATH and XQUERY
How to grab multiple parent/child elements from XML Data Source - We had a question come up about why an XML query wasn’t pulling a 2nd Parent/Child element from the resulting data. This was grabbing data off of a Web Service. The example I was given was a public Web Service call for getting Weather information. ......(more)