Hot answers tagged

5

You can't reset the DMVs, however you can work around this limitation and remove rows from the DMVs by creating a small filtered index on the tables mentioned in the DMVs then immediately dropping that index. For instance: CREATE INDEX IX_temp ON dbo.SomeTable(SomeKey) WHERE SomeKey IS NULL; DROP INDEX dbo.SomeTable.IX_temp; I've created a script to ...


5

If it's a case of different options causing different plans to be used, just check the options using sys.dm_exec_plan_attributes. If the options are not the same for a good and a bad plan, that can then be the reason. A list of plan cache keys can be found in the following answer by Martin Smith: What would cause parameter sniffing one one computer and not ...


5

The main factors in play here are: The optimizer does not try to find the best plan; its goal is to find a reasonable plan quickly It assumes the query will be run with a cold cache The cost model used favours sequential I/O over random I/O Repeated seeks into an index are assumed to be randomly distributed The cardinality estimate for a table variable ...


5

Since you are running Microsoft SQL Server 2014 - 12.0.2000, the very first RTM build including the new Cardinality Estimator I would strongly suggest you try updating to one of the latest CU's. As stated in this blog post on msdn You need to apply SP1 but you must also enable trace flag 4199 in order to activate the fix. SQL Server 2014 Service ...


4

You may want to re-write your query as follows (I'm using dbo rather than XXXX so that I do find some synonyms on my testing database). This is similar to the re-write you found to be more efficient, but avoids the need to declare a variable and use two queries. SELECT name, base_object_name FROM sys.synonyms WHERE schema_id = SCHEMA_ID(N'dbo') ORDER BY ...


4

You should retype the various Name columns from nvarchar(max) to nvarchar([right size]). It's unlikely that names will be up to 2GB in length, and making them max sized prevents them being used as a key in an index. A good general rule of thumb is to avoid large object data types wherever possible. You'll probably need to make that change to the EF code, ...


4

Why would you expect performance to be different with partitioning? Partitioning improves manageability but performance depends greatly on the workload and queries. For example, if you have a useful index on the DateColumn, you will likely get the same performance with and without partitioning. With no index on the column, performance may be better with ...


4

Summary The question does not provide execution plans or a full reproduction script, but based on the information given, you should use a FAST 1 hint. If you can, you should also consider converting the scalar function to the inline table-valued type. AdventureWorks-based examples Download the Microsoft sample database here. Table type CREATE TYPE ...


3

ORDER BY in the sub query is what's making it slow. ORDER BY makes query slow since you need to order (sort) all the objects in the query results. It is in order of N Square if you don't have an index, and N log N if you do. And you don't even need to order by all of it. What you need is only to filter out the minimum, so instead of: ORDER BY ...


3

For this WHERE clause, I would try an index on (customer_account_id, sync_inactive, last_modified, id): ALTER TABLE main_contactinfo ADD INDEX customer_active_last_modified ( customer_account_id, sync_inactive, last_modified, id ) ; The ORDER BY with e small LIMIT complicates things though, so a different order of the columns in the ...


3

Two likely possibilities. Blocking Your production machine probably has a lot more going on than your test/dev machines. You could simply be seeing blocking. Run your query in one window and this in another. SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id > 0 If you are being blocked you may need to look at what's blocking you and how ...


3

I think the condition ON a.ID = ISNULL(b.ID,a.id) is not the best way to do this. It may lead to inefficient plans and may return unwanted results (according to your specifications). If the tblxyz table has rows with NULL values, you'll still get all the rows from table a (and possibly multiple times). I would write the query without using the ISNULL() ...


3

...the original table contains more data than the new table, which should theoretically mean that the new table should finish the query faster. If the execution plans were the same, this would most likely be true, but they're not. The number of rows expected (and the distribution of the data according to the statistics) affects the strategy chosen by ...


3

One possibility is that the stored procedure was created with settings for ANSI_NULLS and QUOTED_IDENTIFIER that differ from those set on your local connection. These settings are stored with the procedure definition and override the session's current value. These two options can dramatically change an execution plan, and may prevent the use of indexes on ...


3

I suggest a filtered index: CREATE INDEX ix_valid_text_column ON dbo.t1(Tid) WHERE text_column IS NOT NULL; Now your query should be able to use that index instead of the clustered index, and it should be much more efficient. Even better, it enables you to look at the metadata views, so you read one row (or a number of rows equal to the number of ...


2

First of all, if custId is the Customer's Primary Key, then there's no need to specify DISTINCT at all. Second, grouping and then filtering by the grouped key may be very inefficient when the filtering will likely render a small subset: filter first, then group. Third, I assume you don't require casting to varchar when removing the DISTINCT clause. Fourth, ...


2

There are multiple things to look at to improve query performance. Understand your system load Volume of data you are expecting to be in the tables mentioned in query Volume of data you are going to fetch from tables mentioned in query Indexes applied on tables mentioned in query Structure of the query I can suggest you improvements related to only ...


2

This is a simple question No, it is not. This (kind of) question is what plagues many DBAs and software developers day in day out, and it is all but trivial. that I can't seem to find the answer for. Yes, you won't. At least not a general answer. First of all, it will depend hugely on which RDBMS you are using (OK, you are using sql-server, but ...


2

1 - Query was introduced to this website 2 - An index has been created to improve the Query: CREATE INDEX CONCURRENTLY ix_feedlog_client_time_notif_id ON public.ja_feedlog USING BTREE ("clientid","gtime" DESC, "log_type", "id"); Total time before the index: 346507.823 ms Total time after the index: 625.375 ms 3 - The query was fast, but not enough. So ...


2

The problem is that TopN Sort in the top left. It has to pull in all possible rows to then find the smallest one. If you have an index on DiameterInches, it's likely to start searching on the smallest ones first, and give you vastly improved performance. Of course, then you'll need an index on Widgets.SizeId, so that it can find those easily. And it'll ...


2

For a table with 1000 rows I think it does not make sense to put any index. The complete table could fit into memory and that would be fast enough. But if you want to put indexes you should always take the business value into consideration. Take into account that each index generates an overhead to the table. Not sure where the limit is, but in any case ...


2

in order to tackle this I would like a T-SQL query that would show me all the values that have been set for the session. To view the SET options for current sessions: SELECT * FROM sys.dm_exec_sessions;


2

Don't randomly add indexes. Look at your queries to decide which indexes are needed. See my cookbook. InnoDB really needs a PRIMARY KEY. Keep in mind that a PK is, an index, is UNIQUE, and is clustered. So don't add any index(es) that start with the same column(s). WHERE a=2 AND b=4 begs for a "composite" index: INDEX(a,b) or INDEX(b,a). Those are ...


2

The way you've done it is correct. So the reasons for slowness will likely come down to: when is it slow (is it when the temp table has a lot of rows, or when it has few rows?), how many rows are usually in each table, what indexes exist on each table, and what data type is ID? You can also look at your real-world execution plan for some answers. I ...


2

You'll speed-up the query significantly if you create an index (ideally a unique index) on property_ad_id and created_at, and another on just created_at: CREATE INDEX ON property_ads_history (property_ad_id, created_at); CREATE INDEX ON property_ads_history (created_at); # if not already there! Note that the condition t1.price > -1 OR t1.price <> ...


2

We solved the issue. The issue was caused by the new Cardinality Estimator in SQL 2014. We disabled the new estimator by activating trace flag 9481 and removing the query plan from the cache then the query worked again.


2

You can UNPIVOT and then PIVOT. Half of it is below (untested) WITH P1_Src As ( SELECT ID_Skill, Val, Col FROM Job_Skill UNPIVOT (Val FOR Col IN (Min_Job, Idea_Job, Max_Job)) AS U ), P1 AS ( SELECT Col, [1], [2], [7], [8] FROM P1_Src PIVOT (MAX(Val) FOR ID_Skill IN ([1], [2], [7], [8])) AS P ) SELECT * FROM P1 You would need to apply a case expression to ...


2

Using an index requires bouncing back and forth between the index and the data. In MyISAM, each index is a BTree sitting in the .MYI file. At the leaf node of the index is a pointer into the .MYD file. (Or, for FIXED, it will be a record number.) Your SELECTs are happy to scan linearly through the index (a BTree is efficient at that), but then for each ...


2

Amazingly broad question but a couple of simple answers. Indexing like you suggested. Make sure the appropriate indexes exist and if the need is great enough make sure they are all covering. Get rid of the *. Specify the columns you need. If you are pulling across 100 columns over a million rows that's going to be a LOT of data. If you only need 3 ...


1

Yes, you can refer to rows of higher levels in a subquery. But that would not solve your problem since you need to consider the whole table, not just the current row. It's not completely clear what the query is trying to achieve and data types (table definition!) are also missing. Be aware that your invocation of generate_series() returns timestamptz, which ...



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