spHowBusy - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

spHowBusy

Author

Topic

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-18 : 11:30:23

A quickie to see how busy Your SQL server is right now.
(without uisng Taskmanager...)

CpuBusyInPercent             IoBusyInPercent              StartMeasure                   EndMeasure
---------------------------- ---------------------------- ------------------------------ -----------------------
35.690235690235              5.723905723905               2005-08-18 17:25:56.070        2005-08-18 17:26:01.087

CREATE PROCEDURE spHowBusy
	@measureforseconds INT = 5
AS
SET NOCOUNT ON

DECLARE @s VARCHAR(8)
SELECT @s = CONVERT(VARCHAR(8), DATEADD(SECOND,@measureforseconds,0), 8)


DECLARE @busy INT, @idle INT, @io INT, @start DATETIME
SELECT @busy = @@CPU_BUSY, @idle = @@IDLE, @io = @@IO_BUSY, @start = GETDATE()

WAITFOR DELAY @s

SELECT	CpuBusyInPercent = 100.0 * (@@CPU_BUSY-@busy) / ((@@CPU_BUSY+@@IDLE)-(@busy+@idle))
	,IoBusyInPercent = 100.0 * (@@IO_BUSY-@io) / ((@@CPU_BUSY+@@IDLE)-(@busy+@idle))
	,@start AS StartMeasure
	,GETDATE() AS EndMeasure

GO

rockmoose

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-18 : 11:34:15

Not much activity over here


CpuBusyInPercent             IoBusyInPercent              StartMeasure                                           EndMeasure                                             
---------------------------- ---------------------------- ------------------------------------------------------ ------------------------------------------------------ 
.000000000000                .645161290322                2005-08-18 11:33:22.557                                2005-08-18 11:33:27.557

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-18 : 11:50:38

Processing cubes, still the same cube....
One more to go, then I can go home

CpuBusyInPercent             IoBusyInPercent              StartMeasure                   EndMeasure
---------------------------- ---------------------------- ------------------------------ -----------------------
35.084095603422              6.506344054293               2005-08-18 17:40:28.967        2005-08-18 17:48:10.970

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-08-18 : 12:17:56

We store @@CPU_BUSY in a table every minute.

We also have a threshold value, and if we go over that, averaged over 5 minutes, we stop allowing new connections to the application (Web site) until the rolling 5-minute average falls below the threshold - kinda like a Health & Safety announcement : "The people in the store can finish up, but no new folk can come in"

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-18 : 12:23:12

quote:
Originally posted by Kristen

We store @@CPU_BUSY in a table every minute.

We also have a threshold value, and if we go over that, averaged over 5 minutes, we stop allowing new connections to the application (Web site) until the rolling 5-minute average falls below the threshold - kinda like a Health & Safety announcement : "The people in the store can finish up, but no new folk can come in"

Kristen

I remember the thread

Rolling average, that's nice!

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-08-18 : 12:27:07

"I remember the thread"

I got a "mentioned in dispatches" from the client for that one.

We predicted to the millisecond [well, you know what I mean] when their server would max-out in the run up to their busiest period.

They sat tight, and then when it happened we got to install a fire-breathing-dragon server with only 24 hours notice - that sort of reaction time co$t$!! And the client was still happy!

We cut-over about 10 GB of databases to a new server in under 15 minutes without any connected users to the web site losing a beat (well, those that choose to believe the "Sit tight for 15 minutes then press REFRESH" page they were shown

)

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-18 : 12:58:51

Good story.
Detach / Attach approach ?

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-08-18 : 15:50:01

"Detach / Attach approach ?"

Never even thought of that! Although I epxect the time to copy the [multi-GB] detached MDF/LDF would be fairly significant

We prep'd up a HOLDING page for the [WHOLE] site

We edited the INCLUDE file to include a commented-out CONNECT string to the new server

We did a FULL BACKUP and restore (with NORECOVERY) on New Server

Then when we were ready we:

o activated the HOLDING page on the Web Site
o Locked the DB [on old server] to DBO only
o Did a DIFF backup
o Copied the DIFF backup to the new server
o Restored the DIFF (on new server) with RECOVERY
o Edited the INCLUDE file and enabled the new Connection string
o Removed the holding page

and away we went. (Mind you, that's from memory, maybe it was more complicated ...

)

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-18 : 16:15:54

I like Your approach

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources