July 4, 2008 at 9:21 am
Hi,
I've recently upgraded a 2000DB to 2005.
The SQL2005 DB is a publisher in merge replication to SQL2000 Subscribers (Push).
3 of my 6 subscribers are experiencing frequent timeouts when apps run against them.
Will Updating the statistics help? Also should this be done against each db?
I'm quiet new at this so any advice (no matter how basic) is greatly appreciated...
July 4, 2008 at 2:03 pm
It should do. It is recommended that the statistics of all tables (in all databases) get updated after upgrading from 2000 to 2005.
The reason is that 2005 keeps more detailed stats than SQL 2000 did. While the 2005 optimiser can use the 2000 stats, it doesn't do so very well.
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
July 6, 2008 at 3:06 am
Thanks for the input.
The strange thing is that the SQL2000 subscribers (and only 3 of the 6) are experiencing the timeouts. Users connecting to the 2005 DB have not experienced any issues.
I've run SP_UpdateStats but this did not help. Would running UPDATE STATISTICS with FULLSCAN make a difference?
I'm gonna try SQL profiler in the mean time ...
July 6, 2008 at 4:22 am
Hmmm. Probably not. Sorry, I missed that it was the people using the 2000 box that were having problems.
Yup, is a good time for profiler. Also maybe start up perfmon and check some counters. Lock waits/sec, latch waits/sec and some replication counters would be a useful place to start. Also check if you have any blocking (sysprocesses)
Has anything else, other than the 2005 upgrade happened?
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
July 6, 2008 at 4:32 am
The results from SQL profiler pointed to our Currency table.
On closer analyis the index fragementation is 97% on this table...
July 6, 2008 at 5:52 am
Rebuilding the index on the table did not make solve the issue 🙁
I'm gonna retrace my steps and collect some more data with performance monitor ..
July 6, 2008 at 6:19 am
Is there any blocking? If you look at the results from profiler, are certain queries slow and other not?
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
July 7, 2008 at 7:39 am
Profiler has not picked up any deadlocks *or blocking that i can see.
Yes some queries quick while others just time out or are incredibly slow.
Some of the users have indicated slow access on the rest of the subcribers aswell so I'm begining to think it has to be the way I've setup the replication.
Thanks a great deal for your help. I'm gonna create a new topic to get advice on setup of Repliciation with a 2005 publisher and 2000 subscribers, must be something I've missed.
July 7, 2008 at 7:44 am
Grinja (7/7/2008)
Profiler has not picked up any deadlocks.Yes some queries quick while others just time out or are incredibly slow.
Not asking about deadlocks. If you look in the sysprocesses table, do you see any sessions blocked by other sessions?
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
July 7, 2008 at 7:55 am
Sysproperties in the Master table does not indicate any blocks ...
July 7, 2008 at 12:54 pm
you may want to read this thread: http://qa.sqlservercentral.com/Forums/Topic523260-291-1.aspx
* Noel
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply