Performance Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Performance Issue

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-06-03 : 16:21:39

Guys,

I am running the same query in the same copies of databases on 2 different instances it gives me different time periods
10 secs for on instance A and 90 secs for on instance B. The database on instance B is exact copy/restore of database on instance A.

The execution plans for the query is also same from Instance A and Instance B.

How do I start troubleshooting the performance on the Instance B?

Any suggestions and inputs would help.

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-03 : 16:23:18

Do those instances have same system resources? Did you compare i/o statistics?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-03 : 16:24:31

Are stats updated after restoring database? check if index is fragmented with DBCC showcontig? Otherwise you have rebuild index which will also take care of updating stats.

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-06-03 : 17:14:32

how do we know index fragmentation from DBCC SHOWCONFIG.

for example for the following table how do I know index fragmentation

DBCC SHOWCONTIG scanning 'ACM_EVENT_ENTRY_EXECUTION' table...
Table: 'ACM_EVENT_ENTRY_EXECUTION' (1887397843); index ID: 0, database ID: 27
TABLE level scan performed.
- Pages Scanned................................: 0
- Extents Scanned..............................: 0
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 0.0
- Scan Density [Best Count:Actual Count].......: 100.00% [0:0]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 0.0
- Avg. Page Density (full).....................: 0.00%

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-03 : 18:31:02

Numbers don't look like real. Basically, Scan Density should close to 100%.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-03 : 18:55:03

No pages are scanned. Try with table which are used in joins .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources