Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server 2000 Increase concurrent queries

Posted on 2010-09-01
10
Medium Priority
?
857 Views
Last Modified: 2012-05-10
We have a client running a fairly small (2GB +/- ) database application.  A number of users hit it internally, a number from the web, all through browser.  Running Windows Server 2003 SP2 and SQL Server 2000 Enterprise Edition (8.00.760).  From time to time users experience significant slowness in application response.  During today's issue, the Application Event Viewer had a lot of 17052 Disk Information events.  No information in the event but a search on the web indicates this is likely due to concurrent queries.  That data indicates that MSDE and Personal Edition had an 8 concurrent query optimization limit that wasn't adjustable and the recommended solution was to upgrade to Standard or Enterprise.  Well we're already at Enterprise and I'm attempting to find out how to determine if a limit is in place and, if so, how to increase it.  Absent that, I'm looking for suggestions on how to alleviate this situation in the future if it is what is indeed causing the latency.  In this case it is not unusual to have 10-20 users in data entry mode where they will be searching the database for accounts or individual record information for updates, additions, etc.
System has 4GB of RAM and plenty of disk space.
Transaction log was quite large (5GB which has been backed up and shrunk) but not sure how that would impact performance intermittently in any event.
Any thoughts or help welcome.
0
Comment
Question by:sim2k_support
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 33580985
Dear Sir or Madam:

We have a similar issue. We have seen the problem is from time to time SQL-Server seems to make an internal process called AutoShrink. This process seems to consume a lot of writings on disk.

We readed an article from Micosoft which says Service Pack 4 solves this issue (I think you have SP3 on your SQL Server 2000). Sorry, I don't have now the link to this article.

You can download SP4 here:
http://www.microsoft.com/downloads/details.aspx?familyid=8e2dfc8d-c20e-4446-99a9-b7f0213f8bc5&displaylang=en


Hope it helps.
0
 
LVL 9

Expert Comment

by:shalabhsharma
ID: 33583021
you could shrik the database to do this detach and attach the database again from managment studio it will improve your performance
0
 
LVL 11

Expert Comment

by:Larissa T
ID: 33583289
What type of license you have CAL or Processor?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33583507
>>you could shrik the database to do this detach and attach the database again from managment studio it will improve your performance<<
I will bite.  Please give me one single piece of reputable documentation that supports the fact that shrinking your database will improve performance.
0
 
LVL 15

Expert Comment

by:gplana
ID: 33583945
0
 

Author Comment

by:sim2k_support
ID: 33589092
Thanks for thoughts and suggestions but I tried to indicate that I didn't think the intermittent issue could be the size of the transaction log since the size had been quite large for sometime but the slowness comes and goes.  The log file has been shrunk as was indicated (it is now under 500MB in size), but I expect we will receive complaints about slowness again.  If anyone has thoughts about how to determine and/or change the number of concurrent queries in SQL Server 2000 Enterprise or related possible constraints, would be grateful.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33593256
>>I tried to indicate that I didn't think the intermittent issue could be the size of the transaction log since the size had been quite large for sometime but the slowness comes and goes<<
I know.  I thought I had set the record straight.  The size of the Transaction Log is not a problem for performance, what could be a problem is if you have repeatedly done a shrink on it, as this can cause serious fragmentation and in consequence the problems you are seeing.

>>If anyone has thoughts about how to determine and/or change the number of concurrent queries in SQL Server 2000 Enterprise or related possible constraints, would be grateful.<<
There are a thousand and one reasons.  From fragmented indexes, statistics that have not been updated since the stone age, queries gone rampant, data or log file having to grow continually and at inopportune times, SQL Server not correctly configured or worse still sharing the server with other apps, the list goes on and on.

If you are not experienced with it, then your best bet is to hire a reputable SQL Server consultant to spend some time getting you up to speed.
0
 
LVL 8

Accepted Solution

by:
Anurag Agarwal earned 500 total points
ID: 34277091
hi sim2k_support,

is your problem solved if not then let me state that the slowing down of application may be due to incorrect measures taken for db management ............

I myself have a db of 12 GB + which previously has same isues look for performance tunning tips

try using indexes, it definately bring a great change, if they are already there then refresh them or rebuild them. Performance tips in sql server

since 2 GB is not a very big size but if you required you can go for partitioning the db
0
 
LVL 15

Assisted Solution

by:gplana
gplana earned 500 total points
ID: 34277552
We had a similar problem on our db, which was caused by the number of locks. Try to commit changes as soon as possible. Also, ensure your transactions are using READ COMMITTED isolation level instead of SERIALIZABLE, where SELECTs would cause locks.

Also analyze the execution plan of your main queries to see if you need to create index as anurag suggests.

Regards.
0
 

Author Closing Comment

by:sim2k_support
ID: 34392907
Suggestions didn't resolve question or even address the issue.  last two (which received points) at least responded to additional comment from initial responses failing to address the question
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

971 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