tag:blogger.com,1999:blog-158104178192097877.post2086272670084742113..comments2023-09-09T22:31:24.624+08:00Comments on Dirty Words: SharePoint and SQL Server with Greg Low at PSPUGMichael Haneshttp://www.blogger.com/profile/01877569030107816208noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-158104178192097877.post-67688548587372415972009-12-07T11:34:45.518+11:002009-12-07T11:34:45.518+11:00@Anonymous - Thanks for the additional points. Mir...@Anonymous - Thanks for the additional points. Mirroring was certainly discussed in the context of HA during the presentation but, if memory serves, I also put a question to Greg at the end of the presentation about the differences between clustering and mirroring in terms of performance; the fairly generic response was to favour mirroring for that scenario but whether this holds true in the skewed reality which is SharePoint seems to be up for debate. I've noticed some (dated?) technet documentation indicating "MOSS 2007 does not support the use of SQL Server database mirroring" [http://technet.microsoft.com/en-us/library/cc262910.aspx].<br /><br />I agree with your other points and the Planning and Monitoring SQL Server Storage for SharePoint link in my previous comment clarifies the "one file per cpu" remark: "The number of *tempdb* data files should equal the number of core CPUs".Michael Haneshttps://www.blogger.com/profile/01877569030107816208noreply@blogger.comtag:blogger.com,1999:blog-158104178192097877.post-18305742618666619332009-12-07T00:06:33.498+11:002009-12-07T00:06:33.498+11:00yes, i was at the same session and i think the pro...yes, i was at the same session and i think the problem is the paraphrasing of what Greg said. For example, with the one file per CPU, he was talking about the standard recommendations on tempdb, not on other database files.<br />there was no suggestion that mirroring would improve performance. comments were actually about sharepoint not supporting mirroring properly.<br />he also didn't recommend avoiding multiple hbas. on the contrary, he was talking about using tools like sqliosim to shake out i/o subsystem problems such as multi-path i/o problems before deploying sql server into production.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-158104178192097877.post-35295609492110337492009-09-05T00:39:00.491+08:002009-09-05T00:39:00.491+08:00Thanks for you comments anonymous--as I mention in...Thanks for you comments anonymous--as I mention in the original post, I'm not a DBA and am paraphrasing (perhaps badly) from Greg's generalised presentation. Critical evaluation of everything on the internet is a must and I generally gather information by cross-referencing multiple sources (much of this information leads to one or more blog posts); this post was an exception to that rule although I have seen many of Greg's recommendations echoed by other repuatable sources (like MS). See <a href="http://technet.microsoft.com/en-us/library/cc262731.aspx" rel="nofollow">Database Maintenance for SharePoint</a>, <a href="http://technet.microsoft.com/en-au/library/cc966412.aspx" rel="nofollow">SQL Server Best Practices</a>, and <a href="http://go.microsoft.com/fwlink/?LinkID=105623&clcid=0x409" rel="nofollow">Planning and Monitoring SQL Server Storage for SharePoint</a>.<br /><br />Finally, for those interested, <a href="http://www.sqldownunder.com/Portals/0/SolidQ%20-%20SQLServerForTheSharePointAdministrator.pptx" rel="nofollow">here's</a> the slide deck Greg presented.<br /><br />SharePoint is a difficult beast spanning many systems and areas of niche expertise; I've not been fortunate enough to work with a top notch DBA in a long time so being a pseudo "DBA" is simply one of the many hats I have to wear every day. My advice is to always consider the environment, consider the information and resources you have available (perhaps a really good DBA sitting down the hall?), do a good job, and learn what you can from the experience to do a better job next time!<br /><br />Ps. Having my own blog means I can worship whoever I want to ;-)Michael Haneshttps://www.blogger.com/profile/01877569030107816208noreply@blogger.comtag:blogger.com,1999:blog-158104178192097877.post-74962556187156828422009-08-28T16:31:29.494+08:002009-08-28T16:31:29.494+08:00I don't know where you got some of this advice...I don't know where you got some of this advice, but apart from the obvious ones that every DBA knows about, most of the other bits are incorrect or can cause you a lot of problems. Take the HBA advice "Multiple HBAs can lead to write ordering and disk subsystem issues"... not only is this incorrect, it can also cause availability problems if you 1) use only one HBA and it fails, or 2) have a dual port HBA that uses the same bus and circuitry which causes throughput issues. The reality of the situation is that multiple HBA's are recommended in MOST high availability situations providing they support 1) Storport driver model and 2) MPIO Device Specific Modules (DSM's). These are the requirement for any HBA to be certified for Windows 2003 and above anyway. Multiple HBAs can improve availability and performance through load balancing, as well as redundancy in the event of a fault. Just because Greg is an MVP and a Regional Director, it doesn't mean that everything he says is correct... so take care and evaluate eveything that you are told first, that advice in itself is worth more than all the advice you got on your SQL training. IMHO, most SQL MVP's work for either SolidQ or SQLSkills, so you must question whether or not they have any real world experience.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-158104178192097877.post-27519686841855849552009-08-28T15:52:59.306+08:002009-08-28T15:52:59.306+08:00I am sorry to shatter your idolisation of your God...I am sorry to shatter your idolisation of your God but...<br /><br />Firstly, there is no peformance gains to be had by creating one file per CPU on SQL Server 2005/2008 as SQL will quite happily use as many threads as it can to maximise your performance. Unless you are using multiple spindles, but then you'd still be better off creating an appropriate RAID array across the spindles instead. Can you imagine manage managing a database with 32 files because you have 32 cores?<br /><br />Secondly, database mirroring will not boost performance, it might actually degrade it. It is designed for high-availability purposes, not as a performance boost. You will degrade performance, whether it's asynchronous or synchronous, as there is more resource consumption (including 2 threads per database). Synchronous mirroring will degrade your OLTP peformance on the primary.<br /><br />Thirdly, I would be concerned with using a fill factor of 70% as all your data in the database would consume 30% more space and crucially memory. which will impact performance! The "correct setting" would require further investigation. It is dangerous to throw up such blanket recommendations without explaining the consequences.<br /><br />Remember:<br />"You shall not make for yourself an idol, whether in the form of anything that is in heaven above, or that is on the earth beneath, or that is in the water under the earth."Anonymousnoreply@blogger.com