slowly running query after rename sql server 2005

Question

Hello,

I have sql server 2005 standart with sp3 on windows xp professional with sp3 too in first computer and sql works well and queries run quickly.
second computer is same by hardware and "image" of first but it has another name - that is to say I didn't install the sql on second, I renamed it using:
sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO
now the sql server on second computer works but very slowly.
same simple query runs 0-1 second on first and 17-19 seconds on second.

what can I do to improve the performance?
or I need reinstall sql?

Answer 1

What is the number of records in the tables you are querying?

Maybe you need to do reindexing again.

---

Ali Hamdar (alihamdar.com - www.ids.com.lb)

Answer 2

 Are you  using VM in same system or you are using different system ?

Answer 3

up to 1000 lines in both cases

Answer 4

it's not VM, it's windows xp pro only

Answer 5

Did you checked there is any index on that table or not ?  

Answer 6

I did checked indexes

i restored db from backup on second and checked users.

databases must be identical by their structure

Answer 7

1. Both machine are identical in terms of CPU,RAM,Hardware? Run update statistics on the second machine and  see for any improvement

 DECLARE @tablename varchar(80),@shemaname varchar(80)
DECLARE @SQL AS NVARCHAR(200)
DECLARE TblName_cursor CURSOR FOR
SELECT t.name,s.name FROM sys.tables t join sys.schemas s
on s.schema_id=t.schema_id




OPEN TblName_cursor


FETCH NEXT FROM TblName_cursor
INTO @tablename,@shemaname


WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'UPDATE STATISTICS '+@shemaname+'.[' + @TableName + '] WITH FULLSCAN ' ---+ CONVERT(varchar(3), @sample) + ' PERCENT'


EXEC sp_executesql @statement = @SQL


   FETCH NEXT FROM TblName_cursor
   INTO @tablename,@shemaname
END


CLOSE TblName_cursor
DEALLOCATE TblName_cursor

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 8

same simple query runs 0-1 second on first and 17-19 seconds on second.

what can I do to improve the performance?

Is it the query itself that is taking 17-19 seconds or is does the slowness occur when the connection is opened?  In the latter case, it could simply be a name resolution issue.  For example, Windows might first try to resolve the name using DNS and fall back to NetBIOS computer name after a timeout (20 seconds).  Try a simple ping on the client using the same name specified in the connection string to see if you see the same behavior.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 9

thank you, it works now much better

Answer 10

thank you but it's not connection

Answer 11

Hi KseniaDralov,

To verify that the renaming operation has completed successfully.
 
Select information from either @@SERVERNAME or sys.servers. The @@SERVERNAME function will return the new name, and the sys.servers table will show the new name. The following example shows the use of @@SERVERNAME.

SELECT @@SERVERNAME AS 'Server Name'

Linked server configurations will be affected by the computer renaming operation.

Client aliases that use named pipes will be affected by the computer renaming operation.

For the default instance, you will have to check the SPs, triggers and queries if those queries are using static instance name and not using @@servername , serverproperty ect. And change them after the servername is changed.

Reference: http://msdn.microsoft.com/en-us/library/ms143799.aspx.

Thanks,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.