October 17, 2008 at 2:30 am
hi friends,
we have migrated from 2000 to 2005 last week and when i try to expand views or tables in management studio, it takes around 20-30 seconds to load it. Is it really slow for every one or there is any problem with our server/machine? Enterprise manager was very quick; in fact it loads in one second. I feel iritating when it takes times to load something coz we are always looking something quickly during development.
any suggestion abt slow response of 2005?
October 17, 2008 at 4:36 am
sometimes it becomes slow due to processing.
consider this link
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 17, 2008 at 6:15 am
Did you update all the statistics after the migration?
Generally, there are exceptions, I've found SQL Server 2005 to be a bit faster, after some adjustments. You may want to check your queries, look at the execution plans. Especially if you were using query hints, performance might be suffering.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 17, 2008 at 9:07 am
Do you have speed problems with the queries from your application, or do you just have speed problems with browsing the metadata in management studio?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2008 at 9:12 am
As Grant mentioned previously; it is most likely statistics. Update w/ a fullscan on all of your tables - i.e.
SET NOCOUNT ON
DECLARE @Table TABLE
(
Id INT NOT NULL IDENTITY(1,1),
TableName SYSNAME,
SchemaName SYSNAME
)
DECLARE @CurrentId INT,
@Counter INT,
@DynamicSql NVARCHAR(1024),
@TableName SYSNAME
INSERT @Table
SELECT TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_SCHEMA NOT LIKE 'zz%'
AND TABLE_SCHEMA NOT LIKE 'WIP%'
AND TABLE_NAME NOT LIKE '%LMOOLT14_NEPTUNE%'
SELECT TOP 1 @CurrentId = Id, @TableName = '['+SchemaName+'].['+TableName+']', @Counter = Id
FROM @Table
WHILE @CurrentId IS NOT NULL
BEGIN
SET @DynamicSql = 'UPDATE STATISTICS ' + @TableName + 'WITH FULLSCAN,INDEX'
EXEC(@DynamicSql)
DELETE @Table WHERE Id = @CurrentId
SET @CurrentId = NULL
SELECT TOP 1 @CurrentId = Id, @TableName = '['+SchemaName+'].['+TableName+']', @Counter = Id FROM @Table
END
go
Tommy
Follow @sqlscribeViewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply