December 10, 2019 at 3:50 pm
I have a user sql from 3rd party started running terrible on prod server. has 128 gig ram, 4 procs plenty of disk space. It basically runs forever and buries tempdb (yes it is a cte and it is ugly). take a backup of prod drop on uat server, 2 procs 16 gig of ram, all sql config settings match, it runs in 13 minutes.
December 10, 2019 at 4:00 pm
sorry hit submit too fast, any ideas on this one, makes no sense at all why does query on high horsepower server bury tempdb with 5 times the ram. They are both enterprise edition and both vmware same level
December 10, 2019 at 4:02 pm
still type too fast, prod is enterprise edition where uat is actually developer, microsoft feature?
December 10, 2019 at 4:14 pm
Have a look at the query plans generated by both of the servers. They will almost certainly be different. Does the production query actually finish? If so, you can tell from the query plan which steps (sorts, joins, etc.) may be spilling data to tempdb.
if you update statistics on the UAT server, does the UAT server begin to perform badly, then?
December 10, 2019 at 4:35 pm
still type too fast, prod is enterprise edition where uat is actually developer, microsoft feature? Also since prod dies can only look at est plan, but it matches the plan on uat when completes. use Hallengren index rebuild, was going to run rebuild on uat to see if that breaks it
December 10, 2019 at 4:42 pm
Hmm. Same query plan? Are there any external influences? References to other databases, linked servers, or even custom CLR types/functions?
December 10, 2019 at 4:49 pm
no i just took sql straight from sentry one capture, ran on in mgmt studio n on dev edition runs great, this just started. All the configs are same been doing sql 25 years never seen this one
December 10, 2019 at 7:19 pm
Ok perfect storm, do db backup and restored which is supposed to bring stats with this. Runs fine on dev. I use Ola's index stuff on everything. It did fail this last saturday but worked week before. This issue started last tuesday. So ran another rebuild on prod and now working as normal. It was odd that the plans looked the same but after index rebuild logical reads way down.
December 10, 2019 at 9:34 pm
check VMWare, right size VM , new host added to exi ? check CPU ready
December 11, 2019 at 1:50 pm
If the exec plans are the same, it's got to be a difference elsewhere. Database settings (probably not since this is a restore), server settings, os settings, vm settings. Something is different. Also, hardware differences, faster/slower CPU, more/less Memory, faster/slower disks.
Side note: Don't be afraid to use the estimated plans as evaluation tools. In most cases, the estimated plans will be the same as what is called "actual plans". Just know, all plans are estimated, some just have runtime metrics.
"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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply