Thursday, March 19, 2009
Anyone who has spent time in the computer business and has some grasp of hardware issues understands fragmentation. For those whose concept of hardware is limited to “it’s the part I can kick”, here is a quick and painless overview.
I like analogies so let’s think of a disk drive as a very large, tall building having many rooms and many floors. The disk head is you. Your task is to run around and “read” something from particular rooms (blocks). A simple thought exercise would suggest it is easier to read rooms “in order” on a floor rather than scattered around the building. You can “read” all the rooms on a hallway far faster than the same number of rooms on different floors or wings. Hence the difference between “sequential” and “random” I/O. Blocks or rooms, it all works the same. One trick used when benchmarking systems is to partition only part of each disk and assign that to a RAID set. This causes the heads to “short-stroke”, leading to faster seek times. This would be like only using the first ten floors of a large building so the elevator only has to move a short distance. Inefficient in space, but faster for throughput.
Fragmentation comes in two forms in SQL; internal and external. Internal fragmentation occurs when the various SQL data structures are not in order within a particular SQL data file. External fragmentation occurs when the SQL data file itself gets scattered in multiple sections over the physical disk. Internal fragmentation can be corrected by reindexing the database, something most of us have running automagically. External fragmentation is not so easy to fix. You have to defragment the disk with SQL Server offline so the data files sections can be moved around and strung back together. However, you can avoid External fragmentation very easily by following a few simple rules. Note that the issues for SQL Log files are a bit different but the same rules apply.
Rules to Avoid External Fragmentation:
1) Never Use Auto-Expand for data files. Note that I do not say disable auto-expand, just never actually use it. Pre-expand your databases in large increments to handle several months’ worth of growth at a time. Leave Auto-Growth as a “safety valve” to keep unexpected allocations from killing your server, but don’t rely on it to manage data file space allocations.
2) Never Shrink data files. Shrinking inevitable leads to later growth. Allocate once and leave it. Shrink also causes massive Internal fragmentation in a SQL database. The shrink algorithm moves data to new locations but does not attempt to keep data in contiguous segments. Auto-Shrink lets you do this on a regularly scheduled basis. How helpful.
3) Keep SQL disks dedicated to SQL Data only. Creating and optionally destroying many data files causes physical fragmentation on the disks. To go back to the building analogy, most disks allocate new from new. That is instead of reusing old rooms freed up from deleted files, the file system simply grabs the next unused room (or rooms) after the last used one and keep moving. This is nice until the first pass through the building is complete and stuff is all over the place.
Fragmentation brings us back to a fundamental dichotomy in SQL Server management. You can optimize for low space utilization or high performance but not both. Considering disk space is relatively cheap, I know which one I always go for.
Thursday, January 01, 2009
Looks like the Great Zune Massacre of 2008 was a day 366 issue. Again, someone forgot to throw out the code from the lowest ten percent of the Stack-Rank system. Sorry to sound harsh, but this is type of thing will flunk you out of Programming 101. I don't even want to get started on the QA failure, but it is even worse.
Semi-Kudos to the Zune Team. There was a notice on the support page shortly after the original post acknowledging the problem and indicating they were working on it. Microsoft also got word out through its MSNBC subsidiary as a news item, linked to the support page. I still say the warning should have been more prominent and on the main page, not the support page, but that is relatively minor.
My Zune is recharging and should be OK.
Wednesday, December 31, 2008
Yes, I own a 30 GB Zune. Yes, it crashed today. Yes, I am unhappy.
Having worked in the computer industry for many years now, I watched many companies deal with failed products. Such is inevitable in an industry that gives the biggest rewards to the first implementation that is “good enough”. More importantly, I have seen companies deal with failures in various ways. Some handled things well and some no longer exist or have lost market dominance due to poor reactions to failures. Here are a few observations that I am sure Microsoft will ignore, partly because you have to plan ahead for failure in order to handle it well.
This may sound strange, but data professionals who play in the High Availability space understand this at a near instinctual level. Every action we take has to have a failure plan as well as a success plan. It is by limiting the duration and impact of failures that we can build and operate systems with very high service levels.
An essential part of any failure plan is communication. Once the failure leaves the server room, you have to tell people what is going on. I have seen this done poorly (SQP 2005 SP2) and done extremely well (SQL 2008 CTP Leap day bug). The key is to have the communication plan in place before anything goes wrong. You won’t have time to decide who to tell, much less find their contact information. Go ahead and have a generic “Sorry, We Failed” web page with a place to write short notices and updates if that is appropriate. Even if your service level agreement states you have four hours to restore service, telling people when the clock started counting and when they can expect service restoration is still important. Don’t try and make things look better than they are. Truth, no matter how painful, is preferable to silence or meaningless verbiage intended to deflect blame.
Note that the Zune.net home page has no announcement. The only information about the problem is on the Zune user help forums and on social network sites like Facebook and Twitter.
Lesson: Don’t let your user community lead the announcement effort. Let them assist, but make sure you have official information for them to share. There will still be speculation, but it won’t dominate the discussion. As of this writing, the community has no official assurance that the Zune team is even aware that they have a problem.
For now, I will give them the benefit of the doubt and see how this plays out. Unfortunately, Microsoft Zune is off to a poor start handling this problem.
Friday, December 12, 2008
Microsoft has successfully migrated its SQL Server support forums to a platform guaranteed to eliminate an estimated 20-30% of all corporate users. By including the word "social" in the URL, most corporate firewalls will block access automatically. Congratulations go to Microsoft for moving people off of their lowest cost support platform.
http://social.msdn.microsoft.com/Forums/en-US/category/sqlserver/
Ready, Fire, Aim.
Friday, December 05, 2008
One of the easy ways to fail at database administration is to allow your databases to self-manage space. Autogrow is a safety valve, not a pressure regulator. Of course, to manage space you have to know exactly how much free space you have. And since database objects are stored in filegroups, it would help to see free space by filegroups buth in MB and in percentages. Well, here you go.
For those not familiar with my administration style, all my servers have an Admin database to store stuff like this.
use Admin
go
----- Create the following table or results come back empty
--Create Table DBSpaceUsedFileGroupExclude (
--Databasename sysname,
--FilegroupName sysname
--)
--------------
Drop procedure DBSpaceUsed
go
create procedure DBSpaceUsed as
set nocount on
Declare @DBName SysName
Declare @Cmd varchar(1000)
Create Table #FooTable (
[DBName] sysname NULL,
[FileId] int,
[FileGroup] int,
[TotalExtents] int,
[UsedExtents] int,
[DevName] sysname,
[FileName] sysname,
[FileGroupName] Sysname NULL)
Declare DBCursor cursor READ_ONLY FORWARD_ONLY for Select [name] from master.dbo.sysdatabases
-- where [name] in ('SiteData', 'JobAppl')
-- Exclusion now based on table Admin.dbo.DBSpaceUsedFileGroupExclude. Table contains database and filegroup
-- names that are deleted from the final table.
open DBCursor
Fetch next from DBCursor into @DBName
while 0 = @@Fetch_Status
begin
select @CMD = 'USE ' + @DBName + ' dbcc ShowFileStats with TABLERESULTS'
insert INTO #FooTable (FileId,
[FileGroup],
TotalExtents,
UsedExtents,
DevName,
[FileName]) exec (@cmd)
update #FooTable set DBName = @DBName
where DBName is null
select @Cmd = 'USE [' + @DBName + '] Update #FooTable set [FileGroupName] = SFG.GroupName
from #FooTable FT
inner join sysfilegroups SFG
on FT.[Filegroup] = SFG.[GroupID]
where FT.[FileGroupName] IS NULL'
exec (@Cmd)
Fetch next from DBCursor into @DBName
end
Close DBCursor
Deallocate DBCursor
delete from #FooTable
from #FooTable FT
inner join Admin.dbo.DBSpaceUsedFileGroupExclude EX
on EX.Databasename = FT.DBName
and EX.FileGroupName = FT.FileGroupName
select @@Servername as Server, DBName, FileGroupName,
convert(decimal (5,2), 100 - (sum(TotalExtents)-SUM(UsedExtents)) /(convert(numeric,sum(TotalExtents))) * 100) as PercentFull,
(sum(TotalExtents) * 64) /(1024 ) AS MBTotal,
(sum(UsedExtents) * 64) /(1024 ) AS MBUsed,
(sum(TotalExtents - UsedExtents) * 64) /(1024 ) AS MBFree
from #FooTable FT
group by DBName, FileGroupName
drop Table #FooTable
Monday, September 29, 2008
No wait, that’s kickboxing. PowerShell is the something of the future. The management interface, the uber-scripting language, the what???
PowerShell, and its SQL-targeted implementation shipped with SQL Server 2008, brings to mind Michael Faraday’s response when asked “What use is electricity?” He replied “What use is a newborn baby?” PowerShell is somewhat of a newborn baby, much like the very early versions of SQL-based databases were. We see how those databases have grown and transformed IT and business in ways we never thought of. Maybe the future of PowerShell is just as bright?
Enough philosophy, let’s see if we can put this baby to use. Rather than the obligatory “Story of PowerShell”, I am just going to dump you to the Windows PowerShell home page. http://www.microsoft.com/technet/scriptcenter/hubs/msh.mspx You don’t need me to rehash the story or to write yet another “What is a cmdlet” post.
Now, let’s play with this toy and SQL Server. SQL 2008 installs a SQL-Specific provider into PowerShell when launched from SQL Server Management Studio. BOL includes instructions on how to configure PowerShell to add the SQL provider by default or to launch SQLPS.exe outside of SSMS.
The SQL Provider allows you to browse any SQL Server system list like the file system. While this is all based on SMO (Server Management Objects), this is not exactly the same as browsing the SMO object model. As a matter of fact, the toughest thing in PowerShell is bridging the gap between the provider and the object model. Part of this is that the SMO object model is somewhat flat, while the provider shows as a hierarchy.
Start with the provider and drill down to a particular column. You get something like this:
SQLServer:\SQL\MachineName\InstanceName\Databases\Adventureworks2008\Tables\Sales.CreditCard\Columns\CardNumber
If we want an SMO object for the current item we do this:
PS SQLServer:\...\Sales.CreditCard\Columns\CardNumber> $MyColumn = Get-Item .
Note the “.” at the end. Very important. You get an error otherwise
If we just wanted a “blank” Column object we would do this:
PS SQLServer\...\Sales.CreditCard.Columns.CardNumber> $MyColumn = New-Object –TypeName Microsoft.SQLServer.Management.SMO.Column
Pretty much every object for SQL is directly under SMO in the object model, regardless of where it is in the provider hierarchy. This makes it easy to find the documentation on each object since the links are all on one page. The provider hierarchy should look very familiar since it is very close to what we see in SSMS.
Now we can create objects mapped to actual provider locations which represent real server elements. From here it is not so hard to get or set properties, invoke methods, and make a lot of adjustments within the SQL Server system. Since SSMS is based on SMO, we can reasonably assume that any task in SSMS can be accomplished from PowerShell. Remember, PowerShell is a scripting environment, not an application development environment. Some of the things in SSMS require a lot of “glue” to tie the object changes together to accomplish a particular task.
This brings us back to why is PowerShell for SQL still a baby? Well, a baby can only do three things (eat, cry, poo). PowerShell is not quite as limited, but it is really only good for automating some very specific tasks and not for general SQL Server operation and maintenance. I see two critical cmdlets missing in the SQL PowerShell story before we can say it has grown to the next stage. Copy-item and New-Item would make SQL much more complete. Right now, if I want to create a copy of a table, alter its properties by adding a column and change its database (called a parent in the object model), then write the changes to the server, effectively createing the altered table in the new target database, I would have to drill down and copy each column and each property of each column individually. Copy-Item does with other providers such as the file system. Right now, if you try to invoke the Copy-Item cmdlet you get:
Copy-Item : SQL Server PowerShell provider error: Copy-Item is not supported.
And yes, it is bright red by default.
New-Item is a close cousin. If I back up to the columns level (cd ..) and want to add a new column, I cannot use New-Item. I get:
New-Item : The method or operation is not supported
This error message gives me hope that maybe I am not doing something correctly, but I suspect it is also not completely implemented. I get the same message when I execute it at the database level of the provider.
So, while PowerShell is unfinished, it is still a cool tool do script a lot of tedious, repetitive junk where you just set the same thing over and over or to retrieve the same thing from all databases/tables/etc. For version 1, I give it an “A”.
Thursday, August 14, 2008
Solutions and Projects were one of the really cool features introduced in SQL Server 2005, judging from the responses I got when I showed how it works. Personally, I use them a lot. SQL 2008 has the same feature in SQL Server Management Studio. However, SSMS 2008 breaks this feature in SQL 2005 SSMS when installed side-by-side.
See all the ugly details here:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=361706
This also affects 64-bit systems, I just happened to find it on a 32-bit box first.
I am guessing that this failure is inherited from SSMS's Visual Studio ancester, which may make it difficult for the SQL team to fix.. No matter how hard I try, I cannot come up with a good reason that it should work this way. I have thought of several bad reasons, but I will hold off on the rock throwing.
UPDATE!! SQL 2008 CU1 fixes this, at least for 64-bit systems.
Wednesday, August 13, 2008
Fast on the heels of SQL 2008 is the Feature Pack for SQL 2008. Cool goodies include stand-alone installers for SQLCMD and the SQL Native Client, SQL 2008 Server Management Objects, SQL 2008 pre-defined Policies, and lots more.
You can find it here.
http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C
Thursday, August 07, 2008
It looks like SQL 2008 may have a slight dependency issue. If you have already installed Visual Studio 2008, you will be blocked from installing SQL 2008 until you install Visual Studio SP1. The problem is that Visual Studio SP1 is not released yet. Our guys came in ahead of schedule and they still get no respect.
Not to worry, Visual Studio 2008 SP1 should be out very soon (think days, not weeks) and this problem goes away.
Microsoft actually documented this issue here:
Visual Studio 2008 SP1 may be required for SQL 2008 Installations
http://support.microsoft.com/kb/956139
--GNH
UPDATE: It's Here
Wednesday, August 06, 2008
SQL 2008 is finished. MSDN has all the bits downloadable now. Expect retail versions shortly.