Tracking your time as a contractor

Helpful little utility should you need to track the time spent at your desk during the day...

 

http://www.timesnapper.com/screenshots.aspx

How to get your unit tests (test project in Visual Studio 2008, a.k.a. MSTest) run multithreaded

 

Add this to the configuration for the test

<TestRunConfiguration ...>

...

<ExecutionThread apartmentState="MTA" />

</TestRunConfiguration>

To check the threading model used, add the following line to your test(s):

[TestMethod]

public void YourTest()

{

System.Diagnostics.Debug.WriteLine(System.Threading.Thread.CurrentThread.GetApartmentState().ToString());

// TODO: Add test logic here

}

http://blogs.msdn.com/b/irenak/archive/2008/02/22/sysk-365-how-to-get-your-unit-tests-test-project-in-visual-studio-2008-a-k-a-mstest-run-multithreaded.aspx

How to just send the updated columns via NHibernate

Use the following attribute in the top of the mapping file to ensure your update statements are as succinct as possible

Dynamic-update="true"

This will ensure only those column values that have changed actually exist in the SET clause of the UPDATE statement, which is useful if your tables have triggers on them guarded by the TSQL IF UPDATE(MyColumnName) type checks.

How to determine SQL log file space used

 

dbcc sqlperf(logspace);
exec sp_spaceused

More info here...

http://msdn.microsoft.com/en-us/library/ms189768.aspx

Currently executing SQL view

 

Useful system view that you can create to view the exact (FULL) sql statement that's currently running on a sql server. Kind of like an sp_who2 but with the full executing sql statement shown.

CREATE VIEW [Current_Running_SQL]
AS
SELECT            CASE
                        WHEN  Requests.sql_handle IS NULL
                        THEN  ' '
                        ELSE  SubString
                                    (
                                          Statements.text,
                                          (Requests.Statement_Start_Offset+2)/2,
                                          (
                                                CASE
                                                      WHEN  Requests.Statement_End_Offset = -1
                                                      THEN      LEN(CONVERT(nvarchar(MAX),Statements.text))*2
                                                      ELSE  Requests.Statement_End_Offset
                                                END
                                                -
                                                Requests.Statement_Start_Offset
                                          )
                                          /
                                          2
                                    )
                  END                                                               AS StatementText,
                  QueryPlans.query_plan                                 AS QueryPlan,
                  Statements.Text                                             AS Batch_Text,
                  Sessions.Session_ID,
                  Sessions.Login_Name,
                  Sessions.Host_Name,
                  Sessions.Program_Name,
                  Sessions.Client_Interface_Name,
                  Requests.Wait_Time,
                  Requests.Cpu_Time,
                  Requests.Total_Elapsed_Time,
                  Requests.Reads,
                  Requests.Writes,
                  Requests.Logical_Reads,
                  Requests.Row_Count,
                  Requests.Granted_Query_Memory*8/1024            AS Granted_Query_Memory_MB,
                  LEN(Statements.text)                                  AS Batch_Text_Length,
                  Requests.Statement_Start_Offset/2               AS Statement_Start_Offset,
                  CASE
                        WHEN  Requests.Statement_End_Offset = -1
                        THEN  LEN(CONVERT(nvarchar(MAX),Statements.Text))*2
                        ELSE  Requests.Statement_End_Offset
                  END    
                  /
                  2                                                                 AS Statement_End_Position,
                  (
                        CASE
                              WHEN  Requests.Statement_End_Offset = -1
                              THEN  LEN(CONVERT(nvarchar(MAX),Statements.text))*2
                              ELSE  Requests.Statement_End_Offset
                        END
                        -
                        Requests.Statement_Start_Offset
                  )
                  /
                  2                                                                 AS Statement_Text_Length
FROM        sys.dm_exec_sessions                      Sessions
INNER JOIN  sys.dm_exec_requests                      Requests
ON                Sessions.session_id                             = Requests.Session_ID
CROSS APPLY sys.dm_exec_sql_text(sql_handle)    Statements
CROSS APPLY sys.dm_exec_query_plan(plan_handle) QueryPlans
WHERE       Sessions.Session_ID                             != @@SPID
GO

Select TOP @recordCount in TSQL

This is how you can limit the results from a table based on a parameter's value in non dynamic TSQL

declare @recordCountint = 100

SELECT TOP (@recordCount)

*

FROM MyTableName

All you have to do is not forget the enclosing brackets around the variable in the SELECT TOP clause and it works! Without these brackets you see the following error...

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '@recordCount'.