先确定是否为 数据库的问题。 --查询占用CPU最多的SQL语句 SELECT TOP 50 total_worker_time / execution_count AS [Avg CPU Time], (SELECT Substring(text, statement_start_offset / 2, ( CASE WHEN statement_end_offset = -1 THEN Len(CONVERT(NVARCHAR(max), text)) * 2 ELSE statement_end_offset END - statement_start_offset ) / 2) FROM sys.Dm_exec_sql_text(sql_handle)) AS query_text, * FROM sys.dm_exec_query_stats ORDER BY [Avg CPU Time] DESC
267579 SELECT i.name AS [Index_Name], CAST(i.index_id AS int) AS [Index_ID], fi.index_depth AS [Depth], fi.page_count AS [Pages], fi.record_count AS [Rows], fi.min_record_size_in_bytes AS [MinimumRecordSize], fi.max_record_size_in_bytes AS [MaximumRecordSize], fi.avg_record_size_in_bytes AS [AverageRecordSize], fi.forwarded_record_count AS [ForwardedRecords], fi.avg_page_space_used_in_percent AS [AveragePageDensity], fi.index_type_desc AS [IndexType], fi.partition_number AS [PartitionNumber], fi.ghost_record_count AS [GhostRows], fi.version_ghost_record_count AS [VersionGhostRows], fi.avg_fragmentation_in_percent AS [AverageFragmentation] FROM sys.tables AS tbl INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id) INNER JOIN sys.dm_db_index_physical_stats(@database_id, NULL, NULL, NULL, 'SAMPLED') AS fi ON fi.object_id=CAST(i.object_id AS int) AND fi.index_id=CAST(i.index_id AS int) WHERE (i.name=N'XPK用户_指令下发记录表')and((tbl.name=N'gprs_t_userCmds' and SCHEMA_NAME(tbl.schema_id)=N'dbo')) ORDER BY [Index_Name] AS 0x02000000F3534C003EEDFE9A3C1835A099D44AD71225B6D1 130 -1 1 0x06000700F3534C00B8E11956000000000000000000000000 2012-05-29 13:10:35.127 2012-05-29 13:10:35.250 1 267579 267579 267579 267579 3493 3493 3493 3493 0 0 0 0 30432 30432 30432 30432 0 0 0 0 18316407 18316407 18316407 18316407 70685 if exists( select 1 from dbo.gprs_t_userCmds where convert(varchar(10), outTime, 120)=convert(varchar(10), @now, 120) and imsi=@imsi group by imsi having(sum(fee)>=1500) ) 0x03000700A815293DE052140151A000000000000000000000 2684 3076 1 0x05000700A815293DB8617308000000000000000000000000 2012-05-29 13:02:10.510 2012-05-29 13:17:44.897 7212 509786129 0 30274 142579 144 0 0 24 0 0 0 0 7377876 0 1023 1023 0 0 0 0 2266116211 0 50782 2386719 61970 select @nextGprsTime=dbo.gprs_f_getNextGprsTime(@imsi) -- --如果今天不为1号,下次gprs时间为1号则走免费 -- if(day(getdate())>1 and day(@nextGprsTime)=1) -- begin -- goto outWithoutCmds -- end /************特殊处理************************************/ 0x03000700FC62B7319D6E10015FA000000100000000000000 3958 4432 1 0x05000700FC62B731B8218F07000000000000000000000000 2012-05-29 13:02:09.390 2012-05-29 13:17:44.897 8348 517326168 0 0 143555 1088 0 0 29 157 0 0 5 9121958 0 6 1808 0 0 0 0 2390154296 0 0 3498047 41015 select top 1 @rstId=rstId, @result=result from CommonData.dbo.ans_t_result where charindex(question,@question)>0 order by len(question) desc --记录输出 0x0300050059ED607F0BD96F0147A000000100000000000000 1998 2330 1 0x0500050059ED607FB8216B50000000000000000000000000 2012-05-29 13:04:33.617 2012-05-29 13:07:48.157 2 82031 42969 39063 42969 0 0 0 0 0 0 0 0 60 30 30 30 0 0 0 0 307618 160157 147461 160157 35473 insert into dbo.gprs_t_response_cmds(gprsId, inTime, portNo, cmdNo, cmdId, spShortName, moNum, price) select @newGprsId gprsId, @inTime inTime, oc.portNo, oc.cmdNo, oc.cmdId, s.shortName, oc.moNum, oc.fee from GprsRun.dbo.gprs_t_out_cmds oc left join CommonData.dbo.base_t_cmd c on c.cmdId=oc.cmdId left join CommonData.dbo.base_t_port p on p.portId=c.portId left join CommonData.dbo.base_t_sp s on s.spId=p.spId where gprsId=@gprsId --保存下发屏蔽 0x03000900AFC61905678DAD003FA000000100000000000000 6508 7474 1 0x05000900AFC61905B841380F000000000000000000000000 2012-05-29 13:03:03.560 2012-05-29 13:17:44.487 2593 91982421 41992 10743 73243 400 0 0 369 30 0 0 4 7629122 2942 2942 2970 0 0 0 0 217766601 229493 21485 1471680 35386 insert into dbo.gprs_t_response_shield(gprsId, inTime, ports, keywords, unionKeywords) select @newGprsId gprsId, @inTime inTime, ports, keywords, unionKeywords from GprsRun.dbo.gprs_t_out_shields where gprsId=@gprsId --保存下发二次配置 0x03000900AFC61905678DAD003FA000000100000000000000 7476 7974 1 0x05000900AFC61905B841380F000000000000000000000000 2012-05-29 13:03:03.650 2012-05-29 13:17:44.717 2593 91756830 28321 14649 84961 315 0 0 310 4 0 0 3 6243035 2408 2407 2424 0 0 0 0 223254885 349610 19532 1049805 23438 if not exists(select * from CommonData.dbo.ans_t_question where question=@question 0x0300050059ED607F0BD96F0147A000000100000000000000 2886 3052 1 0x0500050059ED607FB8216B50000000000000000000000000 2012-05-29 13:04:33.757 2012-05-29 13:04:34.083 1 23438 23438 23438 23438 29 29 29 29 0 0 0 0 288 288 288 288 0 0 0 0 126954 126954 126954 126954 怎么看?
--查询占用CPU最多的SQL语句
SELECT TOP 50 total_worker_time / execution_count AS [Avg CPU Time],
(SELECT Substring(text, statement_start_offset / 2, ( CASE
WHEN statement_end_offset = -1 THEN Len(CONVERT(NVARCHAR(max), text)) * 2
ELSE statement_end_offset
END - statement_start_offset ) / 2)
FROM sys.Dm_exec_sql_text(sql_handle)) AS query_text,
*
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESC
267579 SELECT i.name AS [Index_Name], CAST(i.index_id AS int) AS [Index_ID], fi.index_depth AS [Depth], fi.page_count AS [Pages], fi.record_count AS [Rows], fi.min_record_size_in_bytes AS [MinimumRecordSize], fi.max_record_size_in_bytes AS [MaximumRecordSize], fi.avg_record_size_in_bytes AS [AverageRecordSize], fi.forwarded_record_count AS [ForwardedRecords], fi.avg_page_space_used_in_percent AS [AveragePageDensity], fi.index_type_desc AS [IndexType], fi.partition_number AS [PartitionNumber], fi.ghost_record_count AS [GhostRows], fi.version_ghost_record_count AS [VersionGhostRows], fi.avg_fragmentation_in_percent AS [AverageFragmentation] FROM sys.tables AS tbl INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id) INNER JOIN sys.dm_db_index_physical_stats(@database_id, NULL, NULL, NULL, 'SAMPLED') AS fi ON fi.object_id=CAST(i.object_id AS int) AND fi.index_id=CAST(i.index_id AS int) WHERE (i.name=N'XPK用户_指令下发记录表')and((tbl.name=N'gprs_t_userCmds' and SCHEMA_NAME(tbl.schema_id)=N'dbo')) ORDER BY [Index_Name] AS 0x02000000F3534C003EEDFE9A3C1835A099D44AD71225B6D1 130 -1 1 0x06000700F3534C00B8E11956000000000000000000000000 2012-05-29 13:10:35.127 2012-05-29 13:10:35.250 1 267579 267579 267579 267579 3493 3493 3493 3493 0 0 0 0 30432 30432 30432 30432 0 0 0 0 18316407 18316407 18316407 18316407
70685 if exists( select 1 from dbo.gprs_t_userCmds where convert(varchar(10), outTime, 120)=convert(varchar(10), @now, 120) and imsi=@imsi group by imsi having(sum(fee)>=1500) ) 0x03000700A815293DE052140151A000000000000000000000 2684 3076 1 0x05000700A815293DB8617308000000000000000000000000 2012-05-29 13:02:10.510 2012-05-29 13:17:44.897 7212 509786129 0 30274 142579 144 0 0 24 0 0 0 0 7377876 0 1023 1023 0 0 0 0 2266116211 0 50782 2386719
61970 select @nextGprsTime=dbo.gprs_f_getNextGprsTime(@imsi) -- --如果今天不为1号,下次gprs时间为1号则走免费 -- if(day(getdate())>1 and day(@nextGprsTime)=1) -- begin -- goto outWithoutCmds -- end /************特殊处理************************************/ 0x03000700FC62B7319D6E10015FA000000100000000000000 3958 4432 1 0x05000700FC62B731B8218F07000000000000000000000000 2012-05-29 13:02:09.390 2012-05-29 13:17:44.897 8348 517326168 0 0 143555 1088 0 0 29 157 0 0 5 9121958 0 6 1808 0 0 0 0 2390154296 0 0 3498047
41015 select top 1 @rstId=rstId, @result=result from CommonData.dbo.ans_t_result where charindex(question,@question)>0 order by len(question) desc --记录输出 0x0300050059ED607F0BD96F0147A000000100000000000000 1998 2330 1 0x0500050059ED607FB8216B50000000000000000000000000 2012-05-29 13:04:33.617 2012-05-29 13:07:48.157 2 82031 42969 39063 42969 0 0 0 0 0 0 0 0 60 30 30 30 0 0 0 0 307618 160157 147461 160157
35473 insert into dbo.gprs_t_response_cmds(gprsId, inTime, portNo, cmdNo, cmdId, spShortName, moNum, price) select @newGprsId gprsId, @inTime inTime, oc.portNo, oc.cmdNo, oc.cmdId, s.shortName, oc.moNum, oc.fee from GprsRun.dbo.gprs_t_out_cmds oc left join CommonData.dbo.base_t_cmd c on c.cmdId=oc.cmdId left join CommonData.dbo.base_t_port p on p.portId=c.portId left join CommonData.dbo.base_t_sp s on s.spId=p.spId where gprsId=@gprsId --保存下发屏蔽 0x03000900AFC61905678DAD003FA000000100000000000000 6508 7474 1 0x05000900AFC61905B841380F000000000000000000000000 2012-05-29 13:03:03.560 2012-05-29 13:17:44.487 2593 91982421 41992 10743 73243 400 0 0 369 30 0 0 4 7629122 2942 2942 2970 0 0 0 0 217766601 229493 21485 1471680
35386 insert into dbo.gprs_t_response_shield(gprsId, inTime, ports, keywords, unionKeywords) select @newGprsId gprsId, @inTime inTime, ports, keywords, unionKeywords from GprsRun.dbo.gprs_t_out_shields where gprsId=@gprsId --保存下发二次配置 0x03000900AFC61905678DAD003FA000000100000000000000 7476 7974 1 0x05000900AFC61905B841380F000000000000000000000000 2012-05-29 13:03:03.650 2012-05-29 13:17:44.717 2593 91756830 28321 14649 84961 315 0 0 310 4 0 0 3 6243035 2408 2407 2424 0 0 0 0 223254885 349610 19532 1049805
23438 if not exists(select * from CommonData.dbo.ans_t_question where question=@question 0x0300050059ED607F0BD96F0147A000000100000000000000 2886 3052 1 0x0500050059ED607FB8216B50000000000000000000000000 2012-05-29 13:04:33.757 2012-05-29 13:04:34.083 1 23438 23438 23438 23438 29 29 29 29 0 0 0 0 288 288 288 288 0 0 0 0 126954 126954 126954 126954
怎么看?
1秒=1000毫秒。然后优化这些SQL
有这么夸张?