When I test TPC-E I find a interesting issue. If I use one file group and put all data and log files in the same file group for the large tables the tpsE result is 750 with 100% cpu utilization. If I use 10 partitions for the top 5 large tables using same partition function and scheme, including index partitions, the tpsE is only 645 with only ~50% CPU UT. I also find the physical disk queue length and avg sec/transfer are <12, 6ms vs >450, 68ms respectively. That means the bottleneck is transfered from CPU to disk IO. The wait type are almost all PAGEIOLATCH_SH or PAGEIOLATCH_EX. Now all partitioning file group are put to one LUN and I will try multiple LUNs. But I still can imagine the performance will not be better.
large table with row number
tpce.dbo.CASH_TRANSACTION -- 1133213691
tpce.dbo.HOLDING_HISTORY -- 1650647232
tpce.dbo.SETTLEMENT -- 1231739748
tpce.dbo.TRADE -- 1231782525
tpce.dbo.TRADE_HISTORY -- 3110403022
partitioning functions and scheme
CREATE PARTITION FUNCTION ufn_tpce_broker_separater(bigint)
AS RANGE LEFT FOR VALUES (200000165000000,200000330000000,200000495000000,200000660000000,200000825000000,200000990000000,2000001155000000,2000001320000000,2000001485000000)
CREATE PARTITION SCHEME sch_tpce_broker_separater
AS PARTITION ufn_tpce_broker_separater TO
(broker_fg, broker_fg2, broker_fg3,broker_fg4, broker_fg5, broker_fg6,broker_fg7, broker_fg8, broker_fg9,broker_fg10)
Let me know your storage configuration, SQL server version and query pattern (to find lowest element to partition data) ? I had experianced a better performance using data partition for a database of size 600 GB.
Ananth Ramasamy Meenachi (www.msarm.com)
Interesting.. is this SQL Server 2005? If so, have you looked at the degree of parallelism you are getting? If your table is partitioned, SQL Server 2005 will only use one thread per partition if your query touches more than one partition (see Data Warehouse Query Performance, jump to the section starting "Say we have a fact table.." if you are in a hurry). Are your No of files = number of CPUs?
You can get a quick overview of the degree of parallelism you are getting for various queries with the following query:
Elisabeth Rédei | www.sqlserverland.com | MCITP, MCT | http://www.linkedin.com/elisabethredei
Sometime table partitioning beneficial for the increasing performance of server. Its totally depends upon your setup and using queries.
(Database gurus please guide me if I am wrong....)
1) If you have 3-4 major tables in one file and application have lot of trisection over that tables that means the system have lot of IO on that file. If you separate that table into the different file and kept it into different disk (If you have more disk in your environment), that means you are break that IO into disk and got good performance compare with previous.
2) If you are using a quarry that takes 3-4 joints b/w different tables and that tables are located on different files or on disk. In that case it will be beneficial to combine that tables in single file or filegroup.
- Edited by Deepak_Goyal Wednesday, April 01, 2009 10:04 AM
You are correct Deepak!. Previous test used same physical LUN for ten partitions. We I used physical separate LUNs, which means the ten partitions located on different LUNs and different BUS/Enclosures, the performance was increased greatly (tpsE=1220 and response time %tile constraint is satisfied completely) . I also tested metaLUN to stripe the data in storage level, which can stripe data to more disk drives so that the data is more dispersed. As for tpc-e test the read versus write ratio is about 8:1. Therefore RAID level can be take into consideration to advance better IO performance.
So I think the table partitioning can really bring benefits to OLTP application such like tpc-e, but I have to say keep testing before making conclusion;-)