[WAT] bizarrreness with ISNULL

Coworker came across this, and even found an article by Aaron Bertrand about it.

http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/

But the practical takeaway we had today, from Aaron's code - ISNULL can cause truncation:

DECLARE @c5 VARCHAR(5);
SELECT 'COALESCE', COALESCE(@c5, 'longer name')
UNION ALL
SELECT 'ISNULL',   ISNULL(@c5,   'longer name');

[Extended Events] What servers are connecting to my SQL Server 2012 box?

(code is complete, soup-to-nuts, but only runs on 2012; I'll modify it for 2008/R2 in another post.)

(update 2013/04/18 Jonathan Kehayias helpfully provided a way to do it by adding the existing_connection event - but there appears to be a bug with the histogram, and it doesn't return the right data.  Connect item if you want to vote on it: https://connect.microsoft.com/SQLServer/feedback/details/785042/extended-events-histogram-does-not-properly-save-existing-connection-results)


First of all, MANY thanks to Jonathon Kehayias for all the XE wisdom and code - I modified the snot out of his http://www.sqlskills.com/blogs/jonathan/tracking-sql-server-database-usage/ lock-tracking-using-a-histogram to build this...

Say I want to figure out what servers are connecting to my SQL Server.  This is something you'd traditionally either poll sysprocesses for periodically, or probably run a trace.  Both have issues, though, especially if they're busy servers.  So, how can we get that?  Extended Events.

The easiest way to do this would be using Extended Events - create a "bucketizer" (now called the histogram), and watch for new logins, saving the servername and incrementing a counter each time it happens.  As a bonus, we could then filter it - exclude particular applications, servers, etc (see the commented out code).  And, since it's a pretty basic XE, overhead is very low.  The one caveat is that you can only get one piece of information out - servername.  I'd LOVE to get the App Name & DB Name as well, but you can only get one piece of information at a time.  Downer.  : - (

One caveat: If you have servers that stay connected, and don't open new connections, you won't see them here.  The obvious exclusion, then, is replication, but maybe your app hangs on for a long time (we've seen that with third-party tools).  Easy answer for those is to use SP_WHOISACTIVE.

Any questions?

[Servers] Extensible (Powershell) Repository - just add scripts!

(Version 1.1. I so should've posted this earlier - looks like everybody has been releasing their version of this idea over the past week).


Over the past few months, we've been working on knowing more about our servers.  Besides sp_blitz, there's a lot of data we want: DMVs, WMI info, etc. So a coworker & I had a challenge going - which would be a faster way to query our 80+ production servers, SSIS or PowerShell?  Well, he had it running faster, but then I asked him to up the number of simultaneous threads and it was a chore for him to change.  For me, alter a line of code in a text file.  And it seemed easier for me to add more scripts.

So I decided to make mine, while not (necessarily) the fastest, the easiest to use.  It uses Powershell and some PS scripts originally written by Chad Miller, Arnoud Jansveld, and several other people smarter than me.  I just put it all together.

TL;DR: Drop a query in a folder, and it runs several threads in parallel against all your servers, saving the details out to a table, overwriting the old data if you want.

Pros:

• Trivial install - 6 scripts in a folder, a table with a list of servers, 1 or 2 empty subfolders, and 1 job.
• Low overhead - a sample (simple) script ran on 80+ servers in under 6 seconds.
• Easy to add a new collection - just drop a SQL/PS script in the folder with the name for the table.
• Need more servers done faster?  Up the threads.
• Skips servers it can't connect to.
• No powershell extensions needed - just those 6 scripts.  PS3 below, modify the one line for PS2.

Cons:

• Does not deploy code; just runs scripts. (so sp_blitz, for instance, needs to be installed separately)
• No failure info. That's on my to-do list, but is not nearly as easy as I'd like
• Datatypes for new tables need to be tweaked if the script creates the table - text fields default to varchar(1000)

To install:

• Create a table with a list of servers to monitor.
• Create 3 folders: c:\sql_tools (or wherever; change the actual script's foldername), and underneath it one for scripts where you want to keep the data, and one where you don't.
• Grab add-sqltable.ps1, write-datatable.ps1, and invoke-sqlcmd2.ps1 from Hey Scripting Guy or poshcode. Put in c:\sql_tools.
• http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx
• http://poshcode.org/3679 (Split-Job)
• Save the below script to a file called "repository_extensible.ps1", in c:\sql_tools.
• Create a job with 2 job steps, both as Type: "Operating System (CmdExec)":
• powershell "& c:\sql_tools\repository_extensible.ps1 c:\sql_tools\repository_scripts_keep 0"
• powershell "& c:\sql_tools\repository_extensible.ps1 c:\sql_tools\repository_scripts_delete 1"
• The first script runs scripts where each time, records will be added to the table.  The second will delete records from each server, replacing it with the new records. 
• Toss a couple sample scripts in the folders 
• For SQL, I recommend Paul Randall's excellent Wait & Latches scripts
• http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
• http://www.sqlskills.com/blogs/paul/survey-most-prevalent-latch-waits-code-to-run/
• For Powershell, you will need to tell the script what computer to run against.  This is because we're not using something like SQLCMD which has a "server" parameter for the entire script.
• Note the " -computername $args[0]" - that allows us to tell it which server to query.
• Get-WmiObject Win32_LogicalDisk -computername $args[0] 
• Name each scripts tablename_you_want.sql - the base name becomes the table name
• Run the job once.
• Go into your repository database and modify the table; by default strings automatically become varchar(1000).  Yes, it's not great, but will work for now.

[Replication Monitor] YA monitor - check delay times with last_distsync and sp_replmonitorhelpsubscription

Got hosed because I accidentally added a a clause in our "how backed up are we" from http://thebakingdba.blogspot.com/2010/10/replication-better-alternative-to.html , and set it up wrong, so I filtered stuff I didn't mean to.

Lessons learned, and got me thinking ... One thing I haven't had on my replication monitor was a reliable way of detecting if any of my subscriptions are expired or about to expire.  The dread 72 hours.  So, several hours later, a profile trace and a question on StackOverflow, and I got a way to do it.

Basically, you're running the system SP sp_replmonitorhelpsubscription, once for each server that the distributor handles.  You then filter that out (looking for warnings and ignoring recent records) and send an email if there's anything left.

One downside: because we're trying to avoid the NESTED EXEC issue (can't have an INSERT INTO EXEC where the code you're running has an INSERT INTO EXEC), the most reliable way to avoid it is by using OPENROWSET (thanks to Paul Ibison for that).  However, that requires that Ad Hoc Distributed Queries be enabled via sp_configure.  Yes, it can be a security hole.

An alternative way to do it, according to replication expert Hilary Cotter, is to run the SP twice, and only INSERT INTO on the second Exec.  In my testing it's not as reliable (fails occasionally, and doesn't lend itself to automation quite as well, but it may be a better option for you.

Hope this helps (and hopefully the blogspot sourcecode formatter I'm using is good)!

[tips] using OPENROWSET to avoid "An INSERT EXEC statement cannot be nested."

Found this, looks like from Paul Ibison (replicationanswers.com).  In this case I _did_ need it for replication, but this could be useful whenever you are run into the error "An INSERT EXEC statement cannot be nested.".

One note - depending on how the original piece of code works, you may or may not need the SET FMTONLY OFF;.  (In this case I do).  If you do, be aware that the code is run TWICE.  For this, it works fine, but keep it in mind.

USE eif_workspace
GO
create table dbo.repmonitor (
[status] int null,
warning int null ,
subscriber sysname null ,
subscriber_db sysname null ,
publisher_db sysname null ,
publication sysname null ,
publication_type int null ,
subtype int null ,
latency int null ,
latencythreshold int null ,
agentnotrunning int null ,
agentnotrunningthreshold  int null ,
timetoexpiration  int null ,
expirationthreshold  int null ,
last_distsync  datetime null ,
distribution_agentname  sysname null ,
mergeagentname  sysname null ,
mergesubscriptionfriendlyname  sysname null ,
mergeagentlocation  sysname null ,
mergeconnectiontype  int null ,
mergePerformance  int null ,
mergerunspeed float null ,
mergerunduration int null ,
monitorranking  int null ,
distributionagentjobid  binary(30) null ,
mergeagentjobid binary(30) null ,
distributionagentid  int null ,
distributionagentprofileid int null ,
mergeagentid int null ,
mergeagentprofileid int null ,
logreaderagentname sysname null
)
go

Insert Into aud.dbo.repmonitor

SELECT *

FROM OPENROWSET('SQLNCLI', 'Server=yourservername;Trusted_Connection=yes',  'set fmtonly off;  exec distribution..sp_replmonitorhelpsubscription @Publisher=@@servername,@publication_type=0')

[Extended Events] Finding new connections and saving to a asynchronous bucketizer/histogram

We're trying to get rid of our .Net SQLClient Data Provider apps.  Well, trying to get rid of the useless name.  How do we do that?  By figuring out which servers they're coming from and which databases they're hitting, and giving that to our systems folk so they can find the connection strings and add Application Name.

My first thought was EN...  ha!  No, it won't work for that, I don't think.
My second thought were traces.  Better, but we'd need to just get audit_login, then we'd have to parse it out, etc.
So my third thought was to use XE.  Lo and behold, it works!  Get the client host names, and save them to a bucket.  Then, as they connect, either add to or increment the number for that bucket.

-- If the Event Session Exists, drop it first
IF EXISTS (SELECT 1
FROM sys.server_event_sessions
WHERE name = 'UnknownAppHosts')
DROP EVENT SESSION UnknownAppHosts
ON SERVER;

-- Create the Event Session
CREATE EVENT SESSION UnknownAppHosts
ON SERVER
ADD EVENT sqlserver.login(
    ACTION(sqlserver.client_hostname)
WHERE ([sqlserver].[client_app_name] LIKE 'Microsoft SQL Server Management%')
)
ADD TARGET package0.histogram
( SET slots = 50, -- Adjust based on number of databases in instance
 filtering_event_name='sqlserver.login',
 source_type=1,
 source='sqlserver.client_hostname'
)
WITH(MAX_DISPATCH_LATENCY =1SECONDS);
GO

-- Start the Event Session
ALTER EVENT SESSION UnknownAppHosts
ON SERVER
STATE = start ;
GO

-- Parse the session data to determine the databases being used.
SELECT  slot.value('./@count', 'int') AS [Count] ,
        slot.query('./value').value('.', 'varchar(20)')
FROM
(
SELECT CAST(target_data AS XML) AS target_data
FROM sys.dm_xe_session_targets AS t
    INNER JOIN sys.dm_xe_sessions AS s
ON t.event_session_address = s.address
WHERE   s.name = 'UnknownAppHosts'
 AND t.target_name = 'Histogram') AS tgt(target_data)
CROSS APPLY target_data.nodes('/HistogramTarget/Slot') AS bucket(slot)
ORDER BY slot.value('./@count', 'int') DESC

GO


-- Start the Event Session
ALTER EVENT SESSION UnknownAppHosts
ON SERVER
STATE = STOP ;
GO