How to partition table

Question

Post reply

How to partition table

arooj300

Hall of Fame

Points: 3433
More actions
January 5, 2014 at 2:28 am

#302397

Hi all,
I have 270 column in a table because of that application is not working properly.
can anyone please help me how to partition table and if any alternative option is there please let me know
thanks...

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Define: 'not working properly'

What's the purpose of partitioning? What do you expect to achieve?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arooj300 Hall of Fame Points: 3433 More actions · Answer 2

whenever we try to upload the data...the application will be hang or it stop uploading data.

please tell me how to resolve this issue

Thanks in avance

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

arooj300 (1/5/2014)
whenever we try to upload the data...the application will be hang or it stop uploading data.
please tell me how to resolve this issue
Thanks in avance

Gosh... still not enough information. We need to know what you're using to "upload the data", see some code, see the table and index structure (attach the CREATE statements), know what kind of drivers the app is using to do "the upload", know how many rows you're trying to INSERT into the table at one time, etc, etc. There are other things to know like how many FK's you have against the table, how many indexed views you have against the table, and whether or not any triggers are present.

Indexes on such a wide table could certainly be a problem. For example, even if you have just one low selectivity index, such as a bit column or a status column, the page and extent splits may (many times, will) cause enough of a delay to cause massive timeouts on the GUI side of the house. What you've selected as the Clustered Index may also cause such a problem. That, however, may not be the problem here but there's no way to tell because, what you've posted, so far, is a classic "my car won't run, please tell me what's wrong with it" type of question and that's just impossible to answer without more information.

"Partitioning" typically won't help on such problems even if you take to "vertical" partitioning.

I'll also suggest that having a 270 column table might be a design problem to begin with but I've seen that happen especially in the area of Telephony and Call Detail Record processing (for example).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

arooj300 (1/5/2014)
whenever we try to upload the data...the application will be hang or it stop uploading data.
please tell me how to resolve this issue

No where near enough information. There's not a chance that anyone can do any more than guess with that vague description.

Do some troubleshooting, pinpoint exactly what's happening, then post again with loads more information. This may be helpful https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arooj300 Hall of Fame Points: 3433 More actions · Answer 5

Hi ,

Thanks for the information.

I am explaining clearly....

For uploading data we are using SSIS package , through application more than one user try to upload excel file at the same time the issue occurs.

we are not able to find this is because of Sp or SSIS package.

when excel file are uploaded at the same time we tracing the log file also but there also I couldn't find the issue.

example if i upload 5 records so 4 record will be uploaded and 5th one is rollback.

Thanks again...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Still not enough information. Could be any number of things. Get the book I referenced, do some basic troubleshooting, identify what's happening.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arooj300 Hall of Fame Points: 3433 More actions · Answer 7

There is one application in our organization. At starting they got the data from the client and our people were uploading the data manually and what manager did he assigned the target each and every team member after that it will go to the QA department for analyze the data, if there is no issue then it will passed that is how the application was working before.

But problem was there if one user was working at the same time other user logged in the application became slow and if there are 10 user logged in they were not able to work on this because of locking issue.

So to over come this problem they have created SSIS package . the issue almost resolved but the another issue occuring like if 10 records is uploading ...upto 9 record that is working fine but in 10th record, process is hanged.

why I am asking for split the table because there is 270 column and out of 270 columns 79 columns they have created varchar(MAX).

I hope that will be ok for you to understand my proble.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Why do you think partitioning will help?

Have you confirmed the problem is locking? If so, what kind of locks from what operation?

Have you pinpointed exactly where in the process the problem is occuring?

Get the book I recommended, do some basic troubleshooting, identify the cause of the problem. Once you know exactly what is causing the problem, it's possible to make a proper recommendation as to a solution.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 9

It could be process, your server set-up or your code. Most of the time, it's the code. I've got a book on SQL query tuning that walks you through gathering metrics to identify the poorly performing queries and then walks you through what to do about them.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

arooj300 Hall of Fame Points: 3433 More actions · Answer 10

Hi All,

Thanks for the sharing information...But still I am not sure.

My team is working on this application from past 2 years but still they did not find solution.

When all the people are uploading the data at the same time the end user can update or insert the data in the same database. for that one we ha created SSIS package also but still the issue remain same.

Please help me on this...

Thanks in advance.

arooj300 Hall of Fame Points: 3433 More actions · Answer 11

Sorry I am correctiong the question

Thanks for the sharing information...But still I am not sure.

My team is working on this application from past 2 years but still they did not find solution.

When all the people are uploading the data at the same time the end user can not update or insert the data in the same database. for that we have created SSIS package also but still the issue remain same.

Please help me on this...

Thanks in advance.

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 12

A couple of things:

- Partitioning is not what you are looking for (as pointed out by Gail) at least not SQL Server Partitioning. You may want to look into vertical partitioning your table into multiple tables and then lay a view over the top to reconstruct your original table. I don't think this is what is causing your problems.

- Have you looked into locking and isolation levels as the problem (also pointed out by Gail). Make sure that your application and/or your SSIS package is not running in serializeable isolation level which would block other users during your insert and update periods.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

We still don't have enough information about the problem to even guess at a solution. I can't read your mind, I can't see your server.

GilaMonster (1/6/2014)
Get the book I recommended, do some basic troubleshooting, identify the cause of the problem. Once you know exactly what is causing the problem, it's possible to make a proper recommendation as to a solution.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

EL_oh_EL Old Hand Points: 377 More actions · Answer 14

Maybe your Transaction log is getting full and your process is failing.

or even SSIS/data issue.

Give us more details.