intermittent stalling on a query

I've been trying to diagonose a strange problem with an application that I
inherited. Every few days, SQL Server will take between 3 to 4 minutes to
perform a really simple update.
The application that runs against the database is a real-time monitoring
program that communicates to a number of devices. It probably issues about
a dozen queries every second, 80% updates, 10% selects, 10% inserts. There
are no deletes being done. For the most part, all of the queries run in
less than a second. In the application, the CommandTimeout value on the
queries is set to 2 minutes. The application is setup to restart itself on
when a timeout occurs. And since the CommandTimeout is not high enough (2
mins vs 3+ mins), the application will into a non-stop cycle of restarting
itself... Until I go into Query Analyzer, and run a simple update statement
against an arbitrary table; which will take between 3 to 4 minutes to
complete, and then afterwards, the application will start running fine
again. I've seen instances where the application would stay in a restart
cycle for days at a time.
There does not appear to be a deadlock or open transaction. I've tried
running SP_WHO2, SP_LOCK, DBCC OPENTRAN, DBCC CHECKDB and didn't see any
issues.
The application is running on the same computer as the SQL server. It is
the sole application running against the database. The application uses the
SQLOLEDB provider.
The database is about 10 MB, the transaction log is 3 GB (mostly empty).
Server properties: Windows Server 2003 Standard Edition, Pentium 4 2.8 GHz
(hyperthreading-enabled), 512 MB RAM, and plenty of free drive space.
SQL Server settings:
- SQL Server 2000 8.00.818 (SP3)
- Memory is setup for dynamic allocation
- Processor is setup to use the two "instances"
- "Implicit transactions" is checked
- "Close cursor on COMMIT" is checked
- "Auto Grow" is enabled for both the data file and transaction log
- "Auto Update Statistics", "Auto Create Statistics", and "Torn Page
Detection" are checked
There are jobs to backup both the data file and transaction log.
I noticed that the timeouts tend to be from one of two queries (but about
99% of the time, it's one of these queries being called). Each of the
queries is literally a single update statement touching a single table,
wrapped in a stored procedure. And the tables that they update have at most
a dozen records in them.
Things, I've tried,
- I thought it was auto grow causing the stall, but I manually increased the
database size, and the application did not hiccup.
- I also thought it might be statistics related, so I ran a "sp_updatestats"
against the database. The next day, the problem came up again.
- I've tried adding "with recompile" to the stored procedure with no luck
either.
This seems like it might be server configuration issue, but I'm not sure
what else to check.
TYIA for any advice.My bet is still on the Autogrow. First off I assume you mean the db is 10GB
and not MB? If autogrow kicks in it will attempt to grow the file by about
1GB. On a slow disk subsystem this can take a while. If the connection
that issued the command that forced the autogrow times out the growth will
be canceled and you are back to where you started. The next insert or
update may force another growth which may or may not succeed. It is all
about timing and resources. When you manually grow the DB there is no
problem because there is not a statement (insert or update) in the process
of executing that forced the growth so it will succeed. You should always
have plenty of free space in the db and autogrow should never kick in. When
you run low you need to manually grow it at the appropriate time so you
always have enough free space. If you are that low you are too low anyway
since the first time you do a reindex you will need more space. If you
have auto shrink turned on then turn it off immediately. You should also
change the growth to be a fixed amount vs. a percentage. Make it an amount
that can grow in no more than 30 seconds or so. That way you won't get a
timeout if it does kick in.
Andrew J. Kelly SQL MVP
"John Smith" <john@.smith.com> wrote in message
news:OtaoqmFIFHA.2356@.TK2MSFTNGP12.phx.gbl...
> I've been trying to diagonose a strange problem with an application that I
> inherited. Every few days, SQL Server will take between 3 to 4 minutes to
> perform a really simple update.
> The application that runs against the database is a real-time monitoring
> program that communicates to a number of devices. It probably issues
> about a dozen queries every second, 80% updates, 10% selects, 10% inserts.
> There are no deletes being done. For the most part, all of the queries
> run in less than a second. In the application, the CommandTimeout value
> on the queries is set to 2 minutes. The application is setup to restart
> itself on when a timeout occurs. And since the CommandTimeout is not high
> enough (2 mins vs 3+ mins), the application will into a non-stop cycle of
> restarting itself... Until I go into Query Analyzer, and run a simple
> update statement against an arbitrary table; which will take between 3 to
> 4 minutes to complete, and then afterwards, the application will start
> running fine again. I've seen instances where the application would stay
> in a restart cycle for days at a time.
> There does not appear to be a deadlock or open transaction. I've tried
> running SP_WHO2, SP_LOCK, DBCC OPENTRAN, DBCC CHECKDB and didn't see any
> issues.
> The application is running on the same computer as the SQL server. It is
> the sole application running against the database. The application uses
> the SQLOLEDB provider.
> The database is about 10 MB, the transaction log is 3 GB (mostly empty).
> Server properties: Windows Server 2003 Standard Edition, Pentium 4 2.8 GHz
> (hyperthreading-enabled), 512 MB RAM, and plenty of free drive space.
> SQL Server settings:
> - SQL Server 2000 8.00.818 (SP3)
> - Memory is setup for dynamic allocation
> - Processor is setup to use the two "instances"
> - "Implicit transactions" is checked
> - "Close cursor on COMMIT" is checked
> - "Auto Grow" is enabled for both the data file and transaction log
> - "Auto Update Statistics", "Auto Create Statistics", and "Torn Page
> Detection" are checked
> There are jobs to backup both the data file and transaction log.
> I noticed that the timeouts tend to be from one of two queries (but about
> 99% of the time, it's one of these queries being called). Each of the
> queries is literally a single update statement touching a single table,
> wrapped in a stored procedure. And the tables that they update have at
> most a dozen records in them.
> Things, I've tried,
> - I thought it was auto grow causing the stall, but I manually increased
> the database size, and the application did not hiccup.
> - I also thought it might be statistics related, so I ran a
> "sp_updatestats" against the database. The next day, the problem came up
> again.
> - I've tried adding "with recompile" to the stored procedure with no luck
> either.
> This seems like it might be server configuration issue, but I'm not sure
> what else to check.
> TYIA for any advice.
>|||Thanks, I didn't realize the part about the autogrow being canceled on a
timeout.
I checked again, and was off a bit. It's at 30 MB, but still tiny. It
might have been the transaction log that was expanding (is it affected the
same way, by a connection timeout?).
I'll keep a closer eye on this then. Thanks again.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eRL2YkGIFHA.3336@.TK2MSFTNGP10.phx.gbl...
> My bet is still on the Autogrow. First off I assume you mean the db is
> 10GB and not MB? If autogrow kicks in it will attempt to grow the file by
> about 1GB. On a slow disk subsystem this can take a while. If the
> connection that issued the command that forced the autogrow times out the
> growth will be canceled and you are back to where you started. The next
> insert or update may force another growth which may or may not succeed. It
> is all about timing and resources. When you manually grow the DB there is
> no problem because there is not a statement (insert or update) in the
> process of executing that forced the growth so it will succeed. You
> should always have plenty of free space in the db and autogrow should
> never kick in. When you run low you need to manually grow it at the
> appropriate time so you always have enough free space. If you are that
> low you are too low anyway since the first time you do a reindex you will
> need more space. If you have auto shrink turned on then turn it off
> immediately. You should also change the growth to be a fixed amount vs. a
> percentage. Make it an amount that can grow in no more than 30 seconds or
> so. That way you won't get a timeout if it does kick in.
> --
> Andrew J. Kelly SQL MVP
>
> "John Smith" <john@.smith.com> wrote in message
> news:OtaoqmFIFHA.2356@.TK2MSFTNGP12.phx.gbl...
>|||Yes the tran log is essentially the same behavior in that any statements
that require logging will sit and wait until the log is finished expanding.
If you have a 30MB db and a 3GB log you are not doing something right. You
stated that you are doing regular Full and Log backups. If that were the
case and you didn't have any long running open transactions your log file
should only be a few hundred MB at best. Try running DBCC OPENTRAN and see
what it tells you. Also check your backups to make sure they are actually
happening with no errors.
Andrew J. Kelly SQL MVP
"John Smith" <john@.smith.com> wrote in message
news:OX4$t1IIFHA.608@.TK2MSFTNGP10.phx.gbl...
> Thanks, I didn't realize the part about the autogrow being canceled on a
> timeout.
> I checked again, and was off a bit. It's at 30 MB, but still tiny. It
> might have been the transaction log that was expanding (is it affected the
> same way, by a connection timeout?).
> I'll keep a closer eye on this then. Thanks again.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eRL2YkGIFHA.3336@.TK2MSFTNGP10.phx.gbl...
>