January 24, 2005 at 10:15 am
I 've an application with 4 touchpanels running a MSDE and one server running Enterprise version of SQL Server. With two of the 4 touchpanels i've a serious performance problem.
Query: select * from lot_packaging
Touchpanel 1: approx 8000 records -> query < 1 sec
Touchpanel 2: approx 65000 records -> query between 2 and 3 minutes
Touchpanel 3: approx 30000 records -> query between 1 and two seconds
Touchpanel 4: approx 27000 records -> query between 1 and 2 minutes
As you can see is the execution time not relative to the number of records in the table.
In the begiing I thought it was a problem concerning the statistics or the indexes on the database. I made an analyse, and it does not seem that it has anything to do with it.
I copied the database on which the query has a slow execution time to a touchpanel which has a fast execution time, and there the query ran fast. So I think my problem has more to do with communication between the touchpanel, and the server from which I executes the query.
The touchpanels are configured as linked servers on the server, but even when I run the query in Query analyzer connected to the MSDE touchpanel, the query runs very slow. So I don't think it has anything to do with the settings of the linked server, also because the four linked servers are configured the same and the query only runs slow on two of the four linked servers.
In the statistics of the query I saw something very special, but do not find any (usefull) information about it. The 'Cumulative wait time on server replies' is very different on the slow and the fast executions. When the query runs fast the 'Cumulative wait time on server replies' has a value between 0 and 25, and when it runs slow the property is between 20000 and 100000.
I don't know how I can find the source of this difference; does i have something to do with named pipes, or the TCP/IP sockets? Although I think everything is configured well on this side.
It would be nice if somebody know how I can locate or better solve this problem.
Thanks,
Yves Wittouck
January 24, 2005 at 3:24 pm
1. the fact that you don't have where clause on the query makes me believe that a missing index is not the issue
2. The fact that when run local on the panel is still slow, looks like a resource issue
3. For resource issues is good to:
a) minimize fragmentation in the table ( do you have a clustered index which is not fragmented?)
b) do you have the same memory or HD speed? (make sure that IO is not a bottleneck on the local hardware)
Without more info I won't be able to help you further but if you post more details maybe there is a chance for me to help you.
HTH
* Noel
January 27, 2005 at 1:24 am
I think it has something to do with the Network environment, as I told the 'Cumulative wait time on server replies' is extremely high on two of the four MSDE's.
When I execute 'select * into' instead of 'select * from' then the query executes fast, so I think something goes wrong to return the resultset to the server.
I was looking to the IO on the local machine and it doesn't seem that there are some bottleneck's. Neither the fragmentation can be the issue, because the fragmentation on the four lines is almost the same. I ran the DBCC SHOWCONTIG command for all the tables and indexes, and everything seems normal.
Does anyone know what this statistic ('Cumulative wait time on server replies') does know, and which are the elements that can influence this?
Thanks,
Yves Wittouck
January 27, 2006 at 4:52 am
Heh, so I'm not the only one with the same problem!
For two days now, I have been trying everything - defragmenting, rebuilding indexes, recompiling SPs - but performance has not changed.
'Cumulative wait time on server replies' is in the range of 50000.
Maybe time to look into network related stuff, but I don't yet know which counters/parameters to check. Let's work this out together!
January 9, 2008 at 10:25 pm
I also have the same problem. Does anyone have a solution or is anyone aware of what causes a blowout of the 'Cumulative wait time on server replies' statistic.
I have two servers production and test, test is simply running a backup of the live servers database. When I run a stored procedure on the production server it takes 30 seconds to complete. Running exactly the same stored proc on the test server (returning exactly the same data) completes in 3 seconds. The only statistic I can find that is drastically different is the 'Cumulative wait time on server replies'.
Any assistance would be appreciated.
January 12, 2008 at 12:21 pm
I am going to touch on the hardware side of this. I am going to make a few assumptions first to make the point work.
Assuming your schema is the same on all four servers, and that one is not being used heavier then any of the others, then we can look at a couple of things, the structure of the DB is one, and that has been covered in the posts above, the other is resources, I.E. hardware.
I would put some basic perfmon counters on your machine, namely cpu, memory in use, and the big one (I wouldn't be at all surprised if this was your issue) Hard Disk access and Hard Disk usage.
The reason why I think it could be hard disk is because certain types of disk and certain types of RAID levels are more condusive to writes then reads. If all of the machines are the same then...well...I'm pretty much at a loss on that since the schema is the same and the usage is the same as well. could be some kind of hardware malefunction if that is the case, in which case I would do some memory tests, and some drive tests. If none of this is at fault, you can dig deeper into the perfmon world and throw some SQL perfmon counters into the mix, here is a good link to a page that tells you some of the perfmon counters to use. http://www.sqlservercentral.com/articles/Administering/performancemonitoringbasiccounters/1348/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply