The short of it is this: what I wrote in the tip is cot very clear, and is most likely going to be interpreted wrongly. Aaron’s post does a great job of explaining the difference. I also want people to understand that if you are doing compressed backups, you won’t see much added benefit if you also enable data compression. As Aaron points out in his example, the overall benefit on time and size of the final backup file are comparable whether you are compressing data or not.

I was pretty nervous about the exams. After 18 months on the job as a DBA you would think I could have passed them in my sleep. Of course, if I didn’t pass it, was I really that good at my job? Sure, servers weren’t going to suddenly start exploding if I failed one or both exams, but it would have been a huge blow to my confidence.

Thankfully, I passed.

This was a pretty big milestone as I’ve been a developer for 90% of my professional career. It’s only recently that I’ve switched around and gone with the support route. Even then I don’t consider myself a DBA. I solve problems. Right now SQL Server is how I accomplish that. This certification is just some icing on top of the last 18 months of experience. It might not prove that I know my shit, but it proves that I know a minimally acceptable amount of somebody’s shit.

I was pretty nervous about the exams. After 18 months on the job as a DBA you would think I could have passed them in my sleep. Of course, if I didn’t pass it, was I really that good at my job? Sure, servers weren’t going to suddenly start exploding if I failed one or both exams, but it would have been a huge blow to my confidence.

Thankfully, I passed.

This was a pretty big milestone as I’ve been a developer for 90% of my professional career. It’s only recently that I’ve switched around and gone with the support route. Even then I don’t consider myself a DBA. I solve problems. Right now SQL Server is how I accomplish that. This certification is just some icing on top of the last 18 months of experience. It might not prove that I know my shit, but it proves that I know a minimally acceptable amount of somebody’s shit.

I’ve been to SQL Saturday’s before, I’ve been to Manhattan before, and I’ve even been to the Microsoft office there a few times before as well. But never all three things at once, so this is a first for me. I am excited to be able to take part in this event. I see a lot of familiar names on the list of speakers and I am very interested in hearing them speak.

If you are in the area on April 24th, stop by for some free training.

In the meantime, while I was investigating this stuff, evidently the development team was looking into it on their own. They came to the same set of resources and decided to use the virtual column as part of their real-time, transactional application. Yeah, an undocumented “virtual” column going into a major application. Since I would probably be unable to do anything about this, I decided to at least look into how this thing behaves so I can be aware of what types of problems I might run into.

First, a simple query:

SELECTa.City
–,%%lockres%%
FROM Person.Address AS a
WHERE a.AddressID = 432

If you run this query and take a look at the execution plan you’ll see a nice clean clustered index seek, just as you would suspect. If you take away the comment and run it again, the execution plan is identical. On the version of AdventureWorks2008 currently installed on my machine, I get two page reads, regardless of whether or not I include %%lockres%% or not. With the comments removed, it returns the hash of the primary key: (b0004e040bc2). This looks pretty painless, free even.

If we want to see %%lockres%% in action, it’s not too difficult:

BEGIN TRAN
UPDATE Person.Address
SET City = ‘dude’
WHERE AddressID = 432;
–ROLLBACK TRAN

Obviously this will put a key lock on that row in the table. If I just select against sys.dm_os_tran_locks, the data returned looks like this:

resource_type   resource_description   resource_associated_entity_id   request_mode
KEY                       (b0004e040bc2)            72057594043564032                      X 

The original request from the development team was for a way to get the key value back when you know that a table is locked, such as the case here. I wrote this simple query to make that happen:

SELECT a.AddressID
FROM person.address(NOLOCK) AS a
JOIN sys.dm_tran_locks AS dtl
ON a.%%lockres%% = dtl.resource_description
WHERE dtl.resource_type = ‘KEY’

This query works and returns our key value of 432 just as you would want. But, take a look at the execution plan:

Yes, that’s a clustered index (or table, same thing) scan followed by a Sort followed by a merge join, processing 19614 rows to return one. But hey, it was only 341 reads. To say the least, I’m not excited about seeing this in a production system. This was explicitly cautioned in Kalen Delaney’s book. While it appears that the remote scan operator, which is how the DMV is accessed in this case, is 59% of the operation, that’s the estimated cost and has been pointed out before, isn’t the best measure of real cost in the system.

The development team went off and developed their own query, they had said they were looking for the key value, but evidently they were looking for who was holding the lock on a particular key value:

SELECT s.nt_user_name
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_sessions s
on l.request_session_id = s.session_id
inner join sys.partitions p on l.resource_associated_entity_id = p.hobt_id
where OBJECT_NAME(p.object_id) = ‘Address’ and
l.resource_description in (select %%lockres%%
from person.Address(NOLOCK) a WHERE a.AddressID = 432)

I actually had to adjust their query just a bit to get it to work correctly, but basically they had the right idea. Here’s the final execution plan:

This was still not terribly inspiring a thing to think about running in a production system although it only had one scan and seven reads. Whether or not putting this in a transactional system is a good idea, it certainly adds yet another tool, albeit an undocumented one, to the tool belt.

CREATE TABLE MyTable
(
    ID  INT IDENTITY (1, 1)  NOT NULL
        CONSTRAINT PK_tblMyTable PRIMARY KEY CLUSTERED (ID)
)

... if you say:

SELECT * FROM MyTable

... then the rows will be returned in undefined (that is, random) order.

"Clustering", I've learned, is a physical construct, not a logical one. The rows are laid out in physical order on the hard drive, but SQL Server is free (in the abscence of an ORDER BY clause) to return them in whatever order it thinks most efficient.

As developers, we don't usually see this behavior, because we tend to have fresh, well-maintained tables. In fact, I only discovered it in a customer's database that hadn't had any maintenance done to it in years. So, the bottom line is, as always, "no ORDER BY, no ordering."

Back when Hyper-V 1.0 was released it wasn’t all that great of a product.  It showed some promise, but it really wasn’t there.  I had all sorts of people (mostly from Microsoft) telling me that it was way better than ESX and that I needed to give it a shot.  My personal feelings are that it wasn’t anywhere near where ESX was, and for my production environment I needed the better product, so we went with ESX 3.5.

Well a while back Microsoft released Hyper-V 2.0 and it is a much better release than it was at the time.  I’d even be willing to stack it up next to VMware’s ESX 3.5 which was VMware’s competing version at the time of release.  Put next to ESX 3.5 you would have two well matched products.  Both included a real time online migration solution vMotion for ESX and life migration for Hyper-V.  Both support being put into a high availability cluster.  Both support pass through disks so that the guest OS has direct access to the fibre channel storage.

However shortly after Hyper-V 2.0 was release VMware released vSphere 4.0 which is the successor to ESX 3.5 and with vSphere 4.0 they’ve blown the doors off of Hyper-V yet again.

vSphere gives us FT or Fault Tolerance which basically runs a single VM on two machines with only one of the machines being active at a time.  In the event that one host fails the other host being running the VM actively with no outage to the guest.  Users connected to the guest won’t even know that the guest has switched to another machine.

VMware has also introduced some interesting features as experimental which means that we will probably see them show up as full on features in a future release.  This includes the ability to map an HBA directly to a virtual machine to give the VM actual direct access to the HBA.  At the moment that HBA can only be mapped to a single VM, but hopefully in the next release they will fix that.

Now don’t get we wrong, I think that Hyper-V has come a long way since the v1 release.  Do I think that Hyper-V is an Enterprise Ready solution?  Yes I do.  Do I think Hyper-V is ready to be called the winner in the virtualization server space?  No, not at all.  I think it is anyone’s game still before we have a clear winner.  Hyper-V has a big selling point to it, the cost to get into Hyper-V is free, as long as you don’t want to cluster it.  Then you’ll need to purchase a management tools license for each host machine.  How with VMware you’ll want the management tool weather you cluster the machines or not, but its a single purchase from VMware at least.

What it really comes down to is that you need to fully evaluate both platforms and due a solid CBA (Cost Benefit Analysis) as well as a full feature analysis before picking a platform for your enterprise.  Because once you pick one platform moving from one to the other is very tough to do.

Denny

I don't have many cool tools at my current gig to help me out, so I'm often forced to come up with quick and dirty solutions of my own. Today I have to compare row counts in two identical databases, one in Dev and one in QA. Here's what I did.

1. Generate a list of select count(*) statements

3. Do the same on the other environment.

 Now at this point, you have your data. If there are few enough tables, you can compare by hand. Or you could run each set of SELECT statements with Save As File, import those file to your sandbox DB as two tables, and compare rowcounts that way.

Edit: That's the old school way of getting rowcounts from pre-DMV days. So if you're using SQL Server 2000 or earlier, or need exact rowcounts, use the above.  If you'd like something faster and easier, check out Aaron Bertrand's (blog | twitter) solutions in the comments below!

Another day, another dirty little trick from the MidnightDBA.  Show us your dirty tricks, or a better way to compare - either blog or post in comments!

-Jen McCown
http://www.MidnightDBA.com

In my last post, http://jahaines.blogspot.com/2010/03/performance-tuning-101-what-you-will.html I talked about performance tuning queries that appear to be well tuned.  There are a lot of optimization techniques available unbeknownst to most developers that do not require indexes or radical code changes.  These are the optimizations that I will be talking about in this post.  There is absolutely no way I could go over every possible optimization technique available, but I will do my best to present as much content here today, and will make future posts on other techniques.

I will start things off  by talking about a challenge that Ramesh Meyyappan presented in his webcast, http://www.sqlworkshops.com/.  Ramesh’s challenge was to solve the TOP 101 phenomenon, using SQL Server 2005.  To start things off, I will create a sample table, with data.

USE [tempdb]
GO

SET NOCOUNT ON;
GO

IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'TestData')
BEGIN
    DROP TABLE dbo.[TestData];
END
GO

CREATE TABLE dbo.TestData(
RowNum INT PRIMARY KEY,
SomeId INT,
SomeCode CHAR(2000)
);
GO

;WITH 
   L0 AS (SELECT 1 AS C UNION ALL SELECT 1)       --2 rows
  ,L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B)    --4 rows (2x2)
  ,L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B)    --16 rows (4x4)
  ,L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B)    --256 rows (16x16)
  ,L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B)    --65536 rows (256x256)
  ,L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B)    --4,294,967,296 rows (65536x65536)
  ,Number AS (SELECT row_number() OVER (ORDER BY (SELECT 0)) AS N FROM L5) 
INSERT INTO dbo.TestData
SELECT
    N AS RowNumber,
    ABS(CHECKSUM(NEWID()))%1000000+1 AS SomeId ,
    REPLICATE('a',2000) AS SomeCode
FROM Number
WHERE [N] <= 50000
GO

UPDATE STATISTICS dbo.[TestData] WITH FULLSCAN;
GO

Next, I will create a query that uses TOP and an order by to return 100 rows. 

--Fast
SELECT TOP 100 [RowNum],[SomeId],[SomeCode]
FROM dbo.[TestData]
WHERE [RowNum] < 30000
ORDER BY [SomeId]
OPTION (MAXDOP 1)
/*
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.

 SQL Server Execution Times:
   CPU time = 78 ms,  elapsed time = 102 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
*/

Now watch what happens when I change the TOP operator to 101.  You will notice that I did not change anything else in the query other than increasing the number of rows returned by 1.

--Slow
SELECT TOP 101 [RowNum],[SomeId],[SomeCode]
FROM dbo.[TestData]
WHERE [RowNum] < 30000
ORDER BY [SomeId]
OPTION(MAXDOP 1)

/*
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 312 ms,  elapsed time = 1690 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
*/

Wow…. TOP 101 is over 17 times slower than TOP 100 and all I changed is the number of rows in the TOP operator!!!!!  So why does the optimizer take so much longer to optimize and execute a query using TOP 101, oppose to TOP 100?  The short answer is the memory requirements.  The TOP 101 queries requires a lot more query memory than TOP 101, which translates into tempdb sorting.  As you may recall, I addressed some techniques to solve the tempdb sorting problem in my last post, http://jahaines.blogspot.com/2010/03/performance-tuning-101-what-you-will.html.  If you are using SQL 2008, you can use the same optimization techniques presented in my prior post, but SQL 2005 is a completely different animal.  To make the TOP 101 query faster, we need to first understand why it is slower.  Let’s take a look at what is different when we run the TOP 100 and the TOP 101 query.

Lets start by looking at the memory SQL Server grants to each query.  Open two different query windows and execute each top query within a while loop.  We can then use sys.dm_os_memory_grants to get the required memory.

Here is a sample of how to run the TOP query in a while loop.

WHILE 1=1
SELECT TOP 100 [RowNum],[SomeId],[SomeCode]
FROM dbo.[TestData]
WHERE [RowNum] < 30000
ORDER BY [SomeId]
OPTION(MAXDOP 1)

In a new query window, run the following query to get the memory specifications.

SELECT  [granted_memory_kb],[required_memory_kb],[max_used_memory_kb] FROM sys.dm_exec_query_memory_grants WHERE [session_id] = 58

Here are my results:

     granted_memory_kb    required_memory_kb   max_used_memory_kb
---- -------------------- -------------------- --------------------
FAST 1024                 216                  216

     granted_memory_kb    required_memory_kb   max_used_memory_kb
---- -------------------- -------------------- --------------------
SLOW 6040                 512                  6040

The results are simply astonishing.  The memory requirements increase nearly 28 times when I use TOP 101, instead of TOP 100.  I do not have a formal explanation of why the TOP 101 operator, consumes more memory than TOP 100.  Brad Schulz, http://bradsruminations.blogspot.com/, has contacted Conor Cunningham about this issue and believes that 101 is an arbitrary threshold.  Brad is working on an in-depth post involving the TOP operator.  Keep an eye out for this one, as it should be really good.  Anyway, once the 101 threshold is breached the optimizer uses different calculations to optimize a query, which can effectively bloat the memory requirements for the query.  This memory bloat forces the sort operation to spill into tempdb.  This is where the TOP 101 bottleneck exists.  To verify this problem, open profiler and choose the sort warnings counter and you will see that the slow query has a sort warning error, while the fast query does not.

Now that I have identified the problem, how do I solve it?  I will start by attempting the methods that I used in the previous article, http://jahaines.blogspot.com/2010/03/performance-tuning-101-what-you-will.html.

SELECT TOP 101 [RowNum],[SomeId],CAST([SomeCode] AS VARCHAR(4200))
FROM dbo.[TestData]
WHERE [RowNum] < 30000
ORDER BY [SomeId]
OPTION(MAXDOP 1)

/*
 SQL Server Execution Times:
   CPU time = 344 ms,  elapsed time = 3385 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
*/

Bloating the estimated row size still did not help our situation.  Next I will try shrinking the row size.

SELECT TOP 101 [RowNum],[SomeId],RTRIM(CAST([SomeCode] AS VARCHAR(2000)))
FROM dbo.[TestData]
WHERE [RowNum] < 30000
ORDER BY [SomeId]
OPTION(MAXDOP 1)

/*
 SQL Server Execution Times:
   CPU time = 344 ms,  elapsed time = 2461 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
*/

Hmm.  Still no luck….. How can I reduce the row size of the input passed into the sort operator?  When you really sit back and think about the problem, the answer is really simple.  To reduce the row size, all you have to do is reduce the columns involved in the sort.  I like to use the TOP inside a derived table, making sure to only use the RowNum and SomeId columns.  We can then join back onto the TestData table.  This gives us a fast sort and a ultra fast index seek on the 101 rows we are returning.

SELECT t.[RowNum],t.[SomeId], t.[SomeCode]
FROM dbo.[TestData] t
INNER JOIN(
    SELECT TOP 101 [RowNum],[SomeId]
    FROM dbo.[TestData]
    WHERE [RowNum] < 30000
    ORDER BY [SomeId]
) AS t2
    ON T.RowNum = t2.RowNum
ORDER BY t.[SomeId]
OPTION (MAXDOP 1)

/*
 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 104 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
*/

Alternatively, we can use correlated subqueries or the cross apply operator.

SELECT TOP 101
    t.[RowNum],
    (SELECT t2.[SomeId] FROM dbo.[TestData] t2 WHERE t2.[RowNum] = t.[RowNum]) AS SomeId,
    (SELECT t2.[SomeCode] FROM dbo.[TestData] t2 WHERE t2.[RowNum] = t.[RowNum]) AS SomeCode
FROM dbo.[TestData] t
WHERE t.[RowNum] < 30000
ORDER BY t.[SomeId]
OPTION (MAXDOP 1)

SELECT TOP 101
    t.[RowNum],
    t2.SomeId,
    t2.SomeCode
FROM dbo.[TestData] T
CROSS APPLY(SELECT t2.SomeId, t2.SomeCode FROM dbo.[TestData] t2 WHERE t2.[RowNum] = t.[RowNum]) AS t2
WHERE t.[RowNum] < 30000
ORDER BY t.[SomeId]
OPTION (MAXDOP 1)

It should be noted that the correlated subquery method will produce more IO because it uses two subqueries.  As you can see, the solution to this challenge is quite simple, but the solution requires an understanding of what is occurring underneath the hood of SQL Server. 

Note: It is still possible that some of the sorting will be sent to tempdb, but you should see a elapsed time that rivals TOP 100.

The next optimization technique, I will be demonstrating is a predicate pushing problem.  Unbeknownst to most developers, SQL Server 2005 does have a problem with predicate pushing in views.  A lot of these issues have been resolved in SQL Server 2008, but should be known.  I will be demonstrating a very simple example, using a ranking function.  Ranking functions are relatively new to SQL Server and were introduced in 2005.  I am sure there are other scenarios that cause predicate pushing problems, but I will only be addressing the ranking problem, in this post.

Let’s start by creating a small sample table.

USE [tempdb]
GO

CREATE TABLE Test(
ID INT IDENTITY(1,1) PRIMARY KEY,
FName VARCHAR(50),
LName VARCHAR(50)
);

INSERT INTO dbo.Test VALUES ('Adam','Haines');
INSERT INTO dbo.Test VALUES ('John','Smith');
INSERT INTO dbo.Test VALUES ('Jane','Doe');
GO

CREATE NONCLUSTERED INDEX ncl_idx_LName ON dbo.Test(LName) INCLUDE(FName);
GO

As you can see, the table is relatively simple.  The idea is to present an easy to understand example that demonstrates potential performance problems with views.

Here is my simple query that shows an index seek on LName.

SELECT Id,FName,LName,ROW_NUMBER() OVER(PARTITION BY LName ORDER BY Id) AS seq
FROM dbo.Test
WHERE LName = 'Smith'
GO

Let’s see what happens when I put the logic into a view, with no predicate.  The predicate will be called from outside the view and should be pushed down into the view, as views are materialized into the underlying objects at runtime.

CREATE VIEW dbo.vw_Test
AS
SELECT Id,FName,LName,ROW_NUMBER() OVER(PARTITION BY LName ORDER BY Id) AS seq
FROM dbo.Test
GO

I will now query the view using the same predicate as the original query.

SELECT Id,FName,LName,seq
FROM dbo.vw_Test 
WHERE LName = 'Smith'
GO

The problem here is the optimizer decided to filter the results of the query AFTER the table “Test” has been scanned.  One should expect the optimizer to seek on the LName column because the optimizer should push the predicate; however, SQL Server 2005 does not do a great job of this.  SQL Server 2008 will appropriately push the predicate deep into the plan to get the index seek.  How do we solve this problem?  Unfortunately, there is not a whole lot you can do to make the plan work more efficiently.  The best option in my opinion is to a INLINE TVF to parameterize the query. 

CREATE FUNCTION dbo.fn_Test(@LName VARCHAR(50))
RETURNS TABLE
RETURN(
    SELECT Id,FName,LName,ROW_NUMBER() OVER(PARTITION BY LName ORDER BY Id) AS seq
    FROM dbo.Test
    WHERE LName = @LName
)
GO

Now execute a select against the TVF using the same predicate.

SELECT Id,FName,LName,seq
FROM dbo.fn_Test('Smith')

There you have it.  I have demonstrated a few optimization techniques that I have used to solve performance problems.  I have only scratched the surface here.  There are many more optimization techniques available.  Stay tuned for future posts, where I will explore even more optimization techniques including a shocking example demonstrating how an index rebuild can introduce fragmentation and how to avoid it.

Until next time, happy coding.