Introduction Context In previous article entitled “How to report on SQL Server deadlock occurrences“, we’ve seen how to extract deadlock information from either system_health or specialized Extended Event and store this information into a table in a SQL Server database. We also discussed a simple report which leads to the creation of a deadlock occurrences […]
Description Indexing is key to efficient query execution. Determining what indexes are needed to satisfy production needs is often a game of cat and mouse in which we are forced to react to performance problems after they are brought to our attention. Being able to proactively monitor index needs and respond effectively before complaints are […]
Introduction This is the third article of a series dedicated to discovering geographic maps in Power BI. In the ToC below the article you can find out references to the previous articles and the project’s goal.
The SQL Server Database Engine stores data changes in the buffer pool, in memory, before applying it to the database files, for I/O performance reasons. After that, a special kind of background process, called Checkpoint, will write all of these not reflected pages, also known as Dirty Pages, to the database data and log files […]
On the 19th of April 2017, Microsoft held an online conference called Microsoft Data Amp to showcase how Microsoft’s latest innovations put data, analytics and artificial intelligence at the heart of business transformation. Microsoft has, over the last few years, made great strides in accelerating the pace of innovation to enable businesses to meet the […]
Locking is essential to successful SQL Server transactions processing and it is designed to allow SQL Server to work seamlessly in a multi-user environment. Locking is the way that SQL Server manages transaction concurrency. Essentially, locks are in-memory structures which have owners, types, and the hash of the resource that it should protect. A lock as […]
Description Indexing is key to efficient query execution. Knowing what indexes are unneeded, incorrectly used, or unused can allow us to reduce disk usage and improve write performance at little cost to your organization.
Introduction In any good programming reference, you will read that a developer has to document his code, not only for him/herself but also for the person who, ten years later will be asked to maintain it. This would, of course, be made easier thanks to a good documentation of existing code.
What is SQL Server Stress Testing? Implementing a new database consists of various aspects like creating indexes, tuning stored procedures or installing new RAM etc. But among all of these items, performance stress testing plays a prominent role.
Introduction In the previous article in this series on dynamic management views in SQL Server, we used DMV’s to discover some basic information about databases on an instance you are connected to, along with some ideas about getting basic file system and security info. I promised to dive into security next but first I’d like […]
SQL Server indexes overview Just like a book index, SQL Server index has a similar purpose, to provide faster searching, gathering and sorting of information from a database, without need to go through all of the objects over and over. Instead, an index provides the gateway between the table rows and query engine, in order […]
All database users know about regular aggregate functions which operate on an entire table and are used with a GROUP BY clause. But very few people use Window functions in SQL. These operate on a set of rows and return a single aggregated value for each row.
SQL Server 2017 has been in the talk for its many features that simplify a developer’s life. With the previous versions, developers had to write T-SQL, or user-defined functions using temporary tables, and build complex logic, just for string manipulation. In almost every database I work with, I see many user-defined functions for string manipulation […]
Introduction In the previous article entitled “What are SQL Server deadlocks and how to monitor them“, we’ve seen how a deadlock can occur and which tools are available to DBA or developer to get simple or extended information about the conditions that lead a particular deadlock situation to occur.
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 […]
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.
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:
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.
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 […]
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.
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.
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.
Terabytes 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 […]
Introduction This article is the third and last one of a series of articles that aims to introduce set-based programming approach inside SQL Server, so using Transact SQL. In first article “An introduction to set-based vs procedural programming approaches in T-SQL”, we’ve compared procedural and set-based approaches and came to the conclusion that the second […]
Background As a database administrator, sometimes you need to identify details about a problem as quickly as possible and being able to build and analyze data for analysis will help you solve the problem. From getting information about the latest backups, to saving information about waits or indexes and comparing that to other captured metrics, […]
© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy