August 28, 2012 at 11:53 am
Additionally, you are using SATA disk which are not going to perform as well. I would be interested to know what the RAID configuration is still. As Lynn suggested it would be good to watch for other activity on those drives to see if there is contention during your processing. The SAN admin should be able to provide that information to you pretty easily. Bring a Mt Dew with you and it will go even better. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 28, 2012 at 12:11 pm
Thanks Guys. We will do the monitoring this weekend so we can see if we can spot contention. thanks!
Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
August 28, 2012 at 12:19 pm
David Benoit (8/28/2012)
Additionally, you are using SATA disk which are not going to perform as well. I would be interested to know what the RAID configuration is still. As Lynn suggested it would be good to watch for other activity on those drives to see if there is contention during your processing. The SAN admin should be able to provide that information to you pretty easily. Bring a Mt Dew with you and it will go even better. 🙂
If the Mt Dew is supposed to be a bribe, wouldn't work with me as I don't drink it. :w00t:
August 28, 2012 at 12:23 pm
Lynn Pettis (8/28/2012)
David Benoit (8/28/2012)
Additionally, you are using SATA disk which are not going to perform as well. I would be interested to know what the RAID configuration is still. As Lynn suggested it would be good to watch for other activity on those drives to see if there is contention during your processing. The SAN admin should be able to provide that information to you pretty easily. Bring a Mt Dew with you and it will go even better. 🙂If the Mt Dew is supposed to be a bribe, wouldn't work with me as I don't drink it. :w00t:
They are more into food. I promised a free lunch to whomever figures it out 1st!
Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
August 28, 2012 at 12:27 pm
As long as you got the bribe thing working you'll go far.
I didn't drink dew either Lynn but donuts, that was another thing. Had to forgo those now too since I went over 40. Can't shed them like I used to. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 28, 2012 at 12:37 pm
I'm easy going with either vodka or chocolate. If anybody ever needs plenty IOPS while visiting the Netherlands. Just that you know...
September 3, 2012 at 8:34 am
Any news after this weekends monitoring?
September 18, 2012 at 6:39 am
OK. here is what we have found.
We made multiple copies on the server of one of the exact databases hosted on different SAN aggregates and also local drives along-side one of the prod database.
backup database Mydatabase_DDrive to disk = N'NUL' with copy_only, stats = 10
and we also did this to the prod database.
we found when you run this against any db with no users logged in it was VERY fast, and about 1/4 - 1/8 the IO speed with normal activity as far as reads per second. keep in mind that normal activity was very light. the counters we looked at were:
Disk read Bytes/sec and Disk Reads/sec...physical disk.
so the thought is, since I know a backup does the following:
1.Force a database checkpoint and make a note of the log sequence number at this point. This flushes all updated-in-memory pages to disk before anything is read by the backup to help minimize the amount of work the recovery part of restore has to do.
2.Start reading from the data files in the database.
3.Stop reading from the data files and make a note of the log sequence number of the start of the oldest active transaction at that point (see my article "Understanding Logging and Recovery in SQL Server" for an explanation of these terms).
4.Read as much transaction log as is necessary.
Maybe it is step one with the memory flush that is causing this? Thoughts?
It is definitely not Hardware I believe since the test were also done with a copy of the database on the same exact disk... and during backing up the prod db there is little activity IO wise...
Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
September 18, 2012 at 2:20 pm
thoughts?
Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
September 19, 2012 at 9:44 am
I didn't read over the full thread.
Did you check your backup file folder isn't being compressed at windows level ??
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 19, 2012 at 9:50 am
backup database Mydatabase_DDrive to disk = N'NUL' with copy_only, stats = 10
not going to a real folder...for my tests
Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply