Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
neerav.saini
Starting Member
9 Posts |
 Posted - 2009-06-29 : 05:51:16
|
| Hi all,I have a table with 10,000 records how can i create partition table.Or there is any other possible way to make server search record in this table fast?Thanks,NSThanks,Neerav |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-29 : 06:03:02
|
Proper index(es)!10,000 records are nothing for SQL Server to process.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
neerav.saini
Starting Member
9 Posts |
Posted - 2009-06-29 : 06:12:27
|
| oops i missed one zero in above number of records.can please tell prcedure to create partition tableThanks,Neerav |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-29 : 06:14:38
|
| Proper index(es)!100,000 records are stillnothing for SQL Server to process.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-29 : 06:41:43
|
Major Data? And Colonel Yak DBA?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 2009-06-29 : 06:53:43
|
| HiWhat are partitions and why use them? The simple answer is: To improve the scalability and manageability of large tables and tables that have varying access patterns. Typically, you create tables to store information about an entity, such as customers or sales, and each table has attributes that describe only that entity. While a single table for each entity is the easiest to design and understand, these tables are not necessarily optimized for performance, scalability, and manageability, particularly as the table grows larger. What constitutes a large table? While the size of a very large database (VLDB) is measured in hundreds of gigabytes, or even terabytes, the term does not necessarily indicate the size of individual tables within the database. A large database is one that does not perform as desired or one in which the operational or maintenance costs have gone beyond predefined maintenance or budgetary requirements. These requirements also apply to tables; a table can be considered large if the activities of other users or maintenance operations have a limiting affect on availability. For example, the sales table is considered large if performance is severely degraded or if the table is inaccessible during maintenance for two hours each day, each week, or even each month. In some cases, periodic downtime is acceptable, yet it can often be avoided or minimized by better design and partitioning implementations. While the term VLDB applies only to a database, for partitioning, it is more important to look at table size.In addition to size, a table with varying access patterns might be a concern for performance and availability when different sets of rows within the table have different usage patterns. Although usage patterns may not always vary (and this is not a requirement for partitioning), when usage patterns do vary, partitioning can result in additional gains in management, performance, and availability. Again, to use the example of a sales table, the current month's data might be read-write, while the previous month's data (and often the larger part of the table) is read-only. In a case like this, where data usage varies, or in cases where the maintenance overhead is overwhelming as data moves in and out of the table, the table's ability to respond to user requests might be impacted. This, in turn, limits both the availability and the scalability of the server. Additionally, when large sets of data are being used in different ways, frequent maintenance operations are performed on static data. This can have costly effects, such as performance problems, blocking problems, backups (space, time, and operational costs) as well as a negative impact on the overall scalability of the server.How can partitioning help? When tables and indexes become very large, partitioning can help by partitioning the data into smaller, more manageable sections. This paper focuses on horizontal partitioning, in which large groups of rows will be stored in multiple separate partitions. The definition of the partitioned set is customized, defined, and managed by your needs. Microsoft SQL Server 2005 allows you to partition your tables based on specific data usage patterns using defined ranges or lists. SQL Server 2005 also offers numerous options for the long-term management of partitioned tables and indexes by the addition of features designed around the new table and index structure. byraj...:) |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-29 : 07:30:41
|
| Hi neerav.saini,What Peso and myself are saying (though you should *absolutely* take his word over mine if we end up contradicting each other) is that:Yes, you *can* implement a partition table but you probably don't need to. You said that you have around 100,000 records in your table and you will probably see much better improvement with a good solid index rather than partitioning.To help you in detail you should post:1) The structure of your table, including any indexes that may exist all-ready2) The typical type of query that you want to improve (the exact sql if possible).A good covering index / a change to the sql itself will probably give much better performance that setting up partitioning.Regards,Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
Major Data