performanc degrade

Last Post 21 Apr 2008 11:59 PM by SQLUSA. 18 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
rpitiyar
New Member
New Member

--
15 Apr 2008 11:19 PM
Hi,

I migrated our prod datbase for testing into sql2005 enterprise server from sql2000 std version by backup and restore method. updated statistics using sp_updatestats and tried few queries. one of them which i checked has a very poor performace. ie an increse from less than one sec to 39 sec. both seriver have same hardware and file structure. I checked the query plans and they are different too. drilling down to the root i found sql2005 does a table scan ( clustered index scan) in one part which consumes more resources as opposed to index seek and bookmark look up in the same part in sql2000.
so I created a covering index in sql2005, it started using the index and query time dropped to 12 secs from 39 secs. Also logical reads from 2188608 to 113542.

I give a basic query which give very different perf results in 2000 and 2005

Indexes : clustered index on datetime column and non-clutered index on status column

query :-select top 5 [id] from Movement where Status = 'C' order by [datetime] desc

in sql200 : it does a index seek on status ( which has only five distinct values) and then bookmark look up on clutered index(io cost .001688)

in 2005 : in directly does a clustered index scan . this has a higer ( io cost 3.1992)

Can you explain why sql2005's abnormal behaviour? Backup/restore are very much faster in 2005

Regards,

Rohan
SQLUSA
New Member
New Member

--
16 Apr 2008 05:01 AM
The true explanation is that they were generated by 2 different generations of Microsoft software engineers.

Make sure you dbreindex the entire database.

Assign FILLFACTOR to dynamic tables.

Retest.

Some sproc or index changes maybe necessary to tune SQL Server 2005 performance.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
rpitiyar
New Member
New Member

--
16 Apr 2008 05:06 PM
My tests covered everything you all mentioned, reindexing the whole database, also running stats update with full scan option . nothing changed. file structures are identical. using the same san, tempdb, log on seperate drives and data on two drives ( two file groups). The table I am concern has very low almost nothing, external/internal fragmentation too.

it is obvious that versions are created by two sets of people but unfortunately response from SQLUSA mentoned about fillfactor. IF you read the first email, fillfactor has absolutely no effect on this matter under these conditions.

To me it looks like an issue either ith the optimser or some configuration in sql server other than default. I also check with infrastructure people, both servers are even using the same controllers.

thanks

Regards,


SQLUSA
New Member
New Member

--
17 Apr 2008 12:18 AM
Save yourself some time.

It is a known fact that not all queries/sprocs compile the same way on SQL Server 2000 & SQL Server 2005.

Workaround: change the query/sproc for better performance. Sometime a trivial change, such as adding a #temptable step in a sproc will get the sproc on the right path to performance.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
SQLUSA
New Member
New Member

--
17 Apr 2008 10:05 AM
Sorry mates....

My FUNNY comments about new generation of MS programmers was taken out of context.

The point is, SQL Server 2005 database engine is NOT IDENTICAL to SQL Server 2000 database engine.

Your problem is well known.

Simplest work around is trivial changes to the query. If sproc, just put in a @tempvariable or #temptable intermediary step.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
rpitiyar
New Member
New Member

--
17 Apr 2008 06:30 PM
Hi,

If it meant to be joke that's fine. If my problem is well know are ther any sites or docs avialable on public domain. because this is very useful for those who do upgrades. when mentioned about use of temp tables or variable this complete query is using. strictly speaking you should avoid such as much as possible due to compilation and other porblem at run time.

but the point is , if this is a well known issue, then upgrade is not simple as prevous upgrades. As sql optimser is cost based . and sql 2005 expects to have better performance i cannot see why the new engine behaves very differenlty on this simple query.

Somehw i could get nearly close performance with the covering index but it still takes about 800mil sec more than sql2000.

can someone direct me or suggest any ?

Thanks for all involved any kind of response

SQLUSA
New Member
New Member

--
17 Apr 2008 07:53 PM
Covering index generally a good idea.

If it works for you go for it.


The point I was making is different though: with the rearrangement of the logic, the database engine is guided to the "correct" plan.

I hope this is only with one of your sprocs, not all of them.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
rpitiyar
New Member
New Member

--
18 Apr 2008 12:09 AM
But still performance is less with covering index
with 2000 it is 650milsecs but with 1700 milsecs. I really need to get into this problem. otherwise I'll have a great difficulty in convincing my management in pushing the upgrades.
SQLUSA
New Member
New Member

--
18 Apr 2008 01:40 AM
How many sprocs do you have?

How many have performance issues?

How many can be improved by using SQL 2005 features such as non-key column in index?

Have you tested it on SQL Server 2008?

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, Performance Tuning: http://www.sqlusa.com/order2005highperformance
SQLUSA
New Member
New Member

--
18 Apr 2008 04:18 AM
Also a KB article (may not be related ....)

http://support.microsoft.com/kb/942659/


SQLUSA
New Member
New Member

--
18 Apr 2008 07:47 AM
Can you post the query?

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
rpitiyar
New Member
New Member

--
19 Apr 2008 05:26 AM
HOw many sps or other sql queries is not the problem. Problem is why sql2005 selects a high cost plan for execution. the only thing i didn't do is plan forcing. but still question remains same.
rpitiyar
New Member
New Member

--
19 Apr 2008 05:29 AM
Disk i/o cost is very excessive ie .000765 to 3.12...
in missing index view it suggests the same covering index i created but with that performance is still very poor as indicated ( more than 100%)
SQLUSA
New Member
New Member

--
19 Apr 2008 10:26 AM
Can you post the query?
rpitiyar
New Member
New Member

--
21 Apr 2008 08:51 PM
Appreciated very much your effort . I am still doing many tests and also followed your link and found many bugs which could be relevant for us. I also applied hot fix 4 and 6 on difrent systems and tested it didn't not help too. but I created the database is sql2005 which has hot fix 4 (on sp2) which changed the query plan and produced similar results as with covering index.

My problem is to get atleast close improvements to sql2000 environment without doing any modifications. I have already pointed some probs in other queries and also asked to improve this one too.

For you i am posting the query. The most costly part is the highligted

select @FilterExists = 1

-- determine start & end dates according to offsets
declare @StartDate datetime
declare @endDate datetime
declare @datenow datetime

set @datenow = getdate()

set @StartDate = DateAdd(day, IsNull(@StartDateOffset, 0), @datenow)
set @StartDate = dbo.fn_com_GetDateBeginEnd(@StartDate, 'b')

set @endDate = DateAdd(day, IsNull(@endDateOffset, 7), @datenow)
set @endDate = dbo.fn_com_GetDateBeginEnd(@endDate, 'e')


declare @BPColour char(1)
declare @WPColour char(1)
declare @DGLodgement char(1)
declare @DGExemption char(1)
declare @DGEditDeclaration char(1)
declare @BMColour char(1)
declare @PrevMvtId int
declare @PrevMvtType char(1)
declare @PrevMvtExportExplosiveOverlimit char(1)
declare @PrevMvtExportOxidisingOverlimit char(1)
declare @PrevMvtHazchem char(5)
declare @PrevMvtNotGasFree char(1)
declare @MTTHAZCHEM char(5)
declare @ElapsedWPExists char(1)
declare @ElapsedBPExists char(1)

declare @MovementsWithAlerts table (
MvtID int primary key not null
, BPColour char(1)
, WPColour char(1)
, DGLodgement char(1)
, DGExemption char(1)
, DGEditDeclaration char(1)
, BMColour char(1)
, PrevMvtId int
, PrevMvtType char(1)
, PrevMvtExportExplosiveOverlimit char(1)
, PrevMvtExportOxidisingOverlimit char(1)
, PrevMvtHazchem char(5)
, PrevMvtNotGasFree char(1)
, HAZCHEM char(5)
, ElapsedWPExists char(1)
, ElapsedBPExists char(1)
)


declare @TempMvts table (rownum int IDENTITY (1, 1) Primary key NOT NULL , MvtID int)

SET NOCOUNT ON

--insert into @TempMvts (MvtID)
select distinct
mvt.[ID]
from
Movement mvt
inner join Visit vst
on mvt.VisitID = vst.VisitID
inner join Vessel vsl
on vst.IMO = vsl.IMO
inner join Berth bth
on mvt.DestinationBerthCode = bth.Code
where
(@FilterExists = 1
and (mvt.Status <> 'X'
and (mvt.[DateTime] between @StartDate and @EndDate)
and (isnull(vsl.GT,0) between @GTfrom and @GTTo)
and (@DG = 'A' or (@DG <> 'A' and (vst.DGPresent = @DG)))
and (@BerthCode = '00000' or (@BerthCode <> '00000' and (mvt.OriginBerthCode = @BerthCode or mvt.DestinationBerthCode = @BerthCode)))
and (@IMO = 0 or (@IMO <> 0 and (vst.IMO = @IMO)))
and (@AgentCode = '00000' or (@AgentCode <> '00000' and (mvt.AgentCode = @AgentCode)))
and (@VesselType = '00000' or (@VesselType <> '00000' and (vsl.type = @VesselType)))
and (@VesselFlag = '00000' or (@VesselFlag <> '00000' and (vsl.flag = @VesselFlag)))
and (@MovementType = '*' or (@MovementType <> '*' and (mvt.Type = @MovementType)))
and (@MovementStatus = '*' or (@MovementStatus <> '*' and (mvt.Status in (@MovementStatus))))
and (@Port is null or (not @Port is null and
dbo.fn_com_MovementPortCode(mvt.ID) = @Port
))
and (@Stevedore = '000000'
SQLUSA
New Member
New Member

--
21 Apr 2008 11:59 PM
Suggestions:

1. dbo.fn_com_MovementPortCode(mvt.ID) make this into an added column
2. IsNull((select b.TerminalCode from Berth b where b.Code = mvt.OriginBerthCode), '')
change to -->
COALESCE((select b.TerminalCode from Berth b where b.Code = mvt.OriginBerthCode), '')
3. (select top 5 [id] from Movement where Status = 'C' order by [datetime] desc)
make it into a CTE

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
rpitiyar
New Member
New Member

--
22 Apr 2008 08:23 PM
you're right. using fns in where clause is not a good pratice too.
what i originally did was to create an covering index on (status,datetime, id) so naturally it did give a performance improvement. but the point is current in sql2000, it has an index on status which has five distinct values (not the number of rows) . the sql 2000 uses this index and does the bookmark look up to get the results. but sql2005 uses table scan which produce very high i/o cost and then filter to get top 5 five rows.
my clustered index is on datetime which is more appropriate and that's why sql2005 directly jumps into the clx index scan.

What ever happens going through the bugs lists i see that there are many bugs related to performance in 2005. Another test i did was i recreated the database in sql2005 by creating and ppopulating the tables. this database did same results(without covering index) as with covering index. I need to test more on this line and confirm to you all. now i am testing with sql2005 sp2 hotfix 4 and 6

Thx for you help and coments
SQLUSA
New Member
New Member

--
22 Apr 2008 11:20 PM
Andy,

This is the KB solution for this bug.
****************************************************************************
>>2. IsNull((select b.TerminalCode from Berth b where b.Code = mvt.OriginBerthCode), '')
change to -->
COALESCE((select b.TerminalCode from Berth b where b.Code = mvt.OriginBerthCode), '')

****************************************************************************

Don't ask me to dig up the KB, just try it. Yes they are supposed to be equivalent, but they are not.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
SQLUSA
New Member
New Member

--
25 Apr 2008 08:15 AM
Have you tried this:

COALESCE((select b.TerminalCode from Berth b where b.Code = mvt.OriginBerthCode), '')

This is supposedly the solution to your issue.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/order2005ssis
You are not authorized to post a reply.

Acceptable Use Policy