Today i stumbled upon an interesting performance problem with a stored procedure running on Sql Server 2005 SP2 in a db running on compatible level of 80 (SQL2000). The proc runs about 8 Minutes and the execution plan shows the usage of an index with an actual row count of 1.339.241.423 which is about factor 1000 higher than the "real"...
Here is the situation: I have a table value function with a datetime parameter ,lest's say tdf(p_date) , that filters about two million rows selecting those with column date smaller than p_date and computes some aggregate values on other columns. It works great but if p_date is a custom scalar value function (returning the end of day in ...
We have a query that is taking around 5 sec on our production system, but on our mirror system (as identical as possible to production) and dev systems it takes under 1 second. We have checked out the query plans and we can see that they differ. Also from these plans we can see why one is taking longer than the other. The data, schame ...
I have a stored proc that processes a large amount of data (about 5m rows in this example). The performance varies wildly. I've had the process running in as little as 15 minutes and seen it run for as long as 4 hours. For maintenance, and in order to verify that the logic and processing is correct, we have the SP broken up into secti...
I'm looking for a good, well-written and explained guide on reading and comparing execution plans in SQL Server. This is for more my own edification as well as for sharing with others. If you want to add your own summary here that would be welcome, though I expect the topic is large enough that links to pre-written articles will be req...
On SQL Server 2005, I have a complex multi-level allocation process which looks like this (pseudo-SQL): FOR EACH @LVL_NUM < @MAX_LVL: INSERT INTO ALLOCS SELECT 'OUT', * FROM BALANCES(@LVL_NUM) INNER JOIN ALLOCN_SUMRY(@LVL_NUM) INSERT INTO ALLOCS SELECT 'IN', * FROM BALANCES(@LVL_NUM) INNER JOIN ALLOCNS(@LVL_NUM) INNER JOIN ALLOCN_SUMRY...
I have a table with two indices; one is a multi-column clustered index, on a 3 columns: ( symbolid int16, bartime int32, typeid int8 ) The second is non clustered on ( bartime int16 ) The select statement i'm trying to run is: SELECT symbolID, vTrdBuy FROM mvTrdHidUhd WHERE typeID = 1 AND barDateTime...
Hi I was playing around with SQL Queries and this one came to my mind - select x,y from table T. Here x and y can be arithmetic expressions like 2.0/5.0 OR 1+2, the result has the number of rows of table T, with the first column having value 2.0/5.0 i.e. 0.444444 and the second column having value 3. I am curious to know as to how thi...
Our sites are getting pounded pretty hard so we're taking a look into optimizing some of our existing queries. While looking into this we ran across several queries whose execution plan was about 4-5 times faster when a simple reference of the clustered index is in the query... for example If this was the old query: SELECT ... FROM my...
I have an execution plan for a fairly complex join which shows an index seek being performed on a table with the "Actual Number of Rows" reading ~70,000, when there are in fact only ~600 rows in the table in total (the estimated number of rows is only 127). Note that all of the statistics are up to date and the input parameters to the q...
I have a stored procedure that accepts a date input that is later set to the current date if no value is passed in: CREATE PROCEDURE MyProc @MyDate DATETIME = NULL AS IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP -- Do Something using @MyDate I'm having problems whereby if @MyDate is passed in as NULL when the stored ...
A (spatial) query in Oracle 10g gets different execution plan depending only on a parameter value. And sadly Oracle cannot execute one of the plans at all, giving an error. Changing value (below 282 to 284) or operator (= to <) gives a result. Why are the plans different? Why oracle selects an unexecutable plan? What to do to force Orac...
I have a weird problem where after setting nocheck on a foreign constraint and re-enabling it, I am getting a same out-dated execution plan that was used with nocheck on. Why would SQL server generate an execution plan as if foreign constraint FKBtoA is disabled even after adding the check again with following statement? alter table ...
In a SQL Server Execution plan plans what is the difference between an Index Scan and an Index Seek I'm on sql server 2005 ...
I have SQL Server 2008 and SSMS 2008, and I'm debugging a script. I can step through the script with no problems at all, but if I click the toolbar button for "Include Actual Execution Plan" (the one that adds an additional results tab with the execution plan), I'm not able to debug anymore. Instead, when I click debug, debugging starts...
Hi How to read and learn Execution plan of a T_SQL statement? ...
I always thought that a node in an execution plan can only be executed after its children have executed, and thus the total cost of a node has to be greater or equal than the cost of the child nodes. However, this is not always the case, like in the following example: Plan hash value: 2810258729 ----------------------------------------...
Hi, I found EXPLAIN SELECT query very useful in MySQL because it gives information on how SQL will be executed and gives the opportunity to analyze, for e.g., missing indexes you should add in order to improve response BEFORE doing the query itself and analyzing stats. My question is: In databases like MS Sql, Firebird, Ingres, is ther...
I'm trying to debug a fairly complex stored procedure that joins across many tabls (10-11). I'm seeing that for a part of the tree the estimated number of rows drasticly differs from the actual number of rows - at its worst SQL server estimates that 1 row will be returned, when in actuality 55,000 rows are returned! I'm trying to work o...
I have a stored procedure which filters based on the result of the DATEADD function - My understanding is that this is similar to using user defined functions in that because SQL server cannot store statistics based on the output of that function it has trouble evaluating the cost of an execution plan. The query looks a little like this...