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 ...


13

Personally, whenever I build a new server for a new project I always enable TF4199 globally. The same applies when I upgrade existing instances to newer versions. The TF enables new fixes that would affect the behaviour of the application, but for new projects the risk of regression is not an issue. For instances upgraded from previous versions, the ...


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.


7

The clustered index is partitioned on ReadTime so it couldn't use the PK as you describe. It would need to find the Max(Id) for each partition and then find the max of those. It is possible to rewrite the query to get such a plan however. Using an example based on the article here a possible rewrite might be SELECT MAX(ID) AS ID FROM sys.partitions AS P ...


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 ...


4

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 ...


4

Run ANALYZE after the index has been added. And make sure the column deprovision has statistics. How to verify? Basic statistics in pg_class: SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE oid = 'schema_defs'::regclass; Data histograms per column in pg_stats (pg_statistics): SELECT attname, inherited, n_distinct, ...


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

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 ...


3

SQL Server uses statistics to determine an execution plan. If an index is available, so are statistics, and SQL server will determine the path of least work. This could be using the index or doing a table scan. In your example SQL server has determined that a table scan is less work than doing an index seek and a bookmark lookup. What you can see is that ...


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 ...



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