June 3, 2002 at 5:29 am
We are having two sanbox each has 6 read/write heads.
we put tempdb datafiles 30 GB * 2 on first sanbox on raid 0+1.
we put tempdb logfiles 20 GB * 1 on second sanbox raid 0+1.
we put database 400 GB * 1 on second sanbox.
we put database logfiles 40 GB * 1 on second sanbox raid 0+1.
After this our processes are getting slowed then before
Earlier we had tempdb data and log file + database log file on one raid 0+1 and this was working fine.
I suspect this could be because of network because of both sanbox.
Any help????
All other things are same.
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
June 3, 2002 at 5:32 am
Anyway I started performance monitor with network interface: byte/total sec. Need help if any other counter to measure this?
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
June 3, 2002 at 5:59 am
I think you'd be better off putting the log files on the other array. 30g tempdb is pretty big by the way!
Andy
June 3, 2002 at 6:41 am
Is there is a negative side having 30*2 60 GB for TempDB?
Log files and data files are on the separate sanbox.
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
July 17, 2002 at 6:13 am
I had similar problems with one of our customers SQL 2K that had all its databases in SAN (Dell).
When you have the Tempdb and user databases & logs in the same set of devices or in a SAN there will be bandwidth choking, especially in the case of 100 MBS SAN connectivity.
I suggest to move the tempdb to an independent device (forget SAN storage for tempdb), normal internal 18 or 36 GB HDD , 1500 rpm, or to a group of drives configured in RAID 0.
More importantly as Andy commented, 30 GB is real too much for tempdb unless you really have DBs with tables of size in GBs. Its better to monitor the tempdb size and fix the size to 10% more than the peak value.
Karthik P
July 17, 2002 at 11:31 pm
Hi Karthik,
Well table sizes vary from 2 GB to 9 GB and as one stored procedure is accessing two three tables and putting them in temp tables and process the table and finally insert records in the origional table after truncating. max tempdb usage is still 15 GB but as we have four drives of raid 0+1 array in sanbox we have 72 GB(36*2) space available that's why Tempdb size is 60 GB total. Well do you realy think having extra space could degrade performance?
100 MBS SAN connectivity:
It's fibre switch connection between two sanbox.
The only thing I don't understand is how come network comes in to this picture?
Cheers,
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
July 29, 2002 at 3:59 am
Sorry about that '100MBS'. I mistyped it. It should have been '100MBps'. This is the bandwidth of some old FC HBA.
The news ones claim a bandwidth of 200 MBps, if full duplex is enabled.
I would suggest you monitor the SAN rather than the network counters.
July 29, 2002 at 4:08 am
The network is relevant because that is what you have. Each of those SAN devices is connected via a FC switch to your server(s). The speed of most HBA adapters is 100Mbps, the newer ones are full duplex at 200Mbps but there are not many switches/arrays that support that speed and you pay a price penalty as well. Having said that, I have a 100Mbps device using fibre over copper to a 160Gb 12 drive array and it is blindingly fast. The drives are RAID5, but even so, file copying and data access is faster than the internal SCSI drive system.
I'd check you have the right drivers for your HBA for a start - what make is it?
Simon
UK
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply