Linked servers allow SQL Server access to data from a remote data source. A remote data source can be another SQL Server instance or other data sources such MySQL, Access databases, Oracle, Excel workbooks, text files etc.
Read more »
Refreshing SQL Server Containers automatically with Watchtower
July 25, 2017Introduction
I’ve been working with SQL Server in containers for a while now and one of the challenges that I have is ensuring that the databases within them are kept at the correct version. This is important because you don’t want your development teams testing against databases that have an out of date schema (or reference data).
Read more »
How to email SQL query results to your smartphone using the sp_send_dbmail stored procedure
July 24, 2017Introduction
Sometimes there are tasks that require too much time to finish during business hours and we need to leave the office with some still pending like the migration of data, backups or long running queries. In such cases, we would still want to receive an email in our smartphones to make sure that the job was completed successfully.
Read more »
Performance tuning for Azure SQL Databases
July 21, 2017With the latest versions of Azure SQL database, Microsoft has introduced a number of new mechanisms to help users and administrators better optimize their workload.
Automatic index management and Adaptive query processing provide us with the possibility to rely on the built-in intelligence mechanism that can automatically tune and improve the performance of our workload.
Read more »
How to simplify SQL Server Database Object usage with Synonyms
July 20, 2017The concept of SQL Server Synonyms was introduced the first time in SQL Server 2005 as an alias name that references an existing database object, replacing its fully qualified name. In this way, it makes the database object more portable and provides more flexibility for the clients to reach and maintain it. You can imagine Synonyms as a layer of abstraction that provides us with an easy way to connect to and manage the database objects without the need to identify the real name and location for these objects.
Read more »
How to drop a SQL Server Login and all its dependencies
July 19, 2017Introduction
Sometimes, we need to drop a SQL Server Login because we gave an access to a person who left, this login was used by an application that has been decommissioned for example.
Read more »
How to control online Index Rebuild Locking using SQL Server 2014 Managed Lock Priority
July 18, 2017When 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 »