PRODUCTION PROBLEM: SQL slow with increased number of connections
-
Hi. We are running SQL Server 2000 in a live Internet site. When the number of connections from IIS (different server to the database) is low, then the database server's CPU is high (75%) and queries run quickly.
When the number of connections reaches a limit of about 500, the same query will run 4 times slower, and the database server's CPU will drop to about 50%...
We've checked everything we can think of on the server: Hardware problems, disk space, event log, SQL jobs running, available memory, network bandwidth. We're running full-text search on the server.
Sometimes we get blocks in SQL when this problem is occuring, and the blocks are always on a table that is only updated and read from a commerce server object (yuck!).
Can anyone make any recommendations with regards to my problem, particualy with regard to the number of connections and any performance tuning that could be done? I'm also trying to figure out why the CPU on the db server *DROPS* when it runs slowly.
Thanks!
-
Are you seeing any deadlocks, or is it just blocking?
Ian Dundas
Senior IT Analyst - Database
Manitoba Public Insurance Corp. -
What are the physical specs on the server, in particular RAM and #CPUs? And do the Full Text catalogs share a drive / drive array with your SQL data and/or log files?
Scott Thornburg
Volt Information Sciences
-
I didn't see any deadlocks.
The drives are RAID 5, about 300 GIG. 6 CPU's and 4GIG RAM.
The Full Text catalogs are on the same drive as the databases (D:\MSSQL\FTDATA). I suspect that this could be a problem, as the web says keep the catalogs on a seperate non-RAID5 drive pack.
We see a lot of paging on mssearch, and are starting to think that this paging (aparrently a common problem with mssearch) is causing sql server to throttle back.
We ran this command to try and increase the amount of memory available to mssearch. Perhaps there are other things we can do to tune?
EXEC sp_fulltext_service @action = 'resource_usage' , @value = '5'
-
Did you look at :
http://support.microsoft.com/default.aspx?scid=kb;en-us;298794
http://support.microsoft.com/default.aspx?scid=kb;en-us;303459
Both refer to a specific registry setting.
from the latter:
This problem occurs frequently if the total size of all the catalog files exceeds 256 megabytes (MB).
If you experience performance issues with a full-text index, you must change the MaxPropStoreCachedSize Registry key value in the Windows Registry. The Registry key path is:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Indexer
The REG_DWORD of the MaxPropStoreCachedSize key controls the memory, in megabytes (MB), that a catalog caches. The value of the MaxPropStoreCachedSize key is the maximum memory that each search instance can allocate.
The MaxPropStoreCachedSize value must be five percent more than the total size of all the catalog files. The size of the catalog files is the sum of the sizes of the category file extensions (*.ps1 and *.ps2). Adjust the MaxPropStoreCachedSize value accordingly to assure an optimum use of virtual address space. You must be cautious when you adjust the MaxPropStoreCachedSize value because a reduction in the cache size may affect the performance.
Another article mentioned that you can experience unreported deadlocks in the search service when a large number of querries are being run against it at the same time it is updating/populating the indexes. I am guessing that this might occur if background index upating is turned on.
Ian Dundas
Senior IT Analyst - Database
Manitoba Public Insurance Corp. -
hi,
Do you have a copy of MS's "Full-Text Search Deployment" white paper? ( found here http://support.microsoft.com/default.aspx?scid=kb;en-us;323739 
It says that you should either modify or add the entry.
The document also has quite a bit of info regarding troubleshooting and performance. Take a look at pages 44-49. They discuss setting CPU affinity to ensure that SQL Server and mssearch aren't starving each other. Also, one thing to mention, is that the mssearch uses the windows file cache so you may want to ensure that the sql server isn't taking most of the memory by setting the max server memory low enough to allow enough system RAM to be available to system file cache.
Ian Dundas
Senior IT Analyst - Database
Manitoba Public Insurance Corp.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply