July 28, 2010 at 9:18 am
Hi friends
I have collected some perfmon counters related to disk IO for 30 min, Also i have calculated average value of each column mentioned in the first row of xlsx sheet attached here,
also find the disk details here,
There are 3 disks, also each one is RAID 1
DiskNo. DrivesProgram
1 C OS
D Tempdb Files
2 E SQLDATA
3 F SQLLOG
can anybody suggest what are the ideal values for each counters ?
also give ur opinions.
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
July 29, 2010 at 10:02 pm
hi friends
do u want any more information?
plz need ur suggestions !!!!
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
July 29, 2010 at 10:25 pm
The counters supplied are for the Physical Disk (_Total) counters, so it's impossible to say much more than "your disks are being hammered by writes". But, it's impossible to tell which disk.
I'd like to see the individual Physical Disk counters, the Buffer Page Life Expectancy (how long pages stay in memory), the amount of memory, and results of the sys.dm_os_virtualfilestats DMV (database file activity, at least a starting snapshot, and an ending snapshot).
Disk RAID configurations would also be useful.
July 30, 2010 at 7:04 am
Ideal values are those where you are having acceptable performance. Anything else is useless. 🙂 For some systems 5 ms per read/write is too slow, for others 100 ms per is just fine.
Typically mentioned values are 5-10 ms per read/write for OLTP data, 2-5 ms per tlog write. 10-20ms per OLAP read.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 1, 2010 at 10:08 pm
Thanx for Reply
But i need to check disk io performance, can u plz suggest from where i should start?
also in my application inserts are getting slower(only when i start another Application(let's name it App2))
we reviewed all the code but couldn't find any problem with that ,only thing i doubt is, due to excessive writes and reads those inserts are getting slower.
in app2 data is read from App1 Database tables with nolock clause. Still inserts are getting slower in the same table.
can u suggest how should i begin to troubleshoot this issue?
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
August 2, 2010 at 3:47 am
sanketahir1985 (8/1/2010)
Thanx for ReplyBut i need to check disk io performance, can u plz suggest from where i should start?
Avg sec/read, avg sec/write and % idle time per disk.
What's your physical disk layout (what files on what disks? Which are heavily used)?
Also check the sys.dm_io_virtual_file_stats DMV
[/b]also in my application inserts are getting slower(only when i start another Application(let's name it App2))
we reviewed all the code but couldn't find any problem with that ,only thing i doubt is, due to excessive writes and reads those inserts are getting slower.
Check your indexing. Find the queries that are doing lots of reads and writes and optimise them. Find what the inserts are waiting for.
in app2 data is read from App1 Database tables with nolock clause. Still inserts are getting slower in the same table.
Nolock? You and your users happy with the side effects of that? See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2010 at 10:18 pm
Hi Gila,
As of now no one has complained about nolock from User side since the SPs in which nolock is used are getting executed after every 3 seconds which reads data from Omnet_App1,Omnet_App5,Omnet_App15 Dbs, after processing data,it insert this data to VWAP & OnlineMTMUAT database.
Attached is the IO statistics taken from Prformance dashboard report.
there are 3 disks RAID-1
Disk 1- Tempdb files(8 files)
Disk 2- SQLDATA(All databases' Mdf files)
Disk3-SQLLOG (Log files)
can u suggests whats going wrong ?,is there any IO issues & how did u come to know that?
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
August 2, 2010 at 11:39 pm
TempDB is accounting for 70% of your read and 70% of your write activity. Where is the TempDB LDF file located? Is it on the volume with the other TempDB files, on the volume with the other transaction log files, or elsewhere?
How much memory do you have? Given the amount of read IO on your TempDB files, it sounds as if you don't quite have enough memory to keep the TempDB pages in memory, and have to then read them back from disk. Again, what is the value of the Page Life Expectancy counter? Memory will reduce the amount of read I/O, which will reduce the contention for the write I/O.
If the TempDB LDF file is with the other TempDB files, it may be worth moving it to the same volume as the other LDF files.
Finally, your queries might have a lot of sorting operations. These may be helped by better indexes, but these indexes may not show up as a "missing" index. You do have a number of missing indexes in your attached report - it might be worth investigating those. Alternatively, a code change may assist with lessening the need for TempDB activity.
August 2, 2010 at 11:56 pm
Locations of tempdb files
namefileidfilename
tempdev1D:\TempDB\tempdb.mdf
templog2C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf
tempdb23D:\TempDB\tempdb2.mdf
tempdb34D:\TempDB\tempdb3.mdf
tempdb45D:\TempDB\tempdb4.mdf
tempdb56D:\TempDB\tempdb5.mdf
tempdb67D:\TempDB\tempdb6.mdf
tempdb78D:\TempDB\tempdb7.mdf
tempdb89D:\TempDB\tempdb8.mdf
C:\ and D:\ drives are on same disk
RAM -16GB (10GB dedicated to SQL)
CPU- 8 Cores,16 threads
but why tempdb will cause insert operation to get slower in Omnet_app1, Omnet_app5, Omnet_app15
As location for tempdb and these 3 databases are on different disks.Inserts are plain insert statements.
Also if i have to find out blocking on that particular table how will i achieve it(i want to record it)?
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
August 2, 2010 at 11:59 pm
Additional info
SQL server 2005 SP3 service satrted with domain account "domain\lqss"
Lock pages in memory enabled (with domain\lqss)
Maxserver memory 10gb
min server mem 4gb
AWE enabled
Max degree of parl. 2
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
August 3, 2010 at 8:07 am
sanketahir1985 (8/2/2010)
Locations of tempdb filesnamefileidfilename
tempdev1D:\TempDB\tempdb.mdf
templog2C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf
tempdb23D:\TempDB\tempdb2.mdf
tempdb34D:\TempDB\tempdb3.mdf
tempdb45D:\TempDB\tempdb4.mdf
tempdb56D:\TempDB\tempdb5.mdf
tempdb67D:\TempDB\tempdb6.mdf
tempdb78D:\TempDB\tempdb7.mdf
tempdb89D:\TempDB\tempdb8.mdf
C:\ and D:\ drives are on same disk
RAM -16GB (10GB dedicated to SQL)
CPU- 8 Cores,16 threads
but why tempdb will cause insert operation to get slower in Omnet_app1, Omnet_app5, Omnet_app15
As location for tempdb and these 3 databases are on different disks.Inserts are plain insert statements.
Also if i have to find out blocking on that particular table how will i achieve it(i want to record it)?
Here is where a little knowledge is a dangerous thing. You have probably heard/read that you need to create one tempdb data file per CPU. In this case that is HURTING performance since it is all on the same spindle (which is same spindle as boot disk). Writing/reading tempdb now causes massive disk/head movements to get to each of those files. Drop tempdb back to 1 or perhaps 2 files (2 only if you are using a LOT of temp objects - lot being 10s to 100s or more per second).
I would also check for OS level file fragmentation. I have seen clients with hundreds of thousands of file fragments for sql server data/log files, and a defrag operation resulted in significant performance increase.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 3, 2010 at 9:29 pm
thanx Kevin
But i took that decision of splitting tempdb in multiple files is becoz, i was getting bufferlatch wait type
also it was indicating PAGELATCH_EX,PAGELATCH_UP also resource_Type was 2:........ and all the queries were blocking each other,
and after doing this that issue is resolved up to measurable extent. still i m getting the same wait type but not frequently (hardly i used to get in trading hours).
My questions are
1> How will that affect insertion of records in Omnet_App1,Omnet_App5 and Omnet_App15
as simple insert statements are fired on each database (nothing to do with tempdb)?
2>How to drop tempdb back to 1 or 2 files?
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
August 4, 2010 at 12:05 pm
sanketahir1985 (8/3/2010)
thanx KevinBut i took that decision of splitting tempdb in multiple files is becoz, i was getting bufferlatch wait type
also it was indicating PAGELATCH_EX,PAGELATCH_UP also resource_Type was 2:........ and all the queries were blocking each other,
and after doing this that issue is resolved up to measurable extent. still i m getting the same wait type but not frequently (hardly i used to get in trading hours).
My questions are
1> How will that affect insertion of records in Omnet_App1,Omnet_App5 and Omnet_App15
as simple insert statements are fired on each database (nothing to do with tempdb)?
2>How to drop tempdb back to 1 or 2 files?
You REALLY need to get a better IO subsystem - end of story. If you are doing that much activity you simply cannot run on a cheap (and likely misconfigured) system.
Having said that, try to find a happy medium between head thrashing from too many tempdb files and avoiding SGAM blocking. Maybe 2 to 4 files will work instead of 8.
You cut back to 2 files the same way you went up to 8, by using ALTER DATABASE commands. This will require a sql server bounce.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply