January 22, 2015 at 12:39 pm
I'm new to sql server. ..We have an issue with accessing and updating large database which is about 30tb(we get this data from 3rd party). We have about 20 developers who work on site and when they access the data .. they access some tables and retrieval time is very slow, sometime when they update many records. . Server is shutting down.
I understand we have Some options (please let me know of more options if any.. I'm sure there should be more options)
1) table partitioning
2) File Groups
3) putting SSD drive
They are already on SSD.
But still they have same issue.
Could anyone please help me with the options to handle this situation better. OR Share me there setup diagram of SSD drive (which will help me to revisit our architecture)
Any help is really appreciated.
January 22, 2015 at 12:52 pm
The ABSOLUTE BEST course of action (by far) is to hire a competent consultant to help you a) tune your hardware, b) fix poor settings (windows, sql server, storage, etc), c) tune your schema/queries, d) fix a MULTITUDE of other things that simply MUST be done (or not done) to be able to scale 30TB workloads.
Actually this is truly the only hope you have of getting to where you really want/need to be. You could spend weeks of back and forth on forums and still not do more than scratch the surface of possibilities!!
I know some good consultants if you are interested. Drop me a PM.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 22, 2015 at 1:12 pm
Without knowing what is happening, there's no way to state or suggest what is wrong. As Kevin mentioned, chances are you need a consultant to help you understand the issues.
Paritioning, SSDs, more filegroups, etc. aren't necessarily going to help.
If SQL Server is crashing, you have serious issues. This is a very stable product, and you have people doing something wrong.
If updates are slow, you would have to diagnose what is causing this. Depending on how your developers access the data, they may be causing the issue themselves.
I would suggest that you engage a consultant if you don't know much about SQL Server. It's the most efficient way to get your people working without wasting lots of time here, which is a loss of money in salaries.
If you want to post specific issues, perhaps we can help.
January 22, 2015 at 1:35 pm
Thanks a lot for reply.
SQL Server not Shutting down or not crashing ,We have 15-20 developers to access the 30 TB database,Some of the developers are able to connect the database and remaining developers should be in stand by.
Developers are accessing nearly 100 tables very frequently so i thought if i create schema,File groups or Table partitioning to that tables it will work?
January 22, 2015 at 2:01 pm
chucking in SSDs will just probably mask the real issue. SSDs are indeed faster than traditional disks but even they have a limit.
It's been said already, code and database design should be your first area of inspection
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 22, 2015 at 2:09 pm
Bunny786 (1/22/2015)
Thanks a lot for reply.SQL Server not Shutting down or not crashing ,We have 15-20 developers to access the 30 TB database,Some of the developers are able to connect the database and remaining developers should be in stand by.
Developers are accessing nearly 100 tables very frequently so i thought if i create schema,File groups or Table partitioning to that tables it will work?
Schemas, file groups, partitioning will NOT help your problems. You are already on SSDs and splitting things up will not make them go any faster. Partitioning was not built to make queries run faster (contrary to popular notions). It was built to facilitate large data loads and management.
Like I said, there are almost certainly umpteen things at fault here. There will absolutely be some magic bullets to be found in low-hanging fruit. But there are also almost certainly some difficult issues to be tackled as well.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 24, 2015 at 6:30 am
If you already have updated hardware, I'd say the problems are probably code and/or data structure related. You need to understand what exactly is running slow and exactly why it's running slow in order to address the issue.
And one more voice saying that partitioning is not a performance enhancement. It's about data management (which you may also need, but that's a different discussion).
"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 24, 2015 at 3:09 pm
Bunny786 (1/22/2015)
Thanks a lot for reply.SQL Server not Shutting down or not crashing ,We have 15-20 developers to access the 30 TB database,Some of the developers are able to connect the database and remaining developers should be in stand by.
Developers are accessing nearly 100 tables very frequently so i thought if i create schema,File groups or Table partitioning to that tables it will work?
As you said, you're new to SQL. Having a 30TB database isn't something that newbies or even 15-20 developers can tweak. You need someone that understands SQL Server and databases at a pretty deep level. As Kevin Boles suggested on this thread previously, you need to find a consultant that can come onsite, fix your problems, and educate you in the process.
As Perry suggested above, SSD's can make a decent improvement but they're not a performance panacea.
And, if the server isn't shutting down or crashing, why did you post the following in your original post?
they access some tables and retrieval time is very slow, sometime when they update many records. . [font="Arial Black"]Server is shutting down.
[/font]
Don't lie or make stuff up. It's not going to help here.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply