The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Frequently, on various SQL Server-related forums, I see people typing out the most fundamental questions, such as basic syntax of a SQL command, clearly desperate for an answer. As a forum regular, it can be frustrating to be asked to explain the difference between a block and a deadlock for the 6th time this week, when you know that a simple search against the search engine of your choice will immediately boil up the answer. What the heck is going on? Why are people taking the time to create an account on a forum, type in a long and convoluted question about how to use a WHERE clause when typing about one-fifth as much text into a search engine gives them immediate answers?

I don’t have an answer, but I have a speculation. There’s an old joke that refers to Boogle as the junior DBA, because it's the Junior DBAs job to remember how to do all the routine tasks that you don't want to keep in your head. Ask your "junior DBA" for the basic syntax to do an OUTER JOIN, and out comes an immediate answer.

However, for many inexperienced developers and DBAs, the search engine isn't a font of useful SQL Server answers. Instead, it's a giant black hole into which you pour time, and all you get back is confusion and doubt. One web site emphasizes the vital importance of Page Life Expectancy and how if it falls below 300, it's the death knell for the performance of your server. The next web site will point out what a questionable measure it is and how it depends on a whole slew of other metrics with which you're unfamiliar. These apparent contradictions aren't helpful at all, unless you happen to know which source to trust. Heck, even answers that are now "wrong" were probably right 10 years ago, and it's those answers that often still bubble to the top.

The problem for many people is that they don't need the search engine to be their junior DBA, they need it to be their senior DBA. If they lack the skills to be able to judge accurately which information thrown up by a general Internet search is reliable, then this explains why they go the longer route of typing in questions in forums and waiting for answers they are more confident they can trust.

This raises the question: is there something we can and should do about it? How do we make forums such those on SQLServerCentral act as a more coherent and reliable senior DBA? How might they do a better job of helping train people up until they can reliably use the search engine as their junior DBA?

Grant Fritchey (Guest Editor).

Grant Fritchey 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.

Vendors/3rd Party Products

Redgate DLM Demo Webinar - Watch the recording of our latest DLM Demo webinar and learn how to improve your database change management process. See how Redgate DLM plugs into TFS, TFS Build & Octopus Deploy so that you can automate your database changes alongside your application code....(more)

Blogs : Administration

Columnstore Index- Should I partition my columnstore Index? - Table partitioning is a perfect way to manage large tables especially in the context DataWarehouse (DW) which can be very large (think TBs). The table partitioning can help both in managing large amount of dara as well as improving the query performance by eliminating partitions that are not required. ...(more)

TCP Port Is Already In Use - It’s 3 AM on a Saturday morning and you receive that dreaded phone call – “The SQL Server just had maintenance. We rebooted and now SQL won’t start”! ...(more)

The SQL Server Installer Version 2: Just Install It! - Bob Ward on reducing the SQL Server installation experience to three screens....(more)

The Transaction Log, Delayed Durability, and considerations for its use with In-Memory OLTP – Part I - We will look at how Delayed Durability changes the logging landscape and then we will specifically see how In-Memory OLTP logging builds upon the on-disk logging mechanism. Finally, we will pose the question “should we use Delayed Durability with In-Memory or not”....(more)

A Page Split in SQL Server - the Good, the Nasty and the Smart - In a “good” page split, the storage engine adds a new page on the right hand side of the index’s leaf level. In a “nasty” page split, a row expands and the page doesn’t have enough space to hold the changed data, or if a new row needs to go on the page and there isn’t room for it....(more)

Blogs : Analysis Services / BI

Documenting your Tabular or Power BI Model - If you were used to documenting your SSAS model using the MDSchema rowsets, you might have noticed that some of them do not work with the new tabular models. Not to worry, though. With the new compatibility level 1200 SSAS Tabular models came some new DMVs. ...(more)

Navigating Complex Tabular Models Quickly – Developer Edition - As a developer, working with large/complex tabular models – “large” in terms of the number of tables, columns, and/or measures (not data size) – can be frustrating and borderline soul-crushing. ...(more)

Blogs : Azure SQL Database

SQL Server 2016 and Azure SQL Database V12 Breaking Change - Conversion from datetime to a higher precision temporal data type (datetime2, datetimeoffset, or time) may yield a different, but more accurate, time value than in prior versions....(more)

Blogs : Big Data

Azure Data Lake Analytics U-SQL decryption possibilities - One of the things that make U-SQL so powerful is C# integration. It gives you the possibility to create your own C# classes and methods and then use them in your U-SQL scripts....(more)

Big data: Why the boom is already over - Too many big data projects have been poorly built, and lack return on investment - so companies are spending their money on other priorities....(more)

Your big data projects will probably fail, and here's why - Nearly two-thirds of big data projects will fail to get beyond the pilot and experimentation phase in the next two years, and will end up being abandoned....(more)

Blogs : Computing in the Cloud

SQL Server Timeouts During Backups and CHECKDB - The advantage of Ethernet-connected storage is that it’s really, really cheap to build and manage. The drawback of Ethernet-connected storage is that if your network connection isn’t really robust, then it’s really, really easy to saturate....(more)

Blogs : Data Mining/Data Analysis

What you need to know about data augmentation for machine learning - Just because you don’t have as much data as Google or Facebook doesn’t mean you should give up on machine learning. By augmenting your dataset, you can get excellent results with small data....(more)

Do less in SQL, more in R, if you want to understand your data better - If you use a grammar (SQL) that is designed for querying data, but not for analyzing data, to do data analysis, then you would end up spending most of your time constructing complicated and nested queries and debugging them. ...(more)

Removing the Clutter from Graphs - You can quickly get an audience to see patterns and trends in data if you present that data graphically. Data visualization is often the most persuasive of mediums; and yet, it’s so easy to get it very wrong. ...(more)

Blogs : Developer Tools

Cleaning up common T-SQL coding issues with SQL Prompt - Some SQL coding habits are just annoying. Commas in front of column names? No way! Others are actively harmful; they’ll make your code error prone, harder for others to read and understand, and even harder to edit without making mistakes. Grant Fritchey covers five harmful problems in T-SQL code, and shows how you can use SQL Prompt to avoid or remove them....(more)

Which Edition of SQL Server is Best for Development Work? - You might think, as a developer, that nothing but the best is good enough as a development database. You might be mistaken. There is a lot to be said for LocalDB, but Ed Elliott argues that every edition has its pros and cons, and you need to consider Cloud-based resources, VMs and Containerised databases too....(more)

Blogs : DMO/SMO/Powershell

Rename a Server with PowerShell - Installing Server Core is much the same as other versions, though you end up with only a command line. If you’re like me, using VMWare, you also might end up with a server name like “WIN-LKR3R4FfL5T”. I want to change that. It’s a fine name if I’m working locally. It’s not to much fun connecting across a network....(more)

Representing Hierarchical Data for Mere Mortals - Why is it that we use XML, but with so little enthusiasm when it does so much, and is so feature-rich? Phil Factor argues that there are better ways of doing it, more complete than JSON, but easier to read than XML. To try to convince you, he gives a set of flying demos, using PowerShell and his PSYaml module, to illustrate how YAML can let you work faster, and more accurately....(more)

Blogs : Performance and Tuning

Default auto statistics update threshold change for SQL Server 2016 - Starting SQL 2008 R2 SP1, we introduced a trace flag 2371 to control auto update statistics better (new threshold). Under trace flag 2371, percentage of changes requires is dramatically reduced with large tables. ...(more)

Investigating the proportional fill algorithm - Proportional fill is an algorithm that it’s worth knowing about, so you don’t inadvertently cause a performance issue, and so that you can recognize a performance issue caused by a misconfiguration of file sizes in a filegroup. I don’t expect you to be using trace flag 1165, but if you’re interested, it’s a way to dig into the internals of the allocation system....(more)

Update On Connect Item To Replace DBCC SHOW_STATISTICS - What would a new DMV or function to replace DBCC SHOW_STATISTICS look like? Erik Darling feels free to speculate....(more)

Can I Use Statistics to Design Indexes? (Dear SQL DBA Episode 18) - I’ve noticed that many indexes in my data warehouse aren’t used frequently. Is there a way to use the automatically generated statistics to make useful indexes?...(more)

MOSL – Memory Optimised Scripts Library - Niko Neugebauer launches the Memory Optimised Scripts Library, to help you to discover, maintain & troubleshoot the Hekaton in SQL Server 2014, 2016 & Azure SQLDatabase. ...(more)

A spanking new ReaderWriterSpinlock - We have already seen recent improvement in the reader-writer lock algorithm, so clearly people are finding more corners to squeeze. Now what, pray tell, is a reader-writer spinlock?...(more)

Capturing spinlock statistics for a period of time - Paul Randal provides a script that allows spinlock statistics to be captured for a defined period of time....(more)

Blogs : PowerPivot/PowerQuery/PowerBI

Using The Invoke Custom Function Button In Power BI - Chris Webb walks through how to use the new Invoke Custom Function button in the Query Editor in Power BI and explains why it’s such a useful thing to have....(more)

Generating JSON In Power BI And Power Query - Often, when calling web services from Power BI or Power Query, you’ll need to generate some JSON inside your query to send to these web services. Chris Webb offers some worked examples of M data types and how Json.FromValue() turns them into JSON....(more)

Blogs : Professional Development

Where Do We Go To Share? - No one reads blogs any more. Twitter is dying. Facebook is broken. LinkedIn? Please.G+. Is that even on any more? Where do we go to share?...(more)

Blogs : Security and Auditing

Feature Spotlight: Transparent Data Encryption (TDE) - Rebecca Zhang helps to clarify the attack surface area that Transparent Data Encryption (TDE) protects, and answers some frequently asked questions....(more)

What Every Accidental DBA Needs to Know Now: Basics of SQL Security - The basic understanding of SQL Server security involves knowing the differences between logins, users, schemas and roles. It also means you can tell the difference between SQL Server security and Trusted Authentication. Join Tim as he navigates these topics and gives you the foundational knowledge to succeed in your new job....(more)

Blogs : Software Development

Are microservices for you? You might be asking the wrong question - Deciding whether to use microservices starts with understanding what isn’t working for you now....(more)

Blogs : SQL

GroupBy Clause - SQL Server vs MySQL - A GROUP BY Clause is used to group the data based on specific columns along with summary information. However there are some differences in usage of this clause in SQL Server and MySQL...(more)

Blogs : SQL Server 2016

Understanding the new MAXDOP settings in SQL 2016 - SQL Server 2016 introduced a really neat feature which allows you to configure MAXDOP and some other settings at the database. Simply open the database properties dialog in the UI from Object Explorer and navigate to the Options tab....(more)

SQL On The Edge #10 – SQL 2016 Columnstore Improvements - Columnstore indexes are a different way of organizing and processing data for a database. Instead of organizing the records where all the fields are stored together, a Columnstore will store column values together and then reconstruct the record based on the different column values....(more)

Blogs : T-SQL

Temporal Tables: Connect Item Round Up - Adam Machanic describes temporal tables as potentially the most compelling feature in the SQL Server 2016 release, with broad applications across a number of different use cases. However, just like any v.1 feature, it's not without its faults....(more)

Generating a workload for AdventureWorks - Some scripts worth knowing about if you need to generate some AdventureWorks2014 database activity....(more)

UNION removes duplicates - Bill Fellows correct 15 years of slightly misunderstanding what happens when you combine two sets of data together using the UNION operator....(more)

Floor, Round and Ceiling - Kenneth Fisher on Floor, Ceiling and how to round to the nearest 500,000....(more)

SQL 2016 SSMS memory leak? - Has SSMS (SQL Server Management Studio) been crashing on you? Have you been getting Out of Memory messages when attempting to run queries?...(more)

Correlated Datetime Columns - The concept is simple, turning this on for your database means that dates have a relationship, the example from MSDN uses OrderDate and DueDate from the Purchasing.PurchaseOrderHeader and Purchasing.PurchaseOrderDetail tables respectively. ...(more)

T-SQL Tuesday #83: We’re still dealing with the same problems - In the N years I have been a database professional, we’re still dealing with the same problems. Whatever your problem, this month is a chance to give your stump speech, and raise some awareness about it. Perhaps we can even make some progress in fixing some of these problems....(more)

Writing into a file from database - Sometimes it’s necessary to write into a file from an SQL Server database. This can be done in several ways, for example using FileSystemObject via OLE automation. Perhaps an easier way is to use a small CLR procedure or procedures....(more)

Who the Devil Wrote This SQL Code? - The way that you format T-SQL code can affect the productivity of the people who have to subsequently maintain your work. It is never a good experience to see SQL Code, cry out “Who the devil wrote this code?”, and then realise that it was you....(more)

Blogs : Virtualization

Windows Server 2016 Hyper-V large-scale VM performance for in-memory transaction processing - With Windows Server 2016, Microsoft has significantly bumped up the Hyper-V Virtual Machine (VM) scale limit to embrace new scenarios such as running e-commerce large in-memory databases for Online Transaction Processing (OLTP) and Data Warehousing (DW) purposes. In this post, we highlight the performance of in-memory transaction processing at scale using SQL Server 2016 running in a Windows Server 2016 Hyper-V VM....(more)

Articles

Redgate delivers efficient migrations for Azure SQL Data Warehouse - Redgate’s Data Platform Studio (DPS) provides a simple and reliable way to migrate on-premises SQL Server databases to Azure SQL Data Warehouse. DPS automates the data upload and applies the most appropriate compatibility fixes and optimizations. It reduces the timeframe for a first data migration from days to hours, giving companies an easy way to explore the potential of the SQL Data Warehouse...(more)