May 13, 2008 at 8:04 am
dsc (5/13/2008)
Hello everyone,we have one heavy busy database. I was able to prove that we could do 7 times the current load. But then the SAN is very busy at checkpoints. Is there any way other than using a manual checkpointing to lessen the SAN stress?
1) More frequent manual checkpointing may help
2) are you using RAID 5 for data drive? If so, a different RAID level could allow for more efficient writing of the dirty pages to disk
3) see here in BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/ccdfc689-ad4e-44c0-83f7-0f2cfcfb6406.htm. You can specify a longer duration for the checkpoint call (manually) and that would allow sql server to use FEWER resources to complete the checkpoint.
4) What is your recovery model and recovery interval? There may be some things to do based on these settings.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 13, 2008 at 10:09 am
GilaMonster (5/12/2008)
Also corporate policy states all servers will use SAN storage.
The solution suddenly came to me after I re-read this sentence: Get the "pedestal" version of the server model you want instead of the "rack" version. The pedestal ones can usually take a dozen hard drives or more. Max those out and then sign up for the smallest possible SAN allocation and use it for My Documents.
May 13, 2008 at 2:31 pm
magarity kerns (5/13/2008)
GilaMonster (5/12/2008)
Also corporate policy states all servers will use SAN storage.The solution suddenly came to me after I re-read this sentence: Get the "pedestal" version of the server model you want instead of the "rack" version. The pedestal ones can usually take a dozen hard drives or more. Max those out and then sign up for the smallest possible SAN allocation and use it for My Documents.
Perhaps I should have said, policy is for servers to use SAN storage for everything except the C drive.
I don't want to get rid of the san. That's a battle I can't win, and I'm not even going to try. I just want configuration best practices for SQL server.
btw, what's the max distance for direct attached storage?
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
May 13, 2008 at 6:05 pm
I believe the distance limit for LVD SCSI is 25 meters. (http://www.interfacebus.com/Design_Connector_SCSI.html). not sure of other, newer technologies, but 25meters gives a LOT of room for drives to attach to!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 13, 2008 at 7:47 pm
GilaMonster (5/13/2008)
Perhaps I should have said, policy is for servers to use SAN storage for everything except the C drive.
Sorry it wasn't clear enough that I was just trying to contribute a little levity to the situation. You could still use my suggestion and comply with the letter of policy: mount all of the 10 or 12 internal drives as NTFS folders under drive C even though they're individually different RAID levels and everything, hah hah hah! (Note: this is humor since apparently you're having a rough time but least you can have an Evil Grin moment.)
May 13, 2008 at 11:52 pm
TheSQLGuru (5/13/2008)
I believe the distance limit for LVD SCSI is 25 meters. not sure of other, newer technologies, but 25meters gives a LOT of room for drives to attach to!
Cluster nodes are in different server rooms and separate from SAN. Server rooms are serviced by different networks hardware, different power sources, separate generators, etc
Not important for the original discussion.
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
May 14, 2008 at 1:30 am
TheSQLGuru (5/13/2008)
dsc (5/13/2008)
Hello everyone,we have one heavy busy database. I was able to prove that we could do 7 times the current load. But then the SAN is very busy at checkpoints. Is there any way other than using a manual checkpointing to lessen the SAN stress?
1) More frequent manual checkpointing may help
2) are you using RAID 5 for data drive? If so, a different RAID level could allow for more efficient writing of the dirty pages to disk
3) see here in BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/ccdfc689-ad4e-44c0-83f7-0f2cfcfb6406.htm. You can specify a longer duration for the checkpoint call (manually) and that would allow sql server to use FEWER resources to complete the checkpoint.
4) What is your recovery model and recovery interval? There may be some things to do based on these settings.
Thx for your reply. I read most of your recommendations before and was astound that there are only manual ways to control the checkpointing behaivor of the SQL Server.
It's just that one SQL Server Instance is able trash the entire SAN and that is not documented by microsoft. They say SQL Server adapt "automagically" to the possible IO throughput. But that's just not true.
We had lately very strange errors on different servers where the only possible cause could be the shared SAN ressources. But you'll be out of luck to prove this to or by the IT department.
It'll most end in answers like "It's not the SAN, it's it usage by applications". It's also almost impossible to get a meanigful overview of the current SAN workload.
May 14, 2008 at 2:15 am
Also keep in mind that with Windows 2003 you can use "mounted drives" to get over the 26 letter drive naming.
Each of these mounted drives can point to different luns, so different raid configurations.
A problem accompanied with this is that your disk usage information is only available at drive letter level.
That is another reason to invest in good san monitoring software.
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
May 14, 2008 at 2:54 am
Hi GilaMonster,
Good luck with this. It's worth fighting to get this right. I spec'd and worked on a SAN system almost from the beginning of the project. The system was going to process high volume workloads and the initial proposed setup was similar to your Storage Team's. Thankfully, I had a boss who took note of what I was saying and allowed me to have dedicated LUNs, Striped Mirrors (not RAID 5), Sector Alignment, etc, etc... I was also able to take some benchmark results using SQLIO to show IO and throughput with different settings. This was against our Partner company (the developers of the app) who had set up their SAN (all the same hardware spec, just actual setup was different) on the advice of the SAN Vendor. The systems were put in-place and pushed out to production.
All ran fine...initially. But as users and workloads ramped up our partners started experiencing performance issues. My boss started receiving reports of patches/fixes to the problems they were seeing and the fixes they had implemented. There was some major work carried out by our partners, SAN-wise. However, we didn't have to carry out such drastic work. Our performance was fine. My boss was very happy, as he didn't have to go cap-in-hand for more money for hardware, explain to the higher bosses why this was needed, and why it wasn't done right in the first place.
The only real downside to all this was that I inherited the nickname of Mr Spindles, as I took up virtually all the HDDs in the SAN. But this was a very small price to pay. 😀
SANs are expensive at the best of times. They're even more expensive if they cost the business due to poor performance - lost sales, damaged reputation, etc.
A couple of links I have (sorry if any are duplicated from others):
http://blogs.msdn.com/sqlcat/archive/2005/10/11/479887.aspx
http://support.microsoft.com/kb/895614
HTH
May 14, 2008 at 7:30 am
dsc (5/14/2008)
It'll most end in answers like "It's not the SAN, it's it usage by applications". It's also almost impossible to get a meanigful overview of the current SAN workload.
These perfmon counters can point to SAN performance as a possible bottleneck:
Avg Disk Sec/read and Avg Disk Sec/write.
See earlier postings on this thread.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 14, 2008 at 12:12 pm
Marios Philippopoulos (5/14/2008)
dsc (5/14/2008)
It'll most end in answers like "It's not the SAN, it's it usage by applications". It's also almost impossible to get a meanigful overview of the current SAN workload.
These perfmon counters can point to SAN performance as a possible bottleneck:
Avg Disk Sec/read and Avg Disk Sec/write.
See earlier postings on this thread.
I think the intent was to know which applications were causing what load. IIRC, some of the high-end SAN monitoring/management stacks can provide this down to the application (or at least server - which is usually good enough) level.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 14, 2008 at 3:17 pm
note that you can attach fc DAS, fc isn't only the domain of the network.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 14, 2008 at 3:18 pm
Actually i was just thinking that each time a thread gets going on storage and SANs it really generates a lot of interest making me more than certain that storage is becoming a major hreadache for DBAs.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 14, 2008 at 3:58 pm
colin Leversuch-Roberts (5/14/2008)
Actually i was just thinking that each time a thread gets going on storage and SANs it really generates a lot of interest making me more than certain that storage is becoming a major hreadache for DBAs.
Even worse is that most don't even KNOW it is a problem for them, nor how to deal with it when they do discover it, nor how to really PREVENT it from being a problem (assuming their SAN admins would get their collective heads out of their butts and accept DBA input for SQL Server IO config).
As I have said before - I haven't yet been to a client who did SQL Server storage (SAN or otherwise - but especially SAN) right.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 15, 2008 at 10:34 am
TheSQLGuru (5/14/2008)
colin Leversuch-Roberts (5/14/2008)
Actually i was just thinking that each time a thread gets going on storage and SANs it really generates a lot of interest making me more than certain that storage is becoming a major hreadache for DBAs.Even worse is that most don't even KNOW it is a problem for them, nor how to deal with it when they do discover it, nor how to really PREVENT it from being a problem (assuming their SAN admins would get their collective heads out of their butts and accept DBA input for SQL Server IO config).
As I have said before - I haven't yet been to a client who did SQL Server storage (SAN or otherwise - but especially SAN) right.
It amazes me that companies are willing to invest in the SAN technology, but not the monitoring software that comes with it, because of the price! It's as if people naively think the tool itself will solve all their problems with no tuning necessary. I've witnessed this all too often and still can't believe it!
Then there is the cultural divide between SAs and DBAs. SAs acquire the SAN as a way to ease their own administrative tasks, with DB performance barely on their radar. DBAs generally know little about the technology to offer much input. SAN monitoring software is usually not purchased along with the tool. Vendors are usually not helpful. It's a total mess.
I think the voluminous reaction to SAN postings is a reflection of the fact that DBAs feel the need to know more about a subject that's mostly cryptic to many of us.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 31 through 45 (of 57 total)
You must be logged in to reply to this topic. Login to reply