CLR Stored Procedure is timing out

At one seemingly inoccuous step in my CLR stored procedure, execution stops and the query times-out.

I've tried debugging the stored proc by stepping into it from within VS. When I do, I get to the code in question, but then simply get this message:

WARNING: Debugger was accessing T-SQL variables while managed code was not suspended.
Waiting until the access is done to continue T-SQL execution.
Continueing T-SQL execution.

And these messages appear to repeat indefinitely. I'm running SQL Server locally on my machine, but this also happens on out development SQL Server server.

The place in the code it appears to happen is when returning back results from a lower-level CLR stored proc called within the higher-level CLR stored proc -- when piping the result set, I suppose.

I've set MAXDOP to 1. No help.

Anyone know what's going on?

Are the CPU and memory getting taken up more and more along with the message appears to repeat indefinitely?

Here is one thread with the same warning, it may be similar to your case:

http://forums.microsoft.com/technet/showpost.aspx?postid=780559&siteid=17

Suggest to move the thread to the forum .NET Framework inside SQL Server, there you will get rapid and qualified responses.

Thanks for your understanding!

CLR Stored proc is timing out

At one seemingly inoccuous step in my CLR stored procedure, execution stops
and the query times-out.
I've tried debugging the stored proc by stepping into it from within VS.
When I do, I get to the code in question, but then simply get this message:
WARNING: Debugger was accessing T-SQL variables while managed code was not
suspended.
Waiting until the access is done to continue T-SQL execution.
Continueing T-SQL execution.
And these messages appear to repeat indefinitely. I'm running SQL Server
locally on my machine, but this also happens on out development SQL Server
server.
The place in the code it appears to happen is when returning back results
from a lower-level CLR stored proc called within the higher-level CLR stored
proc -- when piping the result set, I suppose.
I've set MAXDOP to 1. No help there.
Anyone know what's going on?Hello Quimbly,

> The place in the code it appears to happen is when returning back
> results from a lower-level CLR stored proc called within the
> higher-level CLR stored proc -- when piping the result set, I
> suppose.
Can you be a bit more specific about what you're doing?
Is your CLR proc calling another CLR proc without in parameter passed as
ref out out? How are you executing the other stored procedure.
Note: a better newsgroup for this would be Micorosoft.Public.SqlServer.CLR,
I've copied this there.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||> Can you be a bit more specific about what you're doing?
The top-level CLR proc calls a regular static method in the SQL CLR project.
This static method makes a call to another SqlProcedure (static method
tagged with [Microsoft.SqlServer.Server.SqlProcedure] attribute).
The static method is calling the lower-level CLR proc as follows:
string sqlString = "LC_SP_ScheduleEvents_SelectForDate";
SqlCommand command = new SqlCommand(sqlString, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@.P_LuminaireID", luminaireID);
command.Parameters.AddWithValue("@.P_TargetDate", usageDate);
command.Parameters.AddWithValue("@.P_TargetDateStartInUTC", startTimeUTC);
command.Parameters.AddWithValue("@.P_TargetDateEndInUTC", endTimeUTC);
SqlDataReader reader = command.ExecuteReader();
//...
Inside the LC_SP_ScheduleEvents_SelectForDate proc, it times out when it's
piping it's results back:
I.e.:
...
for (int i = 0; i < dr.Length; i++)
{
// send one event
eventRow = new SqlDataRecord(eventRowMetaData);
eventRow.SetSqlBoolean(0, bool.Parse(dr[i]["IsLampOn"].ToString()));
eventRow.SetSqlDateTime(1,
DateTime.Parse(dr[i]["ActualEventTime"].ToString()));
eventRow.SetInt32(2, int.Parse(dr[i]["DimmingLevel"].ToString()));
SqlContext.Pipe.SendResultsRow(eventRow);
}
}
SqlContext.Pipe.SendResultsEnd();|||Hello Quimbly,
I know this seems lame, but I suspect you have a reference type issue here.
Try assigning IsLampOn, ActualEventTime and DimmingLevel to local value type
s,
then sets those into the DataRecord.
Its low hanging fruit to solve first.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

CLR Stored proc is timing out

At one seemingly inoccuous step in my CLR stored procedure, execution stops
and the query times-out.
I've tried debugging the stored proc by stepping into it from within VS.
When I do, I get to the code in question, but then simply get this message:
WARNING: Debugger was accessing T-SQL variables while managed code was not
suspended.
Waiting until the access is done to continue T-SQL execution.
Continueing T-SQL execution.
And these messages appear to repeat indefinitely. I'm running SQL Server
locally on my machine, but this also happens on out development SQL Server
server.
The place in the code it appears to happen is when returning back results
from a lower-level CLR stored proc called within the higher-level CLR stored
proc -- when piping the result set, I suppose.
I've set MAXDOP to 1. No help there.
Anyone know what's going on?
Hello Quimbly,

> The place in the code it appears to happen is when returning back
> results from a lower-level CLR stored proc called within the
> higher-level CLR stored proc -- when piping the result set, I
> suppose.
Can you be a bit more specific about what you're doing?
Is your CLR proc calling another CLR proc without in parameter passed as
ref out out? How are you executing the other stored procedure.
Note: a better newsgroup for this would be Micorosoft.Public.SqlServer.CLR,
I've copied this there.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||> Can you be a bit more specific about what you're doing?
The top-level CLR proc calls a regular static method in the SQL CLR project.
This static method makes a call to another SqlProcedure (static method
tagged with [Microsoft.SqlServer.Server.SqlProcedure] attribute).
The static method is calling the lower-level CLR proc as follows:
string sqlString = "LC_SP_ScheduleEvents_SelectForDate";
SqlCommand command = new SqlCommand(sqlString, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@.P_LuminaireID", luminaireID);
command.Parameters.AddWithValue("@.P_TargetDate", usageDate);
command.Parameters.AddWithValue("@.P_TargetDateStar tInUTC", startTimeUTC);
command.Parameters.AddWithValue("@.P_TargetDateEndI nUTC", endTimeUTC);
SqlDataReader reader = command.ExecuteReader();
//...
Inside the LC_SP_ScheduleEvents_SelectForDate proc, it times out when it's
piping it's results back:
I.e.:
...
for (int i = 0; i < dr.Length; i++)
{
// send one event
eventRow = new SqlDataRecord(eventRowMetaData);
eventRow.SetSqlBoolean(0, bool.Parse(dr[i]["IsLampOn"].ToString()));
eventRow.SetSqlDateTime(1,
DateTime.Parse(dr[i]["ActualEventTime"].ToString()));
eventRow.SetInt32(2, int.Parse(dr[i]["DimmingLevel"].ToString()));
SqlContext.Pipe.SendResultsRow(eventRow);
}
}
SqlContext.Pipe.SendResultsEnd();
|||Hello Quimbly,
I know this seems lame, but I suspect you have a reference type issue here.
Try assigning IsLampOn, ActualEventTime and DimmingLevel to local value types,
then sets those into the DataRecord.
Its low hanging fruit to solve first.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

CLR Stored proc is timing out

At one seemingly inoccuous step in my CLR stored procedure, execution stops
and the query times-out.
I've tried debugging the stored proc by stepping into it from within VS.
When I do, I get to the code in question, but then simply get this message:
WARNING: Debugger was accessing T-SQL variables while managed code was not
suspended.
Waiting until the access is done to continue T-SQL execution.
Continueing T-SQL execution.
And these messages appear to repeat indefinitely. I'm running SQL Server
locally on my machine, but this also happens on out development SQL Server
server.
The place in the code it appears to happen is when returning back results
from a lower-level CLR stored proc called within the higher-level CLR stored
proc -- when piping the result set, I suppose.
I've set MAXDOP to 1. No help there.
Anyone know what's going on?Hello Quimbly,
> The place in the code it appears to happen is when returning back
> results from a lower-level CLR stored proc called within the
> higher-level CLR stored proc -- when piping the result set, I
> suppose.
Can you be a bit more specific about what you're doing?
Is your CLR proc calling another CLR proc without in parameter passed as
ref out out? How are you executing the other stored procedure.
Note: a better newsgroup for this would be Micorosoft.Public.SqlServer.CLR,
I've copied this there.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||> Can you be a bit more specific about what you're doing?
The top-level CLR proc calls a regular static method in the SQL CLR project.
This static method makes a call to another SqlProcedure (static method
tagged with [Microsoft.SqlServer.Server.SqlProcedure] attribute).
The static method is calling the lower-level CLR proc as follows:
string sqlString = "LC_SP_ScheduleEvents_SelectForDate";
SqlCommand command = new SqlCommand(sqlString, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@.P_LuminaireID", luminaireID);
command.Parameters.AddWithValue("@.P_TargetDate", usageDate);
command.Parameters.AddWithValue("@.P_TargetDateStartInUTC", startTimeUTC);
command.Parameters.AddWithValue("@.P_TargetDateEndInUTC", endTimeUTC);
SqlDataReader reader = command.ExecuteReader();
//...
Inside the LC_SP_ScheduleEvents_SelectForDate proc, it times out when it's
piping it's results back:
I.e.:
...
for (int i = 0; i < dr.Length; i++)
{
// send one event
eventRow = new SqlDataRecord(eventRowMetaData);
eventRow.SetSqlBoolean(0, bool.Parse(dr[i]["IsLampOn"].ToString()));
eventRow.SetSqlDateTime(1,
DateTime.Parse(dr[i]["ActualEventTime"].ToString()));
eventRow.SetInt32(2, int.Parse(dr[i]["DimmingLevel"].ToString()));
SqlContext.Pipe.SendResultsRow(eventRow);
}
}
SqlContext.Pipe.SendResultsEnd();

CLR SP Execution

Hello,
I am currently working on an application which calls some CLR stored
procedures in SQL Server 2005. The first time one of the CLR stored
procedures (it doesn't matter which one) is called it takes a lot longer to
execute. After the first CLR stored procedure has been executed the CLR
stored procedures all execute in a reasonable amount of time.
I have been unable to find any articles explaining the process that occurs
when a CLR stored procedure is called. I am currently assuming the initial
delay is related to the CLR loading the DLL? Is the CLR loaded with SQL
Server or only when needed?
If anyone can point me in the direction of an article to aid my
understanding or can explain the process I would be grateful.
Thanks in advance.
Alistair HarrisonHow long of a delay do you experience? Do you get the delay with a trivial
proc like the one below? I get a sub-second response when I execute this
after a fresh SQL Server restart.
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void MyProc()
{
Microsoft.SqlServer.Server.SqlContext.Pipe.Send("Test message");
}
};
I'm no expert on SQL CLR internals but I can't think of any overhead beyond
the usual object-specific overhead.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
message news:71A086F6-487F-46FD-B501-A7844F6936E5@.microsoft.com...
> Hello,
> I am currently working on an application which calls some CLR stored
> procedures in SQL Server 2005. The first time one of the CLR stored
> procedures (it doesn't matter which one) is called it takes a lot longer
> to
> execute. After the first CLR stored procedure has been executed the CLR
> stored procedures all execute in a reasonable amount of time.
> I have been unable to find any articles explaining the process that occurs
> when a CLR stored procedure is called. I am currently assuming the initial
> delay is related to the CLR loading the DLL? Is the CLR loaded with SQL
> Server or only when needed?
> If anyone can point me in the direction of an article to aid my
> understanding or can explain the process I would be grateful.
> Thanks in advance.
> Alistair Harrison|||Dan,
Thanks for your response.
The following are examples of execution times recorded by the Client
Statistics when the query is executed:
Initial execution of my original sp: 1600 to 1700 ms
Subsequent execution: <100 ms
Initial execution of your test sp: 700 to 800 ms
Subsequent execution: <50 ms
Another point I noticed was that if the test sp is executed first followed
by my original sp following a restart the original sp takes around 1200 ms t
o
execute. This is less than the 1600 to 1700 ms it seems to take when
executing the sp first but is still much greater than the subsequent
execution time.
It might be worth mentioning that my original clr sp executes a couple of
tsql sps using a context connection and then returns some xml as an output
parameter.
Thanks,
Alistair
"Dan Guzman" wrote:

> How long of a delay do you experience? Do you get the delay with a trivia
l
> proc like the one below? I get a sub-second response when I execute this
> after a fresh SQL Server restart.
> public partial class StoredProcedures
> {
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void MyProc()
> {
> Microsoft.SqlServer.Server.SqlContext.Pipe.Send("Test message");
> }
> };
> I'm no expert on SQL CLR internals but I can't think of any overhead beyon
d
> the usual object-specific overhead.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
> message news:71A086F6-487F-46FD-B501-A7844F6936E5@.microsoft.com...
>
>|||It's likely that subsequent executions are faster simply a result of data
caching. I'd expect execution times to average about 1200ms if you run DBCC
DROPCLEANBUFFERS before each execution. For performance testing, I usually
execute DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE before each test.
I don't think the sub-second delay the first time the proc is run is
anything to be concerned about.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
message news:6A2A987D-E008-440F-867E-0DE4D6A3AA85@.microsoft.com...[vbcol=seagreen]
> Dan,
> Thanks for your response.
> The following are examples of execution times recorded by the Client
> Statistics when the query is executed:
> Initial execution of my original sp: 1600 to 1700 ms
> Subsequent execution: <100 ms
> Initial execution of your test sp: 700 to 800 ms
> Subsequent execution: <50 ms
> Another point I noticed was that if the test sp is executed first followed
> by my original sp following a restart the original sp takes around 1200 ms
> to
> execute. This is less than the 1600 to 1700 ms it seems to take when
> executing the sp first but is still much greater than the subsequent
> execution time.
> It might be worth mentioning that my original clr sp executes a couple of
> tsql sps using a context connection and then returns some xml as an output
> parameter.
> Thanks,
> Alistair
>
> "Dan Guzman" wrote:
>|||Dan
Thanks again for your response.
I have retested the two stored procedures running DROPCLEANBUFFERS and
FREEPROCCACHE before each execution. The execution times increase slightly
but are still consistently below 300ms.
I suppose as the longer execution only seems to occur following a restart it
is not too much of a worry just intriguing as to why it seems to occur.
Alistair
"Dan Guzman" wrote:

> It's likely that subsequent executions are faster simply a result of data
> caching. I'd expect execution times to average about 1200ms if you run DB
CC
> DROPCLEANBUFFERS before each execution. For performance testing, I usuall
y
> execute DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE before each test.
> I don't think the sub-second delay the first time the proc is run is
> anything to be concerned about.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
> message news:6A2A987D-E008-440F-867E-0DE4D6A3AA85@.microsoft.com...
>
>|||Hello Alistair,
It might be worth remembering that the Assembly (and its dependent assemblie
s)
still needs to be fetched, verified, JITTed and the app domain created befor
e
the CLR code runs in many cases. That could be why you have a slower initial
startup.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

CLR SP Execution

Hello,
I am currently working on an application which calls some CLR stored
procedures in SQL Server 2005. The first time one of the CLR stored
procedures (it doesn't matter which one) is called it takes a lot longer to
execute. After the first CLR stored procedure has been executed the CLR
stored procedures all execute in a reasonable amount of time.
I have been unable to find any articles explaining the process that occurs
when a CLR stored procedure is called. I am currently assuming the initial
delay is related to the CLR loading the DLL? Is the CLR loaded with SQL
Server or only when needed?
If anyone can point me in the direction of an article to aid my
understanding or can explain the process I would be grateful.
Thanks in advance.
Alistair Harrison
How long of a delay do you experience? Do you get the delay with a trivial
proc like the one below? I get a sub-second response when I execute this
after a fresh SQL Server restart.
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void MyProc()
{
Microsoft.SqlServer.Server.SqlContext.Pipe.Send("T est message");
}
};
I'm no expert on SQL CLR internals but I can't think of any overhead beyond
the usual object-specific overhead.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
message news:71A086F6-487F-46FD-B501-A7844F6936E5@.microsoft.com...
> Hello,
> I am currently working on an application which calls some CLR stored
> procedures in SQL Server 2005. The first time one of the CLR stored
> procedures (it doesn't matter which one) is called it takes a lot longer
> to
> execute. After the first CLR stored procedure has been executed the CLR
> stored procedures all execute in a reasonable amount of time.
> I have been unable to find any articles explaining the process that occurs
> when a CLR stored procedure is called. I am currently assuming the initial
> delay is related to the CLR loading the DLL? Is the CLR loaded with SQL
> Server or only when needed?
> If anyone can point me in the direction of an article to aid my
> understanding or can explain the process I would be grateful.
> Thanks in advance.
> Alistair Harrison
|||Dan,
Thanks for your response.
The following are examples of execution times recorded by the Client
Statistics when the query is executed:
Initial execution of my original sp: 1600 to 1700 ms
Subsequent execution: <100 ms
Initial execution of your test sp: 700 to 800 ms
Subsequent execution: <50 ms
Another point I noticed was that if the test sp is executed first followed
by my original sp following a restart the original sp takes around 1200 ms to
execute. This is less than the 1600 to 1700 ms it seems to take when
executing the sp first but is still much greater than the subsequent
execution time.
It might be worth mentioning that my original clr sp executes a couple of
tsql sps using a context connection and then returns some xml as an output
parameter.
Thanks,
Alistair
"Dan Guzman" wrote:

> How long of a delay do you experience? Do you get the delay with a trivial
> proc like the one below? I get a sub-second response when I execute this
> after a fresh SQL Server restart.
> public partial class StoredProcedures
> {
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void MyProc()
> {
> Microsoft.SqlServer.Server.SqlContext.Pipe.Send("T est message");
> }
> };
> I'm no expert on SQL CLR internals but I can't think of any overhead beyond
> the usual object-specific overhead.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
> message news:71A086F6-487F-46FD-B501-A7844F6936E5@.microsoft.com...
>
>
|||It's likely that subsequent executions are faster simply a result of data
caching. I'd expect execution times to average about 1200ms if you run DBCC
DROPCLEANBUFFERS before each execution. For performance testing, I usually
execute DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE before each test.
I don't think the sub-second delay the first time the proc is run is
anything to be concerned about.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
message news:6A2A987D-E008-440F-867E-0DE4D6A3AA85@.microsoft.com...[vbcol=seagreen]
> Dan,
> Thanks for your response.
> The following are examples of execution times recorded by the Client
> Statistics when the query is executed:
> Initial execution of my original sp: 1600 to 1700 ms
> Subsequent execution: <100 ms
> Initial execution of your test sp: 700 to 800 ms
> Subsequent execution: <50 ms
> Another point I noticed was that if the test sp is executed first followed
> by my original sp following a restart the original sp takes around 1200 ms
> to
> execute. This is less than the 1600 to 1700 ms it seems to take when
> executing the sp first but is still much greater than the subsequent
> execution time.
> It might be worth mentioning that my original clr sp executes a couple of
> tsql sps using a context connection and then returns some xml as an output
> parameter.
> Thanks,
> Alistair
>
> "Dan Guzman" wrote:
|||Dan
Thanks again for your response.
I have retested the two stored procedures running DROPCLEANBUFFERS and
FREEPROCCACHE before each execution. The execution times increase slightly
but are still consistently below 300ms.
I suppose as the longer execution only seems to occur following a restart it
is not too much of a worry just intriguing as to why it seems to occur.
Alistair
"Dan Guzman" wrote:

> It's likely that subsequent executions are faster simply a result of data
> caching. I'd expect execution times to average about 1200ms if you run DBCC
> DROPCLEANBUFFERS before each execution. For performance testing, I usually
> execute DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE before each test.
> I don't think the sub-second delay the first time the proc is run is
> anything to be concerned about.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
> message news:6A2A987D-E008-440F-867E-0DE4D6A3AA85@.microsoft.com...
>
>
|||Hello Alistair,
It might be worth remembering that the Assembly (and its dependent assemblies)
still needs to be fetched, verified, JITTed and the app domain created before
the CLR code runs in many cases. That could be why you have a slower initial
startup.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

CLR SP Execution

Hello,
I am currently working on an application which calls some CLR stored
procedures in SQL Server 2005. The first time one of the CLR stored
procedures (it doesn't matter which one) is called it takes a lot longer to
execute. After the first CLR stored procedure has been executed the CLR
stored procedures all execute in a reasonable amount of time.
I have been unable to find any articles explaining the process that occurs
when a CLR stored procedure is called. I am currently assuming the initial
delay is related to the CLR loading the DLL? Is the CLR loaded with SQL
Server or only when needed?
If anyone can point me in the direction of an article to aid my
understanding or can explain the process I would be grateful.
Thanks in advance.
Alistair HarrisonHow long of a delay do you experience? Do you get the delay with a trivial
proc like the one below? I get a sub-second response when I execute this
after a fresh SQL Server restart.
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void MyProc()
{
Microsoft.SqlServer.Server.SqlContext.Pipe.Send("Test message");
}
};
I'm no expert on SQL CLR internals but I can't think of any overhead beyond
the usual object-specific overhead.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
message news:71A086F6-487F-46FD-B501-A7844F6936E5@.microsoft.com...
> Hello,
> I am currently working on an application which calls some CLR stored
> procedures in SQL Server 2005. The first time one of the CLR stored
> procedures (it doesn't matter which one) is called it takes a lot longer
> to
> execute. After the first CLR stored procedure has been executed the CLR
> stored procedures all execute in a reasonable amount of time.
> I have been unable to find any articles explaining the process that occurs
> when a CLR stored procedure is called. I am currently assuming the initial
> delay is related to the CLR loading the DLL? Is the CLR loaded with SQL
> Server or only when needed?
> If anyone can point me in the direction of an article to aid my
> understanding or can explain the process I would be grateful.
> Thanks in advance.
> Alistair Harrison|||Dan,
Thanks for your response.
The following are examples of execution times recorded by the Client
Statistics when the query is executed:
Initial execution of my original sp: 1600 to 1700 ms
Subsequent execution: <100 ms
Initial execution of your test sp: 700 to 800 ms
Subsequent execution: <50 ms
Another point I noticed was that if the test sp is executed first followed
by my original sp following a restart the original sp takes around 1200 ms to
execute. This is less than the 1600 to 1700 ms it seems to take when
executing the sp first but is still much greater than the subsequent
execution time.
It might be worth mentioning that my original clr sp executes a couple of
tsql sps using a context connection and then returns some xml as an output
parameter.
Thanks,
Alistair
"Dan Guzman" wrote:
> How long of a delay do you experience? Do you get the delay with a trivial
> proc like the one below? I get a sub-second response when I execute this
> after a fresh SQL Server restart.
> public partial class StoredProcedures
> {
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void MyProc()
> {
> Microsoft.SqlServer.Server.SqlContext.Pipe.Send("Test message");
> }
> };
> I'm no expert on SQL CLR internals but I can't think of any overhead beyond
> the usual object-specific overhead.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
> message news:71A086F6-487F-46FD-B501-A7844F6936E5@.microsoft.com...
> > Hello,
> >
> > I am currently working on an application which calls some CLR stored
> > procedures in SQL Server 2005. The first time one of the CLR stored
> > procedures (it doesn't matter which one) is called it takes a lot longer
> > to
> > execute. After the first CLR stored procedure has been executed the CLR
> > stored procedures all execute in a reasonable amount of time.
> >
> > I have been unable to find any articles explaining the process that occurs
> > when a CLR stored procedure is called. I am currently assuming the initial
> > delay is related to the CLR loading the DLL? Is the CLR loaded with SQL
> > Server or only when needed?
> >
> > If anyone can point me in the direction of an article to aid my
> > understanding or can explain the process I would be grateful.
> >
> > Thanks in advance.
> >
> > Alistair Harrison
>
>|||It's likely that subsequent executions are faster simply a result of data
caching. I'd expect execution times to average about 1200ms if you run DBCC
DROPCLEANBUFFERS before each execution. For performance testing, I usually
execute DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE before each test.
I don't think the sub-second delay the first time the proc is run is
anything to be concerned about.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
message news:6A2A987D-E008-440F-867E-0DE4D6A3AA85@.microsoft.com...
> Dan,
> Thanks for your response.
> The following are examples of execution times recorded by the Client
> Statistics when the query is executed:
> Initial execution of my original sp: 1600 to 1700 ms
> Subsequent execution: <100 ms
> Initial execution of your test sp: 700 to 800 ms
> Subsequent execution: <50 ms
> Another point I noticed was that if the test sp is executed first followed
> by my original sp following a restart the original sp takes around 1200 ms
> to
> execute. This is less than the 1600 to 1700 ms it seems to take when
> executing the sp first but is still much greater than the subsequent
> execution time.
> It might be worth mentioning that my original clr sp executes a couple of
> tsql sps using a context connection and then returns some xml as an output
> parameter.
> Thanks,
> Alistair
>
> "Dan Guzman" wrote:
>> How long of a delay do you experience? Do you get the delay with a
>> trivial
>> proc like the one below? I get a sub-second response when I execute this
>> after a fresh SQL Server restart.
>> public partial class StoredProcedures
>> {
>> [Microsoft.SqlServer.Server.SqlProcedure]
>> public static void MyProc()
>> {
>> Microsoft.SqlServer.Server.SqlContext.Pipe.Send("Test message");
>> }
>> };
>> I'm no expert on SQL CLR internals but I can't think of any overhead
>> beyond
>> the usual object-specific overhead.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
>> message news:71A086F6-487F-46FD-B501-A7844F6936E5@.microsoft.com...
>> > Hello,
>> >
>> > I am currently working on an application which calls some CLR stored
>> > procedures in SQL Server 2005. The first time one of the CLR stored
>> > procedures (it doesn't matter which one) is called it takes a lot
>> > longer
>> > to
>> > execute. After the first CLR stored procedure has been executed the CLR
>> > stored procedures all execute in a reasonable amount of time.
>> >
>> > I have been unable to find any articles explaining the process that
>> > occurs
>> > when a CLR stored procedure is called. I am currently assuming the
>> > initial
>> > delay is related to the CLR loading the DLL? Is the CLR loaded with SQL
>> > Server or only when needed?
>> >
>> > If anyone can point me in the direction of an article to aid my
>> > understanding or can explain the process I would be grateful.
>> >
>> > Thanks in advance.
>> >
>> > Alistair Harrison
>>|||Dan
Thanks again for your response.
I have retested the two stored procedures running DROPCLEANBUFFERS and
FREEPROCCACHE before each execution. The execution times increase slightly
but are still consistently below 300ms.
I suppose as the longer execution only seems to occur following a restart it
is not too much of a worry just intriguing as to why it seems to occur.
Alistair
"Dan Guzman" wrote:
> It's likely that subsequent executions are faster simply a result of data
> caching. I'd expect execution times to average about 1200ms if you run DBCC
> DROPCLEANBUFFERS before each execution. For performance testing, I usually
> execute DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE before each test.
> I don't think the sub-second delay the first time the proc is run is
> anything to be concerned about.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
> message news:6A2A987D-E008-440F-867E-0DE4D6A3AA85@.microsoft.com...
> > Dan,
> >
> > Thanks for your response.
> >
> > The following are examples of execution times recorded by the Client
> > Statistics when the query is executed:
> >
> > Initial execution of my original sp: 1600 to 1700 ms
> > Subsequent execution: <100 ms
> >
> > Initial execution of your test sp: 700 to 800 ms
> > Subsequent execution: <50 ms
> >
> > Another point I noticed was that if the test sp is executed first followed
> > by my original sp following a restart the original sp takes around 1200 ms
> > to
> > execute. This is less than the 1600 to 1700 ms it seems to take when
> > executing the sp first but is still much greater than the subsequent
> > execution time.
> >
> > It might be worth mentioning that my original clr sp executes a couple of
> > tsql sps using a context connection and then returns some xml as an output
> > parameter.
> >
> > Thanks,
> >
> > Alistair
> >
> >
> > "Dan Guzman" wrote:
> >
> >> How long of a delay do you experience? Do you get the delay with a
> >> trivial
> >> proc like the one below? I get a sub-second response when I execute this
> >> after a fresh SQL Server restart.
> >>
> >> public partial class StoredProcedures
> >> {
> >> [Microsoft.SqlServer.Server.SqlProcedure]
> >> public static void MyProc()
> >> {
> >> Microsoft.SqlServer.Server.SqlContext.Pipe.Send("Test message");
> >> }
> >> };
> >>
> >> I'm no expert on SQL CLR internals but I can't think of any overhead
> >> beyond
> >> the usual object-specific overhead.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Alistair Harrison" <AlistairHarrison@.discussions.microsoft.com> wrote in
> >> message news:71A086F6-487F-46FD-B501-A7844F6936E5@.microsoft.com...
> >> > Hello,
> >> >
> >> > I am currently working on an application which calls some CLR stored
> >> > procedures in SQL Server 2005. The first time one of the CLR stored
> >> > procedures (it doesn't matter which one) is called it takes a lot
> >> > longer
> >> > to
> >> > execute. After the first CLR stored procedure has been executed the CLR
> >> > stored procedures all execute in a reasonable amount of time.
> >> >
> >> > I have been unable to find any articles explaining the process that
> >> > occurs
> >> > when a CLR stored procedure is called. I am currently assuming the
> >> > initial
> >> > delay is related to the CLR loading the DLL? Is the CLR loaded with SQL
> >> > Server or only when needed?
> >> >
> >> > If anyone can point me in the direction of an article to aid my
> >> > understanding or can explain the process I would be grateful.
> >> >
> >> > Thanks in advance.
> >> >
> >> > Alistair Harrison
> >>
> >>
> >>
>
>

close all existing connections and processes to a database

Dear all

I created this trigger on a table that i think failed while execution. I tried to modify it and run it again but it seems that i cant do that. If i try and delete the database i also cant - saying that it is still in use. But i am not using it and ther are no other users connected to it. I think the trigger has probably hit a loop and that is holding the link.

To close that i know that a solution would be to restart the SQL server instance but that would be a bit hard since the SQL server where my test database resides is a production server and has few other databases that are important and few users use them.

Is there any way through a SQL statement that there can be forced a delete? Or force close all the connections? Or force close all the processes without actually restarting the SQL server instance.

I have tried all options that were offered on some other forums like forcing it to a single user but even that operation can not be performed saying that the database is still in use.

Thank you so much for all your help and time.

Sincerely

Dan

You could cycle through the SPID's in master.dbo.sysprocess and KILL off any SPID's using the test database_id.

And then you have learned why you 'should' not 'play' on a production server. Create yourself a local server for experiementation and testing. Get an 'old', decommissioned desktop and convert it to a test server. There is no excuse for mucking up a production server.

clearing execution plan cache

Hi,

We have an application which fetches data from a table which has approximately 1 million records.

1. Nearly 25 users will be using this application concurrently.

2. frequent updations will be done to the records in the geographyrolecurriculum table.

3. This table has 1 clustered index and 4 nonclustered index bounded to it.

Problem Statement:

1. Application runs smoothly for 15 - 20 days and after that all the screens throws timeout errors.

When i clear the sys.syscacheobjects its working fine again and screens get loaded quickly.

Please tell me how clearing the syscacheobjects makes the execution fast? and is this the correct way to solve the timeout issue or is there any other alternative?

2. Will the stored procs timeout if the tempdb is full ?

Thanks,

Arunprasad

Hi,

1. Not sure if you're doing this but you should NEVER manually make changes to the system tables, I am hoping that you are running something like DBCC FREEPROCCACHE to clear out the cache.

What I reckon is happening when you clear out the cache is you are basically forcing all the queries to generate new execution plans, and the new plans seem to be faster than the older ones.

What you need to do to ensure that the query optimiser has enough information to determine the correct query plan is to:

a. Maintain you indexes

DBCC INDEXDEFRAG

ALTER INDEX ….. REBUILD / DBCC DBREINDEX

b. Keep your statistics up-to-date

sp_createstats / CREATE STATISTICS

sp_updatestats / UPDATE STATISTICS

2. Managing you tempdb is a task in itself. This is a very interesting read:

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Basically if you sps are creating temp tables or you are using order by, group by, etc you will be using your tempdb. There are issues around the tempdb but they can be managed.

|||

Hi xrayb,

Thanks for the suggestion and we did what you have adviced us to do but the customer says its a hectic process.

So can you suggest me a workaround?

1. i have update STATISTICS ON for all the databases( DATABASE LEVEL) my server has will this have an effect on the table as well?

(Ex: Update statistics <tablename>)

2. Will the new execution plan be always better the older ones always? because this is happening very frequently in my application.

3. Do you have any other suggestion or findings why this timeout happens this frequently.

4. I appreaciate the effort you have put in to clear my doubt.

Thanks,

Arun

|||

1. I have a couple of issues with turning the create / update stats feature on at DB level, although they may not be an issues in your environment but I see them as:

a. You have no control over when it's run.

b. The update is triggered when they are already out of date, and I think this is based on a percentage (I’ve heard 10% and 20%), this is not a problem when you 1000 rows in your table but if you have 50,000,000 then 10% is 5,000,000 which is an awful long time to wait for the stats to be updated. BTW you can enable trace flag 205 to check how often this happens.

Also compare your estimated and an actual graphical execution plans, this will also let on if the stats are current.

Updating stats should be run each time there's a substantial change to the data / indexes, also the ALTER INDEX can be an online operation (although I would only run it during the working day under duress).

Use the DBCC SHOW_STATISTICS command to check individual tables and see what their current state is regarding stats.

2. The execution may be different and hopefully it will be quicker and more efficient (but not always). You may need to do some research into why this is happening, again, like managing your tempdb, performance tuning is an art in its self. Have a look at the link below it will give you enough information to query the DMVs and check how your query is being treated by the query optimiser (like the query plan).

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

I have slightly modified one of the queries and this returns the top 50 overview of which currently cached batches or procedures are using the most CPU and how many times they are being executed.

Code Snippet

select top 50

sum(qs.total_worker_time) as total_cpu_time,

sum(qs.execution_count) as total_execution_count,

count(*) as number_of_statements,

qs.plan_handle,

eqp.objectid,

OBJECT_NAME(eqp.objectid)

from

sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS eqp

group by qs.plan_handle,eqp.objectid

order by sum(qs.total_worker_time) desc, sum(qs.execution_count) desc

3. You basically need to identify what queries are performing badly, you can capture them (with the above mentioned script) or with server side traces or profiler. You have options to return long running queries. Once you have identified these queries you can start working on them individually.

From what you have described I reckon you have some poorly written queries and as soon as there's a load on the server or the stats are slightly outdated they just collapse in on themselves. I would try and capture the offending query and examine it, you won’t have to go through many before you realise if it’s poorly written.

Have a look at the DMVs too.

From BOL:

Find the 10 missing indexes with the highest anticipated improvement for user queries

SELECT TOP 10 *

FROM sys.dm_db_missing_index_group_stats

ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC;