performance difference between sql 00 and sql 05

I'm testing out our applications against our new SQL 05 instance and I'm
noticing a performance difference between the time SQL 2000 returns my data
back and the time SQL 05 returns my data back. SQL 2000 is actually
returning the data back 2 seconds faster (I know its only 2 seconds but to
my users thats like a eternity). So being new to this DBA role, (coming from
a developer background) what can I start checking to get the performance on
SQL 05 to match or be better then the SQL 2000 result time?
--
MikeRun the query with Execution Plan turned on. I generally look for:
index scans
table spools
table scans
missing statistics
very thick arrows between nodes
hash/merge/nested loop joins - compare performance with explicit hints
the nodes with higher %
I also tend to use:
set statistics time on
set statistics io on
set statistics profile on
You can focus on the parts of the output that look worst (e.g. highest
physical reads or longest CPU time).
Also see http://support.microsoft.com/?kbid=243589
Compare this execution plan with the execution plan on 2000 (I assume that
system is still available).
If you upgraded from 2000 -> 2005, then you should consider the following
actions:
(a) reorganize / rebuild indexes
(b) re-create statistics
(c) make sure compatibility mode is 90 not 80
(d) clear proc cache and recompile (I hope you are using stored procedures!)
(e) if you are using ad hoc queries (!) you may consider changing the forced
parameterization setting as a test
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
> I'm testing out our applications against our new SQL 05 instance and I'm
> noticing a performance difference between the time SQL 2000 returns my
> data back and the time SQL 05 returns my data back. SQL 2000 is actually
> returning the data back 2 seconds faster (I know its only 2 seconds but to
> my users thats like a eternity). So being new to this DBA role, (coming
> from a developer background) what can I start checking to get the
> performance on SQL 05 to match or be better then the SQL 2000 result time?|||Have you re-indexed the tables and updated the stats on the 2005 db?
Thanks,
Karthik
"Mike" wrote:
> I'm testing out our applications against our new SQL 05 instance and I'm
> noticing a performance difference between the time SQL 2000 returns my data
> back and the time SQL 05 returns my data back. SQL 2000 is actually
> returning the data back 2 seconds faster (I know its only 2 seconds but to
> my users thats like a eternity). So being new to this DBA role, (coming from
> a developer background) what can I start checking to get the performance on
> SQL 05 to match or be better then the SQL 2000 result time?
>
> --
> Mike
>
>|||no
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:389A7D51-F9A6-4E1F-929E-3E64D258F5A2@.microsoft.com...
> Have you re-indexed the tables and updated the stats on the 2005 db?
> Thanks,
> Karthik
> "Mike" wrote:
>> I'm testing out our applications against our new SQL 05 instance and I'm
>> noticing a performance difference between the time SQL 2000 returns my
>> data
>> back and the time SQL 05 returns my data back. SQL 2000 is actually
>> returning the data back 2 seconds faster (I know its only 2 seconds but
>> to
>> my users thats like a eternity). So being new to this DBA role, (coming
>> from
>> a developer background) what can I start checking to get the performance
>> on
>> SQL 05 to match or be better then the SQL 2000 result time?
>>
>> --
>> Mike
>>
>>|||"Mike" <Mike@.community.nospam.com> wrote in message
news:OlBO0%23dsHHA.1204@.TK2MSFTNGP03.phx.gbl...
> no
> "Karthik" <Karthik@.discussions.microsoft.com> wrote in message
> news:389A7D51-F9A6-4E1F-929E-3E64D258F5A2@.microsoft.com...
>> Have you re-indexed the tables and updated the stats on the 2005 db?
Updating statistics is one of the post-upgrade install tasks listed in the
Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/b5f6549a-1e6f-486c-aa58-f12e3eb9fefa.htm).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <Mike@.community.nospam.com> wrote in message
news:OlBO0%23dsHHA.1204@.TK2MSFTNGP03.phx.gbl...
> no
> "Karthik" <Karthik@.discussions.microsoft.com> wrote in message
> news:389A7D51-F9A6-4E1F-929E-3E64D258F5A2@.microsoft.com...
>> Have you re-indexed the tables and updated the stats on the 2005 db?
>> Thanks,
>> Karthik
>> "Mike" wrote:
>> I'm testing out our applications against our new SQL 05 instance and I'm
>> noticing a performance difference between the time SQL 2000 returns my
>> data
>> back and the time SQL 05 returns my data back. SQL 2000 is actually
>> returning the data back 2 seconds faster (I know its only 2 seconds but
>> to
>> my users thats like a eternity). So being new to this DBA role, (coming
>> from
>> a developer background) what can I start checking to get the performance
>> on
>> SQL 05 to match or be better then the SQL 2000 result time?
>>
>> --
>> Mike
>>
>>
>sql