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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-18 : 12:58:51
Good story.
Detach / Attach approach ?

rockmoose
Go to Top of Page

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
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-18 : 16:15:54
I like Your approach
Go to Top of Page
   

- Advertisement -