Glenn Berry's SQL Server Performance

Kimberly Tripp has a good post up about how she likes to scan a database, looking for some obvious security vulnerabilities, using this query:

-- Check for security vulnerabilities (Kimberly Tripp)
SELECT OBJECT_NAME(object_id) AS [Procedure Name],
  CASE
      WHEN sm.definition LIKE '%EXEC (%' OR sm.definition LIKE '%EXEC(%' 
         THEN 'WARNING: code contains EXEC'
      WHEN sm.definition LIKE '%EXECUTE (%' OR sm.definition LIKE '%EXECUTE(%' 
         THEN 'WARNING: code contains EXECUTE'
  END AS [Dynamic Strings],
  CASE
      WHEN execute_as_principal_id IS NOT NULL 
         THEN N'WARNING: EXECUTE AS ' + user_name(execute_as_principal_id)
      ELSE 'Code to run as caller - check connection context'
  END AS [Execution Context Status]
FROM sys.sql_modules AS sm
ORDER BY [Procedure Name]; 

This inspired me to write some similar queries that look for other problems.

-- Look at modules that don't have SET NOCOUNT ON
SELECT OBJECT_NAME(object_id) AS [Procedure Name],
CASE
      WHEN sm.Definition NOT LIKE '%SET NOCOUNT ON%' 
          THEN 'WARNING: code does not have SET NO COUNT ON'
END AS [SET NOCOUNT ON Check]
FROM sys.sql_modules AS sm
ORDER BY [Procedure Name];

-- List modules that don't have SET NOCOUNT ON
SELECT OBJECT_NAME(object_id) AS [Procedure Name]
FROM sys.sql_modules AS sm
WHERE sm.Definition NOT LIKE '%SET NOCOUNT ON%'
ORDER BY [Procedure Name];

-- List modules that have NOLOCK hints
SELECT OBJECT_NAME(object_id) AS [Procedure Name]
FROM sys.sql_modules AS sm
WHERE sm.Definition LIKE '%NOLOCK%'
ORDER BY [Procedure Name];

-- List modules that have WITH RECOMPILE option set
SELECT OBJECT_NAME(object_id) AS [Procedure Name]
FROM sys.sql_modules AS sm
WHERE is_recompiled = 1;

-- List each module in the current database
SELECT OBJECT_NAME(sm.object_id) AS [Object Name], 
       o.[type], o.type_desc, sm.[definition]
FROM sys.sql_modules AS sm
INNER JOIN sys.objects AS o 
ON sm.object_id = o.object_id
ORDER BY o.[type], [Object Name];

I will be giving the second presentation (from 7:15-8:00) at the Boulder SQL Server User’s Group on Tuesday, July 14.  I will be giving my “Using SQL Server 2008 Database Mirroring to Become a Super Hero!”  presentation.

Here is the abstract:

Database mirroring is a valuable technology that can be used to minimize or avoid downtime due to hardware failures, with less complexity than fail-over clustering. It can also be used for other innovative uses, such as easily migrating from SQL Server 2005 to SQL Server 2008 with a sub-minute outage. Database mirroring can be used during normal scheduled maintenance for rolling updates to minimize downtime. Database mirroring works very well for migrating very large databases from one SAN to another with minimal downtime. This session will cover all of these scenarios, with practical techniques, scripts and tips to help you take advantage of database mirroring. 

This was one of the presentations that I submitted to SQL PASS, but it only got accepted as an alternate. I should not complain though, since I had two others accepted for Community Sessions.

We have been using database mirroring for nearly three years at NewsGator, with very good results. In fact, it has saved our bacon a few times when we had some major issues with our primary SAN. We used to use a combination of failover clustering and mirroring on SQL Server 2005, but when we transitioned to SQL Server 2008, we went to mirroring exclusively.

Well, I passed 70-450 today, (after passing 70-433 on Monday), so I am nearly done with the MCITP: Database Developer and Database Administrator tracks for SQL Server 2008. I just need to take 70-451, and I will be done. I am debating whether I should go back and get MCITP: 2005 Database Developer.

I saw on Twitter where Kevin Kline was passing around a 65% off discount offer for SQL Server Magazine, which is a pretty sweet deal.

Microsoft’s Stephen Rose has announced on the Springboard Series Blog that you will not be able to download Windows 7 RC after August 15. You will still be able to install it and get license keys after August 15.

I just found out today that I will be one of a small group of Microsoft MVP Bloggers who will be blogging on Amazon.com about Windows 7. I don’t have the URL for the blog yet, but rest assured, I will post it here when I get it. That should be fun!

I decided to take the 70-433 Exam this morning, cold, with no studying, just to see how I would do. It was actually easier than I expected, and I got a score of 850/1000.  Passing this exam gives me the Microsoft Certified Technology Specialist (MCTS) in SQL Server 2008 Database Development certification.

I’ll be taking the 70-450 Exam next, which is for MCITP:Database Administrator 2008. MCITP (Microsoft Certified IT Professional) is the next higher level certification, just above Technology Specialist.

I have always been a fan of getting vendor certified (even though it is not a substitute for hands-on experience and knowledge).  I have run into many developers and DBAs in my career who scoff at getting certified, but I think it is very worthwhile. I am constantly trying to inspire my students at University of Denver - University College to work on getting certified. It shows that you have initiative and care about your career.

I will be giving a presentation at the Boulder SQL Server User’s Group (BSSUG) on July 14, 2009. My presentation is “Using SQL Server 2008 Database Mirroring to Become a Super Hero!” 

Abstract
Database mirroring is a valuable technology that can be used to minimize or avoid downtime due to hardware failures, with less complexity than fail-over clustering. It can also be used for other innovative uses, such as easily migrating from SQL Server 2005 to SQL Server 2008 with a sub-minute outage. Database mirroring can be used during normal scheduled maintenance for rolling updates to minimize downtime. Database mirroring works very well for migrating very large databases from one SAN to another with minimal downtime. This session will cover all of these scenarios, with practical techniques, scripts and tips to help you take advantage of database mirroring. 

Goals
Demonstrate how to implement database mirroring in a high volume OLTP environment. 
Show tip and tricks to make deployment and management easier.
Show techniques for maximizing database mirroring performance. 

There will be far more demonstrations compared to PowerPoint slides, so it should be fun. I have never been to the Boulder user’s group meeting before, and I am looking forward to it.

PASS has published the complete list of sessions for the PASS Community Summit 2009 in November.  There are a lot of very interesting sessions scheduled, with some excellent speakers. I have to admit that I still get a kick out of seeing my name on the list. Here are my two sessions:

Tips and Tricks for Using SQL Server 2008 Integrated Full Text Search in a High Volume OLTP Environment

Dr. DMV: How to Use Dynamic Management Views to Monitor and Diagnose Performance Issues With High Volume OLTP Workloads

Microsoft has released CU4 for SQL Server 2005 SP3. This is Build 4226. They have also released CU14 for SQL Server 2005 SP2, which is Build 3328.

We have a table with a little over 4 billion rows that uses SQL Server 2008 Page data compression. With Page data compression, the table takes up 3.8TB. If I were to remove the data compression, we would need about 4.6TB of space for the same table. This is about a 16% space savings (which is not the best I have ever seen), but this is the largest table that I have used data compression on in a production environment.

BTW, this database lives on a little Dell PowerEdge 1950, with one quad-core Xeon 5440 CPU and 32GB of RAM, connected to a 3PAR S400 SAN.

The June meeting of the Denver SQL Server User’s Group is at 5:30PM on June 18, 2009. The meeting location is not at the Microsoft office anymore. It has been moved to the AmeriTeach office in Greenwood Village. Here is the address:

7800 East Dorado Place, Suite 150
Greenwood Village, CO 80111
720-346-1710

Ed Tomlinson will be presenting on How SQL Server supports Enterprise 2.0 Applications.