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.
| Author |
Topic |
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
 Posted - 2003-12-17 : 03:30:59
|
Hi all,Got one of those questions which won't go away.We are seeing different execution times for queries across different physical environments, with effectively the same database. The production environment, even when run during "quiet time" - no other users, runs slowest on the production configuration. The specific "bad" series ran 14m48s in production, 11m31s on the Read-only environemnt, and faster (unspecified time) on the 3rd server.THE ENVIRONMENTS :We have a production SQL box (x440, 8 cpu, 4 GB ram, Windows 2K sp3, SQL 2K sp2 +slammmer). We have a 180 GB database in 2 filegroups, with logs on a seperate drive, and backups on another drive (data e:\ logs f:\ backups g:\ - we use EMC SAN for drives, SRDF). DBCC updatestats and reindexing is done only in production.We nightly backups this database (differential), and apply the weekly full and nightly differential to re-create the database on a MIS/Read server (same os and SQL levels, 4CPU, 2 GB ram) - backups, logs and data all on same drive (e:\ - same EMC, but raid 5).We truncate the document table (120 GB), shrinkfile, and create a "small" backup (60GB), and restore that on a 3rd server (x440 6cpu, 2GB ram, same E: F: and G: drive configuration as prod server).THE SPECIFIC QUERY can be posted - it is quite long (3 pages) - it does a 6 table join (left joins  ), 2 updates, and the the same 6 table join to review results.We have no reasonable way of explaining the vagaries, ... anyone give me a way to exlpain this (ideally fix it, if it is something we are missing). Thanx*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-12-17 : 08:20:45
|
| Well, to be absolutely certain of actual performance you need to clear the data and procedure caches BEFORE you run the query, using the following commands:DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHEYou should run these each time you test the query, on each machine. This will give you a clean slate each time, and if nothing else will give you an idea just how badly the query performs (it's pretty aggravating to have a 6 minute query turn out to really be a 20 minute query)Be warned that clearing the caches will affect the entire server, so if you can't afford any drop in performance you should schedule this for a quiet time on the server. |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-12-17 : 09:44:15
|
| Thanx Rob,Any thoughts as to where I might find this hidden differences ? Or would you think that a good profile of them might show one doing significantly more IOs or something ?*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
|
|
|
|
|
varying execution paths and times