This week I wanted to call out some information around monitoring SQL from a Search perspective and provide some guidance around configuration of the SQL machine. There are many great articles out there that already document much of this information. So I am attempting to consolidate this information and provide you with my “reading list.” I’ll also summarize some of this data in the key areas you should focus on when working with the SQL box that Search is using.
Primary list of documents to read through, ordered in recommended reading order:
1. Planning and Monitoring SQL Server Storage for SharePoint – This is a good document to start with as it discusses many of the topics at a high-level. However, it does not discuss much about the SQL utilization that Search has.
2. SQL Storage Top 10 Best Practices – A great primer for issues to consider when building out hardware for the SQL box in a Search deployment.
3. Optimizing tempdb Performance – This is very applicable to Search and I strongly recommend you follow the guidance of this article. The tempdb is used in every end-user query executed. Plus, the crawler makes reasonable use of the tempdb. Making sure the tempdb is performant will directly impact the throughput and latencies of end-user queries.
a. Working with tempdb in SQL Server 2005 – A more detailed description of how and when the tempdb is used. Useful as supportive documentation as to why you should optimize the tempdb performance.
4. SQL Predeployment I/O Best Practices – This is a great article discussing the steps you should take to validate the I/O system of a SQL box prior to going into production.