I was recently looking over the settings for a server (Windows 2003 SP2) that is used exclusively as the DB host for a Sql Server 2005 install that is used on a website. I saw that for all three drives on the machine (one for system/program files, one for data and one for logs) the Indexing Service was turned ON.
In my experience (mostly with desktop, not server setups), the performance hit of the indexing service doing its job has always cost much more in terms of performance than any speed gains in finding files (for me, turning it off has always resulted in a big performance gain).
So is there any reason why this should be turned on for a Sql Server Box (or any server, for that matter)? Assume that no tables use Full Text Indexing (would that make a difference)?
Best Answer
No reason you typically need Indexing Service enabled on a Sql Server 2005+ server, even if you are using FTS (in Sql 2005 and beyond, Sql uses it's own indexing service, MSFTESQL or Sql Service FullText Search). I thought the service itself was by default disabled on Server 2003+ anyhow? Naturally, regardless of what is checked for each LUN/folder on your server, if the service is stopped, it ain't gonna index.