Thursday, March 22, 2012

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

No comments:

Post a Comment