February 18, 2009 at 12:54 am
Execute same t-sql query twice with options set statistics profile on, set statistics time on, set statistics io on.
Plans, io statistics absolutely identical
Parse and compile time may be insignificantly different (or plan may be touch from cache)
Elapsed time may be from 1 to 1,5 Cpu Time
CPU Time may by differ 10 times. For example, from 1000 to 10000.
Exist dependence: with grow system loading grow up CPU Time for query
Please, explain me: CPU Time – this is “clean” time, that processor spent on query execute and system load mast don’t have an influence on CPU Time? Or i wrong?
If the grow up of CPU Time – bug, how get rid of this?
PS
There are is no parallelism
Sometimes plan taking of from cache, some times – compile – this is not significance
I’m try to change indexes – plan is change, but problem high grow up CPU Time for identical queries with identical plans is exists all one
In server 4 4-core Xeon, which haven’t HyperThreading
I’m try do index rebuild, update statistics with fullscan, DBCC FREEPROCCACHE, DBCC dropcleanbuffers – this is not help
Query: exec sp_executesql N'select ... from ... join ... where ... order ... ' ', N'@Parametr1 varchar(80)', '%Znachenie1%'
select @@version: Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Grow up of CPU Time was discover after SQL Server SP3 install
February 18, 2009 at 7:06 am
I'm not entirely sure I understand the problem. From the sounds of it, you think the compile doesn't affect CPU. It does.
If you can, can you post the query, an actual execution plan, and the affected table structures? With more data it might be possible to get more specific on the answer.
"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
February 18, 2009 at 8:00 am
Grant Fritchey (2/18/2009)
I'm not entirely sure I understand the problem. From the sounds of it, you think the compile doesn't affect CPU. It does.
sorry, i'm forget note that plan may be taken from cache, may be compile
this is not important -- result plane is identical
if plan compile, parse and compile time very small comparatively CPU Time
fofr example, i run query 10 times
all times plans identical, take from cache
8 times CPU Time = 1 second
1 time CPU Time = 3 seconds
1 time CPU Time = 10 seconds
February 18, 2009 at 8:07 am
Grant Fritchey (2/18/2009)
If you can, can you post the query, an actual execution plan, and the affected table structures? With more data it might be possible to get more specific on the answer.
i post query and actual execution plan 3 hours later
February 18, 2009 at 8:13 am
Are the parameters the same for each execution?
"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 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply