May 15, 2007 at 5:10 pm
I'm running a query (see below) on my development server and its taking around 45 seconds. The production server, which is very similar but with fewer databases, runs the query in less than 1 second. Both servers have been running on VMWare for almost 1 year with no problems. However last week I applied SP 2 to the development server. The production server is still running SQL Server 2005 Standard SP 1. Other than that, both servers are identical and running Windows 2003 Server Standard SP 1. I'm not seeing this discrepancy with other queries running against user databases.
use MyDatabase
GO
select db_name(database_id) as 'Database', o.name as 'Table',
s.index_id, index_type_desc, alloc_unit_type_desc, index_level, i.name as 'Index Name',
avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages,
page_count, avg_page_space_used_in_percent, record_count,
ghost_record_count, min_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count,
schema_id, create_date, modify_date from sys.dm_db_index_physical_stats (null, null, null, null, 'DETAILED') s
join sys.objects o on s.object_id = o.object_id
join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id
where db_name(database_id) = 'MyDatabase'
order by avg_fragmentation_in_percent desc
--order by avg_fragment_size_in_pages desc
--order by page_count desc
--order by record_count desc
--order by avg_record_size_in_bytes desc
May 16, 2007 at 2:15 pm
Do Query - Display Estimated Execution Plan and Query - Include Actual Execution Plan
Do it on both servers when running your query in Management Studion. Check if something takes more costs.
If I run your query, it runs 21 seconds and shows in the Actual Execution Plan that 98% costs are Remote Scan. If I put a mouse on the Remote Scan picture, it says " Scan rows in a table stored in a database or file other then the current database server" "Remote Object - IndexAnalysis"
This is on my local SQL Server 2005 Standard Edition RTM
Also when I ran this query the second time, it took only 4 seconds, not 21 seconds. Probably execution plans were cached. When I connected to the remote SQL Server 2005 Enterprise Edition SP2 on a very good Hardware, it took 4 seconds the first time and less then 1 second the second time.
It is possible that when you installed SP2 the development server was rebooted and the first execution is slower because it does not have execution plans cached yet.
Regards,Yelena Varsha
May 16, 2007 at 2:46 pm
Thanks. Actually, I ran the query six times with elapsed times from 38 to 48 seconds and an average of 45 seconds.
I'm told that this query scans every page in every database. The development server has 18 user databases while the production server has only one. When I cleaned up the development server and reduced the number of user databases from 18 to 9, elapsed time for the query became 6 seconds for the first run and 1 second for all subsequent runs. Apparently the query does not scale!
Specifying the database id and/or specifying 'Limited' mode will also speed up the query:
select ... from sys.dm_db_index_physical_stats (DB_ID('mydatabase'), null, null, null, 'LIMITED')
(DETAILED scans all pages, while LIMITED and SAMPLED don't.)
So, I'm going to assume there's nothing wrong with my development server (which was my worry--I don't care how long the query takes to run).
May 18, 2007 at 10:57 am
Nikole,
I am glad you figured it out.
Just in case for the future: you may want to ask you system engineer to check RAID array in Computer Management (Device Manager and Disk management) , Management Application of your particular brand of RAID controler or whatever is used on your servers. Four of five times over the period of 6 years we did have failed disks in RAIDs on different servers and because it was RAID, the rest of the disks were performing, but very slow.
Regards,Yelena Varsha
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply