The main idea of the SQL Server locking mechanism is that it controls the consistency of transactions. According to this principle, if a process wants to perform insert, delete or update operations, SQL Server engine locks the row or rows and does not allow another process until the transaction is completed. Under some circumstances, this locking mechanism can lead to performance problems such as high concurrent process pressures. So you can experience the deadlock (Deadlock is a concurrency problem in which two transactions want to access the same data simultaneously) problems in your database. In this article, we will focus on how to avoid lock issues with the help of NOLOCK hint. First, let’s learn the main essentials and details of dirty read methodology because the NOLOCK hint can cause dirty reading.
Author: Esat Erkeç
SQL Database Administrator Interview Tips
Interview is the most significant step of SQL database administrator recruitment. During the interview, a technical person or interviewer asks questions about the technical experience of a candidate. The purpose of these questions is to understand candidate’s experience about SQL Server. The answers to the questions will measure your knowledge and experience. If you cannot prove your experience in this interview, most probably the decision on your employment will be negative. First of all, you should be calm during the interview. This advice is the most significant key to success. The first question of an interview is about your past experience and skills. Being relevant to this question, you should describe your old and current job responsibilities and skills. You must be sure that you demonstrate all of your knowledge and experience to the interviewer. Because this introduction makes the first impact on your potential employer. Read More
Basics of sys.dm_exec_requests
Performance monitoring and troubleshooting in SQL Server is a vast topic. In SQL Server 2005, dynamic management views, also known as DMV’s, have been introduced and became an essential helping tool for diagnosing SQL Server performance problems. At the same time, we can use dynamic management views for Azure SQL Database. Some of them can differ from SQL Server on-premise database but the logic of work is still the same. Microsoft has very good documentation about dynamic management views. The only thing, you need to be careful about the version and product validation of dynamic management views. Read More
Main Usage of sys.dm_os_wait_stats
As you know, the main responsibility of the database administrator lies in the monitoring of the SQL Server performance and intervening in determined time. You can find several SQL Server performance monitoring tools in the market but sometimes we need additional information about SQL Server performance to diagnosis and troubleshoot the performance issues. So we must have enough information about SQL Server Dynamic Management Views to handle issues about SQL Server.
Dynamic Management View (DMV) is a concept that helps us to discover SQL Server Engine performance metrics. DMV was first announced in SQL Server 2005 version and it continued in all versions of SQL Server afterward. In this post, we will talk about particular DMV whose database administrator must have enough information. This is sys.dm_os_wait_stats.
Traditional and Natively Compiled Scalar-Valued User-Defined Functions
In the software programming world, there are several approaches used by developers that help them with effortless software development. Design patterns, object-oriented programming, test driven development are some of them. If we particularly mention the code reuse; “Reuse of a typed code. It is written once and can be used in most places. It is a basic concept of software engineering. It is a structure necessary for modular programming. ”
For this reason, user-defined functions in SQL Server help us to avoid rewriting of T-SQL queries. At the same time, they improve code readability. In this post, we will discuss the pros and cons (advantages and disadvantages) of the traditional and natively compiled scalar user-defined functions and make a simple performance test.
Compare Execution Plans in SQL Server
Database Administrator always makes an effort to tune SQL Server query performance. The first step in tuning query performance is to analyze the execution plan of a query. Upon some conditions, SQL Server Query Optimizer can create different execution plans. At this point, I would like to add some notes about SQL Server Query Optimizer. SQL Server Query Optimizer is a cost-based optimizer that analyzes execution plans and decides the optimal execution plan for a query. The significant keyword for the SQL Server Query Optimizer is an optimal execution plan which is not necessarily the best execution plan. That’s why, if SQL Server Query Optimizer tries to find out the best execution plan for every query, it takes extra time and it causes damage to SQL Server Engine performance. Read More
Main concept of SQL Server locking
In this post, we will discuss the SQL Server lock mechanism and how to monitor SQL Server locking with SQL Server standard dynamic management views. Before we start to explain SQL Server lock architecture, let’s take a moment to describe what the ACID (Atomicity, Consistency, Isolation, and Durability) database is. The ACID database can be explained as database theory. If a database is called relational database, it has to meet Atomicity, Consistency, Isolation, and Durability requirements. Now, we will explain these requirements briefly.
Migrating SQL Server Database to Azure SQL (PaaS)
In this article, we will highlight SQL Server Data migration to Azure SQL (PaaS). You can find different approaches for this migration process because SQL Server offers different types of tools and features for this migration. Some of these are:
- Database Migration Assistant
- SQL Server Management Studio Deployment Wizard
- SQL Server to Azure SQL replication
These migration approaches have some pros and cons. But if you ask my opinion, Data Migration Assistant is better than others because, in this migration process, you will face fewer migration problems or issues. Data Migration Assistant will be more helpful to handle these issues.
In this article, we will specifically mention Database Migration Assistant, also known as DMA. At the same time, we will make a small demo of SQL Server Management Studio Deployment Wizard.
Choosing Azure SQL Purchase Model and Service Tier
When we are beginning to think of migrating our on-premises databases to Azure SQL, we have to decide on a proper purchase model, a service tier, and a performance level. Before starting the Azure SQL migration process, we have to find logical and provable answers to the following questions:
- Which purchase model is suitable for my apps and business requirements?
- How much budget do I need?
- Which performance level meets my requirements?
- Can I achieve the acceptable performance of my apps?
Configuring Azure SQL Long-term Backup Retention
Backup is the most important case for database administration because it provides business continuity solutions for high availability and disaster recovery. For this reason, Azure SQL database (PaaS: platform as a service) can make a backup automatically and restore it at a point in time. However, the retention period of backup files changes according to the service tiers.