[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


SQL Server, adodb.recordset Visual Basic. Memory increase on each query

Posted on 2004-10-27
Medium Priority
Last Modified: 2011-09-20
I have an SQL server running but i seem to have some sort of problem.
Every time i perform a sql query the memory useage of the sqlservr.exe increases, is this normal ?

I open the Recordset doing the following
dim rs as new ADodb.recordset

and even close it
set rs = nothing

What could i be doing wrong or does the SQL server simply do this ?
Question by:mSchmidt
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 29

Expert Comment

ID: 12422416
How about closing the connection.  Does that work?


Author Comment

ID: 12422498
Nope doesnt clean up anything
LVL 29

Expert Comment

ID: 12422529
LVL 14

Expert Comment

by:Shiju Sasidharan
ID: 12430467
hi mSchmidt
try these...

1. Keep watching when the memory usage suddenly gets raised
    i.e when
      opening the connection object
      opening the recordset
      closing the connection object
      processing the Recordset (check whether u r using MoveNext inside the loop)

     2. check the record count of the recordset (whether it is huge)
3. if 2 is the reason try to make use of Paging in Recordset (AbsolutePage property)      
4. Check whether ur Network is busy
5. try whether Sql server hangs only when ur specified query is executed


Accepted Solution

rlibrandi earned 2000 total points
ID: 12432083
You could turn on tracing on the SQL server to see what SQL processes are being hammered.

Does SQL get hammered if you do the same query in Query Analyzer?

What does the query look like?  How much data is being returned to the recordset?  Have you considered client-side ADO cursor as opposed to the default server-side cursor?

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question