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.
Read more »How to Create a Clustered Columnstore Index on a Memory-Optimized Table
May 24, 2017With 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.
Read more »How to create an Azure SQL Database using the Cloud Shell
May 23, 2017Introduction
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.
Read more »Understanding Skewed Data in SQL Server
May 22, 2017Introduction
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.
Read more »Parallel Nested Loop Joins – the inner side of Nested Loop Joins and Residual Predicates
May 22, 2017This 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.
Read more »What are SQL Server deadlocks and how to monitor them
May 19, 2017Introduction
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.
Read more »How to migrate MySQL tables to Microsoft Azure SQL database
May 18, 2017Introduction
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.
Read more »How to install SQL Server on Ubuntu
May 18, 2017Introduction
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.
Read more »Monitoring SQL Server with Dynamic Management Objects – Requests
May 17, 2017In 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.
Read more »Migrate an SQL database from Windows to Linux using backup-and-restore and SMB Protocol
May 16, 2017In 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.
Read more »How to Spoof Production Environment Resources for Performance Tuning
May 16, 2017Performance tuning is one of the most important and critical tasks that the SQL Server Database Administrator performs on a daily basis to keep a SQL Server running in a healthy state, by identifying the performance bottlenecks and fixing the main cause of these problems.
Read more »Discovering database specific information using built-in functions and dynamic management views (DMVs)
May 15, 2017Introduction
In the last two articles on dynamic management views in SQL Server, Discovering SQL server instance information using system views and Discovering more SQL Server information using the built-in dynamic management views (DMVs), we used DMVs to discover a fair bit of information about the SQL Server instance we’re connected to. In this article, we’ll begin diving in to database specifics. There is a lot of territory to cover! We’ll also use several of the built-in functions that come with SQL Server.
Read more »Monitoring SQL Server with Dynamic Management Objects – Sessions and connections
May 12, 2017A fundamental task of Database Administrators is monitoring SQL Server performance. Whilst SQL Server does give us a user interface inside management studio that enables us to view current activity (in the rather aptly named Activity Monitor) this article is going to focus on querying Dynamic Management Objects with T-SQL to return various pieces of useful process information that we can use to monitor server usage.
Read more »Implementing Different Calendars in Reporting
May 11, 2017Introduction
There is a common need in reporting to aggregate or return data that is crunched based on date attributes. Calendars themselves can vary and provide differing insight into a business, data, and finance. They also assist in taking the intrinsically messy Gregorian calendar and making it easier to work with.
Read more »SQL DateTime data type considerations and limitations
May 11, 2017Introduction
In this article, we will explorethe SQL Date Time data type and its limitations.
Read more »Installation of SQL Server vNext CTP on Linux Distribution CentOS 7
May 10, 2017It was a paradigm shift in December 2016, when Microsoft made their SQL Server database available for Linux; it was the first time in history that Microsoft ever designed SQL Server to run on a non-Windows operating system. SQL Server vNext was released for public preview so the user community could test and deploy SQL Server on a Linux operating system. Microsoft took a major step in diversifying the database technology into the non-windows platform for the first time.
Read more »How to Avoid Package Design Flaws When Sourcing Data From Flat Files
May 10, 2017As developers of SQL Server Integrations Services (SSIS) solutions, we have more than likely configured Flat File Connections as sources in our Data Flow Tasks. Whilst any unforeseen changes to the structure and formatting of flat files will invariably cause SSIS packages to fail, as developers we can still do a lot in reducing unnecessary SSIS package failures relating to data coming out of flat files. In this article, we offer recommended development practices to some flat file source errors that occur as a result of poor SSIS development practices.
Read more »How to connect and use Microsoft SQL Server Express LocalDB
May 9, 2017Microsoft SQL Server Express LocalDB is intended for developers, it is very easy to install and doesn’t require any complex configuration task to create an instance or to use the database. The Introduction on the installation of Local DB be found on “How to install Microsoft SQL Server Express LocalDB” article.
This article will show different ways of connecting to LocalDB and how to create and use an instance of LocalDB.
Read more »How to install Microsoft SQL Server Express LocalDB
May 9, 2017Microsoft SQL Server Express LocalDB, a solution primarily intended for developers, is a lightweight version of SQL Server Express. It is very easy to install and set up. The installation copies a minimum set of files which are necessary to start SQL Server Database Engine. LocalDB supports the same T-SQL language and has the same limitations as SQL Server Express.
Read more »How to set and use encrypted SQL Server connections
May 8, 2017As the standard for securing the host-server interaction, Secure Sockets Layer or SSL is implemented in a Web environment. However, the SSL can provide the encrypted connection and data transfer between a particular SQL Server instance and a client application. A trusted SSL certificate validates the SQL Server instance when the client application requests encrypted connection (or vice versa), while the SQL Server must be configured to follow the certificate authority (CA). This means that a certificate must be “signed” by a trusted source.
Read more »Introduction to Nested Loop Joins in SQL Server
May 8, 2017A relational database system uses SQL as the language for querying and maintaining databases. To see the data of two or more tables together, we need to join the tables; the joining can be further categorized into INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, and CROSS JOIN. All these types of joins that we use are actually based on the requirement of the users.
Read more »How to use Columnstore Indexes to improve your Data Warehouse Staging Environment
May 4, 2017My team and I were recently tasked with refactoring older data marts, particularly those that were created with SQL Server 2008 in mind. As we all know, SQL Server has undergone significant changes since the release of SQL Server 2008. One of those changes relates to the introduction of columnstore as an alternative to the traditional B-tree index (rowstore). Whilst most of the existing documentation relating to columnstore seem to focus on the benefit of columnstore against data warehouse workloads, in this article I argue that the usage of columnstore index should not be limited to facts and dimensions instead let’s introduce it in our data warehouse staging environments too.
Read more »Different Azure Storage types (File, Blob, Queue and Table)
May 3, 2017There are several advantages to using Azure storage irrespective of type. Azure storage is easily scalable, extremely flexible and relatively low in cost depending on the options you choose.
Read more »Understanding Database Backup Encryption in SQL Server
May 3, 2017Terabytes of data, millions of rows; the entire business depends on this — a database administrator’s responsibility is as massive as it sounds. Apart from keeping the data secure, a database administrator also has to keep the system up and running, and restore the data as needed, in case of a failure, with minimal impact to the business.
Read more »Adaptive Query Processing in SQL Server 2017
April 28, 2017The SQL Server Query Processing Engine is the most complex and sophisticated part of SQL Server. This engine has many responsibilities, but one of the most important parts is to design the query execution plan and execute it in a best and most efficient possible manner.
Read more »