Problem solve Get help with specific problems with your technologies, process and projects.

Performance impact of partitioning table

I have a table in SQL Server that has one field of size 215 bytes. I have 1.1 million records in the tabi le. I noticed that only 20,000 records were using more than 50 bytes. So if partition the table such that one table has field of length 50 bytes and other of 215 bytes for two cases, will there be any performance (speed and memory) gain, and if yes, how much? I am not talking in sense of normalization. My SQL Server memory usage doesn't reduce after any query. It remains the same. How can I free memory of SQL Server after the query has finished?

You are going to see a performance drop, because you are going to have to union the two tables together and then query on those just to be able to accomplish an artificial partition of the data.

SQL Server is NOT supposed to give up memory after each query. Memory allocation is an expensive operation. A SQL Server will give up memory if something else on the machine needs it. Since nothing else needs it, SQL Server is going to keep it, because it will probably need the memory again at a later time. If the SQL Server gave up memory after every query, you would quickly have a system that would spend more than half of the time doing nothing but allocating and deallocating memory at which point there would be very little purpose to even trying to run something on it.


For More Information

Dig Deeper on Microsoft SQL Server Installation

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.