August 28, 2012 at 1:39 pm
Hello all,
We are having an strange problem:
We have two servers and two databases (Nearly the same data)
DB1 is running on server 1
DB2 is running on server 2
DB1 executes an sql script in 7 seconds
DB2 executes the same sql script in 600 seconds.
DB2 is copied to Server 1 in order to discard server performance issues, but the sql script run slowly too on this server.
Once is clear that server performance is not the problem, we think that is caused by small data variations:
The Scripts call to a view where it seems to be the problem:
Case 1: We replaced the view invocation for a replica table (same view data) and it works very fast.
Script calls table (equivalent to the view)
Case 2: As a result of this we think that the problem could be inside the view (it calls to smaller views), so we replace the smaller views call for the equivalent tables, but it works slowly.
Script calls View -> View calls tables (equivalent tables to the subviews)
Resume: The problem does not seems to be inside the view, because the Case 2 works slow. It seems that the problem is because we call the view, does it make sense? remember that this view is running fast on the DB1
Transaction logs has been trunked, Index has been rebuild, a DB shrink has been executed.
Thank you!!
August 28, 2012 at 1:42 pm
The first mistake that comes to mind is that you shrank the database after rebuilding the indexes, per the last line of your post. That will slow queries down all over the database.
But that's probably not the cause of the problem here.
I'd have to see the definition of the view, and the tables it queries, and probably the execution plan for the query, to be able to help pinpoint the problem more precisely, but the problem is likely to be the view, unless it's just a very simple select on one table.
- 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
August 28, 2012 at 1:54 pm
Please provide the execution plan.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 28, 2012 at 3:58 pm
It was the execution plan, we have cleaned the statistics and the procedure cache and now is working fine.
Thank you very much!!!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply