Monthly Archives: November 2016

List All Objects and Indexes in a Filegroup or Partition

— by Lori Brown @SQLSupahStah

One of my clients has a rather large database with multiple filegroups and multiple database files in them. I happened to notice that a specific drive that is occupied by 2 specific files that are supposed to hold indexes is getting utilized at a much higher rate than any of the other files. The index file group has 8 files in it. I was anticipating gathering some performance data to try to figure out what indexes are located on those specific files and needed a way to figure that out. My searching for a script led me to TechNet where there are a bunch of scripts that are freely downloadable. The one I needed came Olaf Helper. I added a join on the database files so that I could see what file they are on and filter on that.

Since I need to join my query to the actual database files, I needed to figure out what the file_id was for the specific files that I am interested in.

SELECT * FROM sys.database_files

This is a very modified output so you can see the different filegroups and the files in them.


Checking the data in sys.database files will give you the answer.

Anyway, put it all together and mine looks like this:

SELECT AS DataSpaceName


,AU.type_desc AS AllocationDesc

,AU.total_pages / 128 AS TotalSizeMB

,AU.used_pages / 128 AS UsedSizeMB

,AU.data_pages / 128 AS DataSizeMB

, AS SchemaName

,OBJ.type_desc AS ObjectType

, AS ObjectName

,IDX.type_desc AS IndexType

, AS IndexName

FROM sys.data_spaces AS DS

INNER JOIN sys.allocation_units AS AU

ON DS.data_space_id = AU.data_space_id

INNER JOIN sys.partitions AS PA

ON (AU.type IN (1, 3)

AND AU.container_id = PA.hobt_id)


(AU.type = 2

AND AU.container_id = PA.partition_id)

JOIN sys.database_files f

on AU.data_space_id = f.data_space_id

INNER JOIN sys.objects AS OBJ

ON PA.object_id = OBJ.object_id

INNER JOIN sys.schemas AS SCH

ON OBJ.schema_id = SCH.schema_id

LEFT JOIN sys.indexes AS IDX

ON PA.object_id = IDX.object_id

AND PA.index_id = IDX.index_id

WHERE f.file_id IN (13,14) AND AU.total_pages > 0 — Look at specific files, could also filter on file group

ORDER BY AU.total_pages desc — Order by size





The output looks like this…I sorted on size but left the columns needed to sort by name:


Now I am ready to collect performance data to see what stressful queries are using the indexes located on these files.

I don’t have the results from the performance data just yet but will likely follow up later on putting all the pieces together to solve this very specific problem.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at We will be happy to help! Leave a comment and feel free to track back to us. Visit us at!


Upcoming SQLRX Lunch and Learn

Please join SQLRX’s Lori Brown and the SQLRX team at the Microsoft Campus in Irving, Texas this coming Wednesday November 16, 2016 for an informative take on reasons to upgrade to SQL Server 2016.

Register Now!


There are a lot of really good reasons to upgrade to SQL 2016. Even if you’re still on SQL 2008 R2 or older, and worried about what might break. Either way, it’s time to upgrade! Join the experts at SQLRx for reasons why to upgrade and how to do it with less risk using SQLRx.

Presentation Topics will include:

  • What’s NEW and really Improved.
  • Using ReplayableTrace to know before you go.
  • Already Upgraded but nothing to show – SQLRx can fix that.
  • BI, AlwaysOn, The Query Store and more…

Who Should Attend: Anyone managing critical business applications running the SQL Server Platform.

Wednesday, Nov 16

Lunch and registration: 11:30

Presentation: 12:00 – 1:00 pm CST

Take this opportunity to meet our experts face to face and see what all the buzz is about in SQL 2016’s new features that improve availability, compatibility, scalability and manageability.


Microsoft Campus 7000 State Hwy 161 (Bush Turnpike) Bldg LC 1 (Bldg on the right) Irving, TX 75039

Register Now!


Get SQL Start Up Parameters and Other Info From sys.dm_server_registry

–By Lori Brown @SQLSupahStah

The dynamic management view sys.dm_server_registry has been around since SQL 2008 R2 SP1 so it is not new but it’s relatively new to me. Since I have a ton of SQL Servers to monitor, it is always good to have some way of easily knowing if there are any start up parameters configured for the instance. I found that this and more can be pulled from the registry using sys.dm_server_registry. ( )

If you look at all values returned by the dmv, you will see something like this:


In my local instance there are 191 rows returned so this can get to be a little difficult to read through. I wanted to just report on certain things so I have narrowed what is returned. Most of the time I am not interested in the actual registry key, so here is what I did:

— Startup Parameters

SELECT value_name, value_data

FROM sys.dm_server_registry

WHERE registry_key LIKE N’%Parameters’

Which returns this as the results:


Notice SQLArg3 which shows that I have added the T1118 trace flag to the startup parameters. Of course I can get some of the same info by opening up the configuration manager and checking the Startup Parameters tab of the SQL Server service but I wanted my results sent to me periodically in a report so that I can know if someone had added or changed anything. I’ll show my report later.

I also have found that it is good to know what protocols are enabled and what port SQL is listening on. Since I have a lot of servers to manage, with some on the default port and others not, I thought that it would be good to report on this. My clients also have different protocols enabled for different servers. So, since nothing is standardized, it’s good that this info be put into a report so that I can easily refer to it should I ever need it.

— List protocols enabled

SELECT sr.value_data AS EnabledProtocol

FROM sys.dm_server_registry sr

WHERE sr.registry_key IN (SELECT k.registry_key

FROM sys.dm_server_registry k

WHERE k.value_name = ‘Enabled’ AND k.value_data = 1)

AND sr.value_name = ‘DisplayName’


— List TCP port

SELECT value_name AS PortName, value_data AS PortValue

FROM sys.dm_server_registry

WHERE registry_key LIKE N’%SuperSocketNetLib\Tcp\IPAll’

AND value_name NOT LIKE N’DisplayName’


And, here is what my report looks like. I like to make small HTML formatted reports that I know I can generate on pretty much any server.


With a little bit of research and creativity, you can come up with ways to get at information in SQL easily.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at We will be happy to help! Leave a comment and feel free to track back to us. Visit us at!