1

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)

0

6 Answers 6

0

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?

3
  • 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, 2009 at 20:37
  • Have you looked at this then? blogs.msdn.com/joesack/archive/2009/01/09/… Sep 15, 2009 at 1:08
  • Yes, I read that and I am having our Net Ops person investigate the network.
    – Racter
    Sep 17, 2009 at 17:19
1

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?

1
  • 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, 2009 at 20:28
0

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

1
  • SQL Server Properties->Connections? There are no Default connection options checked.
    – Racter
    Sep 14, 2009 at 20:39
0

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.

2
  • 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, 2009 at 20:31
  • That means that the problem does not lie in the SQL itself, butrather somewhere else upstream... Sep 14, 2009 at 20:59
0

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

1
  • I have done this and it didn't change the performance on the local box.
    – Racter
    Sep 14, 2009 at 20:29
0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .