Server Info: Windows Server 2003 Standard, SQL 2005 Standard, 4GB RAM, 2X Dual core Processors, no other apps on server.
HDs: C - OS + master + model on 1RAID 1 SCSI 15Krpm, D - .mdf and .ndf 3 drives 3RAID 10 SCSI 15Krpm, E - Log 1RAID 1 SCSI 15Krpm, F - tempdb 1RAID 1 SCSI 15Krpm
NAV Info: 4.0 SP3, Manufacturing, 14GB database, 45 users, 70000+ items in inventory, 15000 items start with the same 3 letters “MFN”.
Generally the database works well.
PROBLEM:
Occasionally when doing CTRL+F + Find as you type in Inventory, then type MFN and it freezes for the user doing the find. Can take up to 50 seconds to get to the first items starting with MAN. At the same time the SQL memory is maxed out at 1.7GB. Also at the same time the temp files (F Drive) gets hammered with average disk queues spiking to 1000!!!
Tested again, use Find as you type hit M - OK, then hit F and it freezes for ages, then hit N and it is slow but not freezes.
Also tested using /3GB in boot.ini. Didn’t seem to help.
I have been looking into this for a long time and can’t solve the problem as yet. The rest of the application works a treat…
You might want to increase the amount of memory SQL is set to use. In SQL Server Management Studio, go to the properties of the server.
On the memory page, consider fixing your min and max values to be the same. If you have 4GB of RAM, try using 3GB. 1GB for the OS should be more than enough. You’ll need to restart SQL and ensure the /3GB in boot.ini is still in place.
Failing that, fire up SQL profiler or the Client Monitor within NAV (is that in 4?) and look for the select statement that hangs. See if the query is forcing an particular index to be used. Whatever the case, take the SQL query into management studio and run it manually. You should be able to replicate the situation. Remember there is a good chance SQL will cache the execution plan of the query, which would explain why it runs better the second time around. You may then want to pay attention to the indexes used, using the execution plan. However I’m not in a position to comment on that given I have not looked into find as you type issues before, and fiddling with the index could be problematic. Others on this forum may be better able to advise.
I recall the performance documents suggest disabling find as you type as it is a very DB intensive action.