THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

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


Published Tuesday, April 5, 2011 11:00 AM by Adam Machanic



Ken Trock said:

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.

April 5, 2011 4:00 PM

Neil Hambly said:

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

April 6, 2011 6:46 AM

Allan T said:

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.

April 7, 2011 12:44 AM

Adam Machanic said:

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.

April 7, 2011 9:22 AM

Adam Machanic said:

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?

April 7, 2011 9:23 AM

Neil Hambly said:

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




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    

April 16, 2011 3:13 PM

Adam Machanic said:

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

April 17, 2011 10:44 PM

Adam Machanic said:

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

April 19, 2011 10:06 AM

Adam Machanic said:

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

April 20, 2011 10:40 AM

Adam Machanic said:

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

April 30, 2011 11:45 AM
New Comments to this post are disabled

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement