In several of my last few blog posts, I’ve shared several methods of getting internal information from a database by using the DBCC PAGE command and utilizing the “WITH TABLERESULTS” option to be allowed to automate this process for further processing. This post will also do this, but in this case, we’ll be using it […]
(2017-2022)
Search Results on DBCC PAGE
In a prior blog post, I demonstrated how using DBCC PAGE can be automated by using the “WITH TABLERESULTS” option. In this post, we will continue with another look at how this can be done. On a nice wintry day, your city ended up being covered in several feet of snow. During the course of […]
Way back in 2006, Paul Randal documented DBCC PAGE on his Microsoft blog at //blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/10/625659.aspx. In his post, you will notice that in order to return the output from DBCC PAGE to the screen, you need to enable trace flag 3604 first. The above blog post shows a few examples of the results and utilizing […]
I was recently reading this msdn article on Ghost Records, and it mentioned that you could get the number of ghost records on a page with DBCC DBTABLE… it also mentioned that you need to be sure that you enable Trace Flag 3604 to see the results. So, two things immediately jumped out at me. […]
Most of the DBCC commands return their results as textual output, even if you have SSMS configured to return result sets to a grid. This makes examining the output a manual process that is prone to errors. It sure would be nice if there was a way to return the output to a grid. If […]
Page Splits 101 In SQL Server, a page split occurs on index pages when a row is required to be on a certain page (because of the index key), and there isn’t enough room on the page for it. This can be from either an insert operation, or an update operation. When this occurs, that […]
While you may not need to worry about the physical location of a row very often, every so often the need comes up. In a few of my earlier posts (here), I have needed this information to prove a point. This post shows how to identify a row’s physical location. It will also show the […]
I’ve been digging deeper into the Ghost Cleanup process recently, and quite naturally my quest lead to Paul Randal’s blog. He has a post about the Ghost Cleanup process in depth, and in this post is this line: The ghost cleanup task will leave a single record on the page to avoid having to deallocate […]
The Myth One misconception that I see a lot deals with how data is stored in a clustered index. Specifically – is the data in a clustered index stored on the page in physical order? Most people will say “Yes”, and they get this from the definition (see BOL: Clustered and Nonclustered Indexes Described), which […]
Understanding the relationship between statistics, indexes and constraints in SQL Server. What meaning does stats_id of 1 mean?
