February 27, 2012 at 2:09 pm
While playing with performance for one of my tables (for testing purpoces I named it my_table, it has 15 mln records and 200k pages), I ran
UPDATE STATISTICS my_table
While it's running I checked what is going on inside of query manager for my SPID:
select
(select text
from sys.dm_exec_sql_text(c.most_recent_sql_handle)) as query_text
from
sys.dm_exec_sessions s join sys.dm_exec_connections c
on s.session_id = c.session_id
where
s.session_id =155
The result is this:
SELECT StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM (SELECT [ColKey] AS [SC0] FROM [dbo].[my_table] TABLESAMPLE SYSTEM (1.341679e+000 PERCENT) WITH (READUNCOMMITTED) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)
Note: if to run this query after commnad has finished, it will show simply " update statistics my_table"
But this is not a problem. The biggest problem is why it chose OPTION (MAXDOP 1) ?
Our maxdop on the server is 4. Server is not much utilized. And can I override it somehow ?
Thanks
February 27, 2012 at 2:11 pm
I don't think a stats update could be done in parallel. It pretty much needs to count values in columns, and spreading that out to multiple threads doesn't seem viable to me.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 27, 2012 at 2:23 pm
In such a case, why do they show this option at all ?
February 27, 2012 at 2:27 pm
Explicit vs Implicit.
Same reason it has "top 100 percent" in the same query. No point to that, since Select without a Top clause automatically does that. It's just a "tell more than needed rather than less" kind of thing. That's my take on it anyway.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 27, 2012 at 2:56 pm
It probably could theoretically be done in parallel (at least the select from table portion), but that would probably be a bad thing for overall system impact, so force a maxdop so that there's no way the query will ever go parallel
A lot of the internal commands have odd options that we can't specify or different behaviours from what we can specify
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply