[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

Performance on MSSQL server

We are running SQL Server 2000 SP3A on a W2000 cluster (passive failover) on a LAN. Occasionally (every week) we have severe performance issues : The time to execute our main query rises from 300 ms to 4000-6000 ms (we build in a logging feature), and as sudden as it rises it can drop again to normal 300 ms response. We even did experience normal transaction responses from other clients using SQL Query with on the same query when our application did show 4000 ms responses) In the past we solved several problems (memory leak : SP3A, wrong execution path). The application is the only one on the cluster, and has only 5 clients using the database direct.
We also placed the application on a other database server (single non clustered) and the problem also occurred on that database machine. We can rule out virus updates, backup and so on, and the network is also good responding on times with high response times.
The last thing we tried is shutting down named pipes on the clients. Since then the problem didn’t occur anymore, but we are very sure its only a question off time.
We are convinced it must be a software problem, but we don’t know anymore where to look.

Suggestions ?
1 Solution
Probably just a locking problem - someone else is updating the data as you run your query, so you have to wait to obtain the lock.
You could run an extensive anylisis of the activity on the server usuing profiler and than locate the slower query i in the trace file and look in its vicinity for other activity that can be affecting the performance of the query at that very moment. You would have to run such trace for an extended period of time to be able to record the occurence of the problem. That of course places an additional load on the server. You have to answer yourself a question if the problem is worth the time and effort needed to analyse it.
There can also be other factors - for instance such pattern can occur when the data is not in buffer cache anymore. In normal query runs it is there, so the query time is small, but then it is flushed from cache for some reason and needs to be re-read from disk. You can try this: start the server clean, run the query for the first time (no data in cache), record the time, re-run the query (now the data is in cache) and compare the time. If the times match those from your problem description - you know the reason.
If you tables are being populated wth large amounts
of data - you need to run

Update statistics on you tables that are being used in you query

in query analyzer run

update statistics tablename

it might take a few minutes to complete

be patient if you gonna do this

HardeveldAuthor Commented:
The application is our Auction Clock system, so it worth the effort/time.
Locking could be the problem, we can see that happen when another part of the applicatie is updating the data. But before and after that part is finished the problem doesnot change : it stayes. The other proces normale slows down our query to 50-75 %.
Since we also tested the same query with the same parameter at the same time from anather station with good responce we think we can rule out locking. Thenolock feature is used in the query.

At this moment the data tables contains less than 1000 records and we limmited the resultset to 40 records
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

1. Check out the status of your transaction logs files. Check out their size, and see whether they are being handled properly.
2. Locking, as stated above, although doesn't seem likely (since you say it only happens once a week).
3. Object statistics (again, as stated above).
4. Memory usage - check out the memory size allows for SQL server. If the machine runs out of memory, then all table variables work SUPER slowly. Temp tables and their associated actions should still work fast.
5. Disk space on the drive.
6. If the above doesn't work - buy a bible and start praying :)
HardeveldAuthor Commented:
1 Transaction log is 23 mb
2 Locking could be but we don't know where to look anymore
3 Object statistics are auto
4 We don't use table variables
5 10% in use
6 What bible would you recommend
The biggest problem is capturing the problem as it occurs. If you can not reproduce it, it may be very difficult to have a trace running at the right time to capture server activity when the problem occurs. The situation you want to analise only happens once a week or so and only takes about 4-6 sec. - how do you capture a log of what happened on the server then? My point is: you would need to have a trace running for weeks (at least) after weeks to capture the picture of what was going on on your server that very moment.
My other question is: where do you measure the response time? On the server or on the client?
The fact that you can not reproduce the problem on another client sugests that it might be something about the connectivity - might be that the connection is re-opened when you execute the query from the client, issue with connection pooling or something.
HardeveldAuthor Commented:
Sorry, there is some misunderstanding : in normal conditions our quey takes 200-300 ms to run. There are periods from 1 to 3 hours the query is slowing down to 4 or 6 seconds per query. That periods of being slow occurs once or twice a week. The query itself is used 4-5 times per minut.
The application being slow is always reported by the users. To rule out the network we build in a logging in the query itself, so the mentioned times are the difference between end en start timestamp of the query.
The application is not a object oriented one, it doesn't create a database connection every time it needs data, on start-up it connects to the database, and uses that connection for each query.

There must be something going on on your server at the times when you have the perfomance problem. The only question is what it is: backup taking place, bulk load, mass update etc.
Do you see any patterns in when the problem occurs? Like for instance: every tuesday afternoon or so...
HardeveldAuthor Commented:
I agree, there must be. We can rule out backup (runs when application is not active) , virus update (shut down,manual). There is a mass update process, that run every 5 minutes. In normal circumstances the update is slowing down our query (to 1 second) , but it runs only 2 to 7 seconds, and that is acceptable.

There is little hope we tackled the problem, we removed named pipes from the client, and the application is running perfect for the past 5 working days. There are some discussions that is better not using named pipes on a slow WAN, so in theory since we are running a 100 mb LAN  with a 1 GB backbone, there should be no difference. Do you know of any reference on named pipes being slow?
As a general rule, TCP/IP is the fastest net library for communication with SQL Server. I am not sure whether your problem is related to the net library used, but switching from named pipes to TCP/IP is generally a good idea.
HardeveldAuthor Commented:
Since our problem didn't occur for almost a month it's best to close the call.I am still not convinced removing named pipes was the solution, maybe there is someone who can explain that it can cause these problems. Tanks for the time and thinking

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now