SQL Server table hints – WITH (NOLOCK) best practices February 14, 2018 by Ahmad Yaseen SQL Server table hints are a special type of explicit command that are used to override the default behavior of the SQL Server query optimizer during the T-SQL query execution This is accomplished by enforcing a specific locking method, a specific index or query processing operation, such index seek or table scan, to be used by the SQL Server query optimizer to build the query execution plan. The table hints can be added to the FROM clause of the T-SQL query, affecting the table or the view that is referenced in the FROM clause only. Read more »
Impact of CLR Strict Security configuration setting in SQL Server 2017 February 13, 2018 by Sifiso W. Ndlovu Every seasoned SQL Server developer will tell you that no matter how hard you try, there are just operations in SQL Server better implemented elsewhere than relying on native Transact-SQL language (T-SQL). Operations such as performing complex calculations, implementing regular expression checks and accessing external web service applications can easily lead to your SQL Server instance incurring significant performance overhead. Thankfully, through its common language runtime (CLR) feature, SQL Server provides developers with a platform to address some of the inconveniences of native T-SQL by supporting an import of assembly files produced from projects written in. Net programming languages (i.e. C#, VB.NET). I have personally found CLR to be very useful when it comes to splitting string characters into multiple delimited lines. Read more »
A walk through the SQL Server 2016 full database backup February 12, 2018 by Ayman Elnory Every DBA, even a beginner, may walk through the SQL Server backup screen multiple times per day. It is mandatory that you know every single detail of every single option you have in the most repeatable task you could do as a DBA. In this article, I will be discussing every option available in full backup screen of SQL Server 2016. Read more »
How to create charts from SQL Server data using PowerShell February 8, 2018 by Jefferson Elias Introduction Intended audience This document is intended for application developers and database administrators who are willing to learn how to generate charts from data stored in Microsoft SQL Server using PowerShell. Context In previous article entitled Overview of PowerShell Charting capabilities we’ve seen some components of an assembly called System.Windows.Forms.DataVisualization assembly that is part of the .NET Framework and how to load it inside PowerShell. Now, it’s time to use what we’ve learned so far. Read more »
Everything you wanted to know about SQL Saturday (but were afraid to ask) February 7, 2018 by Ed Pollack Description Technical training is an important part of our careers. Meeting professionals, learning about new technologies, and making the most of the time and resources that we have can make a profound difference on the work that we do. SQLSaturday provides a quality, inexpensive, and fun way to learn and get involved in the SQL Server community. It is a free, full day of technical presentations from experts that will travel from around the world to help make these events a success! Read more »
Overview of PowerShell charting capabilities February 6, 2018 by Jefferson Elias Introduction Intended audience This document is intended for application developers and database administrators who are willing to learn how to generate charts from data stored in Microsoft SQL Server using PowerShell. Read more »
A high level look at SQL Server disaster recovery planning February 5, 2018 by Kandi Humpf The methodology or paradigm, related to SQL Server disaster recovery has definitely changed in recent years. In the past, database administrators were just hopeful if they had any backup at all. Nowadays software is more complicated, and there are more integrated moving parts involved. Read more »
The history of SQL Server – the evolution of SQL Server features February 2, 2018 by Daniel Calbimonte Introduction This article will explain the main features in SQL Server 2017, 2016, 2015, 2014, 2012, 2008, 2005, 2000, 7, 6.5, 6.0, 4.2, 1.1 and 1.0. In the past, the first SQL Server versions supported OS/2 (an operative system created by Microsoft and IBM) and Windows. Now, the new versions of SQL Server (vNext and SQL Server 2017) can be installed in Linux. 15 years ago, it was impossible to think that. Linux and Microsoft were just like oil in water and now, Microsoft loves Linux. Also, we now enjoy full integration with Azure, Tabular Databases, SSIS, SSAS and more. In this article, we will talk about all these changes and improvements. Read more »
How to install and configure SQL Operations Studio for Windows February 1, 2018 by Marko Zivkovic SQL Operations Studio is free, lightweight database development and operations cross-platform tool for private and commercial usage, that can be installed on Windows, macOS, and Linux for SQL Server, Azure SQL Database and Azure SQL Data Warehouse. SQL Operations Studio is built to simplify work of database developers, database administrators, and system administrators. SQL Operations Studio boosts your productivity with smart code snippets, keyword completion, IntelliSense, source control integration, the ability to view and save results in CSV, Excel, JSON format, and the capability to organize and manage favorite database connections, etc. The first version of SQL Operations Studio was released in November 2017. Read more »
Introduction to mssql-cli, a new interactive command line tool for SQL Server January 31, 2018 by Bojan Petrovic Microsoft recently released a new and interactive command-line query tool for SQL Server with modern features such as auto-completion and syntax highlighting. It’s called mssql-cli and this fully open source tool works on cross-platform under the BSD-3 license. Read more »
How to develop a K-Means model on Azure Machine Learning Studio January 30, 2018 by Esat Erkec In this article, we will discuss the k-means algorithm and how can we develop a k-means model on Azure Machine Learning Studio. Machine learning is an area of artificial intelligence that helps us develop relationships between data and predict the future. There are many secrets that are hidden in the data. To discover these secrets, we need Machine Learning algorithms. Machine learning plays the key role in leveraging existing data to exploit business opportunities. Read more »
Migrating SSRS content with PowerShell January 29, 2018 by Craig Porteous With a distinct lack of up-to-date, fully featured or built-in options to get Reporting Services content cleanly from A to B, it can often be a challenging task maintaining proper Development and QA environments or even moving reports from a SharePoint integrated installation to a native mode one, and vice versa. I want to explore the two most efficient methods of bulk-migrating Reporting Services content & also explore other options I’ve used over the years and those that have come and gone. Read more »
OpenQueryStore for SQL Server – Installation and configuration January 24, 2018 by Marko Zivkovic The OpenQueryStore is an Open source implementation of the popular Query store functionality introduced in SQL Server 2016 CTP2. The OpenQueryStore was first introduced in June 2017. Its main contributors are William Durkin and Enrico van de Laar The OpenQueryStore collects query runtime execution statistics, wait statistics information against a specific database. This information helps identifying performance problems and troubleshooting by quickly finding performance differences. The main difference between the OpenQueryStore and the Query store feature is that the OpenQueryStore supports SQL Server 2005 to SQL Server 2014 while Query Store supports only SQL Server 2016 and higher. Also, the OpenQueryStore is open-source which means that the OpenQueryStore code can be changed whenever you like. Read more »
Ola Hallengren’s SQL Server Maintenance Solution – Index and statistics maintenance January 23, 2018 by Robert Seles This is the third article in Ola Hallengren’s SQL Server Maintenance Solution series. The article will cover the IndexOptimize stored procedure in more detail, along with the index optimization jobs created by Ola Hallengren’s scripts. Read more »
How to move SQL database files (MDF and LDF) to another location January 22, 2018 by Marko Radakovic How often you got to the point that for any reason you don’t have enough space on the specific drive to host a database? In case of database development or other tasks outside the production environment, this should not be a problem as a database can be eventually re-created, restored from a backup and set to be hosted on another location. By default, SQL Server stores database files in its installation folder, specifically in the Data folder: Read more »
How to quickly generate a large number of dimension tables for reporting applications January 19, 2018 by Ed Pollack Description When building reporting structures, we typically have the need to build fact and dimension tables to support the apps that will consume this data. Sometimes we need to generate large numbers of dimension tables to support application needs, such as in Tableau, Entity Framework, or Power BI. Creating this schema by hand is time-consuming and error-prone. Automating it can be a way to improve predictability, maintainability, and save a ton of time in the process! Read more »
Understanding SQL Server query plan cache January 18, 2018 by Ben Richardson Whenever a query is run for the first time in SQL Server, it is compiled and a query plan is generated for the query. Every query requires a query plan before it is actually executed. This query plan is stored in SQL Server query plan cache. This way when that query is run again, SQL Server doesn’t need to create another query plan; rather it uses the cached query plan which improved database performance. The duration that a query plan stays in the plan cache depends upon how often a query is executed. Query plans that are used more often, stay in the query plan cache for longer durations, and vice-versa. Read more »
Top 8 new (or enhanced) SQL Server 2017 DMVs and DMFs for DBAs January 17, 2018 by Prashanth Jayaram Dynamic management views (DMVs) and dynamic management functions (DMFs) are system views and system functions that return metadata of the system state. On querying the related system objects, database administrators can understand the internals of SQL Server. It allows us to monitor the performance of the SQL Server instance, and diagnose issues with it. SQL Server 2017 ships with a number of new and enhanced dynamic management views and dynamic management functions that will help DBAs monitor the health and performance of SQL Server instances. A few existing DMV’s such as sys.dm_os_sys_info and sys.dm_db_file_space_usage have been enhanced. Some have also been newly built and available only for SQL Server 2017. Read more »
How to implement array-like functionality in SQL Server January 16, 2018 by Daniel Calbimonte Introduction I was training some Oracle DBAs in T-SQL and they asked me how to create arrays in SQL Server. I told them that there were no arrays in SQL Server like the ones that we have in Oracle (varray). They were disappointed and asked me how was this problem handled. Some developers asked me the same thing. Where are the arrays in SQL Server? Read more »
Using production data for testing in a post GDPR world January 15, 2018 by Brian Lockwood To SQL Server DBAs who are the shepherds of data in organizations, key GDPR questions, in general, center around whether data will need to be treated differently, safeguarded more etc. and specifically, as it relates to allowing production data to be used in testing. That will be the focus of this article as we’ll work our way through the details of this regulation as well as various authoritative articles on the subject, to address this key question. Then we’ll look to ways and means to potentially ameliorate our findings to provide alternatives and workarounds if possible. Read more »
