New answers tagged

6

Reasoning When comparing the column of the non partitioned table against the partitioned table, sql server will not be able to know what DayOfMonth will hold, even if all of them are 15. As a result it would not know which partitions to return to satisfy this filtering when joining the two tables. A different example to give some more insight can be ...


0

Workaround: create or replace view vw_list_indexes as select i.relname as indexname, t.relname as tablename, t.OID, idx.indisprimary from pg_class i join pg_index idx on idx.indexrelid = i.oid join pg_class t on t.oid = idx.indrelid where i.relkind = 'I'; select * from vw_list_indexes where oid = 'schema.table'::regclass;


0

Single-table multi-tenant is logically what I am describing. However from reading about it I get the impression that "tenant" refers to clients / entities whereas what I'm describing is a general data access strategy. Multi-tenancy is a general data access strategy, in that it allows multiple tenants of the same database to use it as if they were the ...


0

This is obviously a bug. Following the bug thread, I tend to concur with David Rowley's opinion: It should not be allowed to create UNLOGGED partitioned tables. You can always create an UNLOGGED table and attach it as partition to a table. So your solution would be to set all partitions to LOGGED.


0

If all you want is to remove the partitions from the schema view, you could simply move the partitions to another schema: ALTER TABLE child_01_2013 SET SCHEMA otherschema; Everything will continue to work as before, but the visual clutter will be gone.


0

I don't know for dbvisualizer. For pgAdmin4 (tested on version 4.11) the partitions already show up in a folder under the main table in the object tree. Unless you open the folder, you aren't going to see them. You can even hide that folder altogether from the pgAdmin4 preferences setting, if you want.


8

Since you're on SQL Server 2016, it's worth mentioning that there is at least one public bug fix for parallel deadlocks involving columnstore indexes: FIX: A deadlock occurs when you run a parallel query on a clustered columnstore index in SQL Server 2016 and 2017 (thanks to Denis Rubashkin for providing the link initially) This was released as part of ...


2

Have you checked following blog on Intra-Query Parallel Thread Deadlocks The SyncPoint resource indicates the use of an exchange event if I'm not mistaking. Looking at the participants of your deadlock you can see that they are all coming from the same spid(55) and batch(0), but are using different threads. This indicates that they are all part of the ...


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


0

These queries may be helpful to you if you are looking for details in the FG's --Find partition schema and partition function details on a DB SELECT t.name AS TableName, ps.name AS PartitionScheme, ps.data_space_id, pf.name AS PartitionFunction, pf.function_id FROM sys.TABLES t JOIN sys.indexes i ON t.object_id = i.object_id JOIN sys.partition_schemes ps ON ...


1

You have to clean the files before removing them. Using command: USE [YourDB] GO DBCC SHRINKFILE (N'YourFileLogicalName' , EMPTYFILE) Using SSMS: Choose your file and turn option: Empty file by migrating the data to other files in the same filegroup.


1

You need to remove all allocations from that filegroup. That includes indexes, tables and partitions that resides on that filegroup. Then you can remove the files and finally the filegroup. For just a file and to keep other files for the same filegroup (not what I think you're asking about), then you can shrink that file using the EMPTYFILE option and then ...


Top 50 recent answers are included