Tag Info

Hot answers tagged

3

It looks like SQL Server is generating a parameterized query plan that can work for any value of @CustomerPartitionKey. In order to do so, it seems to treat @CustomerPartitionKey as both a partition and a column you are seeking upon. If we take a look at the query plan where we have the bad estimate (3000 rows estimated, 300000 actual), we see that there ...


3

I can reproduce the bad plan. I found three workarounds: OPTION (RECOMPILE) INNER LOOP JOIN hints A nasty, crazy rewrite: . SELECT y.* FROM (VALUES (@CustomerPartitionKey)) x(CustomerPartitionKey) CROSS APPLY ( SELECT --TOP 300 CI.ContactId, I.Ordinal, I.Identifier FROM #identifiers I INNER JOIN ...


3

Be aware of the implications in regions that have the nasty habit of changing their clocks twice a year and Check this Link


2

A few details are still not clear, but let's see, what we can do now. First, having about 100M rows and 231 partitions sounds not that good. The resulting tables will be too small, in turn their number too high - I cannot tell the threshold, but at some point the query planning migt get too expensive. I think it is quite possible that yearly partitions ...


2

Assuming we are talking about 1:1 relationships among all tables. Overall storage is practically always (substantially) cheaper with a single table instead of multiple tables in 1:1 relationship. Each row has 28 bytes of overhead, plus typically a few more bytes for extra padding. And you need to store the PK column with every table. And have a separate ...


1

Oracle said it wasn't a known issue, and basically asked us to re-run with tracing on. Unfortunately on a large production database, and given large downtime required to rollback last time, this wasn't really an option. We had suspicion that it is related to this bug fixed in 11.2.0.4.2 Bug 17325413 - Drop column with DEFAULT value and NOT NULL definition ...


1

In addition to Rolando's suggested schema and my improvement on RENAME, you may want two more things: Purge "old" data via DROP PARTITION. This is about the only real use for moving to partitioning. Add a new partition using ALTER TABLE REORGANIZE PARTITION p99999999999 INTO PARTITION p2016_01_jan VALUES LESS THAN ('2016-02-01'), PARTITION p99999999999 ...


1

Here are the Steps to Partition Your Table by Month Create a Temp Table, Partitioned and Indexed on LogDate CREATE TABLE paramlog_ems_new ( SiteIndex smallint(5) unsigned NOT NULL, RegionIndex smallint(5) unsigned NOT NULL, OrganizationIndex smallint(5) unsigned NOT NULL, DeviceSlaveId smallint(5) unsigned NOT NULL, UserIndex smallint(5) unsigned ...



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