Very Computer

by **asdf** » Thu, 07 Dec 2000 04:00:00

I have a 7.0 sp2 database server that has several databases running on it.
One of the databases is fairly large (1.8GB+) and has several stored
procedures that aggregate information in the database. None of the
procedures has changed, yet the stored procedures take literally 100'sX more
time to process and the disk on the server is running amok.

Any thoughts? This thing is killing me.

by **Patrick Loga** » Thu, 07 Dec 2000 04:00:00

Have you ran SQL Profiler to look at the activity? Are the index statistics
getting dated and is the query optimizer making poor index selection
choices? I'd look at some execution plans provided by the SQL Profiler. Is
it possible the stored procs have been in the proc cache too long with dated
execution plans (this is a reach but viable with 24/7 machines maybe a WITH
RECOMPILE is in order?). Anykind of excessive TempDb activity? I'd get SQL
Profiler online.... What's changed since the system ran "fine"?

Few quick thoughts...
--
Patrick Logan, MCSD
Senior Technical Development Advisor
McKessonHBOC -- Extended Care Solutions Group

Opinions expressed are my own and not necessarily those of McKessonHBOC.

by **Patrick Loga** » Thu, 07 Dec 2000 04:00:00

Has memory usage/configuration been looked at? Is the OS being starved? Is
there a lot of paging file activity?

--
Patrick Logan, MCSD
Senior Technical Development Advisor
McKessonHBOC -- Extended Care Solutions Group

Opinions expressed are my own and not necessarily those of McKessonHBOC.

by **asdf** » Thu, 07 Dec 2000 04:00:00

Patrick,

These are all excellent thoughts. The service is absolutely a 7x24 system.
It has however been flushed rebooted and swiftly kicked. It also has
resources available to SQL exec. There is a full 700MB+ dedicated (1GB
system ram) for SQL. It is not all being used. I have looked at the
profiler and do not see anything out of the ordinary.

As far as what's changed, I am not sure that anything has. I know that we
modified some tables that were being replicated (and have had distribution
database go into single user mysteriously on a couple of occasions). I
believe that we have cleared all of that up though.

Some additional oddities associated with the slow queries and disk
over-usage,
1. Seemingly limited to activity on a single database.
2. Seems to be impacting db insert activity from the stored procedures
only. Replication to this database does not seem to send the disk into the
weeds.
3. ad-hoc select statements execute very quickly and without a great deal
of disk activity.

Anyway, thanks again for any thoughts.

> Have you ran SQL Profiler to look at the activity? Are the index
statistics
> getting dated and is the query optimizer making poor index selection
> choices? I'd look at some execution plans provided by the SQL Profiler.
Is
> it possible the stored procs have been in the proc cache too long with
dated
> execution plans (this is a reach but viable with 24/7 machines maybe a
WITH
> RECOMPILE is in order?). Anykind of excessive TempDb activity? I'd get
SQL
> Profiler online.... What's changed since the system ran "fine"?

> Few quick thoughts...
> --
> Patrick Logan, MCSD
> Senior Technical Development Advisor
> McKessonHBOC -- Extended Care Solutions Group

> Opinions expressed are my own and not necessarily those of McKessonHBOC.

by **Patrick Loga** » Sat, 09 Dec 2000 00:59:17

Quote:> Seems to be impacting db insert activity from the stored procedures and

only limited to activity on a single database.

Any additional indexes on these affected tables? New triggers? Transaction
wrappers? Where are the requests to run the stored procs coming from
MTS/COM+, IIS, etc? Are transactions somehow prolonging the commits? What
does SQL Profiler show the SQL Server doing while processing the insert
stored procs, high CPU or page I/O, transactions (nested maybe), locks being
waited on?

Might try grabbing some lock info with sp_lock while one of the
"long/time-consuming" inserts is occurring. SQL provides the tools (in
conjunction with maybe PerfMon) to sniff what it's spending its time doing
to process your requests.

The raw database files haven't become seriously fragmented have they? Has
the db recently been restored or reattached?

Hope some of these thoughts help....

--
Patrick Logan, MCSD
Senior Technical Development Advisor
McKessonHBOC -- Extended Care Solutions Group

Opinions expressed are my own and not necessarily those of McKessonHBOC.

Very Computer

Database Server Disk Usage

Database Server Disk Usage

Database Server Disk Usage

Database Server Disk Usage

Database Server Disk Usage

Database Server Disk Usage