Strate SQL

Moving to Blog JasonStrate.com

2009-12-20T22:05:00Z

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

December PASSMN Meeting This Week

2009-12-06T23:00:00Z

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.

Does Your Stored Procedure Grant Itself Permissions?

2009-12-03T13:00:00Z

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:

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.
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.

Deadlocks on exchangeEvent and threadpool

2009-12-02T13:00:00Z

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.

December PASSMN Meeting

2009-11-18T19:52:00Z

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

Show Up And Network

If You Don’t Know Where You’ve Been…

2009-11-18T15:00:00Z

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.

Hi, Eric Stratton, Rush chairman…

2009-11-09T13:46:00Z

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…

Tap, Tap, Tap – SSWUG Conference Starts Today

2009-10-21T11:00:00Z

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.

Are You Registered?

2009-10-14T14:00:00Z

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.

Missing Blogger

2009-10-12T16:05:00Z

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

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.
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.
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.

Hopefully I can get this interesting again… and very soon.

User Group Study Groups… Anyone?

2009-10-07T16:10:00Z

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:

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

SQL Server Certification Discounts

2009-10-07T12:18:00Z

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!

Checking EXISTS – What’s Your Practice?

2009-10-06T12:00:00Z

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.

To Be Or Not To Be In the Memory Buffer

2009-10-05T13:56:00Z

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.

Free SQL Server Security Book

2009-10-05T04:00:00Z

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.

Don’t miss out! Oct 21 – IT Care - Painting by Numbers, Update Your SQL Skills with New Paintbrushes

2009-10-02T16:29:00Z

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.

User Group Networking Bingo

2009-09-29T20:00:00Z

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.

PASSMN SQL Summit Networking Bingo.docx

Check out the Ultimate Virtual Conference from SSWUG

2009-09-29T19:00:00Z

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.

September PASSMN Meeting Tonight

2009-09-15T14:00:00Z

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:

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.

Checkout PASSMN Meeting Next Week

2009-09-08T14:00:00Z

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.

Report Manager in Windows 7

2009-09-07T12:00:00Z

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.

Hey Minnesotans – PASSMN SQL Summit

2009-09-05T23:45:00Z

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

8:30 to 9:30	Registration and Breakfast
9:30 to 10:30	Session 1
10:30 to 10:45	Break
10:45 to 11:45	Session 2
11:45 to 1:00	Lunch
1:00 to 2:00	Session 3
2:00 to 2:15	Break with Snack
2:15 to 3:15	Session 4
3:15 to 3:45	Survey collection and Prizes

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.

PASSMN September Meeting

2009-09-01T22:00:00Z

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

Wait-Time Based Performance Management

August PASSMN Meeting Tonight

2009-08-18T12:00:00Z

Tonight’s topics are:

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.

This Week Only PASSMN Meeting (August 18)

2009-08-16T12:00:00Z

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).

Psst… It’s Free!

2009-08-05T18:00:00Z

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.

August PASSMN Meeting (August 18)

2009-08-05T12:00:00Z

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).

Reminder! Oct 21 – IT Care - Painting by Numbers, Update Your SQL Skills with New Paintbrushes

2009-08-03T16:29:00Z

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.

Analyzing Report Execution Time

2009-07-31T12:08:00Z

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.

Finding Number of Reports Executed By Folder

2009-07-30T13:00:00Z

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.

The Sixth Sense

2009-07-29T22:15:00Z

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.

PASSMN SQL Summit 2009 – Call For Presenters

2009-07-29T14:00:00Z

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).

Gotta Stop Violating Foreign Keys

2009-07-29T12:00:00Z

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:

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.
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.

Apply Those Updates

2009-07-23T12:00:00Z

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:

Management Data Warehouse Whitepaper

2009-07-23T04:02:00Z

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!

I Had a Dream, too!

2009-07-21T17:00:00Z

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.

Moving to the Head of the Line

2009-07-21T12:00:00Z

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.

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.~~

Are you busy Wednesday, Thursday, and Friday?

2009-07-20T16:00:00Z

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.

Analyzing Your Indexes with a Custom Report

2009-07-20T13:00:00Z

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:

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.

Resource Governor Whitepaper

2009-07-15T15:00:00Z

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.

July PASSMN Meeting Today (July 14)

2009-07-14T14:00:00Z

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.

Volunteering and Resume Building

2009-07-13T20:55:05Z

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.

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.

July PASSMN Meeting This Week (July 14)

2009-07-13T14:00:00Z

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.

5 Common Mistakes with Tempdb

2009-07-08T17:00:00Z

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:

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.
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.
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.
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…
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.

Script Data for a Table

2009-07-07T23:00:00Z

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.

What Happened to My Fabulous Beverage!

2009-07-07T21:30:00Z

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.

July PASSMN Meeting Next Week (July 14)

2009-07-06T16:00:00Z

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.

Microsoft MVP 2009 for SQL Server

2009-07-01T17:00:00Z

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.

When Did That File Get So Big?

2009-06-30T22:00:00Z

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.

Presenting on Indexing at PASS Summit 2009

2009-06-26T18:55:00Z

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”.

This Wednesday ITCare Event - Solving Business Pains with SQL Server Integration Services

2009-06-22T13:00:00Z

If you’ve been looking for some free training on SQL Server Integration Services, I’ve got the answer for you. The details are below and you can register for the event here.

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.

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.

Objectives

1. Understand flexibility that SSIS features provide
2. Demonstrate application of SSIS functionality to business needs
3. Discuss current best practices in SSIS package design

Audience

IT Administrators, IT Managers, Business Intelligence, DBA, SQL Server administrators.

Prerequisites

Understanding of basic ETL concepts and basic T-SQL coding

Course Length

1 Half Day

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.

June PASSMN Meeting Today

2009-06-16T14:15:00Z

The weather says it’s supposed to be storming today. What a better way to spend a rainy afternoon that in a conference room with your peers discussing SQL Server. So come out at 2:30 PM for snacks and socializing and we’ll kick things off at 3:00 PM.

We’ll be hearing about Analysis Services Dimension Creation Best Practices & Disks, Real and Virtual and What is Important for SQL Server.

Feel free to contact me via e-mail (jstrate@digineer.com) or twitter (@stratesql) with questions or for more information.

June PASSMN Meeting Tomorrow

2009-06-15T16:00:00Z

If you are still trying to make plans for tomorrow afternoon… there is a PASSMN meeting you could attend. This month we’ll be hearing about Analysis Services Dimension Creation Best Practices & Disks, Real and Virtual and What is Important for SQL Server. The meeting starts at 3:00 PM and there will some snacks and socializing before hand.

Feel free to contact me via e-mail (jstrate@digineer.com) or twitter (@stratesql) with questions or for more information.

SQL Satur-What? Here Comes the East Iowa SQL Saturday

2009-06-15T13:00:00Z

Did you hear? The call for speakers for the East Iowa SQL Saturday!

I’ve submitted four sessions and plan on driving down for the weekend. As Michelle points out it’s only a 5-hour down to the event. The event is free and will have a number of tracks which are still TBD but should be a good event.

Maybe if enough people from PASSMN head down there, they’ll join us when we try to put our event on. Though our event is still pretty vapor.

June ITCare Event - Solving Business Pains with SQL Server Integration Services

2009-06-12T13:00:00Z

Some lines got crossed with this month’s New Horizons ITCare event. The topic and speaker have changed but the date is remaining the same. Instead of the previous topic, I’ll be presenting on SQL Server Integration Services. As I mentioned, the date of the event is still June 24th. The event will be available online and you can register for the event here.

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.

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.

Objectives

1. Understand flexibility that SSIS features provide
2. Demonstrate application of SSIS functionality to business needs
3. Discuss current best practices in SSIS package design

Audience

IT Administrators, IT Managers, Business Intelligence, DBA, SQL Server administrators.

Prerequisites

Understanding of basic ETL concepts and basic T-SQL coding

Course Length

1 Half Day

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.

Oct 21 – IT Care - Painting by Numbers, Update Your SQL Skills with New Paintbrushes

2009-06-11T16:24:00Z

This event is a ways out there but thought I’d start getting the word out now for this free event. The session will be hosted by yours truly and feature 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.

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.

Really Search Cache for Execution Plans

2009-06-04T18:31:00Z

Today’s stored procedure for the DBADiagnostics database is a re-hash of a post I did a couple months ago. The reason for re-hashing the procedure is that when I used it last week I found out there were some serious issues with its results. Some plans were being duplicated and the performance statistics weren’t accurate because of this. And why not fix the issues and make a DBADiagnostics post out of it.

In the previous version, I was using the execution count from sys.dm_exec_query_stats to determine how often a plan was being executed. In the DMV though the SQL statements in the may execute in varying counts depending on conditional logic.

Instead of using the execution count from sys.dm_exec_query_stats the procedure now uses usecount from sys.dm_exec_cached_plans; which represents how often the plan was used. The execution count represented how often the statements in the plan were executed.

The stored procedure accepts the following parameters:

@Database: The database to search for plans within. This value can be NULL.
@ObjectName: The name of the procedure to search for plans for. This value can be NULL.

The procedure can use either or none of the variables. Obviously with both variables empty the results will be for all cached plans on the server.

USE [DBADiagnostics]
GO

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

IF EXISTS(SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID('Utility.CachedPlanSearch'))
    DROP PROCEDURE [Utility].[CachedPlanSearch]
GO

/*============================================================
Procedure:    [Utility].[CachedPlanSearch]
Author:       Jason Strate
Date:         June 1, 2009
 
Synopsis:
    Searches cache for all occurances of a plan based on an
    procedure and or database name.  Results assumes that average
    execution time for returned results are less than 24 hours.

Exec Utility.CachedPlanSearch
    @DatabaseName = 'msdb'
    ,@ObjectName = 'sp_sqlagent_get_startup_info'
 
============================================================
Revision History:
Date:         By                Description
------------------------------------------------------------
 
============================================================*/
CREATE PROCEDURE Utility.CachedPlanSearch
    (
    @DatabaseName sysname = NULL
    ,@ObjectName sysname = NULL
    )
AS

;WITH cteExecInfo
AS ( 
    SELECT DB_NAME(st.dbid) AS database_name 
        ,OBJECT_NAME(st.objectid, st.dbid) AS object_name 
        ,cp.usecounts -- Use in place of qs.execution_count for whole plan count
        ,CAST(SUM(qs.total_worker_time)/(cp.usecounts*1.) as decimal(12,2)) AS avg_cpu_time 
        ,CAST(SUM(qs.total_logical_reads + qs.total_logical_writes)/(cp.usecounts*1.) as decimal(12,2)) AS avg_io 
        ,SUM(qs.total_elapsed_time)/(cp.usecounts)/1000 as avg_elapsed_time_ms
        ,st.text AS sql_text
        ,qs.plan_handle 
    FROM sys.dm_exec_query_stats qs 
        INNER JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st 
    WHERE (DB_NAME(st.dbid) = @DatabaseName OR NULLIF(@DatabaseName,'') IS NULL)
    AND (OBJECT_NAME(st.objectid, st.dbid) = @ObjectName OR NULLIF(@ObjectName, '') IS NULL)
    GROUP BY st.dbid, st.objectid, cp.usecounts, st.text, qs.plan_handle 
) 
SELECT cte.database_name 
    ,cte.object_name 
    ,cte.usecounts 
    ,cte.avg_cpu_time 
    ,cte.avg_io 
    ,CONVERT(varchar, DATEADD(ms, cte.avg_elapsed_time_ms, 0), 114) AS avg_elapsed_time
    ,qp.query_plan 
    ,cte.sql_text
FROM cteExecInfo cte 
    OUTER APPLY sys.dm_exec_query_plan(cte.plan_handle) qp 
ORDER BY cte.usecounts DESC

I’ve often found this procedure useful when people complain about the execution of a procedure. Instead of pulling out performance tools and running test versions of the procedure this will provide the last plan that was used for the procedure.

July PASSMN Meeting Date Changed

2009-06-03T20:00:00Z

At the last meeting of the PASSMN board, the date for the July meeting was changed from July 21 to July 14. This was changed because of a conflict with some of the people invited to speak on the Ask the Experts panel.

Speaking of which, we are still looking for a few more people to join in the panel discussion and be an Expert. If you are interested or know someone that would be good to speak and will be in Minnesota on July 14, send me a DM on twitter at @stratesql.

June PASSMN Meeting (6/16/2009)

2009-06-03T02:00:00Z

The topics and speakers for June PASSMN meeting have been announced.

Analysis Services Dimension Creation Best Practices & Disks, Real and Virtual and What is Important for SQL Server

June 16, 2009
3:00 PM - 5:15 PM
Register Here or visit http://www.mnssug.org/

Analysis Services Dimension Creation Best Practices
Speaker: Brian Larson, Superior Consulting Services

Having dimensions that are well structured and function efficiently is key to having performant cubes and an important factor when encouraging users to use cubes for ad hoc reporting and interactive analysis. This session will look at best practices to observe when designing and creating dimensions in Analysis Services.

Brian Larson has 24 years of experience in the computer industry and 20 years’ experience as a consultant creating custom database applications. He is the Chief of Technology and BI Practice Manager for Superior Consulting Services in Burnsville, Minnesota, a Microsoft Gold Certified Partner. Brian is a Microsoft Certified Solution Developer (MCSD) and a Microsoft Certified Database Administrator (MCDBA).

Brian served as a member of the original Reporting Services development team as a consultant to Microsoft. In that role, he contributed to the original code base of Reporting Services. Brian has presented at national conferences and events, including the SQL Server Magazine Connections Conference, the PASS Community Summit, and the Microsoft Business Intelligence Conference, and has provided training and mentoring on Reporting Services and business intelligence across the country. He has been a contributor and columnist for SQL Server Magazine. Brian is the author of Microsoft SQL Server 2008 Reporting Services and Delivering Business Intelligence with Microsoft SQL Server 2008, both from McGraw-Hill Professional.

Disks, Real and Virtual and What is Important for SQL Server
Speaker: James Lorenzen, Xiotech Corporation

Introduction

A brief history of Hard Drives

How Moore’s Law applies

The performance impact of large disks (Greater than 1 terabyte)

Why bigger is not always better

Storage topology – DAS, NAS, and SAN

Definitions

Where they fit in a data center

Type of disks and interfaces available today

More acronyms (SATA, SCSI, IDE, SSD, etc.)

Where the different disks fit, performance and capacity

RAID Definitions

Why it came about

RAID Levels (0 through 50, or so)

How SQL Server uses the storage

The SQL Server file types

The performance requirements of the different file types

Questions

James Lorenzen is a Technical Marketing Engineer at Xiotech Corporation. He is Xiotech’s database specialist with over 25 years of database experience, fifteen years of that working with relational databases, SQL Server and Oracle. At Xiotech, James has focused on how to configure the database storage on a SAN to achieve the best possible performance from the SQL Server database. Prior to joining Xiotech, James has worked as a DBA for various companies both as consultant and dedicated Database Administration Manager. James has presented at user group meetings, covering database configuration on SAN storage. He can be reached at James_Lorenzen@Xiotech.com.

Oops… A Lot Of Trees… Jobs to Update

2009-06-02T13:06:00Z

Yesterday I posted about having an alert in place to know when SQL Agent jobs don’t have operators setup. I’ve found this alert to be extremely useful, but often when its been shared it just leads to junk e-mail.

The reason for this is when the alert is added to 5, 10, or 50 servers it usually brings back a large number of results the first couple times. And all of the jobs listed need to be updated so that the alerts start going out as required.

To alleviate the pain that the first execution can create, an maintenance procedure is included in the DBADiagnostics database which allows for mass or bulk updating of all of the SQL Agent jobs on the SQL Server instance.

The stored procedure accepts the following parameters:

@operator: The name of the SQL Agent Operator that should receive the alerts.
@notify_level_email: The type of notifications the job should send to e-mail. The values for these can be found in help for sp_update_job. The value 2 translates to On Failure.
@notify_level_eventlog: The type of notification the job should send to the event log. The value 0 translates to Never.

USE [DBADiagnostics]
GO
 
IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Maintenance')
    EXEC('CREATE SCHEMA [Maintenance] AUTHORIZATION [dbo]')
GO
 
IF OBJECT_ID('Maintenance.SQLAgentJobsNotifyUpdate') IS NOT NULL
    DROP PROCEDURE Maintenance.SQLAgentJobsNotifyUpdate
GO

/*============================================================
Procedure:    [Maintenance].[SQLAgentJobsNotifyUpdate]
Author:       Jason Strate
Date:         June 1, 2009
 
Synopsis:
    Mass update all jobs without notifications setup with
    new settings. 

Exec [Maintenance].[SQLAgentJobsNotifyUpdate] 
    @operator = 'DBA'
    ,@notify_level_email = 2
    ,@notify_level_eventlog = 0
 
============================================================
Revision History:
Date:         By                Description
------------------------------------------------------------
 
============================================================*/
CREATE PROCEDURE Maintenance.SQLAgentJobsNotifyUpdate
    (
    @operator sysname
    ,@notify_level_email int = 2
    ,@notify_level_eventlog int = 0
    )
AS

DECLARE @job_id uniqueidentifier

DECLARE JOB_UPDATE CURSOR FAST_FORWARD FOR
    SELECT job_id
    FROM msdb.dbo.sysjobs
    WHERE notify_email_operator_id = 0
    AND notify_level_eventlog = 0
    AND enabled = 1

OPEN JOB_UPDATE

FETCH NEXT FROM JOB_UPDATE INTO @job_id

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC msdb.dbo.sp_update_job 
        @job_id=@job_id 
        ,@notify_level_email=@notify_level_email
        ,@notify_level_eventlog=@notify_level_eventlog
        ,@notify_email_operator_name=@operator

    FETCH NEXT FROM JOB_UPDATE INTO @job_id
END

CLOSE JOB_UPDATE
DEALLOCATE JOB_UPDATE

CAUTION: Since the procedure updates all jobs that don’t have notifications setup, check and double check before running this in a production environment to verify that the update it will do is exactly what you are looking for.

But It Wasn’t My Job To Do The Backups!?

2009-06-01T19:30:00Z

It’s happened a few times, I go out to assist with recovery from some sort of failure and the question of database backups results in an uncomfortable pause. One group thought the other group was taking the backups and the other group didn’t know that the backups needed to be taken. And neither group previously attempted to execute the recovery plan before a failure had occurred. The end result is that the miscommunication resulted in a lack of necessary backups.

The most common scenario where this occurs is when the network or backup team is taking the SQL Server backups through a third-party backup tool removing the DBA’s from the “backup” process. In these cases, the DBAs tend to relinquish their responsibility for the backup process placing complete trust that the databases are being backed up.

Trust No On With Backups

If you are a DBA in this situation, don’t trust that the backups are just happening. Confirm it. Every day. Verify it. Every day. It doesn’t matter the level of trust that exists between the DBAs and other groups, as a DBA it is critical that when a database failure occurs that the database can be recovered and that there is full confidence that the needed backups exist. Users will only blame the DBAs when data can’t be recovered because that is what the DBAs are there for. The users won’t care who should have taken the backups only that it doesn’t exist and the DBA let that happen.

As a disclaimer, this isn’t an admonishment of other groups having backup responsibilities. But the reality when other groups are active in the backup process,

Now checking on backups every day can be tedious. In the scenario I’m outlining in this post a separate team is executing the backups. Should the DBAs check each database to make certain that backups are occurring on schedule? Yes, but that doesn’t necessarily mean that each database needs to be opened in SSMS to verify this.

Instead, I have an stored procedure that is executed multiple times a day, typically every few hours, to alert if a full and/or differential backup hasn’t been taken in the last XX number of hours. This way the only concern is the databases who’s backup schedules have fallen outside of SLA.

First the Setup

Before unveiling the new stored procedure for checking backups, there are some other object that will be needed first. These tables are used to configure this and other alerts and maintenance procedures. I’m a big fan of having a single database management code base between all servers, rather than customizing stuff and losing changes from time to time.

USE [DBADiagnostics]
GO

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

IF OBJECT_ID('Maintenance.ExcludeType') IS NULL
BEGIN
CREATE TABLE [Maintenance].[ExcludeType]
    (
    [ExcludeTypeID] [tinyint] NOT NULL IDENTITY(1,1),
    [Description] [varchar](500) NULL,
    [CreateDate] [smalldatetime] NOT NULL CONSTRAINT DF_ExcludeType_CreateDate DEFAULT GETDATE(),
    [CreateBy] [varchar](128) NOT NULL CONSTRAINT DF_ExcludeType_CreateBy DEFAULT SUSER_SNAME(),
    [UpdateDate] [smalldatetime] NOT NULL CONSTRAINT DF_ExcludeType_UpdateDate DEFAULT GETDATE(),
    [UpdateBy] [varchar](128) NOT NULL CONSTRAINT DF_ExcludeType_UpdateBy DEFAULT SUSER_SNAME(),
    CONSTRAINT [PK_ExcludeType] PRIMARY KEY CLUSTERED ([ExcludeTypeID] ASC) ON [Data]
    ) ON [Data]

CREATE UNIQUE NONCLUSTERED INDEX UIX_ExcludeType_Description ON Maintenance.ExcludeType ([Description]) ON Data

END
GO

IF NOT EXISTS(SELECT * FROM [Maintenance].[ExcludeType] WHERE [Description] = 'BackupCheck')
BEGIN
    INSERT INTO [Maintenance].[ExcludeType] ([Description])
    VALUES ('BackupCheck')
END
GO

IF OBJECT_ID('Maintenance.ExcludeDatabase') IS NULL
BEGIN
CREATE TABLE [Maintenance].[ExcludeDatabase]
    (
    [DatabaseName] [sysname] NOT NULL,
    [ExcludeTypeID] [tinyint] NOT NULL,
    [ExcludeReason] [varchar](255) NOT NULL,
    [CreateDate] [smalldatetime] NOT NULL CONSTRAINT DF_ExcludeDatabase_CreateDate DEFAULT GETDATE(),
    [CreateBy] [varchar](128) NOT NULL CONSTRAINT DF_ExcludeDatabase_CreateBy DEFAULT SUSER_SNAME(),
    [UpdateDate] [smalldatetime] NOT NULL CONSTRAINT DF_ExcludeDatabase_UpdateDate DEFAULT GETDATE(),
    [UpdateBy] [varchar](128) NOT NULL CONSTRAINT DF_ExcludeDatabase_UpdateBy DEFAULT SUSER_SNAME(),
    CONSTRAINT [PK_ExcludeDatabase] PRIMARY KEY CLUSTERED ([DatabaseName] ASC, [ExcludeTypeID] ASC) ON [Data]
    ) ON [Data]
END
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_ExcludeDatabase_ExcludeTypeID_FROM_ExcludeType')
BEGIN
    ALTER TABLE [Maintenance].[ExcludeDatabase] WITH CHECK 
    ADD CONSTRAINT [FK_ExcludeDatabase_ExcludeTypeID_FROM_ExcludeType] FOREIGN KEY([ExcludeTypeID]) REFERENCES [Maintenance].[ExcludeType] ([ExcludeTypeID])
END

And then the Store Procedure

The next code window has the stored procedure code for this alert. A few things to point out for the alert. First, the alert assumes that all databases have the same SLA for checking backups. This point of this was that the alert would be configured for the most crucial databases and take the less crucial databases along for the ride. Overly cautious is better than being under prepared when it comes to backups.

Next, the procedure utilizes the table Maintenance.ExcludeDatabases. This table serves as an exclusion table for this alert and some other maintenance processes that will be introduced in later posts. On the cautionary side, I prefer processes that maintain all databases by default and excludes those that a conscious decision to exclude has been made. I’d rather be safe than sorry. ~~Easier to keep my job this way.~~

To make certain the databases the alert isn’t checking databases that should be naturally ignored, the stored procedure ignore the following items as well:

tempdb and model are ignored.
Any database with a source_database_id which indicates that it is a snapshot database.
Any database in standyby mode which represents scenario where logs are being restored.
Any database with a RESTORING or OFFLINE state. These databases cannot be backed up in their current state.

Lastly, the procedure accepts a few parameters to configure the alert. The parameters are the following:

@ToAddress: the person, group, or mailbox that needs to receive the alert.
@SubjectWarning: a customizable message that can be added to the subject line of the e-mail alert. This was added so that when it is setup on pre-production and production servers the same alert code can be used on both servers but the subject line can include text informing the severity of the issue.
@HoursSinceBackup: the number of hours that the backups can age before an alert should be generated.

Without further ado, 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 EXISTS(SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID('Alert.BackupCheck'))
    DROP PROCEDURE [Alert].[BackupCheck]
GO

/*================================================================================
Procedure:    Alert.BackupCheck
Author:        Jason Strate
Date:        June 1, 2009

Synopsis:
    This procedure check the datbase backup is done within a number of hours passed
    into the procedure.  


================================================================================
Revision History:
Date:        By            Description
----------------------------------------------------------------------------------
================================================================================*/
CREATE PROCEDURE [Alert].[BackupCheck]
    (
    @ToAddress varchar(255)
    ,@SubjectWarning varchar(255) = ''
    ,@HoursSinceBackup smallint = 36
    )
AS

SET NOCOUNT ON

DECLARE @SQL varchar(max)
    ,@Subject nvarchar(255)
    ,@Body nvarchar(max)
    ,@RowCount int
    ,@DBCount int

SELECT @Subject = @SubjectWarning + Space(1) + @@Servername + ': Check Database Backup Not Done in the past '
        + Convert(varchar, @HoursSinceBackup) + ' Hours.'
    ,@Body = 'The following database files are not backed up with in '
        + Convert(varchar, @HoursSinceBackup) 
        + ' hour(s).  Please determine the cause of missing Backup(s).' + Char(13)
    ,@SQL = 'Set NoCount On; Select * From tempdb..tmpAlertResults'

BEGIN TRY  
    IF OBJECT_ID('tempdb..tmpAlertResults') IS NOT NULL
        DROP TABLE tempdb..tmpAlertResults

    ;WITH Excludes
    AS (
        SELECT DatabaseName
        FROM Maintenance.ExcludeDatabase ed
            INNER JOIN Maintenance.ExcludeType et ON ed.ExcludeTypeID = et.ExcludeTypeID
        WHERE et.Description = 'BackupCheck'
        )
    SELECT CONVERT(varchar, d.Name) AS [Database Name], 
        MAX(backup_start_date) AS [Backup Start Date], 
        MAX(backup_finish_date) AS [Backup Finish Date],
        DATEDIFF(hour, MAX(backup_start_date),GETDATE()) AS [Hour Since Backup]
    INTO tempdb..tmpAlertResults
    FROM sys.databases d 
        LEFT OUTER JOIN msdb..backupset bs On d.Name = bs.database_name ANd bs.type IN ('D','I')
        LEFT OUTER JOIN Excludes e On d.Name = e.DatabaseName 
    WHERE d.Name NOT IN ('tempdb', 'model') -- Back ups not necessarily needed
    AND source_database_id IS NULL -- If not NULL then database is a snapshot
    AND is_in_standby = 0 -- is_in_standby is read-only log shipped
    AND state_desc NOT IN ('RESTORING','OFFLINE') -- mirroring flag, offline check
    GROUP BY d.Name
    HAVING DATEDIFF(hour, MAX(backup_start_date),GETDATE()) > @HoursSinceBackup
    OR MAX(backup_start_date) IS NULL
    ORDER BY MAX(backup_start_date) ASC 

    IF EXISTS (SELECT * FROM tempdb..tmpAlertResults)
    BEGIN
        Exec msdb.dbo.sp_send_dbmail 
        @recipients = @ToAddress, 
        @subject = @Subject,
        @body = @Body,
        @execute_query_database = 'DBADiagnostics',
        @query = @SQL,
        @query_result_separator = '|'
    END
END TRY 
BEGIN CATCH
    DECLARE @ErrState int,
        @ErrSev int,
        @ErrMsg varchar(max)

    SELECT @ErrSev = ERROR_SEVERITY(),
       @ErrState = ERROR_STATE(),
       @ErrMsg = ERROR_MESSAGE()

    RAISERROR (@ErrMsg, @ErrSev, @ErrState)
END CATCH

If anyone knows how to get the last modified date for a database that would be very helpful. I’d like to add additional logic to the procedure that ignores databases that have been backed up at least once since being made read-only.

Does A Job Really Fail If Nobody Knows About It?

2009-06-01T16:00:00Z

Have you ever gone out and made a new SQL Agent job and forget to assign notification settings for it? ~~God, I hope I’m not the only one because that would mean I suck.~~

This is a common issue that exists in most environments that I’ve worked in. ~~So I must not suck.~~ Quite often I see SQL Agent Jobs executing throughout an organization and no one knows that a critical job has been failing day after day because the notifications where not setup for the job.

Every environment has its own requirements for how notifications need to be aggregated and handled. Most often the SQL Agent jobs are configured to send e-mail notifications and then the person that receives the e-mail is charged with resolving the issue. Sometimes these e-mails are sent to applications that automatically generate a ticket that is then forwarded to the responsible individual. In other environments, the failed jobs are logged to the event log and a server monitoring tool collects the event log information and generates alerts based on these entries.

The alert below was created with these two scenarios in mind. The procedure looks for jobs that don’t have either e-mail operators or an event log status set. To reduce clutter, it also ignores disabled jobs and jobs with the ‘Report Server’ category. The ‘Report Server’ category was mainly added to reduce noise from subscriptions created by reporting services.

The procedure accepts two parameters:

@ToAddress: the person, group, or mailbox that needs to receive the alert.
@SubjectWarning: a customizable message that can be added to the subject line of the e-mail alert. This was added so that when it is setup on pre-production and production servers the same alert code can be used on both servers but the subject line can include text informing the severity of the issue.

Typically this procedure gets scheduled once a day in the morning so that any items it finds can be resolved before the day gets going.

USE [DBADiagnostics]
GO
 
IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Alert')
    EXEC('CREATE SCHEMA [Alert] AUTHORIZATION [dbo]')
GO
 
IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'SQLAgentJobsWithoutNotify')
    DROP PROCEDURE [Alert].[SQLAgentJobsWithoutNotify]
GO
 
/*============================================================
Procedure:    [Alert].[SQLAgentJobsWithoutNotify]
Author:       Jason Strate
Date:         8-11-2004
 
Synopsis:
 
Exec [Alert].[SQLAgentJobsWithoutNotifyEmail] 
        @ToAddress = 'jstrate@hotmail.com'
        ,@SubjectWarning = 'TEST ALERT'
 
============================================================
Revision History:
Date:         By                Description
------------------------------------------------------------
 
============================================================*/
CREATE PROCEDURE [Alert].[SQLAgentJobsWithoutNotify]
    (
    @ToAddress varchar(255)
    ,@SubjectWarning varchar(255) = ''
    )
AS
 
IF EXISTS (SELECT *
    FROM msdb.dbo.sysjobs sj 
        LEFT OUTER JOIN msdb.dbo.syscategories c ON sj.category_id = c.category_id
    WHERE sj.enabled = 1
    AND sj.notify_email_operator_id = 0 
    AND sj.notify_level_eventlog = 0
    AND c.[Name] <> 'Report Server')
BEGIN
    DECLARE @Subject nvarchar(500),
        @Body nvarchar(500),
        @SQL nvarchar(max)
 
    SELECT @Subject = @SubjectWarning + SPACE(1) + @@SERVERNAME + ': MSDB Job Notification Audit',
        @Body = 'The following SQL Agent jobs do not have either e-mail or event log notifications configured to alert in the event that the job fails.',
        @SQL = 'SET NOCOUNT ON; 
            SELECT CONVERT(varchar, COALESCE(sos.originating_server, @@SERVERNAME)) as [Originating Server], 
                CONVERT(varchar, sj.[name]) as [Job Name], 
                COALESCE(STUFF(STUFF(next_run_date, 7, 0, ''/''), 5, 0, ''/'') + Space(1) + 
                    STUFF(STUFF(next_run_time, 5, 0, '':''), 3, 0, '':''), ''Unscheduled'') as [Next Run Date],
                CONVERT(varchar, c.[name]) as [Category Name]
            FROM msdb.dbo.sysjobs sj 
                INNER JOIN msdb..sysjobschedules sjs ON sj.job_id = sjs.job_id
                LEFT OUTER JOIN msdb.dbo.syscategories c ON sj.category_id = c.category_id
                LEFT OUTER JOIN msdb.dbo.sysoriginatingservers sos ON sj.originating_server_id = sos.originating_server_id
            WHERE sj.enabled = 1
            AND sj.notify_email_operator_id = 0 
            AND sj.notify_level_eventlog = 0
            AND c.[Name] <> ''Report Server'''

    EXEC msdb.dbo.sp_send_dbmail 
        @recipients = @ToAddress, 
        @subject = @Subject,
        @body = @Body,
        @execute_query_database = 'DBADiagnostics',
        @query = @SQL,
        @query_result_separator = '|'
END
GO

P.S. This was supposed to go out last Friday which would have put some context around my other post on my DBADiagnostics database. But I went to the lake instead… so yeah. Priorities.

DBADiagnostics – My DBA Database

2009-05-29T13:00:00Z

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

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

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

USE [master]
GO

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

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

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

Free Hyper-V Training

2009-05-27T05:18:59Z

I was reading the high-availability blog on MSDN today and found a post with some free training mentioned within it. Here are the details from the blog:

Microsoft is offering some free training for Hyper-V which includes modules on HA and Failover Clustering.

Free eLearning: Exam 70-652: TS: Windows Server Virtualization, Configuring

This collection of five 2-hour courses helps you develop the skills necessary for implementing and managing Hyper-V in an IT environment, as well as, creating and managing virtual machines and hosts in a virtual environment.
Use promo code: 9350-Y2W6-3676
The courses within the collection include the following topics:

· An overview of the Hyper-V technology

· Creating a virtual environment

· Deploying systems in a virtual environment

· Configuring high availability in a virtual environment

· Administering a virtual environment with SCVMM

Details: https://www.microsoftelearning.com/eLearning/offerDetail.aspx?offerPriceId=228779

Exam Details: http://www.microsoft.com/learning/en/us/exams/70-652.mspx

Free eBook: Understanding Microsoft Virtualization Solutions

This guide will teach you about the benefits of the latest virtualization technologies and how to plan, implement, and manage virtual infrastructure solutions. The technologies covered include: Windows Server 2008 Hyper-V, System Center Virtual Machine Manager 2008, Microsoft Application Virtualization 4.5, Microsoft Enterprise Desktop Virtualization, and Microsoft Virtual Desktop Infrastructure.

Details: http://csna01.libredigital.com/?urmvs17u33

DreamSpark Certification Vouchers (aka Free Tests)

2009-05-22T21:15:23Z

I was reading Glenn Berry’s blog and found out about an opportunity for free Microsoft Exam vouchers. This is a great deal that is part of Microsoft’s DreamSpark program. Through this program, students at accredited academic institutions can take Microsoft Certified Technical Specialist exams for free.

As I mentioned the other day, there are a number of reasons to get certified. One of these reasons that applies to students is that it helps bridge the experience gap. Classes at school will help provide a foundation of the theoretical and a foundation to build a career upon. Certifications provide a place to apply the information gained through school and apply towards the tools in the industry.

Until a student is able to make the move from being a student to having a career and building experience, certifications provide a leg up to help you differentiate yourself from the crowd. That extra dedication and commitment that may make the difference on your next interview.

Are You Down With The BPA?

2009-05-21T18:04:00Z

Yesterday I talked about installing the Best Practices Analyzer 2005 (BPA) for a client that I was working with. After some initial installation issues, I was ready to check out their SQL Server environment.

While the tool was running, I started checking out the SQL Server event logs to see if there was anything interesting in the logs. Of course there was, otherwise this would be a very short and uninteresting blog. Counting successful backup messages in the log is how I roll.

In the SQL Server event log there were a few interesting messages. One in particular looked a lot like the following:

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: XX seconds. Working set (K: XX, committed (K: XX, memory utilization: XX%.

Ah man, this doesn’t look good. The message is talking about paging and performance degradation. That always sound bad. Of course, I started digging into the server to figure out what was causing these performance issues. I found some promising information on this issue here and here.

While I was checking this information out, the Best Practice Analyzer finished its work. I decided to wrap up the BPA work before getting too deep into this issue. As I was exporting the report results, the first item in the list read the following:

There are known issues that exist in Windows and various Device Drivers which can trigger conditions that result in working set of all processes currently running on the machine to be trimmed. This will cause excessive paging and result in drastic reduction in performance of all applications on the machine (specifically SQL Server).We found that TCPIP.SYS present with version [5.2.3790.4318] and BXVBDA.SYS is also present. So we recommend you to disable TCP Chimney.

This issue tracks back to the issue that I found in the SQL Server error log at the beginning of this post. As a result of using the BPA to check out the client’s SQL Server instance a potentially difficult issue to resolve may be resolved with next to no effort.

The client is currently implementing the recommendation and I should know shortly whether this resolved the issue. I am fairly confident that this will resolve the issue for them but will follow up on this post when I know for certain.

The lesson here is that BPA identified and offered a resolution for an issue that could have taken a lot of effort to resolve. The question now is have you run BPA on your SQL Server environment to validate that your environment is up to snuff?

BPA Installs But Doesn’t Work

2009-05-20T14:00:00Z

I was hoping to have a case of the Mondays the other day. Whenever one hopes for “the Mondays”, Monday will always deliver. I was using a new PC at a client and installing some tools for an assessment that I was working on.

After some download and installing, Best Practices Analyzer 2005 was installed and ready to go. I got the tool fired up and pointed it at the server I needed to look at and pressed go.

And then it stopped… I had an unhandled exception error.

Not a big deal. I can handle exception error because I can read. After opening up the details window, the following was presented to me:

See the end of this message for details on invoking 
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.WindowsServerSystem.BestPracticesAnalyzer.UserInterface.BPALink.LinkClicked(Object sender, EventArgs e)
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.Label.WndProc(Message& m)
   at System.Windows.Forms.LinkLabel.WndProc(Message& msg)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


************** Loaded Assemblies **************
mscorlib
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3082 (QFE.050727-3000)
    CodeBase: file:///c:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
SqlBPA2005
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1009.00
    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20Best%20Practices%20Analyzer/SqlBPA2005.exe
----------------------------------------
System.Windows.Forms
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
BPA.UserInterface
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1009.00
    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20Best%20Practices%20Analyzer/BPA.UserInterface.DLL
----------------------------------------
BPA.Common
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1009.00
    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20Best%20Practices%20Analyzer/BPA.Common.DLL
----------------------------------------
SqlBPA.Shared
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.1009.00
    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20Best%20Practices%20Analyzer/SqlBPA.Shared.DLL
----------------------------------------
System.Xml
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3082 (QFE.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
System.Configuration
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
    <system.windows.forms jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.

Great. I can read – but not this. I like error messages that are a little more direct that this.

Fortunately, after a little digging I found a similar issue over at MSDN forums. To solve this issue I needed to install both of the following SQL Server 2005 components:

Microsoft SQL Server Native Client
Microsoft SQL Server 2005 Management Objects Collection

I hadn’t expected this issue since I had SQL Server client tools installed on the PC that I was using. The catch was though that I had the SQL Server 2008 tools installed, not the SQL Server 2005 tools.

Where, oh where would I get these components? Alas! They are available in the SQL Server 2005 Feature Pack.

Now that I’ve got BPA installed it’s time to use it. My question for you is do you use BPA?

SQL Load Generator

2009-05-19T16:30:00Z

This project has been on CodePlex for a while, but I thought I would give it a plug. It’s a really good way to generate a load against a SQL Server without a lot of effort. I’ve used it quite a number of times with presentations and use it when testing different code choices to see what happens when I run it often.

May PASSMN Meeting Today

2009-05-19T13:30:00Z

It probably won’t be as interesting as Old School, but come out this afternoon if you are in the Twin Cities for the PASSMN meeting you could attend. It starts at 3:00 PM and we meet at 2:30 PM for snacks and socializing. And by snacks, I usually mean pizza but no promises.

We’ll be hearing about SSIS – Team Development, Deployment and Configuration & Securing and Troubleshooting Service Broker.

Feel free to contact me via e-mail (jstrate@digineer.com) or twitter (@stratesql) with questions or for more information.

Your 2nd Chance End June 30

2009-05-18T15:26:00Z

I’ve mentioned the 2nd Chance Certification offer that Microsoft provides in the past. And if you’ve tuned in at the PASS MN meetings, I’ve thrown the plug out there a few times.

2nd Chance offers test takers the opportunity to take a run at an exam with the chance to take it a 2nd time for free if the first attempt isn’t successful. It’s a great offer that I’ve used quite a few times in the past. ~~My co-workers reading this probably think I should can it on this offer since I am always bringing it up. Hello, obsessive compulsive me.~~

On top of that, right now you can also get an E-Learning Collection with the offer for just $35 more. That’s a90% discount off the regular price.

So there must be a catch…

The offer expires on June 30th. As in after next month it will no longer be available. This offer has been available for quite some time, but don’t take it for granted and miss out on it.

But who cares about certifications?

Will being certified make you an expert in the subject area of the exam? Not at all, but it does show that you’ve got the determination to spend time studying on the subject. It also shows that you’re minimally qualified to work with the technology that the exam covers.

Along the same lines, certifications cover a lot of features and functionality on their topics. Most employers don’t have the opportunity to allow people to use all of those features and functionality in their environments. When I was working on some of SQL Server 2008 exams last summer, we often talked about how cool it would be to work at an employer that used all of the features of SQL Server 2008 in a single environment.

But unfortunately that environment doesn’t exist. Or I haven’t found it yet…

Certifications, though, give you a reason to go out and find out about all those features that you don’t currently use, because they might be on the test. Studying for certifications is one of the greatest benefits that getting certified will offer you.

Another thing to think about with certifications is that with today’s uncertain economic conditions, a certification could be that bit that helps you get in the door for an interview. Or maybe it’ll be the extra effort that your boss sees you putting in that keeps you in the door.

As Brent mentioned a while back on his blog, certifications are the icing on the cake.

Why go for the 2nd chance?

You’re going to study, study, study, and study. It takes time. Time that you could do other things, like enjoying the summer or watching the new Star Trek movie. ~~Seriously, is anything left as canon anymore now that the timeline is fragged.~~ Each day only has 24 hours in it and you need to prioritize. The 2nd chance offer lets you do that.

Suppose you planned to spend 40 hours studying and when life got in the way you only spent 20 hours studying. Are you ready? Should you re-schedule?

If you’ve been working with the technology for a few years and you may not need 40 hours to study. You might already be ready and not know it. Don’t reschedule. Take the exam and see how you do.

If you pass, that’s probably another 40 hours of free time that you would have tried to study and a bucket full of stress and guilt between choosing between a certification and your kids, wife, family, etc. And if you don’t pass, you know exactly where to study versus going back and hitting all areas of the test. Which might mean you only need to spend 10 hours studying before the next try. Meaning more free time and less stress and guilt. A definite win-win situation.

So, are you willing to take your 2nd chance?

SQL Server 2008 R2

2009-05-12T03:00:00Z

The final answer has ~~mostly~~ arrived, the long awaited projects “Madison” and “Kilimanjaro” are getting near to CTP stage and will be available soon as SQL Server 2008 R2 CTP. This was announced earlier today at TechEd; which also concided with the release of the SQL Server 2008 R2 site.

Some of the details from the new site:

What's New in R2

With this new release, IT professionals, developers and business users will experience the following benefits:

Capitalize on Hardware Innovation

Increase in the number of logical processors supported from 64 up to 256. This will provide customers with more choices for obtaining single system scalability with high performance.

Optimize Hardware Resources

Dashboard viewpoints provide real-time insight into utilization and policy violations to help identify consolidation opportunities, maximize investments and maintain healthy systems.

Manage Efficiently at Scale

Through new extensions in SQL Server Management Studio, organizations will gain insights into their growing applications and databases and help ensure higher service levels through policies and dashboard viewpoints.

Enhance Collaboration Across Development and IT

Streamline Application Lifecycle Management through integration with Visual Studio. A new project type enables a single unit of management for packaging database schema with application requirements. This ensures higher quality application development while also accelerating deployments, moves, and changes over time.

Improve the Quality of Your Data

Centralized approach to defining, deploying, and managing master data can ensure reporting consistency across systems and deliver faster more accurate results across the enterprise.

Manage User-Generated Analytical Applications

Comprehensive management thru Microsoft SharePoint gives IT the ability to manage and secure all BI assets, thus freeing the original authors to focus on the priorities of the business.

Report with Ease

Decrease time and costs developing reports by giving users the ability to design their own queries, reports and charts through powerful and intuitive authoring and ad hoc reporting capabilities.

Get More Out of Your Data

New support for geospatial visualization can produce new insights and discoveries when geospatial data is combined with corporate data for reporting and analysis.

Build Robust Analytical Applications

With the in-memory analytics add-in for Microsoft Excel 2010, business users can quickly access, analyze and summarize vast amounts of data directly in Excel without the assistance of the IT department.

Consolidate Your Data

New “data mash up” capabilities will simplify time consuming data gathering and consolidation tasks. Integrate data from multiple sources, including corporate databases and external sources, using powerful tools within Microsoft Excel.

Share and Collaborate with Confidence

New collaboration tools make it easy to share analytic applications and reports through Microsoft Office SharePoint, where they are refreshed automatically, maintained, and made accessible to others.

I’m really excited to play around with the CTP and dig into the MDM and multi-server management components. Hopefully when the CTP is out I’ll find some time to try and post on the new features.

From h4ppyd4y to StrateSQL

2009-05-08T17:00:00Z

I changed my twitter name from h4ppyd4y to StrateSQL earlier this week. This shouldn’t have made much of a difference to people that are following me since twitter now allows for name changes without having to create a new account. Very cool!

Hopefully this change doesn’t confuse anyone. If you aren’t using twitter, I do recommend it as a way to network with your SQL Server peers. The videos below provide some insight into twitter. Of better yet, check out Brent Ozar’s twitter posts.

More Parallelism

2009-05-08T14:15:00Z

It’s quite often at clients that I spend time working on issues revolving around parallelism. In fact, just this week it’s been the single most irritating performance problem that I’ve had to deal with.

If you aren’t up to snuff on parallelism there are a couple places I would start with getting up to speed:

Both articles are from Jimmy May (@aspiringgeek), who writes some great articles on SQL Server performance and scalability.

Two days ago, I was working on trying to determine which queries were causing the most trouble when it came to parallelism. That was when I modified the current queries executing with parallelism query to make it more useful. And a few days before I posted some queries that I also use to determine plans that may utilize parallelism.

While working on these parallelism issues, I started wondering if identifying query plans utilizing parallelism by CPU was the correct approach. Just because an execution plan has high CPU doesn’t necessarily mean that it is the biggest contributor or source of parallelism on the server.

Instead, I started looking at execution counts of the plans. The query that I’ll be listing below provided a stark indication of the highest contributor to parallelism in the issues I was working on by identify a single query plan that was utilizing parallelism and had been executed nearly a 100 thousand times since I’d cleared the procedure cache that morning.

SELECT TOP 50
    CASE WHEN p.dbid = 32767 THEN '{RESOURCE}' ELSE DB_NAME(p.dbid) END as database_name
    ,OBJECT_NAME(p.objectid, p.dbid) as [object_name]
    ,qs.execution_count
    ,qs.total_worker_time
    ,qs.total_logical_reads
    ,CONVERT(decimal(12,3),qs.total_elapsed_time/1000000./qs.execution_count) as avg_elapsed_time_sec
    ,CASE statement_end_offset WHEN -1 THEN q.text
        ELSE SUBSTRING(q.text, statement_start_offset/2, (statement_end_offset-statement_start_offset)/2) END as sql_statement
    ,p.query_plan
    ,q.text
    ,cp.plan_handle
FROM sys.dm_exec_query_stats qs
    INNER JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) as q
WHERE cp.cacheobjtype = 'Compiled Plan' 
AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:RelOp/@Parallel)', 'float') > 0
ORDER BY qs.execution_count DESC

Now that I’ve shared this, what do you the reader use to identify queries that are utilizing parallelism in your environments?

May PASSMN Meeting (05/19/2009)

2009-05-07T14:15:00Z

The topics and speakers for this months PASSMN meeting have been announced…

SSIS – Team Development, Deployment and Configuration & Securing and Troubleshooting Service Broker

May 19, 2009
3:00 PM - 5:15 PM

SSIS – Team Development, Deployment and Configuration
Speaker: Dan English, Magenic

SQL Server Integration Services (SSIS) provides enterprise-class scalability, advanced data-integration architecture, and high-performance processing. Many enterprise environments are developing centralized services and standards to support their SQL Server Integration Services platform. During this session you will learn considerations and solutions for team development and how to leverage the power of package configurations for deploying packages to multiple environments.

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.

Securing and Troubleshooting Service Broker
Speaker: Eric Strom, RELS

Tired of reading “Hello World” articles about Service Broker? Looking for more information but not finding good resources on securing and troubleshooting Service Broker applications? In this presentation, I will discuss some good security practices and share some lessons I learned while implementing and troubleshooting a medium-sized Service Broker application. Expect to learn about securing and troubleshooting Service Broker. A basic understanding of the Service Broker architecture is helpful.

Eric Strom is a Senior Database Administrator at the RELS Companies and is a member of the PASSMN 2009 Executive Board. He has been a SQL Server DBA since 2001 and specializes in performance tuning. Eric studied database theory at the University of Minnesota to earn a B.S. in Computer Science. He loves exchanging ideas with peers and is always looking for a good discussion.

I Didn’t Receive My Subscription

2009-05-07T13:25:00Z

Ever have a Reporting Services (SSRS) subscription that didn’t fire off as you thought it should? Or was SQL Agent offline when subscriptions should have been fired? Or did you just create a subscription and want to find out if it’s been configured properly?

Fortunately all of the information is sitting neatly and plainly in your SQL Server database. Unfortunately it the plainly part isn’t quite the truth. If you’ve looked before, subscriptions are named with uniqueidentifiers. These values don’t exactly read in a manner that help identify which subscription they correspond to.

The query below provides a list of all current subscriptions in SSRS and also the last execution date. The final column has the SQL syntax that would be needed to start the job to execute the subscription. A little modification and the query can also be used to find execution history on the subscription.

;WITH cte (job_id, job_name, execution_time, execution_order) 
AS 
( 
SELECT DISTINCT j.job_id 
    ,j.name 
    ,CONVERT(datetime, STUFF(STUFF(run_date,7,0,'/'),5,0,'/') 
        + SPACE(1) 
        + STUFF(STUFF(RIGHT('000000' + CONVERT(varchar(20), run_time), 6),5,0,':'),3,0,':'))
    ,ROW_NUMBER() OVER (PARTITION BY j.job_id ORDER BY CONVERT(datetime, STUFF(STUFF(run_date,7,0,'/'),5,0,'/') 
        + SPACE(1) 
        + STUFF(STUFF(RIGHT('000000' + CONVERT(varchar(20), run_time), 6),5,0,':'),3,0,':')) DESC)
FROM msdb.dbo.sysjobs j (nolock) 
    INNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
    LEFT OUTER JOIN msdb.dbo.sysjobhistory jh (nolock) ON j.job_id = jh.job_id 
WHERE c.name ='Report Server'
) 
SELECT 
    x.job_name 
    ,c.name 
    ,x.execution_time
    ,c.path 
    ,su.description 
    ,CONVERT(varchar(max), su.ExtensionSettings) as ExtensionSettings 
    ,'EXEC msdb..sp_start_job ''' + x.job_name + '''' as SQLStatement
FROM cte x 
    INNER JOIN dbo.Schedule sc ON x.job_name = CONVERT(varchar(100), sc.ScheduleID) 
    INNER JOIN dbo.ReportSchedule rs ON sc.ScheduleID = rs.ScheduleID 
    INNER JOIN dbo.Subscriptions su ON rs.SubscriptionID = su.SubscriptionID 
    INNER JOIN dbo.Catalog c ON su.Report_OID = c.ItemID 
WHERE execution_order = 1
ORDER BY 3, 2

Finding Objects In Schema

2009-05-07T05:15:15Z

A while back I was asked to identify schemas that didn’t have any objects. Since it was a useful query, I thought I’d share it.

SELECT s.name as schema_name 
    ,COALESCE(o.type_desc, '--NO ITEMS--') as type_desc
    ,NULLIF(COUNT(DISTINCT o.name),0) as item_count
FROM sys.schemas s 
    LEFT OUTER JOIN sys.all_objects o ON s.schema_id = o.schema_id 
WHERE s.name NOT IN ('INFORMATION_SCHEMA', 'sys') 
GROUP BY s.name, o.type_desc
ORDER BY s.name, o.type_desc

Querying for Parallelism

2009-05-06T05:04:19Z

While working on some parallelism issues today I started using the query that detects whether there are queries currently running utilizing parallelism. It’s the same query that can be found in the white paper Troubleshooting Performance Problems in SQL Server 2005.

Which I’ve included below:

SELECT 
    r.session_id,
    r.request_id,
    MAX(ISNULL(exec_context_id, 0)) as number_of_workers,
    r.sql_handle,
    r.statement_start_offset,
    r.statement_end_offset,
    r.plan_handle
FROM 
    sys.dm_exec_requests r
    JOIN sys.dm_os_tasks t ON r.session_id = t.session_id
    JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE 
    s.is_user_process = 0x1
GROUP BY 
    r.session_id, r.request_id, 
    r.sql_handle, r.plan_handle, 
    r.statement_start_offset, r.statement_end_offset
HAVING MAX(ISNULL(exec_context_id, 0)) > 0

It sure is to know the queries currently executing using parallelism. But the query doesn’t provide enough information when it executes to take some immediate action without further research. The query below provides this additional information.

WITH CurrentParallelism
AS (
    SELECT 
        r.session_id
        ,r.request_id
        ,r.database_id
        ,MAX(ISNULL(exec_context_id, 0)) as number_of_workers
        ,r.sql_handle
        ,r.statement_start_offset
        ,r.statement_end_offset
        ,r.plan_handle
    FROM sys.dm_exec_requests r
        INNER JOIN sys.dm_os_tasks t on r.session_id = t.session_id
        INNER JOIN sys.dm_exec_sessions s on r.session_id = s.session_id
    WHERE s.is_user_process = 0x1
    GROUP BY 
        r.session_id, r.request_id, r.database_id,
        r.sql_handle, r.plan_handle, 
        r.statement_start_offset, r.statement_end_offset
    HAVING MAX(ISNULL(exec_context_id, 0)) > 0
)
SELECT
    cp.session_id
    ,cp.request_id
    ,db_name(cp.database_id) AS database_name
    ,cp.number_of_workers
    ,CASE statement_end_offset WHEN -1 THEN q.text
        ELSE SUBSTRING(q.text, statement_start_offset/2, (statement_end_offset-statement_start_offset)/2) END as sql_statement
    ,p.query_plan
    ,q.text
    ,cp.plan_handle
FROM CurrentParallelism cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) as q

The information that I like to have on hand is the exact statement and query plan that is executing so that I know what it is that I am dealing with. Hopefully others feel the same and find some use in this.

Please Update Your RSS Subscription

2009-05-06T04:07:20Z

To make things easier on both you and myself, please update your RSS feed to http://feeds2.feedburner.com/StrateSql. In the next month or two I’m hoping to get an upgrade to both of the blog sites that I upload to and the old rss feeds will most likely no longer be active.

Snapshots of Index Contention

2009-05-05T05:51:33Z

Occasionally, I need to determine where index hotspots are located within databases. To do this, I use the DMV sys.dm_db_index_operational_stats. I’ve talked about this DMV before while I was going through the index analysis blogs posts.

This DMV returns locking and blocking information for tables and indexes in the database. This information is aggregated since the last time the SQL Server service was started or when the metadata cache for the index is reset. Inactivity of an index or DDL operations are the types of a activities that can cause the metadata cache to be reset.

Sometimes though index contention may not have occurred when you are currently knee deep in performance issues. And as such, finding the hotspot at the time of the issue is more important that knowing that the contention happened at some point since the statistics were last reset. ~~So just go out and restart the SQL Server service because that’s makes it easy.~~

To get a snapshot of the index contention without forcing the metadata cache to reset, it is necessary to baseline the current state of contention and then compare it to a future state after a period of time has past. In the script below, the query provides a 5 minute snapshot of index contention.

DECLARE @dbid INT;
 
SET @dbid=DB_ID();
 
DECLARE @IndexOperationalStats table
    (
    database_id int
    ,object_id int
    ,index_id int
    ,partition_number int
    ,row_lock_count bigint
    ,row_lock_wait_count bigint
    ,row_lock_wait_in_ms bigint
    )

--INSERT INTO @IndexOperationalStats
SELECT database_id
    ,object_id
    ,index_id
    ,partition_number
    ,row_lock_count
    ,row_lock_wait_count
    ,row_lock_wait_in_ms
FROM sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s
 
WAITFOR DELAY '00:05:00.000'
 
;WITH IndexOperationalStats_Delta
AS (
    SELECT s.database_id
        ,s.object_id
        ,s.index_id
        ,s.partition_number
        ,s.row_lock_count - COALESCE(t.row_lock_count, 0) as row_lock_count
        ,s.row_lock_wait_count - COALESCE(t.row_lock_wait_count, 0) as row_lock_wait_count
        ,s.row_lock_wait_in_ms - COALESCE(t.row_lock_wait_in_ms, 0) as row_lock_wait_in_ms
    FROM sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s
        LEFT OUTER JOIN @IndexOperationalStats t 
            ON s.database_id = t.database_id
            AND s.object_id = t.object_id 
            AND s.index_id = t.index_id
            AND s.partition_number = t.partition_number
    WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
)
SELECT 
    DB_NAME(database_id) AS DbName
    ,OBJECT_NAME(ios.[object_id]) AS ObjName
    ,i.name AS IndexName
    ,i.index_id
    ,partition_number
    ,row_lock_count
    ,row_lock_wait_count 
    ,CAST (100.0 * row_lock_wait_count / (1 + row_lock_count) AS NUMERIC(15,2)) AS [block %]
    ,row_lock_wait_in_ms
    ,CAST (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) AS NUMERIC(15,2)) AS [Avg Row Lock Waits (ms)]
FROM IndexOperationalStats_Delta ios
    INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id
ORDER BY row_lock_wait_count DESC

Sorting Project Files in SSMS

2009-05-04T13:55:00Z

Ever need to sort your project files for SSMS and SSIS? I did. So in case anyone could use this… and since I needed to use it again tonight…

http://www.sqldbatips.com/showarticle.asp?ID=78&reason=0

A good tool and easy to use.

Analyze This - Analyze Your Indexes – Part 6

2009-04-29T13:00:00Z

This is the sixth and last ~~for now~~ post in which I’m going through a query that can be used to analyze indexes. Thus far the following has been added to the query:

Combine existing index statistics and missing indexes into a single output (blog)
Find existing duplicate and overlapping indexes (blog)
Find the relationship between indexes and foreign keys (blog)
Added size and current memory utilization (blog)
Add in index analysis guidelines (blog)

If you’ve used the last few versions of this query you may have noticed that performance against databases with a large number of indexes wasn’t all that great. Also, the query didn’t account for partitions which I had planned to resolve at a later date. Oh, look – it’s a later date.

In the first few versions of the index analysis query, new features to the query were added through the use of CTEs. The trouble with the way the query was put together was that it caused it to choke when performing as the volume of indexes being reviewed increased. In a few sample executions, the performance has improved by an order of magnitude. In one case the execution went from almost 3 minutes to 20 seconds.

This version of the index analysis query also now works with SQL Server 2005 RTM. There was a few functions being used that were added in SQL Server 2005 SP 2. Those have been removed to allow for compatibility back to SQL Server 2005 RTM.

A new column that flags whether of not a table has a unique index on it was added. This was added based on a request in a comment a couple posts ago.

Besides fixing the items listed above, there was a need to review indexes to check for blocking. To do this the DMV sys.dm_db_index_operational_stats should be used. This DMV provides I/O, locking, latching, and access method activity for each partition of a table or index. Only blocking statistics have been with the index analysis at this time.

The last thing that could have been added to the query was fragmentation information from ys.dm_db_index_physical_stats. This was not included because the execution of this DMV is invasive even when using SIMPLE mode.

The new version of the query is as follows:

DECLARE @ObjectID int
    ,@DB_ID int

SELECT @ObjectID = OBJECT_ID('')
    ,@DB_ID = db_id()

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

CREATE TABLE #IndexBaseLine
    (
    row_id int IDENTITY(1,1)
    ,index_action varchar(10)
    ,schema_id int
    ,schema_name sysname
    ,object_id int
    ,table_name sysname
    ,index_id int
    ,index_name nvarchar(128)
    ,is_unique bit DEFAULT(0)
    ,has_unique bit DEFAULT(0)
    ,type_desc nvarchar(67)
    ,partition_number int
    ,reserved_page_count bigint
    ,size_in_mb decimal(12, 2)
    ,buffered_page_count int
    ,buffer_mb decimal(12, 2)
    ,pct_in_buffer decimal(12, 2)
    ,table_buffer_mb decimal(12, 2)
    ,row_count bigint
    ,impact int
    ,existing_ranking bigint
    ,user_total bigint
    ,user_total_pct decimal(6, 2)
    ,estimated_user_total_pct decimal(6, 2)
    ,user_seeks bigint
    ,user_scans bigint
    ,user_lookups bigint
    ,row_lock_count bigint
    ,row_lock_wait_count bigint
    ,row_lock_wait_in_ms bigint
    ,row_block_pct decimal(6, 2)
    ,avg_row_lock_waits_ms bigint
    ,indexed_columns nvarchar(max)
    ,included_columns nvarchar(max)
    ,indexed_columns_compare nvarchar(max)
    ,included_columns_compare nvarchar(max)
    ,duplicate_indexes nvarchar(max)
    ,overlapping_indexes nvarchar(max)
    ,related_foreign_keys nvarchar(max)
    ,related_foreign_keys_xml xml
    )

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

CREATE TABLE #ForeignKeys
    (
    foreign_key_name sysname
    ,object_id int
    ,fk_columns nvarchar(max)
    ,fk_columns_compare nvarchar(max)
    )

;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
),MemoryBuffer
AS (
    SELECT au.object_id
        ,au.index_id
        ,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
    WHERE bd.database_id = db_id()
    GROUP BY au.object_id, au.index_id, au.partition_number
)
INSERT INTO #IndexBaseLine
    (schema_id, schema_name, object_id, table_name, index_id, index_name, is_unique, type_desc, partition_number, reserved_page_count, size_in_mb, buffered_page_count, buffer_mb, pct_in_buffer, row_count, existing_ranking, user_total, user_total_pct, user_seeks, user_scans, user_lookups, row_lock_count, row_lock_wait_count, row_lock_wait_in_ms, row_block_pct, avg_row_lock_waits_ms, indexed_columns, included_columns, indexed_columns_compare, included_columns_compare)
SELECT s.schema_id
    ,s.name as schema_name
    ,t.object_id
    ,t.name as table_name
    ,i.index_id
    ,COALESCE(i.name, 'N/A') as index_name
    ,i.is_unique
    ,CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END + i.type_desc as type_desc
    ,ps.partition_number
    ,ps.reserved_page_count 
    ,CAST(reserved_page_count * CAST(8 as float) / 1024 as decimal(12,2)) as size_in_mb
    ,mb.buffered_page_count
    ,mb.buffer_mb
    ,CAST(100*buffer_mb/NULLIF(CAST(reserved_page_count * CAST(8 as float) / 1024 as decimal(12,2)),0) AS decimal(12,2)) as pct_in_buffer
    ,row_count
    ,ROW_NUMBER() 
        OVER (PARTITION BY i.object_id ORDER BY i.is_primary_key desc, ius.user_seeks + ius.user_scans + ius.user_lookups desc) as existing_ranking
    ,ius.user_seeks + ius.user_scans + ius.user_lookups as user_total
    ,COALESCE(CAST(100 * (ius.user_seeks + ius.user_scans + ius.user_lookups)
        /(NULLIF(SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) 
        OVER(PARTITION BY i.object_id), 0) * 1.) as decimal(6,2)),0) as user_total_pct
    ,ius.user_seeks
    ,ius.user_scans
    ,ius.user_lookups
    ,ios.row_lock_count 
    ,ios.row_lock_wait_count 
    ,ios.row_lock_wait_in_ms 
    ,CAST(100.0 * ios.row_lock_wait_count/NULLIF(ios.row_lock_count, 0) AS decimal(12,2)) AS row_block_pct 
    ,CAST(1. * ios.row_lock_wait_in_ms /NULLIF(ios.row_lock_wait_count, 0) AS decimal(12,2)) AS avg_row_lock_waits_ms 
    ,STUFF((SELECT ', ' + QUOTENAME(c.name)
            FROM sys.index_columns ic
                INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            WHERE i.object_id = ic.object_id
            AND i.index_id = ic.index_id
            AND is_included_column = 0
            ORDER BY key_ordinal ASC
            FOR XML PATH('')), 1, 2, '') AS indexed_columns
    ,STUFF((SELECT ', ' + QUOTENAME(c.name)
            FROM sys.index_columns ic
                INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            WHERE i.object_id = ic.object_id
            AND i.index_id = ic.index_id
            AND is_included_column = 1
            ORDER BY key_ordinal ASC
            FOR XML PATH('')), 1, 2, '') AS included_columns
    ,(SELECT QUOTENAME(ic.column_id,'(')
            FROM sys.index_columns ic
            WHERE i.object_id = ic.object_id
            AND i.index_id = ic.index_id
            AND is_included_column = 0
            ORDER BY key_ordinal ASC
            FOR XML PATH('')) AS indexed_columns_compare
    ,COALESCE((SELECT QUOTENAME(ic.column_id, '(')
            FROM sys.index_columns ic
            WHERE i.object_id = ic.object_id
            AND i.index_id = ic.index_id
            AND is_included_column = 1
            ORDER BY key_ordinal ASC
            FOR XML PATH('')), SPACE(0)) AS included_columns_compare
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.indexes i ON t.object_id = i.object_id
    INNER JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id
    LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = db_id()
    LEFT OUTER JOIN sys.dm_db_index_operational_stats(@DB_ID, NULL, NULL, NULL) ios ON ps.object_id = ios.object_id AND ps.index_id = ios.index_id AND ps.partition_number = ios.partition_number
    LEFT OUTER JOIN MemoryBuffer mb ON ps.object_id = mb.object_id AND ps.index_id = mb.index_id AND ps.partition_number = mb.partition_number
WHERE t.object_id = @ObjectID OR @ObjectID IS NULL

INSERT INTO #IndexBaseLine
    (schema_id, schema_name, object_id, table_name, index_name, type_desc, impact, existing_ranking, user_total, user_seeks, user_scans, user_lookups, indexed_columns, included_columns)
SELECT s.schema_id
    ,s.name AS schema_name
    ,t.object_id
    ,t.name AS table_name
    ,'--MISSING--' AS index_name
    ,'--NONCLUSTERED--' AS type_desc
    ,(migs.user_seeks + migs.user_scans) * migs.avg_user_impact as impact
    ,0 AS existing_ranking
    ,migs.user_seeks + migs.user_scans as user_total
    ,migs.user_seeks 
    ,migs.user_scans
    ,0 as user_lookups
    ,COALESCE(equality_columns + ', ', SPACE(0)) + COALESCE(inequality_columns, SPACE(0)) as indexed_columns
    ,included_columns
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.dm_db_missing_index_details mid ON t.object_id = mid.object_id
    INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
    INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = db_id()
AND (mid.object_id = @ObjectID OR @ObjectID IS NULL)

INSERT INTO #ForeignKeys
    (foreign_key_name, object_id, fk_columns, fk_columns_compare)
SELECT fk.name + '|PARENT' AS foreign_key_name
    ,fkc.parent_object_id AS object_id
    ,STUFF((SELECT ', ' + QUOTENAME(c.name)
            FROM sys.foreign_key_columns ifkc
                INNER JOIN sys.columns c ON ifkc.parent_object_id = c.object_id AND ifkc.parent_column_id = c.column_id
            WHERE fk.object_id = ifkc.constraint_object_id
            ORDER BY ifkc.constraint_column_id
            FOR XML PATH('')), 1, 2, '') AS fk_columns
    ,(SELECT QUOTENAME(ifkc.parent_column_id,'(')
            FROM sys.foreign_key_columns ifkc
            WHERE fk.object_id = ifkc.constraint_object_id
            ORDER BY ifkc.constraint_column_id
            FOR XML PATH('')) AS fk_columns_compare
FROM sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE fkc.constraint_column_id = 1
AND (fkc.parent_object_id = @ObjectID OR @ObjectID IS NULL)
UNION ALL
SELECT fk.name + '|REFERENCED' as foreign_key_name
    ,fkc.referenced_object_id AS object_id
    ,STUFF((SELECT ', ' + QUOTENAME(c.name)
            FROM sys.foreign_key_columns ifkc
                INNER JOIN sys.columns c ON ifkc.referenced_object_id = c.object_id AND ifkc.referenced_column_id = c.column_id
            WHERE fk.object_id = ifkc.constraint_object_id
            ORDER BY ifkc.constraint_column_id
            FOR XML PATH('')), 1, 2, '') AS fk_columns
    ,(SELECT QUOTENAME(ifkc.referenced_column_id,'(')
            FROM sys.foreign_key_columns ifkc
            WHERE fk.object_id = ifkc.constraint_object_id
            ORDER BY ifkc.constraint_column_id
            FOR XML PATH('')) AS fk_columns_compare
FROM sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE fkc.constraint_column_id = 1
AND (fkc.referenced_object_id = @ObjectID OR @ObjectID IS NULL)

UPDATE ibl
SET duplicate_indexes = STUFF((SELECT ', ' + index_name AS [data()]
            FROM #IndexBaseLine iibl
            WHERE ibl.object_id = iibl.object_id
            AND ibl.index_id <> iibl.index_id
            AND ibl.indexed_columns_compare = iibl.indexed_columns_compare
            AND ibl.included_columns_compare = iibl.included_columns_compare
            FOR XML PATH('')), 1, 2, '')
    ,overlapping_indexes = STUFF((SELECT ', ' + index_name AS [data()]
            FROM #IndexBaseLine iibl
            WHERE ibl.object_id = iibl.object_id
            AND ibl.index_id <> iibl.index_id
            AND (ibl.indexed_columns_compare LIKE iibl.indexed_columns_compare + '%' 
                OR iibl.indexed_columns_compare LIKE ibl.indexed_columns_compare + '%')
            AND ibl.indexed_columns_compare <> iibl.indexed_columns_compare 
            FOR XML PATH('')), 1, 2, '')
    ,related_foreign_keys = STUFF((SELECT ', ' + foreign_key_name AS [data()]
            FROM #ForeignKeys ifk
            WHERE ifk.object_id = ibl.object_id
            AND ibl.indexed_columns_compare LIKE ifk.fk_columns_compare + '%'
            FOR XML PATH('')), 1, 2, '')
    ,related_foreign_keys_xml = CAST((SELECT foreign_key_name
            FROM #ForeignKeys ForeignKeys
            WHERE ForeignKeys.object_id = ibl.object_id
            AND ibl.indexed_columns_compare LIKE ForeignKeys.fk_columns_compare + '%'
            FOR XML AUTO) as xml) 
FROM #IndexBaseLine ibl

INSERT INTO #IndexBaseLine
    (schema_id, schema_name, object_id, table_name, index_name, type_desc, existing_ranking, indexed_columns)
SELECT s.schema_id
    ,s.name AS schema_name
    ,t.object_id
    ,t.name AS table_name
    ,fk.foreign_key_name AS index_name
    ,'--MISSING FOREIGN KEY--' as type_desc
    ,9999
    ,fk.fk_columns
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN #ForeignKeys fk ON t.object_id = fk.object_id
    LEFT OUTER JOIN #IndexBaseLine ia ON fk.object_id = ia.object_id AND ia.indexed_columns_compare LIKE fk.fk_columns_compare + '%'
WHERE ia.index_name IS NULL

;WITH Aggregation
AS (
    SELECT row_id
        ,CAST(100. * (user_seeks + user_scans + user_lookups)
            /(NULLIF(SUM(user_seeks + user_scans + user_lookups) 
            OVER(PARTITION BY schema_name, table_name), 0) * 1.) as decimal(12,2)) AS estimated_user_total_pct
        ,SUM(buffer_mb) OVER(PARTITION BY schema_name, table_name) as table_buffer_mb
    FROM #IndexBaseLine 
)
UPDATE ibl
SET estimated_user_total_pct = COALESCE(a.estimated_user_total_pct, 0)
    ,table_buffer_mb = a.table_buffer_mb
FROM #IndexBaseLine ibl
    INNER JOIN Aggregation a ON ibl.row_id = a.row_id

;WITH IndexAction
AS (
    SELECT row_id
        ,CASE WHEN user_lookups > user_seeks AND type_desc IN ('CLUSTERED', 'HEAP', 'UNIQUE CLUSTERED') THEN 'REALIGN'
            WHEN type_desc = '--MISSING FOREIGN KEY--' THEN 'CREATE'
            WHEN type_desc = 'XML' THEN '---'
            WHEN is_unique = 1 THEN '---'
            WHEN type_desc = '--NONCLUSTERED--' AND ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY user_total desc) <= 10 AND estimated_user_total_pct > 1 THEN 'CREATE'
            WHEN type_desc = '--NONCLUSTERED--' THEN 'BLEND'
            WHEN ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY user_total desc, existing_ranking) > 10 THEN 'DROP' 
            WHEN user_total = 0 THEN 'DROP' 
            ELSE '---' END AS index_action
    FROM #IndexBaseLine
)
UPDATE ibl
SET index_action = ia.index_action
FROM #IndexBaseLine ibl INNER JOIN IndexAction ia
ON ibl.row_id = ia.row_id

UPDATE ibl
SET has_unique = 1
FROM #IndexBaseLine ibl
    INNER JOIN (SELECT DISTINCT object_id FROM sys.indexes i WHERE i.is_unique = 1) x ON ibl.object_id = x.object_id

SELECT 
    index_action
    ,schema_name + '.' + table_name as object_name
    ,index_name
    ,is_unique
    ,has_unique
    ,type_desc
    ,size_in_mb
    ,buffer_mb
    ,pct_in_buffer
    ,table_buffer_mb
    ,row_count
    ,user_total
    ,user_total_pct
    ,estimated_user_total_pct
    ,user_seeks
    ,user_scans
    ,user_lookups
    ,row_lock_count
    ,row_lock_wait_count
    ,row_lock_wait_in_ms
    ,row_block_pct
    ,avg_row_lock_waits_ms
    ,indexed_columns
    ,included_columns
    ,duplicate_indexes
    ,overlapping_indexes
    ,related_foreign_keys
    ,related_foreign_keys_xml 
FROM #IndexBaseLine
ORDER BY table_buffer_mb DESC, object_id, user_total DESC

NOTE: Populating the table name in the function for the @ObjectID variable is optional. If the value of @ObjectID is NULL then information on all tables is returned.

This version of the query populates the following columns:

row_id: Row identifier used for populating the table
index_action: Analysis recommendation on action to take on the index

CREATE: Recommend adding the index to the table.
DROP: Recommend dropping the index from the table
BLEND: Review the missing index details to see if the missing index details can be added to an existing index.
REALIGN: Bookmark lookups on the index exceed the number of seeks on the table. Recommend investigating whether to move the clustered index to another index or add included columns to the indexes that are part of the bookmark lookups.

schema_id: Schema ID
schema_name: Name of the schema.
object_id: Object ID
table_name: Name of the table name
index_id: Index ID
index_name: Name of the index.
is_unique: Flag indicating whether an index has a unique index.
has_unique: Flag indicating whether the table has a unique index.
type_desc: Type of index; either clustered or non-clustered.
partition_number: Partition number.
reserved_page_count: Total number of pages reserved for the index.
size_in_mb: The amount of space in MB the index utilizes on disk.
buffered_page_count: Total number of pages in the buffer for the index.
buffer_mb: The amount of space in MB in the buffer for the index.
pct_in_buffer: The percentage of an index that is current in the SQL Server buffer.
table_buffer_mb: The amount of space in MB in the SQL Server buffer that is being utilized by the table.
row_count: Number of rows in the index.
impact: Calculation of impact of a potential index. This is based on the seeks and scans that the index could have utilized multiplied by average improvement the index would have provided. This is included only for missing indexes.
existing_ranking: Ranking of the existing indexes ordered by user_total descending across the indexes for the table.
user_total: Total number of seek, scan, and lookup operations for the index.
user_total_pct: Percentage of total number of seek, scan, and lookup operations for this index compared to all seek, scan, and lookup operations for existing indexes for the table.
estimated_user_total_pct: Percentage of total number of seek, scan, and lookup operations for this index compared to all seek, scan, and lookup operations for existing and potential indexes for the table. This number is naturally skewed because a seek for potential Index A resulted in another operation on an existing index and both of these operations would be counted.
user_seeks: Number of seek operations on the index.
user_scans: Number of scan operations on the index.
user_lookups: Number of lookup operations on the index.
row_lock_count: Cumulative number of row locks requested.
row_lock_wait_count: Cumulative number of times the Database Engine waited on a row lock.
row_lock_wait_in_ms: Total number of milliseconds the Database Engine waited on a row lock.
row_block_pct: Percentage of row locks that encounter waits on a row lock.
avg_row_lock_waits_ms: Average number of milliseconds the Database Engine waited on a row lock.
indexed_columns: Columns that are part of the index, missing index or foreign key.
included_columns: Columns that are included in the index or missing index.
indexed_columns_compare: Column IDs that are part of the index, missing index or foreign key
included_columns_compare: Column IDs that are included in the index or missing index.
duplicate_indexes: List of Indexes that exist on the table that are identical to the index on this row.
overlapping_indexes: List of Indexes that exist on the table that overlap the index on this row.
related_foreign_keys: List of foreign keys that are related to the index either as an exact match or covering index.
related_foreign_keys_xml: XML document listing foreign keys that are related to the index either as an exact match or covering index.

As I mentioned in a previous post, when looking at the index_action column don’t take a literal translation of the recommendation. These are put together from my personal experience and used as a pointer towards a direction versus a specific directive.

Hopefully breaking apart the index analysis query into multiple posts has helped people grasp why each piece was added and learn a little bit more about the underlying DMVs and catalog views used to put this together.

In future posts some examples will be put together to look at how to take snapshots of indexes with this query. And also to take the recommendations and implement them to improve the indexing on databases.

EDIT May 18, 2009: Fixed error in aggregating type 2 sys.partition data.

Beta Exam 71-680: TS: Windows 7, Configuring

2009-04-28T13:09:00Z

Microsoft has announced that the Beta exam for 71-680: TS: Windows 7, Configuring has been opened up. Beta exams are free to take are if you are planning to take the exam at a later date, now is as good a time as any… well a little studying first might help.

It doesn’t appear that the outline for the exam is up on Microsoft Learning yet, but you can get a lot of information from the Windows 7 - Learning Portal.

The beta exam is open through May 18th and I really recommend taking it to help vet out the exam and… well, it’s free. Be sure to be as wordy as possible in the exam comment section the people that work on the exam put a lot of time into reading comments and adjusting the exams around those comments.