Database Whisperer
This is an update to my post last week Partitioning a Huge Table where I talk about taking an existing table and making it partitioned. My largest complaint in that post was that it was difficult to do online because rebuilding a clustered index on a huge table required reading or writing a lot of [ ] The post Partitioning a Huge Table Quickly first appeared on Michael J. Swart .
Aaron Bertrand wants you to consider using partitioned tables and the sliding window pattern to help archive old data. That s a great idea. In fact, I d like to do that at my own job. I have a truly humungous log table (Terabytes) and its clustered index is already on CreatedDate so it s a good candidate [ ] The post Partitioning a Huge Table first appeared on Michael J. Swart .
Takeaway: Adding foreign keys require schema modification locks on every table involved. This can cause deadlocks on busy systems. Schema modification locks (SCH-M) are taken by DDL (Data Definition Language) statements like CREATE/ALTER/DROP. Schema stability locks (SCH-S) are taken by DML (Data Manipulation Language) statements like INSERT/UPDATE/DELETE. Those two types of locks are…
It s hard to destroy data. Even when a column is dropped, the data is still physically there on the data page. We can use the undocumented/unsupported command DBCC PAGE to look at it. Example First, create a table based on English values from sys.messages: use tempdb; go /* create table based on english values from [ ] The post Read data from dropped columns first appeared on Michael J. Swart .
Hey what s up? It s been a while. If you define a clustered index that s not unique, SQL Server will add a hidden 4-byte column called UNIQUIFIER. You can t see it directly but it s there. When you add a row whose key is a duplicate of an existing row, the new row gets a new unique [ ] The post Large Uniquifier Values first appeared on Michael J. Swart .
For T-SQL Tuesday, Brent Ozar asked us to write about the most recent ticket we closed. I m going to write a bit about the most recent project I wrapped up. Although this is a project and not a ticket, the story is recent and it really gives an idea of what exactly it is that [ ] The post Modeling Resource Governor Behavior first appeared on Michael J. Swart .
MIN_ACTIVE_ROWVERSION() is a system function that returns the lowest active rowversion value in the current database. Its use then is very similar to @@DBTS. In fact, the docs for MIN_ACTIVE_ROWVERSION() (currently) say: If there are no active values in the database, MIN_ACTIVE_ROWVERSION() returns the same value as @@DBTS + 1. Does it though? You may [ ] The post A Quick SQL Server Puzzle About…
Takeaway: If you want to extract rows from a table periodically as part of an ETL operation and if you use Read Committed Snapshot Isolation (RCSI), be very careful or you may miss some rows. Yesterday, Kendra Little talked a bit about Lost Updates under RCSI. It s a minor issue that can pop up after [ ] The post Watch Out For This Use Case When Using Read Committed Snapshot Isolation first…
When I deploy database changes, I like my scripts to be quick, non-blocking, rerunnable and resumable. I ve discovered that: Turning on Resource Governor is quick and online Turning off Resource Governor is quick and online Cleaning or removing configuration is easy Modifying configuration may take some care Turning on Resource Governor Just like sp_configure, Resource [ ] The post Deploying…
In the past I ve written about monitoring identity columns to ensure there s room to grow. But there s a related danger that s a little more subtle. Say you have a table whose identity column is an 8-byte bigint. An application that converts those values to a 4-byte integer will not always fail! Those applications will only [ ] The post Can your application handle all BIGINT values? first appeared…