Performance tuning for Azure SQL Databases July 21, 2017 by Kaloyan Kosev With 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, 2017 by Ahmad Yaseen The 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, 2017 by Jefferson Elias Introduction 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, 2017 by Ahmad Yaseen 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, 2017 by Samir Behara Do 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, 2017 by Vitor Montalvão Introduction 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, 2017 by Jefferson Elias Introduction 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, 2017 by Simon Liew Abstract 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, 2017 by Prashanth Jayaram The 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, 2017 by Ben Richardson A 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, 2017 by Daniel Tikvicki When 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, 2017 by Vitor Montalvão Introduction 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, 2017 by Thomas LeBlanc In 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, 2017 by Marko Zivkovic Linked 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, 2017 by Ahmad Yaseen In 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, 2017 by Gerald Britton In 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, 2017 by Ahmad Yaseen SQL 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, 2017 by Jefferson Elias 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 timeline. Read more »
Configuration, operations and restrictions of the tempdb SQL Server system database June 28, 2017 by Vitor Montalvão Introduction 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, 2017 by Ed Pollack 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 received can save us immense time while preventing costly performance messes. Read more »
