有一张表,目前数据量是2000W,每天凌晨会对该表进行去重的插入和旧数据更新,去重更新要用到的列已建非聚集索引,ID自增是聚集索引,执行数据操作的程序是C#编写的,每次把要进行操作的数据读入内存,单线程一条一条操作,每天需要操作的数据量大概在100W左右,去重后进入数据库的新数据大概在3、4W左右,正常情况下,100W数据大概1个半小时就可以处理完成,现在某些时候,执行操作数据会很慢,可能10个小时都统计不完。有什么办法可以优化?!SQL Server 2008数据库性能优化海量数据索引
调试欢乐多
DECLARE @MinExecutions int;
SET @MinExecutions = 5
SELECT EQS.total_worker_time AS TotalWorkerTime
,EQS.total_logical_reads + EQS.total_logical_writes AS TotalLogicalIO
,EQS.execution_count As ExeCnt
,EQS.last_execution_time AS LastUsage
,EQS.total_worker_time / EQS.execution_count as AvgCPUTimeMiS
,(EQS.total_logical_reads + EQS.total_logical_writes) / EQS.execution_count
AS AvgLogicalIO
,DB.name AS DatabaseName
,SUBSTRING(EST.text
,1 + EQS.statement_start_offset / 2
,(CASE WHEN EQS.statement_end_offset = -1
THEN LEN(convert(nvarchar(max), EST.text)) * 2
ELSE EQS.statement_end_offset END
- EQS.statement_start_offset) / 2
) AS SqlStatement
-- Optional with Query plan; remove comment to show, but then the query takes !!much longer time!!
--,EQP.[query_plan] AS [QueryPlan]
FROM sys.dm_exec_query_stats AS EQS
CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) AS EST
CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle) AS EQP
LEFT JOIN sys.databases AS DB
ON EST.dbid = DB.database_id
WHERE EQS.execution_count > @MinExecutions
AND EQS.last_execution_time > DATEDIFF(MONTH, -1, GETDATE())
ORDER BY AvgLogicalIo DESC
,AvgCPUTimeMiS DESC