SQL Server Blogs

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)

Knowing when you’re ready to attempt to become an MCM of SQL Server

GavinPayneUK — Tue, 01 May 2012 09:09:23 GMT

Since I became a “Microsoft Certified Master of SQL Server” I’ve had lots of people asking me how I prepared, how I knew I was ready to take the exams and what they were like. At the SQLBits certification session recently I was invited on to the panel to answer questions about the “modern” MCM program. This article hopes to answer some of the questions people ask yet they will always have very personal answers. What feels difficult for me might be slightly less difficult for you yet I might make...(read more)

Offset without OFFSET

Dave Ballantyne — Thu, 26 Apr 2012 10:34:00 GMT

A while ago Robert Cary posted an article on SQL Server Central entitled 2005 Paging – The Holy Grail which is, as the title would suggest about paging in SQL Server. This article provoked some really interesting chat around the subject and is well worth a read.

This is now a lot easier in SQL Server 2012 with the introduction of the OFFSET extension to the ORDER BY clause, but what is the most optimal method is you are not using 2012 ?

Well, whilst playing around by the OFFSET portion of my “What’s new in SQL Server 2012 – TSQL” presentation, I hit on a different method that I’ve not seen published before.

Now whilst finding which rows are on which page is a problem, it is only part of a much wider problem, that being that cost of the lookups to find other related data. For example: You have a list of people which you are paging through in the order of LastName, but you also wish to display FirstName. That is not in your index and so a key lookup occurs, OK I could INCLUDE it in the index but im just simplifying the problem.

So, to demonstrate this I need to create an index on Person.Person in AdventureWorks.

Create index idxLastName on Person.Person(LastName)

The query for the “holy grail” method would look something like this :

with ctePaging
as
(
Select LastName,FirstName,
       row_number() over (order by LastName,BusinessEntityID)-1  as RowN
 from  Person.Person
)
Select * from ctePaging
where RowN between 20 and 39 
order by RowN;

The issue here is that SQL Server has initiated an index scan (against a different index than the one we created) and had to process all the rows in the table and then sort them.

We only want 20 rows returned so this is quite a lot of wasted effort on the engine's part.

OFFSET has been introduced in 2012 and running the equivalent query of :

Select LastName,FirstName,BusinessEntityID
 from  Person.Person
 order by LastName,BusinessEntityID
 offset 20 rows fetch next 20 rows only;

Gives us the query plan of :

Even this is non-optimal though, as the key lookup has occurred 40 times , even though we only needed the data (in this case FirstName) for 20 rows.

This can be resolved by doing the key lookup yourself.

with cteKeySeek
as
(
Select LastName,BusinessEntityID
 from  Person.Person
 order by LastName,BusinessEntityID
 offset 20 rows fetch next 20 rows only
)
Select cteKeySeek.LastName,
       FirstName,
       cteKeySeek.BusinessEntityID
 from  cteKeySeek
 inner join  Person.Person   
   on  cteKeySeek.BusinessEntityID =   Person.BusinessEntityID
order  by cteKeySeek.LastName,FirstName,cteKeySeek.BusinessEntityID;

Even though its longer, wordier and involves a join , it is more efficient as the join has replaced the key lookup and it is now only occurring on the 20 rows of data that we need

Quite neat hey ? When using OFFSET it is important to remember that no magic is happening, SQL Server still has to ‘count’ and scan through the rows that are not to be processed before it can decide which ones it does need.

A comparable query for previous versions and taking the lead from the holy grail method would be :

with cteKeySeek
as
(
Select BusinessEntityID,LastName,
       row_number() over (order by LastName,BusinessEntityID)-1 
             as RowN
 from  Person.Person

)
Select cteKeySeek.LastName,FirstName,cteKeySeek.BusinessEntityID ,RowN
  from cteKeySeek
  inner loop join  Person.Person   
   on  cteKeySeek.BusinessEntityID =   Person.BusinessEntityID
where RowN >= 20 and rown<=39
order by LastName,BusinessEntityID;

Which does similarly filter the rows before doing the index lookup

It does however still involve a scan of 19,972 rows of which 19,932 are irrelevant to our final result set. You may of noticed in the OFFSET versions that the TOP operator is used to filter the data and ‘stop’ the scan once it has reached the last row that we are interested in. What if we could do something similar.

What about this ?:

with cteKeySeek
as
(
Select BusinessEntityID,LastName,
       row_number() over (order by LastName,BusinessEntityID)-1 
             as RowN
 from  Person.Person

)
Select top(20) cteKeySeek.LastName,FirstName,cteKeySeek.BusinessEntityID ,RowN
  from cteKeySeek
  inner loop join  Person.Person   
   on  cteKeySeek.BusinessEntityID =   Person.BusinessEntityID
where RowN >= 20 and RowN<=39
order by LastName,BusinessEntityID;

That does have the rather interesting effect of doing exactly that:

So, this is looking (at least in-terms of rowcounts) very similar to the OFFSET functionality. If we look for a page of data further on (rows 200 to 219) and look at an profiler trace we can see how the three type of query compare.

So as you can see over a medium size (ish) dataset the fake and real offset are comparable in terms of IO.

Hope this helps someone, who needs to do paging

What to do when the solution will never be in front of you

GavinPayneUK — Wed, 25 Apr 2012 21:46:05 GMT

Sometimes you have a requirement which you don’t have a tool in your toolbox for. When that happens the solution may not be to just get another tool and add it to your existing toolbox, it might be you have to use an entirely new type of toolbox with tools you’ve never used before in it. Doing that maybe a bold architectural decision but is often where the biggest performance or functionality gains can come from. Recently, in the SQL Server space there have been some technologies become...(read more)

SQL Server Builds (April 2012)

NeilHambly — Tue, 24 Apr 2012 21:43:00 GMT

If you like me - Knowing which Build you should have deployed when we look @ the myriad of Service Packs & Cumulative Updates possible, it can quickly become confusing What actions should I do to "PATCH" a server to the appropriate build, So here is a quick script that can easily be updated as new builds are made available, and easily customized for your own environments -- This Script only reports on your current version and recommends your course of action - Usualy disclaimer of using...(read more)

UK SQL Server (SQL Relay 2012) - 5 cities of full day content 21st May - 30th May - Overview and Deepdive - It's free too

tonyrogerson — Tue, 24 Apr 2012 13:02:00 GMT

Last October {2011} we held our the first ever "SQL Relay", this title was chosen because it was to be held on five consecutive days, with each of the UK regional SQL Server User Groups hosting an evening event in a relay fashion.

These events were a tremendous success; we all had a lot of FUN hosting the events, and had received some excellent feedback from both our regular and first time attendees to the User Group events.

Having known about the impending release of SLQ Server 2012 for a while now, the same UG Leaders and few new ones have been working tirelessly behind the scenes to organize the “SQL” to “SQL Relay” in the imaginatively titled “SQL Relay 2012” – Hey we’re DBAs not marketing Gurus!

Once again, we will be holding five days of regional events, the various user group leaders have been working very hard in the background, this time we are providing five full day events and they are FREE

These full day events will comprise of a morning overview of SQL Server 2012, which will be given by Microsoft speakers and Partners. The afternoon session will focus on some deeper technical content on the 2012 features; these will be delivered by UK SQL MVPs (Microsoft Most Valuable Professionals). With the evening events being a great way to complete the day or join us if you weren’t fortunate enough to be able to attend our morning or afternoon sessions, the content of these evening events will vary from location to location on their individual style and content – more details to follow soon.

Each of the five events will have their own registration and there are limitations on the number of attendees each event can hold, so be sure to register as early as possible to secure your place, you can attend as many or as few sessions as you wish, just indicate your preference when registering.

You may be asking “How much does It Cost to attend?” These are FREE events, due to HUGE generosity of our sponsors.

When and Where our these events being held

21st May Edinburgh
http://sqlserverfaq.com/?eid=378
Microsoft Edinburgh, Waverley Gate, 2-4 Waterloo Place, Edinburgh, EH1 3EG

22nd May Manchester
http://sqlserverfaq.com/?eid=373
The Co-operative, CIS Tower, Miller Street,
Manchester, M60 0AL

23rd May Birmingham
http://sqlserverfaq.com/?eid=357
Lakeside Centre, Aston University's Conference Centre, Birmingham, B4 7ET

24th May Bristol
http://sqlserverfaq.com/?eid=391
Avon Gorge Hotel, Sion Hill, Clifton, Bristol, BS8 4LD

30th May London
http://sqlserverfaq.com/?eid=377
Microsoft London (Cardinal Place), 100 Victoria Street
London SW1E 5JL

Aside from learning from the excellent speakers throughout the day, you will have the chance to network as well as chances to win prizes

Why not bring along a friend/colleague – Many of you work in teams or have SQL professional friends, so we have a special invitation and competition for you

Simply register and indicate a friend you’d like to invite – if both of you attend you also have a chance to both win a fabulous {book} prize.

Would you like to know more on this these events? fear not as we will be providing further details over the next few weeks in some follow-up emails

We like to ensure you get the information right @ your fingertips - So we also have also put all the relevant information on the events @ these locations as well.

FACEBOOK: https://www.facebook.com/SQLRelay2012 & https://www.facebook.com/SQLRelay2012/events
LINKEDIN GROUPS: http://www.linkedin.com/groups?gid=4153765&trk=hb_side_g & http://www.linkedin.com/groups?gid=2904068&trk=hb_side_g
TWITTER: Simply follow this hash tag for all the latest news on the event #sqlrelay

Want to attend one of the User group events, visit our group website www.sqlserverfaq.com for a full listing of all our events, we also have a map of all UK User Groups http://tsqltidy.com/newmap/map.htm

Microsoft Certifications – a newcomer’s guide

GavinPayneUK — Mon, 23 Apr 2012 14:01:08 GMT

At the recent SQLBits X conference I was invited onto the panel during a session about Microsoft certifications and I said I’d write a follow-up article about some of what we discussed. While one of the session’s aim was to start communicating the changes the SQL Server 2012 certifications are bringing, most of the questions were about Microsoft certifications in general, although there was a disproportionately high interest in the Microsoft Certified Master. I’ll cover MCM topics in...(read more)

The unknown unknowns

DavidWimbush — Mon, 23 Apr 2012 10:17:00 GMT

I was spring cleaning some old scripts and I came across my first attempt at establishing a backup strategy. 12 hours later I'm still feeling a bit queasy. When I first put on the DBA hat, we were running with the simple recovery model and doing a full backup every night. I did some homework and thought I'd got it all figured out. My killer improvement? Use full recovery and every night do a log backup and then a full backup. That way we could recover to any point in time. Brilliant!*

Since then I've learned a lot. I know I'm much better than I was. But I can never quite shake that small nagging doubt: how much of what I'm doing now - believing it's good practice - will I one day look back on and shudder? But this is a good thing. It keeps me on my toes, continually re-evaluating what I'm doing, and hungry to learn.

*Just to be clear, I'm being sarcastic. This NOT a good solution. If you have any doubts:

Read Books Online about recovery models and backups.
Read what Paul Randal has written about it in his blog and in TechNet Magazine.
Check out Ola Hallengren's database maintenance solution before you write your own.