SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL behaving strangely on different servers


SQL behaving strangely on different servers

Author
Message
nadersam
nadersam
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4233 Visits: 574
Hi All,
I have a sql statement working on static tables (no Insert,Update,Delete) on our testing server it's running very fast almost 0 m sec execution time while on production servers it takes much more ranging from 3 to 9 secs depending on where conditions.
I suspected the indexes could be fragmented or statistics might be out dated so i made rebuild indexes for all tables used in the SQL.
Also i compared index structure on all tables in both databases and both are the same.
I attached the execution plan for slow and fast servers and ready to post anything else that might be needed to determine what's reason for that strange behaviour.

Looking forward for you help.
Regards
Nader
Attachments
DIFast.sqlplan (46 views, 266.00 KB)
DISlow.sqlplan (39 views, 246.00 KB)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (672K reputation)SSC Guru (672K reputation)SSC Guru (672K reputation)SSC Guru (672K reputation)SSC Guru (672K reputation)SSC Guru (672K reputation)SSC Guru (672K reputation)SSC Guru (672K reputation)

Group: General Forum Members
Points: 672593 Visits: 48433
Is it getting blocked by other queries on production?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


nadersam
nadersam
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4233 Visits: 574
Thanks for yo
GilaMonster - Thursday, June 15, 2017 2:54 AM
Is it getting blocked by other queries on production?

Thanks for your reply.
I already thought about that reason but i rule it out as those tables are static (no Insert,Update,Delete).

Thomas Rushton
Thomas Rushton
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: Moderators
Points: 13815 Visits: 7042
Is the physical hardware and configuration of both servers identical?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (672K reputation)SSC Guru (672K reputation)SSC Guru (672K reputation)SSC Guru (672K reputation)SSC Guru (672K reputation)SSC Guru (672K reputation)SSC Guru (672K reputation)SSC Guru (672K reputation)

Group: General Forum Members
Points: 672593 Visits: 48433

nadersam - Thursday, June 15, 2017 3:01 AM
GilaMonster - Thursday, June 15, 2017 2:54 AM
Is it getting blocked by other queries on production?

I already thought about that reason but i rule it out as those tables are static (no Insert,Update,Delete).

Then you may want to revisit it as a possible reason, as deletes get blocked by shared locks, which are what read queries take.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


nadersam
nadersam
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4233 Visits: 574
ThomasRushton - Thursday, June 15, 2017 3:01 AM
Is the physical hardware and configuration of both servers identical?

The live server is much much higher configuration , in fact that's the only SQL that perform slower in live environment that test.

nadersam
nadersam
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4233 Visits: 574
GilaMonster - Thursday, June 15, 2017 3:05 AM

nadersam - Thursday, June 15, 2017 3:01 AM
GilaMonster - Thursday, June 15, 2017 2:54 AM
Is it getting blocked by other queries on production?

I already thought about that reason but i rule it out as those tables are static (no Insert,Update,Delete).

Then you may want to revisit it as a possible reason, as deletes get blocked by shared locks, which are what read queries take.

Let me please make sure i understand you correctly, do you mean that normal select SQLs cause locks which might be the reason for that slow performance?
If so could i add with (nolock) to all tables used in my SQL.

Thanks

nadersam
nadersam
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4233 Visits: 574
From the execution plan does it show where is the bottle neck coming from?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (672K reputation)SSC Guru (672K reputation)SSC Guru (672K reputation)SSC Guru (672K reputation)SSC Guru (672K reputation)SSC Guru (672K reputation)SSC Guru (672K reputation)SSC Guru (672K reputation)

Group: General Forum Members
Points: 672593 Visits: 48433
nadersam - Thursday, June 15, 2017 3:28 AM
GilaMonster - Thursday, June 15, 2017 3:05 AM

nadersam - Thursday, June 15, 2017 3:01 AM
GilaMonster - Thursday, June 15, 2017 2:54 AM
Is it getting blocked by other queries on production?

I already thought about that reason but i rule it out as those tables are static (no Insert,Update,Delete).

Then you may want to revisit it as a possible reason, as deletes get blocked by shared locks, which are what read queries take.

Let me please make sure i understand you correctly, do you mean that normal select SQLs cause locks which might be the reason for that slow performance?

Well, yes. Queries take locks to ensure that they read correct data.

If so could i add with (nolock) to all tables used in my SQL.


You could... You'd probably cause more problems than you solve due to the incorrect results that nolock
causes, but if you're happy with your users getting incorrect data fast, then go ahead.

Or, you could check and see whether you have blocking first, and if so take steps to fix the affected queries.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


nadersam
nadersam
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4233 Visits: 574
GilaMonster - Thursday, June 15, 2017 3:33 AM
nadersam - Thursday, June 15, 2017 3:28 AM
GilaMonster - Thursday, June 15, 2017 3:05 AM

nadersam - Thursday, June 15, 2017 3:01 AM
GilaMonster - Thursday, June 15, 2017 2:54 AM
Is it getting blocked by other queries on production?

I already thought about that reason but i rule it out as those tables are static (no Insert,Update,Delete).

Then you may want to revisit it as a possible reason, as deletes get blocked by shared locks, which are what read queries take.

Let me please make sure i understand you correctly, do you mean that normal select SQLs cause locks which might be the reason for that slow performance?

Well, yes. Queries take locks to ensure that they read correct data.

If so could i add with (nolock) to all tables used in my SQL.


You could... You'd probably cause more problems than you solve due to the incorrect results that nolock
causes, but if you're happy with your users getting incorrect data fast, then go ahead.

Or, you could check and see whether you have blocking first, and if so take steps to fix the affected queries.

If the data in those tables are static would the with (nolock) hint still cause a problem?
If i found any blocking happening , what should be the solution knowing that index wise they have got all what they need.

Thanks again

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search