Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to partition table Expand / Collapse
Author
Message
Posted Sunday, January 5, 2014 2:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:13 AM
Points: 92, Visits: 270
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...
Post #1527890
Posted Sunday, January 5, 2014 3:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 42,813, Visits: 35,932
Define: 'not working properly'

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



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1527891
Posted Sunday, January 5, 2014 4:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:13 AM
Points: 92, Visits: 270
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
Post #1527896
Posted Sunday, January 5, 2014 11:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 36,978, Visits: 31,499
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1527913
Posted Sunday, January 5, 2014 11:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 42,813, Visits: 35,932
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 2008, MVP
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

Post #1527919
Posted Monday, January 6, 2014 12:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:13 AM
Points: 92, Visits: 270
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...
Post #1527981
Posted Monday, January 6, 2014 12:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 42,813, Visits: 35,932
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 2008, MVP
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

Post #1527984
Posted Monday, January 6, 2014 12:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:13 AM
Points: 92, Visits: 270
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
Post #1527986
Posted Monday, January 6, 2014 1:05 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 42,813, Visits: 35,932
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 2008, MVP
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

Post #1527988
Posted Monday, January 6, 2014 5:15 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 4:57 PM
Points: 15,661, Visits: 28,047
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1528048
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse