Sql-server – SQL Server deprecated Functions effect

Well, if you're only concerned with how to get around this partitioning issue you have, and are sure you won't face performance issues after you move to a single partition, then you could try to collapse all of your partitions into 1, and there's lots of ways to do.

If you feel your system can handle it, you could dump all the data into another table and give it a max ID number to dump into. Then when the bulk of the data is copied into there, you could note the ID, find any new records that came in. If it's a huge amount of records, dump those new records in the new table as well. After the 2nd dump you should be almost caught up. At that point you do not allow new data to come in for a few seconds/minutes while you run pre-scripted out commands to:

-move the remaining data into the new table that doesn't have partitioning enabled after making sure the schemas match.

-rename the old table. Make sure you don't have blocking issues.

-rename the new table to the old table.

You might want to avoid adding indexes at the beginning, and do those at the very end.

If doing this on the live system isn't an option, you could move the data to another system and do all these steps there.

Perhaps you could use this opportunity to archive your old data. You could consider creating a view and have your old data in a different archive read only database and have it referenced with a view.

How much data is it anyways, with and without indexes?

SQL Server 2012 has vastly changed the way it controls and manages memory. Before 2012, max server memory controlled only 8K pages, and now it controls much more than that.

Here are some good articles about this from the SQLOS team:

Article 1 | Article 2 | Article 3 | Article 4

With these changes, what you see in working set, max server memory, etc. will almost certainly be different between versions. The key is to focus on what "normal" is for these figures and see if they deviate vastly when there is a specific performance issue. I would not just look at a number when the system is working perfectly fine and panic and assume something is wrong (this happens a lot with magic numbers like 300 for page life expectancy - maybe PLE on your system is always 120).

A few queries that might help ballpark and set some baselines:

SELECT TOP (10) page_type, 
  numpages = COUNT(*), 
  size_kb = 8*COUNT(*) 
FROM sys.dm_os_buffer_descriptors
WHERE database_id < 32767
GROUP BY page_type
ORDER BY size_kb DESC;

SELECT * FROM sys.dm_os_process_memory;

SELECT TOP (10) [type], 
  size_kb = SUM(pages_kb) 
FROM sys.dm_os_memory_clerks 
GROUP BY [type]
ORDER BY size_kb DESC;

SELECT * FROM sys.dm_os_performance_counters 
WHERE object_name LIKE '%Memory Broker%'
   OR object_name LIKE '%Buffer Manager%'
   OR object_name LIKE '%Memory Manager%'
   OR object_name LIKE '%Memory Node%';