The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I was just starting to work in industry when Intel announced their Pentium bug, one in the floating point unit that cuased calculations to be wrong. Whether or not this was serious, the world thought so and Intel ended up recalling processors, taking a large financial loss. I had friends that were non-technical people wondering if Intel would survive and should they stick to other types of processors. Few did, and Intel went on to become to de facto choice for most systems.

This week there were articles that a design flaw in Intel chips requires kernel changes in Windows and Linux (and I'm guessing any other OS using the Intel platform like macOS, Unix, etc.). Linux and Windows have patches out that are being tested by early adopters and insiders. It's unclear exactly how serious this bug is, but there are potential issues when switching from user ot kernel mode, and that's the danger. Since information is scarce, and everyone seems to be very careful about giving details about how this could be exploited. However, there are seriously smart people in the world, some of whom have plenty of malicious intent, so everyone should be planning on patching their systems.

And that's potentially a problem. While I've seen some analysis of the initial patch testing showing that most user applications don't show many effects from the changes, there are issues with other systems. One analysis of Linux patches shows significant performace degradation for PostgreSQL. There is speculation that performance could drop from 15-30%. This is early on, and since people are in a rush to put out data, it's possible these are highly speculative and not representative of what you might see, but that is distressing.

If we lost 20% of performance here at SQLServerCentral, we'd be fine. Our systems have a lot of headroom, as you can see here. However, in a number of previous positions, and likely in some of your systems, losing 15-20% would mean that our database servers would be stressed, perhaps unable to meet the load they're designed for. At 30%, we might need to invest in new hardware.

I suspect Intel will end up taking a large hit here, and likely recalling and replacing lots of processors or offering incredible deals on additional CPUs or upgrades to try and avoid lawsuits. I would also take this time to recommend that all of you be sure that your systems are protected from access by unauthorized users and certainly not available on the Internet without strong firewall rules that limit access to known clients. I might also put off any consolidation plans for the next few months as you might need the extra CPU power if the patches don't perform as expected.

Steve Jones 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.

XML

Shreding XML with XQuery - Originally posted on: http://geekswithblogs.scottge.net/ktegels/archive/2006/01/20/ShredWithXQuery01.aspxOver on the Microsoft.Public.SqlServer.XML newsgroup, one Chris Kilmer asked a good question about how to shred a single XML document into multiple ......(more)

Vendors/3rd Party Products

The Database DevOps Challenges SQL Clone Solves - With its PowerShell integration, SQL Clone becomes an important component in a broader Database DevOps toolchain that provides automated logging, and reporting for all the processes required for database development, testing and delivery. It also allows the delivery process to be customized more precisely to the requirements of the team....(more)

Introducing database automation with the SQL Toolbelt - In this post, Mary Robbins explains how Absa bank improved development efficiency and minimized risk by implementing SQL Toolbelt....(more)

Finding code smells using SQL Prompt: old-style join syntax - SQL Prompt implements a static code analysis rule which will check code automatically for occurrences of non-ANSI standard JOIN syntax....(more)

T-SQL

Generating Random Passwords In Bulk - Idera Software hosted another SQL Chat on Twitter yesterday. One of the questions that came up was about security: Something I've worked on recently is generating new passwords for SQL authentication logins. Not just one or two, but in bulk. Hundreds ......(more)

SQL SERVER – Puzzle – Why Does UNION ALL Work but UNION Produces Error? - You already know what UNION and UNION ALL operators do. They combine datasets while UNION keeps the distinct data UNION ALL will retain all the data. You can read more about that over here: SQL SERVER – Difference Between Union vs. Union All – Optimal ......(more)

A Better Way To Script Database Objects - Happy New Year! My New Year’s resolution for 2018 is to help you become a better SQL developer. I want to start off with that today by showing you a much better way to generate database object change scripts....(more)

Heaps, Deletes, and Optimistic Isolation Levels - The Humble Heap If you don’t know this by now, I’m going to shovel it at you: If you have a table with no clustered index (a Heap), and you delete rows from it, the resulting empty pages may not be deallocated. You’ll have a table with a bunch of empty ......(more)

SSMS Solutions (Day 1) - Since SSMS is built upon Visual Studio, many of the features available to Visual Studio are also available to SSMS. The first one that I want to talk about is Solutions. Are you the type of person that has all of your custom queries in one folder, and ......(more)

T-SQL Tuesday #98 – Your Technical Challenges Conquered - Welcome to the January 2018 edition of T-SQL Tuesday and I am your host BlobEater (Arun Sirpal). If you do not know  what T-SQL Tuesday is then a quick recap. T-SQL Tuesday is a monthly blog initiative hosted by a different blogger each month. This was ......(more)

Tech News

Meltdown, Spectre Can Be Exploited Through Your Browser - According to the major browser vendors, attackers could exploit the recently discovered Meltdown and Spectre CPU vulnerabilities, but the vendors have prepared some temporary fixes. ...(more)

Intel Issues Meltdown, Spectre Patches For Newer CPUs - Intel announced that it has started issuing Meltdown and Spectre updates to manufacturers, which should send their updates their customers by the end of the week. However, only five years old or newer CPUs will be patched, leaving some chips vulnerable. ...(more)

AMD Soars After Rival Intel Said to Reveal Processor Flaw - Advanced Micro Devices Inc. surged in early trading after a report that Intel Corp., its only remaining rival in the market for personal computer processors, has a flaw in its products that makes commonly used operating systems vulnerable to hackers. ...(more)

SQL Server Security

How Secure is Transparent Data Encryption (TDE) – and How to Prevent Hacking - TDE is commonly described as “at-rest” encryption, i.e. it protects your data wherever it is stored on disk. This includes the database files, any backups taken (including Log and Differential), and any data that may get temporarily persisted to TempDB ......(more)

Microsoft SQL Server Updates for Meltdown and Spectre Exploits - Over the last couple of days, you have probably heard quite a bit of chatter and speculation about some newly disclosed ways to attack various processors. The initial reports were that only Intel processors were affected, but some sources indicate that ......(more)

SQL SERVER – SQL Vulnerability Assessment – Security Analysis - Microsoft has recently announced a very interesting feature for security of your SQL Server. I really loved this new feature – SQL Vulnerability Assessment. Unlike most of the other V1 features which MS releases, this time I really liked this particular ......(more)

Using Signed Assemblies for SQLCLR: Doing the Safety Dance. - Originally posted on: http://geekswithblogs.scottge.net/ktegels/archive/2006/02/16/69762.aspxYou know that song. Yes, that song. The beeping. The arm flailing. The Safety Dance. I so wanted it stay in the 80s – along side the uncounted Wild Turkey inflicted ......(more)

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining - Despite features added in SQL Server 2005 (yes, 2005!) that allow for very flexible, granular, and robust security, it is still quite common for people to be using the older, riskier mechanisms of temporarily granting additional privileges. What follows ......(more)

Software Development

High Availability Team Foundation Server (TFS) in Azure - A few months ago, I published a template for Deploying Team Foundation Server (TFS) in Azure. Since that approach has particular relevance for Azure Government users, the topic was also picked up by the AzureGov blog. The previous template shows you ......(more)

Security news and thoughts

Understanding Meltdown & Spectre: What To Know About New Exploits That Affect Virtually All CPUs - It seems only fitting that one of the two hardware based exploits to rock the CPU world this week was named Meltdown. Because for the last 24 hours or so, it feels like I’ve been on the verge of one just trying to keep up with all of the new information ......(more)

Spectre and Meltdown Attacks - After a week or so of rumors, everyone is now reporting about the Spectre and Meltdown attacks against pretty much every modern processor out there. These are side-channel attacks where one process can spy on other processes. They affect computers where ......(more)

Six Cyber Threats to Really Worry About in 2018 - From AI-powered hacking to tampering with voting systems, here are some of the big risks on our radar screen. ...(more)

New bill could finally get rid of paperless voting machines - Enlarge / Georgia voters at voting machines during the US presidential election on November 8, 2016. (credit: TAMI CHAPPELL/AFP/Getty Images) A bipartisan group of six senators has introduced legislation that would take a huge step toward securing elections ......(more)

Replication

Simulating Bad Networks to Test SQL Server Replication - SQL Server Replication usually works just fine when testing in a development environment where there’s low latency and high bandwidth. Real world conditions aren’t always like that. I’ve published instructions on how to use the free WANem network emulator ......(more)

R Language

Make your R code run faster - There are lots of tricks you can use to make R code run faster: use more efficient data structures; vectorize your R code; offload complex data management tasks to databases. Emily Robinson shares many of these R performance tips in a case study on A/B ......(more)

Do you have bad R habits? Here's how to identify and fix them. - RStudio's Jenny Bryan (whose recent interviews here and here you should definitely check out) has some excellent advice for improving your workflow for R: If you're you're routinely using setwd to manually change R's working directory, or using rm(list ......(more)

Python in SQL Server

SQL Server Machine Learning Services – Part 3: Plotting Data with Python - One of the advantages of running Python from SQL Server is the ability to create graphics to assist in analysis of data. Robert Sheldon demonstrates matplotlib, a 2D plotting library, widely used with Python to create quality charts....(more)

PowerShell

Azure and Windows PowerShell: The Basics - It shops do not want to spend time and energy managing infrastructure and servers as was done in the past. They must conserve resources for the things that add value for the business. Today, it is challenging for companies to automate and configure their Azure infrastructure. In the first part of his new series, Nicolas Prigent introduces the basics about Azure and Windows PowerShell for DevOps and Sysadmin....(more)

PowerPivot/PowerQuery/PowerBI

Fonts, Images, Power BI Contests and more… - Font Families in Power BI (@dataveld) Dynamic Attributes In A Power BI Report (@PowerPivotPro) Embedding Images in Power BI using Base64 (@SQLJason) Year in Review Contest: Jan. 3-Jan. 31 Happy New Year from Power BI Desktop BONUS ITEMS: EARLIER vs EARLIEST ......(more)

Power BI Custom Visuals Class (Module 84 – ChartAccent – BarChart) - In this module you will learn how to use the ChartAccent BarChart Custom Visual. This visual is a custom bar chart that allows you to annotate individual data points, data series and ranges. Module 84 – ChartAccent – BarChart Downloads Power BI Custom ......(more)

Polybase/HDInsight

Azure HDInsight Performance Insights: Interactive Query, Spark and Presto - Cross post from https://azure.microsoft.com/en-us/blog/hdinsight-interactive-query-performance-benchmarks-and-integration-with-power-bi-direct-query/ Fast SQL query processing at scale is often a key consideration for our customers. In this blog post ......(more)

XBox: Analytics on petabytes of gaming data with Azure HDInsight - Cross post from https://azure.microsoft.com/en-us/blog/how-xbox-uses-hdinsight-to-drive-analytics-on-petabytes-of-telemetry-data/ Microsoft Studios produces some of the world’s most popular game titles including the Halo, Minecraft, and Forza Motorsport ......(more)

Performance Tuning SQL Server

SQL Homework – January 2017 – Inspect an Execution Plan - It’s a new year and yet education never ends. So this month let’s take a look at an important part of performance tuning. The Execution Plan. When a query is run, the optimizer goes through and selects what it feels will be the good enough plan to plan ......(more)

Microsoft News

Microsoft Acquires Hybrid Cloud Storage Vendor Avere Systems - Hybrid cloud storage vendor Avere Systems has been scooped up by Microsoft for tighter integration with Azure. ...(more)

Microsoft in 2017: Windows 10, company layoffs are the big stories - In spite of Microsoft's continued push to be a cloud-first/AI-first company in 2017, ZDNet readers still were Windows watchers, first and foremost. ...(more)

Hardware News

Latest Dell XPS 13 Laptop Sports Kaby Lake-R, New Cooling - Dell unveiled its newest version of the XPS 13 laptop, touting a new thermal design, an even thinner and lighter chassis, and Intel 8th generation processors. ...(more)

HA/DR/Always On/Clustering

Snow, Heathrow Airport, Disaster Recovery - I recently flew from Boston to the UK through the Heathrow airport. It just happened to be on the day that the UK got about 1.5 inches of snow (sorry, 3.8 centimetres, according to Weather Underground though, just 15mm, not sure about that). I spent ......(more)

DevOps and Continuous Delivery (CI/CD)

DevOps for Data Science – Continuous Delivery - In this series on DevOps for Data Science, I’ve explained the concept of a DevOps “Maturity Model” – a list of things you can do, in order, that will set you on the path for implementing DevOps in Data Science. The first thing you can do in your projects ......(more)

Database Design, Theory and Development

Validation, Verification, and Modification - A proper database design is very important, and changes to fix problems after the fact are expensive. In this article, Joe Celko discusses three aspects of database design that are often overlooked: validation, verification, and modification....(more)

Data Warehousing

SQL Data Warehouse – Fast row counts - A couple of years ago I published a script to calculate fast, light-weight, row counts for SQL Server on-premises. The need to view the row counts of tables has not diminished but new technologies have come to the fore-front. Azure... The post SQL Data ......(more)

Data Visualisation

Know Your Audience - This is part two of a series on dashboard visualization. Before you build a dashboard, you have to know your audience.  If you don’t know who your viewers will be and where their interests lie, you run the risk of building a dashboard which fails to ......(more)

What Is A Dashboard? - This is part one of a series on dashboard visualization. Dashboards serve one purpose in life:  to tell us about critical business or operational metrics. This is a dashboard: The best dashboard for the best car. This dashboard tells me several things.  ......(more)

Data Mining/Data Analysis

Practicing Statistics: Female DBAs and Salary - Brent Ozar recently ran a salary survey for people working with databases, and posted an article: Female DBAs Make Less Money. Why? Many of the responses were along the lines of “Well, duh.” I, personally, felt much of the same thing....(more)

Computing in the Cloud (Azure, Google , AWS)

An Introduction to Azure Event Grid - Azure Event Grid (in preview) is a new event routing service that works with Azure Logic Apps and Azure Functions. It’s one more solution that enables developers to focus on business value, not on infrastructure. Christos Matskas explains how this new feature works and walks you through a simple example....(more)

Set auto-shutdown for virtual machines in Azure - You can schedule auto-shutdown for virtual machines created either through the Azure Resource Manager or Azure DevTest Labs, to mitigate the cost waste from running VMs after working hours. This feature was originally available only for VMs in Azure ......(more)

Career Growth

Creating a SQL Server Test Lab On Your Workstation – Part Two, Creating a Domain Account and Joining a SQL Server to the Domain - Part 1 of Creating a SQL Server Test Lab On Your Workstation can be found HERE  In the second part of our series on creating a SQL Server test lab on your workstation, I’m going to look at creating a domain user, joining a SQL Server to our domain and ......(more)

Try Your Hand at 30+ Free SQL Server Quizzes - Writing quizzes is a tricky thing: you learn quickly how challenging it is to choose precise, accessible, and accurate words in questions and answers. Lately, I’ve been writing a quiz a week. Like anything else, the more quizzes you write, the better ......(more)

Backup and Recovery

The Perils Of VSS Snaps - So much, so often Ah, backups. Why are they so tough to get right? You start taking them, you find out you’re not taking enough of them, or that they’re not the right kind, or that you’re not using checksums or compression, or that you’re not storing ......(more)

Backing up to NUL: - Backing up to NUL is like backing up directly to the trash Linux folks have lots of jokes & fun t-shirts about /dev/null/. The null device is a handy location to dump output to, when you don’t actually care about the output. In PowerShell, you can use ......(more)

Azure SQL Database

Azure SQL Analytics - Azure SQL Analytics is currently in preview mode, still it is very impressive. The goal of this feature is to visualize important SQL performance metrics for your Azure SQL Database. There are a couple of things you need to do first. Setup a Log Analytics ......(more)

Database scoped optimizing for ad hoc workloads - SQL Server provides the “optimize for ad hoc workloads” server-scoped option that is used to reduce the memory footprint of single use ad hoc batches and associated plans.  When enabled at the SQL Server instance scope, the “optimize for ad hoc workloads” ......(more)

Azure SQL Database - Backup - Most of you must be knowing that backup in Azure SQL Database fully automatic. After the database is created and the database is automatically backed up by Azure. One can restore to any point in time for a period of 7 days for the basic subscription ......(more)

Azure SQL Data Warehouse and Data Lake

Zones in a Data Lake - As we are approaching the end of 2017, many people have resolutions or goals for the new year. How about a goal to get organized...in your data lake?The most important aspect of organizing a data lake is optimal data retrieval.It all starts with the ......(more)

Azure CosmosDB

Azure CosmosDB in Banking Sector - Other day I was reading about change feed feature of Azure CosmosDB and thought of possibilities can be driven from it. That triggered me to test a scenario which we deal with in our day to day life which is getting notification ......(more)

Analysis Services / BI on the MS Stack

Using the Resource Usage Profiler Event For SSAS Multidimensional Query Performance Tuning, Part 3: Aggregations And Indexes - Building aggregations in your SSAS Multidimensional will make your queries faster, right? While that’s true, they will only make a noticeable difference to performance if your query has Storage Engine-related problems rather than Formula Engine-related ......(more)

AI/Machine Learning/Cognitive Services

SQL Server Machine Learning Services – Part 3: Plotting Data with Python - The series so far: SQL Server Machine Learning Services – Part 1: Python Basics SQL Server Machine Learning Services – Part 2: Python Data Frames SQL Server Machine Learning Services – Part 3: Plotting Data with Python With the release of SQL Server ......(more)

SQL Server ML Services Fills The Plan Cache - We call SQL Server ML Services a lot.  As in building hundreds of thousands of times a day to build models.   It turns out that doing this has a negative effect:  ML Services plans end up staying in the plan cache and don’t get removed.  Here’s how our ......(more)

Administration of SQL Server

Sharepoint Diagnostics and XE - One of the all-time greatest and most beloved applications among DBAs happens to be Sharepoint. Most of us would be lying if we said that we loved Sharepoint and the kind of performance issues it can cause on a SQL Server. When you have an application ......(more)

SQL SERVER – Slow Filestream Data Cleanup. What Should We Do? - One of my clients reported an issue where they were running low on free disk space on the drive where they have file-stream files. They are one of a heavy user of the filestream feature as compared to any of my previous clients. As per their observation, ......(more)

How to fix Orphaned Users easily - What are Orphaned Users “Orphaned Users” is a common issue in SQL Server where a Database User is no longer associated with its relevant Server Login. This often happens when the Server Login is deleted (even if  it’s recreated later), or when the database ......(more)

SQL Server DBA Morning Health Checks - Introduction: As a Microsoft Premier Field Engineer, I get to work with amazing colleagues who create incredible customer solutions. Patrick Keisler ( blog | | ) is a long time SQL Server professional, who also works as a PFE supporting customers throughout ......(more)

Correlate SQL Trace and Actions - I have recently written about the pains of correlating SQL Trace events to Extended Events (XE) events. That article can be found here. At that time, I did not dive into another facet of trace that truly needs converting as well. That component is known ......(more)

Finding active transactions - Confessions of a carbaholic: When I start thinking about `ROLLBACK`, I start thinking about rolls. Have you ever forgot to commit a transaction? Maybe you’ve even left for lunch and caused a problem while you were gone. Uncommitted transactions can cause ......(more)