Defaults with parameters in SQL Server stored procedures gotcha

I was talking with someone today and this person told me that the stored procedure doesn't work as expected



Here is what the simplified code looked like

CREATE PROCEDURE prTest
@SomeParam int,
@SomeOtherParam int = 1,
@SomeThirdParam int
AS
SELECT @SomeOtherParam
GO

The person told me that when calling the proc and passing in NULL, the value would be NULL, he expected the value to be 1 since that is the default. Running the code like this will return the value NULL

EXEC prTest 1,null,3

In order to omit this parameter you can't be passing it in. If your parameter is the last one then you can just omit the value and pass in the first 2 parameters. If your parameter is not last,  then use named parameters instead of positional parameters. if you run the query like this, you will get back the value 1

EXEC prTest @SomeParam = 1,@SomeThirdParam = 3

Here is an example where the last parameter has a default

ALTER PROCEDURE prTest
@SomeParam int,
@SomeOtherParam int ,
@SomeThirdParam int =3
AS
SELECT @SomeThirdParam
GO

Running that proc with just the first 2 values will return the default

EXEC prTest 1,2

So to recap..... a default will only be applied when the parameter is not passed in, if you pass in a NULL, which is technically the absence of a value, NULL will be used

It is ten years ago that I wrote my first blogpost

This month ten years ago I wrote my first blogpost, that post is Split a comma delimited string fast!
Looking at that post, it makes me cringe, it is just a bunch of code and some comments, there is no other text anywhere. What was I thinking?

You can see all my posts for September 2005 here http://sqlservercode.blogspot.com/2005_09_01_archive.html

You will laugh at the one about pinning a table into memory by using DBCC PINTABLE

Go and take a look at the first couple of blogpost you ever wrote, are you satisfied with it, would you change anything?

Also leave me a comment with a link to your first blogpost and I will check it out......

Links to a handful of posts I created today

I got rid of my squarespace site and decided to repost the handful of SQL Server related posts here.  Some of this content might be useful so here it is as a repost.

Of course if I was really smart I would have done this before getting rid of the site. So some of this content I had to pull from Google cache and some images I had to regenerate.

Here are links to the posts:

Questions for SQL Server MVPs

My last day as a SQL Server MVP

Awesome SSMS shortcuts that will save you tons of time

PASS Summit 2014 pre-conference day 2

My 3 favorite sessions at PASS Summit 2014

What is a valid variable name in T-SQL

Questions for SQL Server MVPs

Since I decided I did not want to be awarded again as a SQL Server MVP,  I wanted to ask some fellow MVPs a couple of questions about the MVP program. I asked the following questions

1. How long have you been a SQL Server MVP?
2. What is the best part of the MVP program?
3. What are the two main reasons that you go to the MVP summit?
4. If you were not a MVP anymore, what would you miss the most?

The following people answered: Adam Machanic, Rob Farley, Paul Randal, Alejandro Mesa, Hugo Kornelis, Hilary Cotter, Itzik Ben-Gan, Brent Ozar, Mladen Prajdic, Arnie Rowland, Jamie Thomson, Thomas LaRock, Kevin Kline, Rod Colledge, George Mastros, Jes Borland, Louis Davidson, Denny Cherry

My own answers can be found here: My last day as a SQL Server MVP

Originally I had each MVP's answer separately but then I noticed that the  4 questions were repeated for every MVP, I decided to just list the question once and have all the answer listed under the question. Now all you have to guess is who gave what answer  :-)

A big thank you to the MVPs that took time answering all these questions

Here are the answers...enjoy. 

1: How long have you been a SQL Server MVP?

Since October, 2004
Since October 2006.
7 years
I have been a SQL Server MVP since Jan, 2007. I believe it is 8 years by now.
Since January 2006. So I am now in my ninth year. (Wow!!)
I have been an MVP for about 13 years now.
15 years
5 years - I got the MVP award back in 2009, and I've been tickled pink every time I get renewed.
since 2010
8 years
8 years I think
I earned my first MVP in April of 2009, so this year is my sixth consecutive award.
I was first awarded the MVP status for the 2003 cycle.
I've been an MVP since January 2010 - This is my 5th consecutive year
It will be 4 years in October.
I was awarded for the third time in April 2014
10 years so far...
6 years

2: What is the best part of the MVP program?

Interacting with other MVPs! I've learned a huge amount over the years from numerous MVPs -- famous ones like Itzik Ben-Gan, retired ones like Steve Kass and Anith Sen, and I can't forget some guy called Denis Gobo...

This is more answered by the fourth question. The best part of the program is not really something provided by Microsoft but the community that has formed with the SQL MVPs. But on the assumption that you want an answer that relates to something Microsoft provides, I'll go with the mailing list. Everything discussed there is under NDA, and it's a great place to learn and see what's going on. It's through the mailing list that Microsoft talks to MVPs when they want our opinion on some new feature, reflecting the fact that there are lots of people with Microsoft who do see us as a valuable group of people (not that non-MVPs are not - but the NDA needs to be there).

The free MSDN subscription

For me the best part is to have the opportunity to meet other MVPs, like you, that I have followed for long time either reading their blogs, forum answers, books, etc.

I also like the free MSDN subscription, the interaction with the SQL Server development group and the private list.

I hope you're not forcing me to pick just a single thing, because I can't - there are three things that I all consider to be one of the three best parts. These are, in no specific order: the MVP mailing list (I think the existence of this list is public, so you can include it in your post); the MVP summit; and all the contacts and opportunities that have opened to me through being in the MVP program and networking with other MVPs. (see below for a longer explanation of this)

The best part is the distribution list and being able to pick people brains with questions that I am too lazy to research or can't get good answers about. A close second is the recognition it gives me in getting work. Being an MVP means that I can screw up far more than regular folks and get a pass:)

Industry recognition and MSDN

More chances to talk to Microsoft employees, who are some of the smartest people I know. Forget talking to other MVPs - if you want to do that, go talk to them. You don't need to be an MVP to talk to another MVP - just walk right up to them at any SQLSaturday. They're friendly, open people who love helping others. After all, that's how they became MVPs!

the people you get to meet

Access to many on the product teams, and most especially, the global SQL MVP community.

Free MSDN sub

Best part? - The MVP distribution list. There I get to see many interesting questions and answers as well as interact with the product team at Microsoft.

While there are some monetarily significant benefits, such as the MSDN Universal License, and there are some prestige aspects of the award, the best part of the program for me is the ability to interface with the worldwide MVP community. I especially enjoy the private MVP discussion forum, where MVPs share many of their most challenging and interesting technology questions. MVPs are typically very knowledgeable and, in a sense, a cut above the average IT person. Consequently, I'm always learning from my colleagues in the program and advance my own skills through their efforts. 

The best part is the engagement with the product teams at Microsoft - both at the conference itself, and through the email forums

The MVP emails are, by far, the best part of the program.  It allows me unprecedented access to some of the fine folks that have actually written parts of the database engine.

I appreciate the direct access to Microsoft. Being able to get answers quickly and understand the future direction of the product is a huge benefit. 

The best part is the kind of instant family that you gain when you become an MVP. The communication between the MVPs and Microsoft employees we get is excellent, and not something you can easily get.  To be fair, it isn't that much more than you might get at any conference you attend, but it is far more regular and constant.

It is also kind of the worst part too. The fact is, while it is sort of like family, it is different in one very important way. You are family for life. The members of the MVP family have changed over the years quite a bit, and the Microsoft employees we interact with change constantly. The reasons vary, but I am always sad to see people like yourself fade off into the sunset, though at the same time I think it is awesome that people like you have the intelligence to know when things in your real family are more important.

Being able to work directly with the product group on new features and fixing problems with the product

3: What are the two main reasons that you go to the MVP summit?

I haven't attended an MVP Summit in several years, so I guess I have no reasons to go. When I did go I enjoyed interacting with other MVPs in person (usually over way too many beers). I get the same experience at the PASS Summit.

Actually, I haven't been to the last six MVP Summits. I went the first two years I was an MVP, in early 2007 and early 2008, but haven't been back since. It's ever such a long way to go from Australia, and while I love the MVP community, I actually prefer to be at events that include non-MVPs. I've been keen to go, because the interaction with the product group is really good. And of course, hanging out with the other MVPs is terrific. And the sessions on the internal workings of the product.

Socializing. We're usually not around for the Summit (we live in Redmond) and there's usually nothing new presented at the Summit that we haven't already been told some other way.

First to get together with other MVPs, second to interact with other MVPs. :)

The information exchange with the SQL Server dev team, and the many friends I meet there - many of them just once per year, at that summit.

I haven't gone to the summits. A couple of times I went to some MVP events for SQL Server after PASS and was kind of shocked by most of my peers. They were ego maniacs. I might go to the one this fall. I always toy with the idea of going but normally don't. We will see.

Hearing what the developers have to say and meeting my MVP friends (i.e., having beer together)

I don't, actually. I have a commitment with my family that I'll only travel one week per month, and for the last few years, the private MVP Summit has been the same month as public conferences that I'd rather attend. I'm really happy that this year Microsoft chose to host the MVP Summit in Seattle during the same week as the PASS Summit, and I'll be able to attend both. (Without coming home to find my belongings helpfully arranged on the doorstep.)

to see what'll be next. to see friends and (re)connect with others

Same as #2. Networking, personal contacts, and advanced product information. Toss in a smidgen of actually being asked about future directions –though, at times that seems more like a palliative effort than a ‘real’ engagement…

Learn about new stuff coming out, opportunity to grill the folks that build the produc

2 reasons? - To connect with fellow MVPs and to learn more about where MS is heading (and taking us with them, should we choose to go!)

While the MVP Summit is a great yearly event, it's not on my "must-attend" list. I think that I  manage to attend about 60% of the time. The two biggest reasons I DO like to attend the Summit are: 

1) ...to make introductions with the actual people leading the various efforts for each specific Microsoft SQL Server feature. Because people move around a lot within Microsoft, I like to make sure I know and get to meet the various program managers who are responsible for key areas of SQL Server product development, planning, and strategy. That way, if I ever have a question about the product, I know who to ask and, conversely, they know me. It's not a channel I exercise frequently - perhaps twice per year. But it's has great utility and value for a person who builds tools based on what Microsoft themselves are building.

2) ...to be a part of the feedback loop. Microsoft provides MVPs a couple avenues for feedback at the Summit. For example, there are often opportunities to see Microsoft's roadmap and future plans and to comment on them. That's cool, especially those times where you actually see Microsoft make a course correction in their product based on your feedback. Another example is when Microsoft provides the MVPs an opportunity to comment on an existing set of features and to influence when and how those features grow. 

The Microsoft SQL Server team does appear at other conferences over the course of the year, such as Microsoft TechEd and at the big annual PASS Summit. But I find the MVP Summit is the best place to get some one-on-one time and, in some cases, even make friends with the various team leads for SQL Server. That, for me, is priceless.

To be honest, I've only been once (2010). The travel involved from Australia makes it difficult, particularly when I also go the PASS summit, which is normally around the same time. I will almost definitely be going this year though, so I'm really looking forward to that

I have never been to the MVP Summit, and I regret it each year.

It's a rare conference I go to where I am just an attendee, and I get to sit in on all the sessions and learn tons of cool stuff. I also really like running the hills in Bellevue, WA in the mornings with fellow MVPs.

First, to see everyone in person that I see in email feeds, online, etc. It is always a great week of fun, talking about everything from technical stuff, to entertainment, to a wee bit of politics here and there. The MVPs are a very diverse bunch of folks that have a bit of every personality style so it is a great time.

Second (and the one that makes it a worthwhile expense), to gather material for future writing and future work. Getting information about stuff that is coming allows me to start to plan material I want to work on for the upcoming years. 

To see what the product team has coming out in the next release, and to swap war stories with the other SQL Server MVPs
  

  

4: If you were not a MVP anymore, what would you miss the most?

Interacting with other MVPs, naturally. The private mailing list (which used to be a newsgroup) has always been, by far, the number one benefit for me.

Good question... ;) 

Being an MVP is something I've got used to, and something which has become part of my identity within the SQL Server world. But then again, there are plenty of other things which also contribute to my profile - the style of conference session I give, the fact that I did that song in 2011, and so on. 

If I were not an MVP, it wouldn't change what I do, or who I am. I'd definitely miss the mailing list, and the community that has grown up through that, but I think the community would continue somehow, as it does through things like Twitter already. I'm already a long way away from most of my favourite people in the SQL community, and so I wouldn't stop being part of the community.

At the end of the day, I love the fact that Microsoft has the MVP program. I thoroughly enjoy the fact that I am an MVP, and wish more people could be recognised for their own contributions to the community. It saddens me that there are people who feel they have missed out every time they don't get awarded. But the SQL community is stronger than ever today, and MVPs only make up a small portion of that community.

Being part of the MVP group

The MVP summit for sure.

See Q2.

(*) Being an MVP has opened up lots of opportunities to me. Some directly, others indirectly. I started blogging, authoring, and tech editing, all because MVPs asked me to. I started speaking after being invited to attend a conference for free (MVP perk) and seeing how much fun that can be. And many of the places I have traveled to as a speaker are also the result of speaking with other MVPs. (The best example of this was in a bus at the MVP Summit. Niko Neugebauer, who I barely knew at that time but consider a close friend now, sat next to me, and his exact words were "what would it take to get you to speak in Lisbon".
And it's not just community activities leading to more community activities; it has also resulted in extra income. I don't think I would ever have been asked to be reviewer for a website that offers exam training without my tech editing experience. And without my years of speaking experience, I would not have become a Pluralsight author.

The distribution list. It would hurt my ego for a while but I would get over it.

Industry recognition and MSDN

The humor in Conor Cunningham's answers on the MVP email list.

probably the email distribution list

See #2 and 3 above.

For me, the greatest aspect of being a SQL Server MVP is the Global SQL Server MVP Community. An incredible group of very smart and wise folks that are enthused about the technology we work with on a daily basis, and more than willing to freely share their expertise and knowledge

MSDN sub

Miss? - Besides the MVP distribution list, I'd miss the MSDN subscription. I've been getting enterprise software for free for so long I'm not sure I'm ready for the sticker shock should I ever need to pay for something on my own.

Well, I'm a people person. I keep up with a lot of my friends' comings and goings based upon their presence in the MVP discussion forum. In some cases, there are MVPs who are quite active in the forum who do not post regularly on social media sites and so they'd literally fall of my radar without the forums. So I'd have to say that I'd miss the friendships and interesting people most were I no longer an MVP.

I'd definitely miss the email forums, where I can effectively drop in on conversations between some of the great minds in our industry, and the opportunity to ask questions of them

I would miss the status.  I have never been one to be particularly impressed by status, nor do I gloat about it.  However, being an MVP gives you a certain amount of respect.  All of my customers have a sysadmin and a network admin.  A few have DBA's, but most do not.  They look to me for advice because they know I will take care of them.  When I tell a customer that I will take care of them, they can trust that I will, and that I will do it the right way. I am not boastful, but I am confident in what I know (and not shy to say what I don't know).  All of this leads my customer in having greater confidence in me and my application.

I'd miss the advance knowledge. It's been nice to have access to early versions of software and provide feedback. 

Having something to worry about on October 1, which is my renewal date. I look forward every year to sweating it out...

But seriously.  The communication channels being an MVP opens are really valuable. I already mentioned them a bit, but even the information I gather by osmosis at times comes in handy. I don't know how many times I am in a discussion about trying to use or write about some feature in SQL Server, and I realize I know more than I thought I did.

The MVP mailing list as that gives the MVPs direct access to the product group

My last day as a SQL Server MVP

My last day as a SQL Server MVP

Nine months ago I decided that I did not want to be a SQL Server MVP anymore.

I just want to write a couple of paragraphs telling you what it is like to be part of the SQL Server MVP community. The SQL Server community is unlike any other MVP community, I think the group has better access to people working on SQL Server than other MVP groups have with their respective product. I also think the SQL Server MVP community is more involved with being active online and participating in giving back to the community with events like SQLSaturday

THINGS I REMEMBER THE MOST

The MVP summit is an event that every MVP looks forward to, this is where you meet fellow MVPs and hang out. You also meet the developers and managers that work at Microsoft and they will show you upcoming features and take feedback from the MVPs, this feedback can be both good or bad (did someone say dacpac?)

Windows 7 launch event
This was a pretty cool event, it was in a loft in SOHO New York City and I got to see Steve Ballmer showing us how Windows 7 was the latest and greatest version of Windows that Microsoft has released until then.

THINGS I WILL MISS THE MOST

MVP summit
When I went to the MVP summit, I finally got to meet the people in person who I only knew from blogs, newsgroups, podcasts, webcasts and books/magazines. I still remember the first summit I went to, in the lobby I saw Kevin kline, Rod Colledge and a couple of more people. We grabbed a taxi and went to Redmond to attend some presentations by the SQL Server product group. What was shown was some stuff that was coming down the pipe in a few years. This was great stuff, for normal people this would be like spending time with their favorite actor talking about movies that were not being released for another year or two.

Access to the SQL Server product group
One of the best things is when the MVPs give feedback about some features that they don't particularly like. I still remember dacpac and what an emotional response that got from the community.....

Internal MVP Email Distribution List
The MVP internal email distribution group is probably the most interesting part of the MVP program. If you want to know why something works a certain way, you can ask and most likely someone who works in the SQL Server group will answer you, a lot of these are NDA so you can't share it. This list is great to get some internal knowledge about the product and also to learn new things from fellow MVPs and product group members.

PERKS

There are tons of perks that you get for being a MVP, besides the summit, the internal email list, access to the SQL Server product group, I am going to list just a couple of them.

MSDN Ultimate subscription, I actually only took advantage of this one once, I had a subscription through my job for most of the years

Vouchers to use in the Microsoft store, credit to use in the online store (they don't give the credit anymore)

Hanging out with fellow MVPs

Invitation to launch events

Getting private builds and CTPs earlier than the rest of the people
You didn't think I wrote those 10 blog post all within 1 hour did you? No, as an MVP you get access to the CTPs a couple of days before it becomes general available. This gives you time to prepare the blog posts by making sure all your code works correctly against the latest build

Third party software
There are many companies that will give MVPs a not for resale licensed version of their products. Here are just a couple of companies that do this: Red Gate, Confio, Quest, TechSmith, Pluralsight, Jetbrains etc etc.

THINGS I WON'T MISS....

Hitting F5 and refresh many times every July 1st to see if I got re-awarded :-)
Those days are over, no need to be anxious anymore every time July 1st rolls around, now I can watch other people sweating instead.....
The jet lag when going to the MVP summit in Seattle. I won't be missing waking up at 2 AM and not knowing what to do with myself because my internal clock is on east coast time. I always thought this was the hardest part of the MVP summit

I decided to ping a bunch of my fellow MVPs and ask them 4 questions about the MVP program, the questions I asked were

1. How long have you been a SQL Server MVP?
2. What is the best part of the MVP program?
3. What are the two main reasons that you go to the MVP summit?
4. If you were not a MVP anymore, what would you miss the most?

You can find the answers here: Questions for SQL Server MVPs 

There you have it...my last day as a SQL Server MVP......

Awesome SSMS shortcuts that will save you tons of time

How many times have you written a join with a bunch of tables and you wanted to quickly see what was in a table. You would either find the table in object explorer, right click and click on SELECT top 1000 rows

This would produce something like the following

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [lastrun]
      ,[cpu_busy]
      ,[io_busy]
      ,[idle]
      ,[pack_received]
      ,[pack_sent]
      ,[connections]
      ,[pack_errors]
      ,[total_read]
      ,[total_write]
      ,[total_errors]
  FROM [master].[dbo].[spt_monitor]

What if I told you that there is a way, way better and faster way.....

In SSMS click on Tools then Options, from the right site pick Environment-->Keyboard-->Query Shortcuts you should see something like the following

In the CTRL + 5 shortcut space enter the following select top 100 * from 

Make sure you have a trailing space after from

Hit OK, now restart SSMS because it won't take effect right away

Once you have restarted SSMS and have connected to a database, in a new query window type any table name that you want, for example if you are in the master database type spt_values, double click on the table name so that it is highlighted, now press CTRL + 5 and voila you should see 100 rows from this table....

Now you can just click on any table or view and see the 100 rows, this way you can easy see what the table looks like

You can also do this with sp_helptext, here is how you do it, add sp_helptext where CTRL + 6 is, make sure you have a trailing space after the proc name

Restart SSMS again, type a proc name, double click on the name, hit CTRL + 6 and you should see the definition in the output

There you have it..some time saver shortcuts. You can also do this with other queries and procs...what are some of your favorite shortcuts?

Pass Summit 2014 pre-conference day 2

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.



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

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  :-)


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

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

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

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

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

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

SELECT * from #test

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

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

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......

Did you know you can pass false and true to SQL Server bit parameters?

I noticed a stored procedure with a bit parameter and the default was false, I have never seen this before in SQL Server. You can do this in a language like c# but not in SQL Server right?

Let's take a look

Create this proc

create procedure prTest
@BitTrue bit = true,
@BitFalse bit = False,
@BitNotFalse bit = -2
as
Select @BitTrue as BitTrue, @BitFalse as BitFalse,
@BitNotFalse as BitNotFalse
GO

Now execute the sored procedure

EXEC prTest

BitTrue BitFalse BitNotFalse
1 0 1


As you can see true gets converted to 1, false gets converted to 0 and any number that is not 0 will be converted to 1 as well

What happens when you pass in false for the parameter that had a default of true?

EXEC prTest @BitTrue = false

BitTrue BitFalse BitNotFalse
0 0 1



How about variables..can you use true and false with bit datatype variables?
Let's try it out

DECLARE @BitTrue bit 
SET @BitTrue = true

Msg 207, Level 16, State 1, Line 2
Invalid column name 'true'.

DECLARE @BitTrue bit 
SET @BitTrue = false

Msg 207, Level 16, State 1, Line 3
Invalid column name 'false'.


As you can see you can  use true and false with parameters in a proc but not with variables.

My non SQL blog is up

In case you are interested in non SQL Server related stuff, I have started a blog about non SQL stuff. You can find it here http://denis-gobo.squarespace.com

See you there

SQL Advent 2012: Recap

Another year, another SQL Advent calendar has come to an end. This year there was not much SQL code after day 10, I still hope you found it interesting and useful. Here is a list off all the posts with a little blurb about them.

SQL Advent 2012 Day 24: Getting help
In this post I showed you where to get help and how to use twitter to get some help

SQL Advent 2012 Day 23: Get the tools you need to be more productive
In this post I tried to show you that tools will pay for themselves

SQL Advent 2012 Day 22: Testing your backup and failover strategy
This post was all about the importance of testing your backup and failover strategy

SQL Advent 2012 Day 21: With VLDBs it matters what you do and how you do it
With little databases you can get away with doing stupid stuff, with large databases not so much

SQL Advent 2012 Day 20: Use the new features
This post detailed why it was important to use the features and functionality

SQL Advent 2012 Day 19: How to improve your tech skills
The post showed you a couple of ways to improve your tech skills

SQL Advent 2012 Day 18: Stay relevant and marketable
If you don't keep up, you will lose

SQL Advent 2012 Day 17: No matter how long you are on the wrong path, go back
Sometimes it is better to start from scratch than continue down the wrong path

SQL Advent 2012 Day 16: Lack of database design and normalization
We don't need no stinking keys

SQL Advent 2012 Day 15: Benefits of Indexes
Why do we need indexes and what purpose do they serve?

SQL Advent 2012 Day 14: When to say no
Sometines you have to say no....even to your boss

SQL Advent 2012 Day 13: Features enabled that are not used
Disable and shutdown services and feature that are not used

SQL Advent 2012 Day 12: Proactive notifications
Get notified before the client or your boss about errors

SQL Advent 2012 Day 11: Cursors and loops
Cursors are almost always evil

SQL Advent 2012 Day 10: SQL Server Maintenance
Maintenance is unfortunately required, machines and software are not completely autotuned yet

SQL Advent 2012 Day 9: Reinventing the wheel
Why write something if it already exist

SQL Advent 2012 Day 8: Foreign Keys
A small post showing that you don't always need a primary key for a foreign key

SQL Advent 2012 Day 7: Lack of constraints
Constraints in a database guarantee data integrity

SQL Advent 2012 Day 6: Standardized Naming And Other Conventions
If you have a standard, everything will be easier

SQL Advent 2012 Day 5: Do not trust the SSMS designers
SOmetimes a simple click can cause a lot of data to be moved behind the scenes

SQL Advent 2012 Day 4: Triggers, what to do, what not to do
Some best practices around triggers

SQL Advent 2012 Day 3: Sargable Queries
How to write queries that will take advantage of indexes

SQL Advent 2012 Day 2: Data types storage differences
Make sure you know the difference between the different data types and how they are stored

SQL Advent 2012 Day 1: Sizing database files
You have to presize your databases if you want optimal performance