March 7, 2011 at 7:19 am
We have a view to query sys.dm_db_index_usage_stats, but in profiler I can see that the CPU and duration are both at around 1500, so it takes 1.5 seconds to return the result. This is only on one server where this table has 13,000 rows, on another server with 3,000 its pretty much instant.
Our view is 'like' this
SELECT MAX(last_user_update) AS last_user_update
FROM sys.dm_db_index_usage_stats
WHERE (database_id = DB_ID()) AND (object_id IN (OBJECT_ID('customer'), OBJECT_ID('product')))
The execution plan says 46% table valued function LOGINDEXSTATS and 54% Filter
This is the same for both the slow and fast server
I have tried changing the query to be exact = conditions but this gives exactly the same execution plan
The reason for the query is so that we know that last time the table was updated.
For now we have created update triggers to populate another table and this works fine, but if this dmv does not scale well for being queried then that is a problem.
The 'slow' server is: SQL2005 32-bit 16 processors and 24gb ram awe enabled
The 'fast' server is SQL2006 32-bit 8 processord 13GB ram awe enabled
Has anyone else experienced this slow performance?
Thanks in advance.
March 7, 2011 at 2:45 pm
No, I haven't seen issues with that DMV before. When it's running long, take a look at sys.dm_exec_requests or sys.dm_os_waiting_tasks to see what is causing it to slow down.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply