11
Based on the data that you have provided -
there are 67 total pages. Index fragmentation for such a small table would not affect the performance. I would not worry about index fragmentation for the table that you have mentioned.
You should update your table statistics so that sql server can generate better query plan.
I would start my troubleshooting by ...
10
One quick way I can imagine is creating a table with UNIQUEIDENTIFIER as a primary key and inserting lots of random values. This could be achieved using this script:
CREATE TABLE dbo.Tests (Id UNIQUEIDENTIFIER PRIMARY KEY);
GO
INSERT INTO dbo.Tests (Id)
WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
SELECT NEWID()
FROM x AS ...
9
With a trivial SELECT COUNT(*) FROM dbo.YourTable; query on a disk-based table, SQL server counts rows by scanning either the heap or leaf nodes of an index. The cost-based SQL Server optimizer chooses the narrowest one available in order to minimize the number of pages scanned.
This example shows the behavior with an index scan.
CREATE TABLE dbo.YourTable(...
9
I'd run DBCC UPDATEUSAGE against the table as a first step, since the symptoms show inconsistent space usage.
DBCC UPDATEUSAGE corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index. If there are no inaccuracies in the system tables, DBCC UPDATEUSAGE returns no data. If inaccuracies are found ...
5
You can't compare performance of:
WHERE color_code in ('red','green') and sale_date = '1970'
with:
WHERE color_code = 'red' or color_code = 'green' and sale_date = '1970'
because they are not logically equivalent (will return different results). A trivial example:
with T (color_code, sale_date) as (
values ('red', '1970'), ('green','1969')
)
...
4
You use a nonequality predicate so your "seek" operations are in fact scans which just start from some value (not from "first") and then go to the end of the clustered index leaf level.
In the other hand you return only one column which is the clustered index key so using any of indexes won't get any key lookup operations. The optimizer has to estimate what ...
4
Indices are bound to fragment in course of usage (time). What matters is if it is affecting the performance.
Usually we do not worry about of indices with page_count < 500. (We as in, my team)However,there is not hard and fast rule for a particular page_count.In my previous shop we had set the limit in our Ola script for 1000 page count. But yes with ...
3
I wanted to make several "Ugly" indexes, so I did the following. It worked well
-- Create databases to test index job, each database is about 800MB with 100,000 GUID primary keys, in each of two tables
-- Create 6 database to test index job for DatabasesInParallel Database design based on example https://dba.stackexchange.com/q/9821/21924
--Drop last test
...
3
Without messing with the catalogs (which is not commendable), the only option I can think of would require you to do without foreign keys for a while:
You can define a second UNIQUE index on the table that contains the appropriate INCLUDE clause.
If you use the CONCURRENTLY clause of CREATE INDEX, that shouldn't be disruptive.
Then you can delete the ...
3
This seems like a rather esoteric thing, which is probably why it doesn't get recommended all that much. I do recommend it when the issue comes up, which just isn't all that often. The size of one index is unlikely to be all that meaningful in the context of an entire database, so making it smaller usually isn't worth worrying about a great deal.
I don't ...
3
Column statistics are created automatically as needed by SQL Server when the AUTO_CREATE_STATISTICS database option is ON and also by an explict CREATE STATISTICS statement.
With the AUTO_CREATE_STATISTICS database option, the query optimizer will create single column stats during query compilation as needed on columns specified in query predicates when a ...
3
If auto update statistics is not enough for your DB, try a couple of things:
Manually update statistics - recommend Ola Hallengren's scripts that can be configured to maintain indexes and/ or statistics as required. Test different parameters (all/ indexes only/ sample rate etc) and test running daily/ weekly/ at start of proc and the like, and see what ...
3
Main difference between your Test server and Live server is Concurrency.
You haven't done Concurrency Test in your Test server.
Concurrency is one of the main reason for Live Site to be slow.
What code is written in Front end application like
what you have written in connection string . Like connection pool,whether connection is properly close after ...
2
How fast do you expect this to be? It is taking about the amount of time I would expect (a little slower, but that is probably your hardware). The query may be simple to specify, but it handles a large amount of data.
Most of the time of the nested loop is taken up by waiting for its first child (the hash join) to returns its results. The nested loop ...
2
It's important to know what sort of queries you are running. Are they mostly based on the time of the record, or something other field? That will inform what sort of approach you should take here. Maybe your indexes should actually be multi-indexes for example.
What's your EXPLAIN statement say for your queries? That's really one of the best ways to see ...
2
SQL Server optimiser uses statistics to generate execution plans. The more accurate statistics the better plans it can potentially generate. By default statistics automatically updated only if more than 20% of the records have been changed. What does it mean in practice? Let's say you have a table with 10 months of data and statistics was just recalculated. ...
2
The extension you need is btree_gin and not pg_trgm.
I would say that the biggest drawback is that GIN indexes are slower to update than B-tree indexes (which is why the fastupdate option was invented).
If that is fine with you because you have few data modifications, and query speed is more important, use the GIN index (but then I would recommend ...
2
Just use the sys.columns table:
SELECT name, is_sparse, is_column_set
FROM sys.columns
Column sets are:
Tables that use sparse columns can designate a column set to return all sparse columns in the table. A column set is an untyped XML representation that combines all the sparse columns of a table into a structured output. A column set is like a ...
2
gin index type is about 10 times smaller and about 3 times as performant as the default btree index (the performance was tested using SELECT queries).
and:
it's 30GB for btree and 3GB for gin
Yes, that speaks heavily in favor of the GIN index.
OTOH, this:
repeated about 100 times on average.
and this:
I think I won't ever UPDATE the other_id ...
2
The index does not match exactly what already exists. The Clustered index (the primary key) contains all the other data related to the table. But the nonclustered index (NCI) contains just the primary key. The resulting NCI is much narrower than the clustered index.
Having a non-clustered index on just the primary key has a few benefits that may be ...
1
Try to use the index by an expression:
CREATE INDEX idx_name ON alarm (gDateTime, (DATE(alarmDateTime)))
If It will not help then add generated column and index which uses it:
ALTER TABLE alarm
ADD COLUMN alarmDate DATE AS (DATE(alarmDateTime)) /* VIRTUAL */ ,
ADD INDEX idx_name ON (gDateTime, alarmDate)
Then use new column instead of expression in your ...
1
date(alarm.alarmDateTime)
MySQL cannot use indexes for any column 'hidden inside a function call'.
Anyway, in
Where alarm.gDateTime>'2019-08-01'
group by ...
The most you can get is the use of INDEX(gDateTime) to help with the filtering. Even so, if too large a percentage of the table is needed, that index will be ignored.
OTOH, a "covering index" ...
1
Usually, Index fragmentation happens when there is Update or Insert operation happens on the table.
If you want quickly produce the issue (Index fragmentation), create an Index in your test table with less fill factor and do heavy Update or Insert operation on that table. You can work with these scripts..
1
SQL server may choose to perform an in-place update or an insert and a delete depending on many factors and you shouldn’t worry about it.
Since the keys are not being updated I would leave them out of the SET clause to help the optimizer make the best decision.
If you are using SQL Server 2008 or later, look at the MERGE statement which can replace both ...
1
You can use dynamic SQL with a do statement that iterates over the tables in a schema:
do
$$
declare
l_statement text;
l_rec record;
begin
for l_rec in select y
from x
loop
l_statement := format('create index on %I (z)', l_rec.y);
execute l_statement;
end loop;
end;
$$
Postgres will automatically generate a name for the new ...
1
I fixed it by adding the feed_id field to the ORDER BY clause like that:
mysql> EXPLAIN SELECT a.id FROM articles a WHERE a.feed_id IN (6826,6827) AND a.date < 1564469723.424363 ORDER BY a.date DESC, a.feed_id DESC LIMIT 20;
+----+-------------+-------+-------+-------------------+--------------+---------+------+------+----------------------------------...
1
You cannot. These are different operations, and only one of them is supported by the index. If you were to swap the order of the arguments as well as the operator itself, then it would be the same operation and the index would still support it.
Although it might be possible that you could modify the source code of pg_trgm to support this other operation. ...
1
I think the difference in timing you see is simply a caching effect, based on which query you ran first. It is probably not a real difference caused by how you specify the query (Although as Lennart described, your queries are not really equivalent as you are missing parentheses around the OR part--although all your rows seem to meet sale_date = '1970' ...
1
If the only reason for the index is the above query, then a partial index would better:
CREATE INDEX my_index ON my_table(time desc, service_name) WHERE status='ok';
VACUUM ANALYZE my_table; may help too.
The query plan depends on actual data in the table. In a table with few or zero records a seq scan is cheaper.
If you really dont want the seq scan (...
Only top voted, non community-wiki answers of a minimum length are eligible
