Summary: Let’s learn how to restore a SQL Server database backup for SQL Server 2012. There are various methods of taking database backups, which include using T-SQL code, SQL Server Management Studio, or third-party applications. These backups taken are purposely saved to be restored when the database becomes corrupt or crashes, migrating the database, making a copy of the database, and other business requirements. In this crash course, we will be focusing on how to restore SQL Server database backup by using the SQL Server Management Studio (SSMS) GUI, T-SQL code and SQL recovery softw...

In production environment databases, it is often seen that some SQL where conditions include:equal condition on a normal index + primary key range query + order by limitAlthough using a normal index would be more efficient, the system chooses to use index merge instead in some cases. This article explores such index merge situations.Index Merge Official Introduction  The Index Merge access method retrieves rows with multiple range scans and merges their results into one.Generally, for a single table, the optimizer chooses one index. However, in the case of index merge, the optimizer ...

When the CPU usage of a MySQL database rises abnormally, it's necessary to quickly identify the problematic SQL statements. In this post, we try to provide one way to achieve this. Below are the actual steps.1. Use the top command to find the threads with the highest CPU usage in the MySQL process.# Find the MySQL process IDps -ef | grep mysql# Use the process ID to find the thread IDs with the highest CPU usage:top -H -p In top, press P to sort by CPU usage.Note the thread ID, for example, 39449.2. Log in to the database to query performance_schema and information_schema.-- Query the thr...

When working with Chinese data in Oracle databases, character encoding issues often lead to garbled text, preventing proper display and processing. This is typically because the default character set in Oracle is US7ASCII, which cannot recognize Chinese characters. By modifying the character sets of the database, client, and application, data can be converted to the correct character set, thus avoiding garbled text issues. This article introduces several methods to resolve Chinese character encoding issues in Oracle databases.BackgroundOracle is recognized as a leading DBMS in the industry, ca...

Starting from a problemFive years ago when I was working at Tencent, I found that MySQL request speed was very slow in the pagination scenario. With only 100,000 data, a select query on a single machine took about 2-3 seconds. I asked my mentor why, and he asked in return, "In an indexing scenario, what is the time complexity to get the nth largest number in MySQL?"The pursuit of the answerConfirming the scenarioAssuming there is an index on the "status" column, a query like "select * from table where status = xx limit 10 offset 10000" will be very slow. Even with a small amount of data, there...

Many of you may have heard or used MySQL's query cache, because it used to be a popular way to improve MySQL's performance. As an important feature for improving MySQL's performance, the query cache was often recommended as a solution for slow queries. However, why has MySQL 8 abandoned the query cache? Today, we will analyze and explore this decision.What is query cache?According to official document:The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retriev...

ClickHouse is a high-performance open-source columnar database management system developed by Yandex. Here are some of the key features of ClickHouse:Columnar storage: ClickHouse uses a columnar storage format, which allows it to efficiently store and retrieve data by column, rather than by row. This results in much faster query performance, especially for analytical and aggregate queries.Real-time data processing: ClickHouse is designed to handle real-time data processing and can handle billions of rows of data with sub-second query times.Massively scalable: ClickHouse can scale to handle mas...

Blocking is a common occurrence in an SQL server context, but if you are new to the world of database management you might not know what this issue entails and perhaps even fear that it is a sign of serious underlying problems.To allay your fears and clear up the mystery, here is a brief overview of blocking and the steps you can take to tackle it.Image Source: PixabaySQL blocking explainedSQL blocking according to SentryOne is an offshoot of the way that concurrent databases operate. Because processes can be executed simultaneously, it is likely that at some point more than one process will n...