Some Hidden Facts: Stored Procedure and Its Optimization. We create stored procedures in database applications for several reasons and benefits like enhanced performance, security, code maintenance, etc. But with time, we may see that these stored procedures are not performing well as expected. The SQL Create Procedure tool included in RazorSQL allows users to create stored procedures visually. Razor SQL Query, Edit, Browse, and Manage Databases. The first time a stored procedure executes, SQL Server loads the SQL code for the stored procedure from the system catalog into the plan cache and optimizes and compiles an execution plan. The optimization of SQL statements is based on the parameters. There can be many reasons like dependent objects (tables, indexes, execution plans, and data size) changed or stored procedures executing improperly. So we have to be a little bit careful from the beginning of creating stored procedures to execution. I would like to summarize a few points about all these things.
A. Stored Procedure Recompilation. When creating a stored procedure, we can specify WITH RECOMPILE option in it. But such stored procedure will never benefit from the cached execution plan as each time it is executed; it forces the cached execution plan to invalidate or flush and create a new plan based upon the parameters passed, if any, to it. ![]() I do not see any such big benefits of this option. But one may find this useful when such stored procedure will return results or execute only selective part of the stored procedure body based upon supplied input parameters. For example, statements within If- block or Select- Case block based upon input parameters. But I still feel one should go after the new feature of SQL Server 2. But this option will be quite useful as such recompilation is dependent upon input data. To use this method, one has to specify that SQL- statement within stored procedure with RECOMPILE query hint. For example: CREATEPROC dbo. Example. @x. This procedure forces recompile of user defined stored procedure next time it is run. Let. If this object name is some stored procedure, then this stored procedure will recompile next time it is run. Skip to Navigation Skip to Content SQL Server Pro Search: Register Log In Display name. The primary monitoring tools you will use for SQL Server are the Reliability and Performance Monitor and the SQL Server Profiler. But there are other resources also available for monitoring SQL Server. These include: Activity Monitor This monitor provides information. Stored Procedures Create a Stored Procedure Create a Stored Procedure Create a Stored Procedure Create a Stored Procedure Modify a Stored Procedure. This option is good when the table properties are changed, and this table is in use of many other stored procedures. Instead of recompiling each and every such depending stored procedure, a simple sp. If server is running out of memory, the execution cache will get flushed. If the stored procedure has some session specific SET modifiers (like LOCK TIMEOUT, DATEFIRST, ANSI. For example, most of us create temporary tables inside stored procedure. ![]() Then, we do DML operations based upon values in these temporary tables. Even this type of stored procedure is forced to recompile to create a new plan according to such new temporary tables. For this type of stored procedure, one should opt for statement- level recompilation by using RECOMPILE hint in that DML statement following DDL statements. But what is the alternative to temporary tables here? We can use table variables instead! Or, if we cannot do without temporary tables, then we should write such DDL statements in the beginning of stored procedure body itself so that multiple recompilations are not happening for a single call to this stored procedure. So stored procedure recompilation can be harmful and useful both depending upon situations. One has to think twice whether recompilation is required or not. B. Stored Procedure Name. ![]() We should always create a stored procedure with a full naming convention. Schema name should always prefix the stored procedure name. Schema name will help SQL Server name resolution easily when it is called. This helps SQL Server in which schema to query the stored procedure. C. Table Indexes. Tables should have proper indexes and should be compiled time to time as indexes may be weird off after some time due to huge data insertion or deletion. This is all about some useful facts regarding stored procedures. ![]() ![]() Code. Project. Posted in Code. Project, Sql Server Tagged: Query Optimization Techniques, Stored Procedure Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
October 2017
Categories |