Query Designer vs Query Window Speed

Question

Query Designer vs Query Window Speed

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125522 More actions · Answer 1

Enclose each of the queries within the following while you unit test them. This will return statistics such as physical/logical page reads, CPU time, plan text, etc. for comparison.

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

STATISTICS PROFILE is ON;

<query>

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

STATISTICS PROFILE is OFF;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Grant Fritchey SSC Guru Points: 398696 More actions · Answer 2

Eric M Russell (6/1/2016)
Enclose each of the queries within the following while you unit test them. This will return statistics such as physical/logical page reads, CPU time, plan text, etc. for comparison.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
STATISTICS PROFILE is ON;
<query>
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
STATISTICS PROFILE is OFF;

I'd be very cautious about using STATISTICS IO for measuring performance. In lots of tests I've done, it can actually negatively impact performance making it look like you have a problem when you don't or look like your solution isn't working. I've found using extended events radically less intrusive. I even wrote up a blog post [/url]with examples.

"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

Eric M Russell SSC Guru Points: 125522 More actions · Answer 3

Grant Fritchey (6/1/2016)
Eric M Russell (6/1/2016)
Enclose each of the queries within the following while you unit test them. This will return statistics such as physical/logical page reads, CPU time, plan text, etc. for comparison.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
STATISTICS PROFILE is ON;
<query>
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
STATISTICS PROFILE is OFF;
I'd be very cautious about using STATISTICS IO for measuring performance. In lots of tests I've done, it can actually negatively impact performance making it look like you have a problem when you don't or look like your solution isn't working. I've found using extended events radically less intrusive. I even wrote up a blog post [/url]with examples.

Is this behavior something new in v2014, or have we just been fools all these many years?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Grant Fritchey SSC Guru Points: 398696 More actions · Answer 4

Eric M Russell (6/1/2016)
Grant Fritchey (6/1/2016)
Eric M Russell (6/1/2016)
Enclose each of the queries within the following while you unit test them. This will return statistics such as physical/logical page reads, CPU time, plan text, etc. for comparison.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
STATISTICS PROFILE is ON;
<query>
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
STATISTICS PROFILE is OFF;
I'd be very cautious about using STATISTICS IO for measuring performance. In lots of tests I've done, it can actually negatively impact performance making it look like you have a problem when you don't or look like your solution isn't working. I've found using extended events radically less intrusive. I even wrote up a blog post [/url]with examples.
Is this behavior something new in v2014, or have we just been fools all these many years?

I wouldn't say fools. I'm just not sure too many of us took the time to measure it all. I never did until I couldn't figure out why I wasn't seeing improvements when the changes certainly should have resulted in them. It's also not always going to be a problem. I was dealing with sub-second queries trying to make them faster. For long running queries, I didn't see any impact at all. Still, I like to compare apples to apples as much as possible.

"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

mceventphoto Ten Centuries Points: 1299 More actions · Answer 5

Ok folks, I think I am losing my mind. Now, I am unable to recreate the situation. Thanks all for looking into this and I am so sorry for letting you waste your time on this. If I can recreate it, I will post something.

Thanks,

MC

subramanian.esakkimuthu 40485 SSCrazy Points: 2022 More actions · Answer 6

check the setting in query designer it should not have much difference running the same code