Tuesday, February 10, 2015

SQLSentry - a possible gotcha for high-transaction boxes

Blogging it here for my own edification.

We ran into a problem last year where our performance on a box, monitored by SQLSentry, sucked. 

With the help of SQLSentry Support, we were able to find the problem.  Our code uses lots of functions, and their default statement-level trace slowed things down quite a bit - on one box, parsing was cut by 50%.

Note that this is NOT an inherent problem with SQLSentry, but with the level of detail they were collecting via the trace.

(More here: http://sqlblog.com/blogs/linchi_shea/comments/14682.aspx)

From their support (paraphrased and edited for clarity):

The easiest way to test if this helps in your case is just to turn it off and observe.

To disable it, you can go to View-> Settings, then change "Computer Settings" to "SQL Server Settings" , then change "SQL Server Connection" to "Top SQL Settings". [on SQLSentry 8, it's "Top SQL Source"]

Now, set "Collect Statement Events" to False.You will probably need to change "Inherit From Parent (Global)" to False in "Collection Settings", otherwise it won't let you change the value.  Finally, click on another server in the left-side drop down (which changes context) to make sure it's saved.  

" What you will be losing here is really just the statement level detail for cached plans that SQL Sentry is collecting. It is nice to have that level of detail, but also not an absolute necessity."

Friday, January 16, 2015

[T-SQL] A more efficient DELETE using TOP, CTEs, OUTPUT, and somebody else's clever idea.

I would totally give credit to whomever did this, but I can't figure out who it is (otherwise I would have gone to their site, stolen their code, run it in my environment, and then gone off on my merry way... and my spouse wouldn't yell at me for coming home late because I blogged it)

ALSO: There's almost assuredly a better way to do this, but it's already 5:30 so I rushed it.

When you delete from a large table, you delete in batches.  No blowing out logs, less contention, etc, etc.

DELETE TOP (5000) FROM Table_X

But , it gets slower as it progresses, because each time it has to scan until it finds them.   And so your IO goes up each time it runs.
One potential way around that is to use Simon Sabin's trick...

delete t1
from (select top (10000) *
        from t1 order by a) t1

Which uses a derived table to force it to use an indexed order.  But the problem I ran into is that it still gets slower as it progresses.  Both because there are other rows in the table that I'm not deleting, and because it has a LOT of rows to delete. 
Someone (please tell me who and I'll happily change it!) had a really clever idea - figure out each time what the most recent record deleted was, and use that to modify the WHERE clause to only get more recent.  

E.G. your table is indexed (clustered, whatever) on the inserted_datetime.  So you DELETE TOP 5000 ... ORDER BY (which is why I have the CTE; could probably do the derived trick above), and the most recent one deleted was 1/1/2014 12:23:34.567.  You know that because you saved the deleted values to a table (via the OUTPUT), then grabbed the most recent to a variable, which is in the WHERE clause so the next delete starts looking at that time (1/1/2014 12:23:34.567)

Does it work? Like a champ.  My "stupid" delete was taking 30 seconds when I stopped it an hour in.  
This one is still doing batches every 2-5 seconds.  Of course, for this post I had to stop it... and so when it started back up it needed 8 minutes to figure out where it was (and then each subsequent batch took 2-5 seconds. 

Enjoy, and thanks again to whomever had the great idea!!

[SQLSaturday] Presenting at #SQLSatAustin - January 31st 2015! (two weeks away!)

Quick note:

I will be presenting SQLWatchdog (Event Notifications) at SQL Saturday Austin.  It looks like it's going to have a ton of good presentations - heck, there's a service broker presentation up against mine!  And there's some recently-under-NDA stuff that Conor Cunningham will be presenting on, too!  And a wish-I-could-go-but-my-family-is-coming Precon on Friday, as well.

All around, good selection of speakers, good precons - a good time had by all.  Come learn some new SQL tricks - I've been at a ton of these now, and I still learn something new.  It costs $10 for a day of training(!!!!!), and that's basically covering lunch.  I'm psyched.  Maybe I can even eat at Franklin's!



This will probably be one of my last presentations of SQLWatchdog, unless I make PASS 2015.  I've pretty much hit all the SQLSats that are within decent driving distance (and a couple that aren't), so it's time to come up with a new presentation.  Mixed feelings about it - still ultra-psyched about EN (and I still use the everliving daylights out of it), but I don't/can't just be "that guy" with one presentation.  Well, not if I want to go back to these places.  : )

Wednesday, January 7, 2015

[BULK INSERT] importing a fixed-width file that has header rows ala SQL, but without a format file.

Needed this for today.  Basically someone saved out a query as fixed width, but we weren't able to import it (Excel, BIDS & Import/Export Wizard all failed).  So I decided to build something that could also do it.

A basic file would look like this.  Note that the dashes and field names line up.  So what we do is really basic but still effective - look for those singleton spaces, then use that to pull out the details from the header row. 

STORE_CITY                          STORE_STATE STORE_ZIP
----------------------------------- ----------- ---------
RICHMOND                            VA          23229
SPRINGFIELD                         MO          65807

The one down side is that I don't have time to tweak it to get it working with sp_executesql (since my particular query is over 8000 characters), so I print out each line, toss that into my SELECT, strip out the last comma and add the tablename. 

CREATE TABLE bulk_insert_raw 

    (id INT IDENTITY PRIMARY KEY, resultant VARCHAR(8000))
--view is needed in order to do a BULK INSERT with an inline IDENTITY - using staging table would NOT guarantee order.
CREATE VIEW bulk_insert_view 

SELECT resultant FROM bulk_insert_raw

BULK INSERT bulk_insert_view
   FROM '\\myserver\myfile.csv'
         FIELDTERMINATOR = '|',
         ROWTERMINATOR = '0x0a'  --if a UNIX-type file

DECLARE @min INT, @max INT, @header VARCHAR(8000), @dashes VARCHAR(8000), @last_dash INT, @last_field VARCHAR(200), @sql VARCHAR(max)
SET @min = 1

--The offset is because our particular data file had a bad first row. 

--We know line 1 and 2 are the header/dashes.
SET @header = (SELECT SUBSTRING(resultant,4,8000) FROM bulk_insert_raw WHERE id = 1)
SET @dashes = (SELECT resultant FROM bulk_insert_raw WHERE id = 2)

SET @max = LEN(@dashes) +2 --so we get the last field.
SET @last_dash = 1

SET @sql = 'select '
WHILE @min < @max
    IF (SELECT SUBSTRING(@dashes,@min,1)) IN (' ', '')
            SELECT @last_field = QUOTENAME(RTRIM(SUBSTRING(@header,@last_dash, (@min)-@last_dash    )))
            PRINT @last_field + CONCAT(' = substring(resultant,', @last_dash, ',', (@min)-@last_dash,')', ',')
            SET @last_dash = @min + 1

    SET @min = @min + 1

Tuesday, January 6, 2015

[Event Notifications] Changing security on the endpoints.

(Note: I still owe Justin some help on EN)

Recently had an issue trying to remove my login from a server (I've been transitioning from Dev & Ops to more Dev & Arch), and found I couldn't because of some of the permissions I had.  Namely: EN's (and thus Service Broker's) endpoint.

A former coworker running "SQLWatchdog" (aka my EN solution) came up with this code to fix the issue.  The second block of code fixes the endpoint for Always On.

And here's someone else who had the problem.  Code courtesy of both of them.


USE master;
SELECT SUSER_NAME(principal_id) AS endpoint_owner
  ,NAME AS endpoint_name
FROM sys.service_broker_endpoints;

USE master;

USE master;
SELECT SUSER_NAME(principal_id) AS endpoint_owner
  ,NAME AS endpoint_name
FROM sys.service_broker_endpoints;

USE master;
SELECT SUSER_NAME(principal_id) AS endpoint_owner
  ,NAME AS endpoint_name
FROM sys.database_mirroring_endpoints;

USE master;

USE master;
SELECT SUSER_NAME(principal_id) AS endpoint_owner
  ,NAME AS endpoint_name
FROM sys.database_mirroring_endpoints;

Friday, December 12, 2014

[Presenting] What do I present on next?

Title says it all.  At this point I've pretty much hit every SQLSaturday that's easily reachable from here.  I've had a ball doing it, and the coworkers I drag along with me have had a lot of fun as well.

But I've presented on that topic (Event Notifications) at them.  So what's next?

I need to come up with a new session.  I can obviously just GO (maybe as a volunteer), but I think I have more fun when I'm there to present.  I wouldn't have thought so when I first started doing it, but it's the case and I'm cool with it. 

I just don't know what to present on.  Mark V, in his session, said that any topic is a good topic - everyone brings their experiences to it, and everyone explains it differently.  That being said, there are people who (in my mind) own their topic - they're of the MVP/MCM level (which I'm not in one case, and can never be in the other case), and their topic is freaking fantastic.

So, I want to come up with something useful to many people, and preferably something not often covered. 

Here are some of the thoughts that have been going through my head:

  • The $25000 (per core) Pyramid - what enterprise features don't you use?
  • ($25000 per) CORE - What is it good for?
Would probably be a relatively high-level session.  Cover Peer-to-Peer replication, Hekaton, Columnstore/Clustered Columnstore, things-that-used-to-be-Enterprise-but-are-now-standard,  AO, online restores, online indexes, mirrored backups, partitioning, compression, distributed replay, CDC etc.  Could even do non-Engine features like Tabular Model (BISM) and DQS.


  • Save all the things!  
A session about saving out various things for later, and parsing them for info.  I've got a solution built in Powershell called the EPR that's saved my bacon a bunch (using it right now to figure out when code was last run on all my servers), but could also cover things like pulling out connection strings from SSIS packages, etc.  But I could also expand parts into using Powershell, using SMO, etc.

  • SP_Server_Diagnostics for fun and profit
This would be a decent amount of work - while I have code that will shred out the results, there's a LOT more to it than that.  And I could expand and cover system_health.  The one problem is that Kehayias had a decent session about it at PASS 2013.


Aaaaaaand.... that's all I have right now.  Any thoughts gratefully appreciated.

Tuesday, December 9, 2014

[Presenting] SQLSatDC (SQL Saturday #347) was a blast!

A gigantic thank you to the whole team at SQLSaturday347, aka SQLSatDC 2014.

I had an absolute blast.  Rubbed shoulders with a bunch of people I know of, chatted with some SQLSat friends, met a bunch of new people I want to see more of, and even managed to take in the sights (and some tapas!).

Let's see, what did I see/do?

Thursday/Friday - tourista!

Friday night - speaker's dinner.  A nice little Spanish/LatinAmerican restaurant.  After a bit of "how do we order", managed to get everything straightened out, and had a blast.  Tapas, Paella, lots of SQL and non-SQL conversations, etc.

Saturday morning - going by the vendor tables, some groups I haven't seen before.   Always nice to see.  Wow, a Linchpin People table! (First time I've seen it, though I've run across at least 5 of them at different events, and they've been uniformly amazing folk)

 8:15am - me!  Standing room only, which as a presenter is honestly pretty cool.  Some good questions and comments, which means I need to incorporate them as well as strip out other things.  At this point I've gotten my presentation up to right at 70 minutes, sans breathing, so it's time to strip out some of the less-important slides.

9:40 - David Dye, Dealing with Errors in SSIS 2012.  Some interesting stuff from a SSIS developer perspective, how to save errors out, prevention, dealing with them, etc, etc.  Less interesting for me as a DBA, though if my devs did those fixes now I'd know where to look.  As is, I have to keep using my own email notifier for SSIS Project Deployment Model failures.

11:05 - Jason Brimhall, Murder They Wrote. It's the one-hour summary of the day-long summarization of a week long training session.  *Whew*!  Very nicely done - while I knew many of them, he did a great job explaining them.  The only odd part was that very few people seemed to know what Clue was(!).  And anybody who can make a "boot to the head" reference (Yay Frantics!) is a hero to me. 

Lunch: Fajitas!  And a head shot!  (Thanks, Chris!) And chatting with one of the attendees for a bit, talking about EN, CI, and other two-letter acronyms. And ended up by getting pinned down by one of the redgate folk and looking at their SQLLitehouse tool, which does a lot of what EN (and therefore my presentation) does.  Wonder if I can steal any ideas?  Is that legit, if I'm doing this for fun?

1:30 - Jimmy May, Columnstore, Flipping the Faster Bit.  Apparently I missed the part where he did a handstand, but I'll tell you what, he was the most animated speaker I've seen in a while.  Very knowledgeable, some useful information (and I'm running CS/CCS!), and I got to pick his brain after.  My only suggestion (and I made this to him already) was that if we're in the session, we don't need as much "marketerization" slides (but he knew that already). But it did explain some of the tuple mover issues I've seen, so... WIN!

2:55 - Samuel Vanga, SSIS Data Flow under the hood.  Something I haven't seen much of, specifically a closer-to-the-metal session about tuning SSIS, almost from a DBA perspective.  I swear I've seen some of it before (Tim Mitchell, probably), but it's nice to see more internals covered.  Normally it's very much a "oh, everything works", but this was more about low-level tuning, async vs non-async, spilling to disk, etc.

Anyhow, I'll definitely try and come back - they had a great turnout with some fantastic speakers!

Tuesday, December 2, 2014

[Presenting] SQLSatDC, aka SQLSat347, this weekend (Dec 6 2014)!

Come see me speak in Washington DC!  (Okay, fine, and another 29 speakers like Grant Fritchey, Allan White, Steve Jones, Jessica Moss, Randy Knight, Jason Brimhall, Jimmy May...).

There's even a pre-con on Friday - day-long training on BIMLScript (different venue, though, I think).

SQL Saturday DC 2014.
A mere $10 gets you in - and that includes lunch!
Doors at 7:30am, starts at 8:15am.

Microsoft MTC, 5404 Wisconsin Ave, Chevy Chase, MD, 20815