Tag Info

Hot answers tagged

13

Short version: seek is much better Less short version: seek is generally much better, but a great many seeks (caused by bad query design with nasty correlated sub-queries for instance, or because you are making many queries in a cursor operation or other loop) can be worse than a scan, especially if your query may end up returning data from most of the rows ...


12

Before getting to the main answer, there are two pieces of software you need to update. Required Software Updates The first is SQL Server. You are running SQL Server 2008 Service Pack 1 (build 2531). You ought to be patched up to at least the current Service Pack (SQL Server 2008 Service Pack 3 - build 5500). The most recent build of SQL Server 2008 at the ...


12

The buffer pool is a cache of the database. There is never an 'or', things that are in the buffer pool are also in the database, always. And anything read from the database must be, even temporarily, present in the buffer pool. As for the question: statistics are in the database so a backup/restore will preserve the statistics. Note though that ...


11

Yes. You need the USE PLAN hint. In which you supply the XML from the first plan. SELECT * FROM T OPTION (USE PLAN N'<?xml version="1.0" encoding="utf-16"?> ....') Whilst it doesn't guarantee that the plan will be exactly the same (e.g. compute scalar operators can move around for example) it will likely be pretty close.


10

Think about what "actual" means. It's what actually happens for the execution of that plan. Another common name for the actual execution plan is the "post execution plan". As a real world example to correlate this scenario, say you plan to go on a cross country trip, so you plot out the roads you're going to take and how long you think it'll take. But ...


10

Obvious disclaimer: I work for SQL Sentry. The biggest problems we have are: Like @JNK says, SQL Server obfuscates away the use of a UDF, and does terrible things with them anyway (like always estimates one row). When you generate an actual plan in SSMS, you don't see its use at all either. We are subject to the same limitations because we can only ...


8

This table is very small! It has 20 rows of which 2 match the search condition. The table definition contains three columns and two indexes (which both support uniqueness constraints). CREATE TABLE Person.ContactType( ContactTypeID int IDENTITY(1,1) NOT NULL, Name dbo.Name NOT NULL, ModifiedDate datetime NOT NULL, CONSTRAINT ...


8

Addition to what Remus has mentioned, I would suggest you read -- SQL Server Statistics Questions We Were Too Shy to Ask Aarons answer to - Where are Statistics physically stored in SQL Server? UNDERSTANDING SQL SERVER STATISTICS


7

I'm guessing the 'memory left' output you posted is from Christian Bolton's VAS usage analysis script. Is that correct? If so, you have only 17-18 MB available in the memtoleave area, which is likely to cause a problem at some point and could cause all or most of the error messages that you pasted. This query from Jonathan Kehayias here will give you an ...


7

Your code says you are doing a LEFT OUTER JOIN, but are you really? Your very first WHERE clause (as well as each that follow) filters rows for inclusion from the outer table: WHERE (A.[Operator_Name] LIKE '%'+ @OName +'%') This turns your LEFT OUTER JOIN into an INNER JOIN, whether you meant it or not. Perhaps you meant to move those filters to the ...


6

My initial playing around with various queries suggested no pattern at all but on paying closer attention it appears to be predictable for serial plans. I ended up at KB314648 which @AustinZellner mentions: Each SQL Server connection has an associated process status structure (PSS) that maintains connection-specific state information. Each unique ...


6

The cost is the same (1%) for both the slow and fast cases. Does that mean the warning can be ignored? Is there a way to show "actual" times or costs. That would be so much better! Actual row counts are the same for the operation with the spill. The cost shown is always the optimizer's estimated cost of the iterator, computed according to its ...


6

I'm pretty certain the table definitions are close to this: CREATE TABLE dbo.households ( tempId integer NOT NULL, n integer NOT NULL, HHID integer IDENTITY NOT NULL, CONSTRAINT [UQ dbo.households HHID] UNIQUE NONCLUSTERED (HHID), CONSTRAINT [PK dbo.households tempId, n] PRIMARY KEY CLUSTERED (tempId, n) ); ...


5

Your queries are not equivalent. The first will give you rows where both node names and values match where your second query only check the node values. SQL Fiddle The extra cost for the second query comes from using a table valued function to shred the XML. First query: Second query:


5

When sp_foo executes there is only one query: SELECT * FROM ( SELECT * FROM ServerB.database.dbo.Table1 WHERE Column1 IN (1,2,3)) WHERE Column1=1; This query can execute locally against a remote scan or it can execute remotely. What happens cannot be foretold, because it depends on the capabilities advertised by driver in use by the linked ...


4

No, there is no way to save a query plan to disk, force a query plan, etc. Fix your optimizer parameters and make sure your stats are accurate. You didn't bother to mention your version, show explain analyze output, etc so I can't help you with that in detail. Start here: http://wiki.postgresql.org/wiki/Slow_Query_Questions Check your rowcount estimates ...


4

type: index means it's an index scan. That is, it's scanning through an entire index of that table. An index scan often goes along with Using index because the latter indicates that the query is able to use the index to satisfy the query, without touching the rows of the table. Using index would be more clearly labeled Using only index. There are as many ...


3

I can't say for certain because I've never dealt with that large a stored procedure but you could try this: SELECT query_plan FROM sys.dm_exec_procedure_stats CROSS APPLY sys.dm_exec_query_plan(plan_handle) WHERE object_id = object_id('procedure_name') Then save the output to a text file.


3

So is there a way I can see the execution plan of the proc or the statement in particular? You have to refer to DMV's for extracting such information. sys.dm_exec_cached_plans, sys.dm_exec_sql_text and sys.dm_exec_query_plan are the ones to look into. Especially sys.dm_exec_query_plan DMF will return the plan for a given batch or procedure (along with ...


3

If you wish to dig the subject, a very helpful book (at least for me) is SQL Server Execution Plans by Grant Fritchey, freely available at RedGate here. If you have a query such as SELECT * FROM myTable SQL Server will likely use an Index scan, as it needs to go through all the rows to display the required results. On the contrary, SELECT * FROM ...


3

Generally, seeks are good, scans are bad. Seeks are where the query is able to make effective use of the index, and use it to find the rows it needs. Scans are where the query is looking through the whole index trying to find what it needs. How does SQL choose? Deep in the internals of the query optimiser, the decision is made based on your query and the ...


3

(Moved my comment to an answer, and expanded a bit.) I cannot create indexed views as I've some calculations as well like SUM(Volume * CaseCount) AS SomeColumn which is not allowed in indexed views and also it's a dynamic query and grouping and joins are based on user input. From the plan you provided, the clustered index scan is 3.5x overestimated, ...


3

With a table that large I would consider partitioning. Unfortunately I can't answer your specific questions (1-3) but in general one of the benefits of using partitioned views (instead of native partitioning) is that the individual tables within the partitioned view are considered separate objects and have their own statistics each with 200 steps. Here is ...


3

The sub query in the second query it's what's killing you that requires that the sub query needs to be run once per row that's returned by the record set. If you look at the plan it'll tell you how many times each operator is executed. I'll bet that you'll see a high number of executions on some operators of the second query. (I'm on my phone at the moment ...


2

Since you have ServerB.database.dbo.Table1 meaning you are using Linked server, the server will used link server configuration to send the query to the linked server and retrieve the relevant results. Rest everything will be on the server that you are running the query on. Using SSMS, the real execution of the query will take place on the remote server ...


2

Others have defined well enough the differences between seek and scan. In this instance, your query itself and the execution planner should give you the information you need to see which values are used as predicates (filters) for the query in each part. Typically it's a good practice to always add non clustered indexes on foreign keys, and depending on the ...


2

As Remus said, table statistics are stored in the database similar to other objects like tables and indexes. They play a big role in selecting the execution plan, but there are other factors. That being said, SQL Server knows another type of statistics, statistics that give us information about recent behavior. For example the DMVs ...


2

Sql server's query optimizer does not take variations in disk performance into consideration when compiling a query plan. Paul White provides a great overview of Sql Server's cost based optimizer here: http://sqlblog.com/blogs/paul_white/archive/2010/09/01/inside-the-optimizer-plan-costing.aspx Some key points are: The optimizer isn't trying to ...


2

Looks to me like it is the opposite order of data read access in the plan. Your plan will first read from the Product table to build the hash table (worktable). Than it reads from SalesOrderHeader and form SalesOrderDetail combining them with the merge join operator. The worktable then is read from last to hash-match the original Product rows with those from ...


2

@Kin has a very good answer so I do not intend to supplant his answer, but to supplement. You can also get the plan from Profiler itself. This would be especially helpful if the plan had been evicted from the Plan Cache before you had a chance to analyze it. In the "New Trace" panel click on the "Events Selection" tab. Then check the "Show all events" and ...



Only top voted, non community-wiki answers of a minimum length are eligible