Tag Info

Hot answers tagged

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


10

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

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

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


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

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


6

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 Also read about the basics of data alignment in this related answer on SO. We have three columns for the primary key: PRIMARY KEY ...


6

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


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


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

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

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

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

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


4

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 6GB is most likely being viewed as memory fragmentation. Why ? According to the MySQL Documentation on ...


3

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


3

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


3

I would say your best bet is either extended events or a trace. Unfortunately I don't know enough about extended events to give you any advice there but I can give you some about setting up a trace. First of all a trace can pull, among other things an hostname, which is the server/workstation the query came from. Include it since sometimes the username ...


3

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


3

If the volume of data you're retrieving is large - roughly, millions of narrow rows, or hundreds of thousands of wide rows, or tens of thousands of rows with large objects - you should probably cache the data in a temp table and reuse that. If you're performing significant processing to get the data - such as a table scan - you should probably cache it. ...


3

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


3

The preemptive_xe_* wait types are associated with Extended Events from what I understand and can find. Considering that and your first sentence: We currently use a monitoring tool which shows us our top wait stats either by number of waiting tasks or total wait time. I would start looking at your monitoring tools as the culprit. However being that ...


2

So, from the plans I see one thing: you index is either bloated (then alongside with the underlying table) or simply isn't really good for this sort of query (I tried to address this in my latest comment above). One row of the index contains 14 bytes of data (and some for the header). Now, calculating from the numbers given in the plan: you got 500,000 ...


2

In this article someone looked at the performance of an index and the ordering of the data in the index. Summary As we have shown creating an index in ascending or descending order does not make a big difference when there is only one column, but when there is a need to sort data in two different directions one column in ascending order and the ...


2

Unfortunately you cannot do this with Extended events unless the data from XE can be converted into the format that DTA uses or can be stored in the tables generated by DTA. Not recommended though but Trace is is still available, so can be used if you want. You can even use DMV's for finding missing indexes, etc. Also worth mentioning Aaron's post : Don't ...


2

I've seen this dozens of times. It was always because the statistics for the table became progressively out of date. If this is your case, updating the statistics or reindexing the table should bring performance back up. It's also possible that getting your batch to recompile, by whatever method, would help.


2

OK, here is a cheap way, but it is not what I really want (I really want nice charts and graphs!): -- First PASS DECLARE @First BIGINT DECLARE @Second BIGINT SELECT @First = USER_SEEKS FROM sys.dm_db_index_usage_stats where object_id=14473333 AND index_id = 1 WAITFOR DELAY '00:00:15' SELECT @Second = USER_SEEKS FROM sys.dm_db_index_usage_stats where ...



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