SharePoint Portal Server 2003 Crawl Performance Part 6
SQL performance
I think it goes without saying that if performance of the SQL server is not running at its highest then it will definitely slow things down in SharePoint. However, there are times that even if SQL is running at its highest that actions that occur in SharePoint can cause SQL not to run as fast as it can when we are doing a crawl. I noticed this while testing a theory that I had.
I had just recently added quite a bit of data to a SharePoint site for the purpose of performance testing and found that things were running a bit slow for my tastes. I was reading an outstanding book called Microsoft SharePoint Products and Technologies Resource Kit If you don't have it, I highly recommend it as it will really fill out some of the things that have been covered in these postings. I read in this book in Chapter 10 around page 263 that several SQL commands could be issued to help with performance.
Before showing these commands it is very important to understand that updating the SQL database that is used for SharePoint is not supported. These commands are performing SQL maintenance and these specific commands have been allowed by our development team. Before doing anything in our database, it is very important that you have read kb 841057 and understand it before you proceed.
The SQL updates that are documented in the Resource kit are as follows. I have taken the liberty to add a few extra commands as well.
- use sps2003p1_site -- your site database
- UPDATE STATISTICS Personalization
- UPDATE STATISTICS UserInfo
- UPDATE STATISTICS WebMembers
- UPDATE STATISTICS Sites
- UPDATE STATISTICS Webs
- UPDATE STATISTICS Lists
- UPDATE STATISTICS WebParts
- UPDATE STATISTICS Docs
- DBCC DBREINDEX (Personalization,'',0)
- DBCC DBREINDEX (UserInfo,'',0)
- DBCC DBREINDEX (WebMembers,'',0)
- DBCC DBREINDEX (Sites,'',0)
- DBCC DBREINDEX (Webs,'',0)
- DBCC DBREINDEX (Lists,'',0)
- DBCC DBREINDEX (WebParts,'',0)
- DBCC DBREINDEX (Docs,'',0)
- use sps2003p1_serv -- your serv database
- UPDATE STATISTICS srch_gathererlog_1
- UPDATE STATISTICS srch_gathererlog_2
- UPDATE STATISTICS srch_gathererlog_3
- DBCC DBREINDEX (srch_gathererlog_1,'',0)
- DBCC DBREINDEX (srch_gathererlog_2,'',0)
- DBCC DBREINDEX (srch_gathererlog_3,'',0)
The commands above that are for your _serv database have to do with the gatherer log tables. You may have a different number than what I have shown here, you will need to look at your database to determine if you have more than the 3 that I have listed here and add or remove commands as appropriate for your database.
When running these commands and then doing another full crawl my crawl time reduced over 1 hour in run time, your mileage may vary. I would recommend that these be run frequently, perhaps weekly or monthly depending on the amount of data that you are adding/deleting to the farm.
In a later post we will discuss additional items for use with tuning.
