I wanted to let everyone that comes here know that after some work, I’ve switched from posting to www.stratesql.com to blogging at www.jasonstrate.com.  This is mainly because I wanted to be able to have more flexibility in putting posts together and wanted some features that are not available with the software I was previously using.

If you are getting this message in your RSS feed for my site, please change the RSS subscription from StrateSQL to http://feeds.feedburner.com/StrateSql.  This will keep up with the subscription that has the current site of choice always coming to you and lets you avoid finding this message in your feed in the event I make similar changes a couple years down the road.

For posterity, this site will stay up.  For completeness, though, all of the blog content has been moved to www.jasonstrate.com.

If you’d like to keep up with me in other avenues, I can also be found in these places:

• Facebook – www.facebook.com/stratesql
• LinkedIn - http://www.linkedin.com/in/jasonstrate
• Twitter – www.twitter.com/stratesql
• Friendfeed - http://friendfeed.com/h4ppyd4y
• Delicious - http://delicious.com/StrateSQL

In case you missed the last reminder or hadn’t heard.  We are having a PASSMN meeting in December this year.  Check out the event details below and take a chance to learn new things with some of your fellow SQL Server professionals.

Don't Wait! Consolidate on 2008!

December 8th
5:00 PM Networking & Social
5:15 PM – 5:30 PM Meet the new PASSMN Board
5:30 PM - 7:00 PM Presentation

8300 Norman Center Drive
9th Floor
Bloomington, MN  55437

Don't Wait! Consolidate on 2008!

Speaker: Rick Heiges

SQL Server 2008 introduces features such as Resource Governor, Compression, Virtualization, and Policy-Based Management that help organizations consolidate to keep TCO down. A discussion of features since 2000 that enable consolidation along with the limitations of each will be presented. Real-world stories will enhance this discussion. The session will also encompass SQL Server on Virtual Machines.

Rick Heiges  is a SQL Server MVP and a Sr. Solutions Consultant with Scalability Experts working with customers on solutions within the SQL Server environment and educating users on SQL Server by speaking at user groups, conferences, labs, and road shows across the USA, Europe, and Africa. He holds an MBA and a M.S. in MIS as well as a B.S. in Computer Science. In addition, he also has attained MCTS:SQL2005 and MCITPBA certifications. His career has dealt with a wide array of job responsibilities including professor, developer, trainer, database administrator, and project manager/leader. Rick has been involved with PASS since the spring of 2001 and started up an Official PASS SQL Server user group in North Carolina as well as authoring several articles on for the PASS technical journal, the SQL Server Standard. Rick has served on the PASS Board of Directors since January 2003. You can read his blog at www.sqlblog.com and contact him at rheiges@scalabilityexperts.com.

Show Up And Network

I’d like to encourage everyone to come down and meet someone new at the user group meeting this month.  Walk up to someone you’ve not met before and introduce yourself.  Share a business card.  Make a contact.  Who knows!  The next person you meet just might be a new client or have the answer to solve your next production issue.

Also, with the regular monthly swag, we’ll be giving away CDs from PASS Summit 2008.  This is a library of over 160 sessions covering database administration, development, business development and professional development.  A huge resource of materials that I personally get a lot of benefit out of.

It’s a very good question. One that might not seem to insidious. Nothing that should be able to bring down the system and cause failures. Or will it?

I’ve been to a number of clients and done it myself before where I start to check out a stored procedure with some performance issues and sitting all pretty at the bottom is a GRANT EXEC statement. When I script out the stored procedure I get something similar to the following:

CREATE PROCEDURE dbo.FooGetTableA

    (

    @Parameter varchar(4)

    )

AS

SELECT Column1 

FROM dbo.TableA

WHERE Column2 = @Parameter

GRANT EXEC ON dbo.FooGetTableA TO ApplicationRole

GO

But if you look carefully, there is something missing, or one could say included that shouldn’t be.  Look again if you don’t see it. It’s hidden in plain sight.  The permissions for the procedure are included in the body of the stored procedure.  When the procedure was written, someone thought ahead to add permissions to the script but forgot the GO statement between the stored procedure

In a better world this script would have looked like this:

CREATE PROCEDURE dbo.FooGetTableA

    (

    @Parameter varchar(4)

    )

AS

SELECT Column1 

FROM dbo.TableA

WHERE Column2 = @Parameter

GO

GRANT EXEC ON dbo.FooGetTableA TO ApplicationRole

GO

It’s Just a Permission Statement

Who cares, right?  So you are assigning some permissions every time that procedure executes.  What harm could possibly come of it.  I’ve seen this so many times and usually it’s one of things I’ll point out and say, “oops, you should take care of that”.  When I should be saying, “yeah, fellas.  You’ve got a time bomb there waiting for your business to take off.”

And the time bomb is deadlocks.  Completely preventable deadlocks.

If you have procedures that grant themselves permissions, then as the volume of activity in your database increases you may start to see deadlock graphs similar to the following:

deadlock-list
 deadlock victim=process30108bac8
  process-list
   process id=processec55dd68 taskpriority=0 logused=0 waitresource=METADATA: database_id = 10 PERMISSIONS(class = 1, major_id = 219199881) waittime=15000 ownerId=746424569 transactionname=Load Permission Object Cache lasttranstarted=2009-10-22T23:06:59.287 XDES=0x3712a8e98 lockMode=Sch-S schedulerid=1 kpid=5832 status=suspended spid=157 sbid=2 ecid=0 priority=0 transcount=1 lastbatchstarted=2009-10-22T23:06:59.287 lastbatchcompleted=2009-10-22T23:06:59.280 clientapp=.Net SqlClient Data Provider hostname=PRDWB0111 hostpid=5640 loginname=portaluser isolationlevel=serializable (4) xactid=746424394 currentdb=10 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=AdventureWorks2008.dbo.FooGetTableA line=1 sqlhandle=0x03000a0089b9100d0e527800669c00000100000000000000
CREATE PROCEDURE dbo.FooGetTableA
    (
    @Parameter varchar(4)
    )
AS

SELECT Column1 
FROM dbo.TableA
WHERE Column2 = @Parameter

GRANT EXEC ON dbo.FooGetTableA TO ApplicationRole
    inputbuf
Proc [Database Id = 10 Object Id = 219199881]    
   process id=process30108bac8 taskpriority=0 logused=0 waitresource=METADATA: database_id = 10 PERMISSIONS(class = 1, major_id = 1746157316) waittime=2125 ownerId=746479249 transactionname=Load Permission Object Cache lasttranstarted=2009-10-22T23:07:12.180 XDES=0x3786c61c8 lockMode=Sch-S schedulerid=3 kpid=4048 status=suspended spid=69 sbid=2 ecid=0 priority=0 transcount=1 lastbatchstarted=2009-10-22T23:07:12.180 lastbatchcompleted=2009-10-22T23:07:12.167 clientapp=.Net SqlClient Data Provider hostname=AMBER hostpid=568 loginname=portaluser isolationlevel=serializable (4) xactid=746372404 currentdb=10 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    executionStack
     frame procname=AdventureWorks2008.dbo.FooGetTableB line=1 sqlhandle=0x03000a00043f146882564201a09b00000100000000000000
CREATE PROCEDURE dbo.FooGetTableB
    (
    @Parameter varchar(4)
    )
AS

SELECT Column1 
FROM dbo.TableB
WHERE Column2 = @Parameter

GRANT EXEC ON dbo.FooGetTableB TO ApplicationRole
    inputbuf
Proc [Database Id = 10 Object Id = 1746157316]    
  resource-list
   metadatalock subresource=PERMISSIONS classid=class = 1, major_id = 219199881 dbid=10 id=lock4153ec880 mode=Sch-M
    owner-list
     owner id=process30108bac8 mode=Sch-M
    waiter-list
     waiter id=processec55dd68 mode=Sch-S requestType=wait
   metadatalock subresource=PERMISSIONS classid=class = 1, major_id = 1746157316 dbid=10 id=lock415451780 mode=Sch-M
    owner-list
     owner id=processec55dd68 mode=Sch-M
    waiter-list
     waiter id=process30108bac8 mode=Sch-S requestType=wait

Breaking It Down

When I first started looking at these there are a few things I noted right away:

1. The procedures were access completely different tables with no common objects between them.  In the sample above there is TableA and TableB and no relationship.
2. Looking at each of the processes in the deadlock both of them have the following attributes
   1. waitresource=METADATA: database_id = 10 PERMISSIONS
   2. transactionname=Load Permission Object Cache

So nothing in common and a deadlock on a metadata resource for permissions.  This made me start to re-think how the two procedures were related.  With a metadata resource wait, there seems to be an issue above the data in the table.  Since both procedures point to the Load Permission Object Cache, maybe there is an issue there.

If you take a look, each of the procedures has a GRANT EXEC permission statement in it.  This is the area of commonality and where the two executions deadlocked.  Removing the GRANT EXEC permissions statements stop this deadlock from occurring.

After going through and removing these permission statements from a number of procedures that had this issue, all of the deadlocks with these types of issues disappeared.  And it is smooth sailing once again.

Cautionary Tale

Hopefully this is a scenario that only I’ve run into.  But if it’s not then this should serve as a reminder that little details that seem like a little non-issue, could be the crack that breaks the damn when there’s enough water behind it.  The thing that gets you on this issue is that it isn’t until execution start to really grow before it pops out and it will only hit when you’re the busiest.  This is something I’ll be keeping an eye out for in the future and I’d recommend the same for others as well.

I got to work with deadlocks quite a bit recently.  There were quite a few interesting ones that came up that I had the chance to research.  Since I like easy, I’ll start with the one that I forgot to grab the deadlock details for.

Well, maybe not all of the details… in this case as the title states I was looking at deadlocks with the events exchangeEvent and threadpool.  I managed to come across a post from Bart Duncan that went through and deciphered this deadlock.  The long and the short of it… parallelism deadlocks.

Bart does a better job explaining this than I can do here, especially since I didn’t take the time to grab the deadlock details for review.  Maybe I’ll have that one the next one…

Fortunately, a large part of the issue that I was reviewing for the client had to do with parallelism and so solving this issue actually occurred as a side effect of dealing with parallelism issues.  But I will share my little secret that I used to resolve this and most of the parallelism…

Indexes!!

There I said it.  True, you can have too many indexes.  But no indexes is too few.  No clustered indexes can lead to too many scans.  I could pulpit here on indexes and making sure that you have them, but I’ll save that for another time.

Overall, I used Bart’s Workaround #1.  Hopefully this helps… direct you to a post that is more prescriptive.

Hopefully everyone caught the news that the November PASSMN meeting was cancelled.  We had some scheduling conflicts and things didn’t come together as expected.  But to make up for that, we’ve managed to snag a speaker and room for the beginning of December.

Don't Wait! Consolidate on 2008!

December 8th
5:00 PM Networking & Social
5:15 PM – 5:30 PM Meet the new PASSMN Board
5:30 PM - 7:00 PM Presentation

8300 Norman Center Drive
9th Floor
Bloomington, MN  55437

Don't Wait! Consolidate on 2008!

Speaker: Rick Heiges

SQL Server 2008 introduces features such as Resource Governor, Compression, Virtualization, and Policy-Based Management that help organizations consolidate to keep TCO down. A discussion of features since 2000 that enable consolidation along with the limitations of each will be presented. Real-world stories will enhance this discussion. The session will also encompass SQL Server on Virtual Machines.

Rick Heiges  is a SQL Server MVP and a Sr. Solutions Consultant with Scalability Experts working with customers on solutions within the SQL Server environment and educating users on SQL Server by speaking at user groups, conferences, labs, and road shows across the USA, Europe, and Africa. He holds an MBA and a M.S. in MIS as well as a B.S. in Computer Science. In addition, he also has attained MCTS:SQL2005 and MCITPBA certifications. His career has dealt with a wide array of job responsibilities including professor, developer, trainer, database administrator, and project manager/leader. Rick has been involved with PASS since the spring of 2001 and started up an Official PASS SQL Server user group in North Carolina as well as authoring several articles on for the PASS technical journal, the SQL Server Standard. Rick has served on the PASS Board of Directors since January 2003. You can read his blog at www.sqlblog.com and contact him at rheiges@scalabilityexperts.com.

Show Up And Network

I’d like to encourage everyone to come down and meet someone new at the user group meeting this month.  Walk up to someone you’ve not met before and introduce yourself.  Share a business card.  Make a contact.  Who knows!  The next person you meet just might be a new client or have the answer to solve your next production issue.

Also, with the regular monthly swag, we’ll be giving away CDs from PASS Summit 2008.  This is a library of over 160 sessions covering database administration, development, business development and professional development.  A huge resource of materials that I personally get a lot of benefit out of.

If you don’t know where you’ve been, how can you expect to know where you are going.  Seems like such a simple little line and I’m sure many of us can look at history, politicians, and maybe that restaurant review that you didn’t heed when it comes to this line.  But this also applies to DBAs and more specifically to SQL Server.

In much the same way… if you don’t know what’s been happening in your SQL Server environment, you won’t know how to resolve performance problems that have occurred on the server.  One of the ways I track what’s happened on a SQL Server instance is through wait stats.  Now if you aren’t tracking wait stats and they aren’t one of the first few things that you check when monitoring performance then you need to stop reading this post and read this instead.

Wait Stat Snapshots

To facilitate wait stat monitoring, I typically take a snapshot of the current wait stats (sys.dm_os_wait_stats) on a server every 15 minutes.  This is enough time that I can roll get a good idea of what’s happened from a resource perspective in a short amount of time and I can also easily roll the value up to hour, two-hour, or full day totals.  It gives me options – we all like options.

As I’ve done in a number of posts in the past this new script will be setup to add to the DBADiagnostics database that I’ve blogged about before.

Building the Snapshot

There are two table included in the process for creating the snapshots of wait stat activity.  These are:

• Monitor.WaitStatSnapshot
• Monitor.WaitStatHistory

Here is the schema to create them:

USE [DBADiagnostics]
GO

CREATE TABLE [Monitor].[WaitStatSnapshot](
    [CreateDate] [datetime] NOT NULL,
    [WaitType] [nvarchar](60) NOT NULL,
    [WaitingTasksCount] [bigint] NOT NULL,
    [WaitTimeMs] [bigint] NOT NULL,
    [MaxWaitTimeMs] [bigint] NOT NULL,
    [SignalWaitTimeMs] [bigint] NOT NULL,
    CONSTRAINT [PK_Monitor_WaitStatSnapshot_CreateDateWaitType] 
        PRIMARY KEY CLUSTERED ([CreateDate] ASC, [WaitType] ASC)
) ON [Data]
GO

CREATE TABLE [Monitor].[WaitStatHistory](
    [CreateDate] [datetime] NOT NULL,
    [WaitType] [nvarchar](60) NOT NULL,
    [WaitingTasksCount] [bigint] NOT NULL,
    [WaitTimeMs] [bigint] NOT NULL,
    [MaxWaitTimeMs] [bigint] NOT NULL,
    [SignalWaitTimeMs] [bigint] NOT NULL,
    CONSTRAINT [PK_Monitor_WaitStatHistory_CreateDateWaitType] 
        PRIMARY KEY CLUSTERED ([CreateDate] ASC, [WaitType] ASC)
) ON [Data]

The general process is to insert the current values from the wait stat DMV into Monitor.WaitStatSnapshot.  After which the delta between the last two snapshots in Monitor.WaitStatSnapshot is inserted into Monitor.WaitStatHistory.

If the values in the last snapshot are smaller that those in the second to last snapshot, then the insert assumes that the wait stat DMV has been reset.  Resetting can occur either though a SQL Server service restart or a DBCC command. 

Either way the procedure below will accumulate a snapshot history from the wait stat DMV.  Of courses there are a couple parameters that provide some automated cleanup.  Data in Monitor.WaitStatSnapshot is cleaned up after 1 day and the data in Monitor.WaitStatHistory is cleaned up after 90 days.  This helps prevent the data in the DBADiagnostic database from taking over when it’s neglected because everything is running perfectly.

USE [DBADiagnostics]
GO

/*================================================================================
Procedure:    Monitor.TrackWaitStats
Author:        Jason Strate
Date:        October 26, 2009

Synopsis:
    This procedure takes snapshots of wait stats and compares them with previous 
    snapshots to determine a delta of changes over time.  Raw snapshot information
    is deleted on a short time span, while the delta information in the history
    table is deleted over a longer time span.


================================================================================
Revision History:
Date:        By            Description
----------------------------------------------------------------------------------

================================================================================*/
CREATE PROCEDURE [Monitor].[TrackWaitStats]
    (
    @SnapshotDays tinyint = 1
    ,@HistoryDays smallint = 90
    )
AS

INSERT INTO Monitor.WaitStatSnapshot
SELECT GETDATE()
    , CASE wait_type WHEN 'MISCELLANEOUS' THEN 'MISCELLANEOUS' ELSE wait_type END
    , SUM(waiting_tasks_count)
    , SUM(wait_time_ms)
    , SUM(max_wait_time_ms)
    , SUM(signal_wait_time_ms)
FROM sys.dm_os_wait_stats
GROUP BY CASE wait_type WHEN 'MISCELLANEOUS' THEN 'MISCELLANEOUS' ELSE wait_type END

;WITH WaitStatCTE
AS (
    SELECT CreateDate
        , DENSE_RANK() OVER (ORDER BY CreateDate DESC) AS HistoryID
        , WaitType
        , WaitingTasksCount
        , WaitTimeMs
        , MaxWaitTimeMs
        , SignalWaitTimeMs
    FROM Monitor.WaitStatSnapshot
    )
INSERT INTO Monitor.WaitStatHistory
SELECT w1.CreateDate
    , w1.WaitType 
    , w1.WaitingTasksCount - COALESCE(w2.WaitingTasksCount,0)
    , w1.WaitTimeMs - COALESCE(w2.WaitTimeMs,0)
    , w1.MaxWaitTimeMs - COALESCE(w2.MaxWaitTimeMs,0)
    , w1.SignalWaitTimeMs - COALESCE(w2.SignalWaitTimeMs,0)
FROM WaitStatCTE w1
    LEFT OUTER JOIN WaitStatCTE w2 ON w1.WaitType = w2.WaitType 
        AND w1.WaitingTasksCount >= COALESCE(w2.WaitingTasksCount,0)
        AND w2.HistoryID = 2 
WHERE w1.HistoryID = 1 

DELETE FROM Monitor.WaitStatSnapshot
WHERE CreateDate < DATEADD(d, -@SnapshotDays, GETDATE())

DELETE FROM Monitor.WaitStatHistory
WHERE CreateDate < DATEADD(d, -@HistoryDays, GETDATE())
GO

There you have it.  While I didn’t go into how you can and will use this information, the basis for collecting this data for reporting has been laid out.

Later this week I’ll post a follow-up to show how to report on this information and include a couple Reporting Services reports for digging into the data.

A few times this past week I heard the quote, “Hi, Eric Stratton, Rush chairman, damn glad to meet you”.  That line kind of summarizes what the PASS Summit was like for me this year.  It’s been like coming to a week long rush event for the best fraternity in the world.  This fraternity isn’t the Delta Tau Chi’s though, it’s the PASS organization.

The PASS Summit was a chance to look at upcoming features in SQL Server, share learning in nearly 170 presentations, and networking with peers from across the street and the other side of the world.  It wasn’t always serious and one night at the karaoke bar a fellow DBA Rick Rolled us.

A Great Change

This year was drastically different though (for me at least) – and I believe it has everything to do with social networking.  It started a bit last year, but now with the flood of SQL Server professionals on twitter it seems to have matured quite a bit.  From the first day I was at the conference, I was recognizing people that I’ve shared conversations with on Twitter.  And I didn’t have deal with the nervousness of meeting them for the first time, since Twitter and Social Networking had taken care of that.

Last year at the PASS Summit, I think I met about a dozen new people.  Most of the people I talked to were just people I had met through out that year.  They were a good group of people, but I mostly just continued to develop those relationships.

This year though, I think I met about a hundred people that I hadn’t met before.  True, I had talked with a number of them prior to PASS through Twitter.  But I hadn’t actually met them.  And let’s be honest, I’m just this guy from Minneapolis.  I’m not someone who’s written books or worked on the DBCC commands.

What I am getting at here is that anyone can be a part of the community.  It’s a matter of joining in and becoming a part of the conversation.  And with Twitter anyone can join the conversation at any time.  Or you can just listen, but you’ll eventually join in… it’s too tempting.

Doing It Wrong

I’m going to say it.  I’ve said it to others and I’m going to say it here.  If you are a SQL Server professional and you are not on Twitter yet, you are doing it wrong.

If you are at a company and you are the lone DBA. *POOF*  No more.  Hundreds of others in your same boat, dealing with the same issues are there for you.  If you’re at a large organization dealing with hundreds of servers and run across a new issue that no one at your company has seen.  *POOF* I bet someone out on Twitter has and they might be able to help you through the answer.

I can’t stress the benefits of social networking enough.  It really gives you a door into the office of hundreds of other SQL Server professional who can help you solve problems and give you a sense of community with what you are doing.  And when it comes to the PASS Summit, it’s a way to find out where people are going, what they are doing, and how to get around town to hot spots that others are checking out.

I hope this little unwinding from PASS helps you decide that you need to get on Twitter if you are not already.  And also helps you reconsider the PASS Summit if you haven’t gone to it yet.  I have more thoughts on PASS that I’ll be posting later.

You can register already for next year…

The SSWUG Ultimate Virtual Conference starts this morning.  Are you planning to take advantage of this opportunity?  The Rock Star speakers are waiting in the channels, anticipating you every move.  But, have you registered?

It’s not too late.  If you can’t get to the sessions today, there is always tonight to go through and view them.  Just check out what the conference includes.

• More than 75 technical sessions
• New! Live sessions track - all live sessions, with speaker Q&A/interviews at the end of the day
• On-Demand access to sessions for 45 days - miss a session? No problem.
• Chat, Twitter Integration, SKYPE integration for Q&A
• Experience-based learning - find out what you need to know from people that are using the technology every day
• These are NOT sales presentations.
• 6-Month SSWUG.ORG Membership (or membership extension), included!
• ALL-ACCESS Pass: SQL Server and Business Intelligence with related SharePoint and .NET technologies - all included, one low price
• Great vendor hall - learn about the best tools, technologies, publications and partners out there for your shop

Hope in and join the conference!  I’ll be there after my morning meeting.

It starts next week,the SSWUG Ultimate Virtual Conference on October 21-24.  This is an excellent opportunity to get the benefits of heading to a conference full of Rock Star speakers while being able to view the content from the comforts of home or your desk. 

Have you registered?

If you haven’t registered yet, I really recommend that you do.  Heck, you can even use this discount (SPJSUVC09) to save a little on the registration.  But I’ve told you what to do, have I told you why.  Well, let’s take a look at some bullet points that describe the conference:  ‘Cuz we all like bullet points.

• More than 75 technical sessions
• New! Live sessions track - all live sessions, with speaker Q&A/interviews at the end of the day
• On-Demand access to sessions for 45 days - miss a session? No problem.
• Chat, Twitter Integration, SKYPE integration for Q&A
• Experience-based learning - find out what you need to know from people that are using the technology every day
• These are NOT sales presentations.
• 6-Month SSWUG.ORG Membership (or membership extension), included!
• ALL-ACCESS Pass: SQL Server and Business Intelligence with related SharePoint and .NET technologies - all included, one low price
• Great vendor hall - learn about the best tools, technologies, publications and partners out there for your shop

Are you jazzed?  Wait, I didn’t make you laugh.  Good sales pitches always include some laughter.  One more bullet point:

• The conference that started the best SQL Server humor threads

Yeah, that’s right.  And there just might be more humor at the one coming up.  So let’s get signed up.

I’ve been skipping on posting technical blogs for a couple months.  I’m planning to put that issue to rest with some aggressive blogging over the next couple months to make up for lost time.  After reading SQL Fool’s post on her reasons for change, I figured I’d do the same.  Not because anyone has noticed but because I want to lay out a plan that I’ll stick to.

Making Excuses

I don’t have any reasons as good as a baby in my belly for why I haven’t been blogging so much.  But I’ll share my reasons nonetheless

1. PASSMN SQL Summit 2009: Our user group put together an all-day Friday SQL event.  It was great and a lot of fun.  We had nearly 100 people there for the entire day and from the surveys that I saw it was a wild success.  Now to see if I can inspire people for a SQL Saturday in the spring.
2. Presenting: I’m sure others have been doing this as much as I have but I’m still fairly green at this and it takes time to put them all together.  On the up side, after this month I have one left for the entire year.
3. The House: It’s being sold and now I have to finish all of my house projects.  Saying I am good at starting them and not finishing them is the understatement of the year.

The “Plan”

There are a number of things I plan to touch on relatively quickly.  Some of these topics are:

• More DBA Diagnostics: Let’s be honest, database administration is always a good topic and this conversation never even hit the good stuff.  Such as backups or index and statistic maintenance.  I even have reports that go with some of this stuff.
• My journey into MDM: I’ve talked to a number of clients in the past few months about MDM.  Amazingly, as knew as this topic seems to be, it is just new framework around an old problem.  I am hoping to cover MDM from the basics to the implementation and examples of past projects that could be considered “partial” MDM solutions.
• Web Testing: Recently, I worked on testing a SharePoint site that was using Reporting Services in Integrated Mode.  I found out quite a bit when it came to testing SSRS reports and controlling report functionality through a web test.  I plan to share as much of that as I can.
• Extended Events: I really love these and hope to help others love them as well.
• Implementing Best Practices: I’ve been working with Policy Based management for a work project and plan to develop some policies to implement and monitor best practices and share those on here.
• Presentations: I’ve delivered about a dozen different presentations over the past year.  Most of the time when I want to learn something I either find a project or a presentation that forces me to learn it.  It’s my method.  Well, I’ve got this library of useful stuff now and plan to share that here as well.

Somehow I managed to get through college without ever joining a study group.  None… ever.  I don’t even know how one would put one together properly.  Well, I do know from watching movies that someone is always responsible for bringing brownies or some other treat to share.  But I don’t think that’s the main goal.

My main understanding is that you get a bunch of people together that are motivated toward a common goal and because of the diversity of the group you have immediate resources on hand to answer or bounce questions off of.

So this leaves me with some questions:

1. Does anyone have any experience with putting together study groups for working towards certifications?
2. If so, what are some must-dos and gotchas that are important for success?
3. Would anyone in the Twin Cities metro area be interested in joining a study group?

I like discounts.  Paying full price when I don’t have to sucks.  It’s a waste of resources, and as a person who’s had to tune databases in the past I’ve grown quite fond of resource management.  And the best resource ever to manage is dollars.  Because you can buy stuff.

What About 2nd Chance?

I was a big fan of the 2nd Chance Exam offer that Microsoft used to have.  This offer allowed you to take the exam with an insurance policy that if you failed the first time, it was free to re-take once.  For every exam I took over the past two years, I used that offer.  That is, until it was allowed to expire.  Sad face.

The New Offer

Microsoft is offering a new discount on exams from 15% to 25% off the exam price.  These exam discounts are available for both SQL Server 2005 and SQL Server 2008.  The offer is available until the December 31, so it is a limited time offer.  Of course, this doesn’t include any guarantee that you’ll pass the exam but you can’t have a chance to pass them if you don’t give it a try.

Get Your Discount

Now that we have a new discount, we should take advantage of it.  Just think, if you take a few exams and save enough bucks and I bet you could justify going out to eat at Fogo.  Bring on the meat!

Michael Swart (Database Whisperer) posted a great write up and interview with Brad Schulz that is worth checking out.  Michael’s post links to a post from Brad on the use of EXIST and what is required for the SELECT portion of the statement.  Hope you are still with me.

Definitely worth the read and +1  for my blog subscriber list.

As he demonstrates, it doesn’t matter whether you use SELECT *, SELECT 1 or SELECT 1/0.  If you want to know what this means, please read his post. 

From SQL Server’s perspective, it doesn’t consider any of this information in preparing the results.  The only thing that matters and this is from a practices perspective is to select a style for writing EXISTS statements and sticking to it.

Sometimes when I get pulled in to look at a database, I’ll sit down with the developers or database administrators working on the database and get some background on the database.  This usually includes pointing on the important tables, an explanation of why some design decisions were made, and some pre-conceived notions about where to begin focusing.

I’m going to pass on going through my triage process today, but instead focus on the notion of important tables.  This is an area that I often get to provide some immediate feedback to the client.

The Importance in Being Important

How does one define an important data?

• The most data?  This seems a no-brainer but don’t archive tables tend to usually be pretty large and by being archive, maybe no longer important data.
• The most recently updated data?  Probably not, this could have been the StateProvince tables because Jim in marketing decided that Puerto Rico should be a country now.  Yeah, I worked at a place where some days it was a country and others a territory.
• The most sensitive data?  While a customer may feel the security of their data is the most important aspect of the data in your database.  If I’m out looking into performance issues, it might not be a consideration in this scenario.
• The most active data?  This is usually where I tend to focus.  Data that is being used is probably the most important data in the system.  Especially if a lot of the data is being used.

Finding the Most Active Data

To find the active data, a good place to start looking would be in the memory buffer.  Since data that is most actively being used will hang out there it is a good indicator of what your active data is.  By looking at the data in memory, you can start to make decisions on which tables and indexes to pay a little more attention to.  The trouble with this approach though will be that data used now may not be used later.

But that might be exactly the problem.  If you know that an un-indexed archive table is never used and the entire table is in memory, that may be the source of some problems.  Something had to move out of memory for something to move into it.

Below is the T-SQL Script that I used to determine what is in the buffer.  It is scoped to the database level so you would need to run this multiple times across a few databases to see what is going on for the entire server.

;WITH AllocationUnits
AS (
    SELECT 
        p.object_id
        ,p.index_id
        ,p.partition_number 
        ,au.allocation_unit_id
    FROM sys.allocation_units AS au
        INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id AND (au.type = 1 OR au.type = 3)
    UNION ALL
    SELECT p.object_id
        ,p.index_id
        ,p.partition_number 
        ,au.allocation_unit_id
    FROM sys.allocation_units AS au
        INNER JOIN sys.partitions AS p ON au.container_id = p.partition_id AND au.type = 2
)
SELECT t.name as table_name
    ,i.name as index_name
    ,i.[type_desc]
    ,au.partition_number
    ,COUNT(*) AS buffered_page_count
    ,CONVERT(decimal(12,2), CAST(COUNT(*) as bigint)*CAST(8 as float)/1024) as buffer_mb
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN AllocationUnits au ON bd.allocation_unit_id = au.allocation_unit_id
    INNER JOIN sys.all_objects t ON au.object_id = t.object_id
    INNER JOIN sys.indexes i ON au.object_id = i.object_id AND au.index_id = i.index_id
WHERE bd.database_id = db_id()
GROUP BY t.name, i.name,i.[type_desc], au.partition_number
ORDER BY COUNT(*) DESC

HINT: Try running this on MSDB some time right after database backups.  I’ve run into a few cases where backup tables haven’t been cleaned up in ages and the amount of data in those tables bumps a lot of user data out of the buffer while performing backups.  Not the most ideal situation.

I saw this post a few weeks back and got around to downloading it myself tonight.  If you don’t know what it is.  It’s a free SQL Server security book.

Best of both Worlds

There are two things I like about this e-book.  The first, it is free.  And the second, it’s a book on security which there will never be enough of.  Sadly, security is one of those areas that seem to be overlooked until it is often too late.

Coming up this month is a free event to discover more ways to write T-SQL here is an event for you.  The session will be hosted by yours truly and features T-SQL tricks and tips that I’ve picked up and want to spread the word on.

Here is the registration link.

And the details:

Course Overview

ITCare, delivered and hosted quarterly by our Partners is designed to keep you up to date on the cutting-edge technologies. ITCare is a FREE half-day technical seminar series created by IT Professionals for IT Professionals! We'll address the needs and issues today and show you how to make a real difference in the way that you work.

Objectives

While not everyone may not be a great painter, there is an art form to writing great SQL statements. In this session we'll go over tips and tricks in writing SQL. We'll also review some popular bad practices and provide demonstrations to better change those velvet Elvis's to Rembrandts. As every paint brush can't produce a masterpiece, the same goes for SQL statements. Writing good SQL code requires the right brush. After this session, you'll have a few more paintbrushes and probably throw out a few that are no longer useful.

1. Review and discussion of common coding methods can impact performance.
2. Discussion of uncommon coding methods that can be used to improve performance.
3. Review and application of existing T-SQL functions.

Audience

Junior to mid-level SQL Server developers

Prerequisites

Understanding of basic T-SQL coding skills.

Course Price

$0.00

Topics

Speaker: Jason Strate of Digineer, Inc. Jason Strate has been a Database Architect for over ten years. His experiences include designing and implementing both OLAP and OLTP solutions involving clustering servers, database monitoring and tuning, analysis services, DTS/Integration Services, Reporting Services, and Notification Services. Jason is actively involved with the local PASS chapter (SQL Server User Group); he is currently serving on the Executive Board and has presented at monthly meetings. Jason contributed to Microsoft's published white paper "Empowering Enterprise Solutions with SQL Server 2005 Enterprise Edition and actively blogs about SQL Server and related technologies. Also this past year, Jason participated in the development of Microsoft Certification exams for SQL Server 2008 and presented at the SSWUG SQL Server Launch Event and the last two SSWUG Virtual SQL Server Conferences.

I’ve been to a number of conferences and have always found it difficult to walk up to people that I don’t know and introduce myself.  It’s just not something that comes natural to me.  And I’ve found that this typically isn’t a strength of other people in the IT community.

When we were planning our recent event, a full day event similar to SQL Saturday but on a Friday, I wanted to add something that got people to meet people that they didn’t know and start some networking.

Lara Rubbelke (@SQLGal) suggested that we try doing a bingo card.  It had been used for a recent event she had attended and it seemed like a good idea. And fit the concept I was thinking of – getting lots of people to talk to strangers and get to know people outside their typical bubble. 

The Game

To set this up, we put together a word document that looked like a Bingo card with 12 squares.  The purpose was for each attendee to find someone that matched a square and get their initials.  We used the following items on our card.

• Attended DBA Track Session
• Attended Developer Track Session
• Attended BI Track Session
• SQL Server 2008 in production
• SQL Server version prior to 2005 in production
• Presented PASSMN SQL Summit Session
• 1+ TB database in production
• Passed SQL Server 2005/2008 Certification
• Service Broker in Production
• Blogs on SQL Server
• PASSMN LinkedIn Member
• Microsoft Employee
• Twitters
• SQL Server MVP   
• Used SQL Server 4.1  Yes, this guys was at our event.

The Payoff

Instead of just using survey’s for giveaways at the end of the event, we opted to use the Networking Bingo card to drive people to try to complete the card.  This turned out to be huge.  People were chatting and networking and some people managed to care all of the squares on the bingo card before the first break.

The real payoff was the amount of networking that we had between the sessions.  Instead of sitting at the tables waiting for the next session, most people were up and about meeting and talking to people.  Some people came up and mentioned how much they enjoyed a reason to network for a change.

It was awesome!

I’ve attached our bingo card, so feel free to use it for your own event.

In less than a month SSWUG will be hosting the Ultimate Virtual Conference on October 21-24.  This is an excellent opportunity to get the benefits of heading to a conference full of Rock Star speakers while be able to view the content from the comforts of home or your desk.

Reasons to Attend

Some of the details of the event are:

• More than 75 technical sessions
• New! Live sessions track - all live sessions, with speaker Q&A/interviews at the end of the day
• On-Demand access to sessions for 45 days - miss a session? No problem.
• Chat, Twitter Integration, SKYPE integration for Q&A
• Experience-based learning - find out what you need to know from people that are using the technology every day
• These are NOT sales presentations.
• 6-Month SSWUG.ORG Membership (or membership extension), included!
• ALL-ACCESS Pass: SQL Server and Business Intelligence with related SharePoint and .NET technologies - all included, one low price
• Great vendor hall - learn about the best tools, technologies, publications and partners out there for your shop

If that doesn’t jazz you up, then maybe know that one of the best SQL Server humor threads started at the last SSWUG Virtual Conference. 

StrateSQL Sessions

I’ll be presenting four sessions at the SSWUG Ultimate Virtual Conference.  My sessions will be:

Solving Business Pains with SQL Server Integration Services

SQL Server Integration Services (SSIS) offers a wide range of features and functionality that can be used to solve business pains within Extract, Transform, and Load (ETL) processes. These pains can be caused by the performance of current ETL process, the maintainability of ETL processes or represent a new pain that SSIS features can resolve. This presentation will provide a demonstration of real world scenarios where features of SSIS were used to solve business pains through modification of existing SSIS packages or through the creation of new SSIS packages. The session will also discuss current best practices in SSIS package design.

Necessarily Evils, Building Optimized CRUD Procedures

Every developer loves them and a lot of DBAs hate them. But there are many and valid reasons for creating generic SELECT, INSERT, UPDATE, and DELETE procedures. In this session, we’ll go through designing CRUD procedures that utilize new and existing SQL features to create CRUD procedures that are optimized for performance. 

Improving Daily Imports with Partitioned Tables

At some point, everyone struggles with loading data to OLTP systems. The business needs the data loaded so that users can work through the data. But the users can’t afford the downtime to get the data into the production tables. In this session we’ll review this problem and how to use partitioning to alleviate this issue. 

Are You Following Your Own Best Practices?

Everyone has their own best practices that they try to follow. But often times we aren’t sure how well we are following our own or industry best practices. In this session we’ll go into monitoring and managing best practices throughout your environment. The session will look at how to create a solution using policy based management and other tools to report on your compliance to best practices. 

One More Reason

For those budget conscious in the current economy, I can even offer a discount code to help nudge those that haven’t quite given in to joining the conference.  With these nine letters you can received a $10 discount off your registration… SPJSUVC09

The Final Pitch

I lied about the one more reason, I’ve got to give one more pitch.

It’s not often that conferences give us the ability to attend all of the sessions that we want when we want to.  With the stellar lineup of presenters and the deep content that is flexible to each persons schedule I highly recommend attending this conference.

The Minnesota SQL Server user group (PASSMN) meeting is tonight at the Microsoft offices.  Come check us out to network and mingle with your peers and also for the opportunity to learn some new things.

Tonight’s topics are:

• SQL Server and Partition Alignment
• Wait-Time Based Performance Management

If you have questions or need more information on the meeting, feel free to contact me on twitter at@StrateSQL or though email at jstrate@digineer.com.

Just one week until the next PASSMN meeting.  As I mentioned last week, this month we’ll be focusing a  bit on performance.    And yeah, guys with lights coming out of his body means performance… or it might be just a great party.  Come out and join the discussion with a snack and some great presentations.

SQL Server and Partition Alignment & Wait-Time Based Performance Management

September 15th
2:30 PM Networking & Social
3:00 PM Announcements
3:15 PM - 5:15 PM Presentations

8300 Norman Center Drive
9th Floor
Bloomington, MN  55437

If that wasn’t enough to make you want to want to show up, here are the presentation details…

SQL Server and Partition Alignment

Speaker: Jimmy May, Microsoft
Now that SQL Server wait stats are formally documented & DMVs are available, disk partition alignment may be the best kept secret related to database performance optimization. In combination with stripe unit size & file allocation unit size, learn how you can increase I/O throughput by 10%, 30%, & under some circumstances even more. Many customers are unaware of partition alignment. Even experienced disk administrators may be unfamiliar with it. Explanations are often initially met with disbelief. Engineers familiar with the topic may underestimate its importance. For example, some customers think it is useful only for Microsoft Exchange Server. In fact, partition alignment is important for all servers from which high performance is expected, especially SQL Server. Each month I encounter customers with unaligned storage. Until existing misaligned partitions created using Windows Server 2003 or Windows 2000 Server are rebuilt properly, disk partition alignment will remain a relevant technology.

Jimmy May  is a Principal Performance Engineer for Microsoft’s Assessment, Consulting, & Engineering (A.C.E.) Services & works for Team Lee. He is the Founder & Visionary-in-Chief of SQL Server Pros & was formerly the Senior Database Architect for one of the world’s largest OLTP DBs. He is a founder & on the executive committees of both the Indiana Windows User Group & Indianapolis Professional Association for SQL Server . Jimmy lives in Indianapolis, Indiana with his lovely bride, Phyllis, & Fannie May the Wonder Dog. Subscribe to & read his blog at msdn.blogs.com/jimmymay. Contact him at jimmymay@microsoft.com or aspiringgeek@live.com.

Wait-Time Based Performance Management

Speaker: Janis Griffin, Confio
Using Wait Time Analysis and Wait Types is a newer method for tuning SQL Server instances. As a result, there is often confusion on exactly what the data means. The issue typically centers around the fact the wait time data is analyzed at the wrong level or the collected wait time data is not detailed enough. This presentation will focus on these problems and review several real-life case studies of using SQL Server Wait Type data coupled with Wait-Time based performance analysis to solve the most difficult performance related issues.

Janis Griffin has over 20 years of DBA experience including design, development and implementation of many critical database applications. Before coming to Confio, Janis held DBA positions primarily in the Telecom Industry (15 yrs), working with both real-time network routing databases and OLTP business to business applications.

While working on some questions for a project I am working on, I ran into an issue with Report Manager today.  I haven’t had the pleasure of running into this issue in a while and thought I would share the issue and the resolution because it affects both Windows 7 and Vista. 

Between this time and the previous few times I’ve dealt with this, I’ve spent more than time that I should on it and it does telegraph well into a second issue that I’ll talk about at the end.  And the easiest way for me to remember things is to write them down.

Your First Time

When going into Report Manager for the first time after installing it on my laptop I got the following screen:

Not a very exciting screen.  Reporting Services is nice and secure because, as a user, I couldn’t do anything.  But since doing something was what was needed, figuring this out was required.

I started by going out and checking the Reporting Services configuration.  When the configuration tool is opened in Windows 7 and Vista it asks the user if they want to allow the program to make changes to the computer.

Ah-ha! Security?!

I never setup any security for Reporting Services after the installation.  By default, Reporting Services makes the BUILTIN\Administrators account an administrator to Reporting Services.  I was running Internet Explorer but I wasn’t running it as a user, not as an administrator.  So while I was using

Setting Up The First User

To get started, start up Internet Explorer as an administrator.  To do this right-click on Internet Explorer in the menu and select “Run as administrator”.  BTW, you should check out Live Mesh if you haven’t it’s great for synchronizing folders.

Next go to Site Settings and select the Security tab.  You’ll notice that only the BUILTIN\Administrators have access after the installation.  This was the problem initially because Internet Explorer from regular launch didn’t acknowledge these permissions.

Select New Role Assignment and add yourself in as a  System Administrator.

Next go to Home and select Properties and go to the Security tab.

  Select New Role Assignment and add yourself in as a  Content Manager.

Now close Internet Explorer and open it back up.  Browse to the Reporting Services site and you should be able to do everything you expected to be able to do before.

Bad Default Behavior?

Now, you might be thinking this is all pointless and definitely a bug.  Truthfully it’s not, Internet Explorer is running under minimum permissions which you really want it to do.  The lower the permissions that you are cruising the internet with, the better.  Unless you like viruses then the worse. 

Also, the BUILTIN\Administrators account should be removed as a user and administrator from Reporting Services as soon as the “real” administrators have been added.  No sense leaving a whole in your Reporting Services site for administrators from other systems to get access to reports that they shouldn’t have access to.

Trying to figure out what to do between now and the PASS Summit this fall?  If you are hanging out in the Twin Cities, then your local PASS chapter has some new for you.  Announcing our first local full-day summit coming up at the end of this month.

September 25, 2009
8:30 PM - 3:45 PM
Microsoft Office - 9th Floor
8300 Norman Center Drive
Bloomington, MN
Register Here or visit http://www.mnssug.org/

The day will be choke full of FREE training, networking, food, refreshments and prizes.  We will be having 4 different break-out sessions along 3 different tracks. Expect sessions from MVPs, vendors and everyone in between.

Training for Everyone

There will be 4 session times with a choice of Application Development, Database Administration and Business Intelligence. Below is the schedule of the sessions.  The actual session names and presenters will be available shortly.

PASSMN SQL Summit 2009 Schedule

Special Thanks to PASS and Our Members

Part of the reason this event is possible is because last year we had a large number of members that went to the PASS Summit last year and who registered as member of our user group.  Because of that, PASS provided us with the sponsorship that is being used for our lunch.

Registration is Important

Due to the room sizes for this event we are limited to 120 attendees, be certain to register.

Just two weeks away is the next PASSMN meeting.  This month we’ll be focusing a  bit on performance and to emphasis this point I found a picture of someone moving so fast that light emanates from their body.

SQL Server and Partition Alignment & Wait-Time Based Performance Management

September 15th
2:30 PM Networking & Social
3:00 PM Announcements
3:15 PM - 5:15 PM Presentations

8300 Norman Center Drive
9th Floor
Bloomington, MN  55437

If that wasn’t enough to make you want to want to show up, here are the presentation details…

SQL Server and Partition Alignment

Speaker: Jimmy May, Microsoft
Now that SQL Server wait stats are formally documented & DMVs are available, disk partition alignment may be the best kept secret related to database performance optimization. In combination with stripe unit size & file allocation unit size, learn how you can increase I/O throughput by 10%, 30%, & under some circumstances even more. Many customers are unaware of partition alignment. Even experienced disk administrators may be unfamiliar with it. Explanations are often initially met with disbelief. Engineers familiar with the topic may underestimate its importance. For example, some customers think it is useful only for Microsoft Exchange Server. In fact, partition alignment is important for all servers from which high performance is expected, especially SQL Server. Each month I encounter customers with unaligned storage. Until existing misaligned partitions created using Windows Server 2003 or Windows 2000 Server are rebuilt properly, disk partition alignment will remain a relevant technology.

Jimmy May  is a Principal Performance Engineer for Microsoft’s Assessment, Consulting, & Engineering (A.C.E.) Services & works for Team Lee. He is the Founder & Visionary-in-Chief of SQL Server Pros & was formerly the Senior Database Architect for one of the world’s largest OLTP DBs. He is a founder & on the executive committees of both the Indiana Windows User Group & Indianapolis Professional Association for SQL Server . Jimmy lives in Indianapolis, Indiana with his lovely bride, Phyllis, & Fannie May the Wonder Dog. Subscribe to & read his blog at msdn.blogs.com/jimmymay. Contact him at jimmymay@microsoft.com or aspiringgeek@live.com.

Wait-Time Based Performance Management

Speaker: Janis Griffin, Confio
Using Wait Time Analysis and Wait Types is a newer method for tuning SQL Server instances. As a result, there is often confusion on exactly what the data means. The issue typically centers around the fact the wait time data is analyzed at the wrong level or the collected wait time data is not detailed enough. This presentation will focus on these problems and review several real-life case studies of using SQL Server Wait Type data coupled with Wait-Time based performance analysis to solve the most difficult performance related issues.

Janis Griffin has over 20 years of DBA experience including design, development and implementation of many critical database applications. Before coming to Confio, Janis held DBA positions primarily in the Telecom Industry (15 yrs), working with both real-time network routing databases and OLTP business to business applications.

The Minnesota SQL Server user group (PASSMN) meeting is tonight at the Microsoft offices.  Come check us out to network and mingle with your peers and also for the opportunity to learn some new things.

Tonight’s topics are:

• Syntactic Sugar Sweetens the SQL Server Pot
• Discover and Manage SQL Server in the Enterprise

If you have questions or need more information on the meeting, feel free to contact me on twitter at @StrateSQL or though email at jstrate@digineer.com.

Well… until next month of course.  If you’re in Minnesota and reading this… and don’t mind sharing some snacks with other like minded SQL professional… then come down and join us for this month’s SQL Server user group meeting.

We’ll be in a different location for this month – across the skyway from the normal meeting spot.  But that puts us right over a coffee shop… not a bad tradeoff for the coffee drinkers in the the group.

Without ado here are this month’s topics…

Syntactic Sugar Sweetens the SQL Server Pot & Discover and Manage SQL Server in the Enterprise

August 18, 2009
3:00 PM - 5:15 PM
2nd Floor Classroom - 8000 Building
8000 Norman Center Drive
Bloomington, MN

Syntactic Sugar Sweetens the SQL Server Pot

Speaker: Mark Knutson, Hennepin Faculty Associates

Among the many new features of SQL Server 2005 were some SQL additions that both improve performance as well as simplify complex queries. I have used these countless times and found them quite handy. These include Common Table Expressions, the analytical ranking functions, and aggregations. I will discuss how these work and provide some SQL examples.

Mark Knutson is the Senior Internet Architect at Hennepin Faculty Associates. In this capacity, he administers SQL Server and Oracle database servers, SharePoint Services, IIS, and Hyper-V virtualization server. He also leads a small development team which creates web applications using .NET and C#, and is working on the Cisco CCENT/CCNA certifications.

Discover and Manage SQL Server in the Enterprise

Speaker: Lara Rubbelke, Microsoft

Do you know where SQL Server is installed in your enterprise? Are you absolutely sure you know? Really? Are you looking for a free tool to help you learn where SQL Server is installed? Once you know, are you looking for a better way to manage all of the instances in your enterprise? Learn how to leverage SQL Server 2008 features and enhancements complemented with free tools to discover and centrally manage all of the SQL Server in your enterprise. This session will dive deep into the Microsoft Assessment and Planning Toolkit and the Central Management Server, with a special focus on configuring, securing and leveraging the CMS for multi-instance management and configuration.

Lara Rubbelke brings her passion for architecting, implementing and improving SQL Server solutions to the community as a Technology Specialist for Microsoft. Her expertise involves both OLTP and OLAP systems, data management, performance tuning, encryption, ETL, and the Business Intelligence lifecycle. She is an active board member of the local PASS (Professional Association for SQL Server) chapter and regularly delivers technical presentations at local, regional and national conferences, user groups and webcasts. Lara authored the Microsoft whitepaper detailing SQL Server 2005 Enterprise Edition features and was a contributing author of "Expert SQL Server 2005 Development". Prior to joining Microsoft, Lara was a Microsoft Most Valuable Professional (MVP).

The PASS Summit is coming up in just a few short months.  Have you had a chance to make your arrangement?  If you are like many, the down economy may be cutting into the budget that typically goes towards these events.

If you hadn’t heard, the screaming baby on the side wants you to know that Idera and MSSQLTips have teamed up with an All Expenses Paid Trip to the SQL PASS Summit.

The contest goes through August 25, but don’t wait until the last moment.  There are drinks at the happy hour with your name on it, you just need to claim them.

Coming up in a couple weeks is the next PASSMN meeting.  We’ll be in a different location for this month – across the skyway from the normal meeting spot.  But that puts us right over a coffee shop… not a bad tradeoff for the coffee drinkers in the the group.

Without ado here are this month’s topics…

Syntactic Sugar Sweetens the SQL Server Pot & Discover and Manage SQL Server in the Enterprise

August 18, 2009
3:00 PM - 5:15 PM
2nd Floor Classroom - 8000 Building
8000 Norman Center Drive
Bloomington, MN

Syntactic Sugar Sweetens the SQL Server Pot

Speaker: Mark Knutson, Hennepin Faculty Associates

Among the many new features of SQL Server 2005 were some SQL additions that both improve performance as well as simplify complex queries. I have used these countless times and found them quite handy. These include Common Table Expressions, the analytical ranking functions, and aggregations. I will discuss how these work and provide some SQL examples.

Mark Knutson is the Senior Internet Architect at Hennepin Faculty Associates. In this capacity, he administers SQL Server and Oracle database servers, SharePoint Services, IIS, and Hyper-V virtualization server. He also leads a small development team which creates web applications using .NET and C#, and is working on the Cisco CCENT/CCNA certifications.

Discover and Manage SQL Server in the Enterprise

Speaker: Lara Rubbelke, Microsoft

Do you know where SQL Server is installed in your enterprise? Are you absolutely sure you know? Really? Are you looking for a free tool to help you learn where SQL Server is installed? Once you know, are you looking for a better way to manage all of the instances in your enterprise? Learn how to leverage SQL Server 2008 features and enhancements complemented with free tools to discover and centrally manage all of the SQL Server in your enterprise. This session will dive deep into the Microsoft Assessment and Planning Toolkit and the Central Management Server, with a special focus on configuring, securing and leveraging the CMS for multi-instance management and configuration.

Lara Rubbelke brings her passion for architecting, implementing and improving SQL Server solutions to the community as a Technology Specialist for Microsoft. Her expertise involves both OLTP and OLAP systems, data management, performance tuning, encryption, ETL, and the Business Intelligence lifecycle. She is an active board member of the local PASS (Professional Association for SQL Server) chapter and regularly delivers technical presentations at local, regional and national conferences, user groups and webcasts. Lara authored the Microsoft whitepaper detailing SQL Server 2005 Enterprise Edition features and was a contributing author of "Expert SQL Server 2005 Development". Prior to joining Microsoft, Lara was a Microsoft Most Valuable Professional (MVP).

I mentioned this a couple months back.  If you are looking for a free event to discover more ways to write T-SQL here is an event for you.  The session will be hosted by yours truly and features T-SQL tricks and tips that I’ve picked up and want to spread the word on.

Here is the registration link.

And the details:

Course Overview

ITCare, delivered and hosted quarterly by our Partners is designed to keep you up to date on the cutting-edge technologies. ITCare is a FREE half-day technical seminar series created by IT Professionals for IT Professionals! We'll address the needs and issues today and show you how to make a real difference in the way that you work.

Objectives

While not everyone may not be a great painter, there is an art form to writing great SQL statements. In this session we'll go over tips and tricks in writing SQL. We'll also review some popular bad practices and provide demonstrations to better change those velvet Elvis's to Rembrandts. As every paint brush can't produce a masterpiece, the same goes for SQL statements. Writing good SQL code requires the right brush. After this session, you'll have a few more paintbrushes and probably throw out a few that are no longer useful.

1. Review and discussion of common coding methods can impact performance.
2. Discussion of uncommon coding methods that can be used to improve performance.
3. Review and application of existing T-SQL functions.

Audience

Junior to mid-level SQL Server developers

Prerequisites

Understanding of basic T-SQL coding skills.

Course Price

$0.00

Topics

Speaker: Jason Strate of Digineer, Inc. Jason Strate has been a Database Architect for over ten years. His experiences include designing and implementing both OLAP and OLTP solutions involving clustering servers, database monitoring and tuning, analysis services, DTS/Integration Services, Reporting Services, and Notification Services. Jason is actively involved with the local PASS chapter (SQL Server User Group); he is currently serving on the Executive Board and has presented at monthly meetings. Jason contributed to Microsoft's published white paper "Empowering Enterprise Solutions with SQL Server 2005 Enterprise Edition and actively blogs about SQL Server and related technologies. Also this past year, Jason participated in the development of Microsoft Certification exams for SQL Server 2008 and presented at the SSWUG SQL Server Launch Event and the last two SSWUG Virtual SQL Server Conferences.

One of the reasons I was digging around to find out how many reports were being executed for each folder the other day was that there were some subscription issues at the client.  The goal was to get a break down of when reports were being executed and generally who’s reports they were.

With that information in hand the topic quickly shifted to which reports were taking the most resources on the Reporting Services instance.  There are a number of ways to go about this but for this case I wanted to know how long it took from the when the report was requested until it was delivered to the user.

Like in the last post, this information can be gleamed from the ExecutionLog table in the Report Server database.  There are five columns that I use to get execution time information:

• TimeStart – Time that the report execution begins
• TimeEnd – Time that the report execution completes
• TimeDataRetrieval- Time spent running retrieving data from data sources
• TimeProcessing – Time spent processing the report
• TimeRendering - Time spent rendering the report in the requested format

Note: that the execution time for the report for TimeDataRetrieval, TimeProcessing, and TimeRendering does not equal the difference between TimeStart and TimeEnd.  This is because there are other operations performed while executing and returning reports that are not tracked.

As with the other script, this query also includes start and end dates.  Here is the script:

DECLARE @StartDate datetime
    ,@EndDate datetime

SELECT @StartDate = '1/1/2009'
    ,@EndDate = '12/31/2009'

SELECT c.name
    ,c.Path
    ,COUNT(*) as Executions
    ,CAST(AVG(DATEDIFF(ms, TimeStart, TimeEnd)*1.)AS decimal(12,2)) AS AverageExecutionTime
    ,CAST(SUM(el.TimeDataRetrieval)/(COUNT(*)*1.) AS decimal(12,2)) AS AverageTimeDataRetrieval
    ,CAST(SUM(el.TimeProcessing)/(COUNT(*)*1.) AS decimal(12,2)) AS AverageTimeProcessing
    ,CAST(SUM(el.TimeRendering)/(COUNT(*)*1.) AS decimal(12,2)) AS AverageTimeRendering
    ,CAST((SELECT ' Count = "' + CAST(COUNT(*) as varchar) + '"'
            + ' Value = "' + COALESCE(CAST(iel.Parameters as varchar(max)), '-NONE-') + '"'   as Parameters
        FROM dbo.ExecutionLog iel
        WHERE iel.TimeStart BETWEEN MIN(el.TimeStart) AND MAX(el.TimeStart)
        AND iel.ReportID = el.ReportID
        GROUP BY COALESCE(CAST(iel.Parameters as varchar(max)), '-NONE-')
        ORDER BY COUNT(*) DESC
        FOR XML PATH('')) as xml) as Parameters
FROM dbo.ExecutionLog el
    INNER JOIN dbo.Catalog c ON el.ReportID = c.ItemID
WHERE el.TimeStart BETWEEN @StartDate AND @EndDate
GROUP BY c.name, c.Path, el.ReportID
ORDER BY SUM(el.TimeDataRetrieval) DESC

Running the script on my laptop shows the reports that have been executed.  As you can tell, I am a reporting fiend.  Take a look at the output below":

Do note the last column.  This column has an XML document that summarizes all of the parameter variations that were used to with the report in the given timeframe with a count of the number of executions.  For my purposes, I looked at the reports that were executed most frequently.

When I clicked on the XML link in the first row, it opened the following document:

<Parameters> Count = "1" Value = "ShowDescription=False&amp;StartDate=01/01/2002 00:00:00&amp;EndDate=12/31/2003 00:00:00"</Parameters>
<Parameters> Count = "1" Value = "ShowDescription=False&amp;StartDate=1/1/2002 12:00:00 AM&amp;EndDate=12/31/2003 12:00:00 AM"</Parameters>
<Parameters> Count = "1" Value = "ShowDescription=False&amp;StartDate=1/15/2002 12:00:00 AM&amp;EndDate=12/31/2003 12:00:00 AM"</Parameters>
<Parameters> Count = "1" Value = "ShowDescription=False&amp;StartDate=6/15/2002 12:00:00 AM&amp;EndDate=12/31/2003 12:00:00 AM"</Parameters>

I’ve found that this query to be quite helpful in finding both the high execution time reports along with sample parameter strings that can be used to performance test the reports.

While working with a client a couple weeks back, I was asked to determine the count of reports that were being executed under each folder off the root directory on the Reporting Services instance.  Each of the folders off the root directory represented a department at the client.

For the output, I decided that I wanted to break out the reports executed by each hour of the day to see where the traffic was occurring.  Then the columns would be named after the first level folders off the root directory with counts of the number of reports executed under each folder.

The script is setup to take any date range you’d like.  In the case with the client, I was looking at that data for the first of the month.  In the script below, I expanded it to execute for the entire year.

DECLARE @StartDate datetime
    ,@EndDate datetime

SELECT @StartDate = '1/1/2009'
    ,@EndDate = '12/31/2009'

DECLARE @ColumnList table (FirstLevelFolder nvarchar(128))

INSERT INTO @ColumnList
SELECT DISTINCT REPLACE(LEFT(Path, CHARINDEX('/',Path + '/',2)),'/','')
FROM dbo.ExecutionLog el
      INNER JOIN dbo.Catalog c ON el.ReportID = c.ItemID
WHERE el.TimeStart BETWEEN @StartDate AND @EndDate
AND Path <> ''

DECLARE @Columns nvarchar(max)
      ,@SQL nvarchar(max)
      
SELECT @Columns = STUFF((
      SELECT ',' + QUOTENAME(FirstLevelFolder)
      FROM @ColumnList
      ORDER BY FirstLevelFolder
      FOR XML PATH('')      
      ), 1, 1, '') 

SET @SQL = 'SELECT StartHour, ' 
      + @Columns 
      + 'FROM (
            SELECT DATEPART(hh, TimeStart) as StartHour, REPLACE(LEFT(Path, CHARINDEX(''/'',Path,2)),''/'','''') as Path, COUNT(*) as Executions
            FROM dbo.ExecutionLog el
                  INNER JOIN dbo.Catalog c ON el.ReportID = c.ItemID
            WHERE el.TimeStart BETWEEN @StartDate AND @EndDate
            GROUP BY DATEPART(hh, el.TimeStart), REPLACE(LEFT(Path, CHARINDEX(''/'',Path,2)),''/'','''')) x ' 
      + 'PIVOT ' + 
      + '(SUM(Executions) FOR Path IN (' + @Columns + ')) AS pt;'; 

EXEC sp_executesql @SQL, N'@StartDate datetime, @EndDate datetime', @StartDate = @StartDate, @EndDate = @EndDate

There are two things to take away from this script.  The first is that logging in the reporting services provides a lot of information that can be used to see what the users are doing.  And second, with a little dynamic SQL data can be pivoted without knowing what the end columns will be.

This is an amazing technology concept.  It is a little ugly at the moment, but I bet that once it hits the design phase it will look pretty cool.  Maybe they’ll be able to shrink it into a pair of sunglasses.

If you’ve been coming to the monthly PASSMN meetings then you’ve heard that we are planning to have a full day SQL Server event.  The general details are that the event will be at Microsoft’s Offices on Friday, September 29, 2009.

We are still putting details together for the event and part of that means finding presenters.  And without further ado, if you’d like to present at the PASSMN SQL Summit 2009 (yeah, were hoping to be starting something here) please let us know by completing the following survey:

PASSMN SQL Summit 2009  - Call for Presenters

Maybe you aren’t much of a speaker but have a good idea what types of sessions we should be looking for.  We also have a survey for you:

PASSMN SQL Summit 2009  - Request for Sessions

If you are interested in the event and have questions, feel free to contact me (email | twitter).

About a year ago I was working on a project that required that data be extracted from one database and inserted into an database.  Not the most earth shattering concept.  This is done quite often and doesn’t necessarily require a discussion of foreign keys.

So why talk about them… well… in this case the source and the destination databases were identical. They had the same table and foreign key structure.  But no reason to be sold on this discussion quite yet… because it would be east to just build the ETL process around this.  It would need to be built with the ordering of the tables controlled with precedence constraints or inserts statements in the proper order.

So here’s the rub… this process needed to move data between hundreds of tables.  When hundreds of tables are in the discussion, I lose interest very quickly in trying to determine by hand how to order the tables.  I did in this case and looked for a method to accomplish this through foreign key meta data.

And of course, there wouldn’t be a post if there wasn’t an answer.  Well, maybe I’d post a question, but I not so nanner nanner.  The solution uses a function with a query to get the necessary data.  There might be better ways to do this, but this way worked quite well and had pretty decent performance.  One variation of this used a single CTE that I let run for a couple hours before I decided it was running sub-optimal.

There are two caveats with this solution:

1. Self-referencing foreign keys will cause a recursion error.  When row A needs to be inserted before row B can’t really be helped through metadata.  Since the environment I was working in didn’t have this issue, the solution didn’t address it either.
2. Circular-referencing foreign keys will also cause a recursion error.  There are situations where table A references table B which references table C which then references table A.  I run into these from time to time but not in the environment that this was designed for.

With the caveats out of the way, here is the solution:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ListFKParents]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ListFKParents]
GO

/*================================================================================
Function:    dbo.ListFKParents
Author:        Jason Strate
Date:        July 23, 2009

Synopsis:
    This function returns a comma separated list of parents for a table.

================================================================================
Revision History:
Date:        By            Description
----------------------------------------------------------------------------------
================================================================================*/
CREATE FUNCTION [dbo].[ListFKParents] (@ObjectID int) 
RETURNS table
AS 
RETURN (
WITH cteParentChild (Object_ID, parent_Object_ID, referenced_Object_ID, name) 
AS 
( 
SELECT fk.parent_Object_ID, t1.name, fk.referenced_Object_ID, t2.name 
FROM sys.foreign_keys fk 
    INNER JOIN sys.tables t1 ON fk.parent_Object_ID = t1.Object_ID 
    INNER JOIN sys.tables t2 ON fk.referenced_Object_ID = t2.Object_ID 
WHERE t1.Object_ID = @ObjectID
UNION ALL 
SELECT fk.parent_Object_ID, t1.name, fk.referenced_Object_ID, t2.name 
FROM sys.foreign_keys fk 
    INNER JOIN sys.tables t1 ON fk.parent_Object_ID = t1.Object_ID 
    INNER JOIN sys.tables t2 ON fk.referenced_Object_ID = t2.Object_ID 
    INNER JOIN cteParentChild cte ON cte.referenced_Object_ID = fk.parent_Object_ID 
WHERE fk.referenced_Object_ID <> fk.parent_Object_ID 
) 
SELECT (SELECT ',' + CONVERT(varchar(50), x.referenced_Object_ID) as [text()]
FROM cteParentChild x 
WHERE x.referenced_Object_ID <> @ObjectID
ORDER BY x.referenced_Object_ID 
FOR XML PATH ('')) + ',' AS FKParents
)
GO

IF OBJECT_ID('tempdb..#ParentChild') IS NOT NULL
    DROP TABLE #ParentChild 

CREATE TABLE #ParentChild 
    ( 
    object_id int
    ,name sysname
    ,parents varchar(max) 
    ) 

--List all tables and return a list of all of the parent tables to the table
INSERT INTO #ParentChild 
SELECT object_id, name, x.FKParents
FROM sys.tables t
    CROSS APPLY dbo.ListFKParents(object_id) x

IF OBJECT_ID('tempdb..#TableOrder') IS NOT NULL
    DROP TABLE #TableOrder 

CREATE TABLE #TableOrder 
    ( 
    object_id int
    ,name sysname
    ,rank int 
    ) 

DECLARE @Loop int 
SET @Loop = 0 

--Insert all tables with out parents as 0-level rank
INSERT INTO #TableOrder 
SELECT object_id, name, @Loop 
FROM #ParentChild 
WHERE parents IS NULL 

WHILE 1=1 
BEGIN 
    SET @Loop = @Loop + 1 

--Remove any items that are in #TableOrder and in #ParentChild
    DELETE FROM #ParentChild 
    WHERE object_id IN (SELECT object_id FROM #TableOrder) 

    ;WITH cteParentChild (varchar_object_id, object_id, name, parents) 
    AS 
    ( 
--Select items that match to parent #TableOrder in #ParentChild
    SELECT DISTINCT '%,' + CONVERT(varchar(50),t.object_id) + ',%', t.*
    FROM #ParentChild t 
        INNER JOIN ( 
                SELECT '%,' + CONVERT(varchar(50),object_id) + ',%' as object_id 
                FROM #TableOrder 
                ) x ON parents LIKE x.object_id 
    ) 
--Insert items into that have all parents in #TableOrder
    INSERT INTO #TableOrder 
    SELECT pc1.object_id, pc1.name, @Loop 
    FROM cteParentChild pc1 
        LEFT OUTER JOIN cteParentChild pc2 ON pc1.parents LIKE pc2.varchar_object_id 
    WHERE pc2.varchar_object_id IS NULL 

    IF @@RowCount = 0 
        BREAK 
END 

SELECT * 
FROM #TableOrder 
ORDER BY rank, name

Happy hunting.

Staying on top of patches for your applications can be important, especially when those patches can fix issues that are currently affecting your environment.  I’ve run into this from time to time with where the fix for an issue is related to the current state of updates to the SQL Server instance.

If the data on the SQL Servers wasn’t important, we wouldn’t be putting it into databases.  Because of that the servers need to be taken care of and kept up to date.  Like make-up, over the course of time things can happen.  Things like a little moisture around the eyes causing mascara to run or the discovery of a new bug that causes the server to require a reboot.  The fix for both is to apply an update.  Wouldn’t it be cool if lipstick on a server fixed bugs.

To stay on top of Service Packs and Cumulative Updates, check out the Microsoft SQL Server Release Services blog.  By following them you would have found out that Monday the following items came out:

1. Cumulative Update #3 for SQL Server 2008 SP1
2. Cumulative Update #6 for SQL Server 2008 RTM

One of SQL Server 2008 features I’ve been especially thrilled about is the Management Data Warehouse (MDW).  Just recently, Microsoft released a white paper  by Kalen Delany that goes into a bit a depth on MDW and covers some best practices and things to watch out for.

Happy reading!

Some people have dreams and they talk about them and an entire generation is changed.  Things line up and world is a lot different than the day before just because of a little dream.

I dream, too.  But my dreams aren’t so grand.  I tend to dream about SQL Server professionals coming together to network, tell jokes, listen, and learn about SQL Server and things related to working in a field full of data.

The nice thing about my dream is that I don’t have wait for the world to change.  My dream happens once a year at the PASS Community Summit.  You already registered, right?  But, wouldn’t you know…like most anyone that realizes a dream I want more.  I want it all.  I want it every day.

And I can get more.  And you can get more.  How does this happen you might ask?

Go ahead an ask…

The answer is by attending the SSWUG.ORG Summer Refresher Virtual Conference.  Check out these details:

120 sessions by industry experts in Business Intelligence, SQL Server, SharePoint and .NET Development are all available. These sessions are the best of the best - and selected by the community for this conference.

We are also including live sessions presented by Chris Shaw and Stephen Wynkoop detailing top 20 scripts, performance tips, and things that people should know about SQL Server.  In addition, Q&A sessions each day will be included where you can ask questions live.

You get to watch pre-recorded sessions and participate in a live Q&A with the presenter.  No waiting for the end of the session to answer your immediate question.  Get the answers when the question arises.  And with all of the other people joining in on the chat you get a conversation between everyone that goes beyond the Q&A with the presenter.

Now here’s the rub… it starts tomorrow and goes through Friday.  Oh my!  You may be thinking… you have meetings scheduled and can’t attend all day from the remainder of the week.

That is, like so, not a problem. yeah, I brought in valley  girl speak here, I’m digging deep.  After the sessions have been broadcast you can log back in and watch them again later and catch all of the content that you missed.

So get some popcorn and get registered.  Hope to see some people in my sessions.

Jobs aren’t a dime a  dozen like they used to be during the dot-com bubble.  These days the unemployment rate is heading to territory that hasn’t been seen in decades.  With some states seeming unemployment rates as high as 15%.  It becomes more important with every interview to differentiate yourself from the pack.

Differentiate Yourself

To help people differentiate themselves from others, Microsoft is introduced a program called Thrive.  This program is designed to help you align your career and goals with your skills and passions.

Part of the focus of the Thrive program is on certifications.  I’ve discussed why I think certifications are important in other posts,  so I won’t go into that here.  But the program also focuses on the soft skills that sometimes more important that the technical skills that you have.

Have you ever wondered-

• Do I sell my strong skills?  Or over sell my weak skills?
• Am I too focused on a specific technology?  Or not focused enough?
• Do I know where I want to be next year?  In 5 years?  In 10 years?
• Am I choosing my career or letting someone else hand it to me?
• Do I know the right people in my career?

All of these questions are important and if you don’t know who you are in your career, you can’t expect recruiters and the person across the table from you in your next interview to know who you are either. 

Get An Education

To aid in the all of this, Microsoft is currently hosting three webcasts (one of which already occurred but is available in their online library) that can help provide some great information and direction on building your career.

• Thrive Live! (Part 1 of 3) Create a Personal Marketing Plan for Career Success
• Thrive Live! (Part 2 of 3) Be Lucky! How to Discover Your Next Job
• Thrive Live! (Part 3 of 3) From Dressing to Dollars: Gain Interview and Negotiating Power

These webcasts will help you to develop a personal brand around your career.

But I Have A Job

A good argument against checking out the resources at Thrive is that you may already have a job.  On the other hand nearly 10% of the country is currently unemployed and I bet a lot of them thought that as well.  Becoming a statistic in the unemployment line is probably too late to start thinking about making sure you are a critical resource and in charge of your career.

Maybe you are completely safe in your career and there isn’t any reason at all to worry about losing your job and having to work with recruiters.  Then none of this is for you, right?  Dead wrong.

Everyday is an opportunity to sell yourself to your employer and at least once every year you will have some sort of meeting where you re-interview for your position.  What?!  You don’t do this?  Or do you?!

Every year you will get a chance to re-interview for your job.  Most people call this an annual review.  In this meeting, you talk about what you offer, what the company offers, and if things go well there is some discussion on compensation.  And to me this is an interview.  It’s just one where the chance of having the job is exceptionally high.

So I’ve come across a little doom and gloom here, so I’ll wrap up with one last reason to check out Thrive.

There is Free Stuff!

Not only can you improve your position in the market but there are also some offers for free stuff on the Thrive site. 

Enter to win a Microsoft training course worth $2,995(US)

Global Knowledge is a proud sponsor of the free training course giveaway on Microsoft's Thrive website. At the end of each month, we'll pick one lucky winner and giveaway one Microsoft Classroom or Virtual course valued at up to $2,995! That's right, the winner will get to choose the Microsoft course of their choice and get training FOR FREE!

And who doesn’t love free stuff.  Yes, I managed to place a bing search in this.

This week SSWUG is hosting another virtual conference featuring content for SQL, SharePoint, BI, and .NET professionals.  There is a lot going on and like their previous conferences you don’t have to leave the comforts of your beach bungalow to participate in the conference.

I encourage everyone to check this out…

We're excited to present our 5th vConference series! Attend and find out why the world is raving about these great technical events! Networking with attendees, seeing demonstrations, getting how-to information, Q&A with speakers, downloadable samples and transcripts, on-demand content and much more.

Here’s some of the great content we’re putting together for you

• Scripting techniques for SQL Server
• Powerful .NET development tips and tricks to help in your application development work
• SharePoint key points and techniques, things to optimize your installation
• Troubleshooting and tuning techniques
• Building reports, applying key Business Intelligence (BI) approaches to your work
• Outstanding Sponsorship Opportunities - Be a sponsor! Our sponsorship options are not what you're used to seeing - contact us today for exciting opportunities.

Our surveys reveal that an incredible 88% of attendees gained knowledge they could use immediately, and 99% said they would probably be recommending a SSWUG.org Virtual Conference in the future.

To register for the event… go here.

A while back I put together a number of posts around an index analysis query that I had put together to assist when I go out to clients.  If you are interested, the background for the query can be found in the following posts:

• Analyze Your Indexes Part 1
• Analyze Your Indexes Part 2
• Analyze Your Indexes Part 3
• Analyze Your Indexes Part 4
• Analyze Your Indexes Part 5
• Analyze Your Indexes Part 6

The query has proven to be quite useful for me… one problem with the query, though, was that the format wasn’t the most suitable for quick glance analysis.  It gave a lot of useful information but wasn’t in a format that made it easy to digest. 

To solve this I’ve put together a report that can be executed from within SQL Server Management Studio. 

If you are interested in this report, there is now a CodePlex project for the query and the custom report.  And it is aptly named IndexAnalysis.

Please comment and let me know if this is useful or if there are improvements that can help to improve it’s effectiveness.

There were some questions last night at the PASS MN user group meeting last night about the resource governor.  There must be some psychics working over at Microsoft because it seems just this morning a whitepaper came out on it.

Some highlights from the whitepaper from the whitepaper include (from the download page):

• How the Resource Governor works, including limitations and best practices.
• Methods for building a consolidation plan, and testing those methods.
• Techniques for preventing runaway queries, or at least minimizing their impact.
• Approaches to meeting service level agreements (SLAs) driven by response time and concurrency.
• Basic ideas about implementing a chargeback system to recover resource costs.
• Ways to monitor SQL Server using new Resource Governor functionality.

Thanks to Aaron Bertrand for mentioning the white paper this morning.

As I mentioned last week, there is still time to get register and come down for PASS MN’s second annual Ask the Experts Panel discussion.  The panel will be available to answer questions and help move the conversation along for all attendees.

I really like a great idea.  They have this way of going from a great idea to success.  When you hear a great idea it is always a good idea to share the idea – and let it spread like a virus.

Today, while I was going through and reading posts from Microsoft’s SQL Server bloggers, I found one of these great ideas.  Buck Woody talked about  combining a little volunteerism with resume building.

In a tight economy non-profits have to make tough decisions between their staffing and infrastructure versus the services that are provided.  By volunteering, we can help with those decisions by filling the void of one where the donations are no longer coming in.  And as is mentioned in Buck’s post, at the same time was can work on building our own resume.  I hope I don’t have to explain the benefit of a good resume.

To help inspire others to volunteer and build their resume, I found a few places in Minnesota where people can do some technical volunteering.

• Hands On Twin Cities
• Idealist
• Volunteer Match
• MN Headhunter

I found a few database related opportunities when I looked through these sites.  And encourage anyone looking to build their resume and do some volunteering to check them out.

If anyone knows any other resources or has a story to share along this topic, feel free to comment or e-mail me about it at jstrate@digineer.com.

There is still time to get registered…

So if you’ve got questions about about indexes and other SQL Server related topics bring them down.  We are having the second annual Ask the Experts Panel discussion:

Speakers:

• Lara Rubbelke, Microsoft

• Sanjay Kaul, Microsoft

• Dan English, Magenic

• Jason Strate, Digineer

After the popularity of last year's "Ask the Expert's", we decided to bring back the forum this year.  PASS members will have the opportunity to pose any burning questions they might have about SQL Server to our expert panel.  We will round things out with a couple tips or tricks about SQL Server from each of our panel experts.

The Panel

Lara Rubbelke brings her passion for architecting, implementing and improving SQL Server solutions to the community as a Technology Specialist for Microsoft. Her expertise involves both OLTP and OLAP systems, data management, performance tuning, encryption, ETL, and the Business Intelligence lifecycle. She is an active board member of the local PASS (Professional Association for SQL Server) chapter and regularly delivers technical presentations at local, regional and national conferences, user groups and webcasts. Lara authored the Microsoft whitepaper detailing SQL Server 2005 Enterprise Edition features and was a contributing author of "Expert SQL Server 2005 Development". Prior to joining Microsoft, Lara was a Microsoft Most Valuable Professional (MVP).

Sanjay Kaul is a database professional with over 15 years of experience in various database technologies. In his current position as a SQL Server Technology Specialist, he spends his day as an ambassador for Microsoft - promoting, evangelizing, and assisting customer realize their potential using SQL Server Technologies, and evenings furthering his culinary skills.

Dan English - Dan is a Principal Consultant with Magenic and has been developing with Microsoft technologies for over 12 years and has over 5 years experience with Data Warehousing and Business Intelligence. He has been working with SQL Server since version 6.5 and now with 2008 looking towards the Kilimanjaro release. Dan has screencasts of SQL Server 2008 and PerformancePoint Server on YouTube and Soapbox ( keyword search - Magenic) and is an avid blogger (http://denglishbi.spaces.live.com). Dan is fully certified with MS SQL Server 2005 and 2008 Business Intelligence. He enjoys keeping in contact with the community at large responding to forum postings on the Microsoft forums and SQL Server Central areas. Dan is also part of the PASSMN 2009 Executive Board. 

Jason Strate, Digineer Inc, has been a Database Architect for over ten years. His experiences include designing and implementing both OLAP and OLTP solutions involving clustering servers, database monitoring and tuning, analysis services, DTS/Integration Services, Reporting Services, and Notification Services. Jason is actively involved with the local PASS chapter (SQL Server User Group); he is currently serving on the Executive Board and has presented at monthly meetings. Jason contributed to Microsoft’s published white paper "Empowering Enterprise Solutions with SQL Server 2005 Enterprise Edition" and actively blogs about SQL Server and related technologies. Also this year, Jason participated in the development of Microsoft Certification exams for SQL Server 2008, presented at the SSWUG SQL Server Launch Event and is presenting in upcoming SSWUG SQL Server Conference.

One of the things I get to do pretty often is to visit with clients and discuss how they’ve implemented their SQL Server environment.  And if the title of this post is any indication of the contents there are a number of issues that I tend to be able to make recommendations on in all of these environments.

Below are five of the most common tempdb items that I come across:

1. Just a single tempdb data file: As a rule of thumb, Tempdb should have as many data files as there are CPUs/cores for the SQL Server instance.  This prevents helps contention that can occur during peak tempdb activity.  Of course, too many files can be a problem as well so if you are thinking of adding more than 8 data files, look at doing some testing to verify the additional files are not causing performance issues of their own.
2. Multiple tempdb transaction logs: The transaction log, of course, serves a much different purpose than the data files.  It’s there to log all of the transactions from tempdb and performs this function sequentially.  No matter how many files you have only one will be active at any time.  Having extra files won’t necessarily change performance or how tempdb behaves but it’s not going to provide any benefit.
3. Size Doesn’t Matter: The data files for tempdb should all be the same size and large enough to handle the workload for all 24 hours of the day.  If one of the files grows for some reason, the other files should be grown as well to keep all of the files the same size.  This helps SQL Server distribute the work more better evenly across the tempdb data files.  Also, there are a number of operations that utilize tempdb – make sure you have the room.  Skipping DBCC CHECKS because tempdb is too small is a recipe for disaster a la mode.
4. Tempdb and user database data files sharing a disk: Tempdb should be separated and isolated from user databases.  This creates contention between the user databases and tempdb.  Contention with temporary objects when executing a query and reading data from a user data typically has one unfortunate result… poor performance.  And no one likes that…
5. Tempdb on the slowest disk: Ugh!  Just because the databases on the server aren’t creating temporary tables doesn’t mean tempdb isn’t being used.  Queries that use operations such as Sort, Hash Match, and Spool will use tempdb and the faster the disk for tempdb the faster the performance of the query.  Tempdb has a lot of activity from all of the databases on the server and if it can’t push it’s IO quickly nearly everything will suffer.

I’ve kept these items fairly short and without a lot of detail because if you are doing any of these you need to read up on tempdb performance in Books Online and read the whitepaper Working with Tempdb.  Tempdb is a crucial resource in the SQL Server environment and it’s configuration should be a first thought rather than an after thought to prevent from having it bite you in the face.

Taking care of these kinds of easy configuration issues will help make certain that your tempdb is Harder, Better, Faster, Stronger.

Today a tweet came across my screen that mentioned that you can’t script out data from SQL Server tables.

But… actual you can if you have SQL Server Management Studio 2008 installed.  It’s one of the new options under the task Generate Scripts.  And it works very well and is easy to do.

To demonstrate, I’ll walk through the steps to create scripts for all of the data in the table HumanResource.Department in the AdventureWorks database.

First, select the Tasks—> Generate Scripts… option from the right-click menu under databases:

Next select the database that you need to script data from.  In this case I selected the AdventureWorks database and then selected Next.

For this example, I only want to return the data from the table.  The options (show below) should be set to FALSE:

• Script Create
• Script Defaults
• Script Extended Properties
• Script Check Constraints
• Script Foreign Keys
• Script Primary Keys
• Script Unique Keys

Also, the option for Script Data should be changed to TRUE.  This is also show below.  Then select Next.

Now that the work has been determined, it is time to pick the objects to generate scripts on.  In this example, I am only selecting tables since I only want the data.  Then select Next.

Select the table to get the data from and select Next again.  I think the hardest thing in the little tutorial is all these Next buttons.

Now you are ready to get the data from the table.  You can put it into any of the containers that are typically available.  I left the default of New Query Window.  After this decision, you guessed it… select Next again.

The script will generate all of your INSERT statements (as shown in the window below).  It also includes the IDENTITY_INSERT as needed.

USE [AdventureWorks]
GO
/****** Object:  Table [HumanResources].[Department]    Script Date: 07/07/2009 17:15:08 ******/
SET IDENTITY_INSERT [HumanResources].[Department] ON
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (1, N'Engineering', N'Research and Development', CAST(0x00008C6900000000 AS DateTime))
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (2, N'Tool Design', N'Research and Development', CAST(0x00008C6900000000 AS DateTime))
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (3, N'Sales', N'Sales and Marketing', CAST(0x00008C6900000000 AS DateTime))
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (4, N'Marketing', N'Sales and Marketing', CAST(0x00008C6900000000 AS DateTime))
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (5, N'Purchasing', N'Inventory Management', CAST(0x00008C6900000000 AS DateTime))
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (6, N'Research and Development', N'Research and Development', CAST(0x00008C6900000000 AS DateTime))
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (7, N'Production', N'Manufacturing', CAST(0x00008C6900000000 AS DateTime))
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (8, N'Production Control', N'Manufacturing', CAST(0x00008C6900000000 AS DateTime))
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (9, N'Human Resources', N'Executive General and Administration', CAST(0x00008C6900000000 AS DateTime))
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (10, N'Finance', N'Executive General and Administration', CAST(0x00008C6900000000 AS DateTime))
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (11, N'Information Services', N'Executive General and Administration', CAST(0x00008C6900000000 AS DateTime))
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (12, N'Document Control', N'Quality Assurance', CAST(0x00008C6900000000 AS DateTime))
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (13, N'Quality Assurance', N'Quality Assurance', CAST(0x00008C6900000000 AS DateTime))
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (14, N'Facilities and Maintenance', N'Executive General and Administration', CAST(0x00008C6900000000 AS DateTime))
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (15, N'Shipping and Receiving', N'Inventory Management', CAST(0x00008C6900000000 AS DateTime))
INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (16, N'Executive', N'Executive General and Administration', CAST(0x00008C6900000000 AS DateTime))
SET IDENTITY_INSERT [HumanResources].[Department] OFF

I’ve used this a couple times for generating deploy scripts and it’s worked very well.  A very nice addition to SQL Server Management Studio 2008.

Everyone likes taking some time to relax.  You’re sitting out in the sunshine or on a beach and the waitress is bringing you your favorite beverage.  The company and conversation is great and it seems like the perfect day.  And then… suddenly without warning… your drink is gone.

You were having a great time and the waiter, who should be monitoring for this situation, failed to do so.  As the conversation is interrupted by this horror of this situation you look around the table and start sipping on what’s left of the water you had with dinner.

What if there wasn’t water to switch to?  What would have happened?

In much the same way, DBAs are the waiters and waitresses for their SQL Servers.  Instead of serving beverages, we provide space to the database files for our environments.  In most cases the conversations (transactions) in the database will be safe from harm because there will be extra water (space) laying around.

What if there isn’t and you haven’t planned ahead for this scenario?  It would be worse to have you actual drinks and evening with friends interrupted because the SQL Servers are offline because they ran out of the space necessary to grow.

I’m Growing and Taking More Beverages With Me

In a previous post I talked about knowing when a file growth has occurred.  This is the after-the-fact alert to inform that things may not be going as planned.  Before you get to that point,  it would be nice to have something in place that informs that unplanned growth may occur.

To that end…

How Many Kegs Are Left In the Cooler?

To help alleviate evening interruptions, I put together a file space monitoring alert for the  DBADiagnostics database a while back.  This alert is usually scheduled through a SQL Server Agent job and is executed in the morning before the day starts.  This allows me to see which databases may need some action through the day and I get all day minus meetings, emergencies, meetings, and other work to get this taken care of.

To cut to the chase, here is the procedure:

USE [DBADiagnostics]
GO

IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Alert')
    EXEC('CREATE SCHEMA [Alert] AUTHORIZATION [dbo]')
GO

IF OBJECT_ID('Alert.FilegroupSpaceAvailable') IS NOT NULL
    DROP PROCEDURE [Alert].[FilegroupSpaceAvailable]
GO

/*================================================================================
Procedure:    Alert.FilegroupSpaceAvailable
Author:        Jason Strate
Date:        2005-04-05

Synopsis:
    Alert to monitor amount of available space within database datafiles. 

    This process does not take into account space of transaction logs. Database 
    recovery strategy should account for those files.

Parameters:
    @PercentFull: Threshold percenteage for space used in data file
    @MBEmpty: Threshold MB value for space remaining in each data file.  Prevents
        false-positive alerts for large databases
    @ToAddress: E-mail address to send the alert
    @SubjectWarning: Prefix to e-mail message to code or rate the alert

================================================================================
Revision History:
Date:        By            Description
----------------------------------------------------------------------------------
================================================================================*/
CREATE PROCEDURE [Alert].[FilegroupSpaceAvailable]
    (
    @PercentFull decimal(5,2) = 90,
    @MBEmpty int = 2500,
    @ToAddress varchar(255),
    @SubjectWarning varchar(255) = ''
    )
AS

SET NOCOUNT ON

--Declare variables section
DECLARE @db_full_pages float,
    @db_full_percentage float,
    @SQL nvarchar(4000),
    @db_size float

IF @PercentFull NOT BETWEEN 1 AND 99 OR @PercentFull IS NULL
BEGIN
   RAISERROR('@PercentFull must be a value between 1 and 99.', 16, 1)
   RETURN -1
END 

IF @MBEmpty IS NULL
BEGIN
   RAISERROR('@MBEmpty can not be null.', 16, 1)
   RETURN -1
END 

IF OBJECT_ID('tempdb..#FileStats') IS NOT NULL
    DROP TABLE #FileStats

CREATE TABLE #FileStats
    (
    FileID int,
    FileGroup int,
    TotalExtents bigint,
    UsedExtents bigint,
    Name varchar(255),
    [FileName] varchar(255)
    )

IF OBJECT_ID('tempdb..#Output') IS NOT NULL
    DROP TABLE #Output

CREATE TABLE #Output
    (
    DatabaseName sysname,
    ID int IDENTITY(1,1),
    FileGroup varchar(255),
    FileName varchar(255),
    FilePath varchar(255),
    SpaceAvailable int,
    SpaceUsed int,
    PercentUsed decimal(12,6)
    )

DECLARE @DBName nvarchar(128)

DECLARE CURS_DATABASE CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.databases
    WHERE source_database_id IS NULL
    AND state_desc = 'ONLINE'
    AND is_read_only = 0
    AND is_in_standby = 0
    AND name NOT IN ('model', 'master', 'msdb')
    OPEN CURS_DATABASE

FETCH NEXT From CURS_DATABASE INTO @DBName

WHILE @@Fetch_Status = 0
BEGIN
    Set @SQL = 'Use [' + @DBName + '];
        Exec(''DBCC showfilestats'')'

    DELETE FROM #FileStats

    INSERT INTO #FileStats
    EXEC sp_ExecuteSQL @SQL

    Set @SQL = 'Use [' + @DBName + '];
        SELECT ''' + @DBName + '''
            ,RTRIM(fg.name)
            ,RTRIM(f.name)
            ,RTRIM(f.physical_name)
            ,(TotalExtents * 64) / 1024.
            ,(UsedExtents * 64) / 1024.
            ,100 * CAST(((UsedExtents * 64) / 1024.)/((TotalExtents * 64) / 1024.) as decimal(12,6))
        FROM #FileStats fs
            INNER JOIN sys.database_files f On fs.Name = f.Name COLLATE SQL_Latin1_General_CP1_CI_AS
            INNER JOIN sys.filegroups fg On    f.data_space_id = fg.data_space_id 
        WHERE CAST(((UsedExtents * 64) / 1024.)/((TotalExtents * 64) / 1024.) as decimal(12,6)) > ' + Convert(varchar, @PercentFull/100) + '
        AND ((TotalExtents * 64)) / 1024. - ((UsedExtents * 64) / 1024.) <=  ' + Convert(varchar, @MBEmpty) + '
        ORDER BY fg.name, f.name'

    INSERT INTO #Output
    EXEC sp_ExecuteSQL @SQL

    FETCH NEXT From CURS_DATABASE INTO @DBNAME
END

CLOSE CURS_DATABASE
DEALLOCATE CURS_DATABASE

IF EXISTS (SELECT * FROM #Output)
BEGIN
    DECLARE NOTIFICATION_CURS CURSOR LOCAL FAST_FORWARD FOR
        SELECT DatabaseName,
            FileGroup,
            FileName,
            FilePath,
            SpaceAvailable,
            SpaceUsed,
            PercentUsed
        FROM #Output

    DECLARE @DatabaseName nvarchar(255),
        @FileGroup nvarchar(255),
        @FileName nvarchar(255),
        @FilePath nvarchar(255),
        @SpaceAvailable nvarchar(255),
        @SpaceUsed nvarchar(255),
        @PercentUsed nvarchar(255),
        @Subject nvarchar(255),
        @Body nvarchar(max)

    SET @Subject = LTRIM(@SubjectWarning + SPACE(1) + @@SERVERNAME) + ': FileGroup Space Available Notification'
    SET @Body = 'The following database files belong to filegroups that nearly full.  Please increase the size of the files to prevent unscheduled file growth.<p>' +
            '<table border=1 cellspacing=0 cellpadding=0 align=center>' + 
            '<tr><td>Database Name</td><td>File Group</td><td>File Name</td><td>File Path</td><td>Space Available</td><td>Space USed</td><td>Percent Used</td></tr>'

    OPEN NOTIFICATION_CURS
    FETCH NEXT FROM NOTIFICATION_CURS INTO @DatabaseName, @FileGroup, @FileName, @FilePath, @SpaceAvailable, @SpaceUsed, @PercentUsed

    While @@Fetch_Status = 0
    BEGIN
        Set @Body = @Body + '<tr><td>' + @DatabaseName + '</td><td>' + @FileGroup + '</td><td>' + @FileName + '</td><td>' + @FilePath 
            + '</td><td>' + @SpaceAvailable + '</td><td>' + @SpaceUsed + '</td><td>' + @PercentUsed + '</td></tr>'

        FETCH NEXT FROM NOTIFICATION_CURS INTO @DatabaseName, @FileGroup, @FileName, @FilePath, @SpaceAvailable, @SpaceUsed, @PercentUsed
    END

    CLOSE NOTIFICATION_CURS
    DEALLOCATE NOTIFICATION_CURS

    Exec msdb.dbo.sp_send_dbmail 
        @recipients = @ToAddress,
        @subject = @Subject,
        @body = @Body,
        @body_format =  'HTML'
END
GO

The parameters for the procedure are as follows:

• @PercentFull: Threshold percenteage for space used in data file
• @MBEmpty: Threshold MB value for space remaining in each data file.  Prevents false-positive alerts for large databases
• @ToAddress: E-mail address to send the alert
• @SubjectWarning: Prefix to e-mail message to code or rate the alert

Getting It Taken Care Of

This alert should not be used an a replacement to planning for the future growth of your databases.  Instead, the current and historical database sizes should be used to accommodate for future growth and should be reviewed at regular intervals.

Notifications of this sort are designed to let you know that unplanned growth may be possible and the historical growth rates ay no longer be valid based on the receipt of the alert.  Always know where you expect the database size to be and use this notification to determine when pre-determined safety threshold have been exceeded.

Nobody wants to run out of space or beer.

Caution:  The procedure uses the undocumented command DBCC SHOWFILESTATS.  Use this in production with caution.  It is the same process that SQL Server Management Studio uses to get this information.  But the command can change at any time without notice.

Got that question about string theory that you just can’t seem to get answered?  Well, I don’t think the next PASSMN meeting will have your answer, but I bet it can answer some of your questions about indexes and other SQL Server related topics.

We are having the second annual Ask the Experts Panel discussion:

Speakers:

• Lara Rubbelke, Microsoft

• Sanjay Kaul, Microsoft

• Dan English, Magenic

• Jason Strate, Digineer

After the popularity of last year's "Ask the Expert's", we decided to bring back the forum this year.  PASS members will have the opportunity to pose any burning questions they might have about SQL Server to our expert panel.  We will round things out with a couple tips or tricks about SQL Server from each of our panel experts.

The Panel

Lara Rubbelke brings her passion for architecting, implementing and improving SQL Server solutions to the community as a Technology Specialist for Microsoft. Her expertise involves both OLTP and OLAP systems, data management, performance tuning, encryption, ETL, and the Business Intelligence lifecycle. She is an active board member of the local PASS (Professional Association for SQL Server) chapter and regularly delivers technical presentations at local, regional and national conferences, user groups and webcasts. Lara authored the Microsoft whitepaper detailing SQL Server 2005 Enterprise Edition features and was a contributing author of "Expert SQL Server 2005 Development". Prior to joining Microsoft, Lara was a Microsoft Most Valuable Professional (MVP).

Sanjay Kaul is a database professional with over 15 years of experience in various database technologies. In his current position as a SQL Server Technology Specialist, he spends his day as an ambassador for Microsoft - promoting, evangelizing, and assisting customer realize their potential using SQL Server Technologies, and evenings furthering his culinary skills.

Dan English - Dan is a Principal Consultant with Magenic and has been developing with Microsoft technologies for over 12 years and has over 5 years experience with Data Warehousing and Business Intelligence. He has been working with SQL Server since version 6.5 and now with 2008 looking towards the Kilimanjaro release. Dan has screencasts of SQL Server 2008 and PerformancePoint Server on YouTube and Soapbox ( keyword search - Magenic) and is an avid blogger (http://denglishbi.spaces.live.com). Dan is fully certified with MS SQL Server 2005 and 2008 Business Intelligence. He enjoys keeping in contact with the community at large responding to forum postings on the Microsoft forums and SQL Server Central areas. Dan is also part of the PASSMN 2009 Executive Board. 

Jason Strate, Digineer Inc, has been a Database Architect for over ten years. His experiences include designing and implementing both OLAP and OLTP solutions involving clustering servers, database monitoring and tuning, analysis services, DTS/Integration Services, Reporting Services, and Notification Services. Jason is actively involved with the local PASS chapter (SQL Server User Group); he is currently serving on the Executive Board and has presented at monthly meetings. Jason contributed to Microsoft’s published white paper "Empowering Enterprise Solutions with SQL Server 2005 Enterprise Edition" and actively blogs about SQL Server and related technologies. Also this year, Jason participated in the development of Microsoft Certification exams for SQL Server 2008, presented at the SSWUG SQL Server Launch Event and is presenting in upcoming SSWUG SQL Server Conference.

I received an e-mail from Microsoft today presenting me with the 2009 Microsoft MVP Award for the work I’ve done with SQL Server.  I thrilled beyond believe to have this honor.  I think this means I should buy myself a new jet ski… is three of them really enough?

Thanks to everyone that has helped me as I’ve played with SQL Server over the years.  From my start at US Bank to my current role at Digineer there are so many people to mention that I’m afraid I’ll miss someone if I start listing people individually. 

Also… my ability to focus on anything today is gone.

I haven’t blogged much since getting back from vacation.  It seems like a good restart after the two week hiatus would be to address an issue that has arisen countless times.  And while addressing it, I’ll put it into the DBADiagnostics database that I’ve blogged about a few times before.

Sudden File Growth

In a number of SQL Server environments that I’ve worked in there either isn’t a method for monitoring file size or the process is more of a rubber stamp morning check.  The DBA gets in and looks at the size of the files and if there isn’t a log of changes to update nothing is really done and changes aren’t investigated.

In most cases this won’t be a problem.  For instance, if you’ve gone out and pre-grown your data and log files to appropriate sizes then nothing will grow and there is nothing to see here.

In cases were the unexpected happens, though, database files can and will grow.  And in the worst of these cases, which only occur at night or on vacations, the files will grow to a point where there is no longer any disk space available.  And if your annual review is next week, this will happen to the log file and force your database offline.

Knowing Is Half the Battle

In these types of situations, I like to recall the last couple minutes of the GI Joe epos ides that I watched as a kid.  They almost always ended with the quote, “Knowing is half the battle!  Yo, Joe!”.  If I know that a file growth has happened then I can do something about it.

Now the best case is to know, monitor, and plan for upcoming file growths.  This is what might be called a best practice and if you’re not doing it I’d really recommend putting a process in place.  But we need to be prepared for the unexpected.  And even in the most best, rock solid environment, I’d recommend a file growth monitoring process.

The Solution

This procedure for the DBADiagnostics database differs slightly from previous alerts.  Instead of sending out an e-mail that aggregates all of the changes an error is raised for each file change in question.  This method was selected because this allows the process to be tool agnostic.

If the client has a log file monitoring process, then the error can be picked up that way.  Or if all of the alerts are generated from the SQL Server instances, then a SQL Agent Alert can be created to notify people about the issue.

USE [DBADiagnostics]
GO

IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Monitor')
    EXEC('CREATE SCHEMA [Monitor] AUTHORIZATION [dbo]')
GO

IF OBJECT_ID('Monitor.FileGrowth') IS NULL
BEGIN
    CREATE TABLE [Monitor].[FileGrowth]
        (
        DatabaseName sysname NOT NULL,
        DatabaseFileName nvarchar(260) NOT NULL,
        FileSizeMB decimal(18,3) NOT NULL
        CONSTRAINT PK_MonitorFileGrowth PRIMARY KEY(DatabaseName, DatabaseFileName)
        )
END

IF OBJECT_ID('Alert.FileGrowth') IS NOT NULL
    DROP PROCEDURE [Alert].[FileGrowth]
GO

/*================================================================================
Procedure:    [Alert].[FileGrowth] 
Author:        Jason Strate
Date:        2007-11-14

Synopsis:
    Procedures monitors the size of each file for all user databases and tempdb.  In
    the event of file growrh for any of the monitored databases, a error is raised
    that can be captured through either tools monitoring SQL Servers log files or 
    through SQL Agent Alerts.
         

================================================================================
Revision History:
Date:        By            Description
----------------------------------------------------------------------------------
================================================================================*/
CREATE PROCEDURE [Alert].[FileGrowth] 

As

SET NOCOUNT ON

-- Validate that necessary error message exists
IF NOT EXISTS (SELECT * FROM master..sysmessages WHERE error = 70000)
    EXEC master..sp_addmessage  @msgnum = 70000, @severity = 12, @with_log = 'true', 
        @msgtext = 'File growth has occured in the database %s on the file %s.  The size has increased from %d to %d.  If the file growth was unplanned, please review for unexpected issues.', @replace = 'REPLACE'

-- Insert files for each database that are not currently being watched
INSERT INTO [Monitor].[FileGrowth] (DatabaseName, DatabaseFileName, FileSizeM
SELECT d.name, mf.name, CAST(mf.size as float)*8/1024
FROM sys.databases d
    INNER JOIN sys.master_files mf ON d.database_id = mf.database_id
    LEFT OUTER JOIN Monitor.FileGrowth fg ON d.name = fg.DatabaseName AND mf.name = fg.DatabaseFileName
WHERE d.state_desc = 'ONLINE'
AND source_database_id IS NULL
AND d.name NOT IN ('model', 'master', 'msdb')
AND fg.FileSizeMB IS NULL

-- Delete databases that no longer exist
DELETE FROM Monitor.FileGrowth 
FROM Monitor.FileGrowth fg 
    LEFT OUTER JOIN sys.databases d ON fg.DatabaseName = d.name
WHERE d.name IS NULL

DECLARE @FileGrowthDelta table
    (
    DatabaseName sysname
    ,DatabaseFileName nvarchar(260) 
    ,OldFileSizeMB decimal(18,3)
    ,NewFileSizeMB decimal(18,3)
    )

-- Update files that changed size and output delta rows
UPDATE fg
SET FileSizeMB = CAST(CAST(mf.size as float)*8/1024 as decimal(18,3))
OUTPUT INSERTED.DatabaseName, INSERTED.DatabaseFileName, INSERTED.FileSizeMB, DELETED.FileSizeMB
INTO @FileGrowthDelta
FROM Monitor.FileGrowth fg 
    INNER JOIN sys.databases d ON d.name = fg.DatabaseName 
    INNER JOIN sys.master_files mf ON d.database_id = mf.database_id AND mf.name = fg.DatabaseFileName
WHERE d.state_desc = 'ONLINE'
AND source_database_id IS NULL
AND d.name NOT IN ('model', 'master', 'msdb')
AND FileSizeMB <> CAST(CAST(mf.size as float)*8/1024 as decimal(18,3))

--Declare variables section for triggering error event
DECLARE @DatabaseName sysname
    ,@DatabaseFileName nvarchar(260) 
    ,@OldFileSizeMB int
    ,@NewFileSizeMB int

--For each database name in sysdatabases
DECLARE ALTER_FILE_GROWTH_CURSOR CURSOR LOCAL FAST_FORWARD FOR 
    SELECT DatabaseName 
        ,DatabaseFileName 
        ,OldFileSizeMB 
        ,NewFileSizeMB
    FROM @FileGrowthDelta

OPEN ALTER_FILE_GROWTH_CURSOR
FETCH NEXT FROM ALTER_FILE_GROWTH_CURSOR INTO @DatabaseName, @DatabaseFileName, @OldFileSizeMB, @NewFileSizeMB

WHILE @@FETCH_STATUS = 0
BEGIN
    RAISERROR(70000, 0, 1, @DatabaseName, @DatabaseFileName, @OldFileSizeMB, @NewFileSizeM WITH LOG
    FETCH NEXT FROM ALTER_FILE_GROWTH_CURSOR INTO @DatabaseName, @DatabaseFileName, @OldFileSizeMB, @NewFileSizeMB    
END

CLOSE ALTER_FILE_GROWTH_CURSOR
DEALLOCATE ALTER_FILE_GROWTH_CURSOR
GO

EXEC [Alert].[FileGrowth]

In the next post, I’ll outline the process that I use to check to see if there are any files that may need be running out of available space.

I tweeted it a couple Friday’s ago when I got the notice but I was on vacation and had made the decision to avoid blogging anything during that time. 

But since vacation is over, here’s the session that got accepted:

Getting To Know Your Indexes

Without proper indexing SQL Server can be hard pressed to create efficient and performant execution plans.  Dynamic Management Views (DMV) and system views provide a slew of information about indexes that can be used to analyze indexes within SQL Server.  In this session we’ll go under the hood of SQL Server to look at DMVs and system views to know what indexes you have, should have, and how they feel about the way applications are treating them.

Prerequisites
Understanding of performance tuning needs
Experience with Database design

Goals:
1.    Identify methods to analyze current and potential indexes
2.    Learn how to alleviate stress found on indexes
3.    Demonstrate methods for tuning indexes

Have you registered for the PASS Summit yet?  If not and you need to convince someone, here are some good justifications for going.  My main motivation for going is the opportunity to meet new people in the community that have different visions on implementing SQL Server.  Also

The one last reason to go is project “jager”.