Tag Info

Hot answers tagged

7

I have tried to rebuild the indexes as most of them are sitting at 99%. after the re-build they go to 0. but with in a few minutes they are back at 99%. I guess I know the answer here and have seen many times. Can you please check if auto shrink is on for the databases. You can use below query select name,is_auto_shrink_on from sys.databases where ...


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.


4

To answer your second question: MySQL does not have a parallel query execution engine, so even if you partition the query, you are still single threaded. This will eventually kill your scale. However, you could partition the table by visitor_id. This would allow you to run several queries (one per partition) in parallel, all of them form: SELECT ...


3

Although the syntax for CREATE INDEX supports an option for defining the index as ascending or descending, in the most common two storage engines (InnoDB and MyISAM), this index option is a no-op. There is no such thing as an "ascending" or "descending" index, they are just indexes and can be used for sorting in either direction. But in the case you ...


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

The formats accepted by to_char(timestamp, text) include localized patterns that make it not immutable. Example of different results with the same input: test=> BEGIN; test=> set lc_time='en_US.utf8'; test=> select to_char(now()::timestamp, 'TMDay'); to_char --------- Monday test=> set lc_time TO 'fr_FR.utf8'; test=> select to_char(now()::timestamp, ...


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

First, the possible_keys shows null because there is no index on any of the two columns used in the WHERE clause. So, there is no useful index to even be considered for this query. But even when you add an index on (expirydatetime), the situation is still not good. Because when you place a column inside a function in a condition, like 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 ...


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