SQL SERVER ²é¿´CPUÀûÓÃÂÊ
--²é¿´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.SP
Ïà¹ØÎĵµ£º
ÍâÁ¬½ÓÈ¡Êý¾ÝÈç¹ûΪ¿Õ£¬²»´æÔÚASʱ¿ÉÄÜÈ¡µ½Êý¾Ý£¬µ«¼ÓÁËAS¾Í»á±¨´í¡£±ÈÈçSelect Top 10 a.*,b.Class AS ClassId
,b.DeviceName from AuxBusInfo a Left Join DeviceCandidate b On a.InitialDeviceRscId = b.DeviceRscId Order By AuxBusId£¬µ± ......
1. ˵Ã÷£º¸´ÖƱí(Ö»¸´Öƽṹ£¬Ô´±íÃû£ºa£¬Ð±íÃû£ºb)
SQL: select * into b from a where 1<>1;
2. ˵Ã÷£º¿½±´±í(¿½±´Êý¾Ý£¬Ô´±íÃû£ºa£¬Ä¿±ê±íÃû£ºb)
SQL: insert into b(a, b, c) select d, e, f from b;
&nb ......
SQLÓïÑÔÓÉÃüÁî¡¢×Ӿ䡢ÔËËãºÍ¼¯ºÏº¯ÊýµÈ¹¹³É¡£ÔÚSQLÖУ¬Êý¾Ý¶¨ÒåÓïÑÔDDL£¨ÓÃÀ´½¨Á¢¼°¶¨ÒåÊý¾Ý±í¡¢×Ö¶ÎÒÔ¼°Ë÷ÒýµÈÊý¾Ý¿â½á¹¹£©°üº¬µÄÃüÁîÓÐCREATE¡¢DROP¡¢ALTER£»Êý¾Ý²Ù×ÝÓïÑÔDML£¨ÓÃÀ´ÌṩÊý¾ÝµÄ²éѯ¡¢ÅÅÐòÒÔ¼°É¸Ñ¡Êý¾ÝµÈ¹¦ÄÜ£©°üº¬µÄÃüÁîÓÐSELECT¡¢INSERT¡¢UPDATE¡¢DELETE¡£
Ò»¡¢SQLÓï¾ä
£¨1£©Select ²éѯÓï¾ä
Óï·¨£ºSE ......
SQLÔÚ½¨Á¢Óë·þÎñÆ÷µÄÁ¬½Óʱ³ö´íµÄ½â¾ö·½
ÔÚ½¨Á¢Óë·þÎñÆ÷µÄÁ¬½Óʱ³ö´í¡£ÔÚÁ¬½Óµ½ SQL Server 2005 ʱ£¬ÔÚĬÈϵÄÉèÖÃÏ SQL Server ²»ÔÊÐí½øÐÐÔ¶³ÌÁ¬½Ó¿ÉÄܻᵼÖ´Ëʧ°Ü¡£ (provider: ÃüÃû¹ÜµÀÌṩ³ÌÐò, error: 40 - ÎÞ·¨´ò¿ªµ½ SQL Server µÄÁ¬½Ó)
¸Õ¸Õ°²×°µÄÊý¾Ý¿âϵͳ£¬°´ÕÕÄ ......
Ò»¡¢ÔÚDelphi7ÖÐÁ¬½ÓMS SQL Server 2000µÄ·½·¨¡£
¸Õ¿ªÊ¼Ê±½çÃæÈçÏ£ºÌí¼Ó4¸ö¿Ø¼þ¡£
ÉèÖÿؼþÊôÐÔ¹ý³Ì£º
1¡¢ADOConnection1ÉèÖÃ
1£©Ë«»÷ADOConnection1£¬½øÐÐÉèÖÃÁ¬½Ó×Ö·û´®£¨×÷ÓÃÊÇ£ºÑ¡È¡Á¬½ÓÇý¶¯·½Ê½ºÍÁ¬½ÓµÄÊý¾Ý¿âÉèÖã©¡£¹ý³ÌÈçÏÂͼËùʾ£º
2¡¢ADOQuery1ÉèÖãº
1£©ADOQuery1.connectionÊôÐÔΪADOConnection1; ......