Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 862
  • Last Modified:

SQL Server 2000 Increase concurrent queries

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
sim2k_support
Asked:
sim2k_support
  • 3
  • 2
  • 2
  • +3
2 Solutions
 
gplanaCommented:
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
 
shalabhsharmaCommented:
you could shrik the database to do this detach and attach the database again from managment studio it will improve your performance
0
 
Lara FEACommented:
What type of license you have CAL or Processor?
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
Anthony PerkinsCommented:
>>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
 
gplanaCommented:
0
 
sim2k_supportAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
Anurag AgarwalCommented:
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
 
gplanaCommented:
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
 
sim2k_supportAuthor Commented:
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
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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now