Table Performance issue on select query

Question

Table Performance issue on select query

Sanaullah

SSC Eights!

Points: 892
More actions
July 7, 2008 at 3:42 am

#122384

Hi All,
Here i am having two tables Table1 and Table2 and both the tables are having same number of records and columns i.e 14522, now see the problem.
when i am selecting from table1(Select * from Table1) i am getting the output in 8 sec and when i am selecting from table2 (Select * from Table2) i am getting the output in 3 min and 20 sec.
When i run DBCC SHOWCONTIG
DBCC SHOWCONTIG scanning 'Table2' table...
Table: 'Table2' (1157579162); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 194
- Extents Scanned..............................: 29
- Extent Switches..............................: 28
- Avg. Pages per Extent........................: 6.7
- Scan Density [Best Count:Actual Count].......: 86.21% [25:29]
- Logical Scan Fragmentation ..................: 0.52%
- Extent Scan Fragmentation ...................: 13.79%
- Avg. Bytes Free per Page.....................: 750.0
- Avg. Page Density (full).....................: 90.73%
DBCC SHOWCONTIG scanning 'Table1' table...
Table: 'Table1' (2105058535); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 235
- Extents Scanned..............................: 33
- Extent Switches..............................: 32
- Avg. Pages per Extent........................: 7.1
- Scan Density [Best Count:Actual Count].......: 90.91% [30:33]
- Logical Scan Fragmentation ..................: 0.85%
- Extent Scan Fragmentation ...................: 24.24%
- Avg. Bytes Free per Page.....................: 759.1
- Avg. Page Density (full).....................: 90.62%
I want to know why the second table is taking much time to retrive the records online.
Thanks in advance.
Syed Sanaullah Khadri
DBA.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Is there any blocking? If you check in sysprocesses while the query of the second table is running, do you see anything listed for LastWaitType? If so, what?

how long does a count(*) take for each table?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sanaullah SSC Eights! Points: 892 More actions · Answer 2

Hi Gail,

I ran this query

SELECT SD.NAME,SD.dbid,lastWaittype FROM SYSPROCESSES SP INNER JOIN

SYSDATABASES SD ON SD.DBID=SP.DBID WHERE SD.DBID=21

and with that database i got only two lastwaittype they are

MISCELLANEOUS and

PAGELATCH_UP

And the count is not taking any time not even a second.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Was that while (Select * from Table2) was running?

Is the time taken by the query consistent?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sanaullah SSC Eights! Points: 892 More actions · Answer 4

Hi Gail,

I have just now executed two queries ie from table1 and table2, Now i am having 15062 records in both the tables and table1 is taken 45 sec and table2 has taken 6 min and 35 sec.

and the i executed this query in master database

SELECT SD.NAME,SD.dbid,lastWaittype FROM SYSPROCESSES SP INNER JOIN

SYSDATABASES SD ON SD.DBID=SP.DBID WHERE SD.DBID=21

and what i got with table1 is nothing and with table2 is

DbName ID lastwaittype

DBNAME21 WRITELOG

i want to know what can we do to make this table2 fun faster as table1.

Thanks in advance.

harshil Mr or Mrs. 500 Points: 590 More actions · Answer 5

Sanaullah (7/7/2008)
Hi All,
Here i am having two tables Table1 and Table2 and both the tables are having same number of records and columns i.e 14522, now see the problem.
when i am selecting from table1(Select * from Table1) i am getting the output in 8 sec and when i am selecting from table2 (Select * from Table2) i am getting the output in 3 min and 20 sec.
When i run DBCC SHOWCONTIG
DBCC SHOWCONTIG scanning 'Table2' table...
Table: 'Table2' (1157579162); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 194
- Extents Scanned..............................: 29
- Extent Switches..............................: 28
- Avg. Pages per Extent........................: 6.7
- Scan Density [Best Count:Actual Count].......: 86.21% [25:29]
- Logical Scan Fragmentation ..................: 0.52%
- Extent Scan Fragmentation ...................: 13.79%
- Avg. Bytes Free per Page.....................: 750.0
- Avg. Page Density (full).....................: 90.73%
DBCC SHOWCONTIG scanning 'Table1' table...
Table: 'Table1' (2105058535); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 235
- Extents Scanned..............................: 33
- Extent Switches..............................: 32
- Avg. Pages per Extent........................: 7.1
- Scan Density [Best Count:Actual Count].......: 90.91% [30:33]
- Logical Scan Fragmentation ..................: 0.85%
- Extent Scan Fragmentation ...................: 24.24%
- Avg. Bytes Free per Page.....................: 759.1
- Avg. Page Density (full).....................: 90.62%
I want to know why the second table is taking much time to retrive the records online.
Thanks in advance.
Syed Sanaullah Khadri
DBA.

Hi Syed,

Just wanted to ask you if both of your tables have equal number of columns. It may be that Table2 is having too many number of columns that results in more time in displaying those columns. Also check if your table has indexes created.