January 26, 2009 at 2:01 pm
We have SQL Server 2000. I would like to run a query on ServerB (Test) that reads a table on ServerA (Prod) and loads the data into a table on ServerB. The table is about 8 Million Rows (600 byte records). I would like to know if this query (I guess it is a distributed query) would affect performance on ServerA (Prod)? Where would the processing (creating/loading a temp table) be performed (ServerA or ServerB)? I would like to run the following queries on ServerB (Test):
--Create Table on ServerB (Test Server)
SELECT * INTO ServerB.TestDb.dbo.Employee
FROM ServerA.ProdDb.dbo.Employee
WHERE 1 = 0
--Populate Table on ServerB (Test Server) from ServerA (Prod Server)
INSERT INTO ServerB.TestDb.dbo.Employee
SELECT *
FROM ServerA.ProdDb.dbo.Employee
Thanks in advance, Kevin
January 26, 2009 at 2:10 pm
One other question, is there a method to do this backup (of the table) without putting a lot stress on the production server (ServerA)? For example, without writing to the transaction log?
Thanks in advance, Kevin
January 26, 2009 at 3:06 pm
kevinsql7 (1/26/2009)
I would like to know if this query (I guess it is a distributed query) would affect performance on ServerA (Prod)?
Yes - this is going to have an affect on performance because you are reading every single row in the table on ServerA. This will perform a table or clustered index scan and could potentially take out a shared read lock at the table level. If it does escalate to a table lock - you will block all other access until the process is done.
Where would the processing (creating/loading a temp table) be performed (ServerA or ServerB)? I would like to run the following queries on ServerB (Test):
Since the data exists on ServerA - all reads will be performed on ServerA. Depending upon the hardware you have, the network between the two systems and a whole host of other options - reading in excess of 8 million rows is going to take a bit of time.
As for impact on your production system - no, it is not going to affect your transaction log since all you are doing is selecting data. On the other hand, the database you are writing to on ServerB is going to have a huge impact on the transaction log for that database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply