kellyascent (5/5/2013)
OK so today I did a clean install of SQL still on the same hardware with the same settings, removed the AWE and set the Max worker threads back to 0.I created a new database with all the defaults and created a new table which has a primary key (incremented), a nvarchar(10) field, and two datetime fields - StartDate and EndDate.
I added one row to the table and created a new query (select Name from testable where ID = 1). This query ran fine, but if I run it 200 times in a row so
select Name from testable where ID = 1
GO
select Name from testable where ID = 1
GO
Etc …
It takes over 6 seconds to execute, whereas with the same table/data/query on SQL2000 it runs in less than 0 seconds. I guess what I need to know is if this is normal behavior for SQL2008 or do I really need to dig deeper?
There is no need to update stats as this is a brand new query, it only has a primary key so all that the execution plans shows is 100% time on the primary key.
I realize that you will never do this in a normal situation but this is just a sample. In the "real world" when it is thrown 200 different queries the same thing happens.
Maybe if someone could replicate the database/table and query and see how long it takes to run, then I can have something to compare it to?
So, you've already confirmed the same query on 2000 and 2008 are both performing a similar index scan on the primary key and number of records, but now you're curious about the significant difference in runtime duration?
Now, re-run the queries again on both servers, but first execute the following, which will insure each starts with a clean page cache and plan cache. Also, for comparison it will return information about time spent compiling the execution plan and number of I/O pages read.
-- Removes all clean buffers from the buffer pool. Use this to test queries with a cold buffer cache without shutting down and restarting the server.
DBCC DROPCLEANBUFFERS;
-- Removes all elements from the plan cache.
DBCC FREEPROCCACHE;
-- Displays the number of milliseconds required to parse, compile, and execute each statement.
SET STATISTICS TIME ON;
-- Display information regarding the amount of disk activity generated by Transact-SQL statements.
SET STATISTICS IO ON;
Also, run sp_spaceused on the table in both 2000 and 2008. Compare the number of pages reserved. It's possible that the table on 2008 may be heavily page fragmented for some reason, perhaps as a result of the migration process. If so, that would result in the query performing more I/O and thus more work.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho