How to use partitioning in SQL Server database to avoid ‘timeout’ errors
We have over 300 stores, and with each store having 30gb+ databases, each server is being upgraded to at least 6gb+ memory which is being forced allocated to SQL Server. I want to start to partition the existing SQL Server database by date. How do I go about doing it, because I have absolutely no idea? By the way, the stores experience timeout errors every day and it's not the H/W), please help.
Software/Hardware used:
Software/Hardware used:
ASKED:
April 30, 2008 6:01 PM
UPDATED:
May 2, 2008 1:47 AM
Answer Wiki:
If you are using SQL Server 2000, you have to create new tables broken out by year, then manually move the data into those new tables. Then modify your code to search these tables.
If you are using SQL Server 2005 you can create a partitioned table, then move the data into the table.
The point of partitioning a table is to put more hardware behind a single table. If your hardware (disk drive) is not running beyond capacity then partitioning the table may not help.
I'd be curious to see how large your tables are, and what your indexes look like.
I've got a table which is 78 Gigs and runs very nicely. Until recently we had only 8 Gigs in the server.
If you are having performance problems I would start by looking at the indexes for tables that are having performance problems.
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.