The Call for Speakers is now open for SQL Saturday #50, the East Iowa SQL Saturday event! This is our second time hosting a SQL Saturday, and we're hoping to build upon the success of last year's event. We're looking for a wide variety of topics on SQL Server and related technologies (i.e. PowerShell, R2, LINQ, etc.). We also have had several requests for intro-level topics, such as beginning disaster recovery and basic performance tuning. If you're even remotely thinking about speaking, please submit an abstract!
Last year we had about 100 folks attend from surrounding areas. This year, we're shooting for 125 attendees, which would max out our facility's capacity. Not sure how far away Iowa City is? It may be closer than you think. Allow me to rehash my travel times from last year's plea for speakers:
- Chicago – 3.5 hours
- Omaha – 3.5 hours
- Milwaukee – 4 hours
- Kansas City – 4.5 hours
- Minneapolis – 5 hours
- St. Louis – 5 hours
- Indianapolis – 6 hours
The event will be held on September 18th at the University of Iowa in Iowa City. You can find more information, including an abstract submission form, on our event website at http://sqlsaturday.com/50/eventhome.aspx.
Oh, and if you do make it to our SQL Saturday event, please make sure to stop me and say "hi!" 
Today I stumbled across a database with 87,302 VLF's. Yes, that's right... 87 THOUSAND. Most of our databases have a few dozen VLF's, but this was an old database that had grown to 1.5 TB and had the default autogrowth settings left in tact. How did we discover this? During a routine reboot of the server, this database took 30 minutes to recover, but there were no error messages or status messages in the log.
Now, this blog post is not about VLF's or why you should keep the number of VLF's to a small, manageable number -- although I hear under 50 is a good rule of thumb. No, the purpose of this blog post is to share a little script I wrote to check the number of VLF's each database uses:
Create Table #stage(
FileID int
, FileSize bigint
, StartOffset bigint
, FSeqNo bigint
, [Status] bigint
, Parity bigint
, CreateLSN numeric(38)
);
Create Table #results(
Database_Name sysname
, VLF_count int
);
Exec sp_msforeachdb N'Use ?;
Insert Into #stage
Exec sp_executeSQL N''DBCC LogInfo(?)'';
Insert Into #results
Select DB_Name(), Count(*)
From #stage;
Truncate Table #stage;'
Select *
From #results
Order By VLF_count Desc;
Drop Table #stage;
Drop Table #results;This script is low-impact and is safe to run on large, production databases during business hours. However, just be aware that it's using some undocumented commands.
For more information on VLF's, check out these excellent articles:
I had previously posted an index interrogation script for SQL Server 2005. I've updated that script for 2008; namely, it includes filtered index definitions. For anyone interested:
Declare @objectID int = Object_ID('Sales.SalesOrderHeader');
With indexCTE(partition_scheme_name
, partition_function_name
, data_space_id)
As (
Select sps.name
, spf.name
, sps.data_space_id
From sys.partition_schemes As sps
Join sys.partition_functions As spf
On sps.function_id = spf.function_id
)
Select st.name As 'table_name'
, IsNull(ix.name, '') As 'index_name'
, ix.object_id
, ix.index_id
, Cast(
Case When ix.index_id = 1
Then 'clustered'
When ix.index_id =0
Then 'heap'
Else 'nonclustered' End
+ Case When ix.ignore_dup_key <> 0
Then ', ignore duplicate keys'
Else '' End
+ Case When ix.is_unique <> 0
Then ', unique'
Else '' End
+ Case When ix.is_primary_key <> 0
Then ', primary key' Else '' End As varchar(210)
) As 'index_description'
, IsNull(Replace( Replace( Replace(
(
Select c.name As 'columnName'
From sys.index_columns As sic
Join sys.columns As c
On c.column_id = sic.column_id
And c.object_id = sic.object_id
Where sic.object_id = ix.object_id
And sic.index_id = ix.index_id
And is_included_column = 0
Order By sic.index_column_id
For XML Raw)
, '"/><row columnName="', ', ')
, '<row columnName="', '')
, '"/>', ''), '')
As 'indexed_columns'
, IsNull(Replace( Replace( Replace(
(
Select c.name As 'columnName'
From sys.index_columns As sic
Join sys.columns As c
On c.column_id = sic.column_id
And c.object_id = sic.object_id
Where sic.object_id = ix.object_id
And sic.index_id = ix.index_id
And is_included_column = 1
Order By sic.index_column_id
For XML Raw)
, '"/><row columnName="', ', ')
, '<row columnName="', '')
, '"/>', ''), '')
As 'included_columns'
, ix.filter_definition
, IsNull(cte.partition_scheme_name, '') As 'partition_scheme_name'
, Count(partition_number) As 'partition_count'
, Sum(rows) As 'row_count'
From sys.indexes As ix
Join sys.partitions As sp
On ix.object_id = sp.object_id
And ix.index_id = sp.index_id
Join sys.tables As st
On ix.object_id = st.object_id
Left Join indexCTE As cte
On ix.data_space_id = cte.data_space_id
Where ix.object_id = IsNull(@objectID, ix.object_id)
Group By st.name
, IsNull(ix.name, '')
, ix.object_id
, ix.index_id
, Cast(
Case When ix.index_id = 1
Then 'clustered'
When ix.index_id =0
Then 'heap'
Else 'nonclustered' End
+ Case When ix.ignore_dup_key <> 0
Then ', ignore duplicate keys'
Else '' End
+ Case When ix.is_unique <> 0
Then ', unique'
Else '' End
+ Case When ix.is_primary_key <> 0
Then ', primary key' Else '' End As varchar(210)
)
, ix.filter_definition
, IsNull(cte.partition_scheme_name, '')
, IsNull(cte.partition_function_name, '')
Order By table_name
, index_id;You may need to create some indexes to see this in AdventureWorks:
Create NonClustered Index IX_Sales_SalesOrderHeader_filtered_2005
On Sales.SalesOrderHeader(AccountNumber)
Include (CustomerID, SalesPersonID)
Where OrderDate >= '2005-01-01'
And OrderDate < '2006-01-01';table_name index_name object_id index_id index_description indexed_columns included_columns filter_definition partition_scheme_name partition_count row_count
-------------------- ---------------------------------------- ----------- ----------- ----------------------------------- -------------------- ------------------------------ ------------------------------------------------------------ --------------------- --------------- --------------------
SalesOrderHeader PK_SalesOrderHeader_SalesOrderID 1010102639 1 clustered, unique, primary key SalesOrderID NULL 1 31465
SalesOrderHeader AK_SalesOrderHeader_rowguid 1010102639 2 nonclustered, unique rowguid NULL 1 31465
SalesOrderHeader AK_SalesOrderHeader_SalesOrderNumber 1010102639 3 nonclustered, unique SalesOrderNumber NULL 1 31465
SalesOrderHeader IX_SalesOrderHeader_CustomerID 1010102639 5 nonclustered CustomerID NULL 1 31465
SalesOrderHeader IX_SalesOrderHeader_SalesPersonID 1010102639 6 nonclustered SalesPersonID NULL 1 31465
SalesOrderHeader IX_Sales_SalesOrderHeader_filtered_2005 1010102639 13 nonclustered AccountNumber CustomerID, SalesPersonID ([OrderDate]>='2005-01-01' AND [OrderDate]<'2006-01-01') 1 1379
