March 20, 2008 at 5:11 am
Repected all
We have HP SAN kind of environment for our sql databases.Still in peak hours we face the problems of slow performance and sometimes query gets timedout.Our peak hour starts at 4.00 p.m. as market closes.We are using the VRAID technology as DEPLOYED by HP.Generally microsoft suggests that for database perfoamcne point of view physical RAID should be deployed not the virtual raid.
Also the Log and Mdf file should be installed on physically different raid array like raid 1 or raid 10 as it;s random & sequential behaviour.
So what is your suggestion on Physical RAID & Virtual Raid(VRAID). Also the CPU utilization & ram ultilzation is not too much.
(VRAID has been deployed in SAN on 146GB * 11 hdd with fibre channel)
Please have your kind suggestion on this.
vrijesh
March 20, 2008 at 5:22 am
use some DBCC commands like DBCC UPDATEUSAGE, DBCC FREEPROCCACHE, DBCC DROPCLEANBUFFERS, DBCC CLEANTABLE, ...
for more information see SQL BOL.
March 20, 2008 at 6:48 am
I would suggest you run profiler during the peak period to see what queries are running long, or doing lots of IOs.
Once you've identified the slow running or resource hogging queries, you can work on optimising them. It could be that the code needs changing, it could mean the indexes need tuning.
Do you have lots of blocking duing the slow periods? Are the queries waiting on resources? If so, what resources? (check sys.dm_os_waiting_tasks)
DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS are not something you want to do on a busy server. It could easily make the server go from busy to dead. In general, don't run any DBCC on a production DB unless you know what it does and what the effects are.
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
March 20, 2008 at 8:19 am
Gail
Good advice on the use of DBCC in the production environment. Right on point, far to often I have seen the busy server turn dead and the DBA looking a little foolish. I tend to stay away from DBCCs during peak hours, in fact, I try to stay away from them at all times.
As for this issue, the profiler is probably the way to go, find the offending queries and then perform tuning. As for vRaid vs physical raid, I have always found physical to have better performance over anything created by software to perform a physical function.
Check you LUNS and spindles to make sure you have sufficient throughput. Make sure your data/indexes logs are spread out over the entire array. Is you TEMPDB on it's on drive? This is probably a resources issue and once tuned/optimized for the hardware environment, you will see improvement.
If you are sure everything is optimized, then it's time to go to management for better hardware or the ability to split the databases over a few more servers.
Oh, don't forget partitioning, especially you have large data sets.
Hope this helps
Marvin Dillard
Senior Consultant
Claraview Inc
March 20, 2008 at 12:40 pm
Partitioning is a good option, however, is a little more complicated to implement I will suggest using the profiler, move non-cluster indexes to their own file group and drive, and identify transactional objects and move them also to their own file group and drives. Finally check indexes for fragmentation and set an after hours job to re-build the indexes.:cool: and a final thing you may want to move the tempdb to its own drive.
"One Step at the Time"
March 20, 2008 at 11:29 pm
Get some performance counters running as well, it sounds likely that you're I/O bound (duh!). The question is whether you're overrunning your HBA(s?) or SAN. What kind of throughput in MB/Sec are you getting from the current configuration?
11 disks should be able to perform something on the order of 660 MB/Sec sustained read/write as a group ~60MB/Sec per drive+-, fast SCSI's typically "burst" as high as 120MB/Sec/drive but not under a random read/write load)... How many and what type of HBA's are you running (how many cards and ports per card)? 1Gb/Sec sounds really fast until you realize it's only ~125=0MB/Sec with 10-30% loss for protocol overhead...
Joe
March 21, 2008 at 5:37 am
Are you sure the problem is disk? I would recommend running perfmon and looking at disk queue length along w/ memory and processor. I know it sounds basic, but worth checking if you haven't already done it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply