April 12, 2011 at 9:12 am
Good Morning,
I'm looking to do some performance tuning on the query listed below. My issue is that the application is sending all the values in Unicode IE N'some_value' by placing the N in front of the value. I've read that this prevents SQL from using indexes on the fields that are acting as filters/predicates in the where clause. That point being put aside for a second. I can get a value for the db table on the where clause field to replace the parameter and at least get a valid value that actually exists in the table. However, as you'll see in the query, a couple of the parameters are being placed in the SELECT portion of the nested query, this leaves me with absolutely no clue as to which field they are selecting on.
How can I get the values that are being passed into SQL Server on this query. I've already ran Profiler which is how I received the query below being posted.
The setting in the database relating to FORCED PARAMETERIZATION has been set to allow SQL Server to not recompile this query everytime it comes into the database.
Thanks for any help that points me in the right direction.
(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 int,@P3 int,@P4 nvarchar(4000),@P5 nvarchar(4000),@P6
int,@P7 int,@P8 nvarchar(4000),@P9 int,@P10 nvarchar(
4000),@P11 int,@P12 int,@P13 int,@P14 nvarchar(4000),@P15 nvarchar(
4000),@P16 int)
SELECT kits.wrkref wrkref,
kits.wh_id wh_id
FROM pckwrk
JOIN locmst
ON pckwrk.srcloc = locmst.stoloc
AND pckwrk.wh_id = locmst.wh_id
JOIN
(
SELECT pw.wrkref wrkref,
(
SELECT TOP 1 pw1.wrkref
FROM pckwrk pw1
WHERE pw1.wh_id = pw.wh_id
AND pw1.ctnnum = pw.subnum
AND pw1.wrktyp = @P0
AND pw1.pcksts = @P1
AND pw1.list_id is null
AND pw1.ackdevcod is null
AND 1=1
)
first_dtl_wrkref,
pw.wh_id wh_id
FROM pckwrk pw,
pckmov pm
WHERE pw.cmbcod = pm.cmbcod
AND pw.dstare = pm.arecod
AND pw.wh_id = pm.wh_id
AND not exists
(
SELECT @P2
FROM pckwrk pw3
WHERE pw3.ctnnum = pw.subnum
AND not exists
(
SELECT @P3
FROM pcklst_lodlvl pl
WHERE pl.arecod = pw3.srcare
AND pl.lodlvl = pw3.lodlvl
AND pl.wh_id = pw3.wh_id
)
)
AND pw.subnum is not null
AND pw.wrktyp = @P4
AND pw.pcksts = @P5
AND pw.appqty = @P6
AND pw.list_id is null
AND pw.ackdevcod is null
)
kits
ON pckwrk.wrkref = kits.first_dtl_wrkref
AND pckwrk.wh_id = kits.wh_id
WHERE pckwrk.ordnum is not null
AND pckwrk.unique_pallet_id is null
UNION
SELECT pckwrk.wrkref wrkref,
pckwrk.wh_id wh_id
FROM pckwrk
JOIN locmst
ON pckwrk.srcloc = locmst.stoloc
AND pckwrk.wh_id = locmst.wh_id
JOIN
(
SELECT min (pw1.wrkref) wrkref,
pw1.wh_id wh_id
FROM pckwrk pw1
WHERE not exists
(
SELECT @P7
FROM pckwrk pw5
WHERE pw5.cmbcod = pw1.cmbcod
AND (pw5.pcksts != @P8
OR pw5.appqty > @P9
OR pw5.list_id is not null
OR pw5.ackdevcod is not null
OR pw5.ctnnum is not null
OR pw5.wrktyp != @P10)
)
GROUP BY pw1.cmbcod,
pw1.wh_id
)
pcks
ON pckwrk.wrkref = pcks.wrkref
WHERE exists
(
SELECT @P11
FROM pcklst_lodlvl
WHERE pckwrk.start_pal_flg = @P12
OR (pckwrk.start_pal_flg = @P13
AND pcklst_lodlvl.arecod = pckwrk.srcare
AND pcklst_lodlvl.lodlvl = pckwrk.lodlvl
AND pcklst_lodlvl.wh_id = pckwrk.wh_id)
)
AND pckwrk.ordnum is not null
AND pckwrk.wrktyp = @P14
AND pckwrk.ctnnum is null
AND pckwrk.pcksts = @P15
AND pckwrk.appqty = @P16
AND pckwrk.list_id is null
AND pckwrk.ackdevcod is null
AND pckwrk.unique_pallet_id is null
April 12, 2011 at 12:36 pm
What event are you capturing in the trace? When I run a trace on a database with forced parameterization set, I still see values passed in. If you're seeing that, that's how the code is being formatted I think.
You could try capturing the actual execution plan, but be careful doing that on a production system, that event puts a bit of a strain on the system unless you filter it very carefully. The plan will have the values passed to the parameters.
"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
April 12, 2011 at 12:52 pm
Thanks Grant for the reply. Yes the profiler can definately impact production. But I'm using the default trace in Profiler. It captures both the RPC statement begin and statement completed fields. The issue is that reviewing all the query info in the textdata field that the values used in the parameters isn't given... hence the post. It is coming from the application this way, however I would think that SQL Server would have to have those values in order to complete the query. I just need to know where it is so I can reconstruct the query and then look at the execution plans for enhancement.
Secondary question : The app is sending in the params as Unicode have you found that that connection string property prevents SQL Server form using indexes on those fields? I read in a different source that it does, just looking for confirmation.
April 12, 2011 at 1:04 pm
Dale Cunningham (4/12/2011)
Thanks Grant for the reply. Yes the profiler can definately impact production. But I'm using the default trace in Profiler. It captures both the RPC statement begin and statement completed fields. The issue is that reviewing all the query info in the textdata field that the values used in the parameters isn't given... hence the post. It is coming from the application this way, however I would think that SQL Server would have to have those values in order to complete the query. I just need to know where it is so I can reconstruct the query and then look at the execution plans for enhancement.Secondary question : The app is sending in the params as Unicode have you found that that connection string property prevents SQL Server form using indexes on those fields? I read in a different source that it does, just looking for confirmation.
Sorry, I wasn't clear, you can add an event to capture the actual execution plan, XML Showplan Statistics Profile. If you then export that out to a file you can get to the plan and see the values passed in.
Depending on how the query was prepped by the code, you might not see it. BTW, this doesn't have anything to do with forced parameterization.
Unless for some reason it's doing an implicit conversion, there's no reason I can think of that unicode would lead to the optimizer not picking indexes, and I'm not sure that type of conversion would lead to it. I'd have to test it.
"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
April 12, 2011 at 1:32 pm
Thanks for the info Grant.
Dale
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply