SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 spHowBusy		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rockmoose
SQL Natt Alfen

Sweden
3279 Posts
Posted - 08/18/2005 :  11:30:23  Show Profile  Reply with Quote
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
Edited by - rockmoose on 08/18/2005 11:42:59

X002548
Not Just a Number

15586 Posts
Posted - 08/18/2005 :  11:34:15  Show Profile  Reply with Quote
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
Edited by - X002548 on 08/18/2005 11:34:59
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts
Posted - 08/18/2005 :  11:50:38  Show Profile  Reply with Quote
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

United Kingdom
22191 Posts
Posted - 08/18/2005 :  12:17:56  Show Profile  Reply with Quote
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

Sweden
3279 Posts
Posted - 08/18/2005 :  12:23:12  Show Profile  Reply with Quote
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

United Kingdom
22191 Posts
Posted - 08/18/2005 :  12:27:07  Show Profile  Reply with Quote
"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

Sweden
3279 Posts
Posted - 08/18/2005 :  12:58:51  Show Profile  Reply with Quote
Good story.
Detach / Attach approach ?

rockmoose
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 08/18/2005 :  15:50:01  Show Profile  Reply with Quote
"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

Sweden
3279 Posts
Posted - 08/18/2005 :  16:15:54  Show Profile  Reply with Quote
I like Your approach
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000