One of the dynamic management views (DMVs) that is very useful in troubleshooting query performance is sys.dm_exec_requests.  The documentation around this DMV, however, is quite lacking in two areas. percent_complete This column shows the ''percent of work completed for certain operations, including rollbacks.''  Okay, great, now ...

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 ...

A frequently asked question that surfaced again today is, ''how do I see when my data has been accessed last?''  SQL Server does not track this information for you.  SELECT triggers still do not exist.  Third party tools are expensive and can incur unexpected overhead.  And people continue to be reluctant or unable to constrain ...

  A couple of questions arrived regarding my earlier post about fragmentation on the system tables, and since the questions really don't have anything to do with system tables, I felt they deserved a new post. 1) Using the DETAILED option includes the non-zero index-levels as well (non-leaf pages).  According to many source, this ...

Yesterday Denis Gobo told you about 33 new DMVs in SQL Server 2008; previously, I had noticed that a few of the existing DMVs from 2005 had changed slightly.  So, Denis sparked my curiosity, and I ran the following query on a recent build of SQL Server 2008, which had a linked server pointing to SQL Server 2005: SELECT s1.vn, s1.cn ...

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 ...

As with all of the blog posts I keep meaning to write -- I keep a list and given the infrequency with which I've been posting lately, it's getting quite large -- this script has been on the queue for quite some time. So here I find myself with a spare moment right on the cusp of the new year, and figured what better way to end the year than with a ...

In almost all my classes, and every time I do any performance consulting, I get the question ''How can I determine if our SQL Server is hyperthreaded?'' I was delighted when I found the DMV sys.dm_sys_info, which has a column called hyperthread_ratio. But soon after, I read a blog post from one of my favorite SQL Server bloggers, Buck Woody who ...

This post really should have a topic of ''Pet Peeve'' but I really didn't want to sound quite so negative. It seems like almost every time I see a clever solution in a newsgroup response, or in a blog post, about how to extract some very useful troubleshooting information using the Dynamic Management Views, someone will ask for an equivalent ...