I am attempting to troubleshoot a slow running stored procedure in SQL Server 2005. I am analyzing the execution plan and see a SORT that is 45%, but I am not using an ORDER clauses. What would be causing this. UPDATE SP (cleaned up, and made change on OR's) SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Rp...
What is the usage of Execution Plan in SQL Server? When can these plans help me? ...
I've got a table ItemValue full of data on a SQL 2005 Server running in 2000 compatibility mode that looks something like (it's a User-Defined values table): ID ItemCode FieldID Value -- ---------- ------- ------ 1 abc123 1 D 2 abc123 2 287.23 4 xyz789 1 A 5 xyz78...
I have a LINQ to SQL query that's created dynamically. Funny thing is, when I run it in SQL Management Studio it's lightning fast. When I run it from L2S it becomes awefully slow after a while. This is probably because of the query plan/execution path. When I restart SQL Server the L2S query is also lightning fast again. Now with T-SQL...
Which is faster SELECT * FROM X INNER JOIN Y ON x.Record_ID = y.ForignKey_NotIndexed_NotUnique or SELECT * FROM X INNER JOIN Y ON y.ForignKey_NotIndexed_NotUnique = x.Record_ID ...
I'm trying to understand physical operators in SQL Server execution plans. This page is quite helpful: http://technet.microsoft.com/en-us/library/ms191158.aspx SSMS 2008 shows some physical operator properties that are not displayed in SSMS 2005: Estimated Number of Executions and Number of Executions. But what do these actually mean, ...
I'm curious. Is there any way to prevent SQL query execution plans from being cached for an entire connection's duration? Can I run a command that basically says, "use OPTION(RECOMPILE) for all subsequent commands until I tell you to stop doing so?" I'm acutely aware of all the performance trade-offs here, and I know this is not a ste...
In another question I was trying to research how to control SQL Server's query plan caches: http://stackoverflow.com/questions/2593749/is-there-an-equivalent-of-optionrecompile-or-with-recompile-for-an-entire-c ...and I found trace flag 253 via this article: http://www.sqlservercentral.com/Forums/Topic837613-146-1.aspx The article is...
I could see nothing wrong with the execution plan. Besides, as I understand it, SQL Server 2000 extended many of the performance benefits of stored procedures to all SQL statements by recognising new T-SQL statements against T-SQL statements of existing execution plans (by retaining execution plans for all SQL statements in the procedure...
I saw something from an "execution plan" article: 10 rows fetched in 0.0003s (0.7344s) (the link: http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ ) How come there are 2 durations shown? What if I don't have large data set yet. For example, if I have only 20, 50, or even just 100 records, I can...
We are running MS SQL 2005 and we have been experiencing a very peculiar problem the past few days. I have two procs, one that creates an hourly report of data. And another that calls it, puts its results in a temp table, and does some aggregations, and returns a summary. They work fine...until the next morning. The next morning, s...
Hi, I am trying to find a solution for a problem that is driving me mad... I have a query which runs very fast in a QA Server but it is very slow in production. I realized that they have different execution plans... so I have try recompiling, cleanning the cache for the execution plans, update statistics, check the type of collation......
Say you've got the following query on 9i: SELECT /*+ USE_HASH(t2 t3) */ * FROM table1 t1 -- this has lots of rows LEFT JOIN table2 t2 ON t1.col1 = t2.col1 AND t1.col2 = t2.col2 LEFT JOIN table3 t3 ON t1.col1 = t3.col1 AND t1.col2 = t3.col2 Due to 9i not having RIGHT OUTER HASH JOIN, it needs to hash table1 for both joins. D...
Does SELECT TOP 1000 * FROM TABLE return the same data execution plan as SELECT * FROM TABLE? Please also let me know if this should be moved to ServerFault. Thanks. ...
I am running two very similar update queries but for a reason unknown to me they are using completely different execution plans. Normally this wouldn't be a problem but they are both updating exactly the same amount of rows but one is using an execution plan that is far inferior to the other, 4 secs vs 2 mins, when scaled up this is cau...
I have an sproc in SQL Server 2008. It basically builds a string, and then runs the query using EXEC(): SELECT * FROM [dbo].[StaffRequestExtInfo] WITH(nolock,readuncommitted) WHERE [NoteDt] < @EndDt AND [NoteTypeCode] = @RequestTypeO AND ([FNoteDt] >= @StartDt AND [FNoteDt] <= @EndDt) AND [FStaffID] = @StaffID AND [FNoteTypeCode]<>...
You can read the backstory if you want, after changing my nonclustered index to a clustered index things started running a lot faster. The problem with the initial query plan taking 2-3 seconds still remain though. However, the keep plan query hint improved that quite a bit. BACKSTORY I have an inverted index where I store things I wis...
Hi Does anyone know an open source java solution to display the Query Execution plan of a query send to a database ? Would prefer a graphical output like a graph with nodes etc. EDIT : Used Database is Oracle ...
Today I found this query in our code that pulls up a list of errors from our database: SELECT * FROM ( SELECT Substring(title, 9, Patindex('%)%', title) - 9) AS SERVICE, * FROM core.LOG WHERE logtypeid = 1 AND title LIKE 'Error: (%' AND lastmodified > '2010-06-21T00:00:00' ...
I have a table User with a bunch of indexes. One of them is a unique index on the AccountIdentifier column. Since this is a unique index, why is a key lookup required in addition to the index seek? The index seek tooltip reports that only one record is returned. I've also tried converting the index to a "unique key" type. ...