I’ve been using SQLQueryStress to benchmark a test query against a VM SharePoint database. I used the Docs View and selected the top 101 where size > 100
SELECT TOP 101 FROM Docs WHERE Size > 100
The original time taken was 23 seconds
The disk was 64% fragmented and after defragging, the query time was 9 seconds. That’s a 60% improvement without touching the database.
So, onto the database and all the indexes are defragged and updated. This took the time down to 8 seconds (12% quicker)
Now for a couple of experiments.
SpaceSniffer
SpaceSniffer running on the parent – 3 minutes
SpaceSniffer completed and looking for changes on parent – 9 seconds
SpaceSniffer doesn’t affect the performance of the SQL after it finished scanning but during the scan the performance was terrible.
Compressing files on Guest
I’m compressing IIS log files from the last year and running the same query at the same time. This expanded the query time to 8 minutes.
SQL Service Pack 4
First thing, it’s hard to find and second is there is no update for SQL Express (currently). I will have to reinstall SQL Express to do the update.
Can’t install service pack 4
Synchronizing VM with the undo file
The VM has been running since November and has a 23Gb undo file. I’ll remove this and see if it makes any difference.
It did. Took the total load time down to 4 seconds. from 23 at the start.
