BrandonGalderisi

Development Environment Isolation

2009-11-18T03:03:00Z

One of the challenges facing many development organizations is how to provide adequate development needs while providing enough isolation to prevent developer conflicts. Today's virtual technology provides the ability to spin up developer specific environments quickly easily while providing the horsepower to mimic near physical environment capacity. I will leave the discussions over physical vs. virtual to discuss in another area, I will just talk about my recent experience, and how handling our environment differently could have yielded a more pleasurable result.

First I will tell you a little bit about how we work.

We heavily utilize virtual environments for our development and testing. We buy high end hardware and run on high end SAN solutions. I don't know all of the details about it, I just know that it works and our SEs (System Engineers) bust their butts to give us what we need. Our old development and QA environments are based on some variation of VMWare. Again, I don't know what it is except that it's virtual hardware and it meets our needs. We need an environment, we ask, they deliver. We are in the process of migrating all of our virtual systems for development and QA to a VMWare Lab Manager environment in a different data center. It provides the ability to spin up groups of isolated environments, clone environments to a new configuration and much more. I won't go into the details about VMWare Lab Manager but our SEs have handed over the power to us to be able to create our own virtual environments as we need. They have also provided is with over 9TB (and growing) of storage per team location to host the machines. Unfortunately we are not 100% live on this environment because we have terabytes of data to move across the WAN to the new data center.

So here is what happened recently, and how things could have been different had we been completely migrated to our new VMWare Lab Manager environment.

I was working on diagnosing a problem with deadlocks that was happening in our QA environment. Our QA environments are well protected environments in that our developers, which I am one of, do not have administrative privileges over the web, file, database or any other servers. Our QA teams manage them and we are limited to a basically read only access. So when it comes time to replicating problems found in QA we must do it in our development environment. Had this environment been in our VMWare Lab Manager environment, I would have been able to snapshot the QA environment, make myself an admin over the environment, and proceed with reproducing the errors. Unfortunately it was not so I proceeded to locate an environment with the closest configuration and the same or more data. Since we are not fully live on our new environment, all of our larger development and test environments are in our old system where we do not have abilities to clone configurations. I contacted the person who managed the environment I had been developing on a few weeks prior in order to determine the status of the setup. I informed them that I needed to do extensive testing and would be doing large amounts of data manipulations to the system for troubleshooting a problem with the project that the system was originally setup to develop and test on. I was told that the environment was scheduled for decommission two days from now and that it was not in use. I did not need to backup the database since it was scheduled for decommission, and I would only be working with 4-5 tables with under a billion records which I would be truncating in order to test the process which was deadlocking. Due to the size of the database (100GB+) and the low amounts of storage capacity in our old environment, I likely would not have been able to backup the database if I had tried.

After receiving the all clear, I connected to the system, truncated the tables which I would be working with and started my testing. It didn't take long to reproduce the problem and I was happy. The environment had served it's EXACT purpose to me. As I was adjusting the code to eliminate the deadlocks, the person that I had received permission to use the environment stopped by my desk. They asked, in a troubling voice, "are you working on the ________ environment". Puzzled, since I had just asked for permission less than two hours ago, I replied "Uhhh... yeah. We just talked about that two hours ago". Well APPARENTLY the system was not quite as "not in use" as they had believed. The system has been used for the past two weeks to do large scale testing. Testing that was scheduled to complete the next day and full reports to be generated. Unfortunately, my testing required that the very tables that were used to calculate the results of the scale testing be truncated to perform my tests. Two weeks of testing, POOF GONE in less than 5 minutes.

How could this disaster have been prevented?

I could have (attempted to) backup the database
If successful I could have copied that database to the new environment for testing
I could have looked through the database to look and see how recently it had been used through some of the logging tables (there are several)
I could have found a different environment, which may have ended in the same result with a different team

How will VMWare Lab Manager prevent this in the future?

Rather than taking over the environment I could have cloned it to a new fenced environment where my changes would not impact any other developer

Pretty simple!

In closing, I do not place blame on any one person for this. There are things that could, and should, have been done that could have prevented this. I certainly accept part of the blame because there are things that I could have done to ensure the environment was actually unused before proceeding with my testing. And virtualization is just one solution for preventing this type of scenario from happening. And it may not be the solution for your organization. But whatever your needs may be, make sure that you take the proper precautions to ensure that your development environments are isolated in such a way that developers to not wreak havoc on other developers efforts. Work with your boss, your IT staff and the other developers to create a productive environment that meets your needs. Document your configurations so that when the question is asked "Who is using this server/database/file" it can be easily answered.

Brandon Galderisi
SQL Server MVP
SQL Server Nation Co-Founder

Using row_number() vs. a correlated sub-select

2009-09-21T03:40:00Z

In this article, I will show you the differences in using the row_number() function versus a correlated sub-select for filtering data.

One thing that I have seen frequently is the use of the row_number() function to limit result sets. While row_number() has it's uses, using it to filter results is not necessarily one of them. There are several scenarios in which this is useful. Before setting up the test data and reviewing the results, I will explain a few scenarios that I have seen.

Scenario 1
I have a table that contains customer transactions. I want to show the most recent transaction for ALL customers.

Scenario 2
I havea table that contains patient doctor visits. I want to show the most recent visit for ALL patients.

Scenario 3
I run an auction site. Show me the highest prices that each item has ever sold for.

See the trend? The goal is show me a specific value for every item. For this example, we will use the patient visit scenario. First let's setup the table and some sample data.

create table patient_visits_ADSFG
     (patient_visit_id        int identity primary key clustered
     ,patient_name            varchar(20) not null
     ,visit_date              datetime not null
     ,diagnosis_code          int null
     ,visit_cost              money null
     )
GO
;with a as
     (select 1 as n union all select 1)
     ,b as (select 1 as n from a as a1 ,a as a2)
     ,c as (select 1 as n from b as b1 ,b as b2)
     ,d as (select 1 as n from c as c1 ,c as c2)
     ,e as (select 1 as n from d as d1 ,d as d2)
     ,ns as (select row_number() over (order by n) as num from e)
insert into patient_visits_ADSFG(patient_name,visit_date)
select distinct a.b,dateadd(d, -(num*rand(checksum(newid()))),getdate())
from ns
cross join (select 11 a,'Bob' b
     union select 2,'Brandon'
     union select 3.5,'Jim'
     union select 40,'Tim'
     union select 23,'Jason'

     ) a
GO
update patient_visits_ADSFG
set diagnosis_Code = rand(patient_visit_id)
,visit_cost=patient_visit_id+rand(patient_visit_id)
GO
create index idx_1 on patient_visits_ADSFG (patient_name,visit_date)
GO

Our goal is to show the most recent visit for each patient. Now we know that we want to get back 5 records because we have 5 patients. Below are two SQL statements. Let's run them and compare the execution plans.

select patient_name,visit_date,diagnosis_code,visit_cost
from patient_visits_ADSFG a1
where visit_date = (select max(visit_date) 
                    from patient_visits_ADSFG 
                    where patient_name = a1.patient_name)

;with max_patients as
(select patient_name,visit_date,diagnosis_code,visit_cost
     ,row_number() over (partition by patient_name 
                         order by visit_date desc) rn
 from patient_visits_ADSFG)
select patient_name,visit_date,diagnosis_code,visit_cost
from max_patients
where rn=1

Here are the execution plans for the above two queries.

As you can see, the execution plans indicate that the first query takes 4% of the time while the second took 96%. While both queries ran rather fast against the 163K records that the above sample generated, the first being 24 times faster is significant. Imagine how this will scale in large environments where tables contain millions of records.

Why the difference?
The reason for the difference is simple. The first query does an index scan to find the largest date value for each patient. The second query does an index scan and then for each patient, orders EVERY record on the visit_date descending only to filter out every value but the first.

What are instances?

2009-03-27T20:01:00Z

Any installation of SQL Server is an instance. There are two types of instances. Default instances and Named instances. Any one host computer may have many named instances of SQL Server but only one Default instance can exist.

The Default instance of SQL Server does NOT have a name. It is not named the default instance, it just is the default instance. The default instance is accessed by entering in the machine name into the connection.

Named instances are named and must be accessed with the instance name. The instance name could be a reference to the version of SQL Server, or even the particular application that it is being used for. Named instances are access by entering the machine name and the instance name into the connection information.

For example, if you have an existing SQL Server running SQL Server 2005 as the default instance, and you need to install SQL Server 2008, you could name the instance SQL2008. You would then connect to the SQL 2008 instance by entering "SERVERNAME\SQL2008" as the server in the connection information.

It is also not unreasonable to install multiple instances of the same version of SQL Server.

Reasons for installing multiple instances can include, but is not limited to:

Needing to lock down a set of databases.
Needing to run different global server options..
A particular application needs to run on a later SQL Service Pack and other applications are not compatible.

Instances are a great feature of SQL Server and can help you customize your environment to ensure maximum compatibility and flexibility. But be aware of the downside of overloading instances. The SQL Server Engine itself consumes system resources to manage your databases. If you add multiple Instances, then you will have multiple Master, MSDB and Model databases. Planning for disaster recovery becomes more complex. And since each Instance it it's own installation of SQL Server, each intance must be patched independant of others (even though most patches will allow you to select one or more instance to apply to).

When ordering in a view doesn't work!

2009-03-15T04:27:00Z

If I've seen it one time, I've seen it a hundred times. People trying to sort views by selecting 100 PERCENT in a view with an order by. Because after all, if you try to use an order by statement, without a TOP statement, you will get the following error.

Msg 1033, Level 15, State 1, Procedure vw_CF7D3A8E67D749B38ED6E6F579AB6FF3_3, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

So how do you solve it? You add TOP 100 PERCENT right? Wrong. It doesn't work, and I'll show you why.

The first thing we will do is create a table to test with. If the name looks strange, that's because I want to do my best to ensure that there won't be a name conflct with any tables in any database you may run this in.

create table CF7D3A8E67D749B38ED6E6F579AB6FF3
     (a   int identity primary key clustered
     ,b   int not null
     ,c   char(1) not null
     )
go

Now we will populate it with seed data and select it without an order by statement.

insert into CF7D3A8E67D749B38ED6E6F579AB6FF3 (b,c) values (1,'a')
insert into CF7D3A8E67D749B38ED6E6F579AB6FF3 (b,c) values (2,'a')
insert into CF7D3A8E67D749B38ED6E6F579AB6FF3 (b,c) values (3,'a')
insert into CF7D3A8E67D749B38ED6E6F579AB6FF3 (b,c) values (1,'b')
insert into CF7D3A8E67D749B38ED6E6F579AB6FF3 (b,c) values (2,'b')
insert into CF7D3A8E67D749B38ED6E6F579AB6FF3 (b,c) values (3,'b')
insert into CF7D3A8E67D749B38ED6E6F579AB6FF3 (b,c) values (1,'c')
insert into CF7D3A8E67D749B38ED6E6F579AB6FF3 (b,c) values (2,'c')
insert into CF7D3A8E67D749B38ED6E6F579AB6FF3 (b,c) values (3,'c')
go
select * from CF7D3A8E67D749B38ED6E6F579AB6FF3

As you can see from the results below, with no other alterations or indexes on the table, the results will be ordered by the clustered index (a).

Now we will create two views. The first one will select 100 PERCENT of the records and the second one will select 99 PERCENT. The result here will be that the second view will still select all 9 records. The point is just to show the difference in sorting.

create view vw_CF7D3A8E67D749B38ED6E6F579AB6FF3_1
as
select top (100) percent * from CF7D3A8E67D749B38ED6E6F579AB6FF3
order by b
go
create view vw_CF7D3A8E67D749B38ED6E6F579AB6FF3_2
as
select top (99) percent * from CF7D3A8E67D749B38ED6E6F579AB6FF3
order by b
go

Now there are two ways to show you that the first view above is invalid. The first is to look at the results of the two statements.

select * from vw_CF7D3A8E67D749B38ED6E6F579AB6FF3_1
go
select * from vw_CF7D3A8E67D749B38ED6E6F579AB6FF3_2

The first result set you will see below is the result of the first view. The one with TOP 100 PERCENT. As you can see, it is still sorted by the "A" column, the clustered index, whereas the second result is actually sorted by column B as we told it to?

But why is that? We told it to sort by column B. Let's look at the execution plans for the two selects.

As you can see, there is no SORT happening in the first execution plan. The reason is that SQL Server determines that it doesn't need to sort the records in order to get the TOP 100 PERCENT of records because ALL records are being selected. The only time when SQL Server will sort records in a view, is when it is necessary to do so because not all records will be returned. And even though in THIS case "TOP 99 PERCENT" will return all records, that won't always be true so SQL Server will sort the records to be sure.

There is however a workaround. You can use a Table Valued Function that sorts the records.

create function dbo.fn_CF7D3A8E67D749B38ED6E6F579AB6FF3()
returns @Res table 
     (i int identity
     ,a int 
     ,b int
     ,c char(1)
     )
as
begin
insert into @Res(a,b,c)
select a,b,c from CF7D3A8E67D749B38ED6E6F579AB6FF3 
order by b
return
end
go

Now when we select the columns from the function.

select a,b,c from dbo.fn_CF7D3A8E67D749B38ED6E6F579AB6FF3()

You will see that the results are sorted just like we wanted.

While this simple example shows a workaround for sorting in views, it is not always the right thing to do. Selecting large data sets can hinder performance due to the amound of time it takes to copy the data into the table variable. But when all else fails, it will get the job done!

To download all of the SQL in this post, click here.