The KEEPFIXED PLAN query hint is used in order to retain the original execution plan used to initially compile a statement. It is typically used to disallow a recompile, especially in regards to a statement that utilizes temp table or a table that changes it’s percentage of records frequently resulting in triggering a recompile for a statement. To use, specify OPTION (KEEPFIXED PLAN) immediately after the statement that you do not want to recompile. [cc lang=”sql”] SELECT * FROM #Temp OPTION (KEEPFIXED PLAN) [/cc] Temp tables frequently recompile due to the change in statistics. When using temp tables within stored procedures, this can be a disadvantage. To get around the recompile, either use table variables (indexed with constraints) or use the KEEPFIXED PLAN query hint. Recompiles typically happen when the percentage of a tables (or temp tables) rows change by 500 and the cardinality (or uniqueness) changes by 20%.
Continue reading ...
The MAXDOP query hint is used to implicitly specify the maximum degree of parallelism to use for a specific statement. The degree of parallelism is roughly defined as how many separate processors to utilize for a single query. To set this, use the following convention: [cc lang=”sql”] SELECT * FROM Sales.SalesOrderDetail OPTION (MAXDOP 1) [/cc] This will force only one instance of the SPID to be spawned. To allow more instances of a SPID to be spawned replace the 1 with the maximum number. This needs to be specified after each statement. It is not specific to the statement. As with all query hints, it is recommended that you know internally what you are controlling by specifying this hint. While this specific hint is relatively harmless if you specify “1” it is still recommended you understand it’s effects.
Continue reading ...
Parallelism is a powerful feature in SQL Server designed to allow greater bandwidth for high impact queries. Most of the time however, I’ve seen too many queries qualifying for parallelism and essentially bringing the SQL Server box to it’s knees. To understand parallelism, we need to understand SPIDs (Server Process ID’s). These SPID’s act essentially as threads on a SQL Server. Normally there is one SPID assigned to each database connection. Sometimes, if the query is determined to be expensive, a SPID can be split out into multiple execution threads. This is known as parallelism. Cost Threshold for Parallelism Prior to breaking a SPID out into multiple threads, there is a cost determination. This is called the cost threshold for parallelism (set in seconds) and is set at the server-level. The idea is to allow parallelism when a query is estimated to run over a certain number of seconds. Generally, the determining factor is the estimated number of rows that will be returned either in the main query or an inner query within the execution plan. To set parallelism right click on the Server Instance in Management Studio. And select “properties”. Once properties is selected, a new window pops up displaying a more detailed server settings. We want to select on “Advanced” Now, in the lower middle of the screen we see the settings for Parallelism. The main two we are concerned with the is the cost threshold and the Max Degree of parallelism. What Settings to Choose? You need […]
Continue reading ...
If you’ve stumbled upon this post, then you’re likely at a point where you’ve got a crap query that is slowing you down. This article tells you how to find that one slow query, within a procedure. You also probably know that the execution plan is what you need to use to find out how to fix it, so let’s set that up. First, enable the execution plan: From the SSMS menu, select: Query » Include Actual Execution Plan Ok, so there you’ve enabled it for the session (or window). It is going to be enabled until you close the window or turn it off. Now let’s execute this procedure and see what is slow. [cc lang=”sql”] EXEC dbo.MyCrappyProcedure [/cc] Ahh.. so now that I’ve executed the procedure and it completed, I see this new weird tab on the output pane labeled “Execution Plan”. Select on this new tab. You should see something like this: Now let’s look at each section that states: Query x: Query cost (relative to the batch): x% Hmm.. so if the entire procedure equals 100%, then each of these individual SQL statements shown equal the other x% that is shown? Yep, that’s how it works; on paper at least.. The cost is somehow calculated by SQL Server by probably combining resource usage (CPU, IO, Time etc.) So that’s how you find the most offending SQL Statement. Now in order to begin the troubleshooting it you’ll have to look at the execution plan for that statement.
Continue reading ...
Find the most costly statements Determine why the statement is costly Get an accurate baseline for the procedure Optimize Find the Most Costly Statments There are two main ways to determine costly queries. One is to execute the procedure including an Actual Execution Plan. The other is to get a time difference of a before and after for each statement. The are also other ways (including IO usage and CPU usage but we won’t cover those here). I like to start with the execution plan method. Though this is not the most precise method, it is the easiest.. so let’s start there. Here is how to find the slowest query in a procedure using the execution plan. Determine why the statement is costly This can be them most difficult task. With the use of the execution plan, we can help deduce the most common issues. Now that we’ve found the statement, we need to drill down even further to find out what is causing the statement to be slow. This is done by looking at the cost for each operation in the execution plan. Under each operation, you will see a “cost: x%”. You want to find the operation with the highest percentage and focus on that. When you do find it, many times it will be one of the following operations: Interpreting the Execution Plan Symptom Cause(s) example Table scan, index scan, clustered index scan Missing or improper Indexes, cardinality < 5% thick lines (arrows) from one operation to […]
Continue reading ...
