Sunday, January 31, 2016

sql server - What would cause a query being ran from SSMS on local box to run slower then from remote box




When I run a simply query such as "Select Column1, Column2 from Table A" from within SSMS running on my production SQL Server the results seems to take extremely long (>45Min). If I run the same query from my dev system’s SSMS connecting to the production SQL Server the results return within a few seconds (<60sec).



One thing I have notices is if the system was just rebooted performance is good for a bit. It is hard to determine a time as I have had it start running slow very quickly after reboot but at most it performed good for 20min and then start acting up. Also, just restarting the SQL service does not resolve the issue or provide a temporary performance boost.



Specs for Server are:
Windows Server 2003, Enterprise Edition, SP2
4 X Intel Xeon 3.6GHz - 6GB System Memory
Active/Active Cluster
SQL Server 2005 SP2 (9.0.3239)



Answer



Have you compared the execution plans from both servers? Have you tried querying your production server locally, when the results slow down? Have you checked to see if you have any blocking, or resource waits on your production server?


No comments:

Post a Comment

linux - How to SSH to ec2 instance in VPC private subnet via NAT server

I have created a VPC in aws with a public subnet and a private subnet. The private subnet does not have direct access to external network. S...