Question about OLEDB providers

Hi all,
We have an ASP application that uses a mssql2k db and was migrated from
mssql7.
It is using SQLOLEDB provider to connect to db. Recently there was an
escalation
about some performance issues. While trying to debug some long running
queries,
I came across some weird results for the problem queries - they behaved quit
e
differently when executed from Query Analyzer and when called from the app -
e.g.,
lock escalation, timeouts, etc etc. We might have some issues with the app
design...
however, I had a qn related to OLEDB provider for SQL Server and OLEDB
provider
for ODBC - just to make sure that there are no gotchas here...
1. Which of the above two drivers are used internally by Query
Analyzer/Enterprise
Manager etc?
2. Is OLEDB provider for ODBC recommended at all (MS says it is always
better to
use the native provider for SQL Server) over native provider?
TIA
paraa"Client Network Utility" (cliconfg.exe) shows what network libraries you are
using and OLEDB (for all SEM, QA and your ASP apps) on your client side. The
serverside is same regardless (cannot distinguish whether client is using
ODBC or OLEDB) and simply listens for Tabular Data Stream (TDS) traffic.
Changing from v7 to -2000 improves the optimiser stategies and degree of
parallelism [assuming you have latest Service Packs to eliminate bugs].
Mostly locking problems arise due to user contention [apps holding exclusive
locks and long-running transactions]. Suggest you use Profiler to capture
TSQL generated by apps and then replay from [several sessions of] QA to show
they are equivalent.
You can use Profiler to report on Locks(Escalation) if you suspect foul
play. Can always use SEM CurrentActivity or sp_lock or blocker_blockee (in
RK) to see whats happening engine-side
HTH
Dick
"paraa" wrote:

> Hi all,
> We have an ASP application that uses a mssql2k db and was migrated from
> mssql7.
> It is using SQLOLEDB provider to connect to db. Recently there was an
> escalation
> about some performance issues. While trying to debug some long running
> queries,
> I came across some weird results for the problem queries - they behaved qu
ite
> differently when executed from Query Analyzer and when called from the app
-
> e.g.,
> lock escalation, timeouts, etc etc. We might have some issues with the app
> design...
> however, I had a qn related to OLEDB provider for SQL Server and OLEDB
> provider
> for ODBC - just to make sure that there are no gotchas here...
> 1. Which of the above two drivers are used internally by Query
> Analyzer/Enterprise
> Manager etc?
> 2. Is OLEDB provider for ODBC recommended at all (MS says it is always
> better to
> use the native provider for SQL Server) over native provider?
> TIA
> paraa
>|||thx a bunch pal, your info helps
paraa
"Dick in UK" wrote:
> "Client Network Utility" (cliconfg.exe) shows what network libraries you a
re
> using and OLEDB (for all SEM, QA and your ASP apps) on your client side. T
he
> serverside is same regardless (cannot distinguish whether client is using
> ODBC or OLEDB) and simply listens for Tabular Data Stream (TDS) traffic.
> Changing from v7 to -2000 improves the optimiser stategies and degree of
> parallelism [assuming you have latest Service Packs to eliminate bugs].
> Mostly locking problems arise due to user contention [apps holding exclusi
ve
> locks and long-running transactions]. Suggest you use Profiler to capture
> TSQL generated by apps and then replay from [several sessions of] QA to sh
ow
> they are equivalent.
> You can use Profiler to report on Locks(Escalation) if you suspect foul
> play. Can always use SEM CurrentActivity or sp_lock or blocker_blockee (in
> RK) to see whats happening engine-side
> HTH
> Dick
> "paraa" wrote:
>