As you might know, metadata is one of my favorite topics, and I've written quite a bit about metadata and system objects. A very frequent question is: What exactly IS a system object? There are quite a few different definitions you could use. You could say a system object is one that was installed with your SQL Server installation. You could ...

I've said many times that my favorite new feature in SQL Server 2005 is the new metadata, in particular the new Dynamic Management Objects. When I have to do troubleshooting on a SQL Server 2000 system, it is worse than painful, not to have my favorite tools like sys.dm_tran_locks, sys.dm_exec_cached_plans andsys.dm_exec_query_plan. By now, on the ...

I have frequently heard SQL Server developers and DBAs gasp when a query plan is indicating that SQL Server is performing a table scan, thinking that is the worst thing that could ever happen to a query. The truth is, it's far from the worst thing and in addition, not all table scans are created equal. One thing that is far worse that a table ...

  The editing for Lesson 1 is done and the production run of my DVD has started. We're expecting shipping to start in about 2 weeks. In addition, we've extended the pre-order price for another 5 days, so any orders that come in before Valentine's Day will get the special reduced price. Because this is taking a bit longer than we hoped, we ...

It's my first post of the new year. I hope it's starting out well for all of you! New year, but sometimes the same old questions. I got another email asking about defragging the system tables. It seems to be in the Hit Parade of FAQs. First of all, WHY do you think you would need to defrag a system table? Fragmentation is only a problem when you ...

  I first heard about the possibility of database cloning way back in version 7.0 and thought it sounded like a great idea. Who wouldn't love to have a bunch of identical little databases running around, of smaller size and easier manageability? Cloning a database means creating a statistics only copy of it, so that you can examine ...

  No, I'm not talking about US dollars, which are not worth much at all these days. I'm talking about the $ used in a particular construct in SQL Server 2005 to get information about a partition definition. There is a construct called $PARTITION which the BOL actually refers to as a function, but it doesn't act like any other function in the ...

I’ve mentioned before that the new metadata is so voluminous that I’ve barely skimmed the surface. Last week in class in Minneapolis (www.nhmn.com) one of my students discovered an alternative to sp_configure. You’re probably aware that by default, even if you’re logged in as someone in the sysadmin group, you can’t see all the configuration ...

You may be well aware that if you alter the length of a column to a larger value, that SQL Server doesn’t go through all the rows at the time you issue the ALTER. The ALTER is a metadata only change, and the actual movement of data doesn’t happen until a row is updated. However, SQL Server does not reuse the original bytes in the row when a ...