Thursday 29 September 2011

How to see all running SQL statements

My colleague Paul McMillan left me with this lovely SQL view that you can install on your database to discover all the currently executing SQL (with the full TSQL statement text, not just a buffer from a dbcc inputbuffer call).

It also gives you a link to click to open the query plan as well

image

 

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

Thursday 22 September 2011

Gotcha: Editing tests in Visual Studio 2008 is unresponsive

If you have an issue where by Visual Studio 2008 appears unresponsive during editing the code in a unit test, this could be down to “CMI” = “Code Model Inspection” which involves catching all (yes, *all*) events from the code editing window.  These events are put onto a queue which is polled periodically and then the Test View / Test List Editor is brought up to date.  The default time for this polling is 1.5 sec, but it can be configured from the registry. 

The registry hacks below set this polling interval to one day.  Since I’ve done this, I can edit tests just as smoothly as any other code.

On x86 machines

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\9.0\EnterpriseTools\QualityTools]
"CMUnitTestDelay"=dword:05265c00
"EnableCMI"=dword:00000000

For x64 machines

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\VisualStudio\9.0\EnterpriseTools\QualityTools]
"CMUnitTestDelay"=dword:05265c00
"EnableCMI"=dword:00000000

Tuesday 20 September 2011

How to shut down windows 8

Spent a little time looking at Windows 8 developer preview and it appears this has been baffling a number of users. Found the answer on a you tube video here in the end

http://www.youtube.com/watch?v=5m6-ExUSFGQ

But knowing me, I wanted a more efficient way of doing this, (apart from the classic Win+D, Alt+F4), so noticed in the comments to this video that you can now use Win+I to get to the settings menu directly, and from there, you'll find a power button...

image

Friday 16 September 2011

SQL 2008 Database Maintenance plan not working

The other day we had an issue on SQL Server 2008 (10.0.4000) whereby a newly created maintenance plan was failing to execute.

Turned out this was due to the sp_configure value of "allow updates" having been set to 1.

To fix it we simply ran this sql statement and then set up the maintenance plan again...

sp_configure 'allow updates', 0
reconfigure
go

Thursday 8 September 2011

Outlook Mailbox / Inbox repair tool

Just had to do this again today, after my outlook 2010 wouldn't open, so I thought I would re-blog it here on my new blog just in case I forget the next time.

Found an article on the web that pointed me in the direction of this nifty little tool for MS Office 2010 inboxes.

C:\Program Files (x86)\Microsoft Office\Office14\SCANPST.EXE

Wednesday 7 September 2011

Checking logical reads of TSQL statements

Use the following statistics statements to check the logical reads and other performance stats when running a SQL Statement.
set statistics io on
set statistics time on

SELECT * FROM MyTable
set statistics io off
set statistics time off

Another trick for comparing equivalent sql statements, is to run them both with the execution plan on, and then compare the relative cost to the batch in the resulting execution plans of each statement.

Thursday 1 September 2011

Nifty trick for finding SQL table column names

select top 0 * from MyTable

Gets you the column names instead of having to add a where clause e.g. (where 1= 0)

When is a DateTime? not a DateTime

Interesting fact I just discovered about Nullable<DateTime> (i.e. DateTime? objects) today, is that when you type check it against a non nullable version i.e. System.DateTime, then it will be classed as a DateTime type if the nullable DateTime? has a value.

E.g.

image

image

How to find the last interactive logons in Windows using PowerShell

Use the following powershell script to find the last users to login to a box since a given date, in this case the 21st April 2022 at 12pm un...