Happy New Year

I think that is my first blog which does not relate to SQL Server,though why not?
I wish to SQL Server Consultants lots of contracts to help out businesses in 2009, I wish to SQL Server DBAs lots of interesting work but that their databases will run smoothly,I wish to SQL Server Developers to write efficient queries and resolve challenges and to every body and their families in 2009 I wish you health ,happiness,wealth and if you look forward to a good year ahead, spread happiness with these wonderful New Year wishes.

Do you always write parameterized query?

As you know SQL Server creates an execution plan for the query/stored procedure and stores it in procedure cache in order to reuse it when you run the query next time.
But more and more execution plans in memory will also hurt the performance as we do not have a control the procedure cache's size so what would you do? Lets see the below scenario I've ran on the DEV(DO NOT run on Production) machine. First of all I used DBCC FREEPROCCACHE which clears the procedure cache and causes ad hoc queries to
be recompiled. Next I ran actually the same query with different parameters.

DBCC FREEPROCCACHE

SELECT * FROM Production.WorkOrder
WHERE ProductID=522

SELECT * FROM Production.WorkOrder
WHERE ProductID=737

DECLARE @i INT
set @i = 518
SELECT *
FROM Production.WorkOrder
WHERE ProductID = @i
GO
--See how many plans SQL Server created
SELECT stats.execution_count AS exec_count,
p.size_in_bytes as [size],
[sql].[text] as [plan_text]
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle

/*
1 49152 DECLARE @i INT set @i = 518 SELECT * FROM Production.WorkOrder WHERE ProductID = @i
1 24576 SELECT * FROM Production.WorkOrder WHERE ProductID=737
1 24576 SELECT * FROM Production.WorkOrder WHERE ProductID=522
*/
As you see SQL Server created THREE execution plans.
Now let's wrap the query within a stored procedure and see what will be happened.

CREATE PROCEDURE spTest
@i INT
AS
SELECT *
FROM Production.WorkOrder
WHERE ProductID = @i
GO

EXEC spTest 522
EXEC spTest 737
EXEC spTest 518

--See how many plans SQL Server created
SELECT stats.execution_count AS exec_count,
p.size_in_bytes as [size],
[sql].[text] as [plan_text]
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle

/*
3 40960 CREATE PROCEDURE spTest @i INT AS SELECT * FROM Production.WorkOrder WHERE ProductID = @i
*/
Wow, you we get single execution plan that SQL Server used three times.
I also recommend you to read Tony's blog about the subject
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/07/07/procedure-cache-tuning-sizing-from-1gbyte-to-768kbytes-increase-the-size-of-usable-data-cache.aspx

Tony writes in his blog and I'm completely agree with him.
"Surprised? It's one of the reasons DBA's keep bleating on about using stored procedures, it forces the mindset to use the procedure cache more effectively. By parameterising, but preferably using stored procedures we get plan reuse which means a) less compiles thereby reducing CPU load, b) more available pages for the data cache thereby reducing physical disk IO and c) DBA’s are always right ;)."


PS.Besides using stored procedures we can use sp_executesql with parameters as below

EXEC sp_executesql N'SELECT SUM(ProductID) AS ProductTotal
FROM Production.WorkOrder
WHERE ProductID= @ProductID', N'@ProductID INT', 722

EXEC sp_executesql N'SELECT SUM(ProductID) AS ProductTotal
FROM Production.WorkOrder
WHERE ProductID= @ProductID', N'@ProductID INT', 522

.....

GO

When constraint remains in system table even the table is no longer exists

Ok, I'm talking about temporary tables and yes,it could happen to you even you think that the temporary table is no longer exists. Consider the below script where stored procedure inserts the data with wrong type which caused the error(245conversion error). As you propably know we would never name the constraints for local temporary tables (as opposite to permanent tables), because if we run the following statement from two connections we get something like that.
/*
Msg 2714, Level 16, State 4, Line 1
There is already an object named 'PK_tmp1' in the database
*/


USE master
GO
ALTER PROCEDURE spSample
AS
CREATE TABLE #tmp(
col int --PRIMARY KEY NONCLUSTERED--,
CONSTRAINT PK_tmp1 PRIMARY KEY NONCLUSTERED
(
col
)
)
BEGIN TRY
INSERT INTO #tmp SELECT 'A'
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER()
END CATCH

--Usage
USE master
EXEC spSample


USE tempdb
SELECT * FROM sys.objects
WHERE OBJECT_NAME(OBJECT_ID)like '%#tmp%'

Try add DROP TABLE #tmp in BEGIN CATCH ..block and then SQL Server deletes the constraint as well.