SQL Tact

Use the Default System Trace to Find Autogrowth Events

2013-05-03T09:41:00.001-05:00

The SQL Server Management Studio built-in standard report for "Disk Usage" is very handy to view the performance-sucking autogrowth events for a database... until you want to see if autogrowth events have been occurring in any database on the server.

Autogrowth events are very problematic and can cause sudden, unpredictable and difficult-to-troubleshoot application timeouts and performance drops. This applies to any SQL Server database file, including those in SharePoint databases, system databases (such as the tempdb), and any user database data file or log file.

[As an aside, your friends in SharePoint know all about Autogrowth settings for their content databases as well. Friend of the blog Drew at WerePoint posted this relevant blog entry recently about the timing of growth and recycle-bin emptying events. http://werepoint.blogspot.com/2013/05/on-proper-care-and-feeding-of-content.html ]

Ideally, as professional DBAs, we don't want to ever see an autogrowth event occur on our databases. By proactively monitoring the space in our database files, we can grow database files on our own terms, on our own schedule (outside of busy hours), and without interrupting a user transaction.

Autogrowth events occur when a data file or log file has exhausted its reservation. SQL is forced to request the operating system that more space to be added to a file. During this request and the file growth, very little happens to the data file. This typically occurs during a user transaction - bad news!

The increment of the autogrowth might be defaulted to just 1 MB on your system, which means a single large transaction could result in a series of sequential autogrowth events before the transaction finally commits. In the end, the argument over fixed autogrowth size versus percentage-based autogrowth settings are academic - a vigilant DBA should try to avoid autogrowth events in the first place.

But, while we're on the topic, 10% is a completely reasonable growth rate for data and log files for most databases. Depending on your available storage space, moving to a fixed growth rate (100mb-500mb) is more appropriate.

Instant File Initialization can also make a big difference in the time required to grow a database file (as well as restore database backups.) Note that this may need to be enabled at the virtual guest and SAN level for some virtual environments.

Here's a script to view the available space in every database file in a SQL Server instance, using the hacky old sp_MSforeachdb undocumented stored procedure:

exec sp_MSforeachdb  'use [?]; 
SELECT 
  ''DatabaseName_____________'' = d.name
, Recovery = d.recovery_model_desc
, ''DatabaseFileName_______'' = df.name
, ''Location_______________________________________________________________________'' = df.physical_name
, df.File_ID
, FileSizeMB = CAST(size/128.0 as Decimal(9,2))
, SpaceUsedMB = CAST(CAST(FILEPROPERTY(df.name, ''SpaceUsed'') AS int)/128.0 as Decimal(9,2))
, AvailableMB =  CAST(size/128.0 - CAST(FILEPROPERTY(df.name, ''SpaceUsed'') AS int)/128.0 as Decimal(9,2))
, ''Free%'' = CAST((((size/128.0) - (CAST(FILEPROPERTY(df.name, ''SpaceUsed'') AS int)/128.0)) / (size/128.0) ) * 100. as Decimal(9,2))
 FROM sys.database_files df
 cross apply sys.databases d
 where d.database_id = DB_ID() 
 and size > 0
'

One important piece of information we can get from the default trace when we look for autogrowth events is the time and duration of each autogrowth event. I've personally used these two pieces of information to prove that random application timeouts experienced by a client were because of database file autogrowth events stalling their transaction for whole seconds while more space was added.

How far back does the default trace go? It depends on how much activity is being written to the default trace (which obviously needs to be enabled for this to work). The current active default trace file keeps up to 20mb of data. Five default trace files are kept of 20mb each. This script only reads the latest file.

DECLARE @tracepath nvarchar(260)

SELECT 
 @tracepath = path 
FROM sys.traces 
WHERE is_default = 1

SELECT 
 DBName    = g.DatabaseName
, DBFileName   = mf.physical_name
, FileType   = CASE mf.type WHEN 0 THEN 'Row' WHEN 1 THEN 'Log' WHEN 2 THEN 'FILESTREAM' WHEN 4 THEN 'Full-text' END
, EventName   = te.name
, EventGrowthMB  = convert(decimal(19,2),g.IntegerData*8/1024.) -- Number of 8-kilobyte (KB) pages by which the file increased.
, EventTime   = g.StartTime
, EventDurationSec = convert(decimal(19,2),g.Duration/1000./1000.) -- Length of time necessary to extend the file.
, CurrentAutoGrowthSet= CASE
        WHEN mf.is_percent_growth = 1
        THEN CONVERT(char(2), mf.growth) + '%' 
        ELSE CONVERT(varchar(30), convert(decimal(19,2), mf.growth*8./1024.)) + 'MB'
       END
, CurrentFileSizeMB = convert(decimal(19,2),mf.size* 8./1024.)
, MaxFileSizeMB  = CASE WHEN mf.max_size = -1 THEN 'Unlimited' ELSE convert(varchar(30), convert(decimal(19,2),mf.max_size*8./1024.)) END
FROM fn_trace_gettable(@tracepath, default) g
cross apply sys.trace_events te 
inner join sys.master_files mf
on mf.database_id = g.DatabaseID
and g.FileName = mf.name
WHERE g.eventclass = te.trace_event_id
and  te.name in ('Data File Auto Grow','Log File Auto Grow')
--GROUP BY StartTime,Databaseid, Filename, IntegerData, Duration
order by StartTime desc

Hunt Down Tables Without Clustered Indexes

2013-05-03T09:29:00.000-05:00

This one's a gimme, but an underrated utility script when I enter a new environment and look for low-hanging fruit for easy improvements.

You'd be surprised how many vendors ship applications without a single index, much less properly aligned clustered indexes.

Heaps are bad. You can't defragment a heap by performing index maintenance, despite old myths about the create/drop a clustered index trick.

Similarly, the myth of a heap performing better on pure inserts vs a table with a clustered index is an academic one. Heaps are impossible to order, which means selecting from a heap is always a scan.

If your table will be constantly written to and rarely read, put a clustered index on it. The writes will be sequential, resulting in ~~fewer page breaks and~~ less fragmentation, which mean more efficient storage and maintenance.

EDIT: Page "breaks" (I meant splits) aren't possible on heaps, but forwarding pointers, which create a maze of bad performance in heaps with nonclustered indexes. Thanks to commenters for pointing this out!

And when you have to look in that table for a stray logging record, it won't be a full table scan. If your table will be constantly written to and never read, why is it in your database? Such a table doesn't exist.

This script is an easy one to use for finding heaps in a database, along with the row count and size of jumbled data that is haunting your database. It is in my DBA toolbox folder alongside many other scripts.

--Doesn't work on 2000 databases or databases in 2000 compatability mode.  Need to change the db_id() syntax if so.

select 
 [Database Name] = db_name()
, [Table Name] = s.name + '.' + o.name
, p.row_count
, SizeMb= (p.reserved_page_count*8.)/1024.
from 
 sys.objects o
inner join  sys.schemas s on o.schema_id = s.schema_id
inner join  sys.dm_db_partition_stats p  on p.object_id = o.object_id 
inner join  sys.indexes si on si.object_id = o.object_ID
WHERE si.type_desc = 'Heap'
and  is_ms_shipped = 0
order by SizeMb desc

As an aside, but a different blog post altogether - a good clustered key is narrow, unique, unchanging and sequential - which is why IDENTITY integer columns are the perfect clustered key. Compound clustered keys can be a big waste of space.

TRY_CONVERT broken by WITH SCHEMABINDING in SQL Server 2012 Data Tools build

2013-03-21T06:00:00.000-05:00

Here's a bug you might find if attempting to schemabind an object that uses the new TRY_CONVERT function.

To reproduce, run the following code in a SSDT database solution:

CREATE VIEW [dbo].[aview] WITH SCHEMABINDING 
AS 
SELECT id = TRY_CONVERT(int, id) 
FROM dbo.arealtable

The database project should build with this error:

Error 3 SQL70561: Cannot schema bind view '[dbo].[aview]' because name 'int' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself. C:\Users\...\Documents\Visual Studio 2010\Projects\...\Views\dbo.aview.sql

This view is perfectly valid in SQL Server, and can be created without a problem. In a Data Tools database solution, it prevents the solution from being built with the above error.

In the above screenshot, "try_convert(int" is underlined in red because SSDT isn't recognizing the syntax correctly. Again, this only appears when the view is created with schemabinding.

Note that in my bug report, the project's target platform is SQL 2012 and the project's database settings compatibility mode is SQL 2012, using Microsoft Visual Studio 2010 Version 10.0.40219.1 SP1 and SQL Server Data Tools 10.3.21101.1.

This has been marked as "resolved" on Microsoft Connect, and the fix "will appear in an upcoming release of DACFx/SSDT", as of March 2013: https://connect.microsoft.com/SQLServer/feedback/details/772804/try-convert-broken-by-with-schemabinding-in-sql-server-2012-data-tools-build#details

Conversation History: The Continuing Case Against GUIDs

2013-03-08T04:00:00.000-06:00

We had a cooperative relational database design exercise at the office last week as part of our regular department "Lunch and Learn" series, and inevitably one topic that came up is the use of GUIDs as the unique key for a table.

And before long, I had been goaded onto my soapbox to deliver my hellfire-and-brimstone sermon against the use of GUIDs as the primary key, much less the clustered index, of a table. (If you haven't heard this, you need to attend more Baton Rouge SQL Server User Group meetings.)

The case for GUIDs traces back to an oil rig case study, an actual design example we (at Sparkhound) encountered here in the Gulf South. There are hundreds of oil rigs out in the middle of the Gulf of Mexico, each with a sometimes-available Internet connection, and the need to sync back home to a onshore server. By using GUIDs, each server can write a unique key to the same table, so the argument says, without any conflicts.

(This is not my complete soap-box against GUIDs. Another day.)

Why not use a compound key of integers? Surely, you must still identify what oil rig is sending the data, then using a rig-side identity column would provide a compound primary key with guaranteed uniqueness and at half the cost (4 bytes x 2) of a GUID (16 bytes). That storage space adds up, and has a big impact on nonclustered indexes, fragmentation, page splits, etc.

After that lunch and learn ended, one of our bright new hires engaged me afterwards...

C.M. 1:22 PM

So, to play a little devil's advocate, take your oil rig example, and make it a mobile app

it may be impractical or impossible to keep a record of each client running your application

a user might run the app from multiple devices so you can't use their login

William A. 1:38 PM

cool

the mobile app would still need to be registered to identify it as some sort of key

we may need to know about the equipment they're using or other business data

that registration process would assign an ID to that equipment

it'd have to be linked to a user so its data can be reported consistently every time they check in

or if not to a user, some sort of hardware id.

C.M. 1:41 PM

i was thinking more like a consumer application, without any central management to register new devices, but i suppose the software could connect to the server the first time it runs and obtain a permanent device ID

depending if it's acceptable to require them to be online when they start using it for the first time

William A. 1:44 PM

even then, the local repository could be required to be updated with an account/device/user ID before syncing

C.M. 1:46 PM

so, i'll concede that there's almost always a way to avoid the guids

William A. 1:46 PM

hah

yep

i agree!

though I assume a system like SharePoint would be a nightmare without GUIDs

another alternative is ranges of ID's

so this oil rig gets values 1-1 million, the next gets values of 2-3 million, but i dislike that for a bunch of reasons.

but i've seen it done

at that point it turns the number into a smartcode, which i dislike.

C.M. 1:49 PM

the other context i see them being useful is when you need uniqueness among several different kinds of entities, but i know there are alternative ways to do that too

guids just make it easy

William A. 1:50 PM

easy... until you get to scale

and easy... if you're not the one in charge of index maintenance

and easy... if storage space is unlimited.

and easy... if you can hide bad performance with iron

otherwise... easier(?) maybe :)

C.M. 1:51 PM

yeah, like i said, easy :P

William A. 1:51 PM

hahhah

C.M. 1:51 PM

anyway, i enjoyed the lunch and learn, thanks

William A. 1:51 PM

thanks for coming! nice to "work" with you for the first time

next time you're at a big convention with SQL tracks, there's usually someone there who will RAIL on GUID's

Kimberly Tripp is a SQL MVP and author who gives a 90min anti-GUID presentation that will make you CRY either in despair or laughter

C.M. 1:52 PM

haha

William A. 1:52 PM

depending on how much you've used GUIDs

heh

C.M. 1:52 PM

i haven't used them much personally

William A. 1:53 PM

was that an interview question? If not... it should be :)

C.M. 1:53 PM

only really used them when coding against the TFS API or recently Microsoft CRM

however, i've read about the [theoretical] use of them in some contexts

William A. 1:53 PM

yeah, as a result, SharePoint (and CRM) have different rules for treating database indexes and statistics and their own elaborate internal index maintenance plans

C.M. 1:54 PM

like then a client needs to generate an ID itself instead of letting the server/database do it

then -> when

William A. 1:54 PM

that's a good scenario when GUID's could be helpful too

however

in SQL 2012 there's a new feature called SEQUENCE

where an app can go and get the "next" value and use it to reliably insert their new primary key value.

Oracle has a similar feature

C.M. 1:56 PM

yeah, that's pretty nice

William A. 1:56 PM

sequences are the way to avoid having to insert, retrieve @@IDENTITY, then insert related tables, etc.

can get a sequence out of a function, perform one transaction worth of inserts

C.M. 1:57 PM

is a particular sequence tied to a table or what?

William A. 1:57 PM

nope

C.M. 1:58 PM

i'll read up on it, good stuff

William A. 1:58 PM

welcome aboard man, have a great weekend too

C.M. 1:58 PM

you too, thanks

YYYYMM Math

2013-03-01T08:14:00.000-06:00

I had a project recently where I had to do math on a number that represented a date, YYYYMM.

For example, February 2013 would be stored as an integer, 201302.

As an aside, YYYYMM makes WAY more sense than ever storing anything MMYYYY. Why? For one, it sorts correctly. And the biggest reason - no need to worry about leading zeroes, and therefore the data can be stored as NUMERIC, not VARCHAR data.

I needed to do month math on this data. For example, give me the previous month, six months ago, one year ago, nine months in the future, etc. This gets tricky because though it is tempting to do -1, -6, -12, +9, the rollover of years does not work at all.

The following function solves the problem.

ALTER FUNCTION dbo.[YearMonth_Math]
(
@Yearmonth int, @months smallint
)
RETURNS INT
AS
BEGIN
RETURN
 CASE WHEN @months THEN CASE --This case handles flipping January to the previous years December.
 WHEN convert(int, Right(@Yearmonth,2)) <= abs(@months) --will we need to flip a year?
 THEN convert(int,
  convert(char(4), Left(@Yearmonth,4) + (((@Months)) / 12)  - case when right(@YearMonth, 2) + (@months % 12) < 1 Then 1 else 0 end)
  + convert(char(2), right('0' +  convert(varchar(3), (right(@yearmonth, 2) + (@months % 12) 
  + case when right(@YearMonth, 2) + (@months % 12) < 1 Then 12 else 0 end)),2))
  )
  --Otherwise, this previous month calculation is very simple.
  ELSE @Yearmonth + @months
 END
 WHEN @months >0
 THEN CASE --This case handles flipping December to the next years January.
  WHEN 12 - convert(int, Right(@Yearmonth,2)) <= @months --will we need to flip a year?
  THEN convert(int,
   convert(char(4), left(@YearMonth,4) + ((@months + right(@yearMonth,2) -1) / 12) ) +   
   convert(char(2), right('0' + convert(varchar(3), (right(@YearMonth, 2) + (@months % 12)  
   - case when right(@YearMonth, 2) + (@months % 12) > 12 THen 12 else 0 end)),2))
   )
   --Otherwise, this previous month calculation is very simple.
   ELSE @Yearmonth + @months
  END
 ELSE @YearMonth
 END
END

Here's some testing to verify all the different possibilities.

select  dbo.[YearMonth_Math] (201212,1), 201301
select  dbo.[YearMonth_Math] (201212,2), 201302
select  dbo.[YearMonth_Math] (201212,7), 201307
select  dbo.[YearMonth_Math] (201212,12), 201312
select  dbo.[YearMonth_Math] (201212,13), 201401
select  dbo.[YearMonth_Math] (201212,24), 201412
select  dbo.[YearMonth_Math] (201212,25), 201501
select  dbo.[YearMonth_Math] (201212,36), 201512
select  dbo.[YearMonth_Math] (201201,-1), 201112
select  dbo.[YearMonth_Math] (201201,-2), 201111
select  dbo.[YearMonth_Math] (201201,-7), 201106
select  dbo.[YearMonth_Math] (201201,-12), 201101
select  dbo.[YearMonth_Math] (201201,-13), 201012
select  dbo.[YearMonth_Math] (201201,-24), 201001
select  dbo.[YearMonth_Math] (201201,-25), 200912
select  dbo.[YearMonth_Math] (201212,-1), 201211
select  dbo.[YearMonth_Math] (201212,-2), 201210
select  dbo.[YearMonth_Math] (201212,-7), 201205
select  dbo.[YearMonth_Math] (201212,-12), 201112
select  dbo.[YearMonth_Math] (201212,-13), 201111
select  dbo.[YearMonth_Math] (201212,-24), 201012
select  dbo.[YearMonth_Math] (201212,-25), 201011
select  dbo.[YearMonth_Math] (201212,-36), 200912
select  dbo.[YearMonth_Math] (201206,1), 201207
select  dbo.[YearMonth_Math] (201206,-1), 201205
select  dbo.[YearMonth_Math] (201206,2), 201208
select  dbo.[YearMonth_Math] (201206,-2), 201204
select  dbo.[YearMonth_Math] (201201,1), 201202
select  dbo.[YearMonth_Math] (201201,13), 201302
select  dbo.[YearMonth_Math] (201201,25), 201402