| Author |
Topic |
|
dhw
Constraint Violating Yak Guru
332 Posts |
 Posted - 2003-04-17 : 19:20:39
|
| We are experiencing a problem where the same sql statement is providing radically different performance results on two different databases. The databases are both on the same Sql2000 server. On database "A"...the query returns a result set in less than a second. On database "B"...the same query returns the same results....but in about 15 or 20 seconds!The query is joining three tables. I looked at the three tables in design mode and noticed that somehow the Fill Factor on the indexes for these tables on database "A" were all Zero. However, the same indexes (on the same tables) on database "B" were all at 70%. I thought that this might be the culprit...so I reduced the Fill Factor to Zero on the indexes and ran an IndexDefrag and then a DBReindex on each table/index.Running the same tests yields the same results as before. Database "B" is still running at 15 to 20 seconds and Database "A" is still less than a second.I noticed also that in Database "B", the three tables have more data than in Database "A"...but the difference is anywhere from 15 to 150 rows...not that significant I think.Is there anything else I could look at to determine why the query behavior is different between the two databases?thanks...DW |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-04-17 : 19:25:53
|
| Do the execution plans look the same?Do the execution plans show the same subtree cost?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2003-04-17 : 19:58:06
|
| Yes...the execution plans are the same, with the same costs. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-04-17 : 21:38:46
|
| Run UPDATE STATISTICS on all of the affected tables on both databases, then try it. You should also want to run DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS before you run the queries. These will flush the procedure and data caches and prevent caching from giving an advantage. You really can't compare performance between two different setups without flushing the cache(s).Another thing to look at is the disk(s) that the databases are on. If they're on separate physical disks then there could be a problem with one of them that's causing the slowdown. |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2003-04-18 : 10:30:41
|
| Thanks for the great information. I'll give it a try and post my results.Regaring the physical disk...both databases on are the same disk/server.DHW |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2003-04-18 : 10:47:55
|
| Well...after running UPDATE STATISTICS, DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS....the query performance results are the same. The query on database "B" still runs around 15/20 seconds and the same query on database "A" runs less than a second.This is very strange....the only item that I saw that was different initially, was the Fill Factor setting on the indexes...but now that these are the same, I am at a loss to understand.I will continue to work on this (as we have a customer that is complaining about the slowness), but if anyone has any other suggestions, I would appreciate them.thanks,DHW |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2003-04-18 : 11:08:24
|
| Another possible point of interest:I ran DBCC SHOWCONTIG (table) WITH ALL_INDEXES and there is a difference betweent the two databases. Though, I don't know enough to determine if this is the cause of my performance problem. And, if it is, how I would be able to fix it. Anyway, here are the results of the SHOWCONTIG command for a couple of the indexes. The Scan Density and Extent Scan Fragmentation are noticeably different.Database "A" - (the fast database)DBCC SHOWCONTIG scanning 'oaddr' table...Table: 'oaddr' (725577623); index ID: 2, database ID: 21LEAF level scan performed.- Pages Scanned................................: 17- Extents Scanned..............................: 7- Extent Switches..............................: 6- Avg. Pages per Extent........................: 2.4- Scan Density [Best Count:Actual Count].......: 42.86% [3:7]- Logical Scan Fragmentation ..................: 0.00%- Extent Scan Fragmentation ...................: 71.43%- Avg. Bytes Free per Page.....................: 408.1- Avg. Page Density (full).....................: 94.96%Database "B" - (the slow database)DBCC SHOWCONTIG scanning 'oaddr' table...Table: 'oaddr' (725577623); index ID: 2, database ID: 31LEAF level scan performed.- Pages Scanned................................: 17- Extents Scanned..............................: 3- Extent Switches..............................: 2- Avg. Pages per Extent........................: 5.7- Scan Density [Best Count:Actual Count].......: 100.00% [3:3]- Logical Scan Fragmentation ..................: 0.00%- Extent Scan Fragmentation ...................: 33.33%- Avg. Bytes Free per Page.....................: 353.4- Avg. Page Density (full).....................: 95.63% |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-18 : 11:50:14
|
| 20 secs against one sec.Would normally say this has to be due to not using an index - but the plans are the same.Could it be that the data is already in memory on database A but not on B? This could be becasue you have activity on A which gets thye data into memory before the test on A but not on B.From your scan B has to read less extents and the same number of pages so would expect it to be faster (but not noticably).Just noticed you've cleared the buffers so that's not it.The few extra records on B may be causing it to run out of memory and page - worthwhile checking that - may need to split the query up using temp tables to fit in memory.I assume the two databases are set up the same - collation, recovery model, no extra views involved, computed fields, ...What happens if you run queries on the two databases using joins between pairs of tables used in the query?I just rambling so I'll stop.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2003-04-18 : 11:59:07
|
| I decided to scrutinize the data in the tables carefully. The fact that the databases were the same, on the same server and we're using the same query.....made me think that it had to be the data. (though many here said that it couldn't be the data).And, as it turns out, this was the problem! Not the amount of data...but the data itself. One of the tables had about 60 rows of data at the end that were completely empty...other than a primary key. Not sure how the data got there...a different issue...but when I deleted those rows and ran the query again, the speed on Database "B" matched the less than a second response as observed in Database "A".Whew! Thanks for everyone's help!DHW |
|
|
|
|
Index performance differences...