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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply