A SQL on Call client asked me what a stored procedure recompile was and the more I thought about it I figured I would toss it up here on the blog, at the same time I would use the example from my last posting since I already have that typed out.
So what is a recompile, well the first think that you have to understand is what a compile is. A compile is when the SQL Server Engine take a look at code that it has been passed and does a few things to it. It will validate that it is a valid SQL Statement, and that all the objects exists, it will then go through and look to see what is the best way to execute such a statement. A simple example of this would be if you were searching though a database looking to see someone with the last name of ‘Jones’ was in the database. The engine would look to see if that column existed, and then would check to see if there was an index on the filed. It will also check to see if there are some statistics that surround it that it may be able to help. There is a lot more that goes on during a compile but to be honest I am not an expert at the internals of SQL Server.
Where compiles can almost be removed is by creating a stored procedure. This way when you create the stored procedure and then the first time it executes it the stored procedure will compile for the first time. Now the stored procedure has a good idea on how to execute that query.
So if we have our code in a stored procedure and it has already been compiled one, then what is a recompile? A re-compile occurs when the execution plan for the stored procedure has changed. Or if the query inside has changed. Lets look at the example of my ramblings from last week.
About 10 or 15 years ago (I don’t know exactly), I was working on creating a table that had every company in the US in it. This sounds pretty simple doesn’t it? The creation of the table was easy but then I got to the part of writing queries on it. But that is a different post for a different day. The point that I am trying to make is that I sat there for hours trying to figure out all the different ways that I could create this table that would store 16 million records. Some of the ways that I was thinking about was to:
Split the 1 table into many tables and have one table for each State. That way when I ran a query on it I could just look at the State and go right to the table (By the way this was way before table partitions in SQL Server).
Now if I had created the table like this and I wanted to find a company inside one of those 50 tables then I would have to write a stored procedure that looked something like this.
Create Procedure Find_Company
@State char (2)
@company char (50)
If @state = ‘CA’
Select CompanyName from CA
Where Companyname like @company
If @State = ‘AZ’
Select CompanyName from AZ
Where Companyname like @company
And the flow would remain the same until I had all 50 states covered.
Here is where the recompile problem comes into play. Let’s say the stored procedure has been created already and is ready for the first execution of it. The stored procedure is called with the @State = CA. It compiles knowing that it needs to look at the CA table.
But if the second execution is called with @state = AZ the first compile has no relevance to the query plan. So the engine has to now recompile the stored procedure. In the example that I gave this is a stored procedure that would have been used for a phone directory. So it was one of the most common called queries. A procedure like this would cause your processors to work overtime recompiling the procedure.
The best way to fix a problem with this example (and this is not a great example), would be to evaluate the state Code in the html and them have a proc called that was designed just for that State.