Profiling spikes in query duration
-
Table:
create table GamePeriodsAdvanced (
GameId varchar(30) not null,
PeriodType varchar(10) not null constraint CK_GamePeriodsAdvanced_PeriodType check (PeriodType in ('Q1', 'Q2', 'Q3', 'Q4', 'OT1', 'OT2', 'OT3', 'OT4', 'OT5', 'OT6', 'OT7', 'OT8', 'OT9', 'OT10')),
PlayerId varchar(30) not null,
TeamId varchar(30) not null,
Position varchar(10) not null,OFF_RATING decimal(15, 4),
DEF_RATING decimal(15, 4),
NET_RATING decimal(15, 4),
AST_PCT decimal(15, 4),
AST_TOV decimal(15, 4),
AST_RATIO decimal(15, 4),
OREB_PCT decimal(15, 4),
DREB_PCT decimal(15, 4),
REB_PCT decimal(15, 4),
TM_TOV_PCT decimal(15, 4),
EFG_PCT decimal(15, 4),
TS_PCT decimal(15, 4),
USG_PCT decimal(15, 4),
PACE decimal(15, 4),
PIE decimal(15, 4),constraint PK_GamePeriodsAdvanced primary key clustered (GameId, PeriodType, PlayerId),
constraint FK_GamePeriodsAdvanced_GameId foreign key (GameId) references Games (Id),
constraint FG_GamePeriodsAdvanced_PlayerId foreign key (PlayerId) references Players (Id)
)Query:
if not exists (select * from GamePeriodsAdvanced where GameId = @gameId and PeriodType = @periodType and PlayerId = @playerId)
begin
insert into GamePeriodsAdvanced (
GameId, PeriodType, PlayerId, TeamId, Position,
OFF_RATING, DEF_RATING, NET_RATING, AST_PCT, AST_TOV, AST_RATIO, OREB_PCT, DREB_PCT, REB_PCT,
TM_TOV_PCT, EFG_PCT, TS_PCT, USG_PCT, PACE, PIE
)
select
@gameId, @periodType, @playerId, @teamId, @position,
@OFF_RATING, @DEF_RATING, @NET_RATING, @AST_PCT, @AST_TOV, @AST_RATIO, @OREB_PCT, @DREB_PCT, @REB_PCT,
@TM_TOV_PCT, @EFG_PCT, @TS_PCT, @USG_PCT, @Pace, @PIE
endThe query is running inside a loop (c# desktop app). All data is already in the database, so the 'insert' query never executes, only the 'if not exists...'. There are around 100.000 rows, and the full loop runs about three minutes.
When I profile (with sql server profiler) the full loop I see some spikes in query duration:
How can I find out what causes those spikes? I tried with compile and recompile counters in perfmon, but the query is compiled only once and then reused. The PK_GamePeriodsAdvanced index has 0% fragmentation. I tried most SqlServer counters in perfmon but couldn't pinpoint the problem.
Any ideas?
Edit - this is my development machine where I am the only user and the database not used by other clients.
-
I'm guessing blocking. Is anything else running on the server when you do this? Try capturing sys.dm_os_waiting tasks as frequently for lock-related wait types as you can, and see whether there are any locks being held for extended periods. You can do something similar using Extended Events, and probably Profiler as well, if you prefer using that.
John
-
Long shot but are the stats up to date for what the loop uses? Also, it is possible that you're suffering from a bit of bad parameter sniffing for certain iterations of the loop.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Look into log space allocations. You can trace them in different ways, including extended events.
When a log file is extended, activities in the db must cease while that occurs. And the new space must be pre-formatted with binary zeros before use. To test it, try pre-allocating log space to cover all the INSERTs for a day (or whatever period). I personally don't see how parameter sniffing could affect the specific queries shown. I would have to look at a query plan to be 100% sure, but I'd think it would be a simple SEEK, based on the code itself.
On a related performance topic, you really should seriously consider encoding these columns:
GameId
PlayerId
TeamId
Position
to numerics to represent the string value.I do prefer natural keys whenever possible, esp. as they normally drastically improve search efficiency, but often the values do need to be encoded to prevent long strings from being used.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply