May 8, 2002 at 2:08 am
Hi Guys,
We are using SQL Server 2000 as our backend and VFP as front end. At times the
performance is very poor while running the application . So we used windows task manager to trace
out the problem using the processes tab. Whenever we run a query sql server exe (sqlservr.exe)
takes more memory for its usage and it is not releasing this memory even after completing the
query.
We believe that this may affect the performance . How to overcome this problem ?
Is there any option to release the additional memory that has been used by the SQL Server while
running the query as soon as the query is completed ?
Thanks
DESS
May 8, 2002 at 4:02 am
you can run DBCC FREEPROCCACHE it removes all elements from the procedure cache.
To verify it clears procedure cache run DBCC PROCCACHE
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
May 8, 2002 at 4:43 am
Actually FREEPROCCACHE does not generally release the memory back to the OS and it is considered bad to run on a production environment as you are forcing data to be reloaded into memory instead of being able to get the bennefit of a cached dataset (already in memory from a previous query where it will be faster to access). Also it is typcial for SQL to use upto and in some case more than 70-90% of the memory. This is for various buffers and caches that allow SQL to perform better each subsequent run of a query. Most times poor performance can be attributed to improper design or under par hardware. Maybe you are running a query to return a dataset that is large that you will filter in the app. It is far more effecient to query just what you need limiting the amount of data moved for all (SQL, network, and Client). Use profiler to do a trace and capture your queires to find your worst performering ones and look at correcting. If al else fails you may not have enough memory or some other hardware bottleneck. To find tips on the best way to check for these there are some on this site and check out http://www.sql-server-performance.com .
Hope this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply