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 InterestFor Microsoft, Aggressive Is The New Normal - In the past week, we have seen Microsoft directly go after Oracle, offer up a tool to make it easier to ditch Evernote and pit employees against admins when it comes to Windows 10. ...(more) Stop Taking Your Database Administrators for Granted - We need alignment with our C-level leaders. We need to collaborate with our lines of business. And we certainly need to get in front of the big trends reshaping the IT landscape. But we never get around to talking about the DBA. And that’s a problem, because none of those other good things will happen until we truly empower our DBAs....(more) Tech News : SecurityHow your data is collected and commoditised via “free” online services - Your data is a commodity and as such, others will attempt to extract it from you and sell it. Remember this the next time a site like the ones described in this article request it from you; how are they justifying their online presence? Are you the product? Probably....(more) SQL Server NewsFeedback on SQL Server Documentation - If you care about documentation please provide feedback. The SQL Server writers are working to improve the documentation on MSDN/BOL, and need your help....(more) Microsoft NewsWhy Microsoft is porting SQL Server to Linux - The real reason for Microsoft's move to Linux has now become apparent. It is trying to target Oracle customers and woo them over....(more) Blogs : AdministrationWhy most of you should leave Auto-Update Statistics on - Erik Darling proves using Extended Events that updating statistics, even on some pretty good sized tables, doesn’t really take that long....(more) Index Cannot Be Reorganized… - For months, maybe even years, things are running smoothly. Never a failure. Never an error. Just routine log review day after day. Then one day it happens – there is an error. The index maintenance script failed one night....(more) Database Scoped Configuration in SQL Server 2016 - SQL Server 2016 provides us with a lot of existing configuration options that we can configure at the database level, rather than just instance-level, without using any specific Trace Flags....(more) Blogs : Analysis Services / BINested Display Folders in SSAS - Recently it was announced that Power BI would now support the SSAS display folders in “exploration mode”. Exploration mode is a fancy term for a live connection to an SSAS cube or model. Anyway, great news, as it drastically improved usability of Power BI Desktop when browsing cubes....(more) Creating a Date Dimension in a Tabular Model - As well as its multidimensional model, SQL Server Analysis Services (SSAS) now has a tabular model of database that either runs in-memory or in DirectQuery mode. The in-memory analytics engine allows the users of Excel or Power View to base reports on tabular model objects. Having shown how to handle date-based information using the Multi-dimensional model, Dennes now turns his attention on the in-memory tabular model....(more) Auto install of PowerBI - Simon Sabin uses a few bits of PowerShell to automate installing PowerBI...(more) Enable business insights for everyone with SQL Server 2016: Part 2 - SQL Server 2016 offers a number of significant enhancements including an improved Tabular model, improvements to the DirectQuery storage mode and more....(more) Filter Arguments in CALCULATE - A filter argument in CALCULATE is always an iterator. Finding the right granularity for it is important to control the result and the performance. This article describes the options available to create complex filters in DAX....(more) Blogs : Backup and RecoveryRestoreability and SSMS - Tibor Karaszi describes a couple of cases where SSMS will generate restore commands that will fail, because the restore sequence is based on backups that are impossible to restore from....(more) Blogs : Big DataThe Future of Data and Databases - According to Tom Smith, it's consolidation around big data with rationalization down to 10 core technologies....(more) Blogs : Computing in the CloudUnable to connect to SQL Server on Azure Virtual Machine–VNET Problem - We have seen repeated mistakes by SQL Server users where they took all the effort creating SQL Server VMs and client VMs (like IIS) but they ended up not able to talk to each other. They created the VMs in different VNETs....(more) Blogs : DMO/SMO/PowershellIntroduction to PowerShell Variables - Jeff Hicks covers the concept of variables in PowerShell from a beginner's perspective....(more) More on SQLPS and SQL Server on Linux - Chrissy LeMaire on the current state of SQL Server on Linux, and SQLPS....(more) Blogs : ExcelGenerating Fixed-Width Text Files In Excel With Power Query - While it’s fairly common to need to load fixed-width files using Power Query or Power Query, occasionally you might want to use Power Query and Excel to create a fixed-width output for another system, or maybe to create some test data. ...(more) Blogs : Integration Services/ETLWorking with ragged right formatted files in SSIS - Unfortunately, as ETL developers, we often have to extract data from an imperfect world which has flat files formatted in all sorts of ways....(more) Blogs : NOSQLGoing Social with DocumentDB - Windows Development MVP Matias Quarantaas describes a perfectly normalized and pretty data structure… that fails for the requirements of data storage for social networks. Enter DocumentDB....(more) Blogs : Performance and TuningThe SQL Server 2016 Query Store: Forcing Execution Plans using the Query Store - The SQL Server 2016 Query Store can give you valuable performance insights by providing several new ways of troubleshooting queries, studying their plans, exploring their context settings, and checking their performance metrics. However, it can also directly affect the performance of queries by forcing Execution Plans for specific queries....(more) Live Query Statistics Don’t Replace Actual Execution Plans - I like SQL Server’s new Live Query Statistics feature a lot for testing and tuning large queries. One of my first questions was whether this could replace using actual execution plans, or if it’s useful to use both during testing. Spoiler: Both are useful. And both can impact query performance....(more) How to identify the source of page splits in a database - Page splits have the ability to slow down our queries and increase the amount of IOs needed to retrieve our data. We need to monitor and identify the source of page splits to avoid them in our databases....(more) Blocking operators and actual row counts - Query plans can sometimes be hard to read, and other times can be downright mystifying. Gail Shaw investigates a sort operator, a normal sort, that is reducing the row count....(more) Added per-operator level performance stats for Query Processing - An upcoming SQL Server 2014 Service Pack will add runtime information to Showplan, and expose a new Extended Event in SQL Server 2016 RC0....(more) Blogs : R LanguageScoring R Models with Excel - Enhancements to the AzureML R package make it easy to publish R functions that consume data frames as Azure Web Services. A very nice consequence is that it is now feasible to develop predictive models in R and enable the Excel powered business analysts in your organization to use your model to generate predictions with new data. ...(more) Blogs : Reporting ServicesSQL Server 2016 RC1: What’s new in Reporting Services - SQL Server 2016 RC1 contains a bunch of enhancements to Reporting Services, culminating in the first relatively “feature-complete” preview release of SQL Server 2016 Reporting Services....(more) Blogs : Software DevelopmentReference data as code - Vladimir Khorikov describes a powerful technique that can potentially save you a lot of work and make your code much more concise: representing reference data as code....(more) Blogs : SQLAnalyzing Your Email with SQL - how much time do you spend managing your email inboxes? Find out with SQL....(more) Blogs : SQL Server 2014Getting a functional Books Online - The local BOL for SQL Server 2014 has been broken for a long time. Tibor Karaszi shows how to fix it....(more) Blogs : SQL Server 2016SQL 2016 Technical Deep Dive Video – Summary of all Videos - Simon Sabin provides a handy categorization of the new SQL Server 2016 Technical Deep Dive videos....(more) SQL Server 2016: Changes in default behavior for autogrow and allocations for tempdb and user databases - In SQL Server 2016, the functionality provided by TF 1117 or 1118 will be automatically enabled for tempdb, meaning better out-of-the-box scalability and throughput for SQL Server applications that use tempdb heavily....(more) New DMF for retrieving input buffer in SQL Server - A new DMF for retrieving the input buffer for a session/request (sys.dm_exec_input_buffer) is now available in SQL Server 2016 RC0. This is functionally equivalent to DBCC INPUTBUFFER but has a few advantages....(more) Blogs : T-SQLPerformance Surprises and Assumptions : STRING_SPLIT - For many years, Aaron Bertrand have advised to STOP DOING THIS IN T-SQL. And then SQL Server 2016 came along…...(more) Query Active Directory directly from SQL Server - Have you ever had the need to query Active Directory right out of SQL Server using native TSQL? There are 3 ways to accomplish this....(more) Find the Partitioning Key on an Existing Table with Partition_Ordinal - This seems easy, right? Nope, it’s not. If you just right click the table and say “Script to new window”, it does NOT script the partitioning scheme and column you chose as the partitioning key. ...(more) The Joy of Joining on NULLs - Some of the worst performing queries use ISNULL either in the JOIN or WHERE clause. Still. To this day. In the age of the hoverboard, people are still putting functions where they don’t belong. ...(more) Splitting Strings: SQL Server 2016 To The Rescue! - Erik Darling swoons over the new Built-in Table-Valued Function STRING_SPLIT, in SQL Server 2016....(more) SQL Server 2016: What time is it? - SQL 2016 introduces a function to help alleviate the issues we have when it comes to finding the current time. No, it’s not spacetime (I wish), nor is it a stardate (how cool would that be?), it is the ability to transform a given time into your local time by using the AT TIME ZONE function....(more) Blogs : VirtualizationThe Experience of Switching from VMware to Hyper-V - Brent Ozar is tempted by the Vaio Canvas Z, a powerful Windows 10 tablet, but is underwhelmed with the Hyper-V virtualization capabilities....(more) |