Solved

SQL Server 2000 Increase concurrent queries

Posted on 2010-09-01
10
847 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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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 250 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 250 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

757 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

19 Experts available now in Live!

Get 1:1 Help Now