2005 vs 2000 views performance q

We are attempting to move from sq12k to 2k5 for one of our products, and
have a few replay trace files that we are using to determine performance
diffs between the two.
What we do is baseline backup a 2k db, then start a replay trace, and then
have users run thru their test cases. So for our test, on either 2k or 2k5,
we restore the pre-test database, run a checkdb and rebuild all indexes on
every table in the db.
We have one statement "duration" that sticks out like a sore thumb
performance wise between 2k and 2k5, 2k5 being much slower, here is the
scenario, in sql 2005:
1) The statement hits a view, that sits on top of a table with *a lot* of
data
2) The select statement does a top, and a "like" on 3 varchar columns in the
table, none of which are indexed
3) The first time this query is run, via the sql replay, the statment takes
over a minute to complete.
4) Each subsequent execution takes about 350 microseconds.
Now, in sql2k, we run the exact same test, restore the db, run checkdb,
recreate all indexes. All executions of the above statement take around 14
milliseconds, first exec, and all after that.
Has anyone seen/experienced this?
Thanks in advance!
DerrickDid you update statistics?
Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx
On Aug 23, 9:55 am, "Derrick" <derrick1...@.excite.com> wrote:
> We are attempting to move from sq12k to 2k5 for one of our products, and
> have a few replay trace files that we are using to determine performance
> diffs between the two.
> What we do is baseline backup a 2k db, then start a replay trace, and then
> have users run thru their test cases. So for our test, on either 2k or 2k5,
> we restore the pre-test database, run a checkdb and rebuild all indexes on
> every table in the db.
> We have one statement "duration" that sticks out like a sore thumb
> performance wise between 2k and 2k5, 2k5 being much slower, here is the
> scenario, in sql 2005:
> 1) The statement hits a view, that sits on top of a table with *a lot* of
> data
> 2) The select statement does a top, and a "like" on 3 varchar columns in the
> table, none of which are indexed
> 3) The first time this query is run, via the sql replay, the statment takes
> over a minute to complete.
> 4) Each subsequent execution takes about 350 microseconds.
> Now, in sql2k, we run the exact same test, restore the db, run checkdb,
> recreate all indexes. All executions of the above statement take around 14
> milliseconds, first exec, and all after that.
> Has anyone seen/experienced this?
> Thanks in advance!
> Derrick|||I have a backup of the pre-test mdf/ldf, do an attach, and then execute
this:
use <dbname just attached>
dbcc checkdb
exec sp_Msforeachtable "DBCC DBREINDEX ('?')"
I was going on the theory that this would also update statistics
automatically, is that not the case?
Thanks for input,
Derrick
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1187877687.713708.281740@.j4g2000prf.googlegroups.com...
> Did you update statistics?
>
> Denis The SQL Menace
> http://sqlservercode.blogspot.com
> http://sqlblog.com/blogs/denis_gobo/default.aspx
>
> On Aug 23, 9:55 am, "Derrick" <derrick1...@.excite.com> wrote:
>> We are attempting to move from sq12k to 2k5 for one of our products, and
>> have a few replay trace files that we are using to determine performance
>> diffs between the two.
>> What we do is baseline backup a 2k db, then start a replay trace, and
>> then
>> have users run thru their test cases. So for our test, on either 2k or
>> 2k5,
>> we restore the pre-test database, run a checkdb and rebuild all indexes
>> on
>> every table in the db.
>> We have one statement "duration" that sticks out like a sore thumb
>> performance wise between 2k and 2k5, 2k5 being much slower, here is the
>> scenario, in sql 2005:
>> 1) The statement hits a view, that sits on top of a table with *a lot* of
>> data
>> 2) The select statement does a top, and a "like" on 3 varchar columns in
>> the
>> table, none of which are indexed
>> 3) The first time this query is run, via the sql replay, the statment
>> takes
>> over a minute to complete.
>> 4) Each subsequent execution takes about 350 microseconds.
>> Now, in sql2k, we run the exact same test, restore the db, run checkdb,
>> recreate all indexes. All executions of the above statement take around
>> 14
>> milliseconds, first exec, and all after that.
>> Has anyone seen/experienced this?
>> Thanks in advance!
>> Derrick
>|||> I was going on the theory that this would also update statistics automatically, is that not the
> case?
It will update statistics of all you indexes. But not statistics only.
I'd start with comparing the execution plans between the queries to see if they differ and take it
from there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Derrick" <derrick1298@.excite.com> wrote in message news:%23DjW$iZ5HHA.4584@.TK2MSFTNGP03.phx.gbl...
>I have a backup of the pre-test mdf/ldf, do an attach, and then execute this:
> use <dbname just attached>
> dbcc checkdb
> exec sp_Msforeachtable "DBCC DBREINDEX ('?')"
> I was going on the theory that this would also update statistics automatically, is that not the
> case?
> Thanks for input,
> Derrick
>
> "SQL Menace" <denis.gobo@.gmail.com> wrote in message
> news:1187877687.713708.281740@.j4g2000prf.googlegroups.com...
>> Did you update statistics?
>>
>> Denis The SQL Menace
>> http://sqlservercode.blogspot.com
>> http://sqlblog.com/blogs/denis_gobo/default.aspx
>>
>> On Aug 23, 9:55 am, "Derrick" <derrick1...@.excite.com> wrote:
>> We are attempting to move from sq12k to 2k5 for one of our products, and
>> have a few replay trace files that we are using to determine performance
>> diffs between the two.
>> What we do is baseline backup a 2k db, then start a replay trace, and then
>> have users run thru their test cases. So for our test, on either 2k or 2k5,
>> we restore the pre-test database, run a checkdb and rebuild all indexes on
>> every table in the db.
>> We have one statement "duration" that sticks out like a sore thumb
>> performance wise between 2k and 2k5, 2k5 being much slower, here is the
>> scenario, in sql 2005:
>> 1) The statement hits a view, that sits on top of a table with *a lot* of
>> data
>> 2) The select statement does a top, and a "like" on 3 varchar columns in the
>> table, none of which are indexed
>> 3) The first time this query is run, via the sql replay, the statment takes
>> over a minute to complete.
>> 4) Each subsequent execution takes about 350 microseconds.
>> Now, in sql2k, we run the exact same test, restore the db, run checkdb,
>> recreate all indexes. All executions of the above statement take around 14
>> milliseconds, first exec, and all after that.
>> Has anyone seen/experienced this?
>> Thanks in advance!
>> Derrick
>>
>