Puzzles so big they ship with their own hand truck

I like doing puzzles, usually I do them between December 20 and January 3rd, during this time I am usually not working so I have some free time to do these puzzles. I was looking on Amazon today and noticed these two huge puzzles.

Keith Haring: Double Retrospect - 32000 Pieces Puzzle

This puzzle must be incredible difficult to complete, it would probably take me months to do this. Here are some details

  • Features a retrospective with 32 individual, brightly-colored images of Keith Haring's artwork
  • Includes 32,256 precision-cut pieces packaged in eight separate poly bags
  • Consists of just six colors plus black and white, adding to the already incredible challenge
  • Measures more than 17' x 6' long
  • Weighs 42 lbs and comes with its own hand truck

You can see how huge this puzzle is by lookingLooking at the picture below gives you a sense just how huge this puzzle is

Do you need to move the box around? No problem, this puzzle comes with its own hand truck

I am still not sure if I would ever do that puzzle and if I did what would I do with it after I finished it? Maybe hang it on the wall and pretend it is art? What would you do?

 

Ravensburger New York City Jigsaw Puzzle (32000-Piece)

This is another huge puzzle, instead of art it is of New York City, here are some details, it is petty similar to the Keith Haring puzzle.

  • 32000 piece puzzle featuring a panoramic view of NY City
  • Stunning photographic detail
  • Includes 32,256 precision-cut pieces packaged in eight separate bags
  • Measures 17 x 6 feet!
  • Weighs 42 lbs. and comes with it's own hand truck

Here is a picture of the puzzle when it is completed, assuming the person is not a hobbit, you can see that this thing is massive

What is a valid variable name in T-SQL?

On twitter Adam Machanic posted a question if anyone has a document that details what a valid variable name is, what is allowed and what is not allowed.

Did you know that you can have a variable named @@ or @@@@?

DECLARE @ int=5,@@ int
SET @@=@
DECLARE @@@@ int=@*@@
DECLARE @$$ int=@@@@
SELECT  @$$,@@@@

Running that will print  25 25

There is a Books On Line page that gives some details but it is not very explicit, you can find that page here Identifiers

But what is valid? Let's write a quick SQL script, we are going to use the spt_values in the master database to quickly generate a SQL statement which we can execute

Fiest step is to dump what we want into a temporary table

SELECT number,'declare @' + CHAR(CONVERT(VARCHAR(5),number)) +' int' AS stmt
INTO #test
FROM master..spt_values WHERE type='p'

SELECT * from #test

Running that will give you something like this if you have the output in grid not text

variables.PNG

Don't be fooled by that the first 33 rows, those will be generated as @ and will appear valid, those are non printable characters

So let's continue with our SQL script after you created the temp table, run the following, if there is an error, the script will catch it and print the character number

DELETE #test WHERE stmt IS NULL
DELETE #test WHERE number < 33
DELETE #test WHERE stmt like '%''%' OR stmt LIKE '"'

DECLARE @stmt VARCHAR(100)
DECLARE @start int = 0,@end int =(SELECT max(number) from #test)
WHILE @start <= @end
BEGIN
    SELECT @stmt = stmt from #test where number = @start
    BEGIN TRY
        EXEC(@stmt)
    END TRY
        BEGIN CATCH 
        PRINT 'error ' + right('000' + CONVERT(VARCHAR(3),@start),3) + ' ' + @stmt
    END CATCH

    SET @start+=1
END

Running that code will give you the following output

error 033 declare @! int
error 034 declare @" int
error 037 declare @% int
error 038 declare @& int
error 039 declare @' int
error 040 declare @( int
error 041 declare @) int
error 042 declare @* int
error 043 declare @+ int
error 044 declare @, int
error 045 declare @- int
error 046 declare @. int
error 047 declare @/ int
error 058 declare @: int
error 059 declare @; int
error 060 declare @< int
error 061 declare @= int
error 062 declare @> int
error 063 declare @? int
error 091 declare @[ int
error 092 declare @\ int
error 093 declare @] int
error 094 declare @^ int
error 096 declare @` int
error 123 declare @{ int
error 124 declare @| int
error 125 declare @} int
error 126 declare @~ int
error 127 declare @ int
error 128 declare @€ int
error 129 declare @ int
error 130 declare @‚ int
error 132 declare @„ int
error 133 declare @… int
error 134 declare @† int
error 135 declare @‡ int
error 136 declare @ˆ int
error 137 declare @‰ int
error 139 declare @‹ int
error 141 declare @ int
error 143 declare @ int
error 144 declare @ int
error 145 declare @‘ int
error 146 declare @’ int
error 147 declare @“ int
error 148 declare @” int
error 149 declare @• int
error 150 declare @– int
error 151 declare @— int
error 152 declare @˜ int
error 153 declare @™ int
error 155 declare @› int
error 157 declare @ int
error 161 declare @¡ int
error 162 declare @¢ int
error 163 declare @£ int
error 164 declare @¤ int
error 165 declare @¥ int
error 166 declare @¦ int
error 167 declare @§ int
error 168 declare @¨ int
error 169 declare @© int
error 171 declare @« int
error 172 declare @¬ int
error 173 declare @­ int
error 174 declare @® int
error 175 declare @¯ int
error 176 declare @° int
error 177 declare @± int
error 178 declare @² int
error 179 declare @³ int
error 180 declare @´ int
error 182 declare @¶ int
error 183 declare @· int
error 184 declare @¸ int
error 185 declare @¹ int
error 187 declare @» int
error 188 declare @¼ int
error 189 declare @½ int
error 190 declare @¾ int
error 191 declare @¿ int
error 215 declare @× int
error 247 declare @÷ int

 

So those are all invalid, as well as the single and double quote and everything between characters 0 and 32 since those are not really printable but how would you even use those?

 

So there you have it a quick and dirty script to test what is valid....

 

Exercise for you...expand this to use unicode......

WTF? It is cheaper to buy a CD + MP3 than just MP3 on Amazon

I was looking at the new Calvin Harris album on Amazon. The audio CD is $9.99, this includes the MP3 rip, it will show up in your Amazon library once you buy it

The MP3 album is $10.99

So how does this make any sense? Why wouldn't you get the CD, if you get a couple of other items or if you have Amazon Prime you can get the shipping for free?

 

My 3 favorite sessions at PASS Summit 2014

These were my 3 favorite sessions at the PASS Summit 2014. I attended 13 sessions and most of them, were good, some of them had a demo fail and one of them had several demos fail. So here are the 3 I liked the most, they are in order that I attended them

 

 [AD-400] Query Tuning Mastery: Manhandling Parallelism, 2014 Edition

Speaker:  Adam Machanic 

This was an excellent session, the content was superb and the delivery was top-notch. I was a little sad when the session ended, I wish it would continue.

You can find the demos here: http://sqlblog.com/blogs/adam_machanic/archive/2014/11/06/pass-summit-2014-manhandling-parallelism-demos.aspx

 

[DBA-500-HD] Inside SQL Server I/O 

Speaker(s):  Bob Ward 

What do I have to see that anyone who attended any of Bob Ward's previous sessions  at the PASS Summit, doesn't already know. you HAVE to attend his sessions, don't worry if the content might be to advanced for you. Fast paced, a lot of cool stuff shown and excellent delivery

You can find the demos here: http://www.sqlpass.org/summit/2014/Sessions/Details.aspx?sid=7057

 

[DBA-305] Working with Very Large Tables Like a Pro in SQL Server 2014

Speaker:  Guy Glantser 

First session I ever attended by Guy Glantser. The delivery was perfect, the content was to the point and explained in simple steps.

You can find the demos here: http://www.sqlpass.org/summit/2014/Sessions/Details.aspx?sid=6519

 

I also want to mention the following session, this is a pre-conference session so you won't be able to find the recording

 

Troubleshoot Customer Performance Problems Like a Microsoft Engineer 

Speaker(s):  Tim Chapman  Denzil Ribeiro 

Excellent session with a lot of good stuff, cool demos and tools were shown

 

Of course you might have attended a better session, but since it is impossible to attend all sessions I onlyknow about those that I attended, but you can leave me a comment telling me which ones you liked the best

 

Pass Summit 2014 pre-conference day 2

SQLPASS Summit 2014 stairs

I went to the SQL Server PASS Summit 2014 in Seattle, this is just my recap of my first day. My first day was day 2 of the pre-conference. My day started early, really early, the jet lag is killing me and I am up by 2 AM tossing and turning. By 5 I was already done with my workout, read 30 pages of the book Neuromancer and had showered.

 

Breakfast at sqlpass

 

I met a co-worker and we walked to the Seattle conference center so that he could register and we could get some breakfast before the pre-conference sessions kicked off.. You can see a picture I took of all the people eating breakfast above

 

I attended the Troubleshoot Customer Performance Problems Like a Microsoft Engineer, this session was presented by Tim Chapman and Denzil Ribeiro, both of them work for Microsoft CSS 

Tim Chapman and Denzil Ribeiro

The session started at 8:30 AM and ended at 4:30, I took lots of notes and I have pasted them below as is, there are typos and some stuff might not make sense to you, but these are for me, and I will use these note to investigate and research the stuff I learned at this session. The reason I took all these notes is because the pre-conference sessions are not recorded and won't be on the PASS 2014 DVDs.

Scroll down to after the notes for more stuff about this day if you don't care about these notes

Diag manager 

Choose 64 bit (AMD button)
Perfmon information..overhead is very low
Trace is collected by default... uncheck that... If you don't fin the info you want, you can go back and then run a trace as well


My_Colleectors..click on details an then enhance by picking what you need......

pssdiag.XML..change version to 12 in 3 different places  change 10.5 (2008 R2) to 11 (SQL Server 2012)  or 12 (SQL Server 2014)

Overhead is 1 to 3 percent if you are not running a trace. DOn't put the data collection files on the same drives as you SQL Server data files

SQL Nexus
-------
Modify rowset file if you want to add your own or if you want to supply columns names...SQL Nexus will figure out names by default... name  =  table name..identifies is th print statement in the collection files


WHERE %%LOCKRES%%  = '(HashNumberHere)'

Windows OS = preemptive, SQL Server OS is non preemtive


----  After break

High signal wait time =  CPU pressure
Buffer cache hit ratio is since restarted...if you have a problem in the last week but your server has been up for 6 months you might still be at 95% or higher thus masking the problem

sys.dm_io_virtual_file_stats.....stalls is what SQL Server sees, not what WIndows sees

Cost threshold of parallelism.....  default = 5 ... For OLTP you might want to set it to maybe 30... better to use MAXDOP instead of Cost threshold of parallelism.. Version of SQL Server and NUMA plays a role..hardware changes significantly since they tested this last.


COmmon High stats
WRITELOG
When database changed are flushed to the transaction log file

CXPACKET
parralel queries are happening, not a good or bad thing, sometimes less desirable for OLTP

SOS_SCHEDULER_YIELD
When a thread voluntarily releases its hold on the scheduler to allow another thread to do its work. Not necessarily a problem unless it consumes a very high % of wait time on the system

PAGEIOLATCH_*
Latching a buffer structure to move a page to disk from memory, long waits may indicate a disk or memory issue

PAGELATCH_*
A task is waiting for a page latch not associated with an IO request, can be caused by inserts into the same page or contention on allocation pages

ASYNC_NETWORK_IO
Typically occurs because the client requestion data from SQL Server is not processing the request fast enough

OLEDB
Occurs when SQL Server calls the OLE DB provider. Often associated with 3rd paty tools that heavily call DMVs. Also can be caused by RPC, linked server calls, OpenQuery, OpenRowset or profiler

LCK_*
Waiting to acquire a lock
These are accumulated after the lock has been released


RESOURCE_SEMAPHOR
Waiting for memory grant due to a high number of concurrent queries or excessive memory grant requests. Not uncomment for data warehouse workloads

LATCH_*
When a latch is acquired on some non-buffer construct. Mostly internal uses - usually not a lot you can do about it

CMEMTHREAD
When a task is waiting for a thread-safe memory object, increases when multiple tasks try to allocate from the same memory object

tempdb
Can become a bottleneck if not properly sized/allocated. Faster drives are better
Tempdb is used a lot, for example
Temporary tables and table variables
Internal work tables (spools)
Spills (hash/sort/exchange)
Version Store

Make sure all files are equally sized upon creation

For # of files we recommend
<8 Cores = 8 tempdb files
>= cores =  use 8 unless you still have latch contention, then add 4 at a time afterwards


Perfmon
Save the counters in a cfg file and then next time you open this file you will have all the counters available


Useful Memory Counters

SQL Server: Buffer Manager
Page Life Expectancy
Checkpoint Pages/Sec
Free Pages
Lazy Writes/Secgg

Memory Manager: Memory Grants Pending
Process: Working Set
Memory: Available MBytes

Useful Network Counters
Network Adapter: Current bandwith
Network Adapter: Bytes Total/sec
Network Adapter: Output Queue Length

Useful CPU Counters
Processor % Privileged Time
Processor % Processor Time
Process:*
SQL Statistics: Batch Requests/Sec
Database: Transaction/Sec
SQL Statistics: Compiles/Sec


Useful Process Counters
IO Data Bytes/sec
% Processor Time
Working Set

Useful IOCounters
Logical Disk  (what SQL Server sees)
Physical Disk    (What the OS sees)
Avg Disk Sec/Read
Avg Disk Write
% Idle Time
Disk Transfer/sec

 

Power Settings
Switch from balanced to high performance

 

Performance Dashboard Reports
Set of SSRS performance eports that integrate into SSMS, needs to be installed, download from CodePlex


------------------------------------------------------------------------------------------------------------
Denzil
------------------------------------------------------------------------------------------------------------


Why Xevents?
SQL trace not as performant, reduce need for a debugger, learn internals and troubleshoot, common instrumentation

Xevents: events, Predicates, Actions, Targets

Targets

Event file:    async, disk    Writes events from buffer to disk
ETW file:    sync, disk    Used to correlate with Windows or app data
Event counter:    sync, memory    Counts all specified events
Ring Buffer:    async, memory    event data held in FIFO
Histogram:    async, memory    Sums event occurances based on column or action
Pair matching:    async, memory  Determince when a specified paired event does not occur in a matched set

Demo of how to setup extended events with the wizard as well as T-SQL
Debug channel exposes some advanced things to help you better troubleshoot
Demo of using extended events to track on a per sessionid basis, this is not possible with DMVs


System Health Session

1)Errors
Any error with severity > 20
Memory related errors
CLR allocation and virtual allocation failures

2)Waits
lathes > 15 seconds
Locks > 30 seconds
preemtive waits > 15 seconds

3)Monitors
Deadlocks
sp_Server_diagnostics

4)Ring Buffer Data
COnnectivity Errors
Security Errors
Memory broker
Memory Node OOM
Scheduler Monitor

sp_server_diagnostic_component

If System Health Session is somehow disabled, it will not affect a cluster failover

Spinlocks
If the first acquire is unsuccessful a collision happens causing a spin. then we try again, during this phase we are spinning, we backoff and try again. You cn use spinlock_backoff and spinlock_backoff_warning, spinlock_backoff_warning happens when we have been spinning for a while and the thread got kicked off the CPU

traceflag 3656 + symbols makes callstacks readable instead of just Hex. Symbol files resolve memory into actual SQL Server calls, you can download the symbol list from Microsoft

Showed how Microsoft redesigned locks by using partition_id so locks were not in just one bucket and spinning decreased a lot, you can enable this by running SQL Server with trace flag 1236


Next up was a slide about problems with plans in the cache.
Cache limit is 160K..kb 2964518, I looked this up and here is what is in that kb article about plans

SQL service (Memory Manager)    TF 8032    Entries in the plan cache are evicted because of growth in other caches or memory clerks. You might also encounter plan cache eviction when the cache has reached its maximum number of entries. In addition to this trace flag 8032, consider the optimize for ad hoc workloads server option and also the FORCED PARAMETERIZATION database option.

CMEMTHREAD
Creating a spatial index on a 3 billion row took 4 days, demo showed a smaller table and how it affected performance. Spinlocks wait was high, a lot of CMEMTHREAD wait types, spiking from1 CPU to another. After doing a DBCC STACKDUMP and then loading it in WinDbg, we could see that it was partitioned by node instead of CPU. When starting with trace flag T8048 performance was much better, all the waits vanished. The index creation went from 4 days to 5 hours after using trace flag T8048

The last part of the session was about Hekaton or better known by it's marketing term In-Memory OLTP. At least Denzil didn't say that to use Hekaton there are no application changes needed  :-)

In-Memory OLTP is a good fit for

Performance-critical OLTP (think order processing or trading)
High data-input rate (nicknamed “Shock Absorber”)
In-Memory OLTP as components of ETL
Session state management
Read scale

In-Memory OLTP is not a good fit for
No permission for code changes
App depends on locking behavior 
Full data warehousing 
Long-running reporting workload (use Columnstore instead)
Use a lot of XML manipulation/Full-Text searches
Heavily parallelized query
Constrained on memory

******** END OF NOTES  ****************************


All in all an excellent session,  but man am I wiped out  :-)

Seattle builings. with traffic

 

I went back to the hotel to drop off my laptop, it was actually dry for a change, not a raindrop in sight and about 55 degrees, a very pleasant evening. I had an hour to kill so I took some pics of Seattle, in the picture below you can see Pike Place Market, you can see all of my Seattle 2014 pictures here on flickr

Pike PLace Market night

When I got back to the Seattle convention center, the welcome reception started

SQLPASS Summit 2014 reception

There were a ton of people at this reception, I saw a bunch of old friends and acquaintances.

I didn't stay that long because I had dinner plans with some MVPs (Adam Machanic, Davide Mauri, Marco Russo, Peter Myers and Alberto Ferrari). We went to a restaurant named Rione XIII, this restaurant is the only one that had burrata on the menu, the food was excellent, you can find the menu here: Rione XIII menu

After being up for over 21 hours I finally went to bed. Guess what, four hours later, I am awake again....writing this post...by the time I am used to this jet lag, it will be time to fly back to Princeton again

 

When going to the PASS Summit 2014, don't forget about the focus group and the SQL Server clinic

Do you know that they have focus groups and SQL Server clinics at the PASS Summit 2014?

Here is what is available

Focus Groups

Room 201
Don’t miss this great opportunity to participate in one of the many focus groups that will be conducted at PASS Summit 2014.

Wednesday, November 5
12:00 – 1:00 PM: Power BI Visualizations and Formatting
You will soon have more control over the formatting of visualizations. Give us your opinion on visuals & formatting.

3:00 – 4:00 PM: Power BI Dashboarding
Give your opinions on the features you need to monitor the pulse of their business.

Thursday, November 6
12:00 – 1:00 PM: Power BI - Data Sources and External Connections
Give your opinion on what data sources we should support.

3:00 – 4:00 PM: Improving the user journey from data to insight in Excel
Discuss the user journey from data to insight in Excel desktop.

Friday, November 7
12:00 – 1:00 PM: Azure DocumentDB v-next
Shape future versions of Azure DocumentDB. We want to hear from developers, Architects and Decision Makers.

3:00 – 4:00 PM: Mobile BI - Your data, whenever & wherever you need it 
Review Mobile BI related use cases and scenarios in order to validate our customer hypotheses.

 

SQL Server Clinic

Room 4C
Work through your technical issues with top Microsoft Customer Service and Support (CSS) engineers and get architectural guidance from the SQL Server Customer Advisory Team (SQL CAT) and SQL Tiger Team. 

Wednesday
7:00 AM – 8:00 AM
10:00 AM – 8:00 PM

Thursday
7:00 AM – 8:00 AM
10:00 AM – 6:00 PM

Friday
9:00 AM – 2:00 PM

And if somehow you think you need more material than is already available during the PASS summit, take a look at the more learning page: http://www.sqlpass.org/summit/2014/Sessions/MoreLearning.aspx You will find PASStv and instructor led workshops

Lego Ghostbusters and Back To The Future sets

Some pictures of the LEGO 21103 The DeLorean Time Machine Building Set and the Lego Cuusoo 21108 Ghostbusters Ecto-1, Limited Edition Set

My kids love these and play with them all the time

 

Lego Ghostbusters
Lego Ghostbusters


Lego Ghostbusters side view
Lego Ghostbusters side view


Lego Ghostbusters back view
Lego Ghostbusters back view


Marty McFly
Marty McFly


Marty and Doc
Lego back to the future Marty and Doc


Back to the future car
Lego back to the future car


Emmett Doc Brown
Emmett Doc Brown