Tag Info

Hot answers tagged

11

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


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


8

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


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

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.


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

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


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


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

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


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

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


2

This closely related answer on SO should provide answers to your primary question: Setting enable_seqscan = off in a single SELECT query You could use in similar fashion, to disable hash joins for the current transaction: SET LOCAL enable_hashjoin=off; But that's not my advice. Read the answer over there. And this one about statistics and cost settings, ...


2

You have not defined any indexes, so this query would be slow even without sorting, because it needs a full table scan. You should define a combined index on (uid, id). The first part helps in finding all rows WHERE uid = X. If these have been found, the second part can be used to sort the result set without the need to use filesort. Use this create table ...


2

If you look at the two execution plans you will see that the estimated cardinality is different in each case. When the actual value is in the SELECT statement the optimiser can use the cardinality of that one, actual value to choose an access path. When the SELECT has a variable the optimiser cannot know whether you have provided a very popular or very ...


1

As @dezso suggested, the table with test data was not big enough for the query planner bother going to the index. After I imported a larger set of data, the query uses the index as expected.


1

The query is pretty simple and ought to run fast enough. My guess is that you have a long running transaction performing DDL in one of the databases and your query is getting blocked waiting on a lock. Unfortunately the metadata functions such as OBJECT_ID don't take account of the transaction isolation level of the outer transaction so even setting the ...


1

There isn't much out there for MySQL except the following: EXPLAIN EXTENDED followed by SHOW WARNINGS SHOW PROFILES (Older Releases of MySQL) MySQL PERFORMANCE_SCHEMA Read these carefully, see what you think ...


1

Barry, Check out the statistics histograms on the indexes being used. This great book (free from RedGate) has a section on histograms: http://www.red-gate.com/products/sql-development/sql-prompt/entrypage/sql-query-optimizer-ebook2 You could have skewed data that is messing up the estimates. This has happened to me in the past: Poor query performance, ...


1

Up to date statistics does not necessarily imply perfect estimation. If you try to represent a range of 170 million values with 200 samples, even a small skew can throw you off by a factor of 10. Here is a hint: time series are almost always accessed by time. Very likely DateStamp should be clustered key. For more detailes, post the exact DDL and actual ...


1

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


1

This can be improved in a thousand and one ways, then it should be a matter of milliseconds. Better Queries This is just reformatted with useful aliases and some noise removed to clear the fog: SELECT count(DISTINCT t.id) FROM tickets t JOIN transactions tr ON tr.objectid = t.id JOIN attachments a ON a.transactionid = tr.id WHERE t.status ...


1

Doing a natural self-join is like joining on all the columns, so like joining on the primary key (as the set of all columns is certainly a superset of the primary key.) Every row of the left copy of the table will be joined to exactly one row (its copy) of the right copy of the table. Therefore, the size of the resulting set R NATURAL JOIN R will be exactly ...



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