I’m writing this out of a revelation that when querying to see if a particular value is NOT IN a column that contains NULLS, you will not get the correct result set back. This is somewhat distressful because you would expect when a specific value is not in the result set, then the query would return so. Well, unfortunately that’s not the case. Let’s look at an example: [cc lang=”sql”] — Insert two values (null and 1) SELECT VAL INTO #HASNULL FROM ( SELECT VAL = NULL UNION SELECT VAL = 1 ) t SELECT 1 WHERE 5 NOT IN (SELECT VAL FROM #HASNULL) [/cc] From above, we see that we create a table named #HASNULL and insert two values, a one and a null. So when we query to see if the value 5 is in the result set, it should return a 1. But it does not. A little scary. While there may be a logistical reason for this, I’m not able to see it. Let’s just make sure that the null is the problem. So, let’s delete the NULL and re-query: [cc lang=”sql”] DELETE FROM #HASNULL WHERE VAL IS NULL SELECT 1 WHERE 5 NOT IN (SELECT VAL FROM #HASNULL) [/cc] There we have it. Now we see that a 1 is returned. This brings me to my next point. Use Exists when checking for existence. [cc lang=”sql”] SELECT 1 WHERE NOT EXISTS ( SELECT 1 FROM #HASNULL WHERE VAL = 5 ) [/cc] As most developers […]
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 ...
The DBCC FREEPROCCACHE command clears the procedure cache for all the procedures on the server. This command should be used with caution, however in most circumstances it will not bring a server to its knees. In order to execute in SQL 2005+, you need to have the server-level permissions of ALTER SERVER STATE. To use it, simple execute this: [cc lang=”sql”] DBCC FREEPROCCACHE [/cc] From a production standpoint, this command is sometimes used when it has been determined that execution plans have gone bad. This is typically a rare event and can be determined by comparing the baseline duration of commonly executed procedures. Usually if one procedure is determined to be bad a better thing to execute is sp_recompile. From a development standpoint, this command is used in order to clear the procedure cache in order to get a baseline for the execution time of statements or stored procedures. In SQL Server 2008, the command can now accept a SQL Handle, Plan Handle, or a Resource Governor Pool Number, allowing you to isolate what cache you are clearing. [cc lang=”sql”] DBCC FREEPROCCACHE (0x03000500462D954470E2FF00BE9D00000100000000000000) [/cc]
Continue reading ...
The SET STATISTICS TIME command is used in order to identify parse, compile, and execution times that occur on the server-side. It is important to note, these times do not include the time it takes to transfer the data to the client. In order to turn on, simple execute the statement: [cc lang=”sql”] SET STATISTICS TIME ON [/cc] Once the statement is executed, it is turned on for the entire session, and will stay on until it is turned off. (In order to turn it off, replace the ON keyword above with OFF). After the statement is turned on and queries are executed, the parse, compile & execution times are displayed in the messages tab next to the results tab of the query results. Here is an example: For the results above, we first see two “SQL Server Parse and Compile times”. For both of them in this example they are 0. The first result, relates to how long it took to compile the actual “EXEC” statement. The second parse and compile relates to how long it took to compile the entire procedure. Since it is 0, this means the stored procedure is using an existing execution plan, meaning it has already been compiled. The next two lines relate to the two statements that exist in the stored procedure. The first took 123 ms, while the next took 210 ms. The last statement is the overall time it took to execute the procedure. Overall, the SET STATISTICS TIME statement is […]
Continue reading ...
