Is there any way to solve it, mainly is the SQL query associated with several tables, more time-consuming
Isn't set up the database cluster, reduce the pressure of a single database
Then what method is the Sql server database database synchronization, real-time data problem

CodePudding user response:

Build Alwayson, read-only access read-only auxiliary copy can be balanced pressure,
But in the absence of optimized, 100% CPU usage is to make optimization processing,

How the server's CPU, memory, posted;
2. Server disk IO, do the test, especially within the database file partition to measure,
https://download.csdn.net/download/yenange/9540422

3. The following SQL, you execute on the server, see which SQL CPU, post,

 SELECT TOP 10 OBJECT_NAME (qt objectid, qt. DbId) AS procName, 
DB_NAME (qt. DbId) AS [DB_NAME], 
Qt. The text AS SQL_Full, 
The SUBSTRING (
Qt. Text, 
(qs. Statement_start_offset/2) + 1, 
(
(
CASE statement_end_offset 
THEN the WHEN - 1 DATALENGTH (qt. Text) 
The ELSE qs. Statement_end_offset 
END 
- qs. Statement_start_offset 
)/2 
) + 1 
) AS SQL_Part - statistics corresponding part of the statement 
, 
Qs creation_time, 
Qs last_execution_time, 
Qs execution_count, 
Qs last_elapsed_time/1000000 AS lastElapsedSeconds, 
Qs last_worker_time/1000000 AS lastCpuSeconds, 
CAST (
Qs. Total_elapsed_time/1000000.0/(
CASE 
The WHEN qs. Execution_count=0 THEN - 1 
The ELSE qs. Execution_count 
END 
) AS a DECIMAL (28, 2) 
) AS avgDurationSeconds, 
CAST (qs) last_logical_reads AS BIGINT) * 1.0/(1024 * 1024 * 8060 AS 
LastLogicReadsMB, 
Qs last_logical_reads, 
Qs. Plan_handle 
The FROM sys. Dm_exec_query_stats qs 
CROSS the APPLY sys. Dm_exec_sql_text (qs) sql_handle) AS qt 
CROSS the APPLY sys. Dm_exec_query_plan (qs) plan_handle) AS p 
WHERE qs. Last_execution_time & gt;=the CONVERT (CHAR (10), GETDATE (), 120) + '08:00' - after 8 PM today slow SQL 
AND qs. Last_elapsed_time & gt;=3 * 1000 * 1000 - only to take the record of the execution time is more than 3 seconds 
AND qt. [text] NOT LIKE '% Proc_DBA %' 
The ORDER BY 
Qs. Last_worker_time DESC 

CodePudding user response:

With Alwayson easier

CodePudding user response:

No synchronization database approach, I wanted to subordinate a wanted to cluster, a database changed all of the rest of the database changes

CodePudding user response:

reference qq_43244215 reply: 3/f

no synchronization database approach, I think is to want to deploy a cluster, a database changed all of the rest of the database to modify

Alwayson is ah, you look at the first to say again,
https://blog.csdn.net/yenange/article/details/46495661

CodePudding user response:

In addition, don't put all your hope in alwayson,
If you haven't got the SQL optimization, or lack of index, design is not reasonable, or our business is problematic,
I carry out slow SQL # 1: are you going to optimization,

CodePudding user response:

Because my watch was not my own design, there is a big problem,