December 3, 2009 at 11:42 am
Hello,
We have a main database and a separate database for reporting. Each night, a full backup is performed to copy the main database to the reporting database. The recovery model of the backup database is then set to Simple. (The original one is Full.)
The point of the separate reporting database is to avoid deadlocks, etc. when running reports on data that might be in the process of being updated.
However, some stored procedures take MUCH longer to run on the reporting database then on the main one. One in particular takes 30 seconds on the reporting DB and only 1 second on the main DB. Other than the recovery model, these two database are identical. Same machine, same drives, etc.
Can you think of anything that would explain what I am seeing!? I have already checked for parameter sniffing issues and that is not it.
Thanks!
December 3, 2009 at 12:01 pm
What about Indexing?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 3, 2009 at 12:02 pm
Execution plan caching, or lack thereof. Data caching, or lack thereof. Those two would be my prime suspects.
After you do the restore, try running a few queries with "set statistic io on", and see if one of the databases is getting mostly logical reads and the other is getting mostly physical reads.
- 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
December 3, 2009 at 12:43 pm
So you've got identical execution plans on identical databases on the same server, but one is running slow and the other is not?
Contention is the first thing that comes to mind, are you possibly seeing resources getting allocated to one or the other of the systems, something along those lines. In general though, I wouldn't expect to see radically different performance with identical execution plans unless contention was the issue.
"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
December 3, 2009 at 1:06 pm
Everyone thanks for your help. I ran the SP with "set statistics io on" and saw it doing alot more work then it should have. Turns out I was missing some indexes I needed. This fixed that problem and all kinds of other performance issues I was seeing. HOWEVER, the indexes were the same on both databases (as one was a backup of the other) so this doesn't explain the original question. I guess that will remain a mystery.
Thanks again! 🙂
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply