What happens when you issue a query to SQL Server.
#1 SQL Server is going to parse the syntax of your query.
SQL Server is going to check to see if your T-SQL that you’re submitting is valid and that all of the objects exist. This is a basic check just to make sure all of the objects that are referenced in your query exist and you have valid syntax.
#2 Check its query plan cache
Query plan cache is where SQL Server stores execution plans that have been generated already. It’s a very expensive process from a performance perspective to generate an execution plan. So if SQL Server has already generated a plan, it’s going to try to re-use that plan.
If no plans in cache, it’s going to generate a plan that will be kind-of a road map that will show how the data is retrieved and what various operations it needs to perform to retrieve the data to service your query.
Execution Plans
The optimizer creates a series of operators and these are things like index scans, index seeks, table seeks, table scans, join operators, various joins like merge, nested loops and hash that bring together your data to service your query.
Statistics
The other important thing is that you need to have statistics on your data. The optimizer is only as good at generating a plan as your statistics are. When your statistics are up to date, it gives the optimizer the best opportunity to generate a plan.
Execution Plan Cache
The SQL Server optimizer generates a number of plans when it generates one for your query, it then selects the best plan. So, computationally, this is very expensive. So it’s a major performance benefit to cache those execution plans and then re-use them.
These plans are all stored by the database engine in that cache in some dynamic management views, and the query store, if you’re using SQL Server 2016 or higher, and those plans are all stored as XML, which makes them queryable.
Compiling plans consumes a lot of CPU. So what SQL Server does in order to reduce this resource consumption is stores compiled execution plans in a special area of memory known as the Plan Cache. This allows plans for the same query text to be reused time and time again, which will reduce the overall work load on a server.
Cost
The first time you execute a query, assuming it’s not in the cache, being the first time you’ve run the query, the optimizer generates several plans and you’ll be able to see this in your plan. And it chooses the one with the lowest cost.
The cost is a measure of how much cpu, IO, and memory required to service that given query.You’ll notice the cost actually has a score, and that’s related to the number of seconds that a query would take to complete.
One metric you can check to see if you have a problem with this is recompilations per second. This is a metric of how frequently SQL Server is recompiling queries and generating new plans. If that’s happening too frequently, you may want to consider enabling the setting Optimize for Ad Hoc Queries, which is a database level setting that you can choose. Or you may also want to consider adding more memory to your server, ’cause it could be an indication of memory pressure.
Stored Procedure
With stored procedures, our plans are cached, so even if we’re calling a stored procedure with 20 different parameters, SQL Server is going to store the execution plan with the execution parameter, so even when that parameter has a different value the plan cache is going to see that as one query. That means we’re going to reuse the same plan every time. That’s going to give us overall better performance and consistency.
Comments