SQL 2005 Current Query Activity

Posted by on 2007/10/30 1 comment 
SELECT
    sder.session_id AS [SPID],
    sdes.login_name AS [Login],
    sd.name AS [DBName],
    sder.start_time AS [Start Time],
    sder.status AS [Status],
    sder.command AS [Command],
    sdet.text AS [SQL Text],
    sder.percent_complete AS [Pct Cmplt],
    sder.estimated_completion_time AS [Est Cmplt Time],
    sder.wait_type AS [Wait],
    sder.wait_time AS [Wait Time],
    sder.last_wait_type AS [Last Wait],
    sder.cpu_time AS [CPU Time],
    sder.total_elapsed_time AS [Total Elpsd Time],
    sder.reads AS [Reads],
    sder.writes AS [Writes],
    sder.logical_reads AS [Logical Reads]
FROM
    sys.dm_exec_Requests sder
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sdet
    JOIN sys.dm_exec_sessions sdes on sder.session_id = sdes.session_id
    JOIN sys.databases sd on sder.database_id = sd.database_id
WHERE
    sder.session_id <> @@SPID;

Testing

Posted by on 2007/10/17 No comments

A colleague of mine Aaron Erickson has an interesting post on how deep testing should go here.  This brings up some interesting points, for example in SQL Server I might test my own function, however I don’t test SQL Functions like GETDATE, instead I assume they work properly.  If I bring in CLR functions, the amount of code that doesn’t get tested could potentially grow beyond the code that does get tested.  So, as he points out, technically speaking I haven’t fully tested my implementations and, as I mentioned I could even have < 50% coverage.  So the question Aaron (that would be Aaron Erickson, as I don’t talk about myself in the third person) poses is how far should we go in our testing coverage.

Thinking about it, I’ve actually dealt with this questions a few times without realizing it.  Often I’ve had a project manager that doesn’t know SQL Server that well and has asked questions like (and I’m paraphrasing here):

  • How do we validate that we didn’t lose data after doing a sp_detach_db, copy and then sp_attach_db?
  • How do we validate we didn’t lose data after doing a backup/restore?

Of course my initial reaction is, well it either works or it doesn’t.  However after further thought I realized that I’m saying is that I trust whoever wrote and tested those functions and I believe them to be bug free or at least bug free for how we plan to use them.  While I think that MS is doing a better job getting their own internal code coverage with the use of CTPs, that way people can get the product in their hands test it themselves, I don’t think if data is lost and I were smile and tell my project manager, "It’s Microsoft’s fault since they didn’t fully test their product", I don’t think I’d have a job much longer.  While my client is leveraging SQL Server, I am the one responsible for the success of my work, not Microsoft (and not to mention that my name is on it and I want it to be excellent).

However on the other side of the isle:  If I’ve tested a system function 6 months ago on a different project/client, do I need to test it again for this project/client?  I’d say no since it’s the same code.  However if I don’t have to test it, then why can’t I just accept that another team tested it, even though this team was MS (even though they are changing their process often as well) ?  Am I really saying that I’m the only coder in the world that I trust?   I truly hope not as my workload would have just increased exponentially plus I just decided to always work alone and reinvent the wheel every time.

So we’re back to the same question, how much testing should be done and how deep should we go to realize the best code we can and of course stay in proper time scope.  The more I think about it, the more it sounds like we’re headed back to old faithful – the 80/20 rule…

Check filegroup and file

Posted by on 2007/10/16 No comments

Often when dealing with different filegroups or even different files in SQL Server 2005, I end up wanting to know where certain objects are logically and physically located.  Here’s a query that will give that information:

select 
                'Object Name' = so.name, 
                'Index Name' = si.name, 
                'Type Description' = si.type_desc,
                'FileGroup Name' = sfg.Name, 
                'System FileName' = sdf.Name
from 
                sys.indexes si 
                join sys.objects so on si.object_id = so.object_id
                join sys.schemas ss on so.schema_id = ss.schema_id
                join sys.database_files sdf on sdf.data_space_id = si.data_space_id
                join sys.filegroups sfg on sfg.data_space_id = sdf.data_space_id
where
                so.type = 'U'
                and so.Name not in ('dtproperties')
order by 
                3 desc, 4, 1, 2