1 / 15

Tuning SQL Server for SharePoint Jump Start

Tuning SQL Server for SharePoint Jump Start. Bill Baer | Senior Product Marketing Manager, Microsoft Brian Alderman | Chief Executive Officer | MicroTechPoint. Course Topics. Module Agenda. SQL Server Instance Settings Maximum Memory (Per Instance) Default File Locations Collation

manasa
Download Presentation

Tuning SQL Server for SharePoint Jump Start

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Tuning SQL Server for SharePoint Jump Start Bill Baer | Senior Product Marketing Manager, Microsoft Brian Alderman | Chief Executive Officer | MicroTechPoint

  2. Course Topics

  3. Module Agenda • SQL Server Instance Settings • Maximum Memory (Per Instance) • Default File Locations • Collation • MAXDOP • Operating System Settings

  4. SQL Server Instance Settings

  5. Default Maximum Memory Setting

  6. SQL Server Memory Configuration • Maximum Memory Setting SQL Max Memory = TotalPhyMem - (NumOfSQLThreads * ThreadStackSize) - (1GB * CEILING(NumOfCores/4)) NumOfSQLThreads = 256 + (NumOfProcessors*- 4) * 8 ThreadStackSize = 2MB on x64 or 4 MB on 64-bit (IA64)(* If NumOfProcessors > 4, else 0)

  7. SQL Server Default File Locations Must Restart SQL Server Instance

  8. SQL Server Collation Setting

  9. Max Degree of Parallelism

  10. Modifying SQL Memory SettingsModifying Default DB Locations

  11. Operating System Settings

  12. Operating System Settings • NTFS Allocation Unit Size • 64K is optimal, 4K = 30% Performance Penalty • Use chkdsk <drive>Verify • Use Format to Configure: • Format <drive> /Q • /FS:NTFS /A:64K /V:<volume> /Y

  13. Verifying Operating System Settings

  14. Summary • Modify Memory Settings to Control Memory Usage by SQL Server • Change Default File Locations • Leave MAXDOP at 1 for SharePoint • Verify Operating System Set to 64KB Allocation Unit Size

More Related