CODEWITHSUNDEEP
sqlsql-server
arcee123
arcee123

Reputation: 211

SQL Server partitioning vs indexes

I have a table with over 180 million rows.

I have been considering table partitions to reduce the scans (in addition to indexes). One of the columns I've considered to partition by is STATE. As this is not a transaction table, I do not have a date column that I desire to partition by. We do not do many selects based on the date.

My question is has anyone ever split their tables on a string-value column instead of a date? And if so, what are the performance considerations for doing so?

Thanks.

Upvotes: 4

Views: 10211

Answers (1)

Troy Witthoeft
Troy Witthoeft

Reputation: 2666

Yes, you can partition alphabetically. Example here.

But - if the goal is to improve query performance - I'm not sure that table partitioning is going to be the right solution. The primary use for table partitioning is to speed up bulk data loading and archiving. Remus Rusana - MS SQL Server Developer team member - answered a very similar question here, a quote ...

Partitioning is never done for query performance. With partitioning the performance will always be worse, the best you can hope for is no big regression, but never improvement.

And that's from the SQL Server Dev team! Of course, there may be some edge cases where partitioning improves query performance, but this doesn't look like one.

Luckily, we have many options beyond partitioning to improve performance. Query optimization is a bit of an art, so in order to truly answer your question we'd need many more details. How wide is the table? Do you have some example queries? What is the distribution of data (lumpiness)?

Going only on what you've said - this a not a transaction table - then creating indices sounds like a safe bet. We can add these without worrying about negatively impacting database DML performance (updates/inserts/deletes). To start the investigation into what indices to add, Launch SSMS, click the Show Actual Execution Plan button, and execute one of the problematic queries. Now examine the execution plan. Please and add any missing indices it finds. Hope this helps. Good luck!

TL:DR Partitioning won't improve query performance, try indexes.

Upvotes: 9

Related Questions