Wednesday, November 26, 2008

The Database Transaction Log - Part 2: How the Transaction Log Works

The transaction log files are different from the database data files in the way that they are used, as well as how the space is allocated inside of them.  The Log files, unlike standard data files are written to sequentially.  For this reason, it is often best for the logs to be on dedicated disks for log use only.  Since the IO is all sequential, random I/O from the data files could impact performance of logging.  The log files are subdivided internally into Virtual Log Files as shown in the below picture from the Books Online:

VLF Division

The number of VLF's in the transaction log can be found using DBCC LOGINFO.  This is an undocumented command, but it provides some very useful information regarding the transaction log for a specific database.  The following image shows the output of DBCC LOGINFO for AdventureWorks on my laptop:

image_thumb[2]

As can be seen in the image, there are 4 VLF's in the transaction log.  The FSeqNo provides the logical order of the VLF's inside of the physical log files.  The Status column provides information as to whether a specific VLF is currently being used (2) or if it is available for use (0).  FileSize and offset provide information about the actual location of the VLF inside the physical file structure, and how large the VLF actually is. The actual sizing of the VLF is important to pay attention to.  If your VLF's are to small, then you will have excessive numbers of VLF's in your log files.  If they are to large, then they won't truncate free space effectively or efficiently. 

As mentioned previously, the transaction log is a sequentially written file that is used in a round robin fashion.  The start of the log file may not be the current start of the logical log.  The following image from the Books Online shows how a single log file with four virtual log files is used by the database server.

LogUsageSingleFile

The start of the logical log in this case is at the beginning of the third VLF.  Since the log is written sequentially, the log moves from the start of the third VLF through the fourth VLF and when it reaches the end of the file, starts back over at the beginning of the log file if there is free space.  So long as the end of the logical log never gets back to the start of the logical log, the transaction log will stay the same size.

In a two file system, the files are used sequentially, and unlike the database data files which are striped.  With two transaction log files, the first file is used, and then the second file is written to following the below picture:

TransactionLogMultiFileUsage

As long as the log space is truncated, the logs can continue to be used in a round robin fashion that prevents growth from being required.  The mechanism for truncation differs depending on the recovery model selected for the database in question.  If the database is in FULL recovery,  then the log is truncated when it is backed up using the BACKUP LOG command.  In SIMPLE recovery, the log truncation occurs on checkpoint for all complete transactions.  The active portion of the transaction log should remain fairly small for SIMPLE Recovery.  Exceptions to this would be large long running transactions as covered in my previous posting.

What am I reading? : Pro SQL Server 2005 Assemblies (Robin Dewson and Julian Skinner)

I tend to work in patterns, where I occasionally get caught up by a particular topic and buy every book that I can find on the topic so I can dig deep into that topic.  SQLCLR was one of those topics earlier this year, and for all the work that I have done, I don't actually have any SQLCLR running on a production SQL Server in my environment.  There are a number of reasons for this, primarily, that the databases where I might use it, don't belong to me, they are vendor databases and I can't go play around with them.  One of the books that I bought to learn from for SQLCLR was

Pro SQL Server 2005 Assemblies (Pro) by Robin Dewson and Julian Skinner

This book overall is not bad, but there are some technical errors in some of the information that it provides.  The brief stint on Memory Management is not technically accurate, and does not discuss the differences between 32bit and 64bit SQL Servers.  This book really is programmer based and not management or DBA targeted, so this may not mean that much to most people. 

This book does a good job of introducing the structure and concepts for how to build the various SQLCLR Object types.  It also does a good job of providing some best practices for implementation that will make it easier for CLR code to be moved from the database tier in SQLCLR to a application tier, should scale out be required due to problems or growth.  The tips provided by the author actually save time, and make code reuse much easier between the application and the database.

Based on the other books on SQLCLR that I have read, I would rate this one at the top of the list.  It gives the information needed to design and code SQLCLR objects in SQL Server.  Not all of the examples are actually good candidates for CLR use in my own opinion.  Some of them can be done with TSQL in a manner that performs better than the CLR provided, but the difference in performance is fairly minimal.  It could simply be that the authors needed an example that didn't require a bunch of supporting structure behind it to keep things simple.

Tuesday, November 25, 2008

Anatomy of a Deadlock - Part Deux

This post has been moved to my new blog at SQLBlog. You can find its contents on the following link:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/11/25/anatomy-of-a-deadlock-part-deux.aspx

Saturday, November 22, 2008

PASS Summit 2008 Debriefing

I didn't blog much from PASS and it was intentional.  Since I was covering PASS sessions for Universal Thread, I didn't put time into covering them on here.  You can read about the sessions I attended on the following link:

http://www.utcoverage.com/PASS/2008

Reading about PASS Summit sessions will never match up to the experience of actually attending PASS Summit in person.  For me personally, the sessions were good, but the social networking us what really should drive people to attending events like PASS Summit.  I met and spoke with people from over a dozen different countries this week.  There were many MVP's at the summit which was simply awesome since I got to meet and hang out with the people who been teaching me SQL Server for the last 4 years.  I also got to sit on a discussion panel on Automating DBA Tasks, sponsored by Quest Software as a fill in for Buck Woody who was not able to make it at the last minute. 

If you'd like to catch up on PASS, check out the UT coverage above, but also check out the following blog post from Gail Shaw which lists some blogs to look at.  Also check out others that she missed:

drSQL (Louis Davidson)

If you aren't a member of PASS, I'd recommend that you join, it is after all free. 

Friday, November 21, 2008

SQL Heroes Contest Winners

The contest winners were announced at PASS, albeit in a very anti-climactic manner. The winners in order shown on the screen at PASS were:

Greg Galloway (with Darren Gosbell and John Welch) http://www.codeplex.com/bidshelper

Jonathan Kehayias http://www.codeplex.com/ExtendedEventManager

John Calvin Welch http://www.codeplex.com/ssisUnit

Ignat Andrei http://www.codeplex.com/CDCHelper/

Jason Massie http://www.codeplex.com/QPee



EDIT:

I was contacted regarding this list, and while I saw that the SQL Heroes blog posted a list of winners on 12/02/2008, I didn't actually notice that it has a different list of winners on it:

http://blogs.msdn.com/sqlheroes/archive/2008/12/02/and-the-winners-are.aspx

So why the difference? I am not really sure. My list came from Brent Ozar who had the same problem I did at PASS Summit. They flashed the list to fast for me to be able to copy it all down. His blog post pointed me to Adam Machanic's blog, who I trust, so I felt safe reposting the same information on here, but apparently there was a discrepancy. See the actual SQL Heroes blog to find the correct list which is:

  • BIDS Helper: Greg Galloway, Darren Gosbell, John Calvin Welch
  • SQL 2008 Extended Events Manager: Jonathan Matthew Kehayias
  • ssisUnit: John Calvin Welch
  • SqlMonitoring Tool: Robert Hartskeerl
  • Allocation SQL Server Management Studio Add-in: Daniel Gould

Wednesday, November 19, 2008

Can a DBA function without rights to the Server OS?

I see posts often regarding questions for what rights should be granted and to whom with regards to SQL server.  On this post I am going to address what rights should a DBA have to the Server itself based on the following question recently from the MSDN Forums:

What is a DBA/sa prevented from doing if not a local admin on the SQL Server?

If you read the above post, you will see that my answer is different from start to finish on the thread.  At first I would have said that a DBA needs to be a Local Administrator on their server, but this really doesn't stand up to the whole idea of least necessary permissions, that as a DBA I enforce myself.  I happen to be a Local Administrator on my servers at my job, but that really probably is overkill, and could be reduced.  So what rights would a DBA need to a SQL Server at the OS level?

This is determined and or dictated by the definition of the role of a SQL Server DBA.  As a SQL Server DBA, there are certain things that I expect to be able to see and or do, and that I would argue belong within my realm of responsibility.  A short list of these tasks would be:

  1. Monitoring Performance Counters
  2. Managing Database Files
  3. Managing SQL Server Updates and Service Pack Installations
  4. Viewing System Event Logs
  5. View active Processes on the Server through PerfMon or TaskManager
  6. Start and Stop SQL Services
  7. Monitor Available Disk Space

Now arguably, the easiest way to accomplish the above is to make the DBA a member of the Local Administrators group on the SQL Server, and I would never argue against this practice, it is after all the DBA's job on the line if the server goes down from a mistake they made as a local administrator by goofing with something they shouldn't have.  I personally like having the rope to hang myself, but I can also use that same rope to climb out of a valley in the event of an emergency as well.  I personally know my limits, and I know when to involve my server team members with looking at a problem.  This may or may not be due to my past experience as an NT4 and Windows Server 2000 Administrator, it was something close to 8 years ago, but I know where to draw the line of responsibility.

So where do you sit on this subject?  I am interested to know if you are a DBA that doesn't have Administrator Access to the Windows Server, how you created permissions to do the above.  How would one go about granting the individual rights required to be a DBA without being a Local Administrator?  I can only figure out how to grant a subset of the above, but as I said previously, I am not a Windows Administrator, MCSE, or any other sort of subject matter expert when it comes to security outside of the SQL Server database engine itself.

Tuesday, November 18, 2008

Off to PASS

By the time this actually gets published live to everyone, I will be on a plane heading for Seattle. Ah the wonders of the Internet, and all these cool new features I find on my blog like the ability to write something today and have it published in the future. I probably won't be blogging heavily from PASS as I am writing reviews for Universal Thread which will be published online at:

http://www.utcoverage.com/PASS/2008/

If something catches my eye, I might be inclined to double up my work, and document it here as well, if I find that I can dig into it a bit more and provide some example code and information not provided on the thread above. I might get Part 2 of my Transaction Log series done on the flight which would allow me to publish that. We'll see.

Monday, November 17, 2008

Now on Twitter

A comment recently asked if I am on Twitter.  Well I wasn't but I am now.  Not sure what exactly I plan to do with it, but I can post short blurbs on what I am doing at the moment throughout the day I guess.

http://www.twitter.com/jmkehayias

I try to keep my online information business oriented and professional, so don't expect a whole lot of random rambling on there, but we'll see where it goes.  Not much room to do more than plug some small random stuff, perhaps using it to plug good posts on other peoples blogs rather than re-blogging it here......

What am I reading? : Inside Microsoft SQL Server(TM) 2005: The Storage Engine (Solid Quality Learning)

Since I have a few excellent books that I have already read, I figured I would post some reviews of what I am not currently reading, but I have previously read, and I use regularly for reference as I am working or answering questions online. 

This book is another must have for any production support DBA.  It is the Chilton's Manual/Haynes Manual for SQL Server.  I have this book for SQL 2000 and 2005, and I pre-ordered the SQL 2008 version the day that it was available for pre-order on Amazon.  This book actually has some really good reviews on Amazon already:

Inside Microsoft SQL Server(TM) 2005: The Storage Engine (Solid Quality Learning) by Kalen Delaney

This book is really intended for those who want/need to know the how and why for the way that the Storage Engine works in SQL Server 2005.  It is really is a must for anyone building or architecting an enterprise database solution or looking to upgrade/install SQL Server 2005.  It is not a beginners book at all, in fact I wouldn't recommend that a beginner start off with this book due to the depth of information contained in the book.  It would be very easy to get lost in the details of IAM, GAM, and SGAM allocations and never actually learn how to use SQL Server.  The table of contents for the book is:

Chapter 1 Installing and Upgrading to SQL Server 2005

Chapter 2 SQL Server 2005 Architecture

Chapter 3 SQL Server 2005 Configuration

Chapter 4 Databases and Database Files

Chapter 5 Logging and Recovery

Chapter 6 Tables

Chapter 7 Index Internals and Management

Chapter 8 Locking and Concurrency

My favorite chapters have to be Chapter 5 and Chapter 8.  These are two of the most misunderstood concepts in SQL Server from working on the forums.  The level of detail included by Kalen in these chapters is excellent.  Expect the SQL Server 2008 version of this book to be even better as Kalen is joined by industry expert Paul Randal and his wife Kimberly Tripp as well as Conor Cunningham from Microsoft.

Additional Resources:

Inside Microsoft SQL Server 2000 by Kalen Delaney
Microsoft® SQL Server® 2008 Internals (Pro - Developer) by Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, and Conor Cunningham (Paperback - Feb 18, 2009)

Thursday, November 13, 2008

The Database Transaction Log - Part 1: Managing Size

This post will be the start of a series on the Database Transaction Log.  This started out as a single posting, but the topic is vast that there is no way to properly cover it in a single post.  I could probably write a mini pamphlet on the transaction log in SQL Server the topic is just that big.  I plan to focus on common problems that I consistently see on the MSDN Forums regarding the transaction log, as well as how to prevent/correct them.

The transaction log in SQL Server is one of the most important parts of a SQL Server database, as well as one of the most common generators of problems I see online.  If you don't believe me, do a Google, MSN, or Yahoo search for 'transaction log full', and you will find article after article and question after question dealing with the subject.  Worst yet are the stories of a deleted transaction log:

http://www.sqlservercentral.com/articles/Disaster+Recovery/63311/
https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2727860&SiteID=1
http://bytes.com/forum/thread543465.html

To start with let me first say that you should never, ever, never, under any circumstances delete your database log file.  This is, as Jens Suessmeyer put it, "the transactional heart of the database." 

In a managed database server the database data/log files should only grow when specified by a Database Administrator.  This is not to say that you should turn AutoGrowth OFF on the databases in your environment.  I prefer that this option is left ON as a sort of insurance policy for your database in the event that the space available drops fast enough that you can't react in time to prevent a problem.  It should however, be configured to grow by a fixed size rather than a percentage.  The reason for this is that a fixed size has a predictable cost should the database have to grow automatically whereas a percentage size will be more and more expensive the larger the database grows to. 

I previously blogged about the How to Automate Common DBA Tasks series that I posted on the MSDN Forums Example Site.  One of the articles included in that series is a Data File Free Space monitoring script.  This can be used to monitor the available free space in the log files, and provide early notification to an administrator that they need to grow the file.  By manually managing the size of the log file, an administrator will know if there is a problem almost immediately.  The transaction log on an established database should very rarely have to be grown once properly sized.

So what exactly will cause the log file to fill up and need to be grown?  There are a number of things that can cause this:

  1. Full Recovery Model without Log Backups
  2. Long running transaction
  3. Uncommitted transaction
  4. Rebuilding Indexes
  5. Loading Bulk Data without minimizing logging.

The first item listed is the primary reason that log files get to be oversized, and eventually consume all of the available disk space on the server.  I have yet to figure out why or how, but almost every time that someone posts a problem with an oversized transaction log, the database is in FULL Recovery, and the only backups being performed are nightly full backups.  If you are not backing up the transaction log between full backups, then the database should be in SIMPLE recovery.  The reason for this is that the transaction log is not truncated in FULL recovery except by log backups using the BACKUP LOG command, and specifying WITH TRUNCATE_ONLY doesn't make sense.  If you aren't going to backup the logged data for point in time recovery purposes, then there is no reason to not have it auto truncate on checkpoint.

Long running transactions most often associated with a growing or oversized transaction log are generally data purging processes that are running as a single transaction like the following:

DELETE 
FROM MYTABLE
WHERE MYCOL
< @Criteria

Purging data like this will be heavily logged, even on databases in SIMPLE recovery.  The reason being that there is no commit between the start and commit, so if one million rows are being deleted, then the log has to hold all one million deletes to be able to perform a rollback of the operation.  The appropriate way to purge data like this was provided by fellow forum member Adam Haines in response to numerous forums postings:


DECLARE @BatchSize INT,
@Criteria DATETIME
SET
@BatchSize = 1000
SET @Criteria = '1/1/2005'

WHILE EXISTS(SELECT 1 FROM MYTABLE WHERE MYCOL < @Criteria)
BEGIN
DELETE TOP
(@BatchSize)
FROM MYTABLE
WHERE MYCOL < @Criteria
END

Another solution to this problem was provided by Denis the SQL Menace is:


DECLARE @BatchSize INT,
@Criteria
DATETIME,
@RowCount
INT
SET @BatchSize
= 1000
SET @Criteria = '20050101'
SET @RowCount = 1000

SET ROWCOUNT @BatchSize

WHILE @RowCount
> 0
BEGIN
DELETE
FROM MYTABLE
WHERE MYCOL
< @Criteria

SELECT @RowCount = @@rowcount
END

SET ROWCOUNT 0

Both of these solve the problem by working in smaller implicit transactions which will perform much better, as well as control the size of the transaction log. 


Uncommitted/Open transactions are problematic beyond just causing transaction log growth.  An open transaction will also cause excessive blocking in the database which will also impact the application users.  To find open transactions in a database, you can use DBCC OPENTRAN, which will return the active transactions in the current database.  To demonstrate this, run the following statement in one window:


use tempdb
go
create table mytable
(rowid int identity primary key)

begin transaction
insert into mytable default values

Then open a new window and run:


dbcc opentran

The output from DBCC OPENTRAN will show the open transaction and offending SPID in the database as follows:



Transaction information for database 'tempdb'.

Oldest active transaction:
    SPID (server process ID): 55
    UID (user ID) : -1
    Name          : user_transaction
    LSN           : (20:44:214)
    Start time    : Nov 13 2008 10:35:09:780PM
    SID           : 0x01050000000000051500000064116832e36ccd723422e75bba640000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


To investigate this further, you can use DBCC INPUTBUFFER(spid#) to get the last command run on the offending SPID.  From there, you can determine what action you want to take, whether to kill the offending SPID and cause it to rollback, or maybe troubleshoot further to identify what caused the transaction to be left open.


Rebuilding Indexes and bulk loading data should both be done in SIMPLE or BULK_LOGGED recovery.  The reason for this is to minimize the logging that is done, but you can't eliminate it completely.  In my experience, rebuilding indexes can require a transaction log to be larger than the size of your largest index.  This is where sizing your log file is important as a part of future capacity planning, and should be done considering how large the database might be in the future.


If you are reading this because you are already in trouble, there are a number of ways to deal with a oversized transaction log, and none of them necessitates deleting the file from the file system.  If you get yourself to the point that your server is completely out of disk space, there is no network path where you can backup your log to, and the log is in the hundreds of GB in size, then the best recommendation is to put the database into Simple recovery, which will truncate the active portion of the log and allow it to be shrunk.  I would highly caution and recommend that a new Full backup of your database be taken immediately after performing such an operation.


Look for further postings on the Transaction Log to come.


References:
http://support.microsoft.com/kb/873235
http://support.microsoft.com/kb/317375/

Tuesday, November 11, 2008

New Wrapper installer for SQL Express 2008

If you've ever installed SQL Express 2008, the work required to install the prerequisites before you can actually install SQL Express 2008 was almost maddening.  Microsoft has made this much easier by releasing a new wrapper for the installation that will automatically download and install the dependencies for you, making the process much easier than before.  You can read about this on the SQL Express blogs on MSDN.

http://blogs.msdn.com/sqlexpress/archive/2008/11/05/announcing-the-sql-express-2008-installation-package.aspx

What am I reading? : SQL Server 2005 Practical Troubleshooting: The Database Engine (Ken Henderson)

I don't know if this affects other people, but I often have problems deciding which books I want to buy and or read.  So in an attempt to assist others in making good book choices with what may be limited funding, that I will provide a brief review of my opinion of the books that I have read and or am currently reading.  Since I have quite a stack that I have read, I figured I would start off with my favorite SQL Server 2005 Book. 

SQL Server 2005 Practical Troubleshooting: The Database Engine (SQL Server Series) by Ken Henderson (Paperback - Dec 18, 2006)

This in my opinion has to be one of the best resources for a SQL Server DBA when all hell breaks loose and there are serious problems with a SQL Server.  If you don't have a copy of it yet, I would highly recommend it.  Unlike most of the books that are out there, this book was written by the developers at Microsoft for SQL Server, and edited by the late Ken Henderson, whose own contributions to the SQL Community over the years was second to none.  This book gets down into the nitty gritty of SQL Server, how it functions and why, and how to resolve and troubleshoot some of the most complex problems with the Database Engine.

What is really good about this book is that it provides in depth information and details that could only come from the internal development staff at Microsoft.  The book is divided into specific problem types and details to include stack dump data is included in the book text to demonstrate the problem being explained.

Chapter 1 Waiting and Blocking Issues

Chapter 2 Data Corruption and Recovery Issues

Chapter 3 Memory Issues

Chapter 4 Procedure Cache Issues

Chapter 5 Query Processor Issues

Chapter 6 Server Crashes and Other Critical Failures

Chapter 7 Service Broker Issues

Chapter 8 SQLOS and Scheduling Issues

Chapter 9 Tempdb Issues

Chapter 10 Clustering Issues

One problem reported with the book is that Amazon lists it as coming with a CD included.  Mine didn't, and I am not concerned about it, but I'll provide the warning that you won't get a CD with your book if you order it.  Additionally, expect that it will take 3-4 weeks to get delivery of the book if you do order it online.  I am not sure why it took so long to arrive, but it was definitely worth the wait. 

Monday, November 10, 2008

How do I become a SQL MVP?

I've been asked this both publicly and privately over the last month since I received the award, and I provided some information on a previous blog post Congratulations 2009 Microsoft MVP.  The real answer is that there isn't a magic formula that can be followed to become a MVP in any technology, let alone SQL Server.  The key requirement to becoming an MVP for any technology is a demonstrated dedication to supporting the community over the past year.  How one actually goes about supporting the community differs person to person and case by case. 

One thing to keep in mind is that quality is as important if not more important than quantity.  Posting responses to forums questions consistently but never actually answering the question at hand doesn't support the community very much.  Writing in a blog about personal information might build your blog post count, but it isn't centered towards a specific technology.  Being a member of a local user group and attending meetings but never presenting or being involved is good for personal learning, but not expanding the knowledge of the community. 

One of the requirements is that you have to be nominated by an existing MVP, or a staff member at Microsoft.  This entails that you have or are doing something that gets noticed like answering questions efficiently and effectively online, while providing information to explain and educate the requestor as well as anyone who may find the post by searching later on.  Speaking at local events and writing articles that get published in print or online are other ways to get noticed.  At least for SQL Server, many of the speakers at local events are current or former, yes I said former, MVP's.

So why did I say former?  The MVP award is only for one year, and if you stop contributing, you can be assured that you won't receive the award the following year.  Even if you do continue to contribute, it isn't guaranteed, see statement above about no magic recipe.  Each quarter the current MVP's whose award is expiring, and new candidates for MVP are reviewed by a team to determine who will receive the award.

Hopefully that answers the question for anyone who is curious, while not answering the question since there isn't a definitive answer to this question.  Personally the first email I got about being nominated caught me off guard.  I certainly don't count myself amongst the ranks of MVP's like Paul Randal, Kimberly Tripp, Kalen Delaney, or the long list of other subject matter experts whose work is well above anything I have done.

Friday, November 7, 2008

Are you safe without your Keys?

I am not talking about your house keys, or your car keys, I am talking about your SQL Server Keys.  SQL Server 2005 allows you to use keys for protecting objects and data in the server and databases.  It is very likely that if you are doing encryption in SQL Server that you already know that you have keys in SQL, but if you have never touched encryption, you might now know that, you too, have at least one key in SQL, the Service Master Key.  For those not familiar with this, the Service Master Key is the root of encryption key hierarchy in SQL Server 2005, it is by default used to encrypt Database Master Keys, linked server security information, and any credentials created using CREATE CREDENTIAL.  If you don't use any of the above, then read no further, you are probably fine.

Personally, I learned about keys, certificates, and encryption while studying for my MCITP Database Administrator exams at the beginning of this year, and that was enough to add a backup of the SMK into a password protected file to my installation procedures for each SQL Server Instance I have.  I personally have never needed to use the backup to date.  So what sparked this blog posting?  As usual, a post on the MSDN Forums, where a backup of a database using encryption was restored to a different server, and the poster didn't know the password to the database master key.  After goofing the initial answer on it, I had to do some investigating into what could be done in the case that the password to encrypt a DbMK is lost, and the answer isn't all that complex, and it is included in the post referenced above. 

However, it is much easier to just avoid the situation completely and have available backups of the SMK, as well as all the other keys in use in your database server. These can easily be done with DDL commands:

BACKUP SERVICE MASTER KEY (Transact-SQL)
RESTORE SERVICE MASTER KEY (Transact-SQL)

BACKUP MASTER KEY (Transact-SQL)
RESTORE MASTER KEY (Transact-SQL)

BACKUP CERTIFICATE (Transact-SQL)
CREATE CERTIFICATE (Transact-SQL)

One of the best references online is Laurentiu Cristophors blog on MSDN for security and cryptography in SQL Server.  Laurentiu is a frequent answerer in the SQL Security forum on MSDN, and has wealth of information available about this subject.  His blog is definitely a recommended read and is on my personal blog roll.

Since this is such a vast subject, I plan on putting some time into it in the near future, and writing some additional information on the subject.  However, in the mean time, backup those keys and certificates.

EDIT:  One thing I realized while thinking about his blog is I do have two different Certificates implemented in my servers, one for mirroring between two servers not on a domain, and one for a certificate user to provide permissions for a dynamic SQL search procedure that uses parameterized dynamic SQL to perform optimized searching in one of our databases, since application users are required to use stored procedures and have no table level access.  The certificate user has no associated login and is used WITH EXECUTE AS to provide the needed table level SELECT rights to run dynamic SQL code in the stored procedure.  The calling user only has execute rights on the stored procedure but can still make use of its dynamic code due to this type of impersonation.  I do have backups of both of these certificates, as created when they were created.

If you want more information about using Certificates to grant elevated rights with a Certificate User look at Erland Sommarskogs article Giving Permissions through Stored Procedures.

Picking Random Rows from A Table

Being that I work for a number of restaurant chains, I get some interesting requests/requirements for extracting data that has been collected through various marketing campaigns or contests. A common request in the past has been to randomly pick a winner or randomly pick a list of winners from a particular set of data. This seems like it should be pretty easy to do, but I was never able to figure out how to pick a truly random set of records without looping. Until today, and the solution is really simple:


USE [tempdb]
GO
CREATE TABLE randomresults
(id INT IDENTITY PRIMARY KEY)
GO
INSERT INTO randomresults
DEFAULT VALUES
GO 100000 -- execute batch 100000 times
-- Return rows in a random order
SELECT TOP 10 id
FROM randomresults
ORDER BY checksum(NEWID())
GO


It doesn't matter how many times you run this, it will always return a completely random resultset.

Thursday, November 6, 2008

Are your Disks Aligned Properly?

I am always on the lookout for new blogs to watch and learn from.  Not long ago, Kevin Kline wrote a very intriguing blog post:

How to Improve Application and Database Performance up to 40% in One Easy Step

I read it, but I didn't get a lot of time to dig into it until recently.  I finally got around to digging into the topic more, and I also spoke to one of our server admin's about it.  Doing some research we found that our entire environment is running on misaligned partitions.  I found an additional resource blog post that has an excellent slide deck on the MSDN blogs by a guy name Jimmy May:

Disk Partition Alignment (Sector Alignment) for SQL Server: Part 1: Slide Deck

I am working on doing some testing of this by rebuilding a disk completely to see how it affects SQLIO benchmarks before and after.  I'll post results later on, but this topic is definitely an interesting subject.

More to come........

Tuesday, November 4, 2008

Automating Common DBA Tasks

A few weeks ago I did a live meeting presentation for the PASS Database Administrators Special Interests Group (DBA SIG) on Automating Common DBA Tasks.  I spent the last few weeks trying to figure out how to put the source code from my own processes online for others to view/use.  One problem I encountered is that I never designed this to be used by anyone other than myself, so the code wasn't really friendly to anyone but me.  I also did a number of things that are environment specific to my SQL Server environment, and were overly complex to try to explain online the how and or why behind them.  So in the end I reworked a majority of my own code into what I hope is an easy to follow, and easy to implement article series that I put up on the MSDN Forums Example site we use for the SQL Server Forums.

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=AutomatedDBA

If you happen to have problems with any of the scripts, please let me know by leaving me a comment or sending me an email, and I will correct the problem immediately.