Tag Info

Hot answers tagged

15

Data alignment and storage size Actually, the overhead per tuple is 24 byte for the tuple header plus 4 byte for the item pointer. More details in the calculation in this related answer: Use GIN to index bit strings Basics of data alignment and padding in this related answer on SO: Calculating and saving space in PostgreSQL We have three columns for ...


15

There is dynamic SQL, so no cache plans, meaning plans generated every time Not necessarily true. Dynamic SQL can (and does) use cached plans just as well as static SQL. For dynamic search conditions resolving to dynamic SQL is oft the right answer. See Dynamic Search Conditions in T-SQL for more details. There is an INSERT SELECT pattern, so table ...


11

To answer your main question directly, the sorts are there to present rows to update operators (performing deletions in this case) in index key order. The principle at work here is that sorting on the keys will promote sequential access to the index. This can be a good optimization, though the details depend on your hardware, how likely the affected pages ...


10

In addition to Craig's advice I would like to advise you to examine the storage parameters of the affected tables. I am currently in a similar situation to yours. The largest table in my system contains ~200 million records and the performance was really bad. Tune the storage parameters of your tables and indexes Besides adding several indexes to the ...


8

The reason for this behaviour is that rows where LD is NULL cannot be found in the index. Therefore Oracle has to scan the full table. If the table is created with LD as a NOT NULL column then the optimizer uses this information and does an INDEX FAST FULL SCAN. If you add a "CHECK(LD is not null)" constraint to the table that has not NOT NULL defined for ...


8

Fulltext isn't going to help without refactoring to use the full text functions ( CONTAINS, FREETEXT or their table equivalents ). It also doesn't really work with leading wildcard. Hacks are available, but basically you're going to struggle to write a semantically equivalent query for fulltext. For the future consider redesigning for fulltext which has ...


7

CXPACKET is never a cause; it gets all the blame, but it's always a symptom of something else. You need to catch these queries in the act and figure out what "something else" is. It might be different from query to query, and turning off parallelism altogether is - as you've suggested - unnecessary overkill in most cases. But it is often the least amount of ...


7

There is rarely any need, point or benefit trying to micro optimise star schema queries with non-clustered indexes laden with included columns. Fact tables are built to be scanned. The indexes you've created in your examples are subset copies of the parent table, which are being scanned (no seeks). The minor performance improvements come from scanning ...


7

Your query is no faster with the index because SQL Server has determined that it would be more efficient to do a Clustered Index Scan, than use the IX_ActCost_ScenarioID that you have defined and perform a Key Lookup to retrieve the extra data needed. As you've only defined the index on ScenarioID, with no INCLUDE columns, each extra column you wish to ...


7

By default the PK is clustered and in most cases, this is fine. However, which question should be asked: should my PK be clustered? which column(s) will be the best key for my clustered index? PK and Clustered index are 2 differences things: PK is a constraint. PK is used to uniquely identify rows, but there is no notion of storage. However by ...


6

Deferred indexing would be nice, but isn't currently supported. Adding indexes has a cost - write performance. They're a trade-off. COPY won't help much if index maintenance is the main issue. The simplest solution is to drop the indexes, and re-create them when you're done importing. Since you can live with losing all your data if the DB crashes, you ...


6

For insert performance, see speeding up insert performance in PostgreSQL and bulk insert in PostgreSQL. You're wasting your time with JDBC batching for insert. PgJDBC doesn't do anything useful with insert batches, it just runs each statement. Use COPY instead; see PgJDBC batch copy and the CopyManager. As for number of concurrent loaders: Aim for a couple ...


6

The indexes that were created and dropped by DTA were most likely hypothetical indexes - ones that DTA creates while running to perform its analysis and should (but doesn't always) delete once done. These hypothetical indexes can be created even if you weren't looking at tuning that specific table! I'd recommend you query some of the system DMVs directly ...


6

Have you seen this? https://community.oracle.com/thread/889338?start=0&tstart=0 It states you can only exchange partitions from a partitioned table to a non-partitioned table, or vice-versa. You'd need to temporarily exchange the partition into an interim, non-partitioned, table, then into the target partitioned table. There are some interesting ...


6

Collations in SQL Server determine the rules for matching and sorting character data. Normally, you would choose a collation first based on the comparison semantics and sorting order the consumers of the data require. Humans generally do not find that binary collations produce the sorting and comparison behaviours they expect. So, although these offer the ...


5

It sounds like your transaction log probably has a lot of VLFs on it which causes the SQL Server to take a hit when doing the transaction log backups. You can find this out by running use {MyDatabase} GO DBCC LOGINFO GO The important part to look at is the number of rows which are returned. If there are more than about 100 rows returned then you've got ...


5

You say that performance degrades with the number of executions and that "restarting the deployment" fixes it. I'm unclear what you mean by that particular phrase but presuming that it involves stopping the loop and then restarting it after some period then one possibility is ghost records. I created a table as below (with one row per page for easier ...


5

The default Pg config is almost certainly sub-optimal, it's a conservative default intended for "will run anywhere" not "will be fast". There's lots to be said on that; see http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server, http://wiki.postgresql.org/wiki/Performance_Optimization, and Greg Smith's "PostgreSQL 9.0 High Performance". (By way of ...


5

In addition to Mark's excellent answer, there are a few other strategies you can add on to your existing system (this is not an exhaustive list, of course): Pre-aggregated tables, or indexed views. This will physically materialize the results (or intermediate results) of the query, so SQL Server will end up scanning much smaller indexes to return the full ...


5

Your query_cache_min_res_unit is 4KB and you have 6GB of query cache free ? Look at this 6 MB = 6144 KB 6144 KB divided by 4KB/query = 1536 queries. You can potentially fit up 1536 small query results in that 6GB of free space in the query cache. That 6MB is most likely being viewed as memory fragmentation. Why ? According to the MySQL Documentation on ...


5

Just call pg_database_size(dbname) to know the size of the database. VACUUM (without the FULL clause) does not free any space, it only marks it as reusable, and thus will not change the database's size (except in a rare boundary case, see Routine Vacuuming). ANALYZE does statistical sampling and would be useful if you needed the row counts, but for the ...


5

PostgreSQL relies on the operating system's disk cache for most caching. This cache is usually reported as "free" RAM by most tools, because modern operating systems use all but a little bit of the currently-free RAM for disk cache. This is normal. To confirm, use a better tool that shows buffers/cache separately from truly free memory. On Linux, free -m ...


4

Typically to optimize inserts/deletes for such a table you would cluster on the datetime column. (You are tracking when these events happen, I presume.) This way, inserts go to the end of the table always, minimizing the "bad" kind of page splits. Deleting or archiving data is easy because the clustered index will support range deletes, and these ...


4

If you wish to see the actual execution plan of a query that is running. SELECT plan_handle FROM sys.dm_exec_requests WHERE session_id = [YourSPID] First then enter the result into this query. SELECT query_plan FROM sys.dm_exec_query_plan (Enter the result here.) That will show you actual execution plan that sql used for that query. You could use that ...


4

The table locks on MyISAM can be a killer and migrating to InnoDB is probably one of the best things you can do to continue to improve scalability. Of course, your change to innodb_buffer_pool_size won't impact tables that aren't InnoDB. One problem, however, is that the version of InnoDB in MySQL 5.0 is still quite primitive compared to later releases, ...


4

You can use the below query to pull currently executing requests and the corresponding session/connection information: select r.session_id, s.login_name, c.client_net_address, s.host_name, s.program_name, st.text from sys.dm_exec_requests r inner join sys.dm_exec_sessions s on r.session_id = s.session_id left join ...


4

Actually, I think the original works better. Having "or" clause in where statements usually slows down performance noticeably. And I don't think it's true that the dynamic sql query gets reevaluated each time. As far as I remember, the DBMS will cache the query plan based on query text. But if you modify even one space in it, it will get reevaluated. So, ...


4

but using the "limit" keyword cant prevent the search over the whole set of data(is it correct?) Correct; it'll just limit how many results are returned. For some kinds of query it can also limit how many are scanned in the first place, but you can't rely on that in the general case. does PostgreSQL database have to create this 1M search result ...


4

First and foremost thing, SQL Server in picture is SQL Server 2008 R2 RTM which is not supported by Microsoft in any way please apply SQL Server 2008 R2 SP3 to at least get extended support I analyzed and found that one of the User DB A was using 8 GB RAM I don't think there is any issue because of this, this is totally normal looking at this you ...


4

The question is mainly about how to optimize the select statement: SELECT [TABLE], [FIELD], [AFTER], [DATE] FROM mytbl WITH (NOLOCK) WHERE [TABLE] = 'OTB' AND [FIELD] = 'STATUS' Removing the redundant projections and adding the presumed dbo schema: SELECT [AFTER], [DATE] FROM dbo.mytbl WITH (NOLOCK) WHERE [TABLE] = 'OTB' AND FIELD = 'STATUS'; Without ...



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