September 16, 2010 at 7:35 am
what would be the best way to comapre performance between 2 identical (compeltely)databases, one of them is runing super fast and the other is dea slow.
I have read gate , does it help me compare indexes and statistics?
September 16, 2010 at 7:55 am
You need to change the way you're looking at this. Databases don't perform slow - queries do.
This could be due to a multitude of reasons..database contention, memory/cpu pressure, IO contention, old/missing statistics, fragmented/missing/disabled indexes, etc.
You need to run the same query on each DB and compare execution plans to see where the difference lies.
September 21, 2010 at 7:36 am
I have seen huge performance difference between 2 identical databases and the weired thing is bigger db perfoprming betterr than smaller where the expected was in reverse.
the query i am using has 2 huge temp tables involved and the differece i ahve observed is faster query is using parallelism repartition streams when processing temp tables but the slower query is not, how do i change the slower one to perform better.
fyi.. both databases are on the same server
September 21, 2010 at 8:01 am
Are both databases running in the same instance or are there 2 differnent instances? If they are 2 different instances then keep in mind they are both competing for memory and CPU. In this case look at sp_configure on both instances. In particular are the following set up the same:
cost threshold for parallelism
max degree of parallelism
max server memory (MB)
If they are the same instance but separate databases try running dbcc dropcleanbuffers and DBCC FreeProcCache prior to testing. Read up on these to ensure it makes sense in your case. I assume that the test is otherwise identical - same proc and same input.
Francis
September 21, 2010 at 9:10 am
Its on the same instance but different databses but the databases are completely identical structured.I am wondering why one be faster than other.
I have also ran dbcc dropcleanbuffers and DBCC FreeProcCache but still the execution plan not changed.
September 21, 2010 at 9:21 am
I have also observed that the faster datavase is generating bitmap create filter before parellelism ,how do i make sure even slower db also generate bitmap and parallelism?
September 21, 2010 at 9:43 am
if the databases are truly identical in terms of rows in each table and the defined indexes make sure that all statistics are up to date. SQL may generate differnt plans if the statistics for a particular table do not reflect the current data. Use sp_updatestats to update all stats. ALso if the code you are executing to compare speed is a stored proc try to recompile the stored proc on both databases (example EXEC usp_MyProcedure WITH RECOMPILE) The check the plans being generated
Francis
September 21, 2010 at 9:58 am
i have updated the stats already and here is the code its not a stored proc.
Create Table #dtrange (
jobstart datetime,jobend datetime,paystart datetime,payend datetime)
go
drop table #empdts
select distinct empno,
case when efdt>=jobstart then efdt else jobstart end as efdt,
case when exdt<=jobend then exdt else jobend end as exdt
into #empdts
from dbo.empdate, #dtrange
where exdt >= jobstart and efdt <= jobend
go
--1863459
drop table #dev
select distinct a.empno
into #dev
from dbo.vwecode a inner join #empdts b
on a.empno = b.empno, #dtrange c
where a.sdate between b.efdt and b.exdt
and a.sdate between c.paystart and c.payend
and (a.ecode like '5634%' or a.ecode like '845%' or a.ecode like '9674%')
go
--1863458
-------------------------------------------------------------------------------------
drop table #dev2
select a.empno,sdate,enddate,place
ecode1,ecode2,ecode3,ecode4,ecode5
into #dev2
from employee a inner join #empdts b
on a.empno=b.empno
inner join #dev c
on a.empno = c.empno
where a.sdate between b.efdt and b.exdt
go
only last step of the query is taking very long time
September 21, 2010 at 10:19 am
Its interesting that its all tempdb work being done. I assume your tempdb is sufficeintly large. See http://msdn.microsoft.com/en-us/library/bb522472.aspx fro some help with BITMAP. You may want to try disabling BITMAP by using the JOIN hint MERGE. When you test you may also want to try using the QUERY HINT MAXDOP as well try setting it to MAXDOP(0) and MAXDOP(1) to see if the results differ.
Francis
September 21, 2010 at 10:36 am
i dont think its working for me, is there a chance to improve if we can bring in same statistics from the better performing DB to slower db? not sure if that can be done.
September 21, 2010 at 11:03 am
The statistics are based on the data so you can't "bring them from one database to another". You did say the tables were identical. To me this means not only the same definition but the same data (both in terms of content and number of rows) If the stats are also updated (check that the dates are the same) then the plan should be the same. I assume that one database was restored from the other. Are you saying the the join i=hint and the query hint did not provide any clues at all as to what may be happening?
Francis
September 21, 2010 at 11:13 am
no everything is identical execept the amount of data and data also is different. good database is 900gb and bad db is just 300gb, running the same query on both db's but the difference is almost 10hrs which really weired.
September 21, 2010 at 11:27 am
it is odd that your bad db is 1/3 the size of the good one and yet has worse performance. I keep thinkg SQL is generating a poor plan based on faulty stats. Look at http://msdn.microsoft.com/en-us/library/ms190397.aspx to see if any hints for fixing this may help you.
Francis
September 21, 2010 at 12:45 pm
Ok here is the important observation, I am runnig the above query from a view (which has union of 2 big tables ) which why it is not using paralellism but when i run query directly from both the tables it is super fast. so how would i make my view work just like the tables?
September 21, 2010 at 1:03 pm
Try using an inline function to paramaterize the view (pass in efdt and exdt for the 3rd select) For a sample search for 'predicate pushing' on http://jahaines.blogspot.com/2010/03/performance-tuning-101-what-you-will_17.html where a simlar issue is discussed. Your other option is to not use a view. There are several discussions on more than one site that discuss performance issues with views in SQL 2005 (improved in 2008)
Francis
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply