server performance - 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

SQL Server 2008 Forums

SQL Server Administration (2008)

server performance

Author

Topic

lappin
Posting Yak Master

182 Posts

Posted - 2011-06-10 : 10:18:03

I have a server which has been moved into a virtual server. The performance is worse than old lower spec physical box. I'm trying to locate bottle-neck. Can any-one tell me what would be a normal range for disk I/O speed for a new physical server with good specs under heavy load, e.g. doing updates of large table

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-06-10 : 10:47:36

First make sure that problem is just because of low disk I/O.
To ckeck disk health, if "Avg. Disk Sec/Read" counter value is less then .008 seconds then its excellent, if less then .020 seconds then good BUT if value is greater then .020 seconds then its really performing poorly.

--------------------------
http://connectsql.blogspot.com/

Sanjitsarker
Starting Member

1 Post

Posted - 2011-06-12 : 08:10:00

How to move datafile from one drive to another drive by taking db offline in sql server 2008

Sanjit Sarker

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2011-06-12 : 13:54:52

lappin, you must take metrics for both the physical server and virtual server. Within the virtual server, also measure the hypervisor metrics while you're doing testing. If you haven't got access to the hypervisor metrics , than speak to the administrator.
Is the problem with reads or writes ?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-06-13 : 03:09:44

select DB_NAME(database_id) DB_NAME, di.file_id,df.name,io_stall_read_ms ,num_of_reads
,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
,io_stall_write_ms,num_of_writes
,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
,io_stall_read_ms + io_stall_write_ms as io_stalls
,num_of_reads + num_of_writes as total_io
,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null) di inner join sys.database_files df on df.file_id = di.file_id
where DB_NAME(database_id) = 'your database name'
order by avg_io_stall_ms desc

check the Files Delays with this query of your required database

total File Groups or Files in a group ?
total Drives ? physical drive not partition ?
Raid Level ?
total Processors or Core ?
total RAM ?
Tempdb location or Drive separate?

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

lappin
Posting Yak Master

182 Posts

Posted - 2011-06-13 : 10:34:10

For Data File- avg stalls: read :6.8, Write:10, IO:7.4. Log files are a bit faster.
Avg. Disk Sec/Read (and also Write) are 0.004.

Page Reads /Sec -low use: 0.93, heavy load: 32
Page Faults/Sec -low use: 100, heavy load: 5417
Transition Faults: low use: 22, heavy load: 194
Pages/sec: low use: 1.2, heavy load: 157

The VM file is stored on a SAN - so is there any point in having seperate drives since they are all really a single file anyway?

6GB RAM - on Server, in SQL Server minimum 0, max 4GB. 2CPUs

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2011-06-13 : 15:03:30

lappin, what is the guest to host ratio?
How much physical memory on host relative to total memory allocated to guests?
Your Data File - avg stalls look OK , and Avg.Disk Sec/Rad
My approah with virtualized is to go from the bottom up - Hardware (LUN,Disk,Storage Controller( Cache),Fibre Channel HBA
Hypervisor (resource management, other guests) - Very important!
CPU Cores
OS – Windows
Cache – SQL Server Read Ahead Rate
SQL server
Table Indexes
Query

Is your VM definately a single file or is it configured with Raw Device?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

lappin
Posting Yak Master

182 Posts

Posted - 2011-06-14 : 04:50:29

I'm afraid I don't handle the VMWare side of things but this is a new system with lots of spare capacity. I'm told the VM is a single file. The Page Faults/Sec -low use: 100, heavy load: 5417 - seemed high to me; but is this just something which goes with being in a VM?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources