Performance Questions on SQL 2000 from perfmon

I am a new admin at our office and I am trying to get a handle on the SQL
systems running.
I followed a few online guides to performance monitoring and I have a few
concerns that I wanted to run past some experts.
This SQL server is a dual PIII 1.4 GHz with 2 GB of RAM.
So far I have monitored a few SQL specific counters along with the proc and
memory. Under normal load, everything has seemed good so far, but when we
run reports, the SQL server goes under a heavy load that appears to be
stressing the system. Under Task Manager, the procs spike a lot (not a
curtain though) and the server becomes sluggish. This is what I have
discovered so far from perfmon:
1) Under the processor queue length, the graph is spiking over 200, but it
lists a maximum of 49 on the chart. Either way, this seems to be pretty
high.
2) Total proc time is averaging at around 50%
3) The sqlserver Working Set is at 1.6 GB
4) Pages/Sec frequently exceeds 50 with an average at 20 and a maximum of
755
5) Available MBytes is at 10 - 16
6) Page File % usage is at 38%
7) Non-Paged Pool Memory was around 26 MB
During heavy load, I also lost some collection of data via perfmon at 5
second intervals. My interpretation from this is that SQL has sucked up all
available RAM and wants more. I have not rebooted to see how this effects
the server. The proc's seem to be overloaded under heavy load with the
queue length filing up, but an avg of 50% shouldn't need more processing.
Also I attribute the loss of data collection to the high queue length.
What are possible resolutions? Do I need to collect more data? I suspect
that SQL is not using the 1.6 GB assigned to it very effectively. Is it
possible that some of the applications we have running are somehow either
causing a memory leak, or locking the memory in SQL? If that is an issue,
how can I detect what memory inside SQL is locked/wasted?
Thank You for any assistance,
Kevin Hammonddatabase engines are essentially a disk cache, so it so
should be using all the memory it needs if it is
available, otherwise, it would have to go to disk for the
data,
if you are concerned as to whether SQL server really needs
all that memory, monitor disk read/sec, writes/sec, avg
disk queue len, avg sec/read and avg sec/write for the
physical disk containing the main data files (not the log)
then try reducing the amount of memory SQL Server can use,
if the disk counters rise, then SQL Server was making good
use of that memory
>--Original Message--
>I am a new admin at our office and I am trying to get a
handle on the SQL
>systems running.
>I followed a few online guides to performance monitoring
and I have a few
>concerns that I wanted to run past some experts.
>This SQL server is a dual PIII 1.4 GHz with 2 GB of RAM.
>So far I have monitored a few SQL specific counters along
with the proc and
>memory. Under normal load, everything has seemed good so
far, but when we
>run reports, the SQL server goes under a heavy load that
appears to be
>stressing the system. Under Task Manager, the procs
spike a lot (not a
>curtain though) and the server becomes sluggish. This is
what I have
>discovered so far from perfmon:
>1) Under the processor queue length, the graph is
spiking over 200, but it
>lists a maximum of 49 on the chart. Either way, this
seems to be pretty
>high.
>2) Total proc time is averaging at around 50%
>3) The sqlserver Working Set is at 1.6 GB
>4) Pages/Sec frequently exceeds 50 with an average at 20
and a maximum of
>755
>5) Available MBytes is at 10 - 16
>6) Page File % usage is at 38%
>7) Non-Paged Pool Memory was around 26 MB
>During heavy load, I also lost some collection of data
via perfmon at 5
>second intervals. My interpretation from this is that
SQL has sucked up all
>available RAM and wants more. I have not rebooted to see
how this effects
>the server. The proc's seem to be overloaded under heavy
load with the
>queue length filing up, but an avg of 50% shouldn't need
more processing.
>Also I attribute the loss of data collection to the high
queue length.
>What are possible resolutions? Do I need to collect more
data? I suspect
>that SQL is not using the 1.6 GB assigned to it very
effectively. Is it
>possible that some of the applications we have running
are somehow either
>causing a memory leak, or locking the memory in SQL? If
that is an issue,
>how can I detect what memory inside SQL is locked/wasted?
>Thank You for any assistance,
>Kevin Hammond
>
>.
>

Performance problem and looping

I posted this to SQL Team.com and I thought I'd repost it here and get the .Net perspective.

I'm a developer and not too much SQL Admin knowledge (and neither does my customer) but this problem seems to have something to do with the database server. I'm not sure what might be good information that might point to the problem but this is an overview:

The application is a ASP.Net (ADO.Net) application which sends an embedded SQL query to the server. I can run the application on my local development server and get a two second response time. Running on the customer's server (faster CPU, etc) at night with no traffic takes upwards of two minutes which is completely unacceptable. I ran SQL Profiler (is it possible to post the trace?) and see that it chugs along first getting the query then repeating it over and over. The query consists of a concantenation of queries something like:

DROP TABLE ABRA_MAX;DROP TABLE ABRA_SUMS;DROP TABLE ABRA_TOTALS;SELECT st_sto_id AS store, MAX(ds_date_load) AS maxdate INTO ABRA_MAX FROM ABRA_STORE_SUM, ABRA_STORE, ABRA_CAPLOAD, ABRA_STORE_LOAD WHERE sto_clm_id = cl_clm_id AND st_cl_id = cl_id AND ds_st_id = st_id GROUP BY st_sto_id;SELECT sto_id, etc.
...

In the profiler it appears the server runs this with a duration of approximately 10 seconds then starts over again breaking apart the queries individually separated by "exec sp_reset_connection" events:

DROP TABLE ABRA_MAX
exec sp_reset_connection
DROP TABLE ABRA_SUMS
exec sp_reset_connection
SELECT N'Testing Connection...'
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
SELECT st_sto_id AS store, MAX(ds_date_load) AS maxdate INTO ABRA_MAX FROM ABRA_STORE_SUM, ABRA_STORE, ABRA_CAPLOAD, ABRA_STORE_LOAD WHERE sto_clm_id = cl_clm_id AND st_cl_id = cl_id AND ds_st_id = st_id GROUP BY st_sto_id
...

It then repeats this cycle a number of times reexecuting the 10 second query over and over while chugging along through the other queries. I don't see anything like this on my development server using the profiler it buzzes right through it.

Hopefully, I'm doing something stupid and there's a quick fix - any pointers are appreciated. I know that I really should be using a stored procedure (older and wiser now) - would this make a big difference?

Thanks in advance!

-- JeffCan you post the SQL that's sent? Is it sent once or multiple times?|||Is the data the same on the customer's server? If there are 10 rows in your local tables and 100,000 rows on the customer's, of course you may get different results. Try copying and pasting each statement that is captured in Profiler into Query Analyzer and executing on your customer's SQL Server to isolate which one is slow. This will help you figure out if the query needs to be optimized.

In the customer's environment, is the ASP.NET app executing on the same machine or another web server? If there is a slow network connection between the two, that could also explain why it runs fast locally, but slow in the other environment.

In general you want to avoid looping and executing statements over and over again. This will usually eat up CPU on the database server.

John|||I sent the concantenated series of queries below (sorry for the mess). Originally, I was sending them individually and thought the roundtrip might be slowing things down, but in both cases (concatenated or not) the result is the same.

TABLE ABRA_SUMS;DROP TABLE ABRA_TOTALS;SELECT st_sto_id AS store, MAX(ds_date_load) AS maxdate INTO ABRA_MAX FROM ABRA_STORE_SUM, ABRA_STORE, ABRA_CAPLOAD, ABRA_STORE_LOAD WHERE sto_clm_id = cl_clm_id AND st_cl_id = cl_id AND ds_st_id = st_id GROUP BY st_sto_id;SELECT sto_id, sto_internal_name, sto_bizflag, ABRA_STORE_SUM.* INTO ABRA_SUMS FROM ABRA_STORE_SUM, ABRA_MAX, ABRA_STORE_LOAD, ABRA_CAPLOAD, ABRA_STORE, ABRA_DISTRICT WHERE store = sto_id AND ds_date_load = maxdate AND sto_id = st_sto_id AND st_id = ds_st_id AND sto_clm_id = cl_clm_id AND st_cl_id = cl_id AND sto_dst_id = dst_id AND dst_id = '1';SELECT sto_id, sto_internal_name, sto_bizflag, SUM(ds_st_quantity_target) AS total_quantity_target, SUM(ds_ins_count) as total_actual_count,SUM(ds_st_wip_target) AS total_wip_target, SUM(ds_ins_amt) as total_ins_amt, SUM(ds_ins_count) as total_ins_count,SUM(ds_sch_amt) as total_sch_amt, ds_date_load as last_update INTO ABRA_TOTALS FROM ABRA_SUMS GROUP BY sto_internal_name, ds_date_load, sto_id, sto_bizflag;ALTER TABLE ABRA_TOTALS ADD total_actual_amount AS total_ins_amt + total_sch_amt;

Thanks,

-- Jeff|||John,

The customers database is larger by a factor of two.

I should have mentioned I ran the queries on the customer site in Query Analyzer and get a 10 second response time for the batch. One query in particular takes the majority of time and could be optimized but the QA different performance from the 2 minutes when running from a web server.

Both the app and the server are on the same machine, so I don't think the network is an issue unless there's some problem going back to the client side.

I'm not deliberately looping, it appears to happen on it's own.

Thanks,

--Jeff|||1. It's a nasty mix of DDL and DML is there any way you avoid that? Do you really need to drop a table and add columns afterwards? Can't you create the table and at least truncate it? Could a temp table be used?

2. Have you tried running the batch from a Query Analyser on the web server?

3. I can't see any obvious reason for any kind of looping unless you have some kind of retry mechanism somewhere?

Performance of system

Hi,
I am developing an editorial system(MS-SQL). There are a public part(frontend for reader) and an editorial part (backend for editor, admin,...).
The public part of system will be used approximately 1000 acesses/s.

A possible solution:
I solve whether I have to use two databases:
1.DB all data
2.DB with data for frontend

acesses............trigers
--->2.DB <----1.DB

or more of databases or else?

The purpose is, a reader should have good response that the reader don't wait.

Could you give me any advice ?
Thanksone database

make sure all tables are properly indexed for performance of your main queries|||and is there any at least one reason for more database ??|||separate database can be backed up and recovered separately