Huge performance differences between connections

I was trying to test the performance difference between inserting into a table directly versus inserting into a view in a different database which points to the table. I was shocked to see that a WHILE loop with 10,000 inserts into a simple table took 3 seconds but against the view it took 30 seconds.

As I investigated, I found that if I switched windows in SSMS the view performance was the same as that of the table as I thought it should have been. I then switched DBs in the view window to the table DB and inserted there and it was slow when going directly against the table.

This pointed me to the idea that there was some difference in the connections. These were different tabs in SSMS, one created from the other with -N. I didn't issue any SET commands other than SET NOCOUNT ON (in both windows) and all statistics, query plans, etc. were turned off.

When I ran SQL Profiler to look at the connections, they had identical settings. The performance difference was 100% consistent though.

I checked that there were no transactions open in either connection.

I tried opening another instance of SSMS and I cannot duplicate the slow performance (although it is happening in two out of three of my tabs in the first instance of SSMS).

This is all on a local server. I'm going to need to shut it down in about an hour and I don't know if I can reproduce this problem, so hopefully I can get some suggestions before then :)

Any other ideas on what the difference might be?

Thanks. I didn't mention it in my message, but I checked that and dm_exec_connections

Tom H. 2010-06-04 19:45:09

You should probably focus on trying to find where is the time difference spent. While the slow and fast query are executing, pool on sys.dm_exec_requests wait_type, wait_resource and wait_time, see if there is any obvious difference in the pattern. This should give you a hint *why* is the time difference occuring.

Remus Rusanu 2010-06-04 20:46:37

Btw, make sure you do not hit database and/or log growth events every Nth run which *happen* to be hit always on the same window.

Remus Rusanu 2010-06-04 21:24:08

ansaurus

tags:

views:

answers:

Huge performance differences between connections

related questions