SQL PASS 2013 Live Blogs

For everyone living vicariously through the live blogs and PASSTV, here is a quick round up of the bloggers:

SQL PASS Live Stream

Aaron Bertrand on SQLBlog.com
Ryan Adams Blog 
The Midnight DBA 
Jen Stirrup
Grant Fritchey

90 Days to MCSA - ready, set GO!

I just came across a great resource for those seeking certification in SQL Server (like me) or Windows Server 2012, it's called 90 Days to MCSA and I found it on Gareth Swanepoel's blog (@GarethSwan) and it's just the motivation I needed.

I am going over the plans now, this puts a great structure around a task that I think we have all said "Yeah I should do that...someday..." about, but haven't done anything active to get things rolling. Seeing all these other people getting started on this is motivating to me, it's like we're doing it as a group. If you do this as well, make sure to get into the G+ group too.

So what is the plan over the next 90 days? Here is what Gareth laid out, all credit goes to him:

90 days to MCSA – SQL Server 2012

Week 1 (this week):
     Build your Plan. Following this blog post series and visiting the Microsoft 90 days to MCSA web site will help you to do that.
     Build your Lab. Here is a great article from the fine folks at Microsoft learning on how to set up a lab to prep for exam 70-461: Lab Setup Guide to Prep for Exam 70-461 Querying SQL Server 2012. 
     Sign up for the Microsoft Learning 90 Days to MCSA e-newsletter.This can be found on this page.

Week 2 (next week):
     Build your Knowledge. Do some free training at Microsoft Virtual Academy Querying SQL Server
     Sign up for the first exam. Microsoft is offering free second shot retake vouchers for a limited time. Go and grab 15% or more savings on certification packs here. 
     Share with us in the Google+ community. Let us know how you’re doing, if you have any questions or if you just generally need to talk. We’re here for you.

Week 3:
     Build your Knowledge. Watch the Microsoft Learning Exam Prep video for the first exam that will be attempted – 70-461
     Join the Microsoft Learning Study Group. There are some great resources available in these Study Groups including discussion forums and exam prep wikis. Go and join the Database Certification Study Group.

Week 4:
     Build your Knowledge. You should be consuming the content of an exam training kit like the one offered by Microsoft Press which can be found, amongst other places, on Amazon: Training Kit (Exam 70-461): Querying Microsoft® SQL Server 2012. Also available and recommended is the Study notes and review questions book: MCSA SQL Server 2012 Exam 70-461 & 462 ExamFOCUS Study Notes & Review Questions. 
     Take the 70-461 exam. By this point, you should be in a position to exert your effort into actually putting knowledge to paper. Well, digital bits of paper. You will go and attempt to pass the exam. Let us know how it goes !

Week 5 and beyond:
     The process is more-or-less repeated with the 70-462 and 70-463 certification exams. I will be updating this post with specific links that cover those exams. Keep an eye out for that.

SharePoint List Adapter in SSIS using GUID instead of name

So you have an SSIS package that pulls data from a SharePoint List, and it's working fine (It's not? start here), until...someone comes along and changes the name of the list, breaking your package and possibly failing the agent job that calls it...what to do?

You could just paste in the new name, but to fix this for good, you can use the GUID of the list to make sure that it does not happen again because the GUID remains constant even if the name changes.

In the properties of your SharePoint List source, in the name field, replace the text with the GUID of the list in question as shown below.

For anyone who doesn't have this installed yet, here is the link to the list adapter on codeplex

SQL Server Wait_Type full description table

In preparing for another post to continue identifying blocking within your database, I found I did not have a full description for each wait_type, so I took the data from MSDN and created a table that holds all the full descriptions of each wait type, original data here. This was taken from SQL Server 2012 material, your mileage may vary in other versions.

Here is the table I used, very simple:

CREATE TABLE dbo.Wait_Type_Description
   (
   Wait_type VARCHAR(50) NOT NULL,
   Wait_Type_desc VARCHAR(8000) NOT NULL
   )  

and I am joining it like this, again super simple example:

SELECT *
   FROM sys.dm_exec_requests der
       JOIN dbo.wait_Type_description wtd
       ON der.wait_type = wtd.wait_type

All 345 INSERT statements can be found here, sorry this is obnoxious to look at you may download the script or cut and paste it below.

GitHub Download Link

Are your customers abusing you?

Do you have Customers that you dread talking to? You know the types, they are not happy, ever, and have a seemingly endless stream of new requirements (read as: demands) of you and your team. While this post is geared toward those of us who have or are currently working in a large enterprise where database teams and IT specifically work 'for' other internal teams, it applies to everyone with customers or users.
In a large company, you often don't get to 'charge' for your services, the end users get to create requirements and hand them to you, and you don't get to make any requests of them in response, in essence your services are "free". Whenever you offer something for free, you are going to get a somewhat irrational response, people are really effected by the word or concept of "free". Dan Ariely writes about this concept in his book "Predictably Irrational", he shows us through experimentation that when the price is free, the demand will increase incredibly fast, and at the expense of reasonable decision making. I think this holds true in most transactions, whether its free cell phones or free software, if it's free demand will increase beyond what could be expected from otherwise rational consumers. to quote Dan Ariely (@danariely):

... First of all, let me say that there are many times when getting FREE! items can make perfect sense. If you find a bin of FREE! athletic socks at a department store, for instance, there is no downside to grabbing all the socks you can. The critical issue arises when FREE! becomes a struggle between a free item and another item - a struggle in which the presence of FREE! leads us to make a bad decision. *

I have never seen this concept more ably demonstrated than I have working in a large corporate IT department. There are other examples, for example this post on SQLServerCentral's forums is a great example of exactly the same thing happening but in a smaller company

Amazing what a little regulation does for requirements! After looking into PCI requirements, I also found that CSC (or other authentication methods) can never be stored. So when I brought this to the attention of the department head and suggested we look into our options with the lawyer, he quickly rescinded that particular requirement. Turns out, the CSC is not required by our CC processing software, that requirement was just put there "just in case we ever needed it."  (full thread here)

 This is a great example, since there was no cost involved, an unnecessary and in this case legally precarious, requirement was thrown in 'just in case'. For those suffering under the pile of "nice to haves" and "just in case" requirements and constant revisions, what can be done? I have found that even implementing a nominal cost for IT services can have a big impact on the way your customers will view your services. When it comes to nominal cost, it does not have to be monetary, it can be additional regulatory overheard, legal or financial risk, or an unacceptable increase in time lines that brings the needed 'costs' into the equation. The example from the recent forum thread above did not involve any financial costs, the cost was in the potential legal exposure, and the cost was too great. So how does one go about implementing a cost structure in a corporate environment that realistically does not allow you to actually charge money for your interdepartmental services?
If there is a cost center or ledger you can charge, even a nominal amount, then do it. Having to reconcile charges made between departments really isn't money being 'spent' by the company, however having to justify the expense is usually costly enough in terms of hours and headaches to really give someone pause as to whether the expenditure is necessary. I have seen this implemented and work with surprising effectiveness, if the only rationale for requesting something is "why not", then all you need to do is supply that reason. If the concept of charging for a service won't work in your workplace, then charge your customers in a different way, with time. If a requirement is changed or added last minute, address it with the requester by showing him/her what else you are working on and what will suffer delays because of this change, your charging them the opportunity cost of this new item. By having to look over everything they are asking of you and weigh the new requirement against the others, value judgments will be made and unimportant items eliminated quickly.

If you are dealing with a customer or client that is not internal, but are actual customers in the true sense of the word, then it can be trickier to handle. as evidenced here on TechCrunch in a post by @JasonKincaid
there can be a large cost to FREE! promotions, including the overhead to support additional users both with bandwidth/hardware/storage type needs, as well as the user support requirements of exponentially growing a user base that needs support but has paid you nothing in return .In this case there may be a simple answer, stop giving your app away free and you wont have these problems, but if these freeloaders are also paying customers of another of your products or services, alienating them could be costly. You will need or determine whether the traffic and 'exposure' your brand or product is getting has any payoff down the road.

So when you are considering your next set of requirements or thinking of offering a service or product for free, ask your potential customers or users, what is this worth to you? If they can't say, be prepared to propose a value yourself and be confident in asking for compensation for your hard work, and both you and your consumer will be satisfied that value is being exchanged for value.

*Ariely, Dan Predictably Irrational. New York: Harper Perennial 2010
Predictably Irrational, Revised and Expanded Edition: The Hidden Forces That Shape Our Decisions

If you found this interesting or useful, please click the +1 to share (it's FREE!) -- thank you

Deadlock and blocking in SQL Server - Part 2

With SQL Server 2005 a set of dynamic management views were introduced to allow you better visibility into what exactly was happening within the instances of SQL Server you manage or use. The below script takes the outdated sys.sysprocesses and fn_get_sql functions (which still work but are deprecated) and replaces them with new DMVs sys.dm_exec_requests and sys.dm_exec_sql_text.
Additionally it more narrowly defines the SQL statement being executed by both the blockee and blocker by trimming down the full text of the script or procedure and only returning the actual code currently executing. This is done by using the statement_start and statement_end offset values in the sys.dm_exec_requests view.
There is a lot more information available on what currently executing statements are doing within your instance, but this script will get you some of the most important information about who is blocking who quickly. I will expand on this again in the near future with more details about what resources are being contested, and other metrics.

/*
   Created: October 2012
   Author: SQLWorks blog
   URL: http://www.SQLWorks.blogspot.com
   Changelog:
   10/17/2012 - replaced sysprocesses with new DMV sys.dm_exec_requests
              - replaced fn_get_sql with new DMV dm_exec_sql_text
              - Used SUBSTRING and statement_offet values to determine exact statement that               is currently executing
              - instead of returning entire command or proceure
*/

DECLARE @sql_handle TABLE(blockee_spid INT, blockee_cmd VARCHAR(MAX), blocker_spid INT, blocker_cmd VARCHAR(MAX))
INSERT INTO @sql_handle
   SELECT sp1.session_id AS blockee_spid,

           SUBSTRING(blockee_cmd.TEXT,
               CASE WHEN sp1.statement_start_offset >= 0 THEN sp1.statement_start_offset
                   ELSE 0 END,
               CASE WHEN (sp1.statement_end_offset-sp1.statement_start_offset) >= 0 THEN                      (SELECT LEN(TEXT) FROM sys.dm_exec_sql_text(sp1.sql_handle))
                   ELSE (sp1.statement_end_offset-sp1.statement_start_offset) END),

           sp1.blocking_session_id AS blocker_spid,

           SUBSTRING(blocker_cmd.TEXT,
               CASE WHEN sp2.statement_start_offset >= 0 THEN sp2.statement_start_offset
                   ELSE 0 END,
               CASE WHEN (sp2.statement_end_offset-sp2.statement_start_offset)<= 0 THEN          (SELECT LEN(TEXT) FROM sys.dm_exec_sql_text(sp2.sql_handle))
                   ELSE (sp2.statement_end_offset-sp2.statement_start_offset) END) AS blocker_cmd

       FROM sys.dm_exec_requests sp1
           JOIN sys.dm_exec_requests sp2
           ON sp1.blocking_session_id = sp2.session_id
       CROSS APPLY sys.dm_exec_sql_text((sp1.sql_handle)) AS blockee_cmd
       CROSS APPLY sys.dm_exec_sql_text((sp2.sql_handle)) AS blocker_cmd
       WHERE sp1.blocking_session_id <> 0

SELECT *FROM @sql_handle

Using the same test data from Part 1 of this post, I added a second simple SELECT query that would be blocked by the update loop and then ran the above statement. The results show the way that the statements are blocked by each other.

What you can see here is that spid 52 (the update loop) is blocking spid 56 (the first SELECT) and then spid 56 is blocking spid 53 (the second SELECT). These results are a bit hard to look at, so in the next post I will modify how they are displayed and get a more usable result set returned.

Go back to Part 1 - blocking and deadlocks

If you found this useful please click the +1 to share - its FREE!

Deadlocks and blocking in SQL Server

As a DBA you are probably faced with the issue of users blocking each other's queries in your databases and potentially deadlocks between queries as well. Here is how I identify and resolve those issues. This method is simple and quick and probably all you need for most situations, there are much more elaborate ways of tracing and troubleshooting these, but for user queries and in-the-moment identification of issues, this way almost always works.

First, the difference between blocking or contention and a deadlock in simple terms, blocking is when one spid or query blocks another from using a resource or placing the lock it needs on said resource, and a deadlock is when two users or queries each holds a resource the other needs next and neither will let go, so the engine chooses a 'victim' and ends the standoff by killing one query. Again this is really simplified but its all you need to know to get started. Now how do we identify when each scenario is happening?

Lets set up some test data first:

/* Created: October 2012
   Author: SQLWorks Consulting blog
   URL: http://www.SQLWorks.blogspot.com
*/
CREATE TABLE [dbo].[EmployeePerf](
   [EmployeeID] [int] NOT NULL,
   [EmployeeName] [varchar](25) NOT NULL,
   [Sales] [int] NULL,
   [TimePeriod] [int] NULL
)
-- test data
INSERT INTO dbo.EmployeePerf VALUES(1,'Tony',100,'1')
INSERT INTO dbo.EmployeePerf VALUES(1,'Tony',300,'2')
INSERT INTO dbo.EmployeePerf VALUES(1,'Tony',200,'3')
INSERT INTO dbo.EmployeePerf VALUES(1,'Tony',150,'4')  

This is just a table I had set up already on my server to test something else, so I am reusing it, the contents of the table are not really relevant to the discussion on database contention.

So now we need two queries, one to block the other for our testing:
-- Looping update to create a lock for demonstration
DECLARE @sales INT = 100
BEGIN TRAN
WHILE @sales < 500000
BEGIN
   UPDATE [dbo].[EmployeePerf]
   SET sales = @sales
       WHERE Employeename = 'tony' AND timeperiod = 1

   SET @sales = @sales +1
CONTINUE
END

COMMIT TRAN

and in a second window, you will run this concurrently and it will be blocked by the first:

-- select statement that will be blocked by above update statement for demonstration
-- Run this in a different query window!
SELECT *
   FROM dbo.EmployeePerf

So now that we have blocking occurring, what do we do about it? Well first I always like to know who is blocking who, and the commands being attempted by each party. To find that out there are several methods, here is the one I use in SQL Server 2012:

DECLARE @sql_handle TABLE(blockee_spid INT, blockee_cmd VARCHAR(MAX), blocker_spid INT, blocker_cmd VARCHAR(MAX))INSERT INTO @sql_handle
   SELECT sp1.spid AS blockee_spid, blockee_cmd.TEXT AS blockee_cmd, sp1.blocked AS blocker_spid, blocker_cmd.TEXT AS blocker_cmd
       FROM sys.sysprocesses sp1
           JOIN sys.sysprocesses sp2
           ON sp1.blocked = sp2.spid
       CROSS APPLY sys.dm_exec_sql_text((sp1.sql_handle)) AS blockee_cmd --updated
       CROSS APPLY sys.dm_exec_sql_text((sp2.sql_handle)) AS blocker_cmd --updated
       WHERE sp1.blocked <> 0

SELECT *FROM @sql_handle  

This uses the sys.sysprocesses system view to see what is blocking and being blocked and then calls the fn_get_sql function for each to determine what code is being run. If you run the two queries above, then run this script in a third window you will get the below results:

As you can see, it provides the spid of the blocker and blockee, as well as the commands being run by each. There is so much more to delve into on this subject but this will get you moving, and give you a quick way to answer the recurring user question we all love "Who is blocking my query?!?!"


-- EDIT - updated with suggestions from @AaronBertrand of SQLBlog.com
-- changed fn_get_sql to sys.dm_exec_sql_text

-- EDIT - changed formatting of code so comment lines didn't run over code
  -- EDIT - again fixed weird formatting in the code snippets

coming tomorrow, Part 2 - new version using dm views

If you found this useful, click the +1 or Twitter buttons to share -- thanks

SQL code format for web pages

Just found this piece of genius: http://extras.sqlservercentral.com/prettifier/prettifier.aspx 

Paste your code and it will format it to appear like it does in SSMS, and supply you with the HTML. I know there are many, many sites that offer this, but this one actually gets it right according to SSMS colorization. Thank you SQL Server Central.

How is the role of the 'Analyst' changing in a data driven world?

     I remember the days when I was considered a top notch resource when it came to answering data driven questions. My Excel sheets were sparkling, my PowerPoint slides were beautiful, and my MS Access database was driving it all. In those days, when you needed to know how many widgets the sales team had sold, you asked them for their results sheet, and probably received it by email. When you needed to see attendance records for the call centers, they replied with their own spreadsheet, and a few paper based updates perhaps. After you had all your data collected and entered or imported to Access you could do with it what you pleased, and get some results worth sharing. But those days are gone, over the last 10 years I have witnessed an amazing transition from the scenario I describe above to the new paradigm of the 'Analyst' as power user and SQL dabbler. The data companies house internally is larger and more broad than ever before and growing quickly, and in order to present informed conclusions about a business, market or customer group, you need to be able to intelligently ask questions of these large stores of data.
     Enter the new 'Analyst', someone who is expected to know how to interact with databases like never before and to collate and present data from varied sources. Where a job description from those bygone days might have read "requires advanced MS Office suite knowledge" it is typical now to see requirements similar to "Must be familiar with SQL querying and tools such as SQL Server, Toad for SQL or PC SAS in order to gather results".

Searching Across SQL Server Part 3

To give other users access to use your SQL search script, but keep it centrally managed and compiled on your server, you need to implement it as a stored procedure or function. The choice between stored procedure and function for most installations is largely personal preference, I chose to go with a stored procedure, if you're interested in the relative merits of each there is some good reading here:

I have placed the code for the full stored procedure on git hub here: GitHub Full Procedure Code
I will only display the changes I made in the interest of keeping this post from filling your screen with code.
/*
Created by SQLWorks blog 2012
http://SQLWorks.blogspot.com
SQLWorkers@gmail.com
*/ 

CREATE PROCEDURE Usp_search @search_str VARCHAR(1000)
AS
    SET nocount ON 

...

I added the CREATE statements in the beginning, and made a couple of tweaks to the dynamic SQL in order to prevent duplicate results, see comments in the full code for details.

The usage is different of course, now you will use EXECUTE to run your new procedure and pass in the string you are searching for as a parameter as shown below.

EXECUTE Usp_search 'Employees' 


Which yields the following results showing this keyword is present in two object names:

Let's pass in a keyword present in column names:

EXECUTE Usp_search 'EmployeeID' 

The results yielded are shown below:

Now you can incorporate this procedure into jobs or other processes on your database server or in your applications, or base a report on it to allow users to search for items in your databases as well.

Part 4 coming up next, creating an SSRS report around this procedure

Go back to Part 1
Go back to Part 2

NTILE ranking functions in SQL Server

Following the previous post on the ROW_NUMBER function, lets look at other rank type functions in SQL Server and how you can use them.
Books Online does a good job of explaining the syntax, so I will link to it here and not go into too much detail beside to place this link. Ranking functions via MSDN

So, now that you have the syntax, where would you use these functions? what kind of questions can they help you answer with your data? Here is a scenario, suppose your boss asks you to apply the 10-80-10 rule (placing employees into performance groups comprising 10% (high) 80% (medium) 10% (low) ) to a group of employees every week, so they can evaluate who is performing above and below expectations. You could use any of the ranking functions to achieve this, but the quartile function works the best because it will always divide the group of employees into the number of quartiles you specify, so if people come and go you wont have to worry about counting how many are in each rank to determine over all percentile. Example below.

Here is the test data first:

CREATE TABLE dbo.EmployeePerf
(
employeeid INT NOT NULL,
employeename VARCHAR(25) NOT NULL,
sales INT,
timeperiod INT
)

INSERT INTO employeeperf VALUES (1, 'Tony', 100, 1)

INSERT INTO employeeperf VALUES (1, 'Tony', 300, 2)

INSERT INTO employeeperf VALUES (1, 'Tony', 200, 3)

INSERT INTO employeeperf VALUES (1, 'Tony', 150, 4)

INSERT INTO employeeperf VALUES (1, 'Meg', 900, 1)

INSERT INTO employeeperf VALUES (1, 'Meg', 400, 2)

INSERT INTO employeeperf VALUES (1, 'Meg', 500, 3)

INSERT INTO employeeperf VALUES (1, 'Meg', 850, 4)

INSERT INTO employeeperf VALUES (1, 'Ben', 100, 1)

INSERT INTO employeeperf VALUES (1, 'Ben', 40, 2)

INSERT INTO employeeperf VALUES (1, 'Ben', 50, 3)

INSERT INTO employeeperf VALUES (1, 'Ben', 90, 4)
INSERT INTO employeeperf VALUES (1, 'Jim', 1000, 1)

INSERT INTO employeeperf VALUES (1, 'Jim', 3000, 2)

INSERT INTO employeeperf VALUES (1, 'Jim', 2000, 3)

INSERT INTO employeeperf VALUES (1, 'Jim', 1500, 4)

INSERT INTO employeeperf VALUES (1, 'Abigail', 90, 1)

INSERT INTO employeeperf VALUES (1, 'Abigail', 40, 2)

INSERT INTO employeeperf VALUES (1, 'Abigail', 500, 3)

INSERT INTO employeeperf VALUES (1, 'Abigail', 80, 4)

INSERT INTO employeeperf VALUES (1, 'Mike', 1000, 1)

INSERT INTO employeeperf VALUES (1, 'Mike', 840, 2)

INSERT INTO employeeperf VALUES (1, 'Mike', 350, 3)

INSERT INTO employeeperf VALUES (1, 'Mike', 190, 4)

INSERT INTO employeeperf VALUES (1, 'John', 10, 1)

INSERT INTO employeeperf VALUES (1, 'John', 30, 2)

INSERT INTO employeeperf VALUES (1, 'John', 20, 3)

INSERT INTO employeeperf VALUES (1, 'John', 10, 4)

INSERT INTO employeeperf VALUES (1, 'Deb', 500, 1)

INSERT INTO employeeperf VALUES (1, 'Deb', 400, 2)

INSERT INTO employeeperf VALUES (1, 'Deb', 200, 3)

INSERT INTO employeeperf VALUES (1, 'Deb', 180, 4)

INSERT INTO employeeperf VALUES (1, 'Peter', 100, 1)

INSERT INTO employeeperf VALUES (1, 'Peter', 740, 2)

INSERT INTO employeeperf VALUES (1, 'Peter', 650, 3)

INSERT INTO employeeperf VALUES (1, 'Peter', 890, 4)

INSERT INTO employeeperf VALUES (1, 'Bill', 230, 1)

INSERT INTO employeeperf VALUES (1, 'Bill', 450, 2)

INSERT INTO employeeperf VALUES (1, 'Bill', 245, 3)

INSERT INTO employeeperf VALUES (1, 'Bill', 180, 4)

INSERT INTO employeeperf VALUES (1, 'Julie', 50, 1)

INSERT INTO employeeperf VALUES (1, 'Julie', 40, 2)

INSERT INTO employeeperf VALUES (1, 'Julie', 20, 3)

INSERT INTO employeeperf VALUES (1, 'Julie', 10, 4)

INSERT INTO employeeperf VALUES (1, 'Paul', 1000, 1)

INSERT INTO employeeperf VALUES (1, 'Paul', 700, 2)

INSERT INTO employeeperf VALUES (1, 'Paul', 600, 3)

INSERT INTO employeeperf VALUES (1, 'Paul', 590, 4)

Now to divide these employees up into the requested 10-80-10 we can apply the NTILE() function like this:

SELECT employeeid, employeename, sales, timeperiod, Ntile(10) OVER (ORDER BY sales) AS Quartile
  FROM employeeperf 
  WHERE timeperiod = 1

This gives us our results, the employees group in 10% increments by sales for the time period we care about, but what if you don't want to re-run it for each time period, and you only want to display those in the top and bottom 10% brackets? A common need for performance management, lets change the code to the following:

;WITH salescte 
AS 
( 
SELECT employeeid, employeename, sales, timeperiod, 
     Ntile(10) OVER (partition BY timeperiod ORDER BY sales) AS Quartile  -- added PARTITION BY
  FROM employeeperf 
  --WHERE TimePeriod = 1    <-- removed to include all periods 
) 

SELECT employeeid, employeename, sales, timeperiod, quartile 
  FROM salescte 
  WHERE quartile IN (1, 10) 
    ORDER BY timeperiod, quartile ASC


And the results are exactly what we wanted, the top and bottom 10%

My random test data is a bit skewed, as you can see some sales are radically different, if you were in a more normalized environment the number of people in each quartile would be more even. Hope this is useful.