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 |
|
Kristen
Test
22859 Posts |
 Posted - 2004-12-09 : 14:26:01
|
OK, now I'm panicing.New, bigger, faster SQL box installed Tuesday morning.I have a simple report. Most of our SProcs log their Start and End time. So I can do total elapsed time / number of SProc calls. That gives me average time / SProc.I can also do this for a specific SProc. "What's the average time it took to run SP_XXX on monday?"So ... Tuesday's figures were GREAT! Three times faster than the old box.Wednesday's figures were terrible - already back to the same sort of level as the old box.The "load" was pretty much the same both days.What gives? Clearly the box started off with a RESTORE (into contiguous disk space I presume). I didn't do any defrags etc. on Day 2 - but I did this morning, and we aren't a lot better off.This morning I did DBCC REINDEX on everything under 85%, and an sp_updatestats.Anythign else I should be doing?Here are some comparisons to give you an example. (1) is Tuesday, (2) is today (Thursday) so far. Figures for yesterday were about twice as bad as todayCount1 Count2 Avg1 Avg2 %age Max1 Max2 Elapsed Name------- ------- ------- ------- ------- ------- ------- -------- ------------------67657 31758 189 489 -158 12796 10656 15533252 rk_SP_XX_ProductListing2_Get21791 10716 379 622 -64 4250 26673 6670864 kk_SP_PKID2Params132163 64584 32 129 -303 5516 13123 8333794 rk_SP_PageLogInit17920 8662 190 751 -295 2656 7093 6509395 dng_SP_SOP_ORDI_BasketAdd122027 59498 25 110 -340 1390 1623 6591313 rk_SP_XX_GetPageParamsDefaults9079 4365 272 395 -45 2733 8563 1724563 dng_SP_SOP_ORDI_BasketList_V217003 8281 99 323 -226 2236 2296 2681332 dng_SP_SOP_ORDI_Save3455 1772 480 1158 -141 5483 11280 2053569 rk_SP_PageLogin3117 1639 434 956 -120 9796 11436 1567867 dng_SP_ADR_REG_NewRegister_Save23 24 55887 68698 -22 277406 154170 1648767 XX_SP_XFER_MaginusUpdateAll2723 1398 464 829 -78 5346 10936 1159334 rk_APP_SP_PageLogin Count = Number of executionsAvg = Average elapsed time Start-Finish (ms)%age = difference in average execution timeMax = longest execution time (probably meaningless - maybe it ran when a REINDEX was running or somesuch)Elapsed = Total time used for all executions of that SProc (ms)Kristen |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-09 : 14:30:33
|
| Try an sp_updatestats with a resample of 100%, or UPDATE STATISTICS...WITH FULLSCAN on all tables. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-09 : 18:05:38
|
| thanks Rob, I'll report back tomorrowKristen |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-12-10 : 04:34:19
|
| How do you collect this data? Profiler?? It would be *very* interesting to see for my production database, so unless it's alot of overhead involved I'd REALLY like to know how you do this...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-12-10 : 04:56:18
|
| I think the statement "Most of our SProcs log their Start and End time" indicates a home-built logging table. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-12-10 : 05:35:11
|
| Hm, it crossed my mind too but I was hoping for something easier...crossing my fingers!--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-12-10 : 06:07:50
|
| check out traces:sp_trace_setevent -- 42, 43The logging table is easy and efficient though.rockmoose |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-10 : 16:49:03
|
| "I think the statement "Most of our SProcs log their Start and End time" indicates a home-built logging table."That's perzactly what we do.Today she's going like a rocket. Except the SMTP log is full of errors. Gawd knows what's causing that - but one thing is sure: all the people who shop only once a year aren't getting the "forgotten passwords" so the ratio of sessions to completef orders is, errrmmm ... a bit dire!Another darn client-economy to use the poxy MS SMTP service, rather than somethign worthwhile like SMTP Express ... Something else to build into the Disaster Recovery Consultancy <LicksLips> for the Quiet Period in the spring.We've zipped up some queued EML files and dropped them in the Pickup folder on another box to get them out-of-the-door, but its too much of a time lag to be "instant gratification".Yesterday the telecoms company managed to dig through the fibre for the phones, so that took 60 phone lines out for 9 hours :-(At least the hired generator ($180 a week, provided we don't use it!) is looking cheap this year - no gales blowing down all the trees, so far!Sheesh! I should write a book. Or maybe a blog ;-)Kristen |
|
|
|
|
|
|
|
|
Slow Slower SLOWEST