SQL Table Partitioning

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

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.

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