服务器运行缓慢,查看原因是SQL占用了服务器CPU资源的接近百分百,但是内存足够,公司门户网站,在六月一号前一直运行正常,最近才出现的问题,时好时不好,求各位大侠帮忙解决!!!
说明一下服务器情况:IIS 6.0
.netframeword SNK 1.1
SQLSERVER 2000
说明一下服务器情况:IIS 6.0
.netframeword SNK 1.1
SQLSERVER 2000
解决方案 »
- 怎么把查出的多个结果赋给一个变量
- 嵌套查询(如何根据表中某个字段的值来选择查询条件)
- 紧急求助:数据分组合并问题
- 请问如何将SQL数据库中的某一个表中的数据导入到excel表格中?
- 把一个表指定的几列中的凡是空值的所有行记录更新为0的语句如何写呀?
- 数据位数问题,输入的数据位数怎么跟读取的不一样
- 在存储过程中可递归调用自定义函数吗?如何实现?
- 我建了一个作业"modifydate",但在查询分析器运行USE cass EXEC sp_delete_job @job_name = 'modifydate',出现未能找到存储过程 'sp_delet
- 这种程序是怎么实现的?居然能突破微软的限制 收集到微软禁止的命令?
- 将字符串转换为 smalldatetime 数据类型时失败
- 如何在server2005创建Oracle链接服务器
- 一道sql题,规律我已看出,求做法,谢谢
r.status,r.cpu_time,r.logical_reads,wait_resource,wait_type,LEFT(p.text,500)
--r.*
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) p
WHERE r.session_id>=50
ORDER BY cpu_time DESC
2.从事件探查器跟踪一下SQL语句,分析出占用高CPU的语句,再进行相关处理。
CREATE PROCEDURE sp_GetTop10_CPU AS
BEGIN
SET NOCOUNT ON
DECLARE @cinterval char(8)
DECLARE @interval int
SET @cinterval = '00:00:10'
CREATE TABLE #thread
(
RUN INT NOT NULL,
SPID INT NOT NULL,
THREAD INT NULL,
STATUS varchar(30) NOT NULL,
[IO] INT NOT NULL,
CPU INT NOT NULL,
MEM INT NOT NULL,
LOCKS int NOT NULL,
BLK INT NOT NULL,
hostname varchar(30) NOT NULL,
hostprocess INT NOT NULL
CONSTRAINT PK_thread1 PRIMARY KEY CLUSTERED (RUN,SPID)
)
INSERT #thread
SELECT 1,pr.spid,pr.kpid,pr.status,pr.physical_io,pr.cpu, pr.memusage,ISNULL(count(lk.spid),0),pr.blocked , max(pr.hostname), max(pr.hostprocess)
FROM master.dbo.sysprocesses pr
LEFT OUTER JOIN master.dbo.syslocks lk
ON pr.spid = lk.spid
WHERE pr.spid>=50 AND pr.spid<>@@SPID
GROUP BY pr.spid,pr.kpid,pr.status,pr.physical_io, pr.cpu,pr.memusage,pr.blocked WAITFOR DELAY @cinterval
INSERT #thread
SELECT 2,pr.spid,pr.kpid,pr.status,pr.physical_io,pr.cpu, pr.memusage,ISNULL(count(lk.spid),0),pr.blocked , max(pr.hostname), max(pr.hostprocess)
FROM master.dbo.sysprocesses pr
LEFT OUTER JOIN master.dbo.syslocks lk
ON pr.spid = lk.spid
WHERE pr.spid>=50 AND pr.spid<>@@SPID
GROUP BY pr.spid,pr.kpid,pr.status,pr.physical_io, pr.cpu,pr.memusage,pr.blocked
SELECT TOP 10 T.*
FROM
(
SELECT a.SPID, a.hostname, b.hostprocess, b.STATUS, (b.CPU-a.CPU) as 'CPU'
FROM #thread a
JOIN #thread b
ON a.SPID=b.SPID and a.RUN=1 and b.RUN=2
) T
ORDER BY CPU DESC
DROP TABLE #thread
RETURN
ENDGO --
sp_GetTop10_CPU/*
警告: 聚合或其它 SET 操作消除了空值。
警告: 聚合或其它 SET 操作消除了空值。
SPID hostname hostprocess STATUS CPU
----------- ------------------------------ ----------- ------------------------------ -----------
55 LANDSEA-8CC1455 2440 runnable 141
54 LANDSEA-8CC1455 2440 sleeping 0
52 LANDSEA-8CC1455 2764 runnable 0
51 LANDSEA-8CC1455 2764 sleeping 0*/