Understanding the Impact of NOLOCK and WITH NOLOCK Table Hints in SQL Server

June 6, 2017 by Sifiso W. Ndlovu

Every once in a while, SQL Server database administrators find themselves in disagreements with their application developer counterparts – particularly when it comes to some of the latter’s Transact SQL (T-SQL) developmental practices. One of my first observations when I joined my current employer is that almost all T-SQL scripts written by application developers uses the NOLOCK table hint. However, from the interactions that I have had with these esteemed developers it doesn’t seem like they understand how the NOLOCK table hint works. Furthermore, although they seem to be aware of a distinction between NOLOCK and the WITH NOLOCK table hint, they again do not seem to comprehend how the two differ from one another. In this article, I explore the internal workings of the NOLOCK table hint and examine the implications of omitting the WITH keyword.

SQL Server Database Architecture and Audits for Enhanced Security

June 5, 2017 by Timothy Smith

This year, digital security has risen as a top concern for many people, especially after the recent security compromises with Cloudflare in February and ransomware in May. Throughout the last two years, we’ve also seen the rise of sim-swapping where a hacker is able to extract a user’s cell phone data and compromise all emails and two-factor authentication. Unfortunately, I expect these security issues to continue and cause compromises for user’s data while negatively impacting companies involved.

Initial Attributes and Measures in SSAS Multidimensional Cubes

June 2, 2017 by Thomas LeBlanc

Connecting to tables in SQL Server Analysis Service (SSAS) databases is easy. Maybe too easy. The learning curve for developing in a Multidimensional Cube is steep, although a Tabular Model has lessened this curve. The power of encapsulating the business logic of aggregations and analytics into an Analysis Service database is too great not to embrace Analysis Services in an enterprise environment.

Compression and decompression functions in SQL Server 2016

June 2, 2017 by Ahmad Yaseen

The concept of data compression is not a new on for SQL Server Database Administrators , as it is was introduced the first time in SQL Server 2008. In that SQL Server version, you were able to configure the compression at the row and page levels on the table, index, indexed view or the partition. The row and page level compression is not the best choice in all cases, as it does not work well on the Binary Large Objects (BLOB) datatypes, such as the videos, images and text documents.

Analysis Services (SSAS) Multidimensional Design Tips – Creating Dimensions

June 1, 2017 by Koen Verbeeck

In the previous article, Analysis Services (SSAS) Multidimensional Design Tips – Data Source View and Cubes, we discussed best practices for SSAS Multidimensional cubes and data source views. In this article, we continue the series with design tips for creating dimensions as the subject. As with the previous article, most tips are suited for SSAS 2008 (most likely 2005 as well) to 2016 and later versions. Analysis Services Tabular is not covered in this article.

How to identify slow running queries in SQL Server

May 31, 2017 by Musab Umair

Overview

Slow running queries are one of the most common problems in every organization dealing with huge amounts of data. And the most challenging problem, in almost all the clients, I work with, is how to find the queries running slow and figuring out what is the actual cause behind the performance problem. Thankfully, the solution, in most cases, is simple.

Deep dive into SQL Server Extended events – The Event Pairing target

May 30, 2017 by Kaloyan Kosev

Extended event sessions can use multiple targets to store captured information. We should use different targets depending on the type of data we are capturing and the structure of the data outcome we desire. The available targets include the following: event counter, ring buffer, event file, histogram, event tracing for Windows (ETW), and Event Pairing.

Microsoft Build Conference 2017 – Recap for SQL Developers

May 29, 2017 by Samir Behara

The annual developer Microsoft Build Conference was held at the Washington State Conference Center in Seattle during May 10-12, 2017. The event had over 5,000 attendees and received a lot of love from the technical community. There were a number of announcements made during this 3-day conference, and in this article I will recap my learnings from a SQL Developer perspective. In the Data Platform, there were a number of new innovations, products and capabilities announced and a lot of emphasis was on using data to fundamentally transform your business. There was focus around serving Artificial Intelligence with Data, which seemed to me to be the future of Data Platform.

Digital certificates: when and where to use them

May 26, 2017 by Nikola Dimitrijevic

Digital certificates are form of electronic authorizations used to verify the identities of persons, companies, computers, and other network entities.

A T-SQL design pattern for logging process execution

May 25, 2017 by Jefferson Elias

Introduction

Context

Back in my days at school, I followed a course entitled “Object-Oriented Software Engineering” where I learned some “design patterns” like Singleton and Factory. If you are not familiar with this expression, here is a definition of a design pattern from Wikipedia:

Deep dive into the Extended Events – Histogram target

May 24, 2017 by Kaloyan Kosev

An Extended events target is the destination for all of the information that is captured by Extended Events sessions. You can rely on couple of different targets such as event counter, event file, event tracing for Windows (ETW), ring buffer, event pairing, and histogram.

How to Create a Clustered Columnstore Index on a Memory-Optimized Table

May 24, 2017 by Ahmad Yaseen

With Microsoft SQL Server, like all Microsoft products, you will enjoy experiencing new features and enhancements to the existing ones, when a new SQL Server version is launched. These SQL Server enhancements concentrate heavily in simplifying and speeding up the data retrieval without consuming excessive resources.

How to create an Azure SQL Database using the Cloud Shell

May 23, 2017 by Daniel Calbimonte

Introduction

Cloud Shell is a new feature just released on May 2017. It is currently in beta.

The idea behind the Cloud Shell is to run Azure commands using the Azure Portal directly. In the past, it was necessary to install the Azure CLI on a local machine. You could install on a Windows, Linux or a Mac machine or you could use Docker. Now, you can use the Azure Portal.

Understanding Skewed Data in SQL Server

May 22, 2017 by Susantha Bathige

Introduction

I recently did some research to analyze skewed data distribution in SQL Server. This article is the outcome of my analysis to share with SQL Server community.

SQL Server understands its data distribution using statistics. The SQL Server query optimizer then uses the statistics to calculate estimated row counts when executing the queries submitted by users. The accuracy of estimated row count is crucial to get optimal execution plans. The SQL Server query optimizer is a complex mathematical component and it does a decent job to create better execution plans during a very short period of time.

$C:\Users\Neeraj\Desktop\NL 2\2ndnewinner.JPG$

Parallel Nested Loop Joins – the inner side of Nested Loop Joins and Residual Predicates

May 22, 2017 by Neeraj Prasad Sharma

This article is the second part of the Nested Loop Join Series. In the first part, Introduction of Nested Loop Join, we gave a brief introduction to Native, Indexed and Temporary Index Nested Loop Joins along with CPU cost details.

What are SQL Server deadlocks and how to monitor them

May 19, 2017 by Jefferson Elias

Introduction

As a DBA, I’ve been, more than , confronted with performance problems. Sometimes, poorly written code or lack of indexes will generate blocking conditions that we refer to as a“Deadlock”. Due to their nature and like any similar blocking situation, deadlocks can have a direct impact on user experience and the more they occur, the bigger the impact.

How to migrate MySQL tables to Microsoft Azure SQL database

May 18, 2017 by Daniel Calbimonte

Introduction

Today, it is a common practice to migrate our local data to Azure. Sometimes, we migrate files, sometimes databases and sometimes virtual machines. All the service in the cloud including Azure help a lot to simplify the administrative effort and reduce costs.

How to install SQL Server on Ubuntu

May 18, 2017 by Stefan Veselinovic

Introduction

With an increasing number of new servers running on Linux, and even a third of Microsoft’s Azure virtual machines running Linux, they have made a choice to bring SQL Server to Linux in order to increase their market share and attract new customers.

Monitoring SQL Server with Dynamic Management Objects – Requests

May 17, 2017 by David Alcock

In my last post, Monitoring SQL Server with dynamic management objects – Sessions and connections, I introduced the concept of using T-SQL queries to monitor SQL Server using Dynamic Management Views. The article demonstrated how we can use two views, namely sys.dm_exec_sessions and sys.dm_exec_connections to view activity on our instance of SQL Server and we achieved this by utilising some of the functionality available to us within T-SQL like joins, aliases and functions, all of which helped us develop a query to return monitoring information to us.

Migrate an SQL database from Windows to Linux using backup-and-restore and SMB Protocol

May 16, 2017 by Prashanth Jayaram

In an attempt decouple systems, we may modularize them. In most cases, we would need to move databases from one server to another—sometimes, even move them from Windows to Linux.

Page 2 of 31«123 4 5...10 20 30...»Last »