We have a SQL job that always needs to be running and have the "Start
Automatically when SQL Server Agent Starts" option enabled. We've
noticed that on occassion this job is stopped. No errors / failure
notifications.
Any idea why this would happen? How can we get this to always run, or
at least notify us when the job stops?
Thanks,
RubensAre you on the latest Service Packs ? It would also be nice if you can =mention the SQL Server version you are presetly on ... -- HTH,
Vinod Kumar
MCSE, DBA, MCAD
SCT Software Solutions http://www32.brinkster.com/sqlvinod
"Rubens" <rubensrose@.hotmail.com> wrote in message =news:a988e2b1.0307150438.7e58dc87@.posting.google.com...
> We have a SQL job that always needs to be running and have the "Start
> Automatically when SQL Server Agent Starts" option enabled. We've
> noticed that on occassion this job is stopped. No errors / failure
> notifications.
> > Any idea why this would happen? How can we get this to always run, or
> at least notify us when the job stops?
> > Thanks,
> Rubens|||"Vinodk" <vinodk sct@.hotmail.com> wrote in message news:<erfG7AtSDHA.2148@.TK2MSFTNGP10.phx.gbl>...
> Are you on the latest Service Packs ? It would also be nice if you can
> mention the SQL Server version you are presetly on ...
Sorry, can't believe I didn't mention that. SQL Server 2000 with SP3 on W2K SP3.
Thanks,
Rubens|||"Vinodk" <vinodk sct@.hotmail.com> wrote in message news:<erfG7AtSDHA.2148@.TK2MSFTNGP10.phx.gbl>...
> Are you on the latest Service Packs ? It would also be nice if you can
> mention the SQL Server version you are presetly on ...
Sorry, can't believe I didn't mentioned that. SQL Server 2000 SP3.
Thanks,
Rubens
Showing posts with label job. Show all posts
Showing posts with label job. Show all posts
Sunday, March 11, 2012
Thursday, February 16, 2012
"FOR XML Explicit" why poor performance, and any "quick wins" ?
Just started a new job and need a quick fix if possible.
I need to enhance performance of XML data being returned from a SProc which
uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
Front End Application timeouts occasionally occur, which I believe can only
be caused by the SProc creating XML Data.
Without the "For XML Explicit" clause the DB returns about 5000 data rows in
approx 5 seconds.
With the "For XML Explicit" clause, same request will take 3.5 minutes !!
HOWEVER ... If I create copy of the procedure, and run the same request
parameters, it only takes about 6-8 seconds to complete.
No recompile going on as far a I can tell.
No more than 10 calls against this proc per minute.
There are several #Temp tables in the procedure.
I will improve performance by changing the whole process to return record
sets for the front end code to manipulate as required, which will take time
to integrate.
Any ideas on why this "apparent" performance issue happens ?
Any performance tweeks I can make right now will be of benefit.
Thanks
Steve ...
Doing the XML generation on the client-side may not be faster end-to-end
than using the FOR XML EXPLICIT clause.
Here are some questions:
1. You say that simply by copying and thus recompiling the stored proc, you
are getting 6-8 sec execution? But if you run the existing stored proc it
takes 3.5 mins?
If you look at the query plan, do you see any difference between them?
Can you force a recompile of the original stored proc?
It is very unusual that a for xml explicit query runs that much worse
compared to the same query without the FOR XML explicit clause. the overhead
should be more in the 1 sec range for a 5 sec query.
Best regards
Michael
"Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
> Just started a new job and need a quick fix if possible.
> I need to enhance performance of XML data being returned from a SProc
> which
> uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
> Front End Application timeouts occasionally occur, which I believe can
> only
> be caused by the SProc creating XML Data.
> Without the "For XML Explicit" clause the DB returns about 5000 data rows
> in
> approx 5 seconds.
> With the "For XML Explicit" clause, same request will take 3.5 minutes !!
> HOWEVER ... If I create copy of the procedure, and run the same request
> parameters, it only takes about 6-8 seconds to complete.
> No recompile going on as far a I can tell.
> No more than 10 calls against this proc per minute.
> There are several #Temp tables in the procedure.
> I will improve performance by changing the whole process to return record
> sets for the front end code to manipulate as required, which will take
> time
> to integrate.
> Any ideas on why this "apparent" performance issue happens ?
> Any performance tweeks I can make right now will be of benefit.
> Thanks
> Steve ...
|||Thanks for reply,
Already done recommendations.
The only thing I can put it down to is an occasional recompile event because
of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
Some data requests will be far larger than others, and it appears that these
are the ones with occasional time-outs.
Does this sound plausible, and should I create Indexes on the #TempTables in
the procedure to get round this ?
Thanks
Steve ...
"Michael Rys [MSFT]" wrote:
> Doing the XML generation on the client-side may not be faster end-to-end
> than using the FOR XML EXPLICIT clause.
> Here are some questions:
> 1. You say that simply by copying and thus recompiling the stored proc, you
> are getting 6-8 sec execution? But if you run the existing stored proc it
> takes 3.5 mins?
> If you look at the query plan, do you see any difference between them?
> Can you force a recompile of the original stored proc?
> It is very unusual that a for xml explicit query runs that much worse
> compared to the same query without the FOR XML explicit clause. the overhead
> should be more in the 1 sec range for a 5 sec query.
> Best regards
> Michael
> "Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
> news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
>
>
|||For now I've added an option clause on each SQL Statement in the procedure.
(NB: no parallel processing so Maxdop not required)
OPTION(KEEP PLAN)
As the SProc builds #Temp Tables to use in the final query,
Would I be better off using the OPTION(KEEPFIXED PLAN) instead ?
Steve ...
"Steve [DBA-TC]" wrote:
[vbcol=seagreen]
> Thanks for reply,
> Already done recommendations.
> The only thing I can put it down to is an occasional recompile event because
> of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
> Some data requests will be far larger than others, and it appears that these
> are the ones with occasional time-outs.
> Does this sound plausible, and should I create Indexes on the #TempTables in
> the procedure to get round this ?
> Thanks
> Steve ...
> "Michael Rys [MSFT]" wrote:
I need to enhance performance of XML data being returned from a SProc which
uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
Front End Application timeouts occasionally occur, which I believe can only
be caused by the SProc creating XML Data.
Without the "For XML Explicit" clause the DB returns about 5000 data rows in
approx 5 seconds.
With the "For XML Explicit" clause, same request will take 3.5 minutes !!
HOWEVER ... If I create copy of the procedure, and run the same request
parameters, it only takes about 6-8 seconds to complete.
No recompile going on as far a I can tell.
No more than 10 calls against this proc per minute.
There are several #Temp tables in the procedure.
I will improve performance by changing the whole process to return record
sets for the front end code to manipulate as required, which will take time
to integrate.
Any ideas on why this "apparent" performance issue happens ?
Any performance tweeks I can make right now will be of benefit.
Thanks
Steve ...
Doing the XML generation on the client-side may not be faster end-to-end
than using the FOR XML EXPLICIT clause.
Here are some questions:
1. You say that simply by copying and thus recompiling the stored proc, you
are getting 6-8 sec execution? But if you run the existing stored proc it
takes 3.5 mins?
If you look at the query plan, do you see any difference between them?
Can you force a recompile of the original stored proc?
It is very unusual that a for xml explicit query runs that much worse
compared to the same query without the FOR XML explicit clause. the overhead
should be more in the 1 sec range for a 5 sec query.
Best regards
Michael
"Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
> Just started a new job and need a quick fix if possible.
> I need to enhance performance of XML data being returned from a SProc
> which
> uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
> Front End Application timeouts occasionally occur, which I believe can
> only
> be caused by the SProc creating XML Data.
> Without the "For XML Explicit" clause the DB returns about 5000 data rows
> in
> approx 5 seconds.
> With the "For XML Explicit" clause, same request will take 3.5 minutes !!
> HOWEVER ... If I create copy of the procedure, and run the same request
> parameters, it only takes about 6-8 seconds to complete.
> No recompile going on as far a I can tell.
> No more than 10 calls against this proc per minute.
> There are several #Temp tables in the procedure.
> I will improve performance by changing the whole process to return record
> sets for the front end code to manipulate as required, which will take
> time
> to integrate.
> Any ideas on why this "apparent" performance issue happens ?
> Any performance tweeks I can make right now will be of benefit.
> Thanks
> Steve ...
|||Thanks for reply,
Already done recommendations.
The only thing I can put it down to is an occasional recompile event because
of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
Some data requests will be far larger than others, and it appears that these
are the ones with occasional time-outs.
Does this sound plausible, and should I create Indexes on the #TempTables in
the procedure to get round this ?
Thanks
Steve ...
"Michael Rys [MSFT]" wrote:
> Doing the XML generation on the client-side may not be faster end-to-end
> than using the FOR XML EXPLICIT clause.
> Here are some questions:
> 1. You say that simply by copying and thus recompiling the stored proc, you
> are getting 6-8 sec execution? But if you run the existing stored proc it
> takes 3.5 mins?
> If you look at the query plan, do you see any difference between them?
> Can you force a recompile of the original stored proc?
> It is very unusual that a for xml explicit query runs that much worse
> compared to the same query without the FOR XML explicit clause. the overhead
> should be more in the 1 sec range for a 5 sec query.
> Best regards
> Michael
> "Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
> news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
>
>
|||For now I've added an option clause on each SQL Statement in the procedure.
(NB: no parallel processing so Maxdop not required)
OPTION(KEEP PLAN)
As the SProc builds #Temp Tables to use in the final query,
Would I be better off using the OPTION(KEEPFIXED PLAN) instead ?
Steve ...
"Steve [DBA-TC]" wrote:
[vbcol=seagreen]
> Thanks for reply,
> Already done recommendations.
> The only thing I can put it down to is an occasional recompile event because
> of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
> Some data requests will be far larger than others, and it appears that these
> are the ones with occasional time-outs.
> Does this sound plausible, and should I create Indexes on the #TempTables in
> the procedure to get round this ?
> Thanks
> Steve ...
> "Michael Rys [MSFT]" wrote:
"FOR XML Explicit" why poor performance, and any "quick wins" ?
Just started a new job and need a quick fix if possible.
I need to enhance performance of XML data being returned from a SProc which
uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
Front End Application timeouts occasionally occur, which I believe can only
be caused by the SProc creating XML Data.
Without the "For XML Explicit" clause the DB returns about 5000 data rows in
approx 5 seconds.
With the "For XML Explicit" clause, same request will take 3.5 minutes !!
HOWEVER ... If I create copy of the procedure, and run the same request
parameters, it only takes about 6-8 seconds to complete.
No recompile going on as far a I can tell.
No more than 10 calls against this proc per minute.
There are several #Temp tables in the procedure.
I will improve performance by changing the whole process to return record
sets for the front end code to manipulate as required, which will take time
to integrate.
Any ideas on why this "apparent" performance issue happens ?
Any performance tw
s I can make right now will be of benefit.
Thanks
Steve ...Doing the XML generation on the client-side may not be faster end-to-end
than using the FOR XML EXPLICIT clause.
Here are some questions:
1. You say that simply by copying and thus recompiling the stored proc, you
are getting 6-8 sec execution? But if you run the existing stored proc it
takes 3.5 mins?
If you look at the query plan, do you see any difference between them?
Can you force a recompile of the original stored proc?
It is very unusual that a for xml explicit query runs that much worse
compared to the same query without the FOR XML explicit clause. the overhead
should be more in the 1 sec range for a 5 sec query.
Best regards
Michael
"Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
> Just started a new job and need a quick fix if possible.
> I need to enhance performance of XML data being returned from a SProc
> which
> uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
> Front End Application timeouts occasionally occur, which I believe can
> only
> be caused by the SProc creating XML Data.
> Without the "For XML Explicit" clause the DB returns about 5000 data rows
> in
> approx 5 seconds.
> With the "For XML Explicit" clause, same request will take 3.5 minutes !!
> HOWEVER ... If I create copy of the procedure, and run the same request
> parameters, it only takes about 6-8 seconds to complete.
> No recompile going on as far a I can tell.
> No more than 10 calls against this proc per minute.
> There are several #Temp tables in the procedure.
> I will improve performance by changing the whole process to return record
> sets for the front end code to manipulate as required, which will take
> time
> to integrate.
> Any ideas on why this "apparent" performance issue happens ?
> Any performance tws I can make right now will be of benefit.
> Thanks
> Steve ...
I need to enhance performance of XML data being returned from a SProc which
uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
Front End Application timeouts occasionally occur, which I believe can only
be caused by the SProc creating XML Data.
Without the "For XML Explicit" clause the DB returns about 5000 data rows in
approx 5 seconds.
With the "For XML Explicit" clause, same request will take 3.5 minutes !!
HOWEVER ... If I create copy of the procedure, and run the same request
parameters, it only takes about 6-8 seconds to complete.
No recompile going on as far a I can tell.
No more than 10 calls against this proc per minute.
There are several #Temp tables in the procedure.
I will improve performance by changing the whole process to return record
sets for the front end code to manipulate as required, which will take time
to integrate.
Any ideas on why this "apparent" performance issue happens ?
Any performance tw
s I can make right now will be of benefit.Thanks
Steve ...Doing the XML generation on the client-side may not be faster end-to-end
than using the FOR XML EXPLICIT clause.
Here are some questions:
1. You say that simply by copying and thus recompiling the stored proc, you
are getting 6-8 sec execution? But if you run the existing stored proc it
takes 3.5 mins?
If you look at the query plan, do you see any difference between them?
Can you force a recompile of the original stored proc?
It is very unusual that a for xml explicit query runs that much worse
compared to the same query without the FOR XML explicit clause. the overhead
should be more in the 1 sec range for a 5 sec query.
Best regards
Michael
"Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
> Just started a new job and need a quick fix if possible.
> I need to enhance performance of XML data being returned from a SProc
> which
> uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
> Front End Application timeouts occasionally occur, which I believe can
> only
> be caused by the SProc creating XML Data.
> Without the "For XML Explicit" clause the DB returns about 5000 data rows
> in
> approx 5 seconds.
> With the "For XML Explicit" clause, same request will take 3.5 minutes !!
> HOWEVER ... If I create copy of the procedure, and run the same request
> parameters, it only takes about 6-8 seconds to complete.
> No recompile going on as far a I can tell.
> No more than 10 calls against this proc per minute.
> There are several #Temp tables in the procedure.
> I will improve performance by changing the whole process to return record
> sets for the front end code to manipulate as required, which will take
> time
> to integrate.
> Any ideas on why this "apparent" performance issue happens ?
> Any performance tw
s I can make right now will be of benefit.> Thanks
> Steve ...
Thursday, February 9, 2012
"Cannot find keycodev2.dll or invalid keycode" for certain user account
Hi,
I've created program that sits on a server and provides some reporting functionality. The user submits a SQL Server job that calls this program, passing in some command args.
The SQL Server job is submitted by a C# program that is using a non-sysadmin connection account, therefore it is calling SETUSER to change the job mapping to a local user account, called MYSERVER\SQLServerJobs - I've granted it local admin rights.
Whenever SQL Server attempts to run this job, it's failing with the "Cannot find keycodev2.dll or invalid keycode" error. It's definitely not an install problem, I can run the program from command line using a domain account and it's fine. I guess its a permissions problem with the local account, but I can't put my finger on it!
Again, the local account is in the Administrators group & I've even explicitly granted it rights to the two registry keys
HKEY_CLASSES_ROOT\Interface\{4A4D268A-DF9B-4FC1-8301-D9FEEEF69F9C}
- HKEY_CLASSES_ROOT\TypeLib\{8D43D0B9-C14E-4577-9E67-A9A1EEF82302}
as detailed in some Crystal troubleshooting documents.
Anything else I should check for??
ThanksSOLVED:
I went back onto the machine, logging on as the local user account SQLServerJobs. I then reinstalled the 'CrystallInstaller' setup I had created (just all the merge modules that are required) & made sure the 'For everyone' box was checked - Now works fine.
I reckon i flew through the install the first time round and didn't check the 'For Everyone' box - can't remember to be honest, but would make sense!!
I've created program that sits on a server and provides some reporting functionality. The user submits a SQL Server job that calls this program, passing in some command args.
The SQL Server job is submitted by a C# program that is using a non-sysadmin connection account, therefore it is calling SETUSER to change the job mapping to a local user account, called MYSERVER\SQLServerJobs - I've granted it local admin rights.
Whenever SQL Server attempts to run this job, it's failing with the "Cannot find keycodev2.dll or invalid keycode" error. It's definitely not an install problem, I can run the program from command line using a domain account and it's fine. I guess its a permissions problem with the local account, but I can't put my finger on it!
Again, the local account is in the Administrators group & I've even explicitly granted it rights to the two registry keys
HKEY_CLASSES_ROOT\Interface\{4A4D268A-DF9B-4FC1-8301-D9FEEEF69F9C}
- HKEY_CLASSES_ROOT\TypeLib\{8D43D0B9-C14E-4577-9E67-A9A1EEF82302}
as detailed in some Crystal troubleshooting documents.
Anything else I should check for??
ThanksSOLVED:
I went back onto the machine, logging on as the local user account SQLServerJobs. I then reinstalled the 'CrystallInstaller' setup I had created (just all the merge modules that are required) & made sure the 'For everyone' box was checked - Now works fine.
I reckon i flew through the install the first time round and didn't check the 'For Everyone' box - can't remember to be honest, but would make sense!!
Subscribe to:
Posts (Atom)
