Tag Info

Hot answers tagged

6

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


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.


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


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


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


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


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


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


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/



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