-----------------------------------------------------------
New Message on BDOTNET
-----------------------------------------------------------
From: Jayant_Magic
Message 2 in Discussion
Hi rambab, With the use of SQL Profiler, it is very easy for a DBA or
developer to determine if a stored procedure abnormally recompiles before it is
ever put into a test or production environment. It is also very easy for
production DBAs to determine if they have a problem with stored procedures
already in production. To determine if you have a problem with existing stored
procedures or a specific stored procedure: Start Profiler. Start a new trace.
Connect to your server. On the General Tab, specify the trace a name. On the
Events Tab, remove all default events and add SP:Recompile, SP:Starting, and
SP:Completed under Stored Procedure events. If you want to determine the
statement that causes the recompile also add SP:StmtStarting and
SP:StmtCompleted to the selection. You can leave the data columns as is or
change them as you see fit. You can also leave the trace without filters, but
stored procedures run by replication may tend to clutter your trace. If you are
tracing only one stored procedure, you can filter by the stored procedure name
under the Text-Like filter.
Following is the example which tests the performance of sql stored procedure In
this example, the test automation feeds employee IDs to the stored procedure
programmatically, grabs the actual status code return value, and compares it
with an expected value to determine if the test case passes or fails. Results
are written to a text file. We only have six test cases here; in a production
environment you would likely have many thousands of cases, of course. Behind
the scenes, a SQL database named dbProseware contains a table of employee
information named tblEmployees. The example application calls a stored
procedure usp_empStatusCode by first connecting to the database and preparing
the stored procedure call, as shown in the following code snippet: string
connString =
"server=(local);database=dbProseware;Integrated Security=SSPI";
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlCommand cmd = new SqlCommand("usp_empStatusCode", conn);
cmd.CommandType = CommandType.StoredProcedure; Then the application sets up the
stored procedure return value and the single input parameter:
SqlParameter p=cmd.Parameters.Add("ret_val", SqlDbType.Int, 1);
p.Direction = ParameterDirection.ReturnValue; p=cmd.Parameters.Add("@empID",
SqlDbType.Char, 3);
p.Direction = ParameterDirection.Input;
p.Value = textBox1.Text; And finally the app calls the stored procedure, gets
the return value, and displays it in the application, as shown here:
cmd.ExecuteNonQuery();
int code =
(int)cmd.Parameters["ret_val"].Value;
textBox2.Text = code.ToString();
Even though using stored procedures in an application sometimes requires some
extra programming effort, stored procedures are less susceptible to script
injection attacks, often provide better performance, allow for more
sophisticated multi-statement logic, and can frequently create better program
structure. Manually testing this stored procedure through the application's UI
would be extremely tedious, time consuming, and error prone. And every time
there was a change in the product code, you'd have to test all over again. So
let's test the stored procedure by programmatically sending test case data to
it and examining the return values as shown in Figure 2. Let me emphasize that
we are not testing the entire system; we are just testing the stored procedure
component of the application. Automating the Automation Now that our database
creation script, automation preparation script, and automation run script have
been written, we can execute them in sequence from within the Query Analyzer
program. Another option is to write a short BAT file that issues osql.exe
commands to run the three scripts: @echo off
rem runTests.bat osql -S(local) -E -i makeDbProseware.sql -n > nul
osql -S(local) -E -i prepTestAuto.sql -n > nul
osql -S(local) -E -i runTestAuto.sql -n > nul echo.
echo Test run complete
echo.
The -E switch makes osql request a Windows Authentication connection using your
current Windows login, and is the most secure option for connecting to an
instance of SQL Server. The -n switch suppresses line-numbering output and I
also suppress miscellaneous messages by redirecting output, with > nul to
create a tidier output display. Of course, in a production environment you will
want to see all the information you can get. After we construct a BAT file such
as the one shown previously, we can easily schedule the test scripts to
automatically start by using the Windows Scheduler or by using the Jobs feature
of SQL Server Agent, which we can easily set up using Enterprise Manager.
For more you can visit the gfollowing link:
http://msdn.microsoft.com/msdnmag/issues/04/09/TestRun/#S1
http://www.sql-server-performance.com/articles/per/optimizing_sp_recompiles_p1.aspx
http://www.sql.ru/photos/Tech-Ed03/PPT/DBA322.ppt#456,12,EXECUTE WITH
RECOMPILE Hope all this solves all youe issue. Regards
Jayant
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
http://groups.msn.com/BDotNet/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member
Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you
received this message by mistake, please click the "Remove" link below. On the
pre-addressed e-mail message that opens, simply click "Send". Your e-mail
address will be deleted from this group's mailing list.
mailto:[EMAIL PROTECTED]
