SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to partition table


How to partition table

Author
Message
arooj300
arooj300
SSC Veteran
SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)

Group: General Forum Members
Points: 291 Visits: 533
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...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86901 Visits: 45266
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
arooj300
SSC Veteran
SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)

Group: General Forum Members
Points: 291 Visits: 533
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
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85481 Visits: 41081
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86901 Visits: 45266
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
arooj300
SSC Veteran
SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)

Group: General Forum Members
Points: 291 Visits: 533
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...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86901 Visits: 45266
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
arooj300
SSC Veteran
SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)

Group: General Forum Members
Points: 291 Visits: 533
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86901 Visits: 45266
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
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39539 Visits: 32635
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search