November 16, 2011 at 1:06 pm
We have a production server where flat files are dropped on. The data from these flat files is loaded onto a production cluster from 3-5 AM. There seems to be a delay in the load process, which we didn't find enough as to help from DB perspective.
The server flat files are on, I learnt is a 64 bit server, running 32 bit OS, and hence not utilizing memory. The production DB cluster is running 64 bit Win 2003 R2 SP2 Standard Edition and SQL 2005 SP2 Std Edition as well. Hence there are messages at different intervals of the day "A significant part of sql server process memory has been paged out" And both the server, cluster are in the same datacenter
I only have access to the DB cluster. On the DB cluster. I didn't see any errors in the SQL event log, windows log. The drive that holds DB Data file, had a few high disk reads and high Avg Disk Queue length values. Since these are SAN drives, I was not sure if those numbers indicate a SAN issue.
(I found the disk reads higher than usual - Disk reads went to 194 at 4:18 AM, hit 836 at 4:32 AM, and 872 at 4:47 AM. I have Idera diagnostic manager set to warn if threshold exceeds 15 mins, and it only changed to 'OK' state at 8:19 AM, till then it remained in upwards of 500. Avg Disk queue length for the drive was on the same lines. Today's example - It reaches 11 at 3:26 AM, 83 at 4:37 AM, 60 at 4:51 AM, and around 12 from 5:20-9:00AM. But this happens everyday, even when the loads dont have any issues. So as far as the problem with data loads, Disk reads, queue length may not be a big deal. The SAN Admin didn't see much wrong)
The App team only reported this problem recently, and didn't have the problem previously. They also reported a timeout error from their side. Over the weekend, the reboot of cluster nodes seemed to help for two days, there were no issues, and the issue seems to be back today. So, not sure what changed or how much the above factors contributed. Starting to think of monitoring network counters, have the 'flat file' server also monitored. Please let me know if there are any suggestions.
November 16, 2011 at 1:30 pm
1- Confirm issue is related to the process or loading the flat files into the database.
2- Focus on the 3AM-5AM timeslot which appears to be the ETL window - everything else is not releavant so do not waste your energy.
3- Ask for application side logs showing starting/ending time for the offending processes - make your own evaluation of how performance has decreased - if it did at all.
4- On the same application side logs check data volume - be sure application team is comparing perfomance of comparable work loads.
5- Taking previous points into consideration identify the two or three processes that hurt the most, trace them - focus on wait events.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 17, 2011 at 7:04 am
Disk queue length is a completely meaningless measure on a SAN system. Some of the other measures are ok, but for details you're going to have to work with your SAN team. For some information on how best to do this, I'd suggest going over to Brent Ozar's blog. He has a ton of great info on how to diagnose SAN problems.
As to the issue, have you looked at tuning the load process? You're very focused on the hardware, but code is the problem most of the time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply