SQL Server 2008 :: How Temp Tables Associated To Deferred Recompilation
May 25, 2015
1) "Deferred compile" recompile event occurs because of deferred name resolution. In other words, an object referred to in the statement does not exist at compile time. Later, when the object does exist, it requires a recompile of the statement so that it can create an optimal execution plan. One example of when a deferred compile will occur is if a temporary table is used in a batch and does not exist when the first statements in the batch are compiled.
Let's say that I have a stored proc that is assigned to a service broker queue and is constantly running while it waits for messages in said queue. When a message comes in on the queue, the stored proc creates a table variable based off of the contents of the message and performs various operations with the data. Since the stored proc is constantly running, do the contents of this table variable ever truly get emptied? Should I be deleting the contents of the table variable at the end of the operation to ensure that stale data doesn't persist?
I've recently started a new position and our production box. Contains a procedure that uses 30 + temp tables. I'm currently not in a position to change this as it's production and I would have to be granted a window to re-design.
However the tempDb is showing some strange activity.
If a table is created #CarrierService (CarrierServiceID,DeliveryZoneID,CollectionZoneID) for example
Once the procedure is called It will appear in the tempDB with the session info appended as expected
However once the session has ended the above table will get dropped and a new one created
#2C78E45A, I now have 7000 of these different Tables in the TempDB
When I Interrogate this using
SELECT o.name, o.create_date,o.modify_date , c.Name,C.Column_Id FROM tempdb.sys.Objects o Inner join tempdb.Sys.Columns c ON o.object_id =c.Object_ID WHERE o.type ='U'
I get the Following results
name create_date modify_date object_id name #2C78E45A26/04/2015 18:0930/04/2015 14:55746120282CarrierServiceId #2C78E45A26/04/2015 18:0930/04/2015 14:55746120282CollectionZoneId #2C78E45A26/04/2015 18:0930/04/2015 14:55746120282DeliveryZoneId
select * from sys.master_files - size column value here is 1024 for .mdf,size here for .ldf is 64 select * from tempdb.sys.database_files - size column value here is 3576 for .mdf,size here for .ldf is 224
Why is there a difference and not the same. size columns in the above 2 tables for temp db's do they represent different values ?
--Exec Database.Employees --Use Database --Go --Create PROCEDURE AEM.TempTable --AS --BEGIN --Select * into #emptemp From Database.Employees --End --Select * From #emptemp
Is something like this possible? I can get the EXEC to run the "Select * into #emptemp From Database.Employees" statement, but when I try to use the temp table it doesnt see it.
do you have a general rule of thumb for breaking a complex query into temp tables? For someone who is not a sql specialist, a query with more than a few table joins can be complex. So a query with 10+ table joins can be overwhelming for someone who is not a sql specialist.
One strategy is to break a problem into pieces so to speak by grouping together closely related tables into temp tables and then joining those temp tables together. This simplifies complex SQL and although not as performant as one big query it's much easier to understand. So do you have a general rule of thumb as far as a threshold for the number of joins you include in a query before you break the query into temp tables?
Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.
However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.
Here is the code that works:SET NOCOUNT ON
CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000)) INSERT ##MyTempTbl values ('Put your long message here.') INSERT ##MyTempTbl values ('Put your second long message here.') INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!') DECLARE @cmd varchar(256) DECLARE @LargestEventSize int DECLARE @Width int, @Msg varchar(128) SELECT @LargestEventSize = Max(Len(MyWords)) FROM ##MyTempTbl
SET @cmd = 'SELECT Cast(MyWords AS varchar(' + CONVERT(varchar(5), @LargestEventSize) + ')) FROM ##MyTempTbl order by SeqNo' SET @Width = @LargestEventSize + 1 SET @Msg = 'Here is the junk you asked about' + CHAR(13) + '----------------------------' EXECUTE Master.dbo.xp_sendmail 'YoMama@WhoKnows.com', @query = @cmd, @no_header= 'TRUE', @width = @Width, @dbuse = 'MyDB', @subject='none of your darn business', @message= @Msg DROP TABLE ##MyTempTbl
The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.
Any insight anyone? Or is BOL just full of...well..."stuff"?
I have a dynamic sql which uses Pivot and returns "technically" variable no. of columns.
Is there a way to store the dynamic sql's output in to a temp table? I don't want to create a temp table with the structure of the output and limit no. of columns hence changing the SP every time I get new Pivot column!!
what are some common techniques for ensuring an isolated temp table scope? For example, if 2 different sprocs happen to crud a temp table with the same name? I'm guessing that big SQL shops establish a standard for this early on to avoid conflicts between sprocs.
Is there any way to defer constriants in sql server 2005? I have found some sites that say use the keyword deferred but that always give me an "Incorrect syntax near 'deferred' "error.
I have a stored proc that creates a global temp table. How can I have multiple users select records from and insert records in that table without overwriting and/or deleting data?
I have an application that I am working on that uses some small temptables. I am considering moving them to Table Variables - Would thisbe a performance enhancement?Some background information: The system I am working on has numeroustables but for this exercise there are only three that really matter.Claim, Transaction and Parties.A Claim can have 0 or more transactions.A Claim can have 1 or more parties.A Transaction can have 1 or more parties.A party can have 1 or more claim.A party can have 1 or more transactions. Parties are really many tomany back to Claim and transaction tables.I have three stored procsinsertClaiminsertTransactioninsertPartiesFrom an xml point of view the data looks like this<claim><parties><info />insertClaim takes 3 sets of paramters - All the claim levelinformation (as individual parameters), All the parties on a claim (asone xml parameter), All the transactions on a claim(As one xmlparameter with Parties as part of the xml)insertClaim calls insertParties and passes in the parties xml -insertParties returns a recordset of the newly inserted records.insertClaim then uses that table to join the claim to the parties. Itthen calls insertTransaction and passes the transaction xml into thatsproc.insertTransaciton then inserts the transactions in the xml, and alsocalls insertParties, passing in the XML snippet
I am trying to create a SQL data adapter via the wizard, however, I get the error "Invalid object name #ords" because the stored procedure uses a temp table. Anyway around this? Thanks.
I am just learning about temp temps using Iteration, how do I clear the contents of the #temp table because when I re-run the query I get the following error:
Msg 2714, Level 16, State 6, Line 6
There is already an object named '#mytemp2' in the database.
I have 3 Checkbox list panels that query the DB for the items. Panel nº 2 and 3 need to know selection on panel nº 1. Panels have multiple item selection. Multiple users may use this at the same time and I wanted to have a full separation between the application and the DB. The ASP.net application always uses Stored Procedures to access the DB. Whats the best course of action? Using a permanent 'temp' table on the SQL server? Accomplish everything on the client side?
[Web application being built on ASP.net 3.5 (IIS7) connected to SQL Server 2005)
I have an Application using MS Access ADP linked to SQL 2005. The problem appears when SP that is supposet to called from MS Access via RPC needs to be recalculated (usually if I've modified it). At that time, If I run the SP from SQL Server Management Studio Queri Window, it runs normally (recompilation succeeds), but the same time I can't run the same SP from Access ADP Command object, execute method causes timeout (regardless timeout parameter). If I modify the SP and simplify it (removing some high cost statements) than Command exec runs without problems, after that I undo the SP to it's begining code and Command exec runs again without problems. I wonder why executing of SP from SQL Management Studio (that recompilates the SP) doesn't helps MS Access ADP Command to run that SP without recompilation.
I need to write create table statements for the er diagram that I attached. I am new to sql and I have trouble integrating foreign keys with these bigger er diagrams.
These are the tables I need to create: Create Table Author(...) Create Table Writes(...) Create Table Book(...) Create Table Copy(...) Create Table Loan(...) Create Table Customer(...)
Hello,We have an ADO.NET application using .NET version 1.1.4322 SP1. It iscalling stored procedures in a database that it never written to. Theonly thing the stored procedures do is a select statement on a fewtables that are joined. In the last few weeks we have experiencedissues where, two or three of these stored procedures consistentlytimeout. When we recompile the stored procedures, the problem goesaway for a period of time (anywhere between a few hours and a fewdays), then it reoccurs with the same two or three stored procedures.We have tried running the stored procedures with the same parametersagainst the same server using query analyzer with the same user andconnection settings while we are having the timeout issues, and theyperformed normally (in the 5 second range). We have also put thedatabase into "read only" mode, which has improved performanceduring normal times, but the issue continues to occur.Our trace data shows that during the timeouts periods, the storedprocedures perform the same number of reads and writes, and using thesame amount of CPU as during normal times, but the duration increasesfrom 5-6 seconds to 30 seconds (when the server receives the timeoutrequest).We have found no blocking on any of the tables (they are, after all,read only).Finally, we compared execution plans for the stored procedures when wehad timeouts to right after we recompile and alleviate the issue, andthe plans are identical.What could be causing this problem? Does recompiling a storedprocedure affect the Sql Server .Net Data provider?Thanks in advance
A stored procedure in the cache is automatically recompiled when a table it refers to has a table structure change. User defined functions are not. Here's a simplified code sample:
set nocount on go
create table tmpTest (a int, b int, c int)
insert into tmpTest (a, b, c) values (1, 2, 3) insert into tmpTest (a, b, c) values (2, 3, 4) go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fTest]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[fTest] GO
CREATE FUNCTION dbo.fTest (@a int) RETURNS TABLE AS RETURN (SELECT * from tmpTest where a = @a) GO
select * from fTest(1)
CREATE TABLE dbo.Tmp_tmpTest ( a int NULL, b int NULL, d int NULL, c int NULL ) ON [PRIMARY] IF EXISTS(SELECT * FROM dbo.tmpTest) EXEC('INSERT INTO dbo.Tmp_tmpTest (a, b, c) SELECT a, b, c FROM dbo.tmpTest TABLOCKX') DROP TABLE dbo.tmpTest EXECUTE sp_rename N'dbo.Tmp_tmpTest', N'tmpTest', 'OBJECT'
select * from fTest(1)
drop table tmpTest
Running it, the output is:
a b c ----------- ----------- ----------- 1 2 3
Caution: Changing any part of an object name could break scripts and stored procedures. The OBJECT was renamed to 'tmpTest'. a b c ----------- ----------- ----------- 1 2 NULL
(I know that "select *" is bad, but it's a lot of legacy code that I'm working with here, and that's how it's written.)
The function doesn't detect that the table has changed in structure, or even that there is no longer a dependency on tmpTest. (Appending a column rather than inserting has the same effect, in that only the first 3 columns are returned.)
DBCC FREEPROCCACHE has no effect, not that I really expected it to, but you never know...
Is there any way, other than dropping and recreating, to force a recompilation of a particular function in memory, or perhaps all functions?
I am new in SQL and i need do a query where I need sum values from 2 tables, when i do it the Sum values are not correct. this is my query
SELECT D.Line AS Line, D.ProductionLine AS ProductionLine, D.Shift AS Shift, SUM(CAST(D.DownTime AS INT)) AS DownTime, R.Category, SUM(Cast(R.Downtime AS INT)) AS AssignedDowntime, CONVERT(VARCHAR(10), D.DatePacked,101) AS DatePacked FROM Production.DownTimeReason R left JOIN Production.DownTimeHistory D
MS SQL 2008.I want to execute a delete query on certain tables in my database to delete some rows in the tables.The tables selected has a certain name pattern (the name ends with "Temp").
So I can do this to get a list of the table names
SELECT name FROM sys.Tables where name like '%Temp'
Now I want to check each table to see if it has a column with the name "DateStamp" and then execute a delete query as follows:
delete form TableName where DateStamp is < '2010-01-01'
In other words I need to iterate through the tables names, How to do this?
I have a query, I am trying to update a certain column in my query you can see that is hard coded. The column that I am trying to update is "O_Test" I used a select statement trying to figure out how many records that accounts for with the entire database of all the tables. I got 643 records. So I am wondering if there is a way I can update all the columns without looking up each table and updating each one. This Update statement wont work because I am accounting for all records in the DB of all tables associated of what I hard coded
SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name LIKE '%O_Test%' ORDER BY schema_name, table_name;
I am trying to find a way where I can search for a column that is associated in all tables of the database. I have created a query but is not executing correctly.
SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name FROM sys.tables AS t8 INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name LIKE '%Status%' ORDER BY schema_name, table_name;
In our Production db we have all most all tables have the column created or createdate.
I need to find out all tables without the created or createdate column
SELECT t8.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name FROM sys.tables AS t8 INNER JOIN sys.columns c ON t8.OBJECT_ID = c.OBJECT_ID
I have a table of raw data with supplier names, and i need to join it to our supplier database and pull the supplier numbers.
The issue is that the raw data does not match our database entries for these suppliers; sometimes there are extra periods, commas, or abbreviations (i.e. FedEx, FederalExpress, FedEx, inc.) etc. I'm trying to create a query that will search for entries that are similar.
I tried setting a variable to be equal to the raw data field, and then using a LIKE '%@Variable%' to try and return anything that would contain it, but it didnt return any rows.
Below I have a query which list the relations (constraints) between tables.
I want to list all the relations which are visible in the Database Diagrams.
The list is not complete. How do I get a complete list ?
-- -- Query to show the relations (constraints) between tables. -- This does not show the complete list -- SELECT A.constraint_name, B.table_name AS Child, C.table_name AS Parent,
Ok, here's my dilemma. We're running SQL Server 2000 with the defaultdb setting for the ANSI_WARNINGS option set to off. However, we stillget "Warning: Null value is eliminated by an aggregate or other SEToperation" messages. To eliminate getting the message, we use the SETANSI_WARNINGS OFF in our stored procs. Using that SET command forces arecompilation of the SP and is causing locks/blocking. Anybody elserun into this problem and if so, how have you handled it.Thanks,-Nate
