Set Option causes Stored Procedure Recompilation
I was working on a database performance issue and found that SET options can cause stored procedure recompilation. After knowing this fact a question raised in my mind that, If SET options causes recompilation then why we often use SET options in stored procedure.
Then I tried all applicable SET options in stored procedure and run the SQL profiler by checking the SP:Recompile option, to know whether the SET option is causing recompilation or not.
If Profiler catch the SP:Recompile event it means the stored procedure is recompiled.
Lets Try it,
Step 1: Create a Stored procedure
Create Procedure TestRecompilation
AS
BEGIN
SET ARITHABORT OFF
SELECT ‘SQLCOMMITTED’
END
GO
Step 2: Run the profiler and check the SP: Recompile event. To see the SP:Recompile event check the Show all events check box.
Step 3: Execute the Stored Procedure
exec TestRecompilation
go
Step 4: Check the profiler log. If the SP:Recompile event logged against the Stored Procedure that means the stored procedure recompiled.
Note: Procedure will recompile based on SET option and the value. Say for example if I use SET ARITHABORT ON, it won’t recompile the procedure but SET ARITHABORT OFF will cause procedure recompilation.
Refer link http://msdn.microsoft.com/en-us/library/ms190707.aspx to know more on SET option.
For more detail on procedure recompilation refer http://support.microsoft.com/kb/243586
DDL command with in transaction
I attended the SQL Saturday #116 event on 7th January 2012 at Microsoft Signature Building Bangalore.
I learned a lot from this event. In the event we discussed different features of MS-SQL server.
During the event, like other forum someone asked the same question, Can we rollback a DDL statement? and during the discussion of answer to this question, one answer put me in doubt, that is if the DDL command is within Begin Tran… Rollback, it will rollback the DLL statement and if the DDL command is in between Begin Tran …. Rollback Tran it won’t rollback the DDL statement.
To clear my doubt I tried the below script and found DDL statements can be rolled back in both the cases.
Let’s find it out how it works….
Example 1: Using Begin Tran…Rollback
–Explicitly beginning the Transaction
Begin Tran
Create table Table4 (column1 int)
go
– Check Table is created or not
Select * from INFORMATION_SCHEMA.TABLES
–Rollback the transaction
Rollback
–Check Table is exist or not
Select * from INFORMATION_SCHEMA.TABLES
Output of 1st Select statement: Before Rollback the Table4 is exist
Output of 2nd Select statement. Here we can see the Table4 doesn’t exist
Example 2:Using Begin Tran…Rollback tran
Begin Tran
Create table Result (column1 int)
go
Insert into Result values(1)
select * from Result
Rollback tran
Select * from Result
Output:
(1 row(s) affected)
(1 row(s) affected)
Msg 208, Level 16, State 1, Line 4
Invalid object name ‘Result’.
SQL Server 2008 R2 Database properties – Option Page – Part-1
SQL Server 2008 R2 Database properties – Option Page – Part-1
Not only DBA , I believe anybody working on SQL Server database whether as a SQL Developer or BI Developer all would have seen the below screen.
Using this screen we can set different SQL Server database settings.
In this page there are 4 sections i.e.
1- Collation
2- Recovery Model
3- Compatibility Level
4- Other options
Let’s try to explore different option available in Other Options section.
The Other Options section grouped into 6 categories as :
1- Automatic
2- Cursor
3- Miscellaneous
4- Recovery
5- Service Broker
6- State
In Part-1 we’ll cover Automatic category also called Auto options
These are the options which SQL Server might take it automatically. All attributes under this sections takes Boolean value only ( Either TRUE or FALSE)
AUTO CLOSE :
When this option is set to TRUE, the database is closed and shut down cleanly when the last user of the database exits. In result it will free all resources used by database.
It will reopen again once user tries to use the database. This options mostly used for personal SQL SERVER database as it allow to manage database file as normal files.
If the database is used by an application which connect and disconnect to SQL SERVER repeatedly then it is advisable not to set this option TRUE to avoid over head of closing and reopening the database between each connection.
AUTO CREATE STATISTICS:
Statistics is used by query optimizer to determine how to evaluate a query.
Statistics is a different topic in SQL SERVER , I’ll be writing on Statistics for you all very soon.
By default this option is set to TRUE. If It is TRUE, SQL SERVER automatically creates statistics on columns referenced in a “WHERE” clause or “ON” clause .
It creates statistics on column when any missing statistics needed by the query optimizer.
Adding statistics helps to improve query performance ( How it improve performance?, I’ll be writing on it very soon).
AUTO SHRINK:
If this option is TRUE then both Data files and Log files can be shrunk by SQL Server. File shrinking happened periodically.
To shrink a log file, you have to take the backup the log file or the recovery model set to SIMPLE ( I’ll be writing on Recovery model very soon).
It is not recommended to use this option.
Now a Question for you , When to use AUTO_SHRINK option?
AUTO UPDATE STATISTICS:
By Default this option is set to TRUE. If this option is on existing statistics are updated if the table data has changed.
SQL SERVER maintains a counter of the modification made to a table and it uses this counter to determine when the statistics are outdated.
It updates the statistics when any out of date statistics needed by the query optimizer during query optimization.
AUTO UPDATE STATISTICS ASYNCHRONOUSLY:
If It is set to TRUE, It will not wait for the query which initiate to update the out of date statistics before compiling the query.
Subsequent queries will use the updated statistics when they are available.
If it is set to false, The query which initiate to update the out of date statistics will wait until the statistics is updated. Once the statistics is updated the query will use the updated statistic to prepare the query plan.
Object Name Starts With sp_
Yesterday night I was playinng around with sp_.
When you create a table , view or procedure in master database prefix with sp_ , it is accessible from other user databases without using fully qulify name.
Lets try …..
Use master
Go
Create Table sp_Table1 (C1 Int, C2 char(1))
Go
Insert Into sp_Table1 Values ( 1,‘A’)
Insert Into sp_Table1 Values ( 2,‘B’)
Go
– Go to one of your user database
Use SCOM
Go
Select * from sp_Table1
It will return 2 records.
Lets try something with the procedure name.
When we create a procedure starts with sp_ in user database and run the procedure, SQL Server look for a stored procedure begining with sp_ in following order
1- Master Database
2- It will use fully qualified name to locate the sp
3- If fully qualify name is not given then it will check in current database with dbo as owner
SP name prefix with “sp_” in the userdb, the master database is always checked first, this slow down the performance.
Now a question for you,
Try to answer befor you try the below code
use master
Go
Create Procedure sp_ShowResult
As
Select ‘This is executed from Master Database’
go
Use test
go
Create Procedure sp_ShowResult
AS
Select ‘This is executed from User database’
go
Exec sp_ShowResult
What will be the output?
Happy New Year 2012
Wishing You All a Very Happy and prosperous New Year 2012.
Today I am pleased to publish my website sqlcommitted.com.
In my career I realize the importance of sharing knowledge and Experience. According to me a good book will show you the path towards your goal and gives you detail information but someone’s experience will guide you how to achieve your goal. Whenever I face any technical problem, I always look into blogs first rather than to book to solve it. In my blog I’ll try to put all my experience and knowledge in SQL server which will help SQL server professionals in their job.
Experience comes if you start experiencing it.
I need all of your support to make this site useful and better.
Looking forward to your feedbacks.
About
Sandip Pani is a MSBI/SQL Developer. He holds a Masters of Computer Application degree from IGIT, Sarang, Odisha, India and a number of certifications such as MCTS, MCITP. Befor Joining QSIHealthcare he was working for IBM India Pvt. Ltd and DELL Services. He has passion to contribute to MS-SQL Server community so he is committed to SQL.
You can contact me at s4sql@sqlcommitted.com
SQLcommitted-
Archives
- March 2013 (1)
- January 2013 (3)
- December 2012 (2)
- November 2012 (1)
- August 2012 (4)
- July 2012 (2)
- June 2012 (2)
- May 2012 (1)
- April 2012 (5)
- March 2012 (1)
- February 2012 (1)
- January 2012 (5)
-
Categories
-
RSS
Entries RSS
Comments RSS
