How long does it take to create a TencentDB for SQL Server instance?
Normally, the creation time for a single node (previously Basic Edition) instance is about 20 minutes. For a double node (previously High-Availability/Cluster Edition) instance, the creation time is around 3 minutes. The creation time for a read-only instance depends on the data volume of the master instance; the larger the data volume, the longer the creation time. If the master instance is empty, the creation time for an instance is about 3 minutes. If it exceeds this time, there might be an issue with the creation process, please submit a ticket to contact us for assistance.
How many databases can I create at most in a TencentDB for SQL Server instance?
For performance considerations, we recommend you create databases in a TencentDB for SQL Server instance within the following limits:
Single Node (formerly Basic Edition): Theoretically, the number of databases that can be created is not limited. However, we recommend keeping it below 100.
Dual Node (formerly High Availability/Cluster Edition): In the console, the number of databases that can be created is limited. We recommend keeping it below 70.
Although users can also use the SSMS tool to connect to the instance and create databases, and the databases created via SSMS will automatically sync to the standby machine, it is advised not to exceed the database quantity creation limit, as exceeding the limit may lead to primary-standby sync anomalies. If consultation is needed under special circumstances, please provide feedback through online support.
Does TencentDB for SQL Server limit the IOPS?
TencentDB for SQL Server has no strict limits on the IOPS metric. Theoretically, as long as the CPU and memory are not restricted and the instance is not locked or blocked, the IOPS can be unlimited.
Why does TencentDB for SQL Server have a built-in monitor library?
The monitor library is a system database used to collect monitoring information. It does not affect database performance nor occupy your space.
How does TencentDB for SQL Server track deadlocks?
Deadlocks in TencentDB for SQL Server can be tracked using SQL Profiler. To enable Profiler, open SSMS, select Tools > SQL Server Profiler, and connect to the database. Note that enabling Profiler tracking may slightly affect performance and occupy space, so it is recommended to disable it promptly after use.
How do I view the memory usage of a TencentDB for SQL Server instance?
You can find the memory category on the system monitoring page of the SQL Server console instance, and check the storage space usage of TencentDB for SQL Server through the monitoring indicators of maximum memory, memory usage, and memory usage rate.
How do I view the memory usage of each database in a TencentDB for SQL Server instance?
What should I do if the memory usage metric value stays high in TencentDB for SQL Server?
The memory mechanisms of TencentDB for SQL Server is the same as that of Microsoft SQL Server. The displayed memory usage of the SQL Server process is the highest memory usage of the instance, which will not be automatically released. SQL Server will perform internal interactions automatically. To release the memory, you need to restart the instance.
For example, 16 GB memory is allocated to an instance. When the instance just starts to be used, it may use only 8 GB memory, and the SQL Server process will occupy 8 GB memory. When the instance uses 16 GB memory, the SQL Server process will occupy all of the allocated 16 GB memory and perform internal interactions to replace the old cached data with new cached data. However, that the process occupies 16 GB memory doesn't mean that the instance actually uses 16 GB memory. In fact, it is possible that the instance only uses 1 GB memory.
How do I view the storage space usage of a TencentDB for SQL Server instance?
You can find the storage category on the system monitoring page of the SQL Server console instance, and check the storage space usage of the cloud database SQLServer through the monitoring indicators of used storage space and the percentage of remaining disk capacity.
After a SQL Server database is created, no or only a small amount of data is written, but why does the storage space monitor show that 500 MB of space has been used?
A TencentDB for SQL Server instance automatically allocates a 500 MB initial space to each database. When data is written, it will be written to the initial space first. Therefore, even if you write no or only a very small amount of data, the storage metric will still be displayed as 500 MB.
Why doesn't the storage space usage decrease after data is deleted from a TencentDB for SQL Server instance?
After data is deleted from a TencentDB for SQL Server instance, the extended data files won't be shrunk, and the free space inside the files can support subsequent operations such as insertion and update.
For example, in a 50 GB instance, if 50 GB data is written to a database and then deleted, the value of the storage space usage metric will be 50 GB, but you can still write a large number of files.
What will happen after the data volume exceeds the maximum storage space of a TencentDB for SQL Server instance?
Dual Node (formerly High Availability/Cluster Edition) instances: If the data volume exceeds the purchased disk space, features like database import and rollback will be unavailable. Expansion or deleting some database tables via the console is needed to free up space.
Single Node (formerly Basic Edition) instances: If the data volume exceeds the purchased disk space, the database will switch to a read-only mode. You can expand the storage or delete some database tables via the console to immediately restore read-write permissions.
Why does disk overuse happen in a TencentDB for SQL Server instance?
The following may cause disk overuse:
Too much data: As businesses expand, new data is constantly inserted, resulting in data file space growth.
Too many logs: The TencentDB database backs up and truncates log files regularly. If transactions are not committed for a long time, and there are a high number of UPDATE, INSERT, and DELETE operations in the database, the transaction log file may become too large.
What should I do if the data volume exceeds the maximum storage space of my TencentDB for SQL Server instance?
If the data space is excessively occupied, you need to expand the database or delete some database tables from the console to release storage space. After deleting data, you can perform database shrink operations on the console. It is recommended to perform these operations during off-peak business hours. For details, please refer to Adjusting Instance Specifications or Deleting Databases or Shrinking Databases.
If the log file is too large, there may be some transactions not ended for a long time. You can monitor and clear sessions or transactions with a long execution time.
Can I directly expand the storage space of a TencentDB for SQL Server instance? Do I need to perform data migration? What is the impact of the expansion?
Storage space can be directly expanded. If the storage space on the host of the instance is sufficient, there is no need to migrate data, and the expansion will not affect the business in any way. If the host's storage space is insufficient, the system will automatically create a new primary-standby instance on a host with enough storage space and synchronize the original instance data. The instance access will not be affected during data synchronization; after the migration is complete, a switch will occur, causing a second-level database connection flash disruption.
For more information on how to expand storage space and whether expansion will cause flash disruptions, please refer to Adjusting Instance Specifications.
Can I reduce the disk space of a TencentDB for SQL Server instance?
TencentDB for SQL Server two-node (formerly high-availability/cluster edition) local disk version: Supports disk space reduction.
TencentDB for SQL Server two-node (formerly high-availability/cluster edition) cloud disk version: Disk space reduction is not supported.
TencentDB for SQL Server single-node (formerly basic edition) cloud disk version: Disk space reduction is not supported.
What does the disk space of a TencentDB for SQL Server instance consist of?
1. Data file space: It is the space used by your data. The data file space of TencentDB for SQL Server is preallocated. Therefore, each created database takes up nearly 500 MB to store your data.
2. Transaction log file space: Each database in a TencentDB for SQL Server instance has a log file. In full recovery model, database transaction logs will be written to the log file.
3. Temporary table files: Files occupied by SQL Server tempdb, temporary tables generated by complex queries.
How much disk space is required for DDL operations?
To ensure normal business operations, you should avoid performing operations that may cause disk space usage surges, such as Data Definition Language (DDL) operations. If you must execute a DDL operation, make sure that the available disk space is greater than or equal to twice the size of the tablespace plus 10 GB. For example, if your tablespace is 500 GB, then when performing a DDL operation, make sure that the available disk space is greater than or equal to 500 * 2 + 10 = 1010 GB.
How do I view the data file size of a business database in a TencentDB for SQL Server instance?
SELECT @dbsize = SUM(CONVERT(BIGINT, CASE WHEN status & 64 = 0 THEN size ELSE 0 END)) ,@logsize = SUM(CONVERT(BIGINT, CASE WHEN status & 64 <> 0 THEN size ELSE 0 END))
FROM dbo.sysfiles
SELECT @reservedpages = SUM(a.total_pages) ,
@usedpages = SUM(a.used_pages) ,
@pages = SUM(CASE WHEN it.internal_type IN ( 202, 204, 207, 211,212, 213, 214, 215,216, 221, 222, 236 ) THEN 0 WHEN a.type <> 1 AND p.index_id < 2 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.partitions p
JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id
INSERT INTO #Tablespace EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT * FROM #Tablespace
order by convert(int,replace(DataSize,'KB','')) desc,2 desc
DROP TABLE #Tablespace
How does TencentDB for SQL Server repossess the tablespace?
TencentDB for SQL Server instance can shrink all database files to free up unused space. For more information, see Shrinking Database.
How do I avoid data disk space usage surges caused by massive amounts of data pushed to a TencentDB for SQL Server instance within a short time?
In dual-node (formerly high-availability/cluster edition) primary and standby instances, pushing large amounts of data may cause synchronization delays between the primary and standby instances, preventing log truncation and shrinkage, ultimately resulting in a surge in data disk space. It is recommended to stop for a while when pushing data, wait for full synchronization, and then continue to push the next batch of data.
How do I solve the problem of slow queries in TencentDB for SQL Server?
You can solve this problem in the following ways:
1. By examining the slow SQL log, it's possible to identify if there are any slow-running SQL queries and the performance characteristics of each query, thereby pinpointing the reasons for slow query execution. Log in to the SQL Server Console, in the instance list, click on the instance ID to enter the slow log page to query and download the slow log.
TencentDB for SQL Server can also locate the reasons for slow query execution by querying the DMV views.
2. View the CPU utilization metric to assist in problem diagnosis. For more information, see Monitoring Metrics.
3. Create a read-only instance dedicated for query to reduce the load of the primary instance and mitigate the database pressure.
4. Add an index to the joined field in multi-table correlated subqueries.
5. Avoid using select* statements for full table scans. Specify fields or add where conditions instead.
How do I troubleshoot the problem of a high CPU utilization in a TencentDB for SQL Server instance?
The instance CPU utilization may increase for the following reasons:
1. The business SQL statements are unreasonable, as they have a lot of I/O reads and logical operations, such as compilations, recompilations, sorting, aggregations, and table joins.
Symptom: There are slow queries, the curves of changes in the QPS and CPU utilization don't match, and there are statements with a high I/O among CPU-consuming statements.
Troubleshooting method and solution: Use the following query statements (or monitoring records in the event monitor) together with the slow queries to locate the slow SQL statements and analyze them for optimization. (We recommend you create an index in a table and use it in statements as much as possible. Use SSMS to analyze the actual execution plans of the statements. Then, use optimization suggestions provided by execution plan analysis to optimize the statements based on the specific business conditions.)
-- Query the CPU usage by real-time sessions:
SELECT C.text,DB_NAME(A.dbid) dbname,A.loginame,A.* FROM sys.sysprocesses A
CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) C
where status in('runnable','suspended')
order by cpu desc
-- Query the CPU usage by top20 session SQL statements:
SELECT TOP 20
total_worker_time/1000 AS [total CPU time(ms)],execution_count [number of executions],
qs.total_worker_time/qs.execution_count/1000 AS [average CPU time(ms)],
last_execution_time AS [last execution time],min_worker_time /1000 AS [minimum execution time(ms)],
max_worker_time /1000 AS [maximum execution time(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
AS [statement using CPU], qt.text [complete syntax],
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
--ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC -- (top 20 SQL statements with the highest CPU utilization)
--ORDER BY total_worker_time DESC -- (top SQL statement with the longest total CPU time)
2. The degree of parallelism setting for the instance is inappropriate.
Symptom: A large number of the same session blocks can be found in the query instance's current session. The wait type is CXPACKET.
Explanation: CXPACKET indicates that threads are waiting for each other to finish parallel processing. Generally, the CXPACKET wait type is normal for SQL Server, indicating that SQL Server uses a parallel plan when executing queries, which is usually faster compared to executing queries in a serialized manner. When using a parallel plan, the query is executed across multiple threads, and the query can only proceed once all parallel threads are completed. This means the query will be as fast as the slowest thread. However, when encountering simple queries with excessively high parallelism or complex queries where the data packets processed by parallel threads are unbalanced, unreasonable parallel execution plans or multiple threads waiting for a slower thread may cause CXPACKET waits.
Troubleshooting and handling methods:
SELECT C.text,DB_NAME(A.dbid) dbname,A.loginame,A. wait_type,A.* FROM sys.sysprocesses A
CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) C
where spid in(select SPID from sys.sysprocesses where blocked <>0)
Statement-level setting. Identify high consumption statements through real-time queries or slow logs, and specify OPTION (MAXDOP 1) to cancel parallel processing.
Example: SELECT * FROM TABLE WHERE L1='******' OPTION (MAXDOP 1)
From the instance level setting. Query the current instance's MAXDOP value
select * from sys.configurations where name like '%max%';
Modification method: Changes can be made in the console parameter configuration.
3. The business concurrency is high, increasing the instance load.
Symptom: It can be clearly seen from monitoring data that the number of requests, number of connections, and CPU utilization of the instance change in line with each other.
Solution: This problem is caused by a high number of requests. You can optimize the business logic to reduce the time of each request or upgrade the instance specification.
How do I view current connections and executed SQL statements in TencentDB for SQL Server?
1. Query current connections and executing SQL statements using sys.sysprocesses and sys.dm_exec_sql_text views.
SELECT C.text,DB_NAME(A.dbid) dbname,A.* FROM sys.sysprocesses A
CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) C
--where spid =
2. Query all current connections using sys.sysprocesses.
DBCC INPUTBUFFER(spid)
SELECT * FROM sys.sysprocesses;
Then use DBCC or sys.dm_exec_input_buffer to query the specific SQL of the connection.
DBCC INPUTBUFFER(spid)
SELECT * FROM sys.dm_exec_input_buffer(session_id, request_id);
How do I analyze and solve blockage in TencentDB for SQL Server?
Symptom: When your business often runs slowly but the separate executions of individual SQL statements are fast, there is a high possibility that blocking occurs in your database and slows down the SQL execution.
Cause: Blocking occurs as another transaction is reading/writing the requested resource, and the current SQL statement can continue to read/write the resource only after the resource lock is released by the transaction. If there are waits, the business operations will become slower.
Troubleshooting method:
1. Retrieve relevant sessions that are currently blocked through the sys.sysprocesses system view (blocked is the spid of the blocking source, waitresource is the resource being waited for by the blocked session).
select * from sys.sysprocesses where blocked <>0
SELECT C.text,DB_NAME(A.dbid) dbname,A.loginame,A.* FROM sys.sysprocesses A
CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) C
where spid in(select SPID from sys.sysprocesses where blocked <>0)
Note:
Sometimes sys.dm_exec_sql_text may not retrieve the specific SQL text; you can identify the blocking source and blocked spid through previous SQL queries and use DBCC or sys.dm_exec_input_buffer to query specific SQL.
2.1 If the blocking has already affected the business, you can promptly kill the blocking session (kill spid).
2.2 Check whether the blocking source is an uncommitted transaction, and if so, commit it promptly.
2.3 Analyze and optimize relevant SQL statements and business logic based on the blocking source SQL statement identified in previous troubleshooting steps. For example, if the execution time of the blocking source SQL statement is too long, you can analyze whether the execution plan can be optimized and whether the business logic is reasonable, and then make sure that resources are accessed in sequence to avoid blocking and deadlocks.
2.4 If select blocking occurs, you can use the with nolock query hint to avoid requesting locks and thus avoid blocking (e.g., select * from table with(nolock);).
