SQL Server Standard Update

September 30, 2009 at 1:00 pm (PASS, sql server standard, SQLServerPedia Syndication) (, , , )

Just to keep people who might be interested up to date on what’s happening with the SQL Server Standard relaunch. I’ve received some fantastic abstracts on a wide range of topics from a diverse group of SQL Server DBA’s and Developers. From that, we’ve got a lot of articles in the hopper going through technical edits and second drafts. Three articles have made it all the way to copy edit. They should be done soon. I’m positive you’ll see at least one of them, maybe two, before the PASS Summit.

I need even more abstracts so I can publish even more articles. If you meet the qualifications to write for us, please submit an abstract to my PASS email address: grant.fritchey – at – sqlpass -dot- org (unobfuscate the obvious). Any topic that relates to SQL Server, Reporting Services, Integrations Services, Analysis Services, ORM to SQL Server, PowerShell to SQL Server, Monitoring, Tuning, Designing, Coding… There’s an audience out there that want’s to hear from you.

Permalink Leave a Comment

Pre-Compiled Stored Procedures: Fact or Myth

September 30, 2009 at 9:01 am (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , , , , , )

There’s a very common belief among DBA’s: Stored procedures are pre-compiled. I had someone tell me all about it just last week. There are lots of sources that say what I’m getting ready to say, so this really shouldn’t come as news to anyone,but it will. No, they’re not. Stored procedures are not pre-compiled.

When you create or update a stored procedure on the server, it gets a syntax check. But there is no compile process, let alone a pre-compile process. That stored procedure exists exactly as it is named, a procedure that is stored on the server. It’s just a collection of TSQL code, possibly with some parameters, waiting to be called. Until it’s called, it is not ready to be executed. Don’t believe me? I’m not a huge sports fan (except for MMA), but I’m willing to use a common sports expression. Let’s go to the slow motion instant replay, also known as trace events.

I’m going to run each one of the following statements, one at a time and capture a set of trace events, also listed below. First the code:

CREATE PROCEDURE dbo.MyTest
AS
SELECT *
FROM sys.dm_exec_query_stats AS deqs ;
GO
EXEC dbo.MyTest ;
GO
EXEC dbo.MyTest ;
GO
DROP PROCEDURE dbo.MyTest ;
GO

The events I’m capture are:

  • SQL:BatchCompleted
  • SP:CacheMiss
  • SP:CacheHit
  • SP:CacheInsert
  • SP:CacheRemove

After I run the script, here is what is visible in the trace:

Precompile

With the first statement to create the procedure, you see that there was a CacheMiss event, since the DDL statement isn’t cached. You’ll get a miss event every time this runs. Next, the execute statement runs and you can see another CacheMiss event followed immediately by a CacheInsert event. This is the creation of the execution plan and it’s addition to the cache. The statement completes and the second execute statement fires. This gets a CacheHit event, since the last execute created a plan, and then it completes. The final statement, again, DDL, so you see a CacheMiss event and then the CacheRemove event as the procedure is dropped.

Nothing was added to the cache until the procedure was called. There was no pre-compile process. None. To quote a popular television show, myth busted.

Permalink 22 Comments

Minor Celebration

September 30, 2009 at 7:36 am (Misc) (, , )

A little cheap thrill for me. I passed 50,000 views on the blog yesterday. Thanks to everyone who has stopped by to read my fumbling attempts to understand SQL Server better. I hope some of the posts have proved useful to you. I learn a lot from putting them together.

Permalink 3 Comments