SQLServerPedia

As Cochair of the planning committee I am very pleased to announce that we are officially having a SQLSaturday in Houston. The event will be on January 29th, 2011 at the Bammel Church of Christ.

Co Chairs

Jonathan Gardner (Blog|Twitter)

Malik Al-Amin (Blog|Twitter)

Nancy Hidy Wilson (Twitter)

Information

We have created a Twitter account @SQLSatHou to distribute information. Please use the hastag #SQLSat57 when discussing the event and it will show up on the official SQLSaturday site.

The official SQLSaturday #57, Houston website is here.

Volunteers

We are always looking for people to help out. On the registration page there is a section to volunteer. I would highly encourage everyone to do this. While my motives are a bit selfish, it is really a great way to network with other volunteers and give back. If that is not enough, check out David’s Post on Five Reasons You Should Volunteer with Your Local SQL User Group and Host a SQL Saturday

Hope to see you there!

This weekend I was in Dallas, TX with Nancy Wilson (Twitter) and Malik Al-Amin (Twitter) form the Houston Area SQL Server Users Group volunteering at SQLSaturday 35. It gave us a unique perspective and taught us some very valuable lessons for when we put on SQLSaturday in Houston.

I wanted to give a big thanks to the North Texas SQL Server Users Group, all of the presenters, and volunteers to helped make this event a success. The general consensus that I heard was that everyone took something away from the event that they were going to be able to use.

Denny Cherry (Blog|Twitter) had a great write up about SQL Saturday #33. I am going to follow a similar format for this post.

The Good

Size

I was surprised by the number of people that were registered to attend. 500 people were registered and there was a wait list for the event. The event was limited to 500 people because that would exceed the fire code for the building. In the end, everyone who showed up was able to get into the event. I don’t know the official attendance number but the last number I heard was near 400. There were 42 sessions across 7 rooms with some extra sponsor sessions held during the lunch break.

Food

I ate breakfast at the hotel as I didn’t know that there was going to be breakfast at the event. I didn’t see it mentioned but to be honest that may have been an err on my part. I didn’t read all of the latest event communication so there is a large possibility that I missed the announcement. I didn’t hear anyone complaining about the food, especially with the line for Paciugo Gelato wrapping around the entire building.

Location

SQLSaturday 35 was held at the Region 10 Education Services Center in Richardson, TX. It turned out to be a great place to hold SQLSaturday as it was designed for just such a training event. As a matter of fact, the I like it so much I am going to contact the Region 4 Education Services Center in Houston to see if we can hold a SQL Saturday there soon.

The hotel was very close to the event, approximately 2 blocks. This would be just a short walk, but with temperatures over 90 degrees I don’t think anyone did. I did not hear of anyone that attended the event from out of town say that they were staying somewhere else. What I did hear is that some people, including me, tried to check into the wrong hotel as there are two Courtyard by Marriott locations in Richardson. Instead of looking at my confirmation I relied on Google Maps to tell me where to go. Google Maps #fail.

The Could be Better

The Raffle

The prizes that were give away were fantastic. It was no secret that I wanted the iPad that was being raffled off, I wanted it bad. Execution of raffles can be problematic. The prizes displayed/discussed, the number drawn, and the recipient then had to come down to the same person that called the number out to claim their prize all while the audience clapped after every drawing. This causes prize distribution to be lengthy, hence the creation of the t-shirt canon.

I am not suggesting I have ultimate raffle solution but some great ideas were thrown around at the after party that may have merit for further evaluation.

The After Party

The location for the after party was at the same location as the speaker’s dinner the night before. The room was partitioned off from the rest of the building so it was essentially a private room just for us. There was really no advertising of the after party until the raffle at the end of the day when everyone was trying to file out of the room.

The after party can be the most valuable of the entire event. The after party is where attendees can network and talk with the speakers and volunteers, many times in a one on one conversation. Advertisement of the after party should have been throughout the course of the event.

Overall

I would say that this event was a huge success. The issues mentioned above are really secondary to the main goal of providing a day of SQL training. The team from the North Texas SQL Server Users Group really did a fantastic job putting on the event and I am looking forward to attending again when they have the next one.

I am working with Malik Al-Amin (Twitter) to put on a SQL Saturday in Houston. You can follow either of us on Twitter for more details or you can follow the Twitter account that I have set up for the event SQLSatHou.

Find a SQL Saturday event near you and get involved with your local user groups.

During a Twitter conversation today Malik Alamin (Twitter) asked about the Autogrow settings on his databases. The conversation naturally progressed to the question of how you change the default behavior and set all newly created databases to have user defined settings. Brent Ozar (Blog | Twitter) was able to point us in the right direction to find a solution.  Modifying the model database.

About the model Database

When a new database is created either by using the CREATE DATABASE statement, that mean via SSMS as well, the model database is used as a template. This means that change made to the model database settings, including tables, permissions, stored procedures, and functions will all transfer over to the newly created database.

According to the TechNet article about the model database some file sizes may look different depending on what version of SQL Server is installed.  Also, there are some things that cannot be modified in the model database. Items like CHANGE_TRACKING, ENCRYPTION, and TRUSTWORTY cannot be modified. A full list of restrictions can be found in the TechNet Article.

Example

I need to set the auto grow on my data file to Autogrow by 10% instead of 1 MB as was the default on my instance. I can run the ALTER DATABASE command or use SSMS against the model database

USE [master] GO ALTER DATABASE [model] MODIFY FILE ( NAME = N’modeldev’, FILEGROWTH = 10%) GO USE [master] GO ALTER DATABASE [model] MODIFY FILE ( NAME = N’modeldev’, FILEGROWTH = 10%) GO

Making these changes will alter all newly created databases to have the same settings.

Note: Jorge Segarra (Blog | Twitter) pointed out that this does NOT work in a SharePoint environment.  SharePoint does not use the model database when it creates new site databases.

This is a reminder that we will have Kevin Kline (Blog | Twitter) with us discussing Top 10 Mistakes on SQL Server. I would encourage everyone in the area to come out if they can. Detailed meeting information below:

When: Tuesday, April 13, 2010 – 11:30am-1:00pm Where: Microsoft Houston Office 2000 W. Sam Houston Pkwy. S. #350 Houston, Texas 77042-3615 Pizza and drinks provided by Idera.

LiveMeeting Link Conference Call for audio – 1-888-320-3585 (passcode 76027128)

Topic: Top 10 Mistakes on SQL Server Speaker: Kevin Kline, Technical Strategy Manager, Quest; Immediate Past President of PASS (blog | twitter)

If you are planning to attend in person, please RSVP to (Nancy.Wilson @ sqlpass.org) by Monday, April 12.

When: Tuesday, March 9, 2010 – 11:30am-1:00pm
Where: Microsoft Houston Office
2000 W. Sam Houston Pkwy. S. #350
Houston, TX 77042
Pizza and drinks provided by Idera
Live Meeting
Conference Call for audio: 888.320.3585 (Passcode 76027128)
Topic: Accelerating Database Performance Using Solid State Storage
Speaker: Sumeet Bansal, Principal Solutions Architect at Fusion-io

A few months ago I reviewed my training plan for 2009 and realized I needed to update my certifications and take the SQL Server 2008 – Implementation and Maintenance Exam 70-432. While I felt I had enough experience and knowledge to take the exam I wanted to ensure I would be comfortable with all of the areas covered by the exam. I turned to my training plan on Microsoft’s Learning site and purchased the recommended book by Mike Hotek (Twitter)

Price

The recommended price on the cover is $69.99. At the time of publishing this article the book could be found for around $45.00 at many bookstores including amazon.com. The book comes with a 180 day trial copy of SQL Server 2008 Enterprise, ebook, practice test, and example scripts and databases.

The Good

The book was set up in a very logical progression of technical building blocks. It started out with the very basics of proper setup and configuration. The author then progressed through some database basics and how they related specifically to SQL Server 2008. More advanced topics such as Policy Based Management, Automation, Monitoring and Performance Tuning were then covered. Finally ending with a very in depth look at High Availability solutions offered by SQL Server 2008.

The Bad

Some topics, partitioning in particular, covered basic information but could have been emphasized better as they had a very large presence on the test. That being said, Mike does cover partitioning in an entire chapter of the book but it seemed that this material was more prominent on the test that 1 chapter worth of material.

Overall

I would rate the book at 4.5 out of 5 stars. What made this book really shine was the inclusion of the practice test. After completion of a practice test, users are presented with a reading list based on the areas of the test where the user needs to straighten their skills. This reading list includes material not only in the book but aggregates information from across the web.

I strongly recommend that people interested in taking Exam 70-432 purchase this book.

A few months ago I reviewed my training plan for 2009 and realized I needed to update my certifications and take the SQL Server 2008 – Implementation and Maintenance Exam 70-432. While I felt I had enough experience and knowledge to take the exam I wanted to ensure I would be comfortable with all of the areas covered by the exam. I turned to my training plan on Microsoft’s Learning site and purchased the recommended book by Mike Hotek (Twitter)

Price

The recommended price on the cover is $69.99. At the time of publishing this article the book could be found for around $45.00 at many bookstores including amazon.com. The book comes with a 180 day trial copy of SQL Server 2008 Enterprise, ebook, practice test, and example scripts and databases.

The Good

The book was set up in a very logical progression of technical building blocks. It started out with the very basics of proper setup and configuration. The author then progressed through some database basics and how they related specifically to SQL Server 2008. More advanced topics such as Policy Based Management, Automation, Monitoring and Performance Tuning were then covered. Finally ending with a very in depth look at High Availability solutions offered by SQL Server 2008.

The Bad

Some topics, partitioning in particular, covered basic information but could have been emphasized better as they had a very large presence on the test. That being said, Mike does cover partitioning in an entire chapter of the book but it seemed that this material was more prominent on the test that 1 chapter worth of material.

Overall

I would rate the book at 4.5 out of 5 stars. What made this book really shine was the inclusion of the practice test. After completion of a practice test, users are presented with a reading list based on the areas of the test where the user needs to straighten their skills. This reading list includes material not only in the book but aggregates information from across the web.

I strongly recommend that people interested in taking Exam 70-432 purchase this book.

There are some of us unfortunate DBAs out there that are forced to support MS Access connections to our database. I am one of those poor souls.

The Problem

I manage a Microsoft Dynamics NAV database with over 26,000 tables. When one of our users was trying to create a linked table in Access 2007 he was presented with a ODBC–call failed Timeout expired (#0) error.

Aside: I am always so appreciative of MS descriptive error messages.

I was unable to reproduce this error on my system. The main difference is that I am a member of the sysadmin fixed server role and he is not. I didn’t expect this to be my issue but ran a quick test. Low and behold when I set his user as a member of sysadmin he was able to list all of the table.

Having anyone other than a DBA as a member of the sysadmin fixed server role is unacceptable so I engaged Microsoft for some assistance.

The Environment

Physical Server HP DL380 Windows Server 2008 Enterprise SQL Server 2008 SP1 Enterprise 12 GB of RAM Lefthand Networks iSCSI SAN

The Solution

Disable the ODBC Timeout value

On the client computer, click Start -> Run -> type “regedit” (without double quotes) and press Enter, locate the registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\ODBC

In Access

Click to highlight QueryTimeout, and then on the Edit menu, click Modify. Change the value to 0 (zero).

Close and re-open Access

Microsoft’s Project is a very standard project management tool that most Project Managers are all to familiar with. While Project is a great application it was limited to local information. Microsoft’s Project Server extends Project’s capabilities and allows the sharing of resources and information across an Enterprise.

Project Server 2007 is built around the SharePoint platform and creates four databases upon installation that house project related data. The four databases are Archive, Draft, Published, and Reporting as can be seen in the image.

For any DBA who has tried to deal with SharePoint restores you know the nightmare that can become. Thats a topic for another post entirely. After one such incident and a help ticket with Microsoft it came to light that Project Server has a built in ‘backup’ capability. Microsoft’s words not mine. It is really more akin to version control than backups but that is besides the point.

In the Schedule Backup section of the Server Settings a Project Retention Policy (versions) setting can be found. By default this is set to zero. Microsoft’s best practices are that this be set to a minimum of 3.

Now on to why DBAs supporting Project Server need to know what is going on here. Project server operates by storing data in three of the four Project Server databases. When a project is created and saved to the server but not ‘Publish’ it stores the information in the, you guessed it, Draft database. Once the project has been published it moves to the Published database. I know, you are still with me. When the retention policy is set to 3 the Archive database is used to retain this information. Again not shocking. What needs to be planned for is the space that this will take. Your archive database will be, in this example, 3 times larger than your published database as well as the server activity that will be occurring when the ‘backup’ is taken.

Above is a shot of what gets backed up and when it does. This can be found in below the Project Retention Policy box on the Schedule Backup screen. It was also Best Practices to stagger these times so they didn’t all hit the Project Server queue at once.

While I have enjoyed using Project Server as a Project Manager can sometimes be a pain in the butt as a DBA. Do you have any Project Server stories you would like to share?

During a recent upgrade from SQL 2000 to SQL 2005 some poorly written code was exposed on a custom web application.  Users and developers started reporting that their applications were timing out.  One of the queries I posted in the Execution Plan Challenge.  Another exposed query involved a sub query.

    FROM
        SUM_METER_DAILY a,
        METER_MASTER b,
        MS_METER c
    WHERE
        b.METER_ID IN (SELECT DISTINCT d.METER_ID
            FROM LINE_SEGMENT_DETAIL d
            WHERE d.LINE_SEGMENT = @LINESEG)

While this was not the only issue with this query the execution plan showed that 88% of the query time was being spent on the SELECT DISTINCT portion of the query.  It was taking over 4 min to return a result set that contained only 55 rows.

By using the joins below we were able to reduce the time of the query from over 4 minutes to sub 1 second.

FROM dbo.MS_METER b
    INNER JOIN dbo.SUM_METER_DAILY a
        ON a.METER_ID = b.METER_ID
    INNER JOIN dbo.MS_METER_CFG c
        ON b.METER_ID = c.METER_ID
    INNER JOIN dbo.BAL_GROUP_ASSIGNMENTS e
        ON c.STATION_ID = e.OBJECT_ID
    INNER JOIN dbo.BAL_GROUP f
        ON e.PARENT_BAL_GROUP_ID = f.BAL_GROUP_ID


<span style="color: blue;">WHERE </span>b<span style="color: gray;">.</span>METER_IDNUM <span style="color: gray;">is not NULL
    AND </span>a<span style="color: gray;">.</span>IN_USE_FLAG <span style="color: gray;">= </span>1
    <span style="color: gray;">AND </span>a<span style="color: gray;">.</span>CONTRACT_DAY <span style="color: gray;">&lt;= </span>@CONTRACTDATE
    <span style="color: gray;">AND </span>a<span style="color: gray;">.</span>CONTRACT_DAY <span style="color: gray;">&gt;= </span><span style="color: magenta;">DATEADD</span><span style="color: gray;">(</span><span style="color: magenta;">DAY</span><span style="color: gray;">, -</span>4<span style="color: gray;">, </span>@CONTRACTDATE<span style="color: gray;">)
    AND </span>a<span style="color: gray;">.</span>IN_USE_FLAG <span style="color: gray;">= </span>1
    <span style="color: gray;">AND </span>f<span style="color: gray;">.</span>BALANCE_GROUP_NAME <span style="color: gray;">= </span>@LINESEG</pre>




Are Joins better than Sub-Queries.  The answer to that question is the same as the answer to most SQL questions.  'It depends’.  Joins and Sub-Queries need to be taken on a case by case basis.

In this case the major problem was the SELECT DISTINCT in the subquery.  Using a subquery forces SQL to evaluate each row in the outer query.  Using DISTINCT in the subquery means that the subquery must do a complete scan of the table, sort the values and then return TRUE if any rows are returned.

If you have examples of subqueries that are faster than a join to return the same data set we would love to share them with everyone.

References:

Using Subqueries, http://docs.hp.com/en/36216-90103/ch03s02.html