Hot answers tagged

10

Memory Limit Exceeded The optimizer was forced to stop looking for better plan alternatives due to memory pressure. The reason for that should be investigated and corrected, then query compilation attempted again. The plan returned may very well not be the one the optimizer would have selected had the low memory condition not existed. Time Out This reason ...


8

If you go over to http://schemas.microsoft.com/sqlserver/2004/07/showplan/showplanxml.xsd (which is the link you'll see if you open an execution plan as xml), you'll see the three reasons listed, which are: TimeOut MemoryLimitExceeded GoodEnoughPlanFound The articles you mention seem ok for finding these events, are you having a specific problem? The ...


8

You are using a linked server to access Table1 and Table2. The first query is sent as is to the other server and executed there returning only the rows you want. The second query is doing a join between a local table TB_BRANCH and a remote table Table1. To do that it fetches all rows from Table1 and all rows from Table2 to your local server and does the ...


7

Check the View Tuning Output: If you want to save all of the Transact-SQL scripts that create or drop all database objects in this recommendation into one script file, click Save Recommendations on the Actions menu. As always review and test the recommendations before blindly applying them to your PROD environment. I would highly suggest to look ...


6

They're not redundant, no. Each statistics object includes a histogram of the values stored in the first listed column. For the remaining columns, only density information is kept at each level (no histogram). For example, the proposed statistic on (ClassID, SegmentID, GroupID, Tier1, LanguageID, Active) would create: A histogram for ClassID values A ...


4

Use LEAD() to get the next row within the TicketId partition. Then join to a Calendar table to get all the dates between. WITH TAwithnext AS (SELECT *, LEAD(AssignmentDate) OVER (PARTITION BY TicketID ORDER BY AssignmentDate) AS NextAssignmentDate FROM TicketAssignment ) SELECT t.TicketID, c.Date, t.DepartmentID FROM dbo.Calendar c JOIN TAwithnext t ON ...


4

You do not specify which RDBMS you are working with. Most what I write here should be quite independent but I mostly have experence in MySQL so maybe different systems allow some other optimizations. The (SELECT count(*) FROM review WHERE to_user_id = u.id) as reviewCount is a dependent subquery - it will be executed for each row in your results. Even if ...


4

Please note that a computer generating a list of "missing indexes" should not be swallowed whole. You will still need to decide which indexes to create, which recommended indexes are near duplicates of existing indexes, and how you should want to handle those issues. It still requires you making a decision since the generated recommendations need some ...


4

Better data types text is a sub-optimal data type for key columns. It would be more efficient to use integer. Related: Indexes: integer vs string performance if the number of nodes in the index is the same '26c72242-7e3b-4982-92c5-021b622d7ecd' in your example looks like a UUID. If you need to use UUIDs, still don't store them as text. The appropriate ...


3

Add the second predicate of your query to the partial index as well: WHERE "Post"."createdAt" > '2015-08-19 14:55:50.398' Your timestamp is probably a moving target, but I am going to assume you have lots of old rows that are excluded in most of your queries and only few "younger" rows are of interest. A typical use case. You can cut off old rows in ...


3

I think you will be much better off testing your actual workload under sync and async, and see if you observe any differences. Coming up with a sweet spot to suit a particular feature is kind of backwards. Generally, async is primarily beneficial when you don't care if the query that triggered the stats update still runs that last time with the old stats ...


2

Assuming "first level" means the leaf level of a nonclustered (secondary) index, the b_li/2 part accounts for the cost of scanning (using sequential I/O) through half those index blocks. Assuming "file records" means the base table, the r/2 component represents the cost of looking up additional data not present in the secondary index. Since this is a ...


2

PostgreSQL can only make use of a function index when the comparison is against the results of the function, e.g.: AND (s.full_path)::text ~ '/userfiles/account/[0-9]+/[a-z]+/[0-9]+' Alternatively, create the index without typecasting: CREATE INDEX CONCURRENTLY ix_full_path ON gorfs.inode_segments USING btree (full_path); Note also that the character / ...


2

Your query doesn't loop 10483 times, it loops once (loop=1) for each loop, but it has to sort 10483 rows first prior to discarding all but the first 5 rows. Your last query was almost correct, except for the fact that you filter user_account by the users with greatest follower_count regardless of being involved in the discussion or not. Also, isn't this ...


2

Other than the DTA, is there any other way to find out the missing multicolumn statistics? Nope ! query optimizer assumes that columns within the same table are independent. Joe Sack talks about it here The query optimizer assumes that columns within the same table are independent. For example, if you have a city and state column, we may ...


2

This is a quick way of doing (I have not tested for performance or scalablity) -- create Calendar table -- borrowed from @Aaron's post http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-3 CREATE TABLE dbo.Calendar(d DATE PRIMARY KEY); INSERT dbo.Calendar(d) SELECT TOP (365) DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY number)-1, '20160101') ...


2

Benjamic Navarez has an excellent article on how the cost-based optimizer in SQL Server works. Essentially, when SQL Server is asked to read a table it looks at several methods of returning the requested data, and chooses the "best" path it can by assigning costs to each type of operation. It then chooses the cheapest path, or plan. This may or may not ...


1

Add an index to (post_id) in postlocks, remove the subquery against that table and the reference to that column in HAVING, and add WHERE NOT EXISTS (SELECT * FROM postlocks pl WHERE pl.post_id = p.id). ...for a start. You want that condition to be evaluated early since it will eliminate some of the other lookups, and this should help ensure that the ...


1

Yes, adding indexes could cause IO wait to increase. Perhaps without the index, you are doing a lot of full scans of the table, thousands or millions of blocks, to get just one piece of data. But the IO wait is very low, because the kernel read ahead keeps the pump primed so your process doesn't wait on IO (instead it uses a lot of User CPU to filter ...


1

One thing to try with IN is to change it to EXISTS (with the proper modifications to the subquery): SELECT COUNT(order_id), user_id FROM MyCustomTable a WHERE type='x' AND EXISTS (SELECT 1 FROM MyCustomTable b WHERE type ='y' AND user_id='56' AND ...


1

The only reason for a recheck to happen in this particular query is that the bitmap is too large to fit in work_mem and so has to be down-graded to lossy. So to avoid the recheck, try to increase work_mem, if you can afford to. You shouldn't have to increase it by much to hold 1138854 tuples. Newer versions of PostgreSQL make this clearer, by including ...


1

My understanding: The table contains 1M rows of which 250k are returned by the query. There are 500k rows with foreign_key_id = 1 and 500k rows with af.foreign_key_id2 IS NOT NULL. The query using full table scan (actually doing full index scan on the PRIMARY key in InnoDB) will read all 1M rows sequentially and check each of them for the conditions. The ...


1

Your strategy for getting information from full_path can be useful for a one-off, but for ongoing queries to it, especially over millions of records and expecting quick results, it is far from optimal. Considering the sheer size of your tables, you'll probably benefit from datawarehousing. If your tables are constantly updated, you'll need a trigger to keep ...


1

Perhaps this will help. If you'll rely on the account_id from full_path often, then you'll benefit from a function and a functional index for it: CREATE OR REPLACE FUNCTION gorfs.f_get_account_from_full_path(p_full_path text) RETURNS int AS $body$ SELECT (regexp_matches($1, '^/userfiles/account/([0-9]+)/[a-z]+/[0-9]+'))[1]::int $body$ LANGUAGE SQL ...



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