Error 17805

We have an application that is using our SQL Server 2000 as a repository.
Occassionaly the application displays a "Severe Error Occured" message when
trying to execute a command against the DB.
The SQL Logs show "Error 17805 - Invalid buffer received from client" has
anyone any idea what this means?
We did not write the application so we have no way of knowing what it's
trying to do other than insert data into the database.
Thanks, for any help
BrianHi,
Normally this error occurs due to Network problems and are not critical at
all.
Please look in to the below link for more details;
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=0a0801c3
66fc%24f930e9f0%24a401280a%40phx.gbl&rnum=6&prev=/groups%3Fq%3D17805%2Bin%2B
sql%2Bserver%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3D0a0801c366
fc%2524f930e9f0%2524a401280a%2540phx.gbl%26rnum%3D6
Thanks
Hari
MCDBA
"Brian" <bfordSPAMOFF@.markem.com> wrote in message
news:#cUCzil6DHA.1852@.TK2MSFTNGP10.phx.gbl...
> We have an application that is using our SQL Server 2000 as a repository.
> Occassionaly the application displays a "Severe Error Occured" message
when
> trying to execute a command against the DB.
> The SQL Logs show "Error 17805 - Invalid buffer received from client" has
> anyone any idea what this means?
> We did not write the application so we have no way of knowing what it's
> trying to do other than insert data into the database.
> Thanks, for any help
> Brian
>|||Hari,
Thanks for the response, unfortunately I know the network is not the
problem, and whilst this is not causing any issues on the SQL DB, it is
critical as far as the application is concerned as it doesn't work :(
I have check MS KB and I have the latest SP installed, I have an identical
setup on another box and everything is working fine.
Any other clues?
Thanks again for your input
Brian
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:uFYF7ol6DHA.2404@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Normally this error occurs due to Network problems and are not critical
at
> all.
> Please look in to the below link for more details;
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=0a0801c3
>
66fc%24f930e9f0%24a401280a%40phx.gbl&rnum=6&prev=/groups%3Fq%3D17805%2Bin%2B
>
sql%2Bserver%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3D0a0801c366
> fc%2524f930e9f0%2524a401280a%2540phx.gbl%26rnum%3D6
> Thanks
> Hari
> MCDBA
>
> "Brian" <bfordSPAMOFF@.markem.com> wrote in message
> news:#cUCzil6DHA.1852@.TK2MSFTNGP10.phx.gbl...
> > We have an application that is using our SQL Server 2000 as a
repository.
> > Occassionaly the application displays a "Severe Error Occured" message
> when
> > trying to execute a command against the DB.
> >
> > The SQL Logs show "Error 17805 - Invalid buffer received from client"
has
> > anyone any idea what this means?
> >
> > We did not write the application so we have no way of knowing what it's
> > trying to do other than insert data into the database.
> >
> > Thanks, for any help
> >
> > Brian
> >
> >
>|||Hi,
From the same machine, Did you tried connecting to SQL server using Query
Analyzer with TCP/IP as well as Named pipes seperately ?.
Thanks
Hari
MCDBA
"Brian" <bfordSPAMOFF@.markem.com> wrote in message
news:#FFbR1l6DHA.2488@.TK2MSFTNGP09.phx.gbl...
> Hari,
> Thanks for the response, unfortunately I know the network is not the
> problem, and whilst this is not causing any issues on the SQL DB, it is
> critical as far as the application is concerned as it doesn't work :(
> I have check MS KB and I have the latest SP installed, I have an identical
> setup on another box and everything is working fine.
> Any other clues?
> Thanks again for your input
> Brian
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:uFYF7ol6DHA.2404@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > Normally this error occurs due to Network problems and are not critical
> at
> > all.
> >
> > Please look in to the below link for more details;
> >
> >
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=0a0801c3
> >
>
66fc%24f930e9f0%24a401280a%40phx.gbl&rnum=6&prev=/groups%3Fq%3D17805%2Bin%2B
> >
>
sql%2Bserver%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3D0a0801c366
> > fc%2524f930e9f0%2524a401280a%2540phx.gbl%26rnum%3D6
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Brian" <bfordSPAMOFF@.markem.com> wrote in message
> > news:#cUCzil6DHA.1852@.TK2MSFTNGP10.phx.gbl...
> > > We have an application that is using our SQL Server 2000 as a
> repository.
> > > Occassionaly the application displays a "Severe Error Occured" message
> > when
> > > trying to execute a command against the DB.
> > >
> > > The SQL Logs show "Error 17805 - Invalid buffer received from client"
> has
> > > anyone any idea what this means?
> > >
> > > We did not write the application so we have no way of knowing what
it's
> > > trying to do other than insert data into the database.
> > >
> > > Thanks, for any help
> > >
> > > Brian
> > >
> > >
> >
> >
>|||I can connect to it just fine, the application that I am having trouble with
is located on a seperate server and, for the most part, has no trouble
connecting to the DB. It's just certain screens that fail with this error.
The SQL Server itself is serving many applications on our network connecting
via TCP and Named Pipes, it's just this one app that is giving us a problem.
I think it's time to get in touch with application vendor to find out what
sort of command it's trying to execute.
Thanks,
Brian
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:e2LqXLn6DHA.1752@.tk2msftngp13.phx.gbl...
> Hi,
> From the same machine, Did you tried connecting to SQL server using Query
> Analyzer with TCP/IP as well as Named pipes seperately ?.
> Thanks
> Hari
> MCDBA
> "Brian" <bfordSPAMOFF@.markem.com> wrote in message
> news:#FFbR1l6DHA.2488@.TK2MSFTNGP09.phx.gbl...
> > Hari,
> >
> > Thanks for the response, unfortunately I know the network is not the
> > problem, and whilst this is not causing any issues on the SQL DB, it is
> > critical as far as the application is concerned as it doesn't work :(
> >
> > I have check MS KB and I have the latest SP installed, I have an
identical
> > setup on another box and everything is working fine.
> >
> > Any other clues?
> >
> > Thanks again for your input
> >
> > Brian
> >
> >
> > "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> > news:uFYF7ol6DHA.2404@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > Normally this error occurs due to Network problems and are not
critical
> > at
> > > all.
> > >
> > > Please look in to the below link for more details;
> > >
> > >
> >
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=0a0801c3
> > >
> >
>
66fc%24f930e9f0%24a401280a%40phx.gbl&rnum=6&prev=/groups%3Fq%3D17805%2Bin%2B
> > >
> >
>
sql%2Bserver%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3D0a0801c366
> > > fc%2524f930e9f0%2524a401280a%2540phx.gbl%26rnum%3D6
> > >
> > > Thanks
> > > Hari
> > > MCDBA
> > >
> > >
> > >
> > > "Brian" <bfordSPAMOFF@.markem.com> wrote in message
> > > news:#cUCzil6DHA.1852@.TK2MSFTNGP10.phx.gbl...
> > > > We have an application that is using our SQL Server 2000 as a
> > repository.
> > > > Occassionaly the application displays a "Severe Error Occured"
message
> > > when
> > > > trying to execute a command against the DB.
> > > >
> > > > The SQL Logs show "Error 17805 - Invalid buffer received from
client"
> > has
> > > > anyone any idea what this means?
> > > >
> > > > We did not write the application so we have no way of knowing what
> it's
> > > > trying to do other than insert data into the database.
> > > >
> > > > Thanks, for any help
> > > >
> > > > Brian
> > > >
> > > >
> > >
> > >
> >
> >
>

Error 17805

We have an application that is using our SQL Server 2000 as a repository.
Occassionaly the application displays a "Severe Error Occured" message when
trying to execute a command against the DB.
The SQL Logs show "Error 17805 - Invalid buffer received from client" has
anyone any idea what this means?
We did not write the application so we have no way of knowing what it's
trying to do other than insert data into the database.
Thanks, for any help
BrianHi,
Normally this error occurs due to Network problems and are not critical at
all.
Please look in to the below link for more details;
http://groups.google.com/groups?hl=...hreadm=0a0801c3
66fc%24f930e9f0%24a401280a%40phx.gbl&rnum=6&prev=/groups%3Fq%3D17805%2Bin%2B
sql%2Bserver%26hl%3Den%26lr%3D%26ie%3DUT
F-8%26oe%3DUTF-8%26selm%3D0a0801c366
fc%2524f930e9f0%2524a401280a%2540phx.gbl%26rnum%3D6
Thanks
Hari
MCDBA
"Brian" <bfordSPAMOFF@.markem.com> wrote in message
news:#cUCzil6DHA.1852@.TK2MSFTNGP10.phx.gbl...

quote:

---

> We have an application that is using our SQL Server 2000 as a repository.
> Occassionaly the application displays a "Severe Error Occured" message

---

when

quote:

---

> trying to execute a command against the DB.
> The SQL Logs show "Error 17805 - Invalid buffer received from client" has
> anyone any idea what this means?
> We did not write the application so we have no way of knowing what it's
> trying to do other than insert data into the database.
> Thanks, for any help
> Brian
>

---

|||Hari,
Thanks for the response, unfortunately I know the network is not the
problem, and whilst this is not causing any issues on the SQL DB, it is
critical as far as the application is concerned as it doesn't work
I have check MS KB and I have the latest SP installed, I have an identical
setup on another box and everything is working fine.
Any other clues?
Thanks again for your input
Brian
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:uFYF7ol6DHA.2404@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hi,
> Normally this error occurs due to Network problems and are not critical

---

at

quote:

---

> all.
> Please look in to the below link for more details;
>

---

http://groups.google.com/groups?hl=...hreadm=0a0801c3

quote:

---

>

---

66fc%24f930e9f0%24a401280a%40phx.gbl&rnum=6&prev=/ groups%3Fq%3D17805%2Bin%2B

quote:

---

darkred">
>

---

sql%2Bserver%26hl%3Den%26lr%3D%26ie%3DUT
F-8%26oe%3DUTF- 8%26selm%3D0a0801c366

quote:

---

ed">
> fc%2524f930e9f0%2524a401280a%2540phx.gbl%26rnum%3D6
> Thanks
> Hari
> MCDBA
>
> "Brian" <bfordSPAMOFF@.markem.com> wrote in message
> news:#cUCzil6DHA.1852@.TK2MSFTNGP10.phx.gbl...
repository.[QUOTE]
> when
has[QUOTE]
>

---

|||Hi,
From the same machine, Did you tried connecting to SQL server using Query
Analyzer with TCP/IP as well as Named pipes seperately ?.
Thanks
Hari
MCDBA
"Brian" <bfordSPAMOFF@.markem.com> wrote in message
news:#FFbR1l6DHA.2488@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hari,
> Thanks for the response, unfortunately I know the network is not the
> problem, and whilst this is not causing any issues on the SQL DB, it is
> critical as far as the application is concerned as it doesn't work
> I have check MS KB and I have the latest SP installed, I have an identical
> setup on another box and everything is working fine.
> Any other clues?
> Thanks again for your input
> Brian
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:uFYF7ol6DHA.2404@.TK2MSFTNGP11.phx.gbl...
> at
>

---

http://groups.google.com/groups?hl=...hreadm=0a0801c3

quote:

---

>

---

66fc%24f930e9f0%24a401280a%40phx.gbl&rnum=6&prev=/ groups%3Fq%3D17805%2Bin%2B

quote:

---

darkred">
>

---

sql%2Bserver%26hl%3Den%26lr%3D%26ie%3DUT
F-8%26oe%3DUTF- 8%26selm%3D0a0801c366

quote:

---

ed">
> repository.
> has
it's[QUOTE]
>

---

|||I can connect to it just fine, the application that I am having trouble with
is located on a seperate server and, for the most part, has no trouble
connecting to the DB. It's just certain screens that fail with this error.
The SQL Server itself is serving many applications on our network connecting
via TCP and Named Pipes, it's just this one app that is giving us a problem.
I think it's time to get in touch with application vendor to find out what
sort of command it's trying to execute.
Thanks,
Brian
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:e2LqXLn6DHA.1752@.tk2msftngp13.phx.gbl...

quote:

---

> Hi,
> From the same machine, Did you tried connecting to SQL server using Query
> Analyzer with TCP/IP as well as Named pipes seperately ?.
> Thanks
> Hari
> MCDBA
> "Brian" <bfordSPAMOFF@.markem.com> wrote in message
> news:#FFbR1l6DHA.2488@.TK2MSFTNGP09.phx.gbl...
identical[QUOTE]
critical[QUOTE]
>

---

http://groups.google.com/groups?hl=...hreadm=0a0801c3

quote:

---

>

---

66fc%24f930e9f0%24a401280a%40phx.gbl&rnum=6&prev=/ groups%3Fq%3D17805%2Bin%2B

quote:

---

darkred">
>

---

sql%2Bserver%26hl%3Den%26lr%3D%26ie%3DUT
F-8%26oe%3DUTF- 8%26selm%3D0a0801c366

quote:

---

ed">
message[QUOTE]
client"[QUOTE]
> it's
>

---

Erratic Performance of SQL Server

Hello,

We have a complex system with many stored procedures, the same procedures run every day at about the same time. We are noticing severe fluctuations in performance. One day a procedure will take 150 minutes, the next day 250 minutes, the next day 100 minutes. A graph of the performance looks like a voice graph or a lie detector for a criminal. We are using SQL Server 2000 on an Itanium. Any suggestions or hints about how to stablize? This is happening for all of our procedures that run 24 hours a day.

Hi.

Are you running with SQL Server 2000 service pack 4 (for build 2039) and the last 2000 cumulative hotfix package (for build 2187)?

Regards,

Gary.

|||Stored procedure performance is always going to be directly relative to the load on the server, the number of rows being processed, the nature of the queries, i.e. read only versus updates and, in particular, on the performance of the I/O devices which can certainly vary if your stored procedures are competing against other processes trying to access the same data.

What is the stored procedure doing and what kind of data volumes are we talking about, i.e. a thousand rows, 10 million rows? Are there any cursors? Are there any other applications competing for the same data, i.e. an OLTP system?
|||

You might want to take the following steps:

1. Check the DBCC SHOW_STATISTICS and rowmodctr values (under the sysindexes table -- The rowmodctr value should be as close to ZERO as possible) and find out if the statistics for the database and the tables involved are out of date. If yes, please update the statistics with a full scan or 100% sampling rate.

2. Make sure that you are not running into a parameter sniffing issue. Please refer the following article for more details:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

http://msdn2.microsoft.com/en-us/library/ms190439.aspx

Sporadic bursts in SP performance which leads to inconsistent duration for a stored procedure would be due to recompilation of the SP with a bad input parameter which generates a bad plan.

Also, try and see if recompiling the stored procedure at that particular time helps.

|||You need to itemize your durations. If you run Profiler, you will see real execution costs, such as amount of CPU and number of reads/writes. Are these values wildly different from day to day? Note that different durations may be caused by:
- locking, when you procedure spends significant time in lock waiting state;
- network delays. Your procedure may be waiting for the client to receive the result sets.

For instance, you can run one and the same query twice and get the same CPU, the same reads, but very different duration, because one time there were no exclusive locks to wait for, and the other time the procedure spent most of the time in lock ewaiting state. Another example is when you invoke a query from SSMS, and most of the duration is spent by SSMS drawing a grid. If you switch to text mode and rerun the query, you may get a shorter duration.|||

Thanks for your replies, I'll look at these in more detail, but I want to provide some additional information.

We are running SQL Server 2000, Enterprise edition, 64-bit, version 8.00.2039 SP4. Our server is not shared by any other application, but we have two instances with concurrent processing. The two instances use separate databases, but each instance has 3 job queues that can run procesing tasks simultaneously. Locking could be an issue on one instance, but not on the other instance. We are observing the same erratic processing times on both instances. Our SQL server uses a SAN, which is used by other applications, but the processing performed by the other applications is very light. We have several different procedures, most are not parameterized. Some of our longest running stored procedures perform a fairly simple SELECT INTO with joins on master data tables -- but these procedures are the most erratic with regard to different processing times. The data volumes for these procedures is up to about 20 million records. Locking is not an issue in these procedures. Since all of our procedures seem to be erratic, I'm wondering if there might be a problem with the SQL server optimizer in our configuration.

|||Have you monitored your TempDB's? If they're too small, it will have to keep extending which is a serious drag on performance. If your stored procedures are doing a lot of sorts, group by's, and are using a lot of temp tables this might add further credence to the TempDB issue. Since you're using SELECT INTO the operations are unlogged so I doubt that's your problem. You also might monitor your QIO's on your SAN devices to see if there is a lot of contention, particularly on the device with TempDB on it. (If at all possible you might move the TempDB's to their own devices.) You might also consider creating one TempDB file for each processor if you haven't already and turning on Traceflag 1118.

Just some thoughts. Hope it helps.

Erratic Performance of SQL Server

Hello,

We have a complex system with many stored procedures, the same procedures run every day at about the same time. We are noticing severe fluctuations in performance. One day a procedure will take 150 minutes, the next day 250 minutes, the next day 100 minutes. A graph of the performance looks like a voice graph or a lie detector for a criminal. We are using SQL Server 2000 on an Itanium. Any suggestions or hints about how to stablize? This is happening for all of our procedures that run 24 hours a day.

Hi.

Are you running with SQL Server 2000 service pack 4 (for build 2039) and the last 2000 cumulative hotfix package (for build 2187)?

Regards,

Gary.

|||Stored procedure performance is always going to be directly relative to the load on the server, the number of rows being processed, the nature of the queries, i.e. read only versus updates and, in particular, on the performance of the I/O devices which can certainly vary if your stored procedures are competing against other processes trying to access the same data.

What is the stored procedure doing and what kind of data volumes are we talking about, i.e. a thousand rows, 10 million rows? Are there any cursors? Are there any other applications competing for the same data, i.e. an OLTP system?
|||

You might want to take the following steps:

1. Check the DBCC SHOW_STATISTICS and rowmodctr values (under the sysindexes table -- The rowmodctr value should be as close to ZERO as possible) and find out if the statistics for the database and the tables involved are out of date. If yes, please update the statistics with a full scan or 100% sampling rate.

2. Make sure that you are not running into a parameter sniffing issue. Please refer the following article for more details:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

http://msdn2.microsoft.com/en-us/library/ms190439.aspx

Sporadic bursts in SP performance which leads to inconsistent duration for a stored procedure would be due to recompilation of the SP with a bad input parameter which generates a bad plan.

Also, try and see if recompiling the stored procedure at that particular time helps.

|||You need to itemize your durations. If you run Profiler, you will see real execution costs, such as amount of CPU and number of reads/writes. Are these values wildly different from day to day? Note that different durations may be caused by:
- locking, when you procedure spends significant time in lock waiting state;
- network delays. Your procedure may be waiting for the client to receive the result sets.

For instance, you can run one and the same query twice and get the same CPU, the same reads, but very different duration, because one time there were no exclusive locks to wait for, and the other time the procedure spent most of the time in lock ewaiting state. Another example is when you invoke a query from SSMS, and most of the duration is spent by SSMS drawing a grid. If you switch to text mode and rerun the query, you may get a shorter duration.|||

Thanks for your replies, I'll look at these in more detail, but I want to provide some additional information.

We are running SQL Server 2000, Enterprise edition, 64-bit, version 8.00.2039 SP4. Our server is not shared by any other application, but we have two instances with concurrent processing. The two instances use separate databases, but each instance has 3 job queues that can run procesing tasks simultaneously. Locking could be an issue on one instance, but not on the other instance. We are observing the same erratic processing times on both instances. Our SQL server uses a SAN, which is used by other applications, but the processing performed by the other applications is very light. We have several different procedures, most are not parameterized. Some of our longest running stored procedures perform a fairly simple SELECT INTO with joins on master data tables -- but these procedures are the most erratic with regard to different processing times. The data volumes for these procedures is up to about 20 million records. Locking is not an issue in these procedures. Since all of our procedures seem to be erratic, I'm wondering if there might be a problem with the SQL server optimizer in our configuration.

|||Have you monitored your TempDB's? If they're too small, it will have to keep extending which is a serious drag on performance. If your stored procedures are doing a lot of sorts, group by's, and are using a lot of temp tables this might add further credence to the TempDB issue. Since you're using SELECT INTO the operations are unlogged so I doubt that's your problem. You also might monitor your QIO's on your SAN devices to see if there is a lot of contention, particularly on the device with TempDB on it. (If at all possible you might move the TempDB's to their own devices.) You might also consider creating one TempDB file for each processor if you haven't already and turning on Traceflag 1118.

Just some thoughts. Hope it helps.

Erratic Performance of SQL Server

Hello,

We have a complex system with many stored procedures, the same procedures run every day at about the same time. We are noticing severe fluctuations in performance. One day a procedure will take 150 minutes, the next day 250 minutes, the next day 100 minutes. A graph of the performance looks like a voice graph or a lie detector for a criminal. We are using SQL Server 2000 on an Itanium. Any suggestions or hints about how to stablize? This is happening for all of our procedures that run 24 hours a day.

Hi.

Are you running with SQL Server 2000 service pack 4 (for build 2039) and the last 2000 cumulative hotfix package (for build 2187)?

Regards,

Gary.

|||Stored procedure performance is always going to be directly relative to the load on the server, the number of rows being processed, the nature of the queries, i.e. read only versus updates and, in particular, on the performance of the I/O devices which can certainly vary if your stored procedures are competing against other processes trying to access the same data.

What is the stored procedure doing and what kind of data volumes are we talking about, i.e. a thousand rows, 10 million rows? Are there any cursors? Are there any other applications competing for the same data, i.e. an OLTP system?
|||

You might want to take the following steps:

1. Check the DBCC SHOW_STATISTICS and rowmodctr values (under the sysindexes table -- The rowmodctr value should be as close to ZERO as possible) and find out if the statistics for the database and the tables involved are out of date. If yes, please update the statistics with a full scan or 100% sampling rate.

2. Make sure that you are not running into a parameter sniffing issue. Please refer the following article for more details:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

http://msdn2.microsoft.com/en-us/library/ms190439.aspx

Sporadic bursts in SP performance which leads to inconsistent duration for a stored procedure would be due to recompilation of the SP with a bad input parameter which generates a bad plan.

Also, try and see if recompiling the stored procedure at that particular time helps.

|||You need to itemize your durations. If you run Profiler, you will see real execution costs, such as amount of CPU and number of reads/writes. Are these values wildly different from day to day? Note that different durations may be caused by:
- locking, when you procedure spends significant time in lock waiting state;
- network delays. Your procedure may be waiting for the client to receive the result sets.

For instance, you can run one and the same query twice and get the same CPU, the same reads, but very different duration, because one time there were no exclusive locks to wait for, and the other time the procedure spent most of the time in lock ewaiting state. Another example is when you invoke a query from SSMS, and most of the duration is spent by SSMS drawing a grid. If you switch to text mode and rerun the query, you may get a shorter duration.|||

Thanks for your replies, I'll look at these in more detail, but I want to provide some additional information.

We are running SQL Server 2000, Enterprise edition, 64-bit, version 8.00.2039 SP4. Our server is not shared by any other application, but we have two instances with concurrent processing. The two instances use separate databases, but each instance has 3 job queues that can run procesing tasks simultaneously. Locking could be an issue on one instance, but not on the other instance. We are observing the same erratic processing times on both instances. Our SQL server uses a SAN, which is used by other applications, but the processing performed by the other applications is very light. We have several different procedures, most are not parameterized. Some of our longest running stored procedures perform a fairly simple SELECT INTO with joins on master data tables -- but these procedures are the most erratic with regard to different processing times. The data volumes for these procedures is up to about 20 million records. Locking is not an issue in these procedures. Since all of our procedures seem to be erratic, I'm wondering if there might be a problem with the SQL server optimizer in our configuration.

|||Have you monitored your TempDB's? If they're too small, it will have to keep extending which is a serious drag on performance. If your stored procedures are doing a lot of sorts, group by's, and are using a lot of temp tables this might add further credence to the TempDB issue. Since you're using SELECT INTO the operations are unlogged so I doubt that's your problem. You also might monitor your QIO's on your SAN devices to see if there is a lot of contention, particularly on the device with TempDB on it. (If at all possible you might move the TempDB's to their own devices.) You might also consider creating one TempDB file for each processor if you haven't already and turning on Traceflag 1118.

Just some thoughts. Hope it helps.