January 27, 2011 at 10:40 am
Hi,
I have a situation where we log audit data to a live database and periodically (twice a day) log-ship to a secondary read-only database (same cluster, separate instance) for reporting.
I noticed something strange today... one of my colleagues wrote a piece of SQL that I thought looked inefficient, so I rewrote it and we compared the two against the reporting database. The execution plan (both estimated and actual when executing the two queries) suggested the new formulation was vastly superior (as in the new one 0% of the batch, the original 100%), but on executing them both the original took 10 seconds, the rewritten form took 3 1/2 minutes! The result was the same when placing either one first of the two, on rerunning etc., and I even had the plan cache cleared to no avail.
Puzzled (to say the least, as well as a little humiliated that my 'improved' code was so poor) I decided to check the plans on the live database, only to find that the two plans were much closer (55% to 45% of the batch), and that changed when I varied the timespan for the report. Running them pretty much followed the estimates, and the same parameters that had produced the times mentioned earlier gave 10 seconds for the old SQL (as per the log-shipped database) and 5 seconds for the new version (against 3 1/2 minutes!).
Does anyone know what is going on here? On the log-shipped database, the old form of the SQL was quicker in every case, the gap growing the longer timespan was included in the report. On the live database, however, the new form was consistently twice as fast as the old.
Could this be due to the read-only nature of the database - that it can't either form or save plans properly, or perhaps that log-shipping screws with the statistics?
I'm very confused right now (though relieved that my supposedly more efficient SQL turned out that way after all!)
Cheers,
Dave.
January 27, 2011 at 10:58 am
statistic should be the same. What about the servers themselves, are they roughly the same in terms of hardware and disks?
I am saying this because a plan that was complied for a procedure on server A might not be the best plan for server B, even though the data is the same.
The probability of survival is inversely proportional to the angle of arrival.
January 27, 2011 at 3:03 pm
As to why the costs might not accurately reflect performance, that's because query plan costs do not accurately reflect performance. They are estimates of cpu and io cost, completely dependent on statistics and the types of operations. For example, if you put in a multi-statement table valued function, the cost will be near zero. This is because those functions have no statistics at all, so the optimizer assumes 1 row. But if there is more than one row, the cost estimates & reality will be radically off.
I'm not that familiar with log shipping, but I would assume you would still need to maintain the statistics. You might try updating the stats to see how that affects the behavior.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 28, 2011 at 4:18 am
They're just different instances on the same server cluster.
The actual plans used (according to SSMS at least) match the estimates in both cases.
January 28, 2011 at 6:11 am
what about tempdb... are the files configured the same and on the same spindles on both servers (instances)? What about memory... is the same amount of RAM allocated to each?
Unless the two instances are exactly the same you can't expect them to perform the same.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply