| Author |
Topic |
|
Hillxie
Starting Member
17 Posts |
 Posted - 2003-10-14 : 10:11:32
|
| I have two exact the same databases on the same server (I checked the database properties, tables, stored procedures, functions, all are the same). but one select statement with join and union runs much faster in one database than another one, I can not figure out the reason, please help! thanks.Hill |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-10-14 : 10:28:38
|
A very obvious question, but, do both the database have the same data? What about Indexes? Have you updated the statistics on one database but not on the other?Owais
Make it idiot proof and someone will make a better idiot |
 |
|
|
Hillxie
Starting Member
17 Posts |
Posted - 2003-10-14 : 10:39:59
|
| Thanks for reply. They do have the same data and the same Indexes. The "Auto Update Statistics" are both checked in the database property menu. I copied one database from another one on the same server, after that I checked the settings, data, indexes, funcitons, they are the same. I have lots of joins and unions inside the select statement... |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-14 : 11:19:09
|
| did you run 1 many times and the other just once?And what is "much faster"Time is such a relative thing...Brett8-) |
|
|
|
Hillxie
Starting Member
17 Posts |
Posted - 2003-10-14 : 11:50:39
|
| The "Cumulative wait time on server replies" shows one is 4137, another is 862. I run both of them many times. |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2003-10-14 : 18:26:58
|
| Did you try DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE on the faster performing server to see if it slowed down? Does DBCC SHOWCONTIG show significant fragmentation in the slower database? And finally, have you looked at the execution plans that both queries are using to see if they are identical?Oh yeah, and check the names you are using to reference objects. 2 part names can help with performance while still maintaining the ability to transfer queries between two databases.----------------------"O Theos mou! Echo ten labrida en te mou kephale!""Where theres a will, theres a kludge." - Ken Henderson |
|
|
|
Hillxie
Starting Member
17 Posts |
Posted - 2003-10-15 : 11:33:51
|
| --------------------------------------I tried DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE on both, the faster database was still faster and the slower database became slow every time (before it was only slow at the first time).--------------------------------------I run DBCC SHOWCONTIG, I got these:slower database:TABLE level scan performed.- Pages Scanned................................: 15- Extents Scanned..............................: 3- Extent Switches..............................: 2- Avg. Pages per Extent........................: 5.0- Scan Density [Best Count:Actual Count].......: 66.67% [2:3]- Logical Scan Fragmentation ..................: 0.00%- Extent Scan Fragmentation ...................: 66.67%- Avg. Bytes Free per Page.....................: 171.7- Avg. Page Density (full).....................: 97.88%faster database:TABLE level scan performed.- Pages Scanned................................: 15- Extents Scanned..............................: 6- Extent Switches..............................: 5- Avg. Pages per Extent........................: 2.5- Scan Density [Best Count:Actual Count].......: 33.33% [2:6]- Logical Scan Fragmentation ..................: 6.67%- Extent Scan Fragmentation ...................: 83.33%- Avg. Bytes Free per Page.....................: 171.7- Avg. Page Density (full).....................: 97.88%I can see the fragmentation is different, but I am not sure what causes this.--------------------------------------I checked the execution plans, it is different on the different database. I can see in the execution plan on the slower database, it always uses "Table Spool/Lazy Spool", but the execution plan on the faster database never uses it. Does this mean anything?--------------------------------------I can not understand what "2 part names" means? could you please explain a little bit to me?Thanks, Owais. |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2003-10-15 : 13:01:02
|
| Going in reverse order:Sorry, 2, 3, or 4 part names are part of how SQL Server finds all the involved objects. The full 4 part name is [server name].[database name].[owner/schema name].[object name]. I've been guilty of leaving names from auto-generated code in 3 part names which can result in failures or unexpected results if you run the code in a different database. If you specify only the object name, then the server has to do more work to figure out which object you are referring to and does a search. If you specify everything in 2 part names it can have a small increase in performance. (For instance, in Northwind instead of referring to the Customers table, refer to the dbo.Customers table.) but I really only brought that up in case something really odd was happening like queries hitting both of the databases instead of just one.Yes, it means that for whatever reason the server is using a less efficient plan than the other database. If the databases and data are identical, then this probably means that the statistics have something odd going on in them. You could take the blind faith approach and run sp_updatestats on the slower database to try to goad the server into getting a better sampling, or try using DBCC SHOW_STATISTICS to see if the statistics for the tables/indexes are different between the 2 servers. Regardless, the 2 different query plans are the reason your queries have two different speeds, so your real question should be why the server is coming up with two different plans. You might try to view the estimated query plan (ctrl+l in query analyzer) and see if they are different from the ones that are actually ran. Without knowing the query or plans, it'd be impossible to tell you more.Fragmentation is just a function of how rows are written to disk. SQL Server stores pages of data in extents (I forget how many pages per extent there are) and as new rows are inserted, it may require it to write them in new pages on new extents. This can cause a table access to scan quite a few pages/extents to get all of the required data. I'm not sure what's going on with your two databases, but it looks like the faster one has twice as many extents allocated for the same number of pages. I don't think this is the cause of the problem, but could be wrong.Since it's lazy spooling on one query, and not the other this would kind of be expected. (ie. dropcleanbuffers clears out cached data, and lazy spooling basically is caching data for use later on.)----------------------"O Theos mou! Echo ten labrida en te mou kephale!""Where theres a will, theres a kludge." - Ken Henderson |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-10-16 : 00:55:38
|
>>Thanks, Owais.uhhh...you mean thanks, Lavos  Also try turning on the "Show Execution Plan" in Query Analyzer before running the query(you are using QA, aren't you?). That'll produce a nice graphical representation of the query execution plan generated by SQL Server, and you should be able to discern any differences between the plans in the two databases.Owais
Make it idiot proof and someone will make a better idiot |
|
|
|
Hillxie
Starting Member
17 Posts |
Posted - 2003-10-16 : 10:08:43
|
Thank you all! I am using QA and trying the Lavos's post (thanks, Lavos). because my boss doesn't let me spend more time on this problem, so I have to find free time to do this. Thanks all for your help again. |
|
|
|
|
Query Performance