Click here to Skip to main content
14,218,216 members
Rate this:
Please Sign up or sign in to vote.
See more:
hi every one.
am using sqlserver 2008 R2

i have a few tables with data in those tables increased by 2 crores pluse ,in future it hits more.by that my database performace became very slow, i concentrated on indexing and even on query optimization. still we hitting the same problem.


so i want to use partition technic to overcome this.

i have a doubt for few table am using primary key in were clause and in some table forgien key in where clause.

can any one please suggest if at all we go for partitioning on which columns we need to select .

P.s : all my data in data base is migrated data base.

thanks
karthik
Posted
Comments
NuttingCDEF 6-Apr-12 11:50am
   
That must be some size database or you are hitting it very hard! Or there's a design issue in the way it's built or being used . . .

Have you tried using the SQL Server Profiler and Database Engine Tuning Adviser?

What languages / technologies are you using? Personal experience is that the tools mentioned above can give a lot of help in sorting out performance issues. I use .NET and LINQ and what look like innocuous code changes can have a huge effect. If you're doing lots of inserts, making sure you are doing BULK inserts (which AFAIK LINQ never does!) is also important.

Are your hits primarily Select, Insert, Update or Delete?

Can you give any examples that might help?
karthikkushala 6-Apr-12 12:57pm
   
thanks for reply.
i tried sql server profiler , audit logout taking more durantion.and SSRS taking more resources.
i didn't tried about database engine tuning adviser.and
am using .net , in our application we us more insert and select statement data base size is 25 GB.
system configration windows server 2008 and 4 GB RAM
NuttingCDEF 7-Apr-12 15:24pm
   
First, this is large, but unless your app needs to retrieve significant portions of the data at once I wouldn't have thought that it should be unmanageable.

Is your server 32-bit or 64-bit? If 32 bit, have you considered going 64 bit and adding more RAM (AFAIK 32 bit can only use 4GB)? I presume that this is at least a moderately fast multicore server - and that network bandwidth isn't the issue (are you on at least Gigabit?).

How many clients do you have? Is the problem a few clients that each put huge load on the database or lots of clients with a small load each? Are you retrieving more data than you actually need?

General rule: adding indexes helps with performance of SELECT - but slows down INSERT - so you want to have only those indexes that actually help with the specific queries that you actually use - so knowing whether the real issue is your SELECTs or your INSERTs might help - which is actually taking the bulk of the time?

DETA can help a lot with this - maybe kill a few indexes, then use DETA to see what it suggests adding back

For INSERT, the performance difference between lots of individual inserts, one row at a time (which is what LINQ generates) and BULK INSERT is typically massive (can be a couple of orders of magnitude) - SQL Profiler has helped me identify this and other issues.

With LINQ I've also found that iterating through result sets can be slow as it may only retrieve data on demand one row at a time - adding things like .ToList() at the end of queries can force LINQ to retrieve the entire result set in one go, which can be hugely faster. Again SQL Profiler can identify what queries are actually being fired at the database. Using this I've taken code that generated 10s of 1000s of queries and made minor changes that have reduced it to a few 10s only and achieved exactly the same.

I'd also look at your queries. Are you always querying on exact matches for fields that have been indexed? If not (especially if you are doing anything like WHERE [Field] CONTAINS "some text") then think how you might restructure your queries - or do an efficient retrieve of some dataset and then subquery it client side in memory.

Personally, I'd try the above before looking at partitioning.
karthikkushala 6-Apr-12 13:04pm
   
we use to pay bulk repayment for loans and in accounts forms xml to DB insertion
problem is all select and insert going very slow .
when DB is small it worked fine

1 solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100