Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Table Performance issue on select query Expand / Collapse
Author
Message
Posted Monday, July 7, 2008 3:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 9, 2009 6:37 PM
Points: 62, Visits: 221
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.
Post #529131
Posted Monday, July 7, 2008 4:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:19 AM
Points: 40,205, Visits: 36,608
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 2008, MVP
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

Post #529142
Posted Monday, July 7, 2008 4:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 9, 2009 6:37 PM
Points: 62, Visits: 221
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.
Post #529152
Posted Monday, July 7, 2008 7:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:19 AM
Points: 40,205, Visits: 36,608
Was that while (Select * from Table2) was running?

Is the time taken by the query consistent?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #529294
Posted Wednesday, July 9, 2008 12:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 9, 2009 6:37 PM
Points: 62, Visits: 221
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
DBNAME 21 WRITELOG


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


Thanks in advance.
Post #530520
Posted Wednesday, July 9, 2008 5:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, February 25, 2012 10:25 AM
Points: 304, Visits: 700
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.
Post #530662
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse