Tag Info

New answers tagged

0

In a general sense, fragmentation is caused by: Data inserted out of order (the order of the index in question). Using ever-increasing values (e.g. an IDENTITY field) is one way to mitigate this as rows will always be created at the end of the index (assuming an ASC sort order). FILLFACTOR is another means of reducing fragmentation when not using an ...


0

Since the data is updated constantly, you will have a hard time getting the benefits from index-only scans. They are mostly effective on read-only or read-mostly data. If that is the case, adding columns to the index will likely be counterproductive. If any of the constant stream of updates were into previously-unindexed columns, those could have ...


7

A non-clustered index scan may be chosen in this scenario: the optimizer determines that it is cheaper to scan all rows rather than perform seeks/range scans the non-clustered index is "skinnier" than the clustered index the non-clustered still covers the columns needed by the query (or it covers enough of them and a lookup for the remainder is still ...


3

To answer more concisely than the existing answers: REORGANIZE produces the same amount of log with SIMPLE and FULL. It's just that SIMPLE almost immediately makes that space available again. Except if something is preventing log truncation such as an open transaction (unrelated to the session the REORGANIZE runs in).


1

You missed the exact point what books on line was trying to mention it states that The following index operations require no additional disk space: ALTER INDEX REORGANIZE; however, log space is required. If you read complete article it was trying to point out the commands or operations in SQL Server which would require additional disk space and ...


1

Database recovery mode SIMPLE never means that log will not be created. SIMPLE recovery model just ensure automatically reclaiming of log space once operation is complete/committed. ALTER INDEX REORGANIZE operation creates log and for this it needs log space regardless of database RECOVERY model. Learn more about recovery models in SQL Server ...


0

Your data is cached, this is why you are noticing a performance difference. Assuming you are in a development environment, issue these commands before running your query: dbcc freeproccache dbcc dropcleanbuffers Run the above commands to clear the cache, then run your query before indexes. Apply your indexes, run the two commands again, then run your ...


0

Since you have dropped your index and probably stats aren't up to date I'd suggest running ANALYZE against a table to get valid execution plan. MariaDB - ANALYZE TABLE


1

Most likely the statistics on the field were out of date, adding the index create/updated statistics with a full table scan. More info on statistics https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/engine-independent-table-statistics/


0

Even thouth the docs, states that all fields are synchronized between the parent table and its child (copied before). The optional INHERITS clause specifies a list of tables from which the new table automatically inherits all columns. Use of INHERITS creates a persistent relationship between the new child table and its parent table(s). Schema ...


0

From the docs: "LIKE....Unlike INHERITS, the new table and original table are completely decoupled after creation is complete. Changes to the original table will not be applied to the new table"


3

It's a common optimization to use UNION in these cases (at least when you must use a single query): select * from users where name='smith' /* using single index */ UNION select * from users where nick='smith'; /* using single index */ In my experience, I don't like to rely on index_merge (union) because the performance is usually not as good as doing the ...


1

You can't go wrong with Ola Hallengren's award winning solution. It's pretty awesome ; https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html You can schedule this to run once a day to begin with, and then you should re-evaluate your needs based on how busy the solution actually becomes regarding the needed index maintenance on your ...


1

I would suggest that you use Ola Hallengren's scripts, freely available and widely used. His website is: https://ola.hallengren.com/ •DatabaseBackup: SQL Server Backup •DatabaseIntegrityCheck: SQL Server Integrity Check •IndexOptimize: SQL Server Index and Statistics Maintenance He has the best practices coded into his scripts, so it should serve you ...


5

Don't re-invent the wheel; just get a solution like Ola's in place now: Ola Hallengren's SQL Server Backup, Integrity Check, and Index and Statistics Maintenance You can tweak the settings and learn how it works over time.


1

From taking a quick look, it's quite possible that you're actually looking at indexes on different databases. Alternatively, you could have a partitioned table as the docs state that sys.dm_db_partition_stats: Returns page and row-count information for every partition in the current database. Take it back to basics, don't use DB_ID() and actually ...


1

The big reasons for the "Keep the clustered key sequential" advice is all around insert/update/delete operations and fragmentation. As you make changes to the values (insert new ones out of order, updating an old value that causes it to move, deleting a value) SQL has to move data around on the pages and do page-splits to keep the data in order (the whole ...


2

In MySQL, a foreign key requires an index. If an index already exists, the foreign key will use that index (even using the prefix of an existing multi-column index). If no index exists, defining the foreign key will build the index. So the size increase and time to create a foreign key is about the same as to create an index on the same column(s). The ...


2

If I run your example with a cold cache for both queries, then the bitmap index scan really does win out. So in this sense, the planner is getting it correct. Since you are testing under a perfectly hot cache, the correct thing to do would be to lower the random_page_cost and the seq_page_cost both to zero, and if I do that it does select the index scan ...


0

First - members here will be much more likely if you include DDL for the missing tables: Server_US and ItemPrefix_Text at least. Second, use double quotes instead of apostrophe's; it's ANSI standard and allows analysis on other than MYSQL RDBMS's. Next, In the absence of an index MAX("TradeOffer_US"."PriceTotalSilver") will perforce have to scan all rows ...


0

Two independent indexes cannot be efficiently combined like that. If your theory is correct that computing the area size is the slow step, then storing the area size in a column in the offices table would help a lot. (You could use a trigger to automatically populate area size column from the area column). The functional index you tried doesn't work, ...



Top 50 recent answers are included