March 27, 2009 at 7:32 am
Are informational PRINT statements in stored procedures costly in terms of performance or resource use?
March 27, 2009 at 7:35 am
It will eat up your output Buffer resource if I am not mistaken. PRINT Statements can be seen only on the SSMS window. So why keep it?
-Roy
March 27, 2009 at 9:22 am
They add to the overhead. I use them occasionally when debugging, but always comment them out.
However, I've never tested them to see what the exact cost is.
You can get them from the client.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 27, 2009 at 9:32 am
Grant Fritchey (3/27/2009)
You can get them from the client.
On which client? Normal Web App that uses the Sproc?
-Roy
March 27, 2009 at 9:41 am
Roy Ernest (3/27/2009)
Grant Fritchey (3/27/2009)
You can get them from the client.
On which client? Normal Web App that uses the Sproc?
I meant the data access layer, regardless of how it's displayed. You can retrieve messages, including print statements, through ADO.NET, ODBC, etc.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 27, 2009 at 9:45 am
I did not know that. I learn another thing. Thanks Grant. Learn something new everyday.. 🙂
-Roy
March 27, 2009 at 10:53 am
Roy Ernest (3/27/2009)
It will eat up your output Buffer resource if I am not mistaken. PRINT Statements can be seen only on the SSMS window. So why keep it?
Actually they can be seen in other places. The most obvious is in a SQLAgent job's output log. They can also be piped out to a file from a SQLCMD execution. And it is possible for a SQL Client program to capture and consume them (though I forget how).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2009 at 12:33 pm
I'm using a logging procedure instead of PRINT function. This has two advantages:
* You can use arguments to be used in a formatted string. The arguments are SQL_VARIANT (maybe the only reasonable use for this data type...) so I don't have to CONVERT all debug information always.
* You can either comment the body of the logging procedure out or use a configuration parameter in any config table to deactivate it. So I don't have comment them out if not need them.
PRINT (within this logging procedure) is very useful to log into client application log-file to see the information directly between the other actions of the process.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 28, 2009 at 2:14 am
Hello,
can anybody tell me how 1 can get PRINT statments from SP execution at client side?
I found it really useful in some cases when we need to log the actual reason of the failure.
One more thing,
the same requirement as i mention above can also be fulfill with 'Try..catch' (in SS 2k5), then what would be the basic advantage to get such kind of msgs at client side, as it will take extra memory and execution time!!!!
:exclamation:
"Don't limit your challenges, challenge your limits"
March 28, 2009 at 4:22 am
can anybody tell me how 1 can get PRINT statments from SP execution at client side?
I found it really useful in some cases when we need to log the actual reason of the failure.
It slightly depends on the client programming language but sure this is a very useful feature.
If you are using C# here is an example how to handle PRINT messages and errors on client (I'm curious about the formatting of my C# code by an SQL formatter...)
class Program
{
static void Main(string[] args)
{
using (SqlConnection cn = new SqlConnection("Server=(local);Database=Sandbox;Integrated Security=SSPI"))
{
cn.Open();
// The info message handler can be used for PRINT messages from server
cn.InfoMessage += new SqlInfoMessageEventHandler(cn_InfoMessage);
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
// Use PRINT
cmd.CommandText = "PRINT 'Hello world'";
cmd.ExecuteNonQuery();
Console.WriteLine();
Console.WriteLine();
try
{
// Use RAISERROR
cmd.CommandText = "RAISERROR ('Error because of %d flying cows at %s', 11, 1, 2, 'Oktoberfest')";
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine("####################################################");
Console.WriteLine("# A database error was raised!");
Console.WriteLine("#");
Console.WriteLine("# Please see following lines for further information");
Console.WriteLine("#");
Console.WriteLine("# ------------------------------------");
Console.WriteLine("# .NET EXCEPTION");
Console.WriteLine("#");
Console.WriteLine("# Message: {0}", ex.Message);
Console.WriteLine("# Number: {0}", ex.Number);
Console.WriteLine("# Procedure: {0}", ex.Procedure ?? "<NULL>");
Console.WriteLine("# State: {0}", ex.State);
foreach (SqlError error in ex.Errors)
{
Console.WriteLine("# ---------------------------------");
Console.WriteLine("# SQL ERROR");
Console.WriteLine("#");
Console.WriteLine("# Message: {0}", error.Message);
Console.WriteLine("# Number: {0}", error.Number);
Console.WriteLine("# Procedure: {0}", error.Procedure ?? "<NULL>");
Console.WriteLine("# State: {0}", error.State);
}
Console.WriteLine("#");
Console.WriteLine("####################################################");
//throw;
}
}
cn.InfoMessage -= new SqlInfoMessageEventHandler(cn_InfoMessage);
}
}
static void cn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
Console.WriteLine("PRINT message from SQL Server:");
Console.WriteLine(e.Message);
}
}
the same requirement as i mention above can also be fulfill with 'Try..catch' (in SS 2k5), then what would be the basic advantage to get such kind of msgs at client side, as it will take extra memory and execution time!!!!
Don't confuse. TRY-CATCH is primarily made to handle errors on server. You can use it to avoid errors for the client or send a custom error. So if you do any DML in your procedure you can surround special parts or the complete body with a TRY-CATCH block to avoid any system messages to be send to client.
Guideline
Do not show system errors to users to avoid security issues. If you send a system error like "Foreign key constraint FK_A violation on table A references to table B" tells any hacker that there is table "A", a table "B" and a foreign key "FK_A" in your database. So (s)he has many start information to hack your system. If you enclose your code with a TRY-CATCH block you can log your error to a logging table and only send "Error while trying to save your information!" the user has no idea what happened.
Same applies to any client side errors. If you get an exception from database determine if the error number is >= 50,000; in this case the error already is an custom error and you can show it to the user. In any other case show a custom error message.
Same for all other client exceptions; you can log the real one but don't show it to the user.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 28, 2009 at 10:40 am
Post it like this, Florian!
class Program
{
static void Main(string[] args)
{
using (SqlConnection cn = new SqlConnection("Server=(local);Database=Sandbox;Integrated Security=SSPI"))
{
cn.Open();
// The info message handler can be used for PRINT messages from server
cn.InfoMessage += new SqlInfoMessageEventHandler(cn_InfoMessage);
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
// Use PRINT
cmd.CommandText = "PRINT 'Hello world'";
cmd.ExecuteNonQuery();
Console.WriteLine();
Console.WriteLine();
try
{
// Use RAISERROR
cmd.CommandText = "RAISERROR ('Error because of %d flying cows at %s', 11, 1, 2, 'Oktoberfest')";
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine("####################################################");
Console.WriteLine("# A database error was raised!");
Console.WriteLine("#");
Console.WriteLine("# Please see following lines for further information");
Console.WriteLine("#");
Console.WriteLine("# ------------------------------------");
Console.WriteLine("# .NET EXCEPTION");
Console.WriteLine("#");
Console.WriteLine("# Message: {0}", ex.Message);
Console.WriteLine("# Number: {0}", ex.Number);
Console.WriteLine("# Procedure: {0}", ex.Procedure ?? "<NULL>");
Console.WriteLine("# State: {0}", ex.State);
foreach (SqlError error in ex.Errors)
{
Console.WriteLine("# ---------------------------------");
Console.WriteLine("# SQL ERROR");
Console.WriteLine("#");
Console.WriteLine("# Message: {0}", error.Message);
Console.WriteLine("# Number: {0}", error.Number);
Console.WriteLine("# Procedure: {0}", error.Procedure ?? "<NULL>");
Console.WriteLine("# State: {0}", error.State);
}
Console.WriteLine("#");
Console.WriteLine("####################################################");
//throw;
}
}
cn.InfoMessage -= new SqlInfoMessageEventHandler(cn_InfoMessage);
}
}
static void cn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
Console.WriteLine("PRINT message from SQL Server:");
Console.WriteLine(e.Message);
}
}
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 28, 2009 at 10:44 am
Geek! 😛
How did you format the C# code?
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 28, 2009 at 11:24 am
"Quote" my post and you will see it in the code tag.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 28, 2009 at 11:32 am
Didn't know that other languages are also available.
Thanks!
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply