Crazy Slow SQL

  • Slow performance after upgrading to SQL 2005 using Microsoft Dynamics Great Plains.

    We have checked everywhere we can think of.  We are in desperate need of ideas.  This is what we have done so far;
     
    New server has been installed.  Small Business Server 2003

    All of the appropriate sp's were put on the Great Plains Data then we went to SQL 2005.

    Put on the SQL 2005 sp1

    Installed SQL using Domain account and the mixed mode

    ODBC on server is SQL Server  connects just great

    ODBC on workstations (installed from sql server 2005 disk) native client - connect just fine.

    We have 3 gig of ram, 2 gig of data

    Turned off sharepoint, turned off all monitoring.  When you enter a transaction, for instance, it hesitates on the next document number, lookups are slow.

    Ran all my maintenance on data that is recommended by Microsoft

    Check catalog

    check database

    reindex

    shrink

    update stats

    I can run this at my office on a smaller system smaller that this.  2 processors

    Intel xeon 3.4 GH

    They have installed Norton on the workstations, AVG on the server.  There is a group policy that has the windows firewall turned on.  I went to services and turned it off.  I turned all of the above off and it was still slow.

    I then installed an instance that was local account not domain account.  Just thought I would try.  Nothing.

    ODBC Tracing is not on.

    Can someone please help us PLEASE.

     

  • How much memory is SQL Server actually using?  How are the disks configured and what are the placements of the filegroups?  Is IIS running on the box?  Is the NIC set to the correct speed (don't laugh, I've been bitten by that one)? Does a profiler trace reveal acceptable execution plans for the slow transactions?

    Just the first questions that come to mind....


    And then again, I might be wrong ...
    David Webb

  • First a memory issue on the sql server

    In properties when the server is first installed it places maximum memory at

    2147483648

    What unit is this.  The properties page say maximum memory in MB so if I have 3 gig of ram, what should I put. 

    3 gig is 3072 MB

    3 gig is 3221225472 bytes

    What should do I restrict my memory at Bytes or MB?

    What do you mean how are the disks configured? (don't laugh)

    IIS is running

    NEC speed - You don't laugh, How do I check that.

    I ran profiler.  When the max memory of was reached I received this

    -2147483649 then 2487483648 the this 'pppppppppppppppppppp'  That is the only odd thing I can see. 

     

    Tracy

     

  • Unless you have to, I wouldn't run IIS on a SQL Server box because they'll both try to get as much memory as they can.  Was IIS running on your old box?

    I wouldn't change the memory setting just yet.  Can you tell how much memory the SQL Server is actually using as opposed to other processes on the box?  Is the swap rate on the box high?

    Are the disks attached to the server or are you using a NAS or SAN?  How many disks do you have?  Are they in a raid configuration?  Are all the database filegroups on the same physical disk?  Are they on the same disk as the system swap file?  If you check perfmon, are the disks busy?

     

    TO check the NIC:

    Start / settings/ control panel / network connections

    right click on the connection and choose properties.  You should see the adapter and be able to click 'configure' to see the settings.  Check with your networking folks to see what it should be set to. 


    And then again, I might be wrong ...
    David Webb

  • Memory usage for SQL is 929196K

    However, the network guys have installed exchange server on the same server.  It is at 570905K

    This is a problem right?

  • Yes.  Best practice is to run ONLY SQL Server on the server.  Anything else will lend itself to contention on disk, memory, or both.   


    And then again, I might be wrong ...
    David Webb

  • If you run sp_monitor you should get a baseline and perhaps enough information to determine it you need to go onto more specific IO stats. 

    It will provide CPU%, number of connections, IO stats, packets recieved/ packets sent, read, and write info etc.

  • Is this on a san  configuration as i have a similar problem and it turned out to be a network card io problem....also I strongly believe running exchange and iis on the same server as sql server is bound to cheat sql on memory resources..

  • We did figure it out, we reinstalled the odbc drivers on the workstations and reinstalled the Dynamics GP application and it seems to be working fine.  Not sure what happened there because it was happening on all the workstations.

    Thanx For All You Help

    Tracy Bilcik

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply