Solved

SQL Stop Signs and Slowness

Posted on 2004-04-02
18
208 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:tinaking
  • 4
  • 4
  • 4
  • +2
18 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10743768
explain the processing that your are attempting...

0
 

Author Comment

by:tinaking
ID: 10744133
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10744170
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.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10744217
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....

?  

0
 
LVL 12

Accepted Solution

by:
monosodiumg earned 168 total points
ID: 10744240
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.
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 166 total points
ID: 10744336
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....
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10745493
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...

0
 

Author Comment

by:tinaking
ID: 10769689
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Expert Comment

by:arbert
ID: 10771027
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??
0
 

Author Comment

by:tinaking
ID: 10773581
Yes that is right we have 5 sites that hit the database. The furthest site is 125 miles away.  
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 166 total points
ID: 10774151
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...

e.g.

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...
Deadlock!



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?


?
         
0
 

Author Comment

by:tinaking
ID: 10841601
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?
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 10842456
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.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10843810
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.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 10843990
>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.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10845506
"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?
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

747 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

12 Experts available now in Live!

Get 1:1 Help Now