asked
Performance gain from the Large tables
All, Partitioning is the best option considered when talking about gaining performance. But partitioning is not availble in sql server 2008 starndard. I read about the partioned views, but it appears that these views are not updatable. I am not sure if there is enough budget for upgrading sql server standard to enterprise, what otherway can i achive this performance.not needding to make changes how the frontend application is accessing the data right now. Thanks,
One thing worth noting, partitioning is frequently of only marginal value for performance. It's primarily a tool to enhance data management, not performance tuning.
You can do [Horizontal partitioning][1] your tables into your tables into different filegroups and disk, this will also improve performance as the data sperad across multiple disk. [1]:
http://msdn.microsoft.com/en-us/library/ms178148.aspx
You can also look at the data and archive what you don't need. If you don't need the entire table, archive off the data and make it smaller, thus getting a potential performance increase. Are you seeing specific performance issues? Is the performance issue with just certain queries or is the entire server slow? Any missing indexes? Is your SAN disk blocks sized correctly? There are a slew of things that you might be able to tweak that might help. Of course, you can always throw hardware at it. If the server is memory bound, that might be a quick (and sometimes much cheaper) fix than upgrading the server license. Just throwing out additional ideas.
I agree with John, You might want to take a step back and look at what is being stored, and do you need all that data. Look at the way that data is being accessed, one of the things that I did recently for a client that was not able to use all of the features of SQL Server was to generate them a pre-aggregated table with results that were much smaller than the source table, but it was the way that they looked at the data. Is there a way that you can move the data around, split it up, and create some indexed views, or maybe in a federated database. I think you have a lot of options.
It really depends on how your application accesses the data, if it is all in stored procs and views, then you have a layer between that you can move around in. If there is direct table access from the app then you are right, there is no abstraction layer and you may have to change the app.
Agree with John. Just there could be couple of things to check for better performance. Like: Index (Clustered and Non-Clustered), Index fragmentation (both internal & External), Statistics; Memory configuration (setting maximum memory for SQL Server) this has very large affect in performance( experienced), Paging; More spindle in the disks. loads on your SAN disk i/o, number & type of processor (hardware), also code; I think table partitioning could be the last option.
I think John hit this one on the head, perf tuning is a pretty wide topic. You need to consider what your perf challenges are before you look for a solution. I'm sure you have already done this, if so please share :)
