April 4, 2012 at 3:41 pm
I have a developer who has a SP which takes 10 ~ 24 hours which usually took minutes.
This SP creates Dynamic SQL that picks up data from text-file i/p & creates multiple insert statements.
When I put one of the insert statements in DETA it suggested me to create 9 stats & one index with a possible improvisation of 90%, after implementing those suggestions I found out that this DB which has around 30 tables and about 5GB in size has 972 previously created Statistics, something tells me this is a high number. Is this a high number ? how do I know which of these are being used and which are useless. All those 972 stats were auto created.
The insert statements are pretty convoluted ..
examples would be
INSERT INTO allitenary(cl1, tnumbers,pin,zip,mail, tilt, gauge)
SELECT hail, tnumbers,
'refund','MISSED','Order replaced','789','4568'
FROM FLEET_TEMP
WHERE processed in ('N')
AND filemonth='05'
AND fileyear='2011'
AND substring(hail,4,4)='4504'
AND refund ='W'
AND (hail+tnumbers+typeloss) in
(SELECT DISTINCT (hail+tnumbers+typeloss)
FROM IBR_PROPERTY
WHERE refund ='W')
AND (hail+tnumbers)
NOT IN
(SELECT DISTINCT (hail+tnumbers)
FROM FLEET_TEMP
WHERE refund ='D'AND processed='N'
)
So basically a lot of sub-queries and the stored procedure creates different types of such insert
statements.
what is the best approach to troubelshoot this issue ?
Shd I take each individual query from the generates sql file and put it in DB Engine Tuning adviser?
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
April 5, 2012 at 5:43 am
Stats get created any time a column is referenced in a WHERE or JOIN criteria. It doesn't necessarily mean anything.
I would take any and all suggestions from the DTA with a HUGE grain of salt. In fact, I don't trust them at all. Instead, I suggest reading the execution plan yourself in order to understand what's going on with the query. If it's running one way with one set of parameters and another way with a different set (from what you said), then you might be looking at bad parameter sniffing. Making sure your statistics are up to date is a good first step.
"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