in

SQL Server Blogs

Voices from the UK SQL Server Community

Jason Massie's SQL blog

December 2007 - Posts

  • SQL Connections Spring 2008

    I am speaking SQL Connections in Orlando this April. The session is entitled "Augmenting the DBA toolbox with SSRS" It will be similar to my pass session except it is all about Reporting Services and it will be 100% SQL 2008. However, most reports will be 2005 compatible with minor changes. It will totally be about working smarter not harder. I will have a ton of tools that you can integrate into your environment and I will show you how to put your existing tools to work.

    I am really excited about this. I missed this year but I went in 2006 and 2005. For more infomation, check out http://sqlconnections.com You can also read the co-chair blogs here and here.

     

    *Cross posted*

    http://statisticsio.com

     

  • A quick and dirty WTF???!!!11one

    You can use this to get a quick and dirty picture of what is going on with the server. We basically grab a snapshot, wait a second and then get a diff. Sort as needed.

     select r.cpu_time

                    ,r.logical_reads

                    , r.session_id

    into #temp

    from sys.dm_exec_requests as r

     

    waitfor delay '00:00:01'

     

    select substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1 then datalength(h.text)  else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text

                    , r.cpu_time-t.cpu_time as CPUDiff

                    , r.logical_reads-t.logical_reads as ReadDiff

                    , r.wait_type, r.wait_time

                    , r.last_wait_type

                    , r.wait_resource

                    , r.command

                    , r.database_id

                    , r.blocking_session_id

                    , r.granted_query_memory,r.session_id

                    , r.reads

                    , r.writes

                    , r.row_count

                    , s.[host_name]

                    , s.program_name

                    , s.login_name

    from sys.dm_exec_sessions as s inner join sys.dm_exec_requests as r

    on s.session_id =r.session_id and s.last_request_start_time=r.start_time

    left join #temp as t on t.session_id=s.session_id

    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h

    where is_user_process = 1

    order by 3 desc

     

    drop table #temp

     

    *note*

    cross posted from http://statisticsio.com

     

Powered by Community Server (Commercial Edition), by Telligent Systems