Simple queries intermittantly timing out.

Question

Simple queries intermittantly timing out.

kangarolf

Hall of Fame

Points: 3432
More actions
November 7, 2002 at 2:20 pm

#58140

Hi all,
I have a very curious problem. Simple queries on 2 of my tables time out, when run through the profiler or ASP/ADO. If use one of the columns (call it colA, which is a bittype) in a where clause as in select colA, colb from table Where colA = 1 it will occasionally timeout. Then once it has timed out once SQL starts to 'choke' and ends up with more and more connections open. After a while I can run the query successfully again.
One of the tables it affects is a subscription table updating in a transactional replication from a remote server. I thought the replication would be a good candidate but the other table it affects is not part of the replication. Neither table is very large and the query when run successfully executes blindingly fast. Thw site that runs on top of this database is not hit more than 100-500 times an hour.
Anyone have any ideas about tracking down this error..?
Thanks for any help. Rolf

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Does it happen in EM or QA. If not then can you post an example of the code behind the ASP page? Also, have you looked at the execution plan to see if anything odd is happening? Finally, what do you mean when run thru Profiler, are you talking about a replaying a trace of a bad run?

kangarolf Hall of Fame Points: 3432 More actions · Answer 2

Oringinally the query was running through an ado recordset object, but whilst I was trying to trap it down I ran the same query in EM and QA and it bombed on both.

I'm stuck..!

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Have you try DBCC CHECKTABLE, could be damaged in some way. More info available in SQL BOL.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

Just a quick check. When you brought up Profiler to see what was going on, were the connections being made using TCP/IP or Named Pipes. We saw time out issues with Named Pipes. This is probably not the issue, but it's one of the first checks I do.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

kangarolf Hall of Fame Points: 3432 More actions · Answer 5

Tried DBCC;

There are 1120 rows in 43 pages for object 'redcom.products'

DBCC execution complete.

I have since dropped the subscription and then the table and then recreated and reinitialised the replication. I noticed that the bulk insert commands took an age to complete on this fairly small table (~8 minutes!).

Rolf

kangarolf Hall of Fame Points: 3432 More actions · Answer 6

The table is now refusing to open in any way..I cant get hte schema, I cant run a dbbc checktble on it...Tried dropping and renaming. Definately something criticalyl wrong with this table!

Rolf

Antares686 SSC Guru Points: 125444 More actions · Answer 7

Maybe the databse and not the tabl;e, try DBCC CHECKDB as well.

kangarolf Hall of Fame Points: 3432 More actions · Answer 8

cant do that, dont have the neccessary permissions on the remote server to run that check.

Just tried exporting the table from my DB here to there and it crashed out doing that as well but I cant find any problems with the table at this end!

Crazy problem this!

Thanks for all your help

Antares686 SSC Guru Points: 125444 More actions · Answer 9

Yeah, you obviously have an unstable table or db going on there. I would make sure you have a copy of the data and be prepared to have to drop the databse then recreate to correct.

kangarolf Hall of Fame Points: 3432 More actions · Answer 10

data isnt a problem as its all replicated stuff except for one or two tables..no DTS or jobs or SPs.

I will get the sysadmin to drop and recreate the table tomorrow.

Thanks

Rolf

Antares686 SSC Guru Points: 125444 More actions · Answer 11

Antares686

SSC Guru

Points: 125444

November 7, 2002 at 5:25 pm

#440793

Let us know if anything changes.

kangarolf Hall of Fame Points: 3432 More actions · Answer 12

Hi,

Well I stopped all the jobs that update the local database from source data. Deleted the remote tables then exported them form the local to the remote DB. Then recreated replication without any snapshot and so far so good. Have no idea what caused the problem though!

Thanks for all the help.

Rolf.

kangarolf Hall of Fame Points: 3432 More actions · Answer 13

OK problem is back!

Dont know if this is related but when I watch the replication every now and then it comes up with a 3 transaction change that involves over 5000 commands and this seems to lock it all up for a bit. Cant imagine where it is getting 5000 commands from as all it is doing is updated stock figures and adding the occasional new product.

During the transactional update the web site timesout trying to get READ only access to those tables.

Thanks

Rolf

kangarolf Hall of Fame Points: 3432 More actions · Answer 14

Another update.

Well it definately appears to be the subscription that is causing the problems. If I stop synchronising the subscriber the timeouts stop. I cannot trace down a list of the commands the log reader agent is using so I cant at the minute check to see what its doing.

Could this be an error at the subscriber or is it most likely an error at the distributor..?

Thanks

Rolf