|
|
-
 Ever need to group dates and round them to intervals? I had this exact need for a report on wait stats I was working on for an upcoming post. I wanted the flexibility to be able to group a list of dates by either minute, hour, date, etc. And then also round those dates within those intervals. For example, round a list of dates to every 15 minutes, every 3 hours, or every 2 days. At first thought this sounded like something that might be a little tricky to accomplish. But as I’ll show in the examples below, this is quite easy.
Start With The First of the Month To resolve this issue, I started by using the logic that I typically when I need to take a date and change it to the first day of a month or quarter. The logic I use for that utilizes the DATEADD and DATEDIFF functions. I’ll skip going into how those work since Books Online covers that pretty well. To find the start of a month or quarter, these functions can be used together to be compare a date and time against the value 0. Casting 0 as datetime returns the value 1900-01-01 00:00:00.000. Using the two functions together, use DATEDIFF to determine how many months or quarters have occurred since 1900-01-01. And then use DATEADD to add those months or quarters to the date 1900-01-01. Doing this will provide you with the the first day in either the month or the quarter. The examples below can be used to demonstrate this: SELECT DATEADD(M, DATEDIFF(M, 0, '20100310'), 0)
GO
SELECT DATEADD(Q, DATEDIFF(Q, 0, '20100310'), 0)
GO
Returning the following results:
Round to the Desired Interval
Now that’s the easy part which leaves the next part, where dates and times need to be rounded not to the 1st of the month put to periods that are determined at execution time. As I mentioned above, I need to change the logic above such that I can get dates to round to every 1 week or 2 days or 3 hours.
On the surface, this sounded a lot harder than it turned out to be. Because if you use the % to find the remainder of division, you can easy round any date or time to any logical grouping of dates or times.
Suppose you need to round a datetime to the nearest 4 hours. Find the distance in hours between 1900-01-01 and the datetime in question. Take that number and find the modulo using your rounding interval. In this case that value is 4. Then subtract the remainder from the distance. And use the new distance value in the DATEADD function from 1900-01-01. Hopefully, that didn’t lose too many people.
Rather than explain it another way, take a look at the following T-SQL statements where the datetime is rounded down to the nearest 2, 3, and 4 hour intervals.
--Round down to nearest 2 hours.
SELECT DATEADD(HH, DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')-(DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')%2), 0)
GO
--Round down to nearest 3 hours.
SELECT DATEADD(HH, DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')-(DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')%3), 0)
GO
--Round down to nearest 4 hours.
SELECT DATEADD(HH, DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')-(DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')%4), 0)
GO
These statements produce the following results:
As you’ll hopefully see, this is a very simple technique. Imagine replacing the hour, day, or month date parts with a variable and then the interval to round the dates with a variable. Doing that and the expression can be used in a stored procedure or report to group any set of dates along any set of date or time groupings.
Hope you find this as useful as I did. And in the next couple days, I hope to have something up that uses this technique to make it even easier to use in your own code by providing fully working examples.
|
-
It’s that time of the month again. Time to network and learn about SQL Server with your peers.
|
Location: |
8300 Norman Center Drive, 9th Floor, Bloomington, MN 55437 |
|
Date: |
March 16, 2010 |
|
Time: |
2:30 - 5:00 |
Please click here for meeting details and to RSVP.
Registration has changed with the move from our previous hosting site and you will be required to log into www.sqlpass.org in order to register for our events. If you have any issues with this, please contact support@mnssug.org.
ABCs of CTEs
Jason Strate
Common Table Expressions (CTE) aren’t as common as their name implies. CTEs are often seen as a secret part of the dark art of recursion. This session will explore CTEs to show how they can be extremely useful in improving performance and legibility of T-SQL code. And, of course, we will look at their use in returning recursive data.
Database Design Fundamentals
Louis Davidson
In this session I will give an overview of how to design a database, including the common normal forms and why they should matter to you if you are creating or modifying SQL Server databases. Data should be easy to work with in SQL Server if the database has been organized as close as possible to the standards of normalization that have proven for many years. Many common T-SQL programming "difficulties" are the result of struggling against the way data should be structured and can be avoided by applying the basic normalization techniques and are obvious things that you find yourself struggling with time and again (i.e. using the SUBSTRING function in a WHERE clause meaning you can't use an index efficiently).
|
-
If you’ve got the time, stop in to socialize and learn a bit about SQL Server. Kalen Delaney will be in town teaching a class over at Benchmark Learning and has offered to join the meeting to talk about Simple Recovery Model. Also, Barbara Rokke has offered to share some of her experiences with partitioning in SQL Server. Here are the meeting details: | Location: | 8300 Norman Center Drive, 9th Floor, Bloomington, MN 55437 | | Date: | February 23, 2010 | | Time: | 4:00 -7:00 |
Case Study: A Partitioning Strategy for a VLDB Barbara Rokke, 3M With our database growing rapidly from MB to TB, maintenance tasks locking users out of data access and a 7x24 data access requirement by the clients, the DBA team needed to come up with a better way of managing the large database. Partitioning was discussed however the DBA team didn't have time to develop a manual partition strategy. With the release of SQL Server 2005, partitioning became a viable option. After discussions with Microsoft experts and reading every bit of information available on the web (only 2 websites offering valuable information at the time), the DBA team took the leap into the Partitioning world. Partitioning was installed and a two year window processing script was developed to maintain a rolling window of data. This presentation will provide a review of the thought process and the implementation of the partitioning strategy the DBA team developed. Also a walk-through of the two year window processing to show self-maintenance partitioning will be reviewed. What is Minimal Logging? Kalen Delaney There is a common misconception that Simple Recovery Model means no logging, and this is a very dangerous myth to propagate. SQL Server does log database changes in Simple Recovery, but some (not all) operations are minimally logged. In this session, I'll discuss what exactly minimal logging means and what the benefits and dangers of Simple Recovery model are.
|
-
 A couple weeks back I was asked, “How come Microsoft has yet to put TRIM() in SQL?”
I don’t really know why there isn’t a TRIM() function in SQL Server. There is an LTRIM for function removing leading spaces. And an RTRIM function for removing trailing spaces. But there isn’t a TRIM function.
Let’s be honest, the why doesn’t matter to me – I don’t want the function to be a part of SQL Server. So Instead of figuring out why, I’m going to write about my opinions. This is a great place for them, this being my blog and all.
There are two main reasons that I am against having a TRIM function in SQL Server…
What Do You Know
First, a large majority of people tend to stick with what they know. Once you find something that works, why not stick with it until there is a reason not to use it any longer. Unfortunately this can have some unintended consequences.
 Most of the developers that have built the applications that I consult on were originally designed by application developers versus SQL Server developers. This distinction is important because a lot of these developers are then aware of the .NET or Java function for TRIM in the languages that they are using. In their cases, using it will not have a seriously negative impact on their environments.
But that “knowledge of a function” doesn’t translate well into SQL Server. In SQL Server, the use of TRIM would have a serious impact on the effectiveness of indexing being used to query the database. In case you don’t believe me, this will be demonstrated below.
What Do You Need
Secondly, how often do leading spaces need to be removed from a field? Sure, I’ve removed them while loading data into a database in an ETL process. But most often, leading space saved to a field in a database is just “bad” data that I’d rather clean up.
Are there reasons to have leading spaces in a field? Probably, but a vast majority of the time they won’t be there and they aren’t going to be needed.
By this logic, most of the time only trailing spaces will need to be removed from a field.
Considering the Two Reasons
If leading spaces don’t need to removed but people know about TRIM from their primary programming language, will they be inclined to find out if there is a function the only removes trailing spaces? Or would a person be more inclined to just use what they know. I understand the nature of laziness and pretty confident that people would end up just using TRIM().
Depending on the spaces that need to be removed can have a serious impact on performance. As an example of the performance impact let’s consider the following query.
USE AdventureWorks
SELECT * FROM Person.Contact
WHERE EmailAddress = 'gustavo0@adventure-works.com' When executed, the query returns the following execution plan:
You’ll notice that since there is an index on the column EmailAddress, that the Query Optimizer chooses an Index Seek on the column to find the value(s) the are being filtered. And then it looks up the rest of the columns with a Key Lookup operation.
Suppose though that the column had trailing spaces in it that needed to be removed when querying the data. To do this, you’d need to use the RTRIM() function. The query in this case would likely look like this.
And when executed the query would return the following execution plan:
And in this case, the Query Optimizer has chosen to use an Index Scan. It can do this because SQL Server knows how the value begins and can scan for similar values within the index. And then follow-up with a Key Lookup for the rest of the values in the query.
But what we really are talking about is a function that removes the leading and the trailing spaces. To do this you would need to use the LTRIM() function in conjunction with the RTRIM() function. And now the query would look something like this:
USE AdventureWorks
SELECT * FROM Person.Contact
WHERE LTRIM(RTRIM(EmailAddress)) = 'gustavo0@adventure-works.com' Running that query would produce the following execution plan:
As you see now, the execution plan is quite different from the previous two executions. Now, the Query Optimizer doesn’t know the first letters of the values in the EmailAddress column. With the existing index being useless, the query doesn’t have any other good candidate to find the values from the WHERE clause. This results in the query just doing a Clustered Index Scan.
Of course, the last execution plan looks like it is simpler, but closer scrutiny of the execution would show that the IO between each of the queries has some significant differences.
--EmailAddress = 'gustavo0@adventure-works.com'
Table 'Contact'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--RTRIM(EmailAddress) = 'gustavo0@adventure-works.com'
Table 'Contact'. Scan count 1, logical reads 178, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--LTRIM(RTRIM(EmailAddress)) = 'gustavo0@adventure-works.com'
Table 'Contact'. Scan count 1, logical reads 569, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Based on my people going with what they know, the addition of a TRIM() function would likely result in some serious performance issues with some queries. This would likely happen just as a matter of development without any thought as to the effect and difference between the performance of RTRIM(), LTRIM(), and TRIM().
Some People Want TRIM
Now my opinion isn’t the only one that is out there. Quite often developers I work with ask about the TRIM() function. And Pinal Dave (blogging at SQLAuthority) put in a Connect item regarding adding a TRIM() function. He’s had a number of posts regarding this topic and ways to get around the lack of a TRIM() function.
While I appreciate the discussions I’ve had with him, one of those time on the cursed “it depends”, I’ll have to disagree with him on adding a TRIM() function. The pain in this discovery from people just coming to SQL Server is worth the benefit of unnecessary performance death that could occur by overuse of a TRIM() function. I actually would encourage people to vote down this Connect item.
|
-
A while back, I noticed that Paul Randal (on twitter as @paulrandal) had started a new blog meme on three events that brought their life to where it is today. I’ve skipped a lot the past year’s memes due to time constraints but thought I’d chime in on this one and have a go at it.  First I Was A DJ
Back when I was an extremely shy version of current myself, I had gone to a wedding. The DJ sucked. The musical choices were poor. Songs were cut off in the middle of play for something new. Not a fade to something better, but an abrupt stop and reversal of the entire tone of the moment. And the equipment had no sound quality what-so-ever. In fact, the DJ sucked so bad that I was convinced I could have done a better job. As I was "armchair quarterbacking" the DJ the following week, I said that I should do that. If she could, then I definitely could. And the I was approached by a co-worker who asked if I started a company if I would DJ her wedding. After thinking about it a bit, I decided to take her up on the offer. The biggest problem after getting her money and contract was a general lack of any of the required infrastructure. I worked out a deal with my uncle to purchase the necessary speakers, mixers, and lights. And then I started shopping for CDs. Within a couple months, I was approaching nearly 1,000 CDs and had a new problem. How do I make music lists without a lot of work? How should I catalog the music? How will I find the next song to play when the current song is playing? That led me to take a look at the programs on my computer and I found one called... Access 2.0. I wasn't really sure what it was, but it seemed that it could do what I needed. I sat down and after hundreds of hours of tinkering over the years, I built a fairly robust database that I could use to manage and organize music for my business. And I was officially a database hack. I will qualify, I was a wedding DJ. I did not have two turntables. But I did have a microphone.  Then I Sucked At Credit Analysis
When I was a database hack, I hadn't actually believed anyone could make money or have a career with computer-machines. So instead, I pretended to go to college and worked as a server. Somewhere in there I decided to pick up a temp job and started at a bank working on entering credit card applications. Oh yeah! Data entry. I rocked at the data entry and was soon moved off of that to reviewing individual credit cards. And then off to corporate credit using the internet to try and research the background and news happening with the bank's customers. One of the greatest lessons I learned at this point was that almost everything was on the internet, it was just a matter of submitting the right query. Doing good at your job can have it's curses. I was soon asked to move to another group to do credit analysis within the corporate credit department. I didn't have a background in finances but I was young and willing to do the work in front of me. Long and the short of it, I sucked at credit analysis. I was so clueless as to what I was doing and trying to fake it while I tried to learn what I needed to know. I was in over my head and ready to quit. One of my old managers recognized this and remembered when I was stellar and offered to bring me over to her group rather than my current manager letting me go. My desire to quit had been a little bit slower than the current managers desire to fire me. Fortunately, though, before either happened I was moved to a merchant fraud risk group. The nice thing about this group is that I eventually got a chance to highlight my database hack skills. They had an Access database that they used for uncovering merchant fraud. More specifically, they had an Access database that 100% mimicked a mainframe application and was as user friendly. They even managed to make it uglier than its predecessor.
One thing led to another and when I left this department, I was a SQL Server developer. The Access database now had a SQL Server backend. And I was heading to a data warehousing group to write DTS packages.  I Got Divorced
Throughout my time at the bank, I had a number of life changing events that occurred. Some of the highlights were getting married and having four children. And some of the lows were my daughter and my best friend from my college years passing away. I worked a lot and didn't pay attention to my spouse or children nearly as much as I should have. And really wasn’t having a good time with life. All of this culminated in getting divorced. For many divorce is a low point, and I’ll admit to having some bad days after it started. But as a whole it was one of the best decisions of my life. And really lead to a lot of success, happiness, and a chance to be a better father to my children. It gave me an opportunity to re-think my life and career. This was when I decided that I should control my career and not just let it happen to me, which is when I started blogging and started a new job at Digineer as a consultant. I also decided to stop putting off things that I wanted to do and live my life in a way that I was satisfied with every day and what I did with it. Since then I’ve had a lot of fun with my kids. Enjoyed life and where it is going. And I can once again count my ex-wife as a very good friend. Life is good.
|
-
Looking for an introduction into the different high availability technologies available in SQL Server? Check out this webcast that I recorded for SSWUG. It’s being made available on Feb 24th and covers the following: With failover clustering, data mirroring, peer-to-peer replication, and log shipping available there is an option or two for every scenario. While each technology has a place in the world, they aren't interchangeable and provide the greatest benefit when applied to the proper solution. A walkthrough of each of these technologies and where they fit in your environment. You can register here.
|
-
 I’d like to thank Pam Shaw (twitter as @pamshaw) and Jorge Nunez (twitter as @sqlchicken) for letting me come down to Tampa this past weekend to present at SQL Saturday #32. Jeez, that picture doesn’t make me look very happy. For someone like me that tends to forget to take time off, this is a great way to get out and travel and still feel like I’m being a workaholic.
The session I presented was ABCs of CTEs. Here’s the description of the session: Common Table Expressions (CTE) aren’t as common as their name implies. CTEs are often seen as a secret part of the dark art of recursion. This session will explore CTEs to show how they can be extremely useful in improving performance and legibility of T-SQL code. And, of course, we will look at their use in returning recursive data. I put this session together a couple years ago when I realized I was behind the ball in leveraging CTEs into the T-SQL that I was writing. And after asking around found there was a lot of confusion surrounding their use. Hopefully, people that sat through the session go something out of it. I’ve uploaded the slide deck to the SQL Saturday site but for those that just want the information now… here’s the presentation material:
|
-
 Figured since I helped write the meeting announcement and newsletter for PASSMN this month that I'd just copy most of the details. Since last month we've moved from our old host to a new host, so the RSVP/registration process has changed a bit. Hopefully, there aren't any hurdles but if so please contact me and I'll help anyone that needs to get registered.
Location: 8300 Norman Center Drive, 9th Floor, Bloomington, MN 55437
Date: January 19, 2010
Time: 3:00-5:30 Please click here for meeting details and to RSVP. SQL Azure Sarita Garg, United Health Group This session will answer some of the following questions about new state of the art technology SQL Azure from Microsoft - What is SQL Azure?
- Why to use it?
- How does it work?
- How the pricing and licensing will work?
- Role of DBAs, Developers with SQL Azure
- Future of SQL Azure
SQL Server 2008 R2 Jin Cho and Lara Rubbelke, Microsoft Some of the details are still being flushed out on this presentations, but this will be covering the items below with some demos of these features. - Overview/Highlight of SQL Server 2008 R2 (new features)
- PowerPivot - Excel Add-in, deployment to SharePoint, IT Management
Other News Live Meeting - Beginning with the January 2010 meeting, you will now be able to join monthly meetings virtually through Live Meeting. This is a great opportunity to stay connected that has been provided to us by PASS.
PASS Summit 2010 - Registration for PASS Summit 2010 is now open. Register for only $995 until Jan 15. Tap into your 2009 or 2010 training budgets to take advantage of the lowest rate for PASS Summit.
|
-
 I've been selected to speak at the Tampa SQL Saturday coming up at the end of January. I'm a bit pumped to be going down there since I woke up this morning to below zero weather for yet another day. Not that I'm complaining, but my feet could definitely use a little bit of heat. I know it's a bit chilly down there right now, I heard it was 37 degrees. But let me tell you... that would be awesome right about now.
Here is the topic that I'll be speaking on: ABCs of CTEs Common Table Expressions (CTE) aren’t as common as their name implies. CTEs are often seen as a secret part of the dark art of recursion. This session will explore CTEs to show how they can be extremely useful in improving performance and legibility of T-SQL code. And, of course, we will look at their use in returning recursive data. Prerequisites - Understand of basic T-SQL coding
Goals - Provide an understanding of the purpose and use of CTEs for recursion and derived tables
- Define the pro’s and con’s to using CTEs in recursion and for derived tables
- Demonstrate real-world examples detailing methods for using CTEs to solve coding challenges
Hopefully if you are in the area, you'll stop by and listen. If you want to, feel free to make fun of my lack of sun from the long, cold Minnesota winter. And now that I've purchase flight, hotel, and the rental... I'll be there for certain.
|
-
A few weeks ago I posted a job that I use to track wait stats in the DBA database that I bring to a number of clients. I had promised to follow-up with a couple or three reports that could be used to monitor the values that were being aggregated in those tables. Before getting to that, though, a quick look at the output of sys.dm_os_wait_stats might have indicated that there is a small issue with looking at the wait stats as they currently are. There are a lot of wait types. Specifically, my instances of SQL Server report 202 wait types for SQL Server 2005 and 485 wait types for SQL Server 2008. Combining the two lists yields nearly 500 distinct wait types between SQL Server 2005 and 2008. Ever notice that the MISCELLANEOUS wait type appears twice for SQL Server 2008, I bet there is a good story behind that one. With all of those wait types, there needs to be a method to group them together to assist in researching resource performance related issues. Of course, you already knew this and it’d probably be nice if I stopped my stalling and go to the point. Building A Wait Type Category List Before I go there, first I’d like to point out how I built the list and my plans for keeping it updated. What’s the value in a classification process, if you don’t know the value of the information included? To start with there are people that know the operations and internals of SQL Server much better than I do. There is even a whole department at Microsoft, the CSS SQL Server Engineers, that spends a lot time up to their elbows working with wait types. Let’s be real, I mostly know what these guys have published. Recently, Bob Ward has started a SQL Server Wait Type Repository this is the basis for the wait type categories. Why not just use this list and be done with it? Well, the repository only has 22 wait types currently listed. They are hitting the ones that provide the most value. Their table does provide a structure and starting point for this list and I consider it the source for updates to categorizing wait types. Next the list was augmented with the information provided in the SQL Server 2005 Waits and Queues white paper. This is a detailed white paper the provides categories and troubleshooting information for different wait types for SQL Server 2005. If you haven’t read this white paper you are definitely missing out and should bail on this post and go read it. The third resource I used was the SQLServerPedia Wait Type list. This list covers a number of the items that aren’t covered in the previous two sources and almost all of the remaining wait types. And depending on when you read this, it may just look identical to the wait type category list since I am hoping to submit an update of one with the other. Beyond these, there are still a few holes in the list but hopefully over time one of the three sources above or some other source will fill in the gaps and I’ll just post updates from time to time. Providing the Wait Type Category List Based on the above, here is the schema for a table that will hold all of the wait types that conforms to the structure I mentioned earlier: USE [DBADiagnostics]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Resources].[WaitTypeCategory]') AND type in (N'U'))
DROP TABLE [Resources].[WaitTypeCategory]
GO
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Resources')
DROP SCHEMA [Resources]
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Resources')
EXEC sys.sp_executesql N'CREATE SCHEMA [Resources] AUTHORIZATION [dbo]'
GO
CREATE TABLE [Resources].[WaitTypeCategory](
[WaitType] [nvarchar](60) NOT NULL,
[Category] [varchar](50) NULL,
[Resource] [varchar](50) NULL,
[Version] [varchar](50) NULL,
[Description] [varchar](255) NULL,
[Action] [varchar](max) NULL,
CONSTRAINT [PK_WaitTypeCategory] PRIMARY KEY CLUSTERED
(
[WaitType] ASC
)
)
GO
And then there is the wait types and categories. Since even WordPress thinks this is too much to add to a post, here is a script with all of it together:
If the week goes as planned, the reports I mentioned should be up by Wednesday.
|
-
 With the next release of SQL Server, data analysis is going to go through a major change with the PowerPivot add-in for Excel and SharePoint. If you haven’t been keeping up with these changes to Business Intelligence, SSWUG.org has an opportunity for you to get up to speed. On January 22, 2010 there will be an online conference on PowerPivot with the following sessions:
- Getting started with PowerPivot
- Insight and oversight: the IT perspective on PowerPivot
- Power up your Pivots! Building sophisticated applications with PowerPivot
- Beyond the desktop: PowerPivot in your business infrastructure
Not only is the this a great opportunity to learn about some new features, but it is also a free event. No excuses not to learn on this day… hope to see you online.
|
-
 I’ve blogged about certifications a number of times in the past. One of the certifications that I really want to obtain in the future is the Microsoft Certified Master (MCM) for SQL Server 2008. This is a great certification that really dives in deep and requires classroom training and labs as part of the certification process. That classroom training and the labs equates to one great thing and that is knowledge.
Since I haven’t managed to convince anyone to send me to the MCM training yet, I was pleased today to find a list of pre-reading material for the MCM program. It’s a 4-pages of reading goodness that I encourage everyone else to read. I really think that this is a gem of a find and with this being so close to the New Year, I think I may have found my first resolution for next year.
|
-
Another year is passing and with the end of the year it seems like a good time to do a quick update on where PASSMN is sitting. I had planned to mention this last week but that whole work thing got me all sidetracked. We had our final meeting of the year earlier this month and while there was a snow storm, we still had over 20 people show up. Not the greatest turnout but more than last year’s December meeting. During which it also snowed… maybe someone is telling us something about December meetings. New Board I’m pleased that I will be serving on the PASSMN Board of Directors for the next year. <sarcasm> It wasn’t a very difficult election this year, there were five people running for the five open slots on the board. So I finished in both first and last place. </sarcasm> To build on the momentum from last year, the board members that continued from last year are filling the same roles for 2010 that they did for 2009. Those board members are: New to the board this upcoming year is: Hopefully this upcoming year, we’ll have more opportunity to bring education and events to our members. As always, the PASSMN Board of Directors wants to hear from it’s members, potential speakers, and potential sponsors. You can get a hold of us through any of the above, comments on this post, or just e-mail the board. New Site As one of the last things for wrapping up this year’s business, the Board of Directors completed our move from our existing user group site to a new web site. The new site is hosted by PASS and allows us some great opportunities to coordinate our activities with the larger organization. The direct URL to the new site is http://minnesota.sqlpass.org. But to keep things easy, the old link re-directs to the new site as well. If you haven’t been out there yet, take a look… 
Some things that might be worth checking out on the new website: - Meeting announcements can be found right under the Home menu.
- Upcoming meetings are listed on the Meetings page.
- The About menu has a Contact PASSMN option with an area to leave comments.
- The Discussion area has some generic forums along with an area for recruiters and candidates to discuss open positions
- Underneath Resources, logged in users are able to find content for the last couple years worth of meetings. Going forward we’ll include session descriptions with each of the items to make materials easier to search for.
And as I mentioned above, if there is anything that should be added to the site. Let us know we’ll work on getting it out there.
|
-
 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 MCITP:DBA certifications. His career has dealt with a wide array of job responsibilities including professor, developer, trainer, database administrator, and project manager/leader. Rick has been involved with PASS since the spring of 2001 and started up an Official PASS SQL Server user group in North Carolina as well as authoring several articles on for the PASS technical journal, the SQL Server Standard. Rick has served on the PASS Board of Directors since January 2003. You can read his blog at www.sqlblog.com and contact him at rheiges@scalabilityexperts.com. Show Up And Network I’d like to encourage everyone to come down and meet someone new at the user group meeting this month. Walk up to someone you’ve not met before and introduce yourself. Share a business card. Make a contact. Who knows! The next person you meet just might be a new client or have the answer to solve your next production issue. Also, with the regular monthly swag, we’ll be giving away CDs from PASS Summit 2008. This is a library of over 160 sessions covering database administration, development, business development and professional development. A huge resource of materials that I personally get a lot of benefit out of.
|
-
 It’s a very good question. One that might not seem to insidious. Nothing that should be able to bring down the system and cause failures. Or will it?
I’ve been to a number of clients and done it myself before where I start to check out a stored procedure with some performance issues and sitting all pretty at the bottom is a GRANT EXEC statement. When I script out the stored procedure I get something similar to the following: CREATE PROCEDURE dbo.FooGetTableA
(
@Parameter varchar(4)
)
AS
SELECT Column1
FROM dbo.TableA
WHERE Column2 = @Parameter
GRANT EXEC ON dbo.FooGetTableA TO ApplicationRole
GO
But if you look carefully, there is something missing, or one could say included that shouldn’t be. Look again if you don’t see it. It’s hidden in plain sight. The permissions for the procedure are included in the body of the stored procedure. When the procedure was written, someone thought ahead to add permissions to the script but forgot the GO statement between the stored procedure
In a better world this script would have looked like this:
CREATE PROCEDURE dbo.FooGetTableA
(
@Parameter varchar(4)
)
AS
SELECT Column1
FROM dbo.TableA
WHERE Column2 = @Parameter
GO
GRANT EXEC ON dbo.FooGetTableA TO ApplicationRole
GO
It’s Just a Permission Statement
Who cares, right? So you are assigning some permissions every time that procedure executes. What harm could possibly come of it. I’ve seen this so many times and usually it’s one of things I’ll point out and say, “oops, you should take care of that”. When I should be saying, “yeah, fellas. You’ve got a time bomb there waiting for your business to take off.”
And the time bomb is deadlocks. Completely preventable deadlocks.
If you have procedures that grant themselves permissions, then as the volume of activity in your database increases you may start to see deadlock graphs similar to the following:
deadlock-list
deadlock victim=process30108bac8
process-list
process id=processec55dd68 taskpriority=0 logused=0 waitresource=METADATA: database_id = 10 PERMISSIONS(class = 1, major_id = 219199881) waittime=15000 ownerId=746424569 transactionname=Load Permission Object Cache lasttranstarted=2009-10-22T23:06:59.287 XDES=0x3712a8e98 lockMode=Sch-S schedulerid=1 kpid=5832 status=suspended spid=157 sbid=2 ecid=0 priority=0 transcount=1 lastbatchstarted=2009-10-22T23:06:59.287 lastbatchcompleted=2009-10-22T23:06:59.280 clientapp=.Net SqlClient Data Provider hostname=PRDWB0111 hostpid=5640 loginname=portaluser isolationlevel=serializable (4) xactid=746424394 currentdb=10 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=AdventureWorks2008.dbo.FooGetTableA line=1 sqlhandle=0x03000a0089b9100d0e527800669c00000100000000000000
CREATE PROCEDURE dbo.FooGetTableA
(
@Parameter varchar(4)
)
AS
SELECT Column1
FROM dbo.TableA
WHERE Column2 = @Parameter
GRANT EXEC ON dbo.FooGetTableA TO ApplicationRole
inputbuf
Proc [Database Id = 10 Object Id = 219199881]
process id=process30108bac8 taskpriority=0 logused=0 waitresource=METADATA: database_id = 10 PERMISSIONS(class = 1, major_id = 1746157316) waittime=2125 ownerId=746479249 transactionname=Load Permission Object Cache lasttranstarted=2009-10-22T23:07:12.180 XDES=0x3786c61c8 lockMode=Sch-S schedulerid=3 kpid=4048 status=suspended spid=69 sbid=2 ecid=0 priority=0 transcount=1 lastbatchstarted=2009-10-22T23:07:12.180 lastbatchcompleted=2009-10-22T23:07:12.167 clientapp=.Net SqlClient Data Provider hostname=AMBER hostpid=568 loginname=portaluser isolationlevel=serializable (4) xactid=746372404 currentdb=10 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=AdventureWorks2008.dbo.FooGetTableB line=1 sqlhandle=0x03000a00043f146882564201a09b00000100000000000000
CREATE PROCEDURE dbo.FooGetTableB
(
@Parameter varchar(4)
)
AS
SELECT Column1
FROM dbo.TableB
WHERE Column2 = @Parameter
GRANT EXEC ON dbo.FooGetTableB TO ApplicationRole
inputbuf
Proc [Database Id = 10 Object Id = 1746157316]
resource-list
metadatalock subresource=PERMISSIONS classid=class = 1, major_id = 219199881 dbid=10 id=lock4153ec880 mode=Sch-M
owner-list
owner id=process30108bac8 mode=Sch-M
waiter-list
waiter id=processec55dd68 mode=Sch-S requestType=wait
metadatalock subresource=PERMISSIONS classid=class = 1, major_id = 1746157316 dbid=10 id=lock415451780 mode=Sch-M
owner-list
owner id=processec55dd68 mode=Sch-M
waiter-list
waiter id=process30108bac8 mode=Sch-S requestType=wait
Breaking It Down
When I first started looking at these there are a few things I noted right away:
- 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
- waitresource=METADATA: database_id = 10 PERMISSIONS
- 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.
|
|
|
|