x

Partitioning Tables In Sql Server 2000

I have a very large table of over 4.5 million rows which when queried can return results very slowly. I am looking to speed things up and was wondering if partitioning it would help. There is a date column within this table so wonder if I could partition it on this column and how I would go about this.

TIA
more ▼

asked Jul 08, 2011 at 08:21 AM in Default

David 2 1 gravatar image

David 2 1
412 45 50 52

Are you going to create the partitions on the same disk array? Also, I see you are using 2000?
Jul 08, 2011 at 08:31 AM sp_lock
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

There's no actual partitioning in SQL 2000 so you would have to go via a route of creating separate tables and then combining the information using a partitioned view. Take a look at http://msdn.microsoft.com/en-us/library/aa902650(SQL.80).aspx for some good information.

more ▼

answered Jul 08, 2011 at 10:05 AM

SirSQL gravatar image

SirSQL
4.8k 1 3

(comments are locked)
10|1200 characters needed characters left

To add a little bit to @SirSQL's answer, you could do the multi-table pseudo-partition, which tends to work acceptably in data warehouse situations where most of the data is static and only the newer stuff gets added or updated. But before going down that road, I would recommend checking to make sure your indexes are appropriate and your statistics up to date. 4.5 million rows, in the grand scheme of things, isn't that large (depending upon your available resources--particularly RAM), and with a good index, you could still get to a single record in 3-4 I/Os on a seek (and 6-8 with a bookmark lookup). If things are slow, you're probably seeing table scans or pulling back too many records. Unnecessary table scans can be eliminated or at least reduced with better indexes--though if you want to read the whole table, well, that's just going to take a table scan; nothing much you can do about it--and reducing the number of rows and columns you return to the user will reduce network processing time and ASYNC_NETWORK_IO waits (where SQL server is waiting on your client to process the data being sent to it).

Also, note that if you do partition, you'll only gain when it comes to queries where you are searching on the partitioned column. If people are always and forever searching by date and constantly want, say, an entire month's worth of data (in other words, too many rows for a seek to make that much sense), that's a good situation for partitioning, as the SQL Server engine is smart enough to ignore tables in which it is logically impossible for a match to exist, so you're left scanning a smaller range of records. But for any other searches, you still would need to scan the entire range, so it wouldn't help at all. And to be honest, for 4.5 million rows, a good index on that date column would still probably be a lot better than creating and maintaining so many partitions even if users really do use that date column constantly in their searches.
more ▼

answered Jul 10, 2011 at 05:59 PM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

Quite right - check the indexes first. I've had significantly bigger tables than this perform very happily on SQL2000 10 years ago.
Jul 10, 2011 at 11:53 PM ThomasRushton ♦
Thanks everyone. This is legacy system that won't be upgraded however I think you are right I need to check the indexing and statistics first. Great advices.
Jul 11, 2011 at 02:18 AM David 2 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x984
x473
x84
x51

asked: Jul 08, 2011 at 08:21 AM

Seen: 2087 times

Last Updated: Jul 08, 2011 at 08:21 AM