Tag Info

Hot answers tagged

18

The following is just insane ranting and raving... If you leave all data in one table (no partitioning), you will have O(log n) search times using a key. Let's take the worst index in the world, the binary tree. Each tree node has exactly one key. A perfectly balanced binary tree with 268,435,455 (2^28 - 1) tree nodes would be a height of 28. If you split ...


13

You've got a bunch of different questions in here, so let's break 'em out individually. Q: If I join two tables in the same database with the above query, why is it slow? A: For starters, you're not using a WHERE clause, so SQL Server has to build the complete result set, merging both tables together. If you only need a subset of the data, consider using a ...


12

A partitioned table is really more like a collection of individual tables stitched together. So your in example of clustering by IncidentKey and partition by IncidentDate, say that the partitioning function splits the tables into two partitions so that 1/1/2010 is in partition 1 and 7/1/2010 is partition two. The data will be layed out on disk as: Partition ...


11

"This table has the appropriate indecies but is becoming a major hangup when running queries" Partitioning alone doesn't help query performance unless SQL Server is able to eliminate partitions when running a query. Your WHERE clause needs to line up with the way you partition. We only get one field to use as a partitioning field, so if that field isn't ...


11

I do not have a 2005 server to test with. 2008 however, appears to handle this as expected: USE [Test] GO CREATE TABLE [IDRanges]( [ID] [int] NOT NULL ) GO CREATE PARTITION FUNCTION IDRange1 (int) AS RANGE LEFT FOR VALUES (10) ; GO --Add one record to each partition INSERT INTO IDRanges ([ID]) VALUES (17) INSERT INTO IDRanges ([ID]) VALUES (7) GO ...


11

You're right that the SQL Server optimizer prefers not to generate parallel MERGE join plans (it costs this alternative quite high). Parallel MERGE always requires repartitioning exchanges on both join inputs, and more importantly, it requires that row order be preserved across those exchanges. Parallelism is most efficient when each thread can run ...


11

Switch is an extremely efficient way to add or remove data to your table. You can switch in data, which is the preferred way to upload data in an ETL process. By preparing the data in a staging table (completely unrelated to your actual data) you can prepare it w/o concurrency issues (your ETL won't block reporting) and present the new data in one single ...


10

The server spec questions should be directed to either Serverfault or DBA.SE. For the partitioning question, I don't think you necessarily need to partition for this. 360m rows is a lot but it's not too unwieldy. Do NOT under any circumstances try to partition based on the last digit of a field. I'm not sure this would even work, but it's not SARGable ...


9

No. Partitioning does not speed things up. Your table that has 2 million records before partitioning, it will continue to have exactly the same 2 million records after partitioning. If you want to look only at a small subset of the records use an index. It does seem like your data is a multi-tenant schema with the tenant key be the ManufacturerID. In such ...


8

When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition. For example, if a clustered index has four partitions, there are four B-tree structures; one in each partition. Ref. Clustered Index Structures Special Guidelines for Partitioned Indexes You can ...


8

Not at all. One of the most common scenarios for partitioning is to use a date field, which is totally unrelated to your PK. For instance, if you have a table Orders with the field OrderDate you would most likely partition based on the month and year of OrderDate. When records age out and are no longer relevant you can move those partitions off to an ...


7

To answer your second question first: yes you should partition. Oracle's query optimizer has a feature called partition elimination, which will check the predicate for the partition and only execute the SQL on the appropriate partitions. Partitioning also leaves all the data in one space. Conceptually, think of it as many tables of identical structure, with ...


7

Software can't fix this if the underlying IO/disks are opaque to you. If you add partitions, you won't spawn 30 threads If all your partitions are on the same volume you'll decrease throughput I've worked on similar systems where we had a staging DB SIMPLE recovery on staging deferred deletes (eg a new or updated run is an INSERT) that run out of hours ...


7

Partitioning in isolation may be sufficient but you may get better results by combining with partitioned views and multiple tables. It very much depends on the pattern of querying and growth. The current limitation with partitioning is that column statistics are only maintained at a table, rather than partition level. If you have a pattern of querying that ...


7

200 million rows is certainly in the range where you could benefit from table partitioning. Depending on your application, you could bet some of the benefits listed below: Ease of purging old data If you need to clear down records more than (say) 6 months old, you can partition the table on the date and then swap out older partitions. This is much faster ...


7

Methinks a better query is as follows: select object_schema_name(i.object_id) as [schema], object_name(i.object_id) as [object], i.name as [index], s.name as [partition_scheme] from sys.indexes i join sys.partition_schemes s on i.data_space_id = s.data_space_id This looks at the 'proper' place to identify the partition scheme: ...


7

BOL has answer -- It is not ON by default as it may increases the potential for deadlock. The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks. Similarly, page locks are always escalated to table locks. In SQL Server 2008, locking of partitioned tables can escalate to the HoBT level for the ...


7

First things first, I notice that your 'what I do now' query: SELECT TOP (1) ca.SensorValue, ca.Date FROM sys.partitions AS p CROSS APPLY ( SELECT TOP (1) v.Date, v.SensorValue FROM SensorValues AS v WHERE $PARTITION.SensorValues_Date_PF(v.Date) = p.[partition_number] AND v.DeviceId = @fDeviceId ...


7

Easy: you should always use indexes. Partitioning for performance is probably the most misunderstood myth out there. When you partition, the best you can hope for is on-par performance with a non-partitioned table. And yes, that is including partition elimination enhancements. Reducing table scans to partition scans because of missing indexes is simply not ...


6

Short answer: If the clustering key would not be part of the key set then the index uniqueness across all partitions could not be guaranteed. The long story: The only way to guarantee uniqueness is by enforcing uniqueness via a B-Tree structure. With a B-Tree is really easy to enforce uniqueness, since any key has a deterministic logical position in ...


6

What can I expect performance-wise of such a trigger/partition scheme? Context switches mean using triggers is always going to use a lot more CPU than a simple insert. The script below can be used to quantify how much impact that will have - and also demonstrates auto-creating partitions using triggers and compares the performance either way. Please ...


6

This query should give you what you want: select distinct t.name from sys.partitions p inner join sys.tables t on p.object_id = t.object_id where p.partition_number <> 1 The sys.partitions catalog view gives a list of all partitions for tables and most indexes. Just JOIN that with sys.tables to get the tables. All tables have at least one ...


6

You did not explain why you want to partition the table and what do you expect from partitioning. You only mention table size, which is hardly a criteria for partitioning. Performance wise partitioning will make everything slower, not faster. The best you can hope for is on-par performance with the unpartitioned table. Some scenarios that do make sense with ...


6

So you have a huge partition at the end because you forgot to create new empty partitions. You could do this (will be an offline operation): drop all unaligned indexes create an empty staging table, with identical structure as your data table (including all aligned indexes) with the proper constraints to accept the last partition switch the last partition ...


6

When you can identify a row or set of rows already in the table, you can do it like this: SELECT $PARTITION.[PartitionFunctionName](PartitioningColumn) FROM dbo.table WHERE <clause to identify rows>; If you have just a value, and it's not necessarily in the table yet, you can tell what partition it would be in given the current state using ...


6

Yes, it is a valid physical design modification to accommodate more flexible locking behavior. If you join the two tables and expose it to your ORM with a view, I don't think the ORM will know the difference (unless it attempts to look at the view metadata like looking for primary key or something during code generation). Your view should be updatable, and ...


6

Here are two really efficient solutions to this, since you're only moving data. These are efficient because they don't actually move data at all: they simply manipulate the metadata to present the data in the desired location. This means not only will they be fast, but the amount of logging required will be minimal. If you're using Enterprise edition, take ...


6

You can use sys.dm_db_index_physical_stats to identify the size of your partitions and how much space is being used. Use the page_count field to see the size of your partition. If you perform a SAMPLED or DETAILED scan, you will also get a value for avg_page_space_used_in_percent, which will allow you to estimate how much of the space in the partition is ...


6

As the error message says, any partition-aligned unique index has to include the partitioning key in the index key. This requirement exists so the engine can enforce uniqueness on updates without checking every partition. In your case, this means including OrderDate in the nonclustered index key, or having a non-aligned index. Both are potentially valid ...


5

The table is only 7 Gigs. Table partitioning probably won't do much for you. Table partitioning is typically for really large tables. Think 100 Gigs or more. Now if the table will be getting that large then it might help, but if that isn't very likely I would just leave it as is. You probably just need to add any missing indexes.



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