SQL Slow access, a lot of timeouts and locks.

Posted on 2006-05-18
Last Modified: 2008-03-03
Have Win03 and SQL 2000 setup and running.  Use a Program Called Macola ES to access the database and also a few ASP pages I have writen.  Data is taking a long time to come from the database, and causes the tables to lock and timeout.  There is one location that uses the Macola ES program from a wireless USB Network card and that one takes 2x-3x as long as the PCs on the normal LAN to process an item.  Any issues, or ideas that we can try to speed up SQL Server or the workstation's link to SQL via Wireless?  The other Wireless locations are only accessing an ASP page witch is slow also, and times-out if the other PC locks the table.
Question by:kwitcom
    LVL 27

    Expert Comment

    >> Any issues, or ideas that we can try to speed up SQL Server or the workstation's link to SQL via Wireless?

    This sounds as if this is a front-end (connection) issue. You might find people with more and better information on the Networking area. This area deals with transact-sql queries or SQL Server management.

    You should check out to see what you would need to do to set up a SQL Server performance audit. You will have to establish some benchmarks before you can evaulate whether the connection is slow or not.

    How do you process an item? Is it a series of stored procedures? An ad hoc query?

    Author Comment

    Using the Software I will goto a screen enter order number change process to 100% done then set finish QTY to 1 and save it, at that time it will take all the parts out of inventory that makes that product and assign them to the product and then generate a serial number on a POP-UP window, then Click save and it saves all the data it pulls together.  A lot of parts go into making the Product, about 100+.  Over time the process will be broken down into zones on the shopfloor and parts will be assigned when they are used.  Till that time all items are marked as used when the finial Product is done.
    LVL 8

    Expert Comment

    Some sugestions that have helped me in the past...

    - Check your perfmace settting in Enterprise Manger..
         Right click on your sql server registration and go to properties. Theres a tab called performance.. If you have duel processors you can make  sure its useing both processors.  Also theres a box to select to boost SQL server priotity on windows.  I use this check box on my sql servers that are primarly useing sql server only..

    -Also on some of my sql servers the datbases became fragmented..  Might not be the case here since its fairly new sql db..

    -Check the how often your backing up transaction logs..  

    -Check the sql server logging level....

    hope some of these help...
    LVL 8

    Expert Comment

    Correction...  I meant the Processor tab in the SQL server Properties...

    Author Comment

    Where do I Check this information?

    -Check the how often your backing up transaction logs..  

    -Check the sql server logging level....
    LVL 8

    Accepted Solution

    The Transaction logs are usally backed up via the MS sql server agent maintence plan.  In Enterprise manager.

    - Expand the mangemnet tab
    - click on databse maintenance plans
    If you have a maintence plan setup to backup your datbase and Transaction logs you should see one here.
    - If you do right click on it and click properties
    - Go to the tab labeled Transaction log backups and change how often the transaction logs are backed up if you feel oits too often.

    Since your in there make sure you have the box under the integrity tab labled check database integrity marked and include indexes.

    Also check out this website...Ive always found very useful information about sql server logging

    SQL server logging levels

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    760 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

    7 Experts available now in Live!

    Get 1:1 Help Now