Recently, I came across an application that generated DB queries. I understand that there is nothing new about that, but when application began running slow and I had to find out the reason of the slowdown, I was amazed to find these queries. Here is what SQL Server sometimes has to deal with: (more…)
Usage of UUID as a primary key for tables has a bunch of pros, including the option to retrieve IDs for objects created in a client application on its own without calls to the database server. However, usage of UUID as a primary key has a con: GUIDs generated by the client application may be not quite SQL Server-friendly that can lead to the overhead during the addition of a new record. (more…)
An interesting project related to the task queue processing come to the company I work for. It was previously developed by another team. We needed to detect and resolve issues that occurred at high load on the queue.
In short, the project consisted of several databases and applications located on different servers. A ‘Task’ in the given project is a stored procedure or a .NET application. Correspondingly, the ‘task’ must be performed on a certain database and on a certain server.
All queue-related data is stored on the dedicated server. As for the servers at which tasks must be performed, they store only metadata. That is, procedures, functions, and service data related to this server. All task-related data comes from a Linked Server. (more…)
In our projects, we have to cope with different tasks. To solve some of them, we use dynamic T-SQL.
Why do we need dynamic T-SQL? Well, it is up to you.
In one of the projects, we have solved the task of building dynamic reports, and in others — data migration. Dynamic T-SQL is essential when you need to create, modify, get data or objects, but values or names come as parameters. For sure, it may seem unreasonable. Still, such tasks are possible. Later in the article, we will see several examples.
This article is the second one of the three articles devoted to a particular security configuration combination of database security.
In my previous article, I presented a scenario in which we were able to compromise data in a SQL Server database.
I would like to note that the knowledge of this configuration combination is critical. In this article, I am going to provide further information and reasons for the importance of this issue. (more…)
In this article, we are going to talk about using the nvarchar data type. We will explore how SQL Server stores this data type on the disk and how it is processed in the RAM. We will also examine how the size of nvarchar may affect performance.
Actual data size: nchar vs nvarchar
We use nvarchar when the size of column data entries are probably going to vary considerably. The storage size (in bytes) is twice as much the actual length of data entered + 2 bytes. This allows us to save disk storage in comparison of using nchar data type. Let us consider following example. We are creating two tables. One table contains nvarchar column, another table contains nchar columns. The size of the column is 2000 characters (4000 bytes).
SQL query describes the expected result, not the way to get the result. The set of specific steps the server must take to return the result is called the query execution plan. The plan is built by the optimizer. Selection of a plan affects execution speed, what makes it one of the most important elements of the query performance problem analysis.
Execution plan comprises operators and their properties that are interrelated with each other in the form of the tree structure. Each operator is responsible for a separate logical or physical operation. All together, they ensure the result described in the query text. Inside the tree, operators are represented by the class objects in the memory of SQL Server. Server users (that is, you and me) see the description generated in XML format with a specific schema, that is displayed graphically by the SQL Server Management Studio (SSMS) environment.
There are many various plan operators and even more properties. Besides, new ones emerge from time to time. This article does not dare to describe all possible variety of operators. Instead, I would like to share the most interesting additions in this subject and to remind some old but useful elements. (more…)
This article is a short review of the main scheduled maintenance with a database of the 24/7 information system that does not have downtime, as well as approaches to their execution in MS SQL Server.
Any comments and updates to the article are much appreciated.
- If data is being changed in one transaction, selection of this data (in other transaction or without a transaction) will not wait till the first transaction is finished and will return data entries of uncommitted transactions.
- If data is being read in one transaction, updates of this data in other transaction will not wait till the first transaction is finished.
- Shared locks are not used. Identical to setting the NOLOCK hint for all selections in Read Committed.
- Exclusive locks are enabled during statement execution and disabled at the end of the transaction.
It is important for a database administrator to know when there is no space on a disk. Thus, it is better to automate the process in order for them not to do it manually on each server.
In this article, I am going to describe how to implement automatic daily data collection about logical drives and database files.