SQLServerPedia

Finally back in my hotel room; I’ve forgotten how exhausting it is to actually present these days.  John Baldwin and the rest of the Steel City SQL user group did an awesome job pulling this together.  These guys are a lot of fun to be around, and they really went out of their way to make sure that speakers felt welcome.

Some highlights:

• The facility was awesome.    It was held at a new building on the Jefferson State Community College campus; every room was equipped with a projector and audio equipment, and seating was comfortable, etc.
• There were a few familiar faces, but I also had the chance to meet other Southeastern SQL geeks.  I’m hoping that we can build on this relationship, and encourage new growth for all of our user groups.
• I particularly enjoyed hearing Andy Warren’s session on statistics; even though I don’t do a lot of work in performance tuning anymore, it was useful for me as a developer to understand what is a potential cause for bottlenecks on our servers.
• My own session (Confessions of a Data Integrator) went well; I need to trim it a bit and sharpen the focus, but I got a lot of positive feedback and positive comments from attendees.  I definitely think I can sharpen it a bit, and I also think I can revamp it for SQL Saturday 17 (in Baton Rouge).

The only snafu I noticed was that the schedule had some interesting time slots; some sessions were 60 minutes in length and some sessions were 75 minutes.  On at least two occasions, the 75 minute sessions were cut short (because the volunteer misread the length of time for that session).  Overall, it was a great day, and I’m looking forward to future events.

Obviously, I’m excited about SQL Saturday 7.  I’ll post more after the festivities are over, but for now, I’m packing up and heading over.   I love geek days.

Back in the days, Kalen Delaney blogged about how to remove single page allocations and how the algorithm changed from SQL Server 2000 to SQL Server 2005. However there seems to be some confusion when the single page allocations are kicked in. The confusion seems to be that single page allocations will start as soon as 3 extents are used.

Here is what Kalen had said: In SQL Server 2005, single page allocation is only disabled for the leaf level of the clustered index (the actual table data) if the table has least 3 extents (24 pages).

Here is the code snippet used by Kalen to show that at-least 3 extents are necessary for single page allocations.

IF OBJECT_ID('LargeRows') IS NOT NULL
    DROP TABLE LargeRows
GO
CREATE TABLE LargeRows
     (col1 int identity, col2 char(8000) default '8000 bytes of data')
GO
INSERT INTO LargeRows DEFAULT VALUES
GO 24
DBCC EXTENTINFO(testIndexSize, LargeRows, -1)
GO
CREATE CLUSTERED INDEX LargeRow_index on LargeRows(col1)
GO
DBCC EXTENTINFO(testIndexSize, LargeRows, -1)
GO

IF OBJECT_ID('LargeRows') IS NOT NULL
    DROP TABLE LargeRows
GO
CREATE TABLE LargeRows
     (col1 int identity, col2 char(8000) default '8000 bytes of data')
GO
INSERT INTO LargeRows DEFAULT VALUES
GO 25
DBCC EXTENTINFO(testIndexSize, LargeRows, -1)
GO
CREATE CLUSTERED INDEX LargeRow_index on LargeRows(col1)
GO
DBCC EXTENTINFO(testIndexSize, LargeRows, -1)
GO

Now take a look a this code snippet below:

IF OBJECT_ID('MediumRows') IS NOT NULL
    DROP TABLE MediumRows
GO
CREATE TABLE MediumRows
     (col1 int identity, col2 char(7000) default '7000 bytes of data')
GO
INSERT INTO MediumRows DEFAULT VALUES
GO 25
DBCC EXTENTINFO(testIndexSize, MediumRows, -1)
GO
CREATE CLUSTERED INDEX MediumRow_index on MediumRows(col1)
GO
DBCC EXTENTINFO(testIndexSize, MediumRows, -1)
GO

IF OBJECT_ID('MediumRows') IS NOT NULL
    DROP TABLE MediumRows
GO
CREATE TABLE MediumRows
     (col1 int identity, col2 char(7000) default '7000 bytes of data')
GO
INSERT INTO MediumRows DEFAULT VALUES
GO 27
DBCC EXTENTINFO(testIndexSize, MediumRows, -1)
GO
CREATE CLUSTERED INDEX MediumRow_index on MediumRows(col1)
GO
DBCC EXTENTINFO(testIndexSize, MediumRows, -1)
GO

IF OBJECT_ID('MediumRows') IS NOT NULL
    DROP TABLE MediumRows
GO
CREATE TABLE MediumRows
     (col1 int identity, col2 char(7000) default '7000 bytes of data')
GO
INSERT INTO MediumRows DEFAULT VALUES
GO 28
DBCC EXTENTINFO(testIndexSize, MediumRows, -1)
GO
CREATE CLUSTERED INDEX MediumRow_index on MediumRows(col1)
GO
DBCC EXTENTINFO(testIndexSize, MediumRows, -1)
GO

 

Now, lets look at some screenshots on what we find.

It is clear that the single page allocations aren’t removed as soon as we hit 3 extents from the above. And Kalen’s statement still stands correct, just pay attention to the fine print.

In SQL Server 2005, single page allocation is only disabled for the leaf level of the clustered index (the actual table data) if the table has least 3 extents (24 pages).

Acknowledgements: Kalen Delaney

Get started managing your SQL Server Express Edition and developing T-SQL with Microsoft’s free SQL Server Management Studio Express Edition.  Learn how from Brent Ozar and Jeremiah Peschka.

For more about this topic, check out:

• The SQL Server Express Edition wiki article
• Jeremiah Peschka’s article on Row-Based Security
• Jeremiah Peschka’s blog at Facility9 – where he blogs about SQL Server from a developer’s point of view
• Microsoft SQL Server Express Edition – the product page at Microsoft.

If you have an MP3 player or a portable video player and you want to download our videos automatically, you can subscribe to our podcast feeds here:

• MP4 (Apple) Video Feed
• WMV (Microsoft) Video Feed
• MP3 Audio-Only Feed
• Zune One-Click Subscribe for Video
• Zune One-Click Subscribe for MP3

You can also download this video to watch it later:

• MP4 (Apple) Video Download
• WMV (Microsoft) Video Download
• MP3 Audio-Only Download

I’ve written two articles on SQLTeam.com about a great new feature in SQL Server 2008 called Extended Events. They are the new low level, high performance eventing system in SQL Server. They use less system resources and provide better tracking of SQL Server performance than previous methods like Perfmon and SQL Trace/Profiler events.

 

1. Introduction to SQL Server 2008 Extended Events

This is an introductory article where we take a look at performance troubleshooting and system monitoring and what they lack in previous SQL Server versions. After that we get to know the Extended Events architecture, new terminology and we try them out with a simple example.

 

2. Advanced SQL Server 2008 Extended Events with Examples

Part 2 of the series takes a look at some performance considerations we must be aware of when using Extended Events like synchronous or asynchronous target target, predicate short circuiting and event action size. After we take a look at 7 examples that show a different way we can use them to troubleshoot our system. Examples are meant to show Extended Events power and give some ideas on how to use them for more advanced monitoring.

 

So in my first post I explained that my moniker was inspired by the rubber chicken hanging on the wall in my cube. After realizing that he's now become an integral part of the office in that we all squeeze him whenever something breaks its time he had a name. That's where you guys come in!

My chicken needs a name. Anything. SQL-related is cool but it doesn't really matter as long as its befitting its awesomeness. After all entries are in we here in the office will go over the entries and decide a winner. Unfortunately I don't have the backing of Godaddy.com on this one like the SQL Rap Contest so sadly I don't have any prizes, just bragging rights. I'll set the deadline to two weeks from today (Friday June 12th). Feel free to submit entries here on this blog or DM them to me on Twitter.

(channeling “Forest Gump”)… you never know what you’re gonna get. Ack! Sorry, I just can’t help myself sometimes. Moving on…

I was recently discussing guilds with my gamer husband and he commented how much running a user group sounds like running a guild. For those of you who aren’t already aware, before I traded my gaming addiction for a SQL one, he and I ran a guild together with around 140 members.

Not sure what a guild is? Check out the The Guild, a popular, very humorous, and only slightly exaggerated web series.

The more I’ve thought about what my husband said, the more I realized how right he is. Allow me to share my (questionable) thoughts on the subject:

Guild Masters

Some guild masters are great leaders and others are just very dedicated; the same is true with user group leaders. If you spend all day trolling forums and working on maxing your DPS, you’re probably a good gamer but it doesn’t necessarily make you a good guild master. Similarly, being a SQL samurai does not necessarily prepare you to lead a user group. A handful of people are just naturally good leaders; most everyone else has to acquire the skill, often through painful experience. Before starting a guild or user group, you should ask yourself the following questions:

• Are you in the market for an unpaid part-time job?
• Do you like to alphabetize your DVD collection?
• Do you enjoy helping n00bs (junior admins)?
• Have you ever led an anti-social, semi-violent mob before?

If you answered “no” to any of those questions, don’t worry, it doesn’t necessarily mean you should not start your own guild or user group. But you may want to consider first joining an existing group to ensure you’ll enjoy the experience. Either way, you’ll quickly find out how important it is to have…

Officers

You can’t do it alone. Well, you can try, but don’t expect the 40-man heroic raid you scheduled to start on-time or run smoothly. Every guild and group leader needs supportive and dedicated officers. If it’s your first time leading a group, try to recruit someone who has leadership experience but perhaps doesn’t have the time or energy to be El Jefe; this person can be an invaluable resource for you. And if you have run a group before, you still want officers to help distribute the workload. Officers can help with a variety of tasks, from managing supplies to organizing major events. Lastly, they’re also a great point of contact for your…

Guildies

Guildies (members) are the fine men and women who have entrusted you to lead them into battle (provide stimulating meetings). Without them, you would have no guild (user group). They have joined for any number of reasons: some are new to the game and want to learn (junior DBAs), some are interested in meeting new people with similar interests, and others are just there for the free food.

Over time, you’ll find membership waxes and wanes; people switch servers (move to a new city), trade in gaming for a more boring hobby (switch from DBA to sysadmin), or just run out of time in the day. There’s little you can do to change this, so you’ll inevitably have to do some new-member recruitment. However, if you’ve got a good group, you’ll find much of your advertisement is by the word-of-mouth of current members. Still want to recruit new members? Try throwing some big…

Events

Whether it’s an end-of-game dungeon or a SQL Saturday, everyone loves a good event. There’s a couple of things you should be aware of, though. First, always, ALWAYS plan for people to not show. Don’t take it personally; life just happens. I’ve heard that 70% of registrants is a good estimate of how many people will actually show up.

Secondly, while everyone loves to attend events, not many people want to actually help organize one. If you’re lucky enough to get volunteers, treat them very well! You’ll quickly find out a good volunteer is worth his or her weight in…

Loot

Just as dungeon bosses drop loot (prizes) both good and bad, so do sponsors. Very few guildies are motivated solely by loot, and loot is not absolutely necessary for a successful event. Still, everyone likes to win, and there’s really nothing like the joy of rolling a perfect 100 to score that epic dagger (erm… I guess the best translation for this one is having your ticket drawn to win a copy of Quest’s Capacity Manager).

PvP

One of the most popular event types is a PvP (player vs. player) raid. This is where your guildies attack members of opposing factions, just for fun and bragging rights. To help make user groups even more guild-like, I’m currently organizing raids against the local Oracle and mySQL user groups. We hope to use the element of surprise to really lay into ‘em. I’ll let you know how it turns out.

All jokes aside, guilds and user groups may not be _exactly_ the same, but there are certainly a surprising amount of similarities. If nothing else, both definitely involve a lot of time, effort, and dedication, and I think many of the leadership and organizational skills learned in a guild are truly transferable to the “real world.”

Hopefully by now you’re either feeling motivated to start a user group, or you’re off to the store to stock up on Cheetos and Mt. Dew, the sustenance of choice for most gamers, so you can survive the weekend locked in the basement playing PC games. Whatever the case… have fun!

A couple years ago I started consolidating all of my DBA scripts into a portable DBA database that I could use for diagnostics (hence the silly name) and for maintenance scripts. To help me to continue to be inspired to update the database and to get some feedback, I plan to post a number of the tables, procedures, and other database components over the next few weeks.

Hopefully others will get as much use out of these as I have. To make it easier to find these posts, you can search my blog for the tag DBADiagnostics and all of those future posts should pop right up.

For convenience sake, here’s is the script that I used to create the DBADiagnostics database:

USE [master]
GO

IF EXISTS(SELECT * FROM sys.databases WHERE name = 'DBADiagnostics')
    DROP DATABASE [DBADiagnostics]
GO

CREATE DATABASE [DBADiagnostics] ON PRIMARY (
    NAME = N'DBADiagnostics'
    ,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DBADiagnostics.mdf' 
    ,SIZE = 10240KB 
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 10240KB 
)
,FILEGROUP [Data] DEFAULT (
    NAME = N'DBADiagnostics_data'
    ,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DBADiagnostics_data.ndf' 
    ,SIZE = 10240KB 
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 10240KB 
)
LOG ON (
    NAME = N'DBADiagnostics_log'
    ,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DBADiagnostics_log.ldf' 
    ,SIZE = 10240KB 
    ,MAXSIZE = 2048GB 
    ,FILEGROWTH = 10240KB 
)
GO

Of course, depending on your directory structure above you may need to change those values.

I ran into a database earlier this week with a lot of stored procedures that look like this:

CREATE PROCEDURE updateUser
  @id INT, @FirstName VARCHAR(50), @LastName VARCHAR(50), @EmailAddress VARCHAR(50)
AS
SET NOCOUNT ON
�
UPDATE Users
SET FirstName = @FirstName,
    LastName  = @LastName,
    EmailAddress = @EmailAddress
WHERE id = @id
GO

Every table had  similar CRUD procedures that had been generated. If any data in the Users table changed, say a user updated their email address, this procedure would handle the update. Is this a good idea?

Aaron Alton recently posted about UPDATE statements. In his post, he explains why it is a good idea to use the WHERE clause to filter out rows that don’t need to be updated. I want to expound on Aaron’s point, and say that you should also avoid needlessly updating columns that don’t need to be updated. The reason? Nonclustered Indexes.

Let’s take a look at the following table:

CREATE TABLE Users (
  id INT,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  EmailAddress VARCHAR(50)
)
�
CREATE UNIQUE CLUSTERED INDEX cix_Users ON Users(id)
CREATE NONCLUSTERED INDEX ix_Users_FirstName ON Users(FirstName)
CREATE NONCLUSTERED INDEX ix_Users_LastName ON Users(LastName)
CREATE NONCLUSTERED INDEX ix_Users_EmailAddress ON Users(EmailAddress)
�
INSERT INTO Users
SELECT 1, 'John', 'Smith', 'jsmith@gmail.com'

If we were to call the updateUser procedure:

EXEC updateUser 1, 'John', 'Smith', 'jsmith@hotmail.com'

Even though the FirstName and LastName values aren’t changing, SQL Server will still update all of the columns resulting in the nonclustered indexes on the FirstName and LastName columns being locked and updated.

If we were to run the following UPDATE statement instead, the nonclustered indexes on FirstName and LastName would not need to be updated.

UPDATE Users
SET EmailAddress = 'jsmith@hotmail.com'
WHERE id = 1

So, how do we fix the problem with our update procedure listed above? If we know that updating an Email address is a common occurance, we might create a seperate procedure that only updates the EmailAddress column. Alternatively, we can use dynamic SQL to build the correct UPDATE statement for us.

Here is an example of how you could do this in a stored procedure:

CREATE PROCEDURE updateUser
  @id INT, @FirstName VARCHAR(50), @LastName VARCHAR(50), @EmailAddress VARCHAR(50)
AS
SET NOCOUNT ON
�
--Variables to hold the updated status
DECLARE @u_FirstName BIT, @u_LastName BIT, @u_EmailAddress BIT
�
--Check to see which values were updated
SELECT
  @u_FirstName = CASE FirstName WHEN @FirstName THEN 0 ELSE 1 END
 ,@u_LastName = CASE LastName WHEN @LastName THEN 0 ELSE 1 END
 ,@u_EmailAddress = CASE EmailAddress WHEN @EmailAddress THEN 0 ELSE 1 END
FROM Users
WHERE @id = id
�
--If none of the values were updated return
IF (@u_FirstName = 0 AND @u_LastName = 0 AND @u_EmailAddress = 0) RETURN
�
DECLARE @SQL NVARCHAR(4000)
�
SET @SQL = '
DECLARE @first bit --for the first value
UPDATE Users SET
 @first = 1 '
�
IF @u_FirstName = 1 SET @SQL += '
 ,FirstName = @FirstName '
�
IF @u_LastName = 1 SET @SQL += '
 ,LastName = @LastName '
�
IF @u_EmailAddress = 1 SET @SQL += '
 ,EmailAddress = @EmailAddress '
�
SET @SQL += '
WHERE id = @id'
�
PRINT @SQL
PRINT ''
EXEC SP_EXECUTESQL @SQL,
  N'@FirstName varchar(50), @LastName varchar(50), @EmailAddress varchar(50), @id int',
  @FirstName, @LastName, @EmailAddress, @id
�
GO

This trigger will show which columns have been updated:

CREATE TRIGGER t_Users
  ON  Users
  AFTER UPDATE
AS
       SET NOCOUNT ON
       IF UPDATE(FirstName) PRINT 'FirstName updated'
       IF UPDATE(LastName) PRINT 'LastName updated'
       IF UPDATE(EmailAddress) PRINT 'EmailAddress updated'
GO

To summarize, limiting the columns in the SET portion of the UPDATE statement will reduce locking, minimize index updates, and increase concurrency.

Let me know if I’ve left anything out.