Tag Info

Hot answers tagged

14

And nothing about the functions. Why is the function information missing in the actual plan? This is by design, for performance reasons. Functions that contain BEGIN and END in the definition create a new T-SQL stack frame for each input row. Put another way, the function body is executed separately for each input row. This single fact explains ...


11

Lets take your problem step by step: It has been running great until one fine morning I pushed a lot of rows to a table that is heavily used and since then I was getting query timeouts. Whenever you do large updates/inserts to you tables, highly recommend to update stats and reorg/rebuild indexes. That way query optimizer does not select or produce bad ...


11

I found this 6 year old blog post mentioning the same behavior. It looks like ROW_NUMBER() always includes a segment operator, whether PARTITION BY is used or not. If I had to guess I would say this is because it makes creating a query plan easier on the engine. If the segment is needed in most cases, and in the cases where it's not needed it's essentially ...


11

How to find out what the cost of creating a query plan is? You can look at the properties of the root node in the query plan, for example: This information is also available by querying the plan cache, for example using a query based on the following relationships: WITH XMLNAMESPACES (DEFAULT ...


9

This bug has been known for years, and won't ever be fixed. I first reported it 6 years ago but it has existed far longer than that. These are just estimated costs and don't really have any bearing on the optimizer itself and whether you can trust it. It's simply the showplan output that has some questionable math. ...


9

If a user executes a statement that is one of the statements in the multi-statement query can it use that relevant part of the query plan already in the cache for the multi-statement query? No. The basic unit of plan reuse in SQL Server is the batch. Would it be better to hash each statement in a multi-statement query so they could be used by users ...


9

I guess you are comparing the estimated costs for the queries. Those are just estimates based on (among other things) the estimated number of rows returned by the query. Not the actual number of rows. Your first query estimated that it would return 30 rows and your second query estimated 1000 rows. That is where your difference in query cost comes from. If ...


9

I think my understanding of the second estimate is incorrect and the differing numbers seems to indicate that. What am I missing? Using the SQL Server 2012 cardinality estimator, the selectivity of the join drives the estimated number of rows on the inner side of the nested loops join, and not the other way around. The 11.4867 number is derived (for ...


8

For 157MB in Tools -> Options -> Query Results -> SQL Server -> Results to Grid screen you will need to set Maximum Characters Retrieved -> XML data to unlimited as below.


6

What you're talking about is Short Circuit evaluation. Unlike in CASE statements (except certain scenarios using variables and aggregate functions), where the order is important, you have little control over the order in which the WHERE clause in SQL Server is evaluated (except with some grouping paratheses). So, even if it does short-circuit, SQL Server ...


6

The line from MSDN is talking about using EXEC(), like this: SET @sql = 'SELECT foo FROM dbo.bar WHERE x = ''' + @x + ''';'; EXEC(@sql); In my testing, modern versions of SQL Server are still able to reuse a plan like this, but there may be other variables (such as version, or for example if you add conditional WHERE clauses based on the presence of ...


6

According to the showplan.xsd for the execution plan, GroupBy appears without minOccurs or maxOccurs attributes which therefore default to [1..1] making the element compulsory, not necessarily content. The child element ColumnReference of type (ColumnReferenceType) has minOccurs 0 and maxOccurs unbounded [0..*], making it optional, hence the allowed empty ...


5

The core issue appears to be that the optimizer does not (or cannot) use the index idx_17109 to seek for the c.precedence IS NOT NULL predicate. The following modification allows the seek, but still requires a hint to avoid the sort: SELECT pcm.catalog_id FROM cat_catal_defer AS c USE INDEX (idx_17109) JOIN cat_produ_catal_map_defer AS pcm ON ...


5

First of all, no, you probably shouldn't clear your query plan cache. You probably having a problem with bad parameter sniffing. Here are some articles about it by various people. Greg Larson with SimpleTalk, Jes Schultz Borland with Brent Ozar Unlimited, and Thomas LaRock. You can do a quick search and you will see tons of others. It's a popular ...


5

"How bad is it?" depends on the degree to which you are suffering now or could suffer with increased workload in the future. One major point of suffering with plan cache pollution could be too many single use plans bloating your plan cache leading to inefficient cache usage. Another point of suffering could be high compilations/second - so in an ...


5

Clearing the plan cache is never a good idea. You're starting with a cold cache then, and you'll see a performance hit as SQL Server will have to compile everything from that moment on until plans are then cached to be reused. He came to this conclusion after noticing performance improved after a reboot. Oftentimes when I see DBAs reboot, restart the ...


4

When you create an expression index, it causes PostgreSQL to gather statistics on the that expression. With those statistics on hand, it now has an accurate estimate for the number of aggregated rows that the query will return, which leads it to make a better plan choice. Specifically in this case, without those extra statistics it thought the hash table ...


4

This example shows that your database users can run queries and collect the execution plans within a database where they have been granted SHOWPLAN (and without being added to the db_owner role), as long as the server-level login has not been explicitly denied the ability to ALTER TRACE. It also shows that unless you explicitly grant any trace-related ...


3

This: SET SHOWPLAN_XML ON; GO SELECT * FROM sys.objects; GO Is equivalent to pressing Display Estimated Execution Plan on the toolbar (or hitting Ctrl + L). You'll notice that no rows are returned from the query, like there is when you use Include Actual Execution Plan (Ctrl + M). The spill warning is only a runtime warning. There is no way that SQL ...


3

Option 1 The planner has no insight into the true nature of the relationship between EffectiveId and id, and so probably thinks the the clause: main.EffectiveId = main.id is going to be much more selective than it actually is. If this is what I think it is, EffectiveID is almost always equal to main.id, but the planner doesn't know that. A possibly ...


3

Yes, it is possible to wind up with a poor plan. One common cause of this is called "parameter sniffing". This is usually helpful, but if the stored procedure can be called with parameters that cause widely varying result sets, then the procedure can be stuck on a 'poor plan' for many executions. (But it might have been a fine plan for the execution that ...


3

Assuming "cost" is in terms of time (though not sure what else it could be in terms of ;-), then at the very least you should be able to get a sense of it by doing something like the following: DBCC FREEPROCCACHE WITH NO_INFOMSGS; SET STATISTICS TIME ON; EXEC sp_help 'sys.databases'; -- replace with your proc SET STATISTICS TIME OFF; The first item ...


3

if in a row col1 is greater then or equal two col1 in another row, then the same relation is valid between the two corresponding col2 entries In which case you can reformulate your query to look like: SELECT * FROM table WHERE col2 >= val1 AND col2 <= val2; because you can find the lower bound for col2 from the lower bound for col1, like this: ...


3

In this context, you should consider "hotfix" to mean any fix that ended up in a Service Pack, Cumulative Update, or on-demand hotfix and falls under the jurisdiction of this trace flag. These fixes are all in the most recent builds of each major supported version, but they are not used unless the trace flag is turned on. This is because, in some cases, the ...


3

The EnrichedVendor is not the same in the queries. I suspect that when you get a table scan you are working with products where the EnrichedVendor has a good majority of all the records in the table and therefore it makes sense to read all the table and the other EnrichedVendor has a much smaller percentile of the total records in the table. But you also ...


2

Your CTE actually does nothing else then 'outsource' a few WHERE conditions, most of them looking equivalent of WHERE TRUE. Since CTEs are usually behind an optimization fence (meaning that it is optimized on its own), they can help a lot with certain queries. In this case, however, I would expect the exact opposite effect. What I would try is to rewrite ...


2

Notice this line: -> Index Scan using data_area_pkey on data_area (cost=0.00..52.13 rows=1 width=8) (actual time=0.006..0.008 rows=0 loops=335130) If you compute the total cost, considering loops, it is 52.3 * 335130 = 17527299. This is larger than 14857017.62 for the seq_scan alternative. That is why it does not use the index. So the optimizer ...


2

I have used HeidiSQL for a while and it have very useful profiling feature that allow to get detailed profile of query execution. Also EXPLAIN is the first tool you have to learn. Less obvious is that you have to place your databases on the filesystem with noatime option. Each time some software read/write from/to the disk, another implicit write(s) ...


2

The queries which are run via sp_executesql follow the same rules of execution plans as normal queries which aren't run through sp_executesql. If the query text changes then a new plan is created. If the text doesn't change because of the user of parameters then the plan is reused. When the stats are updated then plans expire and new plans are generated ...


2

With queries like this is it often more efficient to perform a LEFT OUTER JOIN instead of the NOT EXISTS style check, it often implies a full index scan (or table scan without the right indexes in place) but with many rows in the main table(s) this is less expensive than the large number of index seeks (one on the reference table for each row returned from ...



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