January 5, 2014 at 2:28 am
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...
January 5, 2014 at 3:39 am
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
January 5, 2014 at 4:09 am
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
January 5, 2014 at 11:16 am
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
Change is inevitable... Change for the better is not.
January 5, 2014 at 11:59 am
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
January 6, 2014 at 12:31 am
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...
January 6, 2014 at 12:37 am
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
January 6, 2014 at 12:55 am
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
January 6, 2014 at 1:05 am
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
January 6, 2014 at 5:15 am
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
January 7, 2014 at 8:28 am
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.
January 7, 2014 at 8:48 am
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.
January 7, 2014 at 8:57 am
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.
January 7, 2014 at 9:30 am
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
January 14, 2014 at 5:56 pm
Maybe your Transaction log is getting full and your process is failing.
or even SSIS/data issue.
Give us more details.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply