When you perform a SQL Server Online Index Rebuild operation, introduced for the first time in SQL Server 2005, the index will not be taken down. But at a specific point, in which the new index new is built and switched from the old structure of the index, a special kind of lock, Schema Modification (SCH-M), will be granted. This lock may cause blocking if your database server is busy.
Read more »Continuous Integration with SQL Server Data Tools in Visual Studio 2017
July 17, 2017Do you want to make your deployments risk free? Do you want to deliver business values to your customers faster? Do you want to increase the efficiency of your Team? Are your application and database deployment separate processes? Do you encounter issues while deploying your database changes? Is your database slowing you down?
Read more »SQL Server system databases – the msdb database
July 14, 2017Introduction
This article is the third I am writing about Microsoft SQL system databases.
- The first article Configuration, operations and restrictions of the tempdb SQL Server system database was about the tempdb database
- The second article SQL Server system databases – the master database was about the master database.
In this article, I will focus only on the msdb database, one of the four system databases that exist in any MSSQL instance:
Read more »How to automate SQL Server deadlock collection process using Extended Events and SQL Server Agent
July 13, 2017Introduction
This article is the last one of a series in which we discussed how to collect data about deadlocks so that we can not only monitor them but also build reports based on our collection results.
Read more »SQL Server Logins, Users and Security Identifiers (SIDs)
July 12, 2017Abstract
Logins and Users are basic security concepts in SQL Server. They are often, and incorrectly, considered to be pretty much one in the same so it is sometimes confusing to some SQL Server users. Another important security concept tied to a login and user in SQL Server is Security Identifiers (SID). This article will explain these concepts through a step-by-step demonstration. This article will focus on the Windows login and provide some specific usage scenarios.
Read more »How to analyze SQL Server database performance using T-SQL
July 10, 2017The performance of a database is the most challenging and critical part of database optimization. The challenge every DBA faces is to identify the most resource-intensive databases. This article talks about the natively available features that can accomplish the task of getting the required details of the entire database at a granular level.
Read more »How to use SQL Server built-in functions and create user-defined scalar functions
July 7, 2017A function is a set of SQL statements that perform a specific task. Functions foster code reusability. If you have to repeatedly write large SQL scripts to perform the same task, you can create a function that performs that task. Next time instead of rewriting the SQL, you can simply call that function. A function accepts inputs in the form of parameters and returns a value. SQL Server comes with a set of built-in functions that perform a variety of tasks.
Read more »How to monitor the SQL Server tempdb database
July 6, 2017When it comes to the monitoring of SQL Server system databases, the tempdb database is one of the most important for consideration, since it holds most of the internally created objects.
Read more »SQL Server system databases – the master database
July 6, 2017Introduction
There are at least 4 system databases in any SQL Server instance as shown by the following SQL Server Management Studio (SSMS) screen capture:
Read more »Analysis Services (SSAS) Cubes – Dimension Attributes and Hierarchies
July 5, 2017In Data Warehouse language, slicing and dicing is done with Dimension Attributes. Sometime a developer feels the need to provide everything to end users, whereas seasoned Business Intelligence Architects understand to provide only the attributes from the requirements. It is a hard path to follow and the seasoned architect’s experience might be the better option.
Read more »How to create, configure and drop a SQL Server linked server using Transact-SQL
July 5, 2017Linked servers allow getting data from a different SQL Server instance using single T-SQL statement.
This article will explain how to create, configure and drop a SQL Server linked server using system stored procedures.
Read more »SQL Server SET Options that Affect the Query Result – SET CONCAT_NULL_YIELDS_NULL, SET NUMERIC_ROUNDABORT, SET QUOTED_IDENTIFIER, SET NOCOUNT, SET XACT_ABORT
July 4, 2017In the previous article of this two-part series SQL Server SET Options that Affect the Query Result – SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS and SET ARITHABORT, we described the first four SQL Server SET options and showed practically how setting these options ON and OFF affects the SQL Server Database Engine behavior and the query result. To recall, SQL Server SET options are a group of session-level options that control how the SQL Server behaves on the database session level, and the option value can be changed using the SET T-SQL command for the current session that you execute the SET command on.
In this article, we will describe another five SET options and see how turning it ON and OFF will change the SQL Server behavior and the query result.
Read more »How to use SQL Server Data Compression to Save Space
July 3, 2017In the previous article, on tracking database file storage usage How to track SQL Server database space usage with built-in functions and DMVs, I introduced some ideas and calculations for figuring out when your database is going to run out of headroom on the file system – hopefully in time to get an order in the works to increase that space!
Read more »SQL Server SET Options that Affect the Query Result – SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS and SET ARITHABORT
June 30, 2017SQL Server provides us with a number of options to control SQL Server behavior on the connection level. These session-level options are configured using the SET T-SQL command that change the option value for the session on which the SET command is executed. Changing the default value of these session-level configuration affects how the session queries will be executed affecting the query result. The performed change on a session-level option will be applied to the current session until its value is reset or until the current user’s session is terminated.
Read more »How to use SQL Server Extended Events to parse Deadlock XML and generate statistical reports
June 29, 2017Introduction
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 timeline. Read more »Configuration, operations and restrictions of the tempdb SQL Server system database
June 28, 2017Introduction
tempdb is one of the 4 system databases that exists in all SQL Server instances. The other databases are master, model and msdb. In case of using Replication, a fifth system database named distribution will also exist. You can find all existing system databases in SQL Server Management Studio (SSMS) under the Databases / System Databases folder:
Read more »Collecting, aggregating, and analyzing missing SQL Server Index Stats
June 27, 2017Description
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 received can save us immense time while preventing costly performance messes.
Read more »Overview of Resumable Indexes in SQL Server 2017
June 26, 2017Managing indexes is a critical component of database maintenance but we often don’t think about the indicators behind the index maintenance operations. SQL Server 2017 (CTP 2.0) introduces a very useful index feature, to mitigate the administration overhead of index maintenance which we’ll review and discuss in this article.
Read more »How to calculate work days and hours in SQL Server
June 26, 2017Like any other enterprise RDBMS system, SQL Server ships with several built-in functions that make developers’ T-SQL code clean, convenient and reusable. To demonstrate the efficiency of functions, say we needed to retrieve a server name for one of our SQL Server instances. Well, one of doing this would be to write a SELECT statement that would query the system view [sys].[servers] from the master database as shown in Script 1.
Read more »How to copy an Azure SQL database using the Azure Portal, Cloud Shell and T-SQL
June 23, 2017This article will provide an overview covering programmatically moving databases on the Azure Portal while avoiding common problems with users and logins.
Read more »How to create geographic maps in Power BI using custom shape maps
June 22, 2017Introduction
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.
Read more »Analysis Services (SSAS) Multidimensional Design Tips – Relations and Hierarchies
June 22, 2017We have already discussed quite some design tips for building Analysis Services (SSAS) Multidimensional cubes and dimensions:
Read more »How to create geographic maps in Power BI using built-in shape maps
June 21, 2017Introduction
This is the second article of a series dedicated to discovering geographic map tools in Power BI.
Read more »SQL Server 2016 Memory-Optimized Tables – The Checkpoint operation
June 21, 2017The 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 periodically.
Read more »How to use Python in SQL Server 2017 to obtain advanced data analytics
June 20, 2017On 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 demands of a dynamic marketplace and harness the incredible power of data—more securely and faster than ever before.
Read more »