How can I compare the performance of an Index on a table using the Query Analyzer? I would like to run the same query through the Estimated Execution Plan twice, once using the index and once without, and have a good comparison of the table scans/index scans that are generated. I have tried CREATE INDEX..., SELECT..., DROP INDEX..., SE...
Does anyone have a query which searches through SQL2005/2008's plan cache identifying queries or stored procedures which have table/index scans within their execution plans? ...
Question 1: When we execute a query, does the execution plan change for each and every time when executing the query? If yes, any performance hit? If no, then if we change something in the table, i.e adding an index, how does the databse know that there is something it can use to change the execution plan for faster execution? QU...
I'm looking for an application to display what a linq expression would do, in particular regarding the usage of multiple access to the same list in a query. Better yet, the tool would tell if the linq query is good. ...
I have a table, let's call it History. The primary key (aka Clustered Index) is called HIST_ID. The table has some 2300 rows in the development DB. Now consider the following two queries: Query 1: declare @x int set @x = 14289 select * from History where hist_id=@x Query 2: declare @x int set @x = 14289 select * from History where...
I'm having trouble understanding the behavior of the estimated query plans for my statement in SQL Server when a change from a parameterized query to a non-parameterized query. I have the following query: DECLARE @p0 UniqueIdentifier = '1fc66e37-6eaf-4032-b374-e7b60fbd25ea' SELECT [t5].[value2] AS [Date], [t5].[value] AS [New] FROM ( ...
I've looked all over for this command....what's the command to reset the SQL Server's execution plan? ...
One of my stored procedure has long execution time (average around 4 to 7 minutes). Now I trying to tweak it an make it run faster. I am looking at execution plan and two things I see that using most of percentage. First is 68% of "Clustered Index Scan" of one main tables for reporting, This table has primary key of two columns and 200...
I have a query where I select a few columns from each of 5 left outer joined tables. I did an execution plan in SQL Server 2008, and there are basically table scans on all of the joined tables, but the cost is all 0% for them - I'm assuming because there aren't many records in these tables. Then at the last 2 steps of the execution p...
I've Googled this question and can't seem to find a consistent opinion, or many opinions that are based on solid data. I simply would like to know if using the wildcard in a SQL SELECT statement incurs additional overhead than calling each item out individually. I have compared the execution plans of both in several different test querie...
I have a simple data logging table with two data columns A and B for which I want to generate the sum of A+B. However either or both A and B could be null. The table also has a timestamp column which is the primary key I can see several ways to skin this cat, but I was curious to know what the preferred method might be to sum the dat...
I have a query that creates several temporary tables and then inserts data into them. From what I understand this is a potential cause of Table Spool. When I look at my execution plan the bulk of my processing is spent on Table Spool. Are there any good techniques for improving these types of performance problems? Would using a view ...
I'm trying to improve the performance on a query that is running very slowly. After going through the Actual Execution plan i found that a Clustered index seek was taking up 82%. Is there any way for me to improve the performance on an Index Seek? Below is an image of the problem Index Seek from the execution plan as well as the index...
I am trying to speed up a long running query that I have (takes about 10 minutes to run...). In order to track down what part of the query is costing me the most time I included the Actual Execution Plan when I ran it and found a particular section that was taking up 55% (screen shot below) This didn't quite seem right to me so I add...
Consider the following "code" define stmt1 = 'insert into T(a, b) values(1, 1); define stmt2 = 'select * from T'; MSSqlCommand.Execute( stmt1;stmt2 ); MSSqlCommand.Execute( stmt2 ); Investigating the cached query-plans using: SELECT [cp].[refcounts] , [cp].[usecounts] , [cp].[objtype] , [st].[dbid] , [st].[objectid] , [st].[tex...
I have three tables: SmallTable (id int, flag1 bit, flag2 bit) JoinTable (SmallTableID int, BigTableID int) BigTable (id int, text1 nvarchar(100), otherstuff...) SmallTable has, at most, a few dozen records. BigTable has a few million, and is actually a view that UNIONS a table in this database with a table in another databas...
I've been hearing a lot lately that I ought to take a look at the execution plan of my SQL to make a judgment on how well it will perform. However, I'm not really sure where to begin with this feature or what exactly it means. I'm looking for either a good explanation of what the execution plan does, what its limitations are, and how I...
Does anyone know how to reduce the 'Number of executions' you can see in the execution plan of a SQL Query in MS SQL Server? We have a query that runs slow on one production server (others are fine). When checking the execution plan, we see that one of the first steps is a Clustered Index Scan. On the servers that run fine, this scan is...
Is there a measure of time frame as to which the execution plan of inline queries will cached? ...
Based on getting Query Execution Statistics using this extremely useful piece of SQL obtained from this post Most Executed Stored Procedure - Stack Overflow: SELECT TOP 100 qt.TEXT AS 'SP Name', SUBSTRING(qt.text, qs.statement_start_offset/2, CASE WHEN (qs.statement_end_offset = -1) THEN LEN(qt.text) ELSE (qs.statement_end_offset ...