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-05-09 : 02:58:01
|
| Guys,We are in Deep $h1t here ...SQL 7 SP 2 to SQL 2000 SP 2 + slammer upgrade.Testing showed no problems (extensive testing, yet obvioulsy not extensive enough)...Application upgrade.Monday morning 7:30 = coolMonday morning 8:00 = okMonday morning 8:30 = time outs from some client doing a specific complex transactionMonday morning 9:30 = time outs from almost all client doing a specific complex transactionPROBLEM - complex transaction is also CORE transaction for call centre.SO Far:a SQL statement as part of app upgrade was found, and after indexed, it allows users to work from Tuesday afternoon.Wednesday 7:30 = coolWed morning 8:00 = okWed morning 8:30 = still ok wed morning 9:30 = still ok - we smileWEd 13:00 = time outs from almost all client doing a specific complex transaction - we cry.As part of upgrade, a full dbcc reindex was done, and also sp_updatestats on db's.Question : with the talk of _wa indexes etc., SQL 2000 doesn't "inherit" the SQL 7 ones, and use them ? If not, then how long before it builds up it's own ?Any thoughts ? More info needed*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
Peter Dutch
Posting Yak Master
127 Posts |
Posted - 2003-05-09 : 07:02:07
|
| Maybe I'm saying something obvious here, but you could trace for a while (with profiler) and examine the output, or check the execution plan for the complex query. |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-05-09 : 09:27:03
|
| @Peter - Thanx for the response.We profiles for 20 minutes on monday morning, which generated a 500 MB tracefile! We are working through this - we've already implemented a new index based on a SQL statment we found that was doing a index scan (on 500K row table), improve it to a index seek. (scary - the table was accessd as a nexted sub-select, as well!).Having said that, we are struggling to prove that new, poor SQL is the culprit (esp. since we don't own the code - it's a vendor package, specialized by the vendor for us). Concerns are raised wince we are seeing current disk queue length's jump from 2 and below, all the way to 170 +, and sometimes extending over a periods of minutes with averages in the region of 50 to 60. COuld be indicative of extremely bad access paths ?Ciao*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-05-13 : 06:54:23
|
| Well, an update ...We seem to be ok-ish (i.e. not really, but business can run). Amongst other things, a sys adm installed Anti-Virus software without infomring anyone, and incorrectly set it up to scan ALL files when they're opened. That DIDN'T help, I can tell you. SIGH - we're slowly maturing our environment, nad using performace to get vendor to review their code, their data structure etc. I just hate having to have had some much hassle and stress to get here!!! The vendor are now trying to figure out what are the "hotspots" (high contention / usage) tables, and trying to figure out what SQL they have, so that it can be reviewed. Some scary stuff in there ... "select *" the STANDARD, in online transactions, we've seen select * from prices (a 500K + row table!), a cartesian product (cross-join ) that never get's used - Thank Goodness , because it is a 400 K table and a 500 K table ... so how long do you think a transaction with a 200 000 000 000 resultset will take ? I could go on, but I think you get the jist...Ciaoand wish us luck*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-13 : 10:09:35
|
Regan,post resume now!  No but really, server ops installing virus software without telling anyone...cool  Anyway...isn't that your calling?quote:
Chaos, Disorder and Panic ... my work is done here!
I wise consultant once said to me:quote:
Chaos means money
Good luckBrett8-) |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-05-19 : 07:20:05
|
| Thanx Brett,Yeah - we have basically "recovered" by adding some indexes on new, bad SQL, reconfiguring the virus scanner, connecting new server to same point of network as our previous prod server ("oh - they forgot to move it" so we had an extra network hop to a switch , router issues for part of our client base, and that's just the tip.Having said all of the above, the system running on SLQ 2K seems a lot more sensitive to outside influence - very difficult to quantify , this is more perception than proof. Our newest headache is the SQL 2K change to reindex and indexdefrag , in terms of the impact on logs, now that these operations are fully logged. Maybe now they'll (management) realize we want ALL parts of the prod environment tested when they test new versions, not the odd backup and the applications.*sigh**#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here!Edited by - wanderer on 05/19/2003 07:22:23 |
|
|
|
|
|
|
|
|
SQL7->SQL 2000 - timeouts, performance down the...