Are Your Database Statistics Fresh?

SQL maintains a vast amount of data – or statistics – about the content of each object in a database. The statistics can become stale if they have not been updated very often, or if a large number of changes have occurred within the database. As the statistics become less useful, the time for running queries can increase dramatically.

In Production systems, statistics should be updated during the usual maintenance window to ensure that the metadata is fresh.

To see how fresh the statistics are for one object, run:

DBCC SHOW_STATISTICS ( 'CCAuditSessionType' ,CCAuditSessionType_PK)

If you need to see the statistics for all databases, run this instead:

select STATS_DATE(o.object_id,s.stats_id) as StatsDate,o.name as TableName, s.name as StatsName, auto_created, user_created, no_recompute

from sys.stats s

join sys.objects o on s.object_id=o.object_id

where o.type='U'

To update statistics that are out of date, execute the command

exec sp_updatestats

on each database on the server that needs to have its statistics updated.

Use the following query to generate a script to update the statistics on all databases

declare @db varchar(30)

, @dbID int

, @sql varchar(max)

create table #t

(DbName varchar(30), databaseID int)

Insert #t (DbName, databaseID)

select [name], database_id

from sys.databases

where database_id > 4

Select @dbID = MIN(databaseID)

from #t

While @dbID is not NULL

BEGIN

   select @db=DbName

     from #t

    where databaseID=@dbID

   set @sql = 'Use [' + @db + ']' + CHAR(13) + 'go ' + CHAR(13)

   set @sql = @sql + 'exec sp_updatestats' + CHAR(13) + 'go '

   PRINT @sql

   Select @dbID=min(databaseID)

     from #t

    where databaseID>@dbID

END

drop table #t

Copy and paste the printed output from your result set into the query portion of a SQL Server Agent job and this will ensure that the statistics are updated for all databases on a regular schedule. NOTE: the query above excludes the system databases.

Adventures with Denali CTP3–Part 1

I usually only realize how slow downloads can be when I’m eager to begin working with the item being downloaded. The hour it took to download the AdventureWorks sample databases felt far longer than it actually was.
One thing that surprised me was that the downloads for the databases were only the MDF (data) file – the log file was not included. After fiddling unsuccessfully with attaching it using the UI in Management Studio (no, I didn’t think of deleting the log file name from the file list in the UI – I’d assumed it was required and didn’t realize that if you did not list a logfile that it automatically treated it as an ATTACH_REBUILD_LOG command), I finally decided that it would be sensible to actually read the instructions. Technet provided me a very simple query to attach the database
CREATE DATABASE AdventureWorks2008R2 ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Data.mdf') FOR ATTACH_REBUILD_LOG ;
Worked like a charm.
I modified the query and attached the AdventureWorksDWDenali database in a similar manner then ran a few quick SELECT queries on various tables to see what they contained. I was pleasantly surprised to see that the OLAP database’s dimDate table contained English, French and Spanish day and month names.
I then launched the BI Development Studio and opened the AdventureWorksDWMultidimensionalDenali project (provided in samples pages on Codeplex). I verified the connection information in the datasource and successfully deployed the cube.
If everyone knew how easy this was, I’d probably be out of a job.

Query to Pull Database File Information

This query will list the name, size and location of all files for all databases. This is handy for checking and documenting database server configuration to confirm whether the server follows our recommended best practices.

set nocount on

declare @sql varchar(max), @sql2 varchar(max), @name varchar(100)

if object_id('tempdb..#t') is not null drop table #t

create table #t (DbName varchar(30), LogicalName varchar(30), FileName varchar(100), sizeMB int, UsedSpaceMB int, growthMB int, is_percent_growth bit)

set @sql = ' substring(name,1,30) as LogicalName,substring(physical_name,1,75) as FileName,'

set @sql = @sql + 'size * 8192./1024/1024 as SizeMB,sum(a.total_pages * 8192./1024/1024 ) as UsedSpaceMB, '

set @sql = @sql + 'growth * 8192./1024/1024 as growthMB, is_percent_growth from '

declare c cursor for

select name from sys.databases where database_id > 4

open c

fetch next from c into @name

while @@fetch_status=0begin

set @sql2 = @sql + @name + '.sys.database_files df left join '

set @sql2 = @sql2 + @name + '.sys.allocation_units a on df.data_space_id=a.data_space_id left join '

set @sql2 = @sql2 + @name + '.sys.partitions p on p.partition_id = a.container_id '

set @sql2 = @sql2 + 'group by df.name, df.physical_name, growth, is_percent_growth, df.size'

begin try exec ('insert #t select ''' + @name + ''' as DbName, ' + @sql2 ) end try begin catch end catch

fetch next from c into @name

end

close c

deallocate c

select * from #t

I’m sure that there are other ways to pull this data, however, in some environments your permissions may restrict you from using any method other than this to pull the data.

SQL Recovery Mode Adjustment

Often when new Development boxes are handed over to my group, the databases are set to Recovery Mode = FULL because the setting match production recovery modes. Unfortunately, since the Dev boxes rarely have any backups running, eventually the transaction logs fill up the drive. When that happens, the databases can no longer accept new transactions and we are left with a (temporarily) non-functional box.


Run this script on a Dev box to set the recovery to SIMPLE for all databases to avoid the above scenario. It works on SQL 2000, 2005 and 2008 SQL servers.


NOTE: it is recommended that PRODUCTION servers use FULL recovery mode rather than SIMPLE.


use master

go


DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE DatabaseCursor CURSOR FOR


SELECT name FROM master.dbo.sysdatabases
where Name not in ('tempdb') -- cannot set recovery for Tempdb.
ORDER BY 1


OPEN DatabaseCursor


FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN


     set @cmd = 'ALTER DATABASE ' + @database +'
     SET RECOVERY SIMPLE'


     EXECUTE sp_executesql @statement=@cmd


     FETCH NEXT FROM DatabaseCursor INTO @Database


END


CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor


For your homework, you can substitute a WHILE loop for the cursor.  
If your environment is running mostly SQL 2008 (or higher), please check out SQLChicken's article on setting up Policy Based management to handle ensuring that the dev boxes are all set to Simple Recovery Mode.

Meme Monday: I Got 99 SQL Problems And the Disk Ain’t One

This month, Thomas Larock (Website| Twitter ) declared a meme Monday inspired by the Hugo song 99 Problems - aside from disk issues, name 9 problems you frequently see in your shop which are not related to disk issues.

1) Using default install settings for file growth
Despite numerous examples from live systems showing that those settings are not appropriate for our product's databases, we frequently see new customers with all their databases set to the default 10% growth setting, despite the statement in the "best practices" documentation that tells them otherwise.

2) Bloated SQL error logs
Many times when customers report having issues and we're called in to examine what's happening with their SQL server, we find that we can't open the SQL Error Logs because the customer's SQL server hasn't been restarted in a long time and the errorlog is so bloated that it's too big for the UI to open in a timely manner. The simple fix, of course, is to set up a SQL job that runs sp_cycle_errorlog periodically.

3) Not doing ANY index maintenance
Frequently, when I hear about SQL performance issues, I find that the customer has turned off the regular index maintenance jobs "because they take too long". Eventually, this results in painfully out of date statistics, severely fragmented indices and terrible performance.

4) Shrinking databases as "maintenance" to "free up disk space"
I try my best not to use profanity or to scream (loudly) when I see this enabled on customer servers. I just take a deep breath and forward the following links to the guilty party:
Paul Randal: Why You Should Not Shrink your Data Files
Brent Ozar: Stop Shrinking Your Database Files. Seriously. Now.

5) Developers "testing" code on production
Don't get me started....

6) Poor backup plans not even close to SLA requirements
High volume OLTP Production database, full recovery with log backup once a day at midnight and full backup once a day at 1AM - but their SLAs say they have to completely recover from failure within one hour. They claim that because the SQL server is clustered, that they don't have to back up the databases more often. Really? Please don't call me when things go south.

7) No disaster recovery plan
... And office in the middle of Tornado alley. Again, please don't call me to resurrect your SQL server when your data center gets destroyed by one of the 500+ tornadoes that went through town. You don't have a copy elsewhere and I can't create something from nothing.

8) Letting idiots have access to the Server room
Believe me: I can't make this one up - it actually DID happen.

A particular person on the night cleaning crew entered a server room to vacuum it. Because the vacuum's cord was too short to allow him to vacuum the far side of the server room, he unplugged something to free up an outlet so he could vacuum the far corner of the server room. The "something" he unplugged was the main power for the SQL server, which knocked out the customer's website until someone entered the server room in the morning and noticed that the server was unplugged.

9) Not having automated monitoring on servers
You'd think this was obvious, but I've been called too many times to count late at night to hear that someone's server is "down", only to find out the reason the SQL server crashed or the backups failed was because the disk was full. Automated disk monitoring systems have been around for over a decade, yet many of our customers don't have any automated monitoring and I doubt that their IT people check the servers every day since they seem so surprised to discover that their disk has filled up completely.

After just thinking about those 9 items, it's time for a stress pill.

SQLRally - Birds of a Feather

Earlier today, Jorge Segarra (twitter) sent via twitter a request for volunteers to head up Birds of a Feather tables at SQL Rally. I asked him if he had someone to head up a PASS WIT table.

He replied, "@LadyRuna if you're volunteering, you could host it :-D #sqlWIT #sqlRally"

So it looks like I'm hosting the WIT table at the Birds of a Feather event at SQL Rally. Please stop by and say hello if you're there. I've never run one of these before and I'll be making things up as I go along.

Hooray for Wonderful Husbands

I was talking to my boss yesterday about my professional development plan for this year, and the subject of training came up. He said we only had enough money in the budget to send one person to a conference this year, and since I’d attended SQL PASS Summit last year, they were going to send my coworker this time – meaning I wasn’t eligible to attend any offsite training at all. He suggested I find some books to read to fulfill my professional development plan.

Needless to say, I was not at all thrilled, and mentioned it to my darling husband.

He, too, was unimpressed. He knew how important and useful attending SQL PASS Summit had been for me.

Over dinner, he casually asked me, “Do you think you can take time off from work the second week of May?”

I checked my calendar – I was not scheduled to be on call, so I could take time off.

I replied, “Sure. Why?”

He held my hand and said, “There’s this PASS SQLRally thing in Orlando that week. We have enough vacation money saved up to send you.”

(Queue romantic music and sappy romantic scene….)

Ok, get your mind out of the gutter… The final result is: I’m going to PASS SQLRally and even get to attend one of the Pre-con classes. Hooray for wonderful, amazingly loving husbands! I certainly picked the BEST husband in the world. See you at PASS SQLRally!

Mater Immersion Event Competition (Entry)

As an amazingly wonderful contribution to the SQL community, Paul Randal (blog | twitter) has offered the opportunity for a member of the SQL community to win a free seat at the SQLskills.com 5-day Internals and Performance class in Dallas, February 21-25. To qualify, one has to state in a blog post why you want to come to a class taught by them and why you'd make the best use of the knowledge you'll get from being in the class. I've decided to throw down the gauntlet and accept that challenge.

What would deep SQL Server training provide me?

Although I've been working with SQL server for about 14 years, for the most part it's still a black box. I'm often required to quickly respond to performance and corruption problems related to critical SQL Server systems belonging to my company's customers. I often feel as if I’m staring down the barrel of Mon’s Meg (see picture at right) as I strive to think up what to try next to solve the issues. Normally I wind up following the "well, last time we did -this- and the issue went away..." 

For many types of issues involving SQL Server, using past experience to solve them without fully understanding exactly how SQL server functions can produce acceptable results - that is, the issue is cleared, but I cannot explain the failure, nor can I can't elaborate on why what I did worked or why the database failed the way it did.

I often feel as if I’m an archeologist on the Shetland Island unearthing another broch. I know how to handle the items to reduce or prevent damage to them, but may not necessarily have a full understanding of what they are or how they’re really supposed to be used.

Understanding how SQL server stores and retrieves data is crucial to efficiently finding solutions to complex issues within extremely aggressive SLA timeframes. This is even more important when users are wanting to take advantage of the new features of SQL 2008, because structures such as data compression, sparse columns and Filestream can drastically impact a server's ability to perform as the users wish it to.

Many times when I’ve needed quick assistance with solving database issues or explaining to yet another customer why one should not shrink databases daily as “maintenance,” I’ve referenced Paul’s blog to provide the explanations for me. I’m hoping that by attending this training, I will learn what I need to know so that I don’t feel so sheepish when defending the necessary fixes for SQL issues.

I would make use of the knowledge learned in Paul & Kimberly’s class almost every day at my job. It could also help me advance from just another regular SQL person to a senior level or even expert level SQL person.

Finally, I have a friend living about an hour outside Dallas, TX who has a mixed herd of Barbado sheep and fainting goats – proving that sheep and goats can live together happily (Paul knows I have 5 goats).

*ALL photos taken by me on our trip to Scotland in July 2000 (well, except the last one, which was taken in 2009 at my friend’s place in Texas).

Un-SQL Friday: My Tech Giants

It’s Friday, the end of another long work week, and just as we have the monthly T-SQL Tuesday in which all SQL people are encouraged to post something related to a particular topic, the female half of Midnight DBA, the dazzling newly-minted MVP Jen McCown (blog | twitter) has declared this Friday, “Un-SQL-Friday”. For this Un-SQL-Friday, she asked us:

“Read this blog, and then write whatever you want about Tech Giants. Be sure to mention in your blog that you’re writing for Un-SQL Friday, and link to this post. Oh, and have it up any time before the weekend (Saturday Jan 22) hits, mmkay?”

Well, that’s an interesting question. I suppose another way to put it is,

“If you were asked to fill the shoes of __<name of tech giant>_ , could you do it?”

I think the picture to the right answers the question quite succinctly as “Well, I can plug the top of his boot, but I’ll need a lot of other material to fill the rest.”  That is, I may not be at the same skill level on that topic as the one who wrote the book on it, but with enough effort and time I could get there. Much of the “greatness” comes from that person having researched and documented something that I haven’t yet encountered. The "SQL Gurus” are the ones who wrote the books I’m buying or actually worked on making SQL server what it is today.

I could rattle off a litany of names (as some of the other un-SQL participants did), or I could

SELECT TOP 10 GuruName FROM SQLServerGurus

and see what that returns to me. Or I could direct you to the listing on the right-hand side of my blog which includes links to many of the SQL blogs that I frequent. Either way, you’ll see that there is no shortage of knowledge available and I thank each and every one for all of the information that they’ve freely provided which has made my job much easier to do.  

t-sql-tuesday-resolutions



TSQL2sday Linkback



Happy TSQL Tuesday Everyone!

This week's TSQL2day is hosted by the lovely Jen McCown (blog | twitter ), the female half of the dynamic duo of SQL MVPs known as the MidnightDBAs. The theme is to post about our technical resolutions for 2011 – what we hope to accomplish and why.

It seems that a lot of us are making similar resolutions for 2011, so if you think you've seen this list before, you probably have. I'm not copying other people's lists, it's just that many of us in the SQL Server Community are seeking to accomplish similar things this year (birds of a feather and all that).

RESOLUTION: BLOG at least once a Month on SQL topics

I started my blog in July 2010, and have been rather lazy about updating it. In 2010, I posted 15 articles, of which only 1 was 100% about SQL. I did have 6 others I tagged with "SQL" or "SQL PASS", but they were not focused specifically on SQL. That's not a particularly good record, especially since the purpose of starting this blog was to write about SQL and NOT about sewing, random thoughts or my goats, even though those are excellent topics for blogging. The good news is that by posting this article, I can check off January's SQL post...a good start to any set of resolutions. Of course, I DO have at least 11 other SQL blog posts that I must create.

RESOLUTION: READ Books on SQL Server 2008
AND
POST reviews of the SQL Server 2008 Books on my BLOG

This is a dual resolution, thanks to Brent Ozar (blog | twitter), who very kindly sent me a copy of "Professional SQL Server 2008 Internals and Troubleshooting"  which he co-authored with 6 other people. As a condition for giving it to me, he asked that I write a review of it. (Brent: I'm reading it now - really!!). Reading books on SQL will certainly help me better understand the inner workings of SQL and hopefully advance my career. Blogging about what I read will not only ensure that I absorb the information I read about but also will fulfill my blogging resolution.

RESOLUTION: WRITE a PRESENTATION on a SQL Topic
AND
Volunteer to PRESENT on a SQL Topic

As a SQL professional who has been working with SQL server since the days of SQL 6.5, I really ought to volunteer to present at a SQL-related event - be it a SQL Saturday, SQL User Group meeting, 24 Hours of PASS, or PASS Summit itself. This resolution is a two part resolution since the second part won't happen unless I actually have something prepared to present. I've been hiding in the background whenever calls have gone out for volunteers to speak because I "have nothing to say" and "have no idea what I can present about." Anyone who has heard me talk about sewing and costuming at the Science Fiction and Anime conventions that I've been attending knows that I have plenty to say (well, plenty to say about sewing, costuming, and a few other hobby-related topics) and don't fear getting up in front of 50 or so people and talking their ears off.

RESOLUTION: Become a SQL MVP

This is likely the hardest one for me to accomplish - especially since it requires others to recommend me for the award. However, if I work to exceed the other goals listed above, I might actually have that within reach.
 
What do you think? What are YOUR resolutions for 2011?