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

Posted on 2004-10-27
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
    LVL 29

    Expert Comment

    How about closing the connection.  Does that work?


    Author Comment

    Nope doesnt clean up anything
    LVL 29

    Expert Comment

    LVL 14

    Expert Comment

    by:Shiju Sasidharan
    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

    LVL 4

    Accepted Solution

    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?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT Security CISA, CISSP & CISM Certification

    Master the advanced techniques required to protect network resources from external threats with the IT Cyber Security bundle. Built around industry best-practice guidelines, the IT Cyber Security bundle consists of three in-depth courses.

    Suggested Solutions

    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    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…
    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…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    934 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now