Less Data is More Data (A Month of Monitoring, Part 5 of 30)

re: Less Data is More Data (A Month of Monitoring, Part 5 of 30)

Ken Trock — Tue, 05 Apr 2011 20:00:28 GMT

Got a few SPIDs on one of our production servers under the default configuration.

So far I've noticed that you're able to get the whole sql_text for an active SPID. I know they cutoff routinely in Performance Dashboard. Maybe that's 2k or 4k characters. Will have to wait until we get a real massive query :) This is cool.

re: Less Data is More Data (A Month of Monitoring, Part 5 of 30)

Neil Hambly — Wed, 06 Apr 2011 10:46:19 GMT

Cool Series - will add to my daily reading list (need to move some stuff of that as it's getting very big)

BTW Did seen an odd thing with @show_system_spids = 1 I noticed the system one have a {start_time} as dates in the future I.E

only seems to be on these Builds: 10.xx

dd hh:mm:ss.mss session_id sql_text login_name

90 15:16:10.000 9 NULL sa

start_time request_id collection_time

2011-04-16 06:13:45.000 0 2011-04-06 11:15:14.080

re: Less Data is More Data (A Month of Monitoring, Part 5 of 30)

Allan T — Thu, 07 Apr 2011 04:44:36 GMT

Great series.

A colleague told me about this script.

I have used it at a customer site to troubleshoot some issues.

I am looking forward to reading through this series as is progresses.

I have run into one inconvenient issue. It is difficult to export the results of sql_text to--say--Excel. It generates a new line after <?query-- and then again before ..?>, shifting all of the other columns.

As the series progresses, I am hopeful for discussion on how to customize this output.

re: Less Data is More Data (A Month of Monitoring, Part 5 of 30)

Adam Machanic — Thu, 07 Apr 2011 13:22:33 GMT

Hi Allan,

There will be a post on output customization. Whether or not it will fix your Excel issue is another question entirely. I have some other ideas for you, though. Stay tuned to the series and follow up later this month if you haven't figured it out yet.

re: Less Data is More Data (A Month of Monitoring, Part 5 of 30)

Adam Machanic — Thu, 07 Apr 2011 13:23:56 GMT

Hi Neil,

Dates in the future are of definite concern! This is not something I've ever seen before. What does sys.dm_os_sys_info report in the sqlserver_start_time column?

re: Less Data is More Data (A Month of Monitoring, Part 5 of 30)

Neil Hambly — Sat, 16 Apr 2011 19:13:06 GMT

Hi Adam

it seems that this is only showing dates in the future only for background spids, I have checked this is happening on both 2005 & 2008 systems, it maybe due to high values in sqlserver_start_time_ms_ticks perhaps

Example of one of the systems

sqlserver_start_time_ms_ticks

971501395

sp_whoisactive

spid 1

dd hh:mm:ss.mss session_id sql_text login_name wait_info tasks CPU tempdb_allocations tempdb_current blocking_session_id reads writes context_switches physical_io physical_reads used_memory status open_tran_count percent_complete host_name database_name program_name start_time request_id collection_time

35 15:16:10.000 1 NULL sa NULL 1 840,299 0 0 NULL 6 1 147,901 1 1 0 background 0 NULL master 2011-04-18 13:45:22.000 0 2011-04-16 20:06:03.357

EXEC sp_WhoIsActive

@get_task_info = 2

,@show_system_spids = 1

Select * from sys.dm_os_sys_info

re: Less Data is More Data (A Month of Monitoring, Part 5 of 30)

Adam Machanic — Mon, 18 Apr 2011 02:44:41 GMT

Thanks to Neil running some more tests on his end, this issue has been fixed. A new version will be posted shortly.

Getting More Information (A Month of Activity Monitoring, Part 18 of 30)

Adam Machanic — Tue, 19 Apr 2011 14:06:32 GMT

This post is part 18 of a 30-part series about the Who is Active stored procedure. A new post will run

Deciding What (Not) To See (A Month of Activity Monitoring, Part 9 of 30)

Adam Machanic — Wed, 20 Apr 2011 14:40:09 GMT

This post is part 9 of a 30-part series about the Who is Active stored procedure. A new post will run

Twenty Nine Days of Activity Monitoring (A Month of Activity Monitoring, Part 30 of 30)

Adam Machanic — Sat, 30 Apr 2011 15:45:02 GMT

This post is part 30 of a 30-part series about the Who is Active stored procedure. A new post will run