what does partitioning means, its advantages and disadvantages.?

Problem: what does partitioning means, its advantages and disadvantages.?

Solution:  Partitioned Tables and Indexes

Partition: Partition is a unit of data which is created based on grouping a range of values in a column of a table or index. The data of this units from a Partitioned table in a database can be stored across into different file groups. From a query perspective, the inserts and updates the table or index is treated like a single entity.

Partitioning is generally available in Enterprise Edition or Developer Edition and starting from SQL Server 2016 SP1 the partition functions seems to be available in standard edition as well. Based on this blog article sql-server-2016-service-pack-1-sp1-released

NOTE from Microsoft in terms of number of partitions supported:

SQL Server 2016 supports up to 15,000 partitions by default. In versions, earlier than SQL Server 2012, the number of partitions was limited to 1,000 by default. On x86-based systems, creating a table or index with more than 1000 partitions is possible, but is not supported.

Table Partitions can be helpful when there are large number of rows in a table or index. Consider an oil and gas company, where there is a sales table or invoice table. In which you have transactions happening all over the country and the data sets are retrieved from all over the country to view the transactions using a reporting server or some other front end data visualization application. If you break the table into some n number of partitions, it becomes easy for the inserts as the locks would only be the partition level rather than the entire table.

As the Partitions of a table can be separated on to multiple file groups, the daily accessed data where quick retrieval is required can be stored in a faster storage drives and the other set of partitions where the data won’t be accessed all times can be on a slower storage drives.

Generally partitions are found from sys.partitions

Below is the query to get list of all the partitions, indexes, tables, rows

SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]

FROM sys.partitions p

INNER JOIN sys.objects o ON o.object_id=p.object_id

INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id

LEFT  JOIN sys.tables t ON t.object_id=p.object_id

WHERE t.is_ms_shipped=0

 Advantages:

• Faster transfer in terms of loading data from application DB(OLTP) to Analytics DB(OLAP)
• 
  
• you can choose to compress data in one or more partitions or rebuild one or more partitions of an index.
• I/O operations can be speed up by improving data storage for quick access data into a separate file group partitions.
• It can improve performance by enabling lock escalation at the partition level instead of a whole table. This can reduce lock contention on the table.

Disadvantages:

•   Slows down DBCC commands
•  Number of partitions and process cores needs to same in number for maximum performance
• 16 GB RAM is required if large number partitions are in use and DDL, DML may fail if it doesn’t have enough memory. More memory for less problems
•  Index operations can take a while like rebuild or reorganize will slow down as there are number of partitions in the index.
• Depending on the columns in the partition if the columns outside of the partition function are selected or searched, this can take longer than expected as it must search all the partitions.