Category Archives: SQL Server

Don’t Mix DDL and DML

I sat through an absolutely fantastic presentation on query optimization a couple of weeks back.  The presenter did a fine job of covering many of the basics like SET NOCOUNT ON, minimize cursor use, avoid explicit or implicit functions in the WHERE clause and why not to use the sp_ prefix when naming custom stored procedures.  He also touched upon practices which are critical aspects of query optimization, yet, in my opinion, few put into practice. Specifically, he talked about the need for benchmarks and how to establish them.  He also demonstrated how to conduct sufficient testing.  Then there were the more advanced topics like table fragmentation, associated DBCC commands, Statistics IO, Fill Factor and how procedure cache works.  He also shared plenty of TSQL samples and how they will (or will not) use a given index.  For example, did you know the LIKE clause can use indexes if the pattern starts with a character string such as WHERE lname LIKE ‘W%’ but LIKE cannot use an index if the pattern starts with a leading wildcard, such as WHERE lname LIKE ‘%wold’?  Again, excellent presentation and did I mention it was wrapped up in under an hour? 

I typically roll the dice with these types of presentation knowing they may turn out to be a refresher course, way over my head, or worse, an absolute bore.  This one was worth sitting through as I picked up the following nugget: Mixing DDL and DML Operations Can Cause a Recompile.

What does this mean?  DDL (Data Definition Language) and DML (Data Manipulation Language) almost always exists in our stored procedures.  Examples of DDL are our DECLARE variable and CREATE TABLE statements.  DML, on the other hand, includes our SET, SELECT and INSERT to name a few.  A key cause of stored procedure recompiles is the interleaving of DML and DDL statements — especially DDL following DML statements.  In other words, according to Microsoft best practices, place all of your DDL statements at the top of your stored procedures and then do your actual query work. Now, are recompiles always going to happen if you have interleaved DDL and DML?  Nope, not in all case, but you should do everything possible to prevent recompiles (even if it is super easy.)

Coping with Windows Auth

There are a few primary reasons why Windows Authentication should be considered a best practice. Since passwords aren’t visibly exposed in configuration files and credentials are not sent over the network, your systems tend to be more secure.  Additionally, password management (expiration periods, minimum lengths and account lockout after multiple invalid logon requests) becomes a heck of a lot easier. Considering the benefits, I have no issues with applications accessing SQL Server using Integrated Security, however, I think there is a big different between application access and developer access. 

Really quickly, you can setup SQL Server to run in one of two security modes: Windows Authentication or Mixed Mode.  Mixed Mode is exactly as it implies and allows users to connect using Windows NT Authentication or using SQL Server Authentication.  I’m a fan of Mixed Mode which allows for application accounts to reap the benefits of Integrated Security while allowing considerably easier SQL Auth access for developers, support personnel, etc. 

Please consider the following scenarios: 

  1. An instance of SQL Server is setup in the Development Environment and it isn’t running in Mixed Mode.  This implies that one needs to be a member of the appropriate domain and have appropriate permissions in order to access the SQL Server.  Not a big deal, right?  Well, I would agree if I were a developer working onsite and the Dev Network was readily available to me. But I happen to work remotely on occasion and there isn’t an entrance point into this particular domain through VPN.  Boy, SQL Authentication sure would come in handy in this case. 
  2. Let’s say your environments (perhaps Dev, QA and Production) are hosted in separate domains.  It would be painful to switch between domains in order to access each SQL Servers, wouldn’t it?  (I know what you are thinking, “Why would a developer need to access QA or Production?”  Special assignment.  Let’s leave it at that.)

Fortunately, there’s a workaround.  If you are me, you beg and plead and moan and request that security mode be changed (not that that’s easy.) OR you discover an even better solution in the appropriately named RunAs command which allows a user to run specific tools and programs with different permissions than the user’s current logon provides.  

The following are a few of  my favorite commands which I’ve wrapped up neatly in their own .cmd file for quick execution (you will need to update the domain and user values accordingly):

  • runas /user:domain\user “C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\ssmsee.exe”
  • runas /user:domain\user “C:\WINDOWS\system32\mmc.exe /s \”C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC\”"
  • runas /user:domain\user isqlw

This tip actually came from a wise, remote developer working in Canada who was confronted with Scenario 1 above.  Pretty neat, eh?

DBTalk – Avoid Functions in the WHERE Clause

Functions like “getdate()” and convert(varchar(10), MyDateColumn,101) commonly show up in the WHERE clause of queries.  Here’s an example:

select   c.LastName
from     Customer c with (nolock)
where    c.LastUpdated < getdate()

You might think “no big deal.” It’s just a function, but for sql server, it almost always turns a deterministic query into a non-deterministic one.  The difference?  Index usage.

Most of the time, sql server will ignore any index on a column that is used in a WHERE clause expression containing a function, turning an index seek into an index or table scan, with the obvious performance penalty.

So instead of the above query, replace it with:

declare  @CurrentDate datetime 
select   @CurrentDate = getdate()   

select   c.LastName 
from     Customer c with (nolock) 
where    c.LastUpdated < @CurrentDate

The function is gone and sql server will use an index on LastUpdated if it exists.

The same holds true for this example:

select   c.LastName 
from     Customer c with (nolock) 
where    c.LastUpdated < dateAdd(day, -1, getdate()) 
becomes:

declare  @CurrentDate datetime
select
@CurrentDate = dateAdd(day, -1, getdate())select   c.LastName
from     Customer c with (nolock)
where    c.LastUpdated < @CurrentDate

Sometimes there is simply no workaround for a function in the WHERE clause but, knowing their impact, make every effort to eliminate them. 

DBTalk

Over the past 6 months, I’ve subscribed to the “DBTalk” distribution list at work and I’ve really been enjoying it.  Here’s are my top 2 reasons:

1. Though I know there are many .NET coders who also happen to be very capable database designers and developers, I believe that the many of us still have a lot to learn.  I would say that we all know at least the basics, but there’s an awful lot of DB knowledge that has eludes us over the years.  If nothing else, the “DBTalk” distribution list has proven to be a non-intrusive way to educate an entire group of developers on SQL Server Best Practices and a few Tips & Tricks.

2. The moderator does a really nice job of getting everyone involved.  Those of us who frequent technical blogs know this isn’t always easy — especially when your audience isn’t necessarily comfortable with the topic.  Fortunately, the moderate provides information on varying topics for all levels and he throws out carrots all the time.  For example, he’ll present a Tip of the Day and ask if anyone has an alternative solution or he will simply submit a problem and ask for possible solutions.  It seems with most conversation at least someone on the distribution list is willing to bite.

Anyhow, with permission, I’ll be posting a number of the topics and tips which have been discussed over the past several months.  They may come all at once, in chunks or one every once in a while.  I haven’t yet decided.

What’s the Best Way to Manage a Database Queue?

This question circulates through the office about once a year.  “Um, I have Windows service which processes requests which are queued in a database table.  Everything is working great, but I would like to add a second instance of the service and I need to know the best way to ensure the services don’t step on each other.  In other words, how do I mark each row as it is being processed so it won’t be picked up again?” 

Here are the ingredients in my current ideal solution:

  1. The table must include the following columns: a Unique Identifier such as RequestID, Status, LockedBy, and LockedTime.  I also like to include RequestedTime and CompletionTime columns as I’ve found that capturing these values help when/if system stats are required.
  2. The key is to lock the current request at the time of processing.  To do so, you should use a transaction and an update lock.
  3. I think it is important to pull requests off the queue in an intelligent order.  FIFO is the best bet under most circumstances.  In this case, the RequestedTime column becomes imperative (unless your RequestID is an auto-incrementing identity column and you are comfortable ordering on it.)
  4. Finally, I like to make sure no request is left behind.  Let’s say Service A locks a request for processing and then the application server crashes.  I want the lock to eventually expire so Service B can pick up the row and save the day.

Assuming new requests are added to the queue as ‘Pending’ , one may use a routine similar to the one below to safely pull the next request off the queue:

create procedure [GetNextItemInQueue]
(
    @LockedBy varchar(75)
)
as 

set nocount on 

declare
    @Error                     int,
    @ExitCode                  int,
    @RowCount                  int,
    @PendingStatus               int,
    @MinutesBeforeBreakLock       int,
    @RequestId                   int

select
    @Error                      = 0,
    @ExitCode                   = 0,
    @RowCount                   = 0,
    @PendingStatus                = 1,
    @MinutesBeforeBreakLock        = 30 

begin tran ProcessQueue 

select top 1 @RequestId = RequestId
from Queue with (updlock)
where Status = @PendingStatus
and (LockedTime IS NULL OR
DateDiff(mi, LockedTime, GetDate()) >= @MinutesBeforeBreakLock)
order by RequestedTime Asc 

select @RowCount = @@RowCount, @Error = @@Error 

if (@RowCount = 0)
    goto ExitProc 

if (@Error <> 0)
    goto ErrorHandler 

update Queue
set    LockedBy = @LockedBy,
    LockedTime = GetDate()
where RequestId = @RequestId 

select @Error = @@Error
if (@Error <> 0)
    goto ErrorHandler 

select @RequestId as RequestId 

select @Error = @@Error
if (@Error <> 0)
    goto ErrorHandler 

goto ExitProc 

ErrorHandler:
    raiserror   (@Error, 16, 1)
    if @@trancount > 0
        rollback tran ProcessQueue
    select      @ExitCode =  -100 

ExitProc:
    commit tran ProcessQueue
    return      (@ExitCode)

Once the request is processed, a database call will be made to update the row’s Status to ‘Complete’ and CompletionTime. 

Note, you may choose to change the status of each row from ‘Pending’ to ‘Processing’ when you do the initial lock.  This is perfectly fine, but I’ve found that is unnecessary as the Processing status can be determined based on the value of other columns.

Again, that’s my current preferred implementation.  Perhaps there are better solutions out there?

SQL 2005 Installation Issues … Again

I picked up a new development box the other day and I spent most of yesterday (heck, it was ALL of yesterday) getting the machine configured. Nearly all software installed without any issues except for SQL Server 2005 Developer Edition. After completing the installation, I planned to validate the installation by first checking to see that the SQL Server services were running and then launching SQL Server Management Studio to ensure I could connect to the Northwind database. The services were running, however, there wasn’t much to be found other than Configuration Tools under Programs > Microsoft SQL Server 2005.

This wasn’t the first time this installation gave me trouble. In fact, the screen shot above comes from one of my laptops which I opted not to “patch.” But since I was surely going to need SQL installed on the new machine, I didn’t have the luxury of abandoning the installation this time around.

I attempted to install a few times with no luck. I googled for help with little luck. In the end, I uninstalled every “Microsoft SQL Server…” program and then reinstalled. It worked! I assumed this attempt was successful because I had uninstalled a version of SQL Server 2005 Express along with all the other programs. A google search implied that this may be causing the issues. However, the laptop which I still haven’t patched doesn’t have the Express Edition running.

I’m not sure if I’ll ever figure this one out. Actually, I’m not sure if I want to. I know well over a dozen individuals who have successfully installed SQL 2005 without any problems and now I have botched up the installation on two separate occasions on two different machines using two different installation discs. I’m either unlucky, cursed or dumb (or a combination of the three) and I don’t care to know which…

Integrated Authentication Gotchas

We are currently going through the exercise of converting our web applications (primarily .NET web applications) from SQL Server Authentication over to Integrated Authentication.

Integrated authentication allows for SQL Server to leverage Windows NT authentication to validate SQL Server logon accounts. This allows the user to bypass the standard SQL Server logon process. With this approach, a network user can access a SQL Server database without supplying a separate logon identification or password because SQL Server obtains the user and password information from the Windows NT network security process.

Choosing integrated authentication for ASP.NET applications is a good choice because no credentials are ever stored within your connection string for your application.   Thus, security is improved.  Another reason to incorporate Integrated Authentication is to facilitate account and password policy enforcement through a domain controller.  For example, this might require passwords be recycled every 90 days.

“Integrated Authentication is not difficult to implement.”  I’ve heard this time and time again.  Actually, in an ideal scenario, this is a correct statement and the implementation requires just a few steps:

1. Set SQL Server Authentication to Mixed Mode.

2. Set identity impersonate to true and assign the username and password in the IIS.

3. Update you connection string to use one of the following variations:

    a. data source=myServer;initial catalog=myDB;Integrated Security=SSPI 
    b. server=myServer; database=myDB;Trusted_Connection=true

Easy, right?  Sure.  But what if you are converting to NT Authentication and your existing network and application(s) are not ideally setup?  In this case, there are a number of potential (read probable) gotchas. 

There are three obstacles I wish to discuss, but today I am going to focus on one of them. This issue is the most obscure and I would bet that the majority of those tasked with estimating a conversion to NT Authentication would not even consider this condition.  I’m also calling attention to this gotcha first because, in my opinion, it comes with painless and noteworthy solution.

Gotcha # 1:

Our web applications call upon a number of Windows Services.  These services should be considered legacy components.  They have been around for a while and they continue to do their job so they get little attention.  Each of these components store connection string information in the registry. 

As you have noticed, connection string formats vary based on the authentication method.

Integrated Authentication
data source=myServer;initial catalog=myDB;Integrated Security=SSPI;

SQL Server Authentication:
data source=myServer; initial catalog=myDB; user id=myUsername; password=myPassword; 

In this case, the registry held the four primary SQL Server connection parameters and the service would use the associated values to build the string on the fly.  Can you see where this is going?   Fortunately, we identified this little issue early on and we estimated time to rework the functionality, rebuild, test and redeploy the services.  This was risky since these services were rarely modified/deployed and it was going to be expensive since there were more than a dozen services which required these changes.

This was until a colleague of mine uncovered an elegant hack. [Is that an oxymoron?]  He discovered that once the Integrated Security parameter of the connection string is set to SSPI parameters such as User ID and Password are virtually ignored.  (He also found out that extra semi-colons don’t make a lick of difference either.) 

In the end, we hacked our registry entries to allow our services to build and use NT Auth compliant connection strings. 

Sample registry entries:

 

 

 

 

 

Resulting connection string:

datasource=myServer;integrated security=SSPI;initial catalog=myDB;user id=;password=;

This reduced risk and simplified our deployment to modifications to the registry through the execution of a single .reg script. 

Opening up the services and redeploying may have been the best solution, but this hack got the job done safely and effectively and on the cheap.  And we learned a bit more about how connection strings work.  Who knows?  This tip may help you out of a pinch someday.

Take Advantage of Application Name

Along with the SQL Server, Database, Username and Password, don’t forget to include the Application Name in your connection string.  It’s an optional parameter, but it can be a lifesaver. 

     Data Source=myServer;
     Initial Catalog=myDB;
     User Id=myUsername;
     Password=myPassword;
     Application Name=myApp;

Consider this example:  There are multiple .NET applications running on a single web server.  Each application shares a common SQL Server Server which is suddenly performing very poorly.   You are tasked with determine which of the applications is causing havoc.  You launch SQL Profiler and run a trace.  Since you have included the optional parameter in your connection string, you may now filter the application specific queries and troubleshoot the problem far more effectively.  If you hadn’t specified the application name, you would have some work ahead of you.  In this case, all of the queries would have had a generic app name, .NET sqlClient Data Provider, and that isn’t very helpful, is it?  

Do yourself a favor and be sure to add this practice to your coding standards…