SQL Stop Signs and Slowness

I use an Access Database with SQL and I have multiple users accessing it and we get a lot of stops signs and slowness. Does Anyone have any suggestions on how to get rid of the stop signs and slowness?
Who is Participating?
monosodiumgConnect With a Mentor Commented:
The blocking coupled with low CPU activity suggest either I/O bound process or something else. The something else could be too many locks which in tunr might be due to too manz recordsets being held open and connected. If its the latter, then changing the code to open the recordsets in readonly mode where possible and/or disconnecting and/or closing them asap could help a lot. I saw a slowdown like that once that was due to that.
If it's and I/O issue, then consider moving some of the tables to files on other disks.
explain the processing that your are attempting...

tinakingAuthor Commented:
We are using a very large SQL Database (100+GB) to store a customer database with a very large history of past transactions.  We use an Access front end.  The processing we are doing constantly are customer lookups, updates to the accounts, and adding transaction data.  We have contacted the vendor of this application for the past few years, (yes years!) and they haven't been able to assist us with improving server slowdowns.  

During busy times, the database will be filled with blocking and blocked processes.  The users will receive a "stopsign" message for a transaction that is taking more than 15 seconds to complete.  Sometimes I will update the statistics on the hot tables, and sometimes it will improve performance.  Running a SQL trace shows us that normal transaction times range from 400ms to 3000ms, with the expected time to be less than 1000ms.  During slowdowns, transaction times will jump to 1 minute and sometimes up to 3-5 minutes for a transaction to go through.  These will be the same transactions that were just taking 400ms to complete.  Many of the processes will time out after being blocked for a period of time.  We have had 100+ processes in SQL active and had no problems, but later, we'll experience blocking and slowdowns.

The cpu usage is fairly low for how busy the server can be, and SQL has about 2GB RAM allocated to it, and the OS has another 2GB RAM for any other processes.  We are running SQL Server 2000 Standard Edition (8.00.534 SP2) with Windows 2000 Server (5.00.2195 SP3).  Our indexes are rebuilt monthly, and some of the major tables are defragmented weekly.  The vendor says that all of the indexes are functioning and that there aren't any more that we would need.

We are looking to find out why these blocked processes appear intermittently, and if there is anything we can do to correct problems or prevent them in the future.  Thanks in advance for your help.
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Scott PletcherSenior DBACommented:
Do you really need all of the history on one db?  Maybe you could move history older than, say, 5 years to a different db.  You could create a view that would use both sets of data for those times when you did need full history.
so just to confirm

Access is used for the Front end GUI ?

and a SQL Server Database is used to store the data?

Standard edition can only use 2GB of ram certainly allow it to use this much ....

do you have memory bottlenecks?

what disk arrangements do you have on the server...

are the LOG / TEMPDB / MAIN DATABASE files separated onto physical drives?

would migrating to enterprise edition be an option? and buying more ram?

could you "partition" the database into an active(current) and history database ...

does the vendor have any other clients running databases approaching this size....


arbertConnect With a Mentor Commented:
Have you ran Profiler to identify the SQL workload on the server?  Have you ran Windows Perfmon to identify disk io, paging, memory usage?

I'm guessing that the front-end app probably wasn't constructed all that well.  Any chance that users "browse" records, leaving them on the screen for a period of time, and block other users?  Next time you see blocking, take a look at the process and what it's doing....
as i asked and mono and arbert suggest ....

please explain in lower level business terms the actual processes which are running when you experience thses slow downs...

tinakingAuthor Commented:
That's correct... Access is used for the front end GUI and SQL stores the data.  I don't believe that we have memory bottlenecks... how can I check?  

I am working on gathering info on the disk arrangements for the server.

The main database, tempdb, and the log are all on separate logical drives.  We have considered enterprise edition, but didn't want to unless we had to.  We currently can't modify the database into an active/history database at this time, but have suggested this to the vendor.  Our vendor claims that there are other clients with a database just as large and they don't have the problems that we do... Not sure that I believe that one though.

I have ran profiler and perfmon but I am not sure what red flags to look for, or what to report.  If you could suggest some counters for perfmon and their expected ranges, then I can see if they are abnormal.  Also, how to I check the workload on SQL with profiler.  I have run traces, but again, not sure what would be a red flag in there.

I will call the vendor to see if their transactions allow browsing.  It is difficult to isolate what is going on when we have slowdowns, because there are about 100 client PCs at five sites that are geographically distant from each other.

Thank you all for your help.
Perfmon--look at memory usage, disk queues (read and write), context switching (if it's a multi-proc box), paging.

Profiler, capture a workload and the use the index tuning wizard (under tools/wizards in Enterprise Manager) to analyze the workload and make suggestions.

So you have sites from other locations that hit this database??
tinakingAuthor Commented:
Yes that is right we have 5 sites that hit the database. The furthest site is 125 miles away.  
LowfatspreadConnect With a Mentor Commented:
you haven't elaborated on what in business terms your users are doing to the database...

blocking occurs when different users require access to the same resources .. and can be exacerbated (and lead to
deadlocks) when the resource access isn't performed in a consistent manner...


user a
will required   Item A
                     item b
                     item C

user B requires
            item c
            item b
            item a

if user b gets item c
and user a gets item A
then which ever gets B it will be waiting for the other to complete...

but if you vendor is supporting multi gigbabyte databases i'd have expected them to have
dealt with this...

is the Access Fontend and the back-end database actually part of the same package...

Is it possible that one/some of your users have written there own functionality / frontends
which maybe a problem source....

are you trying to run a mix of transaction and mis reporting at the same time on the database?

tinakingAuthor Commented:
Our business terms is we have Multi Casinos and we use this a Players Database that keeps track of all the players that come into the casino and the Points that they acquire during there stay while playing at a slot. Our drives are set up in 3 Power Vaults. In the first Enclosure we have 10 drives and each Drive is set up with only Application on it. And the second Enclosure is mirrored off the first in a Raid 1/0 setting.  And on the 3 Enclosure we have a Raid 5 setting and have 10 drives and they are as well set up with on Application. Each enclosure has a hot swappable spare as well.

We are also having the problem that the MSSQLSERVER service terminates unexpectedly. Does any one have any suggestions on this as well?
The stopping bi tworries me. That doesn't sound like a resource problem. I wonder if the latency problems stems from the stopping.
If you SQL process restarts after a stoppage without manual intervention then I'd guess it was being stopped deliberately by some other process and then restarted as opposed to crashing. That would suggest you have some other software or config setup that causes this, could be some misbehaving watchdog process.
If SQL server really is stopping, then check the Windows logs. That might yield some clues.
Again, run perfmon and look at the counters--do you see any IO queuing?  We're running 400gigs on RAID 10 and IO is our biggest bottleneck right now--placing filegroups and getting the biggest IO for you bang is crucial.  IO bottlenecks could also contribute to SOME blocking because the transactions take longer to complete.
>IO bottlenecks could also contribute to SOME blocking because the transactions take longer to complete.
A very good point. It can be a lot more than some. Once the transactions start to block everything slows down which in turns makes transactions block longer and very quickly you can get an explosion which causes the system to grind to a halt . If that's what is happening, then you need to improve the performance and/or throw more hardware at it until you get below a critical load level.
"We are also having the problem that the MSSQLSERVER service terminates unexpectedly."  Anything in the SQL log or any DMP files in the log directory?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.