December 1, 2015 at 9:20 am
To My knowledge TEXT data fields havent been fully depreciated and there are ways round it with Custom Datatypes when it does happen, but thought Id make you aware. Also a standard option is to create a file group for storing blob data (Text/Image Filegroup in SSMS properties)
Sean's suggestions are a good starting point to keep the DB running, but its a sticking plaster, that will only solve the problem for a short while, the same goes for archiving, especially if your data is going to grow, archiving once a year isnt bad, but it can soon become once ever 6 months then once a Quarter.
I dont know how old the solution is, or the technology it was written in, the first couple of points are about that :
1) Business process changes that are no longer capable to be accomodated within the existing structure.
2) The age of the solution and the technology used for delivering a front end and back end solution
Other talking points,
1) Maintenance costs increase due to increased regression testing times, as products evolve the regression testing becomes more and more cumbersome if the code base has grown. You fix procedures A and B but forget C, or that a Business critical Process D requires output from A but is run once a Quarter.
2) Inflexible/Poor design, making it difficult to add new business processes as well as extract data,
3) Data volumnes (yes disk is cheap), they will only increase making it more onerous to query for users.
4) Partitioning is really not required, you start doing that when you get close to at least 500GB.
If you have a Dev server, do a rough and ready POC during your spare time (lunches, friday afternoons etc) to show how taking the tblCall table and normalising it a little will make a significant difference to performance.
Then propose a rethink of the Design as a whole, including the front end application, if the application is more than 2 years old you can bet the users are likely to want new features.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
December 1, 2015 at 2:39 pm
Welsh Corgi (12/1/2015)
I need at least a few items to make a case against partitioning.
The case against partitioning is that there is no case in favour of partitioning.
Think of DB partitioning as splitting a book into volumes.
If your chapters are in order than the only reasons to split a book into volumes is when it's too heavy to carry.
While your database fits a single SAN and its backups do not overwhelm the repository - there is no case for partitioning.
_____________
Code for TallyGenerator
December 1, 2015 at 3:00 pm
Welsh Corgi (11/30/2015)
I'm looking at another table that is much worse:
Yes, the table is horrible.
Even by the naming you can tell it's made of several different tables.
Cannot be good.
But 2.4 million rows is not such a big deal.
Not sure about Excel, but Access would handle it well.
Can you post top 3 queries which cause biggest performance problems?
_____________
Code for TallyGenerator
Viewing 3 posts - 61 through 62 (of 62 total)
You must be logged in to reply to this topic. Login to reply