February 7, 2013 at 10:28 pm
Hi
Following is the ssenario:
Table Name : Policy_Transactions
Table Size : 225 Gb,
Environment : OLAP (table used for reporting)
DML operation : Only Insert operation; get loaded from another environment.
table Content : contain 80 columns , multiple records against every claim (multiple claim_ID)
Table structure : Heap table with NO index.
Policy_Number
Audit_Trail_ID
Journalization_ID
Program_ID
Product_ID
Producer_ID
Surplus_Line_ID
Policy_ID
Underwriter_ID
Claim_Id
MOP_ID
Insured_ID
Division_ID
Endorsement_ID
Risk_ID
Dat_ID
Coverage_ID
ASLOB_ID
Account Number
Acquisition Costs
Amount
Attachment Point
Base Amount
Calculated Earned Premium
Cash measures - TBD (policy, claims, reinsurance)
Cash Received
Cat Calculated Earned Premium
Cat Percentage
Cat Premium
Commission
Commission Percentage
Credit Amount
Debit Amount
Deductible Amount
Deductions
Deferred Acquisition Costs
Earned Premium
Earning Days in Period
Expenses (Allocated)
Fee income
Fees
Fronting Fee
'
'
'
'
''
'
Start_time
End_time
Index related Questions:
Team needs query to run fast ( asking for indexes) but most of the queries contains start_time and end_time with some ID columns (here policy_id is being used rarely)
1) WHat would be the combination used on clustered index ?
"policy_id + end_time" OR "start_time + policy_id"
2) if i directly make some non-clus indexes like ( Insured_ID + start_time + end_time ) ? who much it makes differnce with or without clus index. ?
3) D: drive (data drive) only have 30 GB free space. and Tempdb has 80 gb free space. how much more space is required for index creation. (dedicated RAm : 30 GB)
Partitioning related Questions:
Table contains 400 aprox. millions records.
Can i use "policyid + start_time" key as range for partioning ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 8, 2013 at 1:28 am
Clustered index key: start_time + policy_id.
Partition by start_time. You cannot partition by "policyid + start_time".
Use sort in tempdb option or filegroup on a drive with enough space.
February 8, 2013 at 1:42 am
Bhuvnesh (2/7/2013)
1) WHat would be the combination used on clustered index ?"policy_id + end_time" OR "start_time + policy_id"
No way to answer that, need information on the queries, the data type, the data distribution and insert pattern, the common access path into the table
3) D: drive (data drive) only have 30 GB free space. and Tempdb has 80 gb free space. how much more space is required for index creation. (dedicated RAm : 30 GB)
Creating or rebuilding an index requires size of index + sort space.
Can i use "policyid + start_time" key as range for partioning ?
Why are you partitioning? What's the goal?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2013 at 1:50 am
who much it makes differnce with or without clus index?
Without clustered index (that is, on a heap table) you cannot do partition switching. This is the deal-breaker for me.
Rebuilding a heap internally also rebuilds all NC indexes on that table (because NC index on a heap locates a row using it's physical location which is changed so NC index is not valid and has to be rebuilt). That makes heap more difficult to maintain.
If you have a clustered index table, each NC index will be added clustered index key columns if not already present.
February 8, 2013 at 1:59 am
GilaMonster (2/8/2013)
need information on the queries, the data type, the data distribution and insert pattern, the common access path into the table
Most of the queries having filter
on "Ids column + starttime" OR "Ids column + Endtime" like insuredID + starttime
policyID --> nvarchar(15) starttime --> datetime
data is placed on one table residing on one disk. (datawarehouse kind of structure)
GilaMonster (2/8/2013)
Creating or rebuilding an index requires size of index + sort space.
How can i calculate sort space.
Why are you partitioning? What's the goal?
to manage the data , it contains 400 milions rows , NO index so i am plannig to archive historical data into separate disk then please aligned indexes on partitioned tables.keep on last 12 month data
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 8, 2013 at 2:35 am
How can i calculate sort space.
Follow this link ; For space calulations ..
http://msdn.microsoft.com/en-us/library/ms191183(v=sql.105).aspx
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply