December 11, 2014 at 3:05 am
Hi there - I am troubleshooting a performance issue. On our production environment (168GB, 24cores) a fairly basic query takes 20secs to run, but if I restore the same database to a VM (40GB, 8cores) the same query takes 1 second.
SELECT count (*) FROM
WITH (NoLock) WHERE (table.FID IN (359890) OR
(table.FID IN (N'19508e3b-0595-41cf-88f2-c13141e0bc40') AND table.FID = 0))
AND table.date>= '09 November 2014 23:59:59'
AND table.date <= '11 December 2014 23:59:59'
AND table.Type >= 201 AND table.Type < 400
Why would this be? Is there something the restore process does to improve the performance?
Thanks .
--------------------------------------------
Laughing in the face of contention...
December 11, 2014 at 3:40 am
Too much of a hardware difference to say anything useful.
Execution plans of fast and slow?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 11, 2014 at 3:52 am
Hi - I have attached the execution plans. There is a slight difference in the number of records this because the restore took place a week ago. But the difference in runtime is far greater to attribute the difference to the number of records. Thanks again.
--------------------------------------------
Laughing in the face of contention...
December 11, 2014 at 4:28 am
Cracked it. It was a statistics issue. They're not being updated. Thanks!
--------------------------------------------
Laughing in the face of contention...
December 11, 2014 at 8:22 am
Glad to hear the solution. I was also going to suggest looking into bad parameter sniffing, but it's probably just the stats.
"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
December 11, 2014 at 2:50 pm
Your initial query was impossible:
WHERE (table.FID IN (359890) OR
(table.FID IN (N'19508e3b-0595-41cf-88f2-c13141e0bc40')
but the query plan cleared it up: the second column is a different column from the first one.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply