sp_tables call taking more time which causes blocking

Question

Hi,

We could see that during our perl program runs which basically connects to SQLserver to insert/delete/update data, the below is called very frequently sp_tables @table_name='NOXXTABLE'. We see that for many SPID's the call happens a lot of times.

On running sp_tables @table_name='NOXXTABLE' in SQLserver we can see that it returns no rows. It usually completes in milliseconds.

But after profiling, when we checked the runtime of the particular SQLText(sp_tables @table_name='NOXXTABLE') for the blocking SPID's, it was varying from between 0 seconds to 1 minute and was getting called very very frequently within the same SPID. The same is happening for many SPID's which again have multiple calls to sp_tables each taking a lot of time.

Is there any solution to get out of this?

Answer 1

The NOXXTABLE is used by DBD::ODBC's ping method. If you call DBI's ping method with DBD::ODBC it does a tables('', '', 'NOXXTABLE', ''). If you want to do it less call ping less or override DBD::ODBC's ping method with other code more appropriate for your system.