February 15, 2011 at 9:38 am
I am throwing around the idea of moving databases with a light load to a virtual environment. Currently I have a multi-instance physical environment. What counters would I use to determine which databases (not instances) have a heavy load?
Thanks in advance!
February 15, 2011 at 10:46 am
I generally have a server-side trace running that will tell me how many transactions per minute/second/hour/month/whatever, a given database is supporting, and how much CPU, etc., they are using. That's what I generally use for that kind of thing. That and some of the DMVs on index updates and selects.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 15, 2011 at 11:25 am
Basic metrics such as wait states and queues, cpu load, disk i/o, these are the things that tell you the server is under load. It doesn't tell you why necessarily. For that you need the information that Gus has suggested.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 15, 2011 at 1:25 pm
GSquared (2/15/2011)
I generally have a server-side trace running that will tell me how many transactions per minute/second/hour/month/whatever,
Are you referring to perfmon? I only see an option for transaction at the instance level.
February 15, 2011 at 2:05 pm
SQLdba-473999 (2/15/2011)
GSquared (2/15/2011)
I generally have a server-side trace running that will tell me how many transactions per minute/second/hour/month/whatever,Are you referring to perfmon? I only see an option for transaction at the instance level.
No. A trace. Perfmon is good for basic data, but you can get a lot more details out of a trace, in my experience.
Search online or in BOL for "sp_trace_create" and related subjects. There's a LOT you can do with them, for making a DBA job easier and more effective.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 15, 2011 at 2:10 pm
I'll look into it. Thanks.
February 16, 2011 at 11:34 am
I've taken someone elses query of fn_virtualfilestats and slightly modified it so I can see the drive letter so if you want you can break it down to that level as well. This should give the read/write activity per database file since the last SQL restart so it is a point in time. So if you need to monitor the activity between day 1 and day 30 you will need to capture the data either to a table or just a simple spreedsheet. The % values are across the SQL instance.
DECLARE @TotalIO BIGINT,
@TotalBytes BIGINT,
@TotalStall BIGINT
SELECT @TotalIO = SUM(NumberReads + NumberWrites),
@TotalBytes = SUM(BytesRead + BytesWritten),
@TotalStall = SUM(IoStallMS)
FROM ::FN_VIRTUALFILESTATS(NULL, NULL)
SELECT [DbName] = DB_NAME([DbId]),
(SELECT Left(physical_name,1) FROM sys.master_files
WHERE database_id = [DbId]
and FILE_ID = [FileId]) DriveLetter,
(SELECT name FROM sys.master_files
WHERE database_id = [DbId]
and FILE_ID = [FileId]) filename,
[%ReadWrites] = (100 * (NumberReads + NumberWrites) / @TotalIO),
[%Bytes] = (100 * (BytesRead + BytesWritten) / @TotalBytes),
[%Stall] = (100 * IoStallMS / @TotalStall),
[NumberReads],
[NumberWrites],
[TotalIO] = CAST((NumberReads + NumberWrites) AS BIGINT),
[MBsRead] = [BytesRead] / (1024*1024),
[MBsWritten] = [BytesWritten] / (1024*1024),
[TotalMBs] = (BytesRead + BytesWritten) / (1024*1024),
[IoStallMS],
IoStallReadMS,
IoStallWriteMS,
[AvgStallPerIO] = ([IoStallMS] / ([NumberReads] + [NumberWrites] + 1)),
[AvgStallPerReadIO] = (IoStallReadMS / ([NumberReads] + 1)),
[AvgStallPerWriteIO]= (IoStallWriteMS / ( [NumberWrites] + 1)),
[AvgBytesPerRead] = ((BytesRead) / (NumberReads + 1)),
[AvgBytesPerWrite] = ((BytesWritten) / (NumberWrites + 1))
FROM ::FN_VIRTUALFILESTATS(NULL, NULL)
ORDER BY dbname
February 16, 2011 at 1:05 pm
Very helpful, thank you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply