Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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)

share|improve this question

migrated from stackoverflow.com Sep 3 '09 at 2:35

This question came from our site for professional and enthusiast programmers.

add comment

6 Answers

up vote 0 down vote accepted

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?

share|improve this answer
    
Execution plans for both local and remote queries are the same. The issue only occurs when querying on the local production server. Activity Monitor does not show any blocking, locks on resources used within the query. Status of query is suspended with Wait Type Async_Network_IO. This status and Wait Type is the same on both local query and remote query, but the remote returns dramatically faster. –  Racter Sep 14 '09 at 20:37
    
Have you looked at this then? blogs.msdn.com/joesack/archive/2009/01/09/… –  Aaron Alton Sep 15 '09 at 1:08
    
Yes, I read that and I am having our Net Ops person investigate the network. –  Racter Sep 17 '09 at 17:19
add comment

how many rows are you returning?

it is possible (but not likely) that when you return the results on the server you are starving the server for resources because of the need to display all the results in SSMS.

In SSMS on the server go to Tools->Options->Query Results->SQL Server->Results to Grid. Check the option discard results after execution

Then open a new query window, press CTRL + D (to ensure results output to grid) and run your query again. Does it still take 45 mins?

share|improve this answer
    
Number of rows vary depending on the query and table used. For the query that is taking ~45min I am returning around 2.2 million records but only two columns. The slow performance issue occurs on all tables in the database. Again, these queries run under 10 min when executed from a different system. Issue only occurs when executed from same system the database is on. Discarding the results after execution did not change the time the queries takes. –  Racter Sep 14 '09 at 20:28
add comment

Can you go to your remote server properties, and select the 'Connection' node. Are any of the 'Default connection options:' ticked?

share|improve this answer
    
SQL Server Properties->Connections? There are no Default connection options checked. –  Racter Sep 14 '09 at 20:39
add comment

45 mins vs 60 seconds? That's... unusual. I've seen 45 minute queries before, but never anything in the realms of something as simple as "select col1 from table". Even 60 seconds should retrieve hundreds of thousands of records in that time.

That said, I've no idea why this might be acting like that, but I would look into your indexes on your tables to see if they're appropriate, or if they even exist, and then run the query optimiser to get its opinion.

share|improve this answer
    
I have taken the query which took ~45min to run and used the Tuning Advisor. Suggestion was to create a new index. I implemented the suggestion and still no improvement. –  Racter Sep 14 '09 at 20:31
    
That means that the problem does not lie in the SQL itself, butrather somewhere else upstream... –  Mark Henderson Sep 14 '09 at 20:59
add comment

Might want to run UPDATE STATISTICS on the table(s) in the query

share|improve this answer
    
I have done this and it didn't change the performance on the local box. –  Racter Sep 14 '09 at 20:29
add comment

I have noticed the same thing, it seems to be a SSMS issue....

this statement, run from code, executed in 5 seconds..

ALTER TABLE xxx ADD XID_New BIGINT NOT NULL CONSTRAINT DF_xxx DEFAULT 0

the same statement, run from SSMS has been running for 7 minutes..

ALTER TABLE xxx ADD XID_New BIGINT NOT NULL CONSTRAINT DF_xxx DEFAULT 0

same machine, same DB, same code, one run from c# project, the other run from SSMS, there must be a bug or issue with SSMS

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.