April 30, 2008 at 6:11 am
I just created a little script to log response times during the day of several SQL 2000 & SQL 2005 instances.
I was surprised to see that the select response was always 4 till 8 times faster on a sql2000 system.
Investigating the qry execution plan told me that sql2000 treads it differently than sql2005
In sql2000 :
Clustered Index Scan (900 records), Filter (100 records) , compute Scalar (100), Select (100)
In sql2005:
Clustered Index Scan (100 records), Compute Scalar (100 records) , compute Scalar (100), Select (100)
The sql2000 system has less mry available than the sql2005 system.
CPU's are the same, disks also the same configuration.
Can someone tell me why this script and especially the select, is slower on a 2005 instance?
The script is very simple:
set nocount on
declare @start as datetime
declare @insert as int
declare @select as int
declare @update as int
declare @delete as int
declare @total as int
declare @i as int
CREATE TABLE [#loadtest](
[id] [int] IDENTITY(1,1) NOT NULL,
[Type] [char](4000) NULL,
[Value1] [int] NULL,
[Value2] [float] NULL,
[remark] [varchar](1000) NULL,
[Product] AS ([Value1]*[Value2]),
CONSTRAINT [PK_LoadTest] PRIMARY KEY CLUSTERED ([id] ASC)
)
set @i = 0
select @start = getdate() -- set start time insert
while @i < 1000
begin
insert into #loadtest (Type, Value1, Value2, remark) values(cast(@i % 10 as char(1)) + 'LongText',@i,(@i % 10) * pi(),'SQL performance test')
set @i = @i + 1
end
select @insert = datediff(ms,@start,getdate()) -- set insert time
select @start = getdate() -- set start time select
select * from #loadtest where Type = '6LongText'
select @select = datediff(ms,@start,getdate()) -- set select time
select @start = getdate() -- set start time update
update #loadtest set remark = 'Update SQL performance test' where Type = '3LongText'
select @update = datediff(ms,@start,getdate()) -- set update time
select @start = getdate() -- set start time delete
delete from #loadtest where Type = '1LongText'
select @delete = datediff(ms,@start,getdate()) -- set deletet time
select @total = @select + @insert + @update + @delete
select @total as Total,@insert as [insert],@select as [select],@update as [update],@delete as [delete]
drop table #loadtest
April 30, 2008 at 6:33 am
Since there are no indexes involved, hardware is the most likely suspect and then parallelism.
Neither server has much for options in the optimization area because they have to do a table scan for all of the operations.
So, it is likely that the SQL 2005 server has slower times at the IO level or some other resource restriction.
You can easily eliminate parallelism is the culprit by setting the max DOP to 1. However, the number of records is low, so you are probably not reaching the threshold for parallelism.
April 30, 2008 at 6:42 am
it is on the same server, only with less memory on for the sql 2000 and for both instances max degree of parallelisme = 1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply