April 1, 2015 at 12:40 am
A stored proc working fine in local is getting time out error in production, Issue cant be reproduced in local and I dont have access to production. Is there any thing can be changed in sp level?
urgent solution is required. Proc is taking XML as input and inserting into some tables. Does not have any parameters. Help Needed.
April 1, 2015 at 2:50 am
A timeout means that the procedure is running for longer than the application is willing to wait. You'll need to analyse the proc, identify the cause of the poor performance and tune it. There's no magic switch or option.
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
April 1, 2015 at 3:29 am
But in local it is working fine. Not able to c any performance issue.We r not sure, whether it is with sp or
April 1, 2015 at 3:37 am
It's the stored proc.
Not uncommon for a dev server not to show performance issues that prod does. Dev typically has tiny data sets and a couple of users at most. Prod has larger data volumes and lots of concurrent access and contention.
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
April 1, 2015 at 4:09 am
Shall I go ahead and verify the execution plans for performance or?
April 1, 2015 at 4:11 am
You need to investigate the proc and identify what portions are performing badly and fix it. Execution plans are part of that.
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
April 2, 2015 at 5:30 am
Run this with your proc name on live vs your local.
I give you this because set options may be a reason why you can say 'works on my machine'.
Issue could also be blocking, poor indexing, overindexing, massive result sets, but you can eliminate set options by comparing the below.
SELECT plan_handle, usecounts, pvt.set_options
FROM (
SELECT plan_handle, usecounts, epa.attribute, epa.value
FROM sys.dm_exec_cached_plans
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
WHERE cacheobjtype = 'Compiled Plan') AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "objectid")) AS pvt
where pvt.objectid = object_id('MyProcNAme');
THEN
Take the set_options from both and plug into the below and you will see the differences in the set options on live vs your local, if there are any.
declare @set_options int = 4347
if ((1 & @set_options) = 1) print 'ANSI_PADDING'
if ((4 & @set_options) = 4) print 'FORCEPLAN'
if ((8 & @set_options) = 8) print 'CONCAT_NULL_YIELDS_NULL'
if ((16 & @set_options) = 16) print 'ANSI_WARNINGS'
if ((32 & @set_options) = 32) print 'ANSI_NULLS'
if ((64 & @set_options) = 64) print 'QUOTED_IDENTIFIER'
if ((128 & @set_options) = 128) print 'ANSI_NULL_DFLT_ON'
if ((256 & @set_options) = 256) print 'ANSI_NULL_DFLT_OFF'
if ((512 & @set_options) = 512) print 'NoBrowseTable'
if ((4096 & @set_options) = 4096) print 'ARITH_ABORT'
if ((8192 & @set_options) = 8192) print 'NUMERIC_ROUNDABORT'
if ((16384 & @set_options) = 16384) print 'DATEFIRST'
if ((32768 & @set_options) = 32768) print 'DATEFORMAT'
if ((65536 & @set_options) = 65536) print 'LanguageID'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply