New answers tagged

3

We now have a table with over two billion rows and 700GB of data. It's a small number of columns, one being a 256 char varchar used to store serialised blob data. This is now of a size that when people try to insert, delete and update to it (via the app) users occasionally lock each other out. This can only get worse as time goes on. Why do ...


-2

Take Backup of your database. Change Recovery Model of database to Bulk_Logged Identify Identity or datetime value, before which you want to delete data SET ROWCOUNT 10000 -- SET TO DELETE SMALL CHUNK OF DATA AT A TIME WAITFOR DELAY '' --DEFINE DURATION TO AVOID INTERUPTED/SLOW PERFORMANCE OF APPLICATION QUERIES DELETE FROM TABLE_NAME WHERE ...


2

There are a few problems here: First, as jkavalik says in the comments on the OP, the order of columns in an index matters. Basically, in your case for index_rqcd to be used for filtering on rq_date, t_id has to be used before it can "see" and filter on rq_date. Since usually only one range scan on an index can be done for a query and it has to be the ...


6

Which of the following primary keys (the column order is different) is preferred? Like all indexing decisions, much depends on how the table will be queried. All partitioned indexes (for SQL Server 2008 and later) have the partition ID (not partitioning key value) as a hidden leading key column in each partitioned index, so the effective competing ...


1

So, to test query performance for SELECT statements you'll want to leverage statistics and look at the query plan. Your statistics (using SET STATISTICS IO ON, and SET STATISTICS TIME ON) will show you the number of logical, physical, lob reads and the CPU and elapsed times of the query. When you compare partitioned versus non-partitioned results look for ...


3

at no point while processing do we JOIN on or include date in WHERE clauses All partitions will need to be touched when the partitioning column is not specified in query predicates. Put yourself in SQL Server's shoes - how would you know which partitions to access (or not) without knowing the partitioning column value? Consider that a singleton ...


0

The primary key should be just the ID field. There should be an index over ┬┤date┬┤, since you use it for archiving and reporting. I would even consider making it the clustered index. Foreign keys are not a must if there is only one application with write privileges to the database and no user is allowed to make manual changes. Partitioning features are not ...


0

There is a new tool called pg_pathman (https://github.com/postgrespro/pg_pathman) that would do this for you automatically. So something like the following would do it. SELECT create_range_partitions('master', 'dt_created', '2015-01-01'::date, '1 day'::interval);



Top 50 recent answers are included