Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through
a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advanceUse Profiler to trace the statements sent to sql server. Start by using the
duration template and filter for duration greater than, let us say, 1000
milliseconds or whatever amount you decide. See BOL for more information
about "Profiler".
AMB
"RG" wrote:

> I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
> application written accessing this SQL Server 7 db. Unfortunately, throug
h a
> series of workstation upgrades, the source code was lost.
> Recently, the performance of table inserts degraded significantly. I am
> trying figure out as to what is causing it.
> I am guessing there is some sort of locking problem going on.
> Is there a utility which allows you to trace ODBC activity?
> If anyone knows of any other utility or method to diagnose the problem, I
> would greatly appreciate help.
> Thanks in advance|||Thanks for your help.
It appears the following statement is a problem
sp_cursorfetch 422977628, 2, 0, 1.
How can I find the query behind this cursor?
Thanks
"Alejandro Mesa" wrote:
> Use Profiler to trace the statements sent to sql server. Start by using th
e
> duration template and filter for duration greater than, let us say, 1000
> milliseconds or whatever amount you decide. See BOL for more information
> about "Profiler".
>
> AMB
> "RG" wrote:
>|||See if this helps:
Server Side Cursors and ADO Cursor Types
http://www.sqlteam.com/item.asp?ItemID=11842
AMB
"RG" wrote:
> Thanks for your help.
> It appears the following statement is a problem
> sp_cursorfetch 422977628, 2, 0, 1.
> How can I find the query behind this cursor?
> Thanks
> "Alejandro Mesa" wrote:
>|||It looks like this is it. It also appears that if you don't have the source
,
there is not much you could do on the database side to improve this
performance.
"Alejandro Mesa" wrote:
> See if this helps:
> Server Side Cursors and ADO Cursor Types
> http://www.sqlteam.com/item.asp?ItemID=11842
>
> AMB
> "RG" wrote:
>

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advance
If you think there is some locking, blocking going on then
you likely want to monitor this in SQL Server. You can start
by using the system stored procedures sp_lock, sp_who2 and
querying master..sysprocesses.
You may also want to take a look at the following article:
INF: How to Monitor SQL Server 7.0 Blocking
http://support.microsoft.com/?id=251004
-Sue
On Thu, 17 Feb 2005 08:39:09 -0800, "RG"
<RG@.discussions.microsoft.com> wrote:

>I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
>application written accessing this SQL Server 7 db. Unfortunately, through a
>series of workstation upgrades, the source code was lost.
>Recently, the performance of table inserts degraded significantly. I am
>trying figure out as to what is causing it.
>I am guessing there is some sort of locking problem going on.
>Is there a utility which allows you to trace ODBC activity?
>If anyone knows of any other utility or method to diagnose the problem, I
>would greatly appreciate help.
>Thanks in advance

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advance
my 1st guess would be index fragmentation.
1. do your tables have a clustered index (They should have in most cases)
2. do your indexes (if present) have a fill factor of Less Than 100% and
Greater than 0% (they should if you are inserting and updating a lot AND if
the clustered index is NOT an identity or some other monotomically
incrementing value)
3. are your indexes (if Present) fragmented ?
(Check by using DBCC ShowContig (TableName)
Then look at the "Scan Density" Number. It should be >=80% or
performance will start to degrade
Cheers,
Greg Jackson
PDX, Oregon
|||Thanks for your help.
It appears the following statement is a problem
sp_cursorfetch 422977628, 2, 0, 1.
It takes over a second to execute it.
How can I find the query behind this cursor?
Thanks
"pdxJaxon" wrote:

> my 1st guess would be index fragmentation.
> 1. do your tables have a clustered index (They should have in most cases)
> 2. do your indexes (if present) have a fill factor of Less Than 100% and
> Greater than 0% (they should if you are inserting and updating a lot AND if
> the clustered index is NOT an identity or some other monotomically
> incrementing value)
> 3. are your indexes (if Present) fragmented ?
> (Check by using DBCC ShowContig (TableName)
> Then look at the "Scan Density" Number. It should be >=80% or
> performance will start to degrade
>
>
> Cheers,
> Greg Jackson
> PDX, Oregon
>
>
>

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through
a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advancemy 1st guess would be index fragmentation.
1. do your tables have a clustered index (They should have in most cases)
2. do your indexes (if present) have a fill factor of Less Than 100% and
Greater than 0% (they should if you are inserting and updating a lot AND if
the clustered index is NOT an identity or some other monotomically
incrementing value)
3. are your indexes (if Present) fragmented ?
(Check by using DBCC ShowContig (TableName)
Then look at the "Scan Density" Number. It should be >=80% or
performance will start to degrade
Cheers,
Greg Jackson
PDX, Oregon|||Thanks for your help.
It appears the following statement is a problem
sp_cursorfetch 422977628, 2, 0, 1.
It takes over a second to execute it.
How can I find the query behind this cursor?
Thanks
"pdxJaxon" wrote:

> my 1st guess would be index fragmentation.
> 1. do your tables have a clustered index (They should have in most cases)
> 2. do your indexes (if present) have a fill factor of Less Than 100% and
> Greater than 0% (they should if you are inserting and updating a lot AND i
f
> the clustered index is NOT an identity or some other monotomically
> incrementing value)
> 3. are your indexes (if Present) fragmented ?
> (Check by using DBCC ShowContig (TableName)
> Then look at the "Scan Density" Number. It should be >=80% or
> performance will start to degrade
>
>
> Cheers,
> Greg Jackson
> PDX, Oregon
>
>
>

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advancemy 1st guess would be index fragmentation.
1. do your tables have a clustered index (They should have in most cases)
2. do your indexes (if present) have a fill factor of Less Than 100% and
Greater than 0% (they should if you are inserting and updating a lot AND if
the clustered index is NOT an identity or some other monotomically
incrementing value)
3. are your indexes (if Present) fragmented ?
(Check by using DBCC ShowContig (TableName)
Then look at the "Scan Density" Number. It should be >=80% or
performance will start to degrade
Cheers,
Greg Jackson
PDX, Oregon|||Thanks for your help.
It appears the following statement is a problem
sp_cursorfetch 422977628, 2, 0, 1.
It takes over a second to execute it.
How can I find the query behind this cursor?
Thanks
"pdxJaxon" wrote:
> my 1st guess would be index fragmentation.
> 1. do your tables have a clustered index (They should have in most cases)
> 2. do your indexes (if present) have a fill factor of Less Than 100% and
> Greater than 0% (they should if you are inserting and updating a lot AND if
> the clustered index is NOT an identity or some other monotomically
> incrementing value)
> 3. are your indexes (if Present) fragmented ?
> (Check by using DBCC ShowContig (TableName)
> Then look at the "Scan Density" Number. It should be >=80% or
> performance will start to degrade
>
>
> Cheers,
> Greg Jackson
> PDX, Oregon
>
>
>

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through
a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advanceIf you think there is some locking, blocking going on then
you likely want to monitor this in SQL Server. You can start
by using the system stored procedures sp_lock, sp_who2 and
querying master..sysprocesses.
You may also want to take a look at the following article:
INF: How to Monitor SQL Server 7.0 Blocking
http://support.microsoft.com/?id=251004
-Sue
On Thu, 17 Feb 2005 08:39:09 -0800, "RG"
<RG@.discussions.microsoft.com> wrote:

>I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
>application written accessing this SQL Server 7 db. Unfortunately, through
a
>series of workstation upgrades, the source code was lost.
>Recently, the performance of table inserts degraded significantly. I am
>trying figure out as to what is causing it.
>I am guessing there is some sort of locking problem going on.
>Is there a utility which allows you to trace ODBC activity?
>If anyone knows of any other utility or method to diagnose the problem, I
>would greatly appreciate help.
>Thanks in advance