Shaun J Stuart

It's Two For One day here at shaunjstuart.com! That's right! Today only, you get TWO tips for the price of one!

One of the things I routinely do when setting up a new SQL Server or adding one to the flock that I already manage is to set up a job to maintain the job history maintain in the msdb database. If this isn't done, the msdb database can grow very large, especially on servers that have been running for years and are taking frequent backups. the majority of the time, you don't need all that job execution history, so I use the sp_delete_backuphistory to keep the size under control.

I recently discovered another thing that can bloat msdb - sendmail logs. With SQL 2005, it became much easier to send email from SQL Server. Database mail is a handy little utility for sending email and I use it to send all sorts of alerts about the state of my servers. In my environment, the developers have also used it to send out email from SSIS packages. We have hundreds of these that run on schedules from weekly to daily to hourly. I think it's safe to say my SQL Servers send out more email than the combined top 25% of our users.

When Database Mail sends a message, it stores a copy of the message in a table in the msdb database, as well as any attachments the email might have. It will also create a log entry in msdb regarding the sending of the message. If your server sends out a large number of emails, you may find the size of you msdb database becoming quite large. Microsoft has provided two stored procedures to help maintain these tables: sysmail_delete_mailitems_sp and sysmail_delete_log_sp. The commands have a couple of different parameters, but the most common way to use them is to supply a date for which all emails or log records prior will be deleted. Check with your business to see what your retention requirements are and then set up a job to perform this maintenance periodically. If you haven't been doing this, you can realize some significant space savings. On one of my more active servers, I freed up 250 MB of space from msdb. That translates to faster backups, restores, DBCCs, etc.

It was while I was setting this maintenance task up across my servers when I ran into a little problem that led me to my second tip. I created this job originally on a SQL 2008 R2 server and then used SSMS to script it out. I ran the script on other 2008 R2 servers without problem, but when I ran it on a SQL 2005 server, it failed with an error: @schedule_uid is not a parameter for procedure sp_add_jobschedule.

A quick visit to Google showed me the problem and the solution. SSMS can be configured to create scripts using different versions of T-SQL. I was using SSMS that came with SQL 2008 R2, so that was the T-SQL version it defaulted to. But if you go to Tools --> Options --> SQL Server Object Explorer --> Scripting, you can select a different version:

When I switched to script for SQL Server 2005, I got a new script that ran without errors on both my 2005 and 2008 R2 servers.

So what was the difference that was causing me problems? It had to do with the scripting of the job schedule. Here is the script in 2005 T-SQL:

-- 2005

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule
		@job_id=@jobId,
		@name=N'Sa @ 5 AM',
		@enabled=1,
		@freq_type=8,
		@freq_interval=64,
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relative_interval=0,
		@freq_recurrence_factor=1,
		@active_start_date=20120430,
		@active_end_date=99991231,
		@active_start_time=50000,
		@active_end_time=235959

And here is it in 2008 R2 T-SQL:

-- 2008

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule
		@job_id=@jobId,
		@name=N'Sa 5 AM',
		@enabled=1,
		@freq_type=8,
		@freq_interval=64,
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relative_interval=0,
		@freq_recurrence_factor=1,
		@active_start_date=20120430,
		@active_end_date=99991231,
		@active_start_time=50000,
		@active_end_time=235959,
		@schedule_uid=N'0623b9e9-fd03-4dcb-866d-cb156c623261'

Note the last line - the @schedule_uid parameter is not supported in SQL 2005. This is an output parameter in 2008, 2008 R2, and 2012. It doesn't appear to be well documented in MSDN. In fact, for the MSDN entries, it's not even listed in the Syntax section at the start of the entry, but you only find it as the last entry in the Arguments section. (There are a couple of Connect items filed regarding this, but Microsoft seems to have closed them all as "By Design." Even granting them the design part, I think this is a pretty poor design. At the very least, @schedule_id is also an output parameter and that is not scripted at all in any version of T-SQL. So why is just @schedule_uid scripted?)

SQL Saturday Phoenix took place last weekend. I went last year and I really enjoyed it. I planned on going this year as well, especially since it takes place incredibly close to my house, but those plans were destroyed by a wrecking ball.

Bruce Springsteen and the E Street Band were playing in Los Angeles on the Friday before SQL Saturday. And since the tour wasn't stopping in Arizona, that meant one thing: road trip! Sorry SQL, but I can't pass up a chance to see Bruce. Here's an example of why: Tom Morello from Rage Against The Machine joined Bruce for several songs and, towards the end of this one, played one of the hottest guitar solos I've ever heard.

If anyone is interested in the time I got to meet and have dinner with a couple of the members of the E Street Band, check this out.

The big news last week was the announcement from Microsoft that they are changing their certifications.  In a nutshell, the MCTS and MCITP certifications are ending with SQL Server 2008. Going forward, the new certifications will be called Microsoft Certified Solutions Associate (MCSA) and Microsoft Certified Solutions Expert (MCSE). (I think the "MCSE" terminology is a bit unfortunate given that this acronym used to stand for Microsoft Certified Systems Engineer several years ago and will likely cause confusion and / or unrealistic expectations of for people referring to the new certification.)

There are two big changes to the new certifications. First, the SQL Developer and SQL Administrator tracks are being combined. In order to get the new certification you will need to have knowledge of both areas. Second, the certifications are no longer product version based and will require getting re-certified every 3 years.

I just watched Kendra Little's webinar about this changes on BrentOzar.com. I recommend people view that to get more information about the changes.

What are my thoughts on the certs? I think it's a mixed bag. I like the periodic re-certification requirement. I think that is a good method to ensure people who have the certification remain up to date on the latest technology. After all, doctors and Certified Public Accountants have a similar requirements to maintain their licenses. On the other hand, I have a strong suspicion that the re-certification exams are going to focus on the latest version of SQL Server that is out at the time. This puts people who work for companies that do not like to be on the "bleeding edge" at a disadvantage.

I also like the combination of the Developer and DBA tracks. I may be partial to this because I started as a SQL Developer and moved into the Database Administrator role, so I already have a background in both, but I think this is a good change. I think it may be harder on existing developers than DBAs, but over time, I think only good can come from this. Hopefully, it will force developers to perhaps write code with more of a mind towards administration and performance issues their code might generate.

What don't I like? First, I don't like that they changed the certification program yet again. I don't think the MTCS and MCITP certifications have been completely embraced by the business community. I still see ads for DBA positions that list a MCDBA certification as a requirement and that certification was retired with SQL 2000. Now hiring managers will have another set of initials to remember and look for. Combined with the previously mentioned confusion over the MCSE acronym, I don't see this to be a good thing.

(Full disclosure: I am also a bit peeved because I just received my MCITP: Database Administrator 2008 certification two days ago and now everything is changing.)

My shiny new badge

But my biggest complaint is the number of exams the new certs require. I was able to get my MCTS: SQL Server 2008 cert by taking one exam (70-432) and my MCITP:DBA 2008 cert by taking one exam (70-450). That was starting from scratch, no upgrade exams from earlier certs. Look at the new cert requirements:

MCSA: 3 exams (70-461, 70-462, and 70-463)

MCSE: MCSA plus 2 exams (70-464 and 70-465)

The upgrade path from MCTS to MCSA is two exams and the upgrade path from MCITP to MCSE is three exams.

That's a lot of exams, especially when they cost $150 each. (I'm assuming that will be the price. That's what the current MCTS and MCITP exam prices are). If your company will reimburse you for exams, it's not too bad, but if you are paying for these yourself, that's a hefty investment. And don't forget the 3 year re-certification requirement. No word yet on how many exams that will take.

During her webinar (at the 8:35 mark), Kendra said the new certs require only one more exam than the current certifications. This is somewhat misleading and smells a bit like spin to me. Her math seems to be based on comparing the number of tests to get the new MCSE cert with the number of tests to get both the MCITP DBA and MCITP Developer certs. Arguably, this is one way to look at it because the new MCSE cert combines the DBA and Developer certs. But in the old program, if I didn't need both a DBA and Developer cert, I didn't need to take all the exams. Now, I have no choice. So from my viewpoint, I went from having to take 2 exams for a cert that was 100% relevant to my job role to 5 exams for one that is maybe only 50%-75% relevant. That's an increase of 3 exams. And the upgrade path from MCITP doesn't really cut that down significantly.

(I am interested in how many people currently have an MCITP in both Administration and Development. Please leave a comment and let me know.)

As I said, I think these changes are a mixed bag. I will probably end up not getting a new certification any time soon. For one, my company has no plans to move to SQL 2012 or the cloud. For another, I have little faith that Microsoft won't decide to revamp the whole certification program again in another 5 years. I will definitely be taking the wait and see approach.

I got a request from my manager to update a document we have here called our Service Catalog. The document is published by the IT department and lists all the major software programs we use, the business units that use them, whether or not they are 24x7 systems, and some other stats about our department. My job was to update the database section.

If you can guess how many objects are in the database, you win the database!

One of the items was the number of SQL Servers we have and their versions. This was relatively easy. The next piece of information I needed to update was the total number of databases we have. A bit trickier, but since I have my Drive Space Monitoring procedure in place, and that breaks out sizes by database, it was a simple matter to write a query to count the total number of databases we have.

The last piece of information I needed to provide was a count of "database objects." Huh? I have no idea what this meant, and neither did my manager. Apparently it was put in the document by a DBA who has long since left. The size of the number didn't really give me any clues either. We had about 600 database and the database object number was just over 7,000.

To me, a database object refers to something very specific - something that is stored in a database (excluding user data). So that would include all tables, views, stored procedures, triggers, foreign keys, constraints, logins, etc. If that was truly what this number represented, a value of 7,000 for 600 databases seems very low to me. Furthermore, I'm not even sure why this number would be in the document, unless it was to make it sound like the DBA had a huge domain to watch over.

Well, whatever the case, I decided to go ahead and update the number, although I will be adding a note to the document indicating the number represents the total number of user tables, views, and stored procedures in our environment.

The next step was determining the easiest way to get that data. I do not have a Central Configuration Server set up, which probably would have made this job a bit easier. So instead, I decided to use the multi-server query capabilities of SSMS. A slight problem was that my environment has a mix of SQL 2000, 2005, and 2008 R2 servers, so the basic system tables I would need to query would be slightly different. No problem. I've written before about how I like to write my scripts to be version agnostic. Well, they aren't truly agnostic in that they don't care about which version they are running on, but they do detect the version and then use the appropriate tables. By writing scripts this way, I can execute the same script in SSMS against multiple servers at once and not get errors.

Below is my code to count all user tables, views, and stored procedures in all user databases on a server. Note that is skips system databases and offline databases. If you want to count things besides tables, views, and stored procedures, simply add them to the CASE and WHERE statements in the definition of the @SQL variable. This script will work on 2000, 2005, 2008, and 2008 R2 servers. It will probably even work on SQL 7.0 servers, but you'll need to change the IF statement that looks at the ProductVersion property.

/*
Script to count all user tables, views, and stored procedures in
	all user databases on a server.

shaunjstuart.com

Verion 1.0

*/

DECLARE @DBName AS VARCHAR(100)
DECLARE @TotalUserTables INT
DECLARE @TotalStoredProcedures INT
DECLARE @TotalViews INT

CREATE TABLE #tblDBResults
        (
         DatabaseName VARCHAR(100)
        ,Item VARCHAR(50)
        ,Total_count INT
        )

DECLARE @SQL VARCHAR(1000)
	/* basic select statement */
DECLARE @SQLWithDB VARCHAR(1000)
	/* select statement with USE db added */

SET @TotalUserTables = 0
SET @TotalStoredProcedures = 0
SET @TotalViews = 0

/* Use appropriate tables based on SQL Server version */
/* 8 = SQL 2000, otherwise assume 2005+ */

IF (SELECT  LEFT(CAST(SERVERPROPERTY('ProductVersion') AS CHAR(1)), 1)
   ) = 8
   BEGIN

-- Configure basic select statement (without 'USE db' part) for SQL 2000 servers

         SET @SQL = 'SELECT	DB_NAME(),
							[type]
						   ,total_count = COUNT(*)
					FROM    (SELECT type = CASE WHEN xtype = ''U'' THEN ''User tables''
												WHEN xtype = ''V'' THEN ''Views''
												WHEN xtype = ''P'' THEN ''Stored procs''
										   END
							 FROM   sysobjects
							 WHERE  xtype IN (''P'', ''U'', ''V'')
							) s
					GROUP BY type;'

         DECLARE DatabasesCursor CURSOR
         FOR
                 SELECT name
                 FROM   sysdatabases
                 WHERE  dbid > 4
                        AND (512 & status) <> 512
					/* exclude system and offline databases */
   END
ELSE
   BEGIN

-- Configure basic select statement (without 'USE db' part) for SQL 2005+ servers

         SET @SQL = 'SELECT	DB_NAME(),
					[type]
                   ,total_count = COUNT(*)
            FROM    (SELECT type = CASE WHEN type = ''U'' THEN ''User tables''
                                        WHEN type = ''V'' THEN ''Views''
                                        WHEN type = ''P'' THEN ''Stored procs''
                                   END
                     FROM   sys.objects
                     WHERE  type IN (''P'', ''U'', ''V'')
                    ) s
            GROUP BY type;'

         DECLARE DatabasesCursor CURSOR
         FOR
                 SELECT name
                 FROM   sys.databases
                 WHERE  Database_id > 4
                        AND sys.databases.state = 0
				/* exclude system and offline databases */
   END

OPEN DatabasesCursor
FETCH NEXT FROM DatabasesCursor INTO @DBName

WHILE @@fetch_status = 0
      BEGIN

            SET @SQLWithDB = 'USE [' + @DBName + '];' + CHAR(10) + CHAR(13)
                + @SQL

            INSERT  #tblDBResults
                    EXEC (@SQLWithDB)

            FETCH NEXT FROM DatabasesCursor INTO @DBName

      END

CLOSE DatabasesCursor
DEALLOCATE DatabasesCursor

SELECT  Item
       ,SUM(Total_count) AS TotalItems
FROM    #tblDBResults
GROUP BY [#tblDBResults].Item
ORDER BY [#tblDBResults].Item

DROP TABLE #tblDBResults

Unfortunately, if you are running this across multiple servers using the SSMS trick I mentioned earlier, there is one additional step you need to do. SSMS, when performing a query against multiple servers, will add a column to the result set called Server Name. Your result set will include a set of numbers for each server. There is no way to have SSMS aggregate these into one set of totals. You'll need to copy and paste the results into Excel and sum up the numbers of tables, views, and stored procedures across all servers.

By the way, counted this way, my count of database objects went from just over 7,000 to over 550,000, even while the number of databases dropped by 150.  Restricting the count to only user tables, the number was still over 250,000. Clearly the old value was looking at something else. But since no one knows what it was, I'm going to redefine the term and do things my way.

A couple years ago, I was interviewing for a new job and one of the interviewers I met with asked me how I would go about tuning a query to improve performance. One of the options I mentioned was to run the query through the Database Engine Tuning Advisor, although, I added, its results should be taken with a grain of salt and not blindly implemented. This seemed to take the interviewer by surprise and he asked me to elaborate. I did so, and I will do so again here in more detail.

The Database Engine Tuning Advisor is a great tool that Microsoft started including with SQL Server 2005. It can

Who can resist Deta's cute face? A cold-hearted DBA, that's who!

take a query and look at the tables used in the query and recommend steps to improve performance. It can recommend adding clustered and non-clustered indexes, table partitions, statistics, and indexed views, to name a few. It will also report the performance improvement it thinks its changes will achieve. It's a great tool to have in your DBA toolbox.

However, as with most cases of automated analysis, a human should carefully review the recommendations and not blindly implement them. Why? There are a couple of reasons.

First, the DETA works in a vacuum. All it knows about your server is the query you gave it and the current database structure - indexes, statistics, etc. It has no idea of the server workload. Perhaps the query you are optimizing is one used once a month for reporting on an OLTP system that is handling thousands of updates and inserts a minute. Any added indexes that DETA recommends will slow down access to the table because now SQL has to update additional indexes for each DML operation. This could adversely affect performance. (Note: you can give DETA a SQL Profiler trace file as an input and it will consider that entire workload as it evaluates options, but in this case I am talking about optimizing a single query. You cannot do this and provide a workload file at the same time, unless the query is part of that workload.)

Second, I've seen DETA make some recommendations that are redundant. Well, technically, they are not redundant as the recommendations are not identical. But they are close enough so that only one of the two would be needed. For example, yesterday, I was using DETA and it recommended two indexes on a table. One index was just on Column A. The second recommended index was on Column A with Column B as an included column in the index. Technically, those aren't redundant, but in practice, the second index will provide all the functionality of the first.

Lastly, DETA can only provide recommendations that fit the parameters it has been given. Here is a good example. I've got a table in a vendor database that contains approximately 450 million rows. There is no clustered index. (I know, I know. Ridiculous.) The table takes 45 GB of space. There are three non-clustered indexes on that table. As a test, I had DETA tune a simple query that did a SELECT * FROM Table WHERE ColumnF = <some value>. ColumnF was not included in any of the existing indexes. Because this table is so big, I do not want DETA to recommend creating a clustered index. Creating that would require reordering all the data on the disk and would likely take a very long time. So I checked the option to recommend nonclustered indexes only.

What was the result? DETA recommended a nonclustered index on ColumnF with all the other fields in the table listed in the INCLUDE clause of the index. (Basically, a clustered index in all aspects except name.) This would result in a 99% performance improvement! Sounds great, right? But DETA also reports this index will require another 45 GB of disk space.In effect, DETA just told me to double the size of my table. Plus there is the before mentioned impact of DML commands having to update an additional index.

Clearly, it would be unwise to blindly accept this recommendation. A better option would be to leave off the included columns. SQL will need to do a lookup to get the other fields, but when compared to the additional storage requirements and index upkeep during DML operations, this is a small price to pay.

These examples show why it is important for an experienced DBA to carefully review the recommendations of the Database Engine Tuning Advisor and not just blindly implement them. As with any advice, the DBA is free to do with it as he or she pleases, including ignore it or just take selected bits.

That's my advice. Do with it as you please. :-)