SQL Server Database Management

Today's editorial on SQLServerCentral was about what your employer should provide for training and professional development and that got me thinking about what I have been doing. Over the the past year I have really made an effort to catch up with SQL Server 2005 and start managing my professional development. I relocated from northern New Hampshire to central Florida, right outside of Orlando and that has given me the opportunity to take advantage of local user groups (OPASS and ONETUG), Code Camps, SQLSaturday, and other technical events that were not easily available to me in New Hampshire. Through these resources I have probably learned as much about performance and administration as I had learned in the previous 5 years. I have picked up information about backups, profiling, performance tuning and monitoring, security, and reliability best practices. All without paying for a class or book. I've also taken the time to write a couple of articles for SQLServerCentral and I am preparing to speak at the next OPASS meeting. These are great ways to work on your professional development as one of the best ways to learn something is to have to teach it to someone else.

I also take advantage of online resources like SQLServerCentral, SSWUG, SQLServerPerformance.com, and many blogs. Most MVP's and Microsoft development teams have blogs so there many good ones, along with some you may want to out. So while I believe your employer should provide some time and money, I also think you need to take advantage of the same resources I have. Granted I take time at work to utilize many of these on-line resources, but since I'm not asking my employer to spend on me, I feel justified.

What do you do to "keep up"?

Page Restores

This is just a short post about something I just learned. You can restore a single page of your database. I learned about this in this post, Search Engine Q&A #22: Can all page types be single-page restored?, by Paul Randall on his In Recovery blog. The basic syntax is:

Restore Database Test Page = '2:10' From Disk = 'C:\Backups\Test.bak'

I'm not sure what the practical use of this is, as doing a full database restore will obviously restore any pages that might be corrupted. You need to search a little to find some good documentation in BOL, so here is a link to the documentation, Performing Page Restores.

If you have ever used page restore let me know why and how it worked for you.

Profiler Bug?

I was working with SQL Server 2005 Profiler to prepare for a talk I will be giving at the OPASS September 9th meeting (come on out and see how I do) and I saved my definition as a template. I then re-opened the template and received and error, "Invalid Event ID". I could not figure out what had happened as 5 minutes prior I had this same Trace running, and all I had done was stop the trace and save as a template. Being intrigued by this I tried to determine what the invalid event was, but how could I do that? I used Profiler. I started a new Trace filtering out the SPID for that trace and then removing the default filter that excludes Profiler. I then opened the custom template that was failing. In my new trace I saw all the trace procedures firing and the last sp_trace_setevent was for an event if of 64000 something (I did not write down the actual event id). I then queried the sys.trace_events table to see that the highest event_id is 202. Where do the 64 thousand-something number come from? Any ideas? Is it a bug, or some kind of weird combination of events? I wish I could re-create it, but I have not been able to, any helpers out there?

Disk Alignment?

I attended the Orlando PASS meeting last night where we had 2 speakers. Dolores Tofel, a local and regular attender, who did a short presentation on using Microsoft Access as a UI for SQL Server data. Even though I have developed a distaste for Access, she did a good job showing how it can be used as a RAD tool, particularly for reporting. Andy Warren, president of OPASS, is trying to give local speakers a boost by giving them an opportunity to do a mini-presentation and not be the main draw for the meeting.

The main speaker for the evening was Amy Styers from EMC. She does performance consulting for EMC customers running Microsoft products (SQL Server, Exchange, Sharepoint). Her topic was the top ten ways to improve your SQL Server performance, and I have to admit that most of the list were practices I was already familar with. But, her #10 was new to me and I think to majority of the ~20 folks in attendance. It was to get your disks aligned. She showed us that because the Master Boot Record is 63 k and the tracks are 64k. Basically this means that you will regularly have to do 2 IOs per read or write because you have to cross tracks. Apparently you can see up to a 20% IO performance gain by aligning your partitions. Of course to do this on a production system is a BIG deal as you have to make sure you have good backups and totally wipe your disks. Then you can use the DISKPAR or DISKPART(Windows Server 2003 sp1) to align the partitions and then reformat. Apparently Windows Server 2008 no longer has this issue. Linchi Shea has done some testing of this and posted the results on his blog, here. There is another article here, that deals with the same issue for Exchange with reference to an HP whitepaper. I found this subject very interesting as I had never heard this before and neither had the server admin at my workplace. We don't have any overburdened servers at this time so we won't be making the change, but it is still good to know about in case I meet someone who may be able to do something with it.

This is one reason I go to the user groups meetings even when the featured presentation may not be something I think I'll be using. Last meeting was about SQL Server Mobile and I am using it now for a personal project when I never thought I would before the meeting. I find that I learn something new every meeting and I have a good time meeting other SQL Server specialists.

The next OPASS meeting is scheduled for September 9th, 6:00pm at End to End Training, just outside Orlando. If you are going to be in the area come join us. The featured speaker is going to be great. How do I know that? It's ME! I'll be presenting on SQL Profiler Basics and I think you'll get something from I it. I know I have in preparing for it. If you do come, plan on joining us for the after event as well for some informal geek talk.

Aggregation based on Date

Imagine you have Sales database, we'll use AdventureWorks, and you need to return the number of sales by product in the last 30, 90, and 180 days. How would you do this? In the past I might have used temp tables, table variables, or a union in order to get the the numbers for each date range. This would not be, in my opinion, the optimal way to retrieve this data. You could just return all the data with order dates and use Excel, SSRS, or another reporting tool to pivot the data, but once again this is probably not the optimal way to return the data. So, what do I think is the best way to do this? I do this in a single select filtering on the largest date range using CASE to return the data for the smaller date ranges. Here is the code, based on AdventureWorks (I use 7/1/2004 because that is the most recent order date in my copy of AdventureWorks):

SELECT
D.ProductId,
COUNT(H.SalesOrderID) AS sales_for_180,
SUM(D.ORderQty) AS no_products_sold_180,
SUM(CASE
WHEN H.OrderDate >= DATEADD(DAY, -90, '7/31/2004' ) THEN 1
ELSE 0
END) AS sales_for_90,
SUM(CASE
WHEN H.OrderDate >= DATEADD(DAY, -90, '7/31/2004' ) THEN D.OrderQty
ELSE 0
END) AS no_products_sold_for_90,
SUM(CASE
WHEN H.OrderDate >= DATEADD(DAY, -30, '7/31/2004' ) THEN 1
ELSE 0
END) AS sales_for_30,
SUM(CASE
WHEN H.OrderDate >= DATEADD(DAY, -30, '7/31/2004' ) THEN D.OrderQty
ELSE 0
END) AS no_products_sold_for_30
FROM
Sales.SalesOrderHeader H JOIN
Sales.SalesOrderDetail D ON
H.SalesOrderID = D.SalesOrderID
WHERE
H.OrderDate < '7/31/2004' AND
H.OrderDate >= DATEADD(DAY, -180, '7/31/2004')
GROUP BY
D.ProductId

This will return the data you asked for in a single pass and will do an index seek if you have an index on OrderDate. All in all an efficient way to return the desired data. You may have learned this a long time ago, but it is something I recently picked up when answering a question on SQLServerCentral, so I know not everyone knows this. This technique works whenever you need aggregates based on some sort of partition. It could be a breakdown of products within a product category as well.

Ghost of SQL Server 2005 Database Mail Reply To - Fix

If you have been following my posts at all, you know I have had some issues with Database Mail (1, 2, 3, 4). Well the first one has been fixed, I have not tested the fix yet, by Microsoft in Cumulative Update Package 8 for Service Pack 2. The article for that is here, http://support.microsoft.com/kb/953841/en-us. Microsoft never updated my Connect post, where they said it would not be fixed unless a QFE was requested, but I'm not complaining.

Now I need to try it out on my personal SQL Server.

SSIS Webinars, tablediff, and Free Zoom Tool

I had the opportunity to attend 3 of 5 free webinars on SSIS presented by Brian Knight of Pragmatic Works. The 2 I chose not to participate in were introductory in nature, and I have some experience with SSIS. I attended Administering and Deploying SSIS Packages, Performance Tuning in SSIS, and SSIS for the DBA. I enjoyed each of the webinars and picked up some good tips. I enjoy taking any opportunities, particularly free ones, to expand my toolkit and knowledge base. I appreciate Brain and co-workers taking the time to present the webinars as I know that there had to be a lot of time and effort put into doing them. I hope it pays off for them in their business as well. I believe that the goodwill it will. Check out the Pragmatic Works web site as they offer some very good custom SSIS tasks for free! They also offer some other commercial product as well.

I actually used something I learned in one of the Webinars today, Webinar SSIS for the DBA,. I used an Execute SQL Task to populate an object variable with a list of schemas and tables and then used a For Each Loop Task to loop through the result set and use the Execute Process Task to run the tablediff utility Microsoft has included with SQL Server 2005 (I read about it in Benjamin Wright-Jones' blog earlier today). It is a command line utility that compares 1 set of tables (source and destination) and I wanted to compare all the tables in my database to my development database so I needed the loop. Tablediff compares the schemas and if they are different will return an error, "can not compare the table x to table y because the schemas are different". If the schemas are the same it will, depending on the parameters you supply, show any differences in the data and prepare a T-SQL scipt to synchronize the data. I actually was only interested in comparing the schemas and, not having read all the documentation, was hoping it would report those differences as well. Maybe in a later version of SQL Server a schema comparison tool will be included (sorry Red Gate). It was still a good exercise, and knowing about the tablediff utility will probably come in handy later.

One last thing, Brian used a nice free tool to zoom and markup during his presentation. It is called ZoomIt and is by Sysinternals (now part of Microsoft). I would recommend it to anyone who give presentations as it really helped us see what Brian was doing.