3000 rows = 45 sec

I've got a table <Person> with 70 columns
that contains about 3000 rows

It takes 45 sec in the QA to run <Select * From Person>

3000 rows = 45 sec = Poor performances

So how in the heck do you all do to run
<Select * From MillionPersonsTable>

You've got superdoopers alien machines ?

(sorry, i've got terrible perf problems)Check for activity, locks on your server, try just select count(*) from your table or select one column from your table. It is impossible to have such performance on sql server.|||Do you servers have dual processors, and multiple hard drives?|||pentium III or IV|||select count(*) takes 3 secs
select column takes 3 secs too

I tought that I had poor perfs on Sybase
But they're practically the same on SQL Server|||How much data are you returning to the client?

select dpages/128 as KB
from sysindexes
where id = object_id('yourtable')
and indid in (0, 1)

70 columns sounds awfully wide.|||1 KB|||Heh. Small problem with units. Guess that's why I don't work at NASA...

You are returning somewhere between 1 and 2MB of data to the client. Still 45 seconds sounds a bit long for 1MB of data to wiggle its way through your cabling. Is the server in your building, or remote?|||the server is in the building|||The select you did above on a single column. Was that an indexed column? If so, how log does it take to return an unindexed column?|||3 secs ...
for indexed or non indexed columns|||OK. Last thought.

Open a QA session to the server, and note the SPID number (bottom right). In this window run the select * from table query. In a second window run the following:

select spid, cpu, physical_io, memusage, waittype, lastwaittype, blocked, waitresource
from master..sysprocesses
where spid = (your spid)

Blocked should always be 0 (so if it is not, you have an answer right there).
Lastwaittype is the type of waiting the connection did last, so it will not change often.
Waittype is a binary field that is the current waittype of the connection. You can find some of the definitions in KB article Q244455. 0x800 is network IO, I believe.
Physical_IO is the one I suspect you will see jump. If this jumps early in the query, then you are reading the table from disk. Table scans tend to recylcle memory this way, to keep more efficient tables in memory. If you have outrageous physical_io, you can look at trying to break up the table, or simply not do select * on it without a where clause.|||I'll check that tomorrow.

Thank you for all your time

Caroline|||Are you running any traces ? Is sql server agent running ?|||Run dbcc showcontig on the table, I bet you have high fragmentation. If you do not have a clustered index on the table, you should. There are rarely times that a table should not have a clustered index.

HTH|||dbcc showcontig

Table : 'T74PERS' (453576654); index ID = 1, base de donnes ID = 9
Analyse du niveau TABLE effectue.
- Pages analyses........................: 213
- extensions analyses.....................: 28
- extensions commutes....................: 28
- Moy des pages par extension............: 7.6
- Densit d'analyse [meilleure valeur du compte rel]......: 93.10% [27:29]
- Fragmentation d'analyse logique..: 12.68%
- Fragmentation d'analyse d'extension..: 3.57%
- Moy octets libres par page................: 389.8
- Densit de page moy (pleine).........: 95.18%

Is 12.68% and 3.57% high fragmentation ?|||I've got a clustered index on one of the columns of the table|||Your numbers look OK to me. Don't think it is an frag problem

2005 Merge Agent Degradation

I have a merge agent running extremely slow - schedule fires every 1 minute,
however it takes 1 to 3 minutes to execute. Delivery Rate 5 rows/sec or less,
all 5 other merge agents execute in less than 20 seconds and Delivery Rate 80
rows/sec or greater.
Each time merge agent fires, subscr cpu spikes to 85% - normally 15 to 20%.
Ran profiler and discovered this stamement below hogging the cpu and having
the longest duration.
I am rebuilding the MSmerge_genhistory table nightly w/ the following: DBCC
DBREINDEX (MSmerge_genhistory, '', 80)
Any ideas? A re-init will resolve problem , have had to do this many times
in past - at least every 30 days... however, want to get to root cause. tia
Chris
select top (@.numgens) *
from
(
select generation, guidsrc, art_nick,
case when genstatus = 4 then 0 else genstatus end as
genstatus,
pubid, nicknames,
okaytoskip = case when
art_nick is not null and art_nick <> 0
and genstatus in (0,4)
-- Skip all rows that are for incomplete
generations for articles that have no joins.
and not exists (select 1 from
dbo.sysmergesubsetfilters where (join_nickname = art_nick or art_nickname =
art_nick) and (filter_type & 1) = 1)
then 1 else 0 end
, changecount
from
(select generation, guidsrc, art_nick, genstatus, pubid,
nicknames, changecount
from dbo.MSmerge_genhistory with (rowlock, repeatableread)
where generation >= @.genstart
and generation <= @.maxgen_to_enumerate
and generation > @.mingen_to_enumerate
and (art_nick = 0 or art_nick is NULL or
art_nick in (select nickname from dbo.sysmergearticles
where pubid = @.pubid))
) as generation_range
UNION ALL -- use UNION ALL instead of UNION for perf
reasons. Merge agent code will skip dupes. Will only have max 2 dupes.
select generation = @.next_possible_watermark, guidsrc =
@.next_possible_watermark_guidsrc,
art_nick = @.next_possible_watermark_art_nick, genstatus =
@.next_possible_watermark_genstatus,
pubid = @.next_possible_watermark_pubid, nicknames =
@.next_possible_watermark_nicknames,
okaytoskip = 0, changecount =
@.next_possible_watermark_changecount
where @.next_possible_watermark is not null
union all
select generation = @.min_open_gen, guidsrc= @.min_open_gen_guid,
art_nick=@.min_open_gen_art_nick, genstatus=0,
pubid=@.pubid, nicknames=NULL, okaytoskip = 0,
changecount=0
where @.min_open_gen is not null
) as genertions
order by generation ASC
Two things I can suggest here is:
1. Avoid metadata contention. Make sure merge agents do not run at the
same time so stagger their schedules.
2. Retention period can cause lots of data to be stored in the contets
and tombstone tables. Default i think is 14 days. So 14 days worth of
changes are stored. Consider reducing this if subscribers are not
offline for a long time.
Regards Jim
http://jims-spanakopita.blogspot.com/
|||What do your join filters look like? By placing indexes on all the filters
you will get better performance. Another option to try is to modify your
filters to make them as shallow as possible.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4ED9E8B8-9CA1-4B10-A609-E67921202C68@.microsoft.com...
> I have a merge agent running extremely slow - schedule fires every 1
> minute,
> however it takes 1 to 3 minutes to execute. Delivery Rate 5 rows/sec or
> less,
> all 5 other merge agents execute in less than 20 seconds and Delivery Rate
> 80
> rows/sec or greater.
> Each time merge agent fires, subscr cpu spikes to 85% - normally 15 to
> 20%.
> Ran profiler and discovered this stamement below hogging the cpu and
> having
> the longest duration.
> I am rebuilding the MSmerge_genhistory table nightly w/ the following:
> DBCC
> DBREINDEX (MSmerge_genhistory, '', 80)
>
> Any ideas? A re-init will resolve problem , have had to do this many times
> in past - at least every 30 days... however, want to get to root cause.
> tia
> Chris
>
> select top (@.numgens) *
> from
> (
> select generation, guidsrc, art_nick,
> case when genstatus = 4 then 0 else genstatus end as
> genstatus,
> pubid, nicknames,
> okaytoskip = case when
> art_nick is not null and art_nick <> 0
> and genstatus in (0,4)
> -- Skip all rows that are for incomplete
> generations for articles that have no joins.
> and not exists (select 1 from
> dbo.sysmergesubsetfilters where (join_nickname = art_nick or art_nickname
> =
> art_nick) and (filter_type & 1) = 1)
> then 1 else 0 end
> , changecount
> from
> (select generation, guidsrc, art_nick, genstatus, pubid,
> nicknames, changecount
> from dbo.MSmerge_genhistory with (rowlock, repeatableread)
> where generation >= @.genstart
> and generation <= @.maxgen_to_enumerate
> and generation > @.mingen_to_enumerate
> and (art_nick = 0 or art_nick is NULL or
> art_nick in (select nickname from dbo.sysmergearticles
> where pubid = @.pubid))
> ) as generation_range
> UNION ALL -- use UNION ALL instead of UNION for perf
> reasons. Merge agent code will skip dupes. Will only have max 2 dupes.
> select generation = @.next_possible_watermark, guidsrc =
> @.next_possible_watermark_guidsrc,
> art_nick = @.next_possible_watermark_art_nick, genstatus =
> @.next_possible_watermark_genstatus,
> pubid = @.next_possible_watermark_pubid, nicknames =
> @.next_possible_watermark_nicknames,
> okaytoskip = 0, changecount =
> @.next_possible_watermark_changecount
> where @.next_possible_watermark is not null
> union all
> select generation = @.min_open_gen, guidsrc= @.min_open_gen_guid,
> art_nick=@.min_open_gen_art_nick, genstatus=0,
> pubid=@.pubid, nicknames=NULL, okaytoskip = 0,
> changecount=0
> where @.min_open_gen is not null
> ) as genertions
> order by generation ASC
>