Database Version: Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 3)
I have a table with about 7000 rows enabled for full text search on a couple text fields in the table. If I run the following Query:
dbcc freeproccache
dbcc dropcleanbuffers
set statistics time on
select * from Codes
where CONTAINS([level1AltDesc], '"Unspec*"')
set statistics time off
I get correct consistent results every time. However the elapsed times and cpu times can vary quite wildly. Executing multiple times I get the following in Messages:
DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(861 row(s) affected)
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 251 ms.
Running it a few more times I get:
DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(861 row(s) affected)
SQL Server Execution Times: CPU time = 15 ms, elapsed time = 869 ms.
I've tried running with IO STATISTICS but they never change. I've tried using the SQL Profiler but just see the same differences in the elapsed times. I've only added the fairly standard events, so I just might be missing the one that would explain this.
I have seen the same behavior using a stored procedure instead of as a standalone query. Running this as a stored procedure and then querying sys.dm_exec_query_stats I can see a total_elapsed_time that is significantly higher than the total_worker_time.
Since this is on a dedicated test machine with me as the only user and no load from any other source and no noticeable load on the computer (avg 98-99% cpu idle) I'm a little worried about what might happen on a production machine when the system goes under heavier load.
Any ideas on what might be causing these wild swings in execution times and what I can do about them?
