Rotating Header Image

Hey #SQLFamily Where Do You Go?

OK so this video came out like 7 years ago which is pretty well how long it’s been since I’ve blogged.

I miss blogging and the #SQLCommunity #SQLFamily #SQLHelp #SQLPASS…. basically all the hashtags that have anything/everything to do with SQL.

I turned on Tweetdeck for the first time in a LONG time and said a quick “Hello, from the other side”

 

(I’ll spare you the Adele meme) 🙂

 

Low and behold a couple of #SQLBeauties (New Hashtag ALERT) @grrl_geek and @SQLBek remembered me and said hello.  <3

So why am I here?

Deep right?

I’m trying to find where my #SQLPeeps (New Hashtag ALERT) are hanging out these days…. twitter seems quiet with the usual suspects of #SQLPASS and #SQLHelp getting the odd post here or there…. have people just stopped tweeting?

Anyways I made a New Years Resolution to get my name back out there and start getting my presenting chops back because I really miss it (and all of you!!!).

If you’re out there…..

Hit me back, just to chat, truly yours, your biggest fan…. this is Colin

 

Thanks!!

Enjoy!! (Follow me on Twitter: @ColinStasiuk)

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

Do You Even Blog Bro? (Stats, Execution Plans, Indexes, and crazyness)

Have you ever worked with a piece of software for like 15-20 years and ended up doing something with that product that you haven’t or can’t remember doing ever before?

joygasm

 

This happened to me quite recently and although I wont have a bunch of sexy scripts for you to reproduce the problem I will go through what I learned/remembered you can do with statistics.

Scenario:

  • Production database at a customer site is choosing an absolutely shitty execution plan (not using an index as expected).
  • Can’t get a backup
  • Don’t want to “cowboy” it up on a live 24/7 $$$$$$ production system
  • Can’t reproduce in development with an old backup that we have on site.

SadPanda

Trying to determine “why” this is happening was proving to be a rat-hole until I came across this:

http://blog.kejser.org/the-ascending-column-problem-in-fact-tables-part-two-stat-job/

specifically the part that said showed that you can run this:


DBCC SHOW_STATISTICS ([TableName], [StatisticsName]) WITH STATS_STREAM

What this UNDOCUMENTED/UNSUPPORTED/BE VERY CAREFUL USING/GREAT POWER BLAH BLAH BLAH is doing is this is actually scripting out your statistics and the histogram

dsjkfjdsoks

 

Now that you have the statistics from (in my case) the production database that I cannot get a backup of, or actively troubleshoot against, etc I can then run the following statement on my development server (which has an older backup of the database in question restored)


UPDATE STATISTICS [dbo].[TableName]([StatisticName]) WITH STATS_STREAM = 0x01000000010000000000000000000000B242E5900000000000038......etc., ROWCOUNT = 159543018, PAGECOUNT = 2904894
GO

And BOOM… we were able to reproduce and establish the root-cause of the issue.

After googling around for blog posts about STATS_STREAM I did find the following as well:

Anyways this was just one of those moments where I got all excited and had that awesome feeling about trying something new and having it work.

Now that SQL Server 2016 is available to download for everyone I imagine I’ll have more “a-ha” moments

Aha

(If you don’t get the reference you’re young and I hate you and get off my lawn!!!)

Thanks!!

Enjoy!! (Follow me on Twitter: @ColinStasiuk)

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

Microsoft…. Y U NO FIX??

YuNO

 

I would like…. if I may… it take you on a strange journey….

Consider this:

  • A SQL Server database with ALLOW_SNAPSHOT_ISOLATION set to true
  • A stored procedure that creates a #temp table in tempdb and subsequently drops it
  • Multiple calls of this stored procedure (running in Snapshot Isolation mode) happening at the same time

Please tell me how/why I would get this error:

Msg 3961, Level 16, State 1, Procedure CREATETABLE, Line 8
Snapshot isolation transaction failed in database ‘tempdb’ because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction.  It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation.

The_Riddler_(FG)

Riddle me that!!!! (and yes I saved you the horror of seeing me in skintight green spandex)

SETUP (credit: Loris Chiocca):

USE [master]
GO
CREATE DATABASE [SnapshotIsolationDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'SnapshotIsolationDB', FILENAME = N'C:\TEMP\SnapshotIsolationDB.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'SnapshotIsolationDB_log', FILENAME = N'C:\TEMP\SnapshotIsolationDB_log.ldf' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [SnapshotIsolationDB] SET ALLOW_SNAPSHOT_ISOLATION ON 
GO
USE SnapshotIsolationDB
GO
CREATE PROCEDURE [dbo].[ACCESSTABLE]
AS
BEGIN
    SELECT * FROM #TEMPORARYTABLE
END
GO
CREATE PROCEDURE [dbo].[CREATETABLE]
AS
BEGIN
     CREATE TABLE #TEMPORARYTABLE (
      ID int NOT NULL primary key
     )
     waitfor delay '00:00:01'
        exec ACCESSTABLE
 
    DROP TABLE #TEMPORARYTABLE
END
GO

MULTIPLE CONNECTIONS RUNNING CODE BELOW AT THE SAME TIME (credit: Loris Chiocca):

set transaction isolation level snapshot
begin tran
    exec CREATETABLE
commit tran
go

Keep in mind you’ll probably have to rush-click-execute a few times before being able to produce the error…

So this isn’t my code or my find in the first place.

Why am I talking about it then?

3 Reasons…

  1. To hopefully bring more light to this bug
  2. To hopefully help others who run into this problem and their google-fu wasn’t able to point them to the Microsoft Connect Item.
  3. Cause it’s a thorn in my ass right now

Check this out…

https://connect.microsoft.com/SQLServer/feedback/details/777261/concurrency-issue-with-temp-tables-in-snapshot-isolation-level

And the comment of particular concern:

“Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. The reasons for closing this bug is because the scenario reported in the bug are not common enough and due to the risk of implementing a fix it unfortunately does not meet the bar for the current version of the product.

Inconvievable

 

If anyone is curious as to how I “fixed” my problem…. I stopped explicitly dropping the #temp tables in my stored procedure and allowed SQL Server to “clean up after itself” as the Snapshot Isolation errors always seemed to have an issue with the:

DROP TABLE #TEMPORARYTABLE

 

The question outstanding though is if #temp tables are created with the same name by different SPIDs and they exist in tempdb with the unique name that looks like : #temp_____00000090019545666 how can ANOTHER SPID be affected by the creation and dropping of said #temp table???

 

Thanks!!

Enjoy!! (Follow me on Twitter: @ColinStasiuk)

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

PASS Summit 2014 – I’m Speaking!!!

Good Morning!!

With all the huzzah of PASS announcing the speakers and topics recently I thought I would wait a bit before doing the standard “I’m Speaking” blog post 🙂

As always it’s an honor to be selected to speak anywhere but the PASS Summit has and always will have a special place in my heart.

I’ve said it before and I’ll say it again… it’s the most wonderful time of the year for me as I get to recharge my SQL battery and see all the amazing friends and #SQLFamily I’ve had the pleasure of knowing over the years.

Last year I had two sessions and felt VERY overwhelmed and nervous (as it was my first ever Summit as a speaker) this year I’m hoping to be a bit more “myself” and enjoy the experience that much more.

Have a great day and I’ll see you in November!!!

PASS-Summit-2014-Speaking-Badge_250x250

Enjoy!! (Follow me on Twitter: @ColinStasiuk)

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

Waiting For A Pot To Boil… Restore Database Progress

BoilingPot

Ever kicked off a restore and didn’t set the STATS parameter?

Ever had a separate application handle a restore and not give you any feedback at all on how far along a restore is?

Well if your answer is no than this post / script is NOT for you  🙂

BUT

If your answer is YES then this script should tickle your fancy:

SELECT	A.start_time as 'RestoreStartTime',
		A.percent_complete,
		DATEDIFF(minute, A.start_time, getdate()) as 'CurrentRestoreDurationInMinutes',
		((DATEDIFF(minute, A.start_time, getdate()) * 100) / A.percent_complete) as 'EstimatedTotalDurationInMinutes',
		ROUND(((DATEDIFF(minute, A.start_time, getdate()) * 100) / A.percent_complete) - (DATEDIFF(minute, A.start_time, getdate())), 2) as 'EstimatedTimeRemainingInMinutes',
		B.[text]
 FROM	sys.dm_exec_requests A CROSS APPLY 
		sys.dm_exec_sql_text(A.sql_handle) B
 WHERE	A.percent_complete != 0

Thanks!!

Enjoy!! (Follow me on Twitter: @ColinStasiuk)

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.