SQL Server Blogs

PAL and SQLDiag with SQL Server 2012

MartinBell — Thu, 24 May 2012 22:56:00 GMT

After a new release you may expect a delay in updating codeplex projects to the latest SQL Server version. Here are some tips on using PAL and SDCT until a version that officially supports SQL Server 2012 is available....(read more)

How atomic is a SELECT INTO?

leo.pasta — Tue, 22 May 2012 22:47:00 GMT

Last week I got an interesting situation that prompted me to challenge a long standing assumption. I always thought that a SELECT INTO was an atomic statement, i.e. it would either complete successfully or the table would not be created.

So I got very surprised when, after a “select into” query was chosen as a deadlock victim, the next execution (as the app would handle the deadlock and retry) would fail with:

Msg 2714, Level 16, State 6, Line 1
There is already an object named '#test' in the database.

The only hypothesis we could come up was that the “create table” part of the statement was committed independently from the actual “insert”. We can confirm that by capturing the “Transaction Log” event on Profiler (filtering by SPID0). The result is that when we run:

SELECT * INTO #results FROM master.sys.objects

we get the following output on Profiler:

It is easy to see the two independent transactions. Although this behaviour was a surprise to me, it is very easy to workaround it if you feel the need (as we did in this case). You can either change it into independent “CREATE TABLE / INSERT SELECT” or you can enclose the SELECT INTO in an explicit transaction:

SET XACT_ABORT ON
BEGIN TRANSACTION
SELECT * INTO #results FROM master.sys.objects
COMMIT

Coming back from (blog) retirement

leo.pasta — Tue, 22 May 2012 21:30:54 GMT

So, it has been more than 3 years without a single blog post. I wished I could have a decent excuse for it, but in the end, I guess it boils down to laziness and procrastination. :-)

Even though I learned a lot in that period (and added a feel tricks to my bag), I couldn’t find the will to sit down and write. I hope all my readers (yes mom and dad, I’m talking to you) have not been disappointed.

I will try really hard not to let routine take over. I don’t expect I will be the most active blogger in the community, but hopefully a couple of posts per month is a good target to aim.

Changing Server Side Trace Scripts

MartinBell — Sun, 20 May 2012 18:38:00 GMT

All though Server Side Traces are being depricated they will still be around for some time. This nuggets of code may help make them easier to use....(read more)

SQL Server data platform upgrade - Why upgrade and how best you can reduce pre & post upgrade problems?

ssqa.net — Sat, 19 May 2012 10:26:00 GMT

SQL Server upgrade, let it be database(s) or instance(s) or both the process and procedures must follow best practices in order to reduce any problems that may occur even after the platform is upgraded. The success of any project relies upon the simpler methods of implementation and a process to reduce the complexity in testing to ensure a successful outcome. Also the topic has been a popular topic that .... read more from here ......(read more)

Microsoft SQL Server 2008 R2 Administration Cookbook

ssqa.net — Fri, 18 May 2012 22:25:00 GMT

Its one year on my first book released, keeping aside the financial gains from this book I'm more happy to achieve one of the important goals from my career. This is something big in my life to announce, it gives immensive pleasure and happiness to share about my first book (hard paper) and eBook release, titled : Microsoft SQL Server 2008 R2 Administration Cookbook is released and out now. share my experience and task based real-world best practices in a cookbook style. My thanks to the technical...(read more)

SQL Server 2012 - Upgrade Whitepaper

JustinL — Thu, 17 May 2012 16:24:00 GMT

Just a short note to mention Microsoft have released the Technical Reference Guide for upgrading to SQL Server 2012.

The paper is available for download here: http://tinyurl.com/84xm5b4

There's some interesting details on approaches to upgrade, including features such as high availability, full-text search, service broker and other components (SSIS, SSAS, SSRS). Additionally, there's a (fairly) recent initiative to organise and present TechNet content more easily, there's some useful content (with interesting presentation) at the link below:

http://technet.microsoft.com/en-us/library/hh393545.aspx

Good luck planning your upgrades,

Regards,

Justin

Read Committed Snapshot Isolation– Two Considerations

GavinPayneUK — Fri, 11 May 2012 20:27:20 GMT

The Read Committed Snapshot database option in SQL Server, known perhaps more accurately as Read Committed Snapshot Isolation or RCSI, can be enabled to help readers from blocking writers and writers from blocking readers. However, enabling it can cause two issues with the tempdb database which are often overlooked. One can slow down queries, the other can cause queries to fail . Overview of RCSI Enabling the option changes the behaviour of the default SQL Server isolation level, read...(read more)

“Query cost (relative to the batch)” <> Query cost relative to batch

Dave Ballantyne — Thu, 10 May 2012 10:19:17 GMT

OK, so that is quite a contradictory title, but unfortunately it is true that a common misconception is that the query with the highest percentage relative to batch is the worst performing. Simply put, it is a lie, or more accurately we dont understand what these figures mean.

Consider the two below simple queries:

SELECT * FROM Person.BusinessEntity
JOIN Person.BusinessEntityAddress
ON Person.BusinessEntity.BusinessEntityID = Person.BusinessEntityAddress.BusinessEntityID
go
SELECT * FROM Sales.SalesOrderDetail
JOIN Sales.SalesOrderHeader
ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID

After executing these and looking at the plans, I see this :

So, a 13% / 87% split , but 13% / 87% of WHAT ? CPU ? Duration ? Reads ? Writes ? or some magical weighted algorithm ?

In a Profiler trace of the two we can find the metrics we are interested in.

CPU and duration are well out but what about reads (210 and 1935)? To save you doing the maths, though you are more than welcome to, that’s a 90.2% / 9.8% split. Close, but no cigar.

Lets try a different tact. Looking at the execution plan the “Estimated Subtree cost” of query 1 is 0.29449 and query 2 its 1.96596. Again to save you the maths that works out to 13.03% and 86.97%, round those and thats the figures we are after. But, what is the worrying word there ? “Estimated”.

So these are not “actual” execution costs, but what’s the problem in comparing the estimated costs to derive a meaning of “Most Costly”. Well, in the case of simple queries such as the above , probably not a lot. In more complicated queries , a fair bit.

By modifying the second query to also show the total number of lines on each order

SELECT *,COUNT(*) OVER (PARTITION BY Sales.SalesOrderDetail.SalesOrderID)
 FROM Sales.SalesOrderDetail
JOIN Sales.SalesOrderHeader
ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID

The split in percentages is now 6% / 94% and the profiler metrics are :

Even more of a discrepancy.

Estimates can be out with actuals for a whole host of reasons, scalar UDF’s are a particular bug bear of mine and in-fact the cost of a udf call is entirely hidden inside the execution plan. It always estimates to 0 (well, a very small number).

Take for instance the following udf

Create Function dbo.udfSumSalesForCustomer(@CustomerId integer)
returns money
as
begin
   Declare @Sum money
   Select @Sum= SUM(SalesOrderHeader.TotalDue)
     from Sales.SalesOrderHeader
    where CustomerID = @CustomerId
   return @Sum
end

If we have two statements , one that fires the udf and another that doesn't:

Select CustomerID
  from Sales.Customer
 order by CustomerID
go
Select CustomerID,dbo.udfSumSalesForCustomer(Customer.CustomerID)
  from Sales.Customer
 order by CustomerID

The costs relative to batch is a 50/50 split, but the has to be an actual cost of firing the udf.  Indeed profiler shows us :

No where even remotely near 50/50!!!!

Moving forward to window framing functionality in SQL Server 2012 the optimizer sees ROWS and RANGE ( see here for their functional differences) as the same ‘cost’ too

SELECT SalesOrderDetailID,SalesOrderId,
       SUM(LineTotal) OVER(PARTITION BY salesorderid 
         ORDER BY Salesorderdetailid RANGE unbounded preceding)
from Sales.SalesOrderdetail
go
SELECT SalesOrderDetailID,SalesOrderId,
       SUM(LineTotal) OVER(PARTITION BY salesorderid 
       ORDER BY Salesorderdetailid Rows unbounded preceding)
from Sales.SalesOrderdetail

By now it wont be a great display to show you the Profiler trace reads a *tiny* bit different.

So moral of the story, Percentage relative to batch can give a rough ‘finger in the air’ measurement, but dont rely on it as fact.

Changing SQL Server Port with Powershell

MartinBell — Mon, 07 May 2012 16:48:00 GMT

Powershell will be even more important when managing SQL Server on Windows Server Core so it's time to build up your toolbox of scripts....(read more)