We are collecting perfmon counters every 15 second . Among the counters is Average Disk sec/Write . I under stand that it is the average time taken in second to write a data to the disk. However I do not understand what the is quantum of data written in one second. What is the measure or unit of data in Average Disk sec/Write or is it simply the average time taken for an I/O write request.
View 4 RepliesIf I return the Average, Minimum, and Maximum values for the counter Physical Disk: Avg. Disk Queue Length, and those values are 10, 0, 87 respectively, which value do I use to compute the Avg. Disk Queue Length for a 4 disk array(RAID 10): Average, Minimum, or Maximum? The disk(lun) is on a SAN.
Have a sql 2000 db which I have no say in design, just make it run. My typical sql counters such as system queue, and buffer cache and cache hit ratio are all good. If I need to monitor disk activity (mainly how fast my data is being read, how long the user is waiting for that data for both reads and inserts), what are the best counters for this, and what value should throw up a red flag.
View 1 Replies View Related
What's the best practice when it comes to disk write caching? Is it better to enable or disable this feature?
Thanks.
During investigation problem with disk i found some issue, that every 2 minutes is writing on disk , it looks like to mdf file of database, but in almost 6-8milions Bytes / sec , it is about 1-2 sec , but every 2minutes... this is normal behaviour ? this is synchonyous writing to disk from memory.
View 4 Replies View RelatedHow you would calculate the average read/write latency experienced by a SQL Server instance during a specific time window in order to monitor this for multiple instances. From this MSDN blog, I know that you have to take multiple samples and do some calculations to get the correct latency.
[URL] ...
However, the SQLServer:Resource Pool Stats object tracks these numbers per resource pool and we want to get one number for the whole server. Since there can be a different base value for each resource pool, you can't simply sum the numerator values together. Here's some sample data from a server that illustrates the problem.
object_name counter_name instance_name cntr_value cntr_type
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) default 307318919 1073874176
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) Base default 25546724 1073939712
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) internal 2045730 1073874176
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) Base internal 208270 1073939712
I'm thinking I would need to do some sort of weighted average, but I'm not sure if that will result in the correct value. Here's the formula I am thinking about using currently before doing the calculation over time
((default * default[base]) + (internal * internal[base]))/(default[base] + internal[base])
Then to do the calculation over time, I'd use the changes in the calculated numerator and denominator to get the average.
Does this sound like to correct way to get this value? Is there a good way to verify?
I have do some benchmark test between the "Principle", "Mirror" and "Witness", things seems normal, the System usage (CPU, Disk Read/Write) on the "Witness" is very small and the CPU usage on Principle and Mirror is similar, However....
For the "Disk Write Bytes/sec", the usage on Principle is 200,000 and the Mirror is 1,500,000, the mirror server is talking about x7 times Disk Write Bytes/sec on the Mirror.
I cannot think of any reason why the Disk Write Bytes /sec is so much higher than the Principle, can somebody help me?
hello,all
I am new to Sql 2000,I installed sql 2000 database in C disk,but Now I found my C disk space is smaller than before,So I want to move my databse(include data and structure) from C Disk to D Disk(its space is very large) .
is it possible to do it ?
if its can be done ,do I need to change my asp.net program source code (exp: chaneg my crystal report connectstring ) ?
thanks in advanced!
Calculation of an average using DAX' AVERAGE and AVERAGEX.This is the manual calculation in DW, using SQL.In the tabular project (we're i've noticed that these 4 %'s are in itself strange), in a 1st moment i've noticed that i would have to divide by 100 to get the same values as in the DW, so i've used AVERAGEX:
Avg_AMP:=AVERAGEX('Fct Sales';'Fct Sales'[_AMP]/100)
Avg_AMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_AMPdollar]/100)
Avg_FMP:=AVERAGEX('Fct Sales';'Fct Sales'[_FMP]/100)
Avg_FMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_FMPdollar]/100)
The results were, respectively: 701,68; 2120,60...; -669,441; and finally **-694,74** for Avg_FMPdollar.i can't understand the difference to SQL calculation, since calculations are similar to the other ones. After that i've tried:
test:=SUM([_FMPdollar])/countrows('Fct Sales') AND the value was EQUAL to SQL: -672,17
test2:=AVERAGE('Fct Sales'[_Frontend Margin Percent ACY]), and here, without dividing by 100 in the end, -696,74...
So, AVERAGE and AVERAGEX have a diferent behaviour from the SUM divided by COUNTROWS, and even more strange, test2 doesn't need the division by 100 to be similar to AVERAGEX result.
I even calculated the number of blanks and number of zeros on each column, could it be a difference on the denominator (so, a division by a diferente number of rows), but they are equal on each row.
I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged...
I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25.
The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement.
?confused?
Here's the original query.
accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): '
SELECT name, country_cd from weather_station where platformId=&&platformId;
SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS
FROM daily
WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime
GROUP BY to_char(datetime,'MM')
ORDER BY to_char(datetime,'MM');
with a result of:
NAME_________________CO
-------------------- --
OFFUTT AFB___________US
MO______MAX_T _____AVG_T__MIN_TEMTP_TOTAL_YEARS
-- ---------- ---------- ---------- -----------
01_________21______-5.31________-30__________60
02_________26______-2.19______-28.3__________61
03_______31.1_______3.61______-26.1__________60
04_______35.6______11.07______-12.2__________60
05_______37.2_______17.2_______-3.3__________60
06_______41.1______22.44__________5__________60
07_______43.3______24.92________7.2__________60
08_______40.6______23.71________5.6__________60
09_________40______18.84_______-2.2__________59
10_______34.4_______12.5_______-8.9__________59
11_________29_______4.13______-23.9__________60
12_________21______-2.52______-28.3__________60
-- Initialize Control Mechanism
DECLARE@Drive TINYINT,
@SQL VARCHAR(100)
SET@Drive = 97
-- Setup Staging Area
DECLARE@Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)
WHILE @Drive <= 122
BEGIN
SET@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT@Drives
(
Info
)
EXEC(@SQL)
UPDATE@Drives
SETDrive = CHAR(@Drive)
WHEREDrive IS NULL
SET@Drive = @Drive + 1
END
-- Show the expected output
SELECTDrive,
SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes,
SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes,
SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes
FROM(
SELECTDrive,
Info
FROM@Drives
WHEREInfo LIKE 'Total # of %'
) AS d
GROUP BYDrive
ORDER BYDrive
E 12°55'05.25"
N 56°04'39.16"
Hello,
I am trying to setup a test cluster and am having an issue. When I try to create the resource of a physical disk it takes both the drive e: and drive q: and doesn't seperate them into two physical disks as resources. This means when I try to associate the quorum disk it links the to physcial disk resource of drive e and q. Then when I try to install SQL2k5 I get the warning about installing SQL on the quorum disk. Am I missing something? Is there a way to seperate e and q onto two physical disk resources so I can specifically associate the quorum to q and the sql to e or should I be setting the quorum disk to a majority node set? Thanks in advance.
John
Hello,
this is my configuration :
1) 3 disks in RAID5 that hold the SQL data
2) 1 disk in RAID0 that holds the only paging file.
What will happen to the SQL data (DB) when the disk that holds the paging file crashes?
Kindest regards,
Luc.
I have a SQL 7.0 environment with 5 servers running SP1. I use an NT4.0 workstation with a copy of 7.0 installed on it as my workstation. When I run performance monitor with SQL 7.0 not running on my workstation, I don't see the SQL Server counters. When I have my local SQL 7 copy running on my workstation, they show up, but only when I have my workstation selected for monitoring; when I select another server, they don't show up. Anyone have a suggestion or work-around for this? Makes it kinda hard to monitor my servers remotely...
View 2 Replies View RelatedI have a sql6.5 sp4 on nt sp4, the counters for sqlserver are not listed in performance monitor when I use [start][programs][administrative tools][performance monitor] and choose the sqlserver machine. I also get flat lines on the counters that are built in to sqlserver performance monitor and can't add any counters, as none are available. It should not be flat lined as there are 200 users on the system. How do I get the sqlserver counters in perfmon? If a reinstall is necessary, should it be NT or SQL?
View 2 Replies View RelatedI'm running a % Processor Time on _Total (all processors) on my sql server. In perfmon, the graph of the processor will be going along, stop and then continue on, leaving gaps in the line on the graph. I do get an occasional message telling me there was a problem with the "sampling."
I'm wondering if the stop/start behavior is an indicator of some type of performance challenge. These "gaps" in the processor line do correspond to decreased performance but I can't correlate them to anything. If I look at Current Activity/Process info it doesn't look like anything unusual is going on.
Any ideas?
Hi
I have to give a report out stating the Min,Avg & max values of the memory utilization .
Now when I run the perfmon it shows me the values , but these are dynamic , I have to get theses values at the end of 8 hours of the shift ....
How can that be done , does anyone have any link to some tutorials or some explanation plz
Thanks
I am not showing any sql objects when trying to monitor my sql server through perfmon. This is the situation when running NT perfmon or SQL perfmon locally on the server or from a workstation through the network. I have found some tech net articles but they all say that the ojects should exist locally on the server. Any ideas?
Thanks David
Hi Experts,
Below are some counters from perfmon. Can anybody tell me what should be the acceptable figures for each count?
MemoryAvailable MBytes
MemoryPages/sec
Network Interface(Broadcom NetXtreme Gigabit Ethernet)Bytes Total/sec
Paging File(??D:pagefile.sys)\% Usage
PhysicalDisk(2 H:)Avg. Disk Queue Length
PhysicalDisk(2 H:)Disk Reads/sec
PhysicalDisk(2 H:)Disk Writes/sec
PhysicalDisk(5 F:)Avg. Disk Queue Length
PhysicalDisk(5 F:)Disk Reads/sec
PhysicalDisk(5 F:)Disk Writes/sec
Processor(_Total)\% Processor Time
SQLServer:Access MethodsFull Scans/sec
SQLServer:Buffer ManagerBuffer cache hit ratio
SQLServer:Databases(_Total)Transactions/sec
SQLServer:General StatisticsUser Connections
SQLServer:Locks(_Total)Average Wait Time (ms)
SQLServer:Locks(_Total)Number of Deadlocks/sec
SystemContext Switches/sec
SystemProcessor Queue Length
Thanks
Sachin
Don't sit back because of failure. It will come back to check if you still available. -- Binu
Are reports you can generate when obtaining data from a perfmon log based on an average or cumulative data. If on an average how is the average calculated?
THanks
At my company we recently needed to reload SQL 7 on one of our production servers. We then loaded SQL SP2 on it. Later we realized that none of the perfmon counters are showing up for SQL. I tried the whole unlodctr and lodctr routine but it didn't help. Anyone have any suggestions on how to fix this? Any help would be much appreciated.
Best,
John
I am working on a machine with SQL server 6.5 that is missing the SQL Server performance monitor counters that link in with NT`s performance monitor. The performance monitor itself seems to function ok. But I can`t check SQL server`s performance using the built in objects.
I`m told, a few months ago, this same problem was occuring. At that time, they did a reinstall of SQL and the counters remained missing. So they rebuilt the server from scratch, starting with a new OS install (NT server 4.0). The counters were there after the rebuild.
About that time, they were also encountering database corruptions (page allocation problems), that went away after the rebuild.
Now, the performance monitor counters for SQL have mysteriously disappeared again. Also, data corruption errors are beginning to show up.
I`ve searched MS knowledgebase and found some suggestions. I`ve tried re-registering using the commandline option of "RegistryRebuild=Yes" on setup and I`ve also checked the permissions on the registry keys. Nothing has helped so far.
Has anyone encountered this problem before and is there anything to do besides rebuild the computer? I don`t want to bother rebuilding if it`s just going to fail in a few months anyway.
Some other characteristics about the system. It`s a huge database (9Gb) and there is disk compression on the data device. I am now in the process of removing compression, because I`m sure it can`t be helping SQL.
Thanks in advance,
Jamie Birger
Ok, I'm really stumped on this one and I'm hoping someone can shed some light on this. I was using PerfMon to monitor SQL Recompilations (SQL Re-Compilations/Sec counter), and found that there were small spikes of about 3 or 4 re-compilations every minute. Probably nothing to lose sleep over, but I thought I would investigate further to see if I could eliminate the cause.
So I ran a profiler trace, which included the SP:Recompile Event, but it came up empty. Kind of odd, so I ran both PerfMon and Profiler concurrently to see if the event would show up in Profiler when the graph spiked in PerfMon. I saw the PerfMon graph spike, but Profiler didn't pick up anything. I double-checked my Profiler settings by running a test script that would cause a recomplile (interleaved DDL and DML statetments) and Profiler correctly picked up the event.
Does anyone have any idea why the two tools would report different results?
Thanks,
Roby2222
Does anyone have a completed script that will import Perfmon logs from csv format into an existing database that follows the SQL Log File Schema EG; CounterData, CounterDetails, DisplayToID tables accurately. I don't know enough T-SQL to get it right. Thanks for any input you may have.
View 5 Replies View RelatedI am training to be a DBA in a company running about 30 machines with MS SQL Server (2000 and 2005). Last week I went to a class where the instructor recommended establishing a performance baseline using windows performance monitor. He also advised to run perfmon remotely so as to not effect the performance.
What I am wondering is since I have so many different machines to baseline, can I run perfmon on one box, using a seperate counter log for each server? I would like to get a nice week-long baseline for each machine, but I also don't want to get bad data by running too many logs at the same time.
My plan is to do a small set of counters for processor, memory, disk, and the SQL server instance(about 10 counters total).
If anyone has experience in this area, I would appreciate any advice that you might have.
Thanks,
Matt
Hi all,
Ok here goes,
I have a three tier system using SQL server 2000, we are currently experiencing IO bottle necks on our SCSI Raid 10 array, which holds the Data and the logs in separate partitions.
So my options as I understand it are:
Get Enterprise edition
or
Get another physical raid 10 array and separate the logs and data i.e. data on one array and logs on the other array.
I would like to try the latter but I am totally unsure how much difference this will make or whether it will make any difference at all.
Does anyone know how much performance increase I will get from using two arrays as opposed to one?
Any other advice on this scenario would be greatly appreciated.
Thanks
My question is, My perfmon counters reads anywhere between 0 and 1200 with the average being around 250 faults/second.
My concern is my memory max size being too large, I have 4gb ram, SQL reports usage at 2.9 GB, my max mem size is 3.9 gb, should I maybe set my SQL server to use a fixed memory size of 2.9 gb?
Thanks in advance
Pete Karhatsu
Copied from SWYNKs' article:
Process: Page Faults/sec
If this value is greater then 0 then the SQL Server process is producing soft page faults and as a result CPU overhead. Try setting the working set size value to be as close to the SQL Server's memory allocation.
Recently, using PERFMON, I've been rather dismayed to find that ourapplication is averaging 3 - 4 lock timeouts per second, andfrequently has extended periods of several minutes where this figurereaches the hundreds.Average LockWaits/sec are less than 0.05, and TableLockEscalations/secare less than 0.5These last two seem very good to me, and as a result I wouldintuitively expect a LockTimeout figure of near-zero.Can anyone suggest why the measured LockTimeout value might be so high?Is the measured value actually considered "high" at all? Doubtlessthis depends on a number of things (transaction rate, number of usersetc), but a rule-of-thumb opinion would be welcomed.
View 4 Replies View RelatedHi,
Can anybody explain to me what's going on with my Target memory and Total memory in Perfmon?
Last week, before I upgraded my servers memory, they were both almost the same, at around 24 on the graph. Target was just fractionally above total, but there was almost no space between the two.
Then I doubled my servers memory to 4GB and expected to see total go way up and target stay the same. However, target went up to 72, and total came down to 16. When I looked this morning, target is now around 47 and total is 25.
I guess I expected these numbers to fluctuate, but not as much as this, and also why is there now such a big difference between target and total?
Thanks
Colin
I have a SQL Server 2000 cluster running on x64 OS. I found the threads in the forum to run perfmon locally by using the x86 version of perfmon (mmc /32 perfmon). However, I cannot run a perfmon remotely from another machine and see the SQL Server perfmon data on any of the nodes in the cluster. The remote perfmon picks up all of the other perfmon variables but no SQL.
I found another thread where somebody asked this question but it wasn't answered. Thanks in advance.
Mark
Im backing up to a network directory thats actually a mount point on a different server.My backup was slower than usual so i opened up perfmon to have a look.
When selecting the mount point from the Logical Disks section in perfmon i can see that writes/sec & write bytes/sec both show zero for a long period of time, even though the backup percent complete is increasing.Then all of a sudden the writes to the network share jump massively.
Is there some caching mechanism for backups in sql where during a backup data is only flushed to the disk periodically during backup?
Hi,
I tried to backup the master key by the following syntax :
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'
BACKUP MASTER KEY TO FILE = 'c: empmaster' ENCRYPTION BY PASSWORD = 'mypassword'
but it failed and i got the following message:
Cannot write into file 'c: empmaster'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
NB: I am using the "sa" user to execute this command.
I know that we have a security permission issue , but where and how ?
Regards,
Tarek Ghazali
SQL Server MVP
Hi
I'm using a gridview to show a list of high scores in a c#.net page. The position of the score is not stored in the database, just the score, name and id.
In the gridview however I would like to indicate the position of the scores, the SQL statement sorts the records by ascending scores so the are already in the correct order.
Does anybody know how to include a counter in the SQL statement that I can refer to vai the gridview so that it is able to display it as the position?
Thanks in advance for any help!