May 6, 2008 at 8:04 am
last weekend we had an upgrade, my data retrieval time from the replication server is extremely slow. a report that previously took maybe 15 seconds to return data is taking 5 minutes now. I have made no changes to the query.
running on sql server 2005
replication is working fine
can any one tell me what might be the problem
May 6, 2008 at 8:28 am
When you say you had an upgrade, do you mean you upgraded from SQL 2000 to SQL 2005, or do you mean something else?
There are a lot of reasons for a query to slow down. RAID cards going bad, RAID drives going bad, indexes being out of date, table/index fragmentation, increases in network traffic, network routers/switches having problems, increased traffic on one or more servers, etc., ad naseum.
If you can give a little more data on what was changed, and some data on what you've checked so far, we might be able to help you narrow it down more.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2008 at 8:40 am
we upgraded the software version, and the problem is only while running the queries, even basic queries are running slow.
if its a problem of indexes how do i need to correct it.
May 6, 2008 at 11:21 am
I'd start by checking the tables and indexes for fragmentation and the statistics for how recent they are.
You can do this by right-clicking these things in Management Studio and checking the properties.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 7, 2008 at 2:37 pm
Hio
Your best bet here is to take the following approach.
Even a software vendr states that is was only a software upgrade you will be surprised to see what actually changed.
Start by running SQL server profiler and performance monitor and correlate the data. The easiest way to do this is using PSSDIAG. Run the tool for a day and start analysing the data.
Look at CPU,Memory,Disk at sql server engine level
Look for blocking analysing the blocker script with sherlock (small app that is very usefull)
Filter through the trace looking for high CPU IO , Read IO and long duration queries
Also look at your DMV's constantly to identify bottlenecks
This all sounds lenghty but there is alot of benefit ..number one your are fimiliarizing yourself with the production load.
May 9, 2008 at 4:14 pm
I have a database whose size increased from like 28000 records to 800K records ansd searching in this database takes about 18 seconds which is not a pleasure, can anyone guide me on this... 28K database used to take 1-6 seconds.
--archana
May 21, 2008 at 8:24 pm
Way too generic a question... but here's some generic answers...
You need to make sure the correct indexes are in place and that the queries can actually use them effectively. You need to ensure that TempDB has been sized correctly on boot up. You need to make sure you're using good set-based code instead of RBAR. You need to make sure that statistics have been correctly updated. You need to make sure that you have a good maintenance plan to rebuild or defrag indexes. You need to make sure that the OS file system is defragged. You need to .... etc, etc.
--Jeff Moden
May 22, 2008 at 5:14 am
First thing I would do is compare the original database's structure to the upgraded one. Look for missing indexes.
Also, try updating statistics and also possibly doing a reindex/rebuild of indexes.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply