March 27, 2009 at 8:51 am
It may be worth asking your server guys to send server logs to the vendor for analysis also. I have seen this happen before due to a bad firmware version on a server....
March 30, 2009 at 4:37 pm
Markus (3/27/2009)
Tuning indexes: Last Thursday night we had an outage that I rebuilt ALL of the indexes and did a 100% update stats as well. I typically rebuild some of the tables indexes every 3-4 months.
What about reorganization? With data change indexes naturally will become a bit fragmented; minor fragmentation can be dealt with just simple reorganization. I re-organize all indexes on weekly.
Memory: There is 4 gig of memory in the server as it is 32bit and SQL Server is using 1.8 gig.
4GB? Do you have any other applications running on server if not maybe you want to look at enabling AWE to take advantage of additional memory. So your SQL Server can use from 3GB and leave 1GB for your OS. Check out Memory counters to see if the Page Life Exp & Cache Hit Ratio with in acceptable parameters.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 1, 2009 at 2:04 am
Hi All,
Came across a very interesting article that might help you.
http://www.computationpress.com/images/sql_disk_001_10a.pdf
Regards
IC
April 2, 2009 at 10:12 am
The reason you are seeing this issue on new equipment is simply that SQL is outperforming the disk IO. The IO errors you are seeing are informational in nature stating that the system is waiting on disk. This warning will appear primarily during high IO functions like backup, integrity checks, etc. If it's happening during normal business use than you may have drives/databases that were not setup optimally on the new server and very extended timeouts can actually indicate impending database critical issues.
Per Microsoft's tuning best practices disk/database file configuration is very critical. Tempdb should always be on it's own set of disks as well as the log files. Each type of file with a different raid setup for best performance. High Performance Data Warehouse with SQL Server 2005 whitepaper is recommended as well for useful information on setting SQL up for best IO prformance.
If your server is using SAN storage space you will have to work closely with your SAN admin to determine the best way to "carve" out disk space for SQL. Unfortunately the norm for many SAN vendors is to configure all the drive space as raid 5 which isn't optimal for log files.
April 2, 2009 at 10:44 am
I understand your point. However, this new back end storage is 5 times faster than the old array. When we are experiencing these issues when he runs statistics on the array it shows responses back from request in under 5 ms. The entire thing runs perfectly well without any problems for about 4-6 weeks and then we hit this wall. A reboot will fix the issue and then we see it once again in 4-6 weeks. I have opened a case with MSFT and given then two different PSSDIAGs to digest. If it were a back end storage issue I would expect we would see this issue before 4-6 weeks.
April 3, 2009 at 12:54 pm
Sorry to stick me head in between but:
1 Tempdb file per cpu-core
I have put tempdb on the OS drive (only thing running on the os = Sql server ( Raid 1 on 15k drives)
Lofile on seperate spindels (raid 1 on 15K drives)
Database file on other separate spindel (Raid 5 on 15K drives)
I have a 4 cpu-code server with 4Gb Ram (AWE NOT Enabled, just mention this)
So, you are saying i should have 4 Tempdb files,
Could you also point out to me where i should put them, can i put all 4 of those files then on the OS drives or should i mix them by putting them also on teh other spindels next to my logfile and data files .?
Any suggestion on this would be appreciated,
Wkr,
Eddy
Nb: sorry again for mixing in 😎
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply