September 22, 2016 at 5:56 am
Hi,
I'm querying sys.master_files to get the size of my data files and indexes for a particular database:
SELECT a.[name], a.physical_name, sum((a.size * 8.0) / 1024) AS SizeInMB
FROM sys.master_files a
GROUP BY a.[name], a.physical_name
ORDER BY a.[name]
The query reports the mdf as 7,213 MB (7 GB) and the ndf as 52,023 MB (52 GB). At the file system level, they are 7,386 MB and 53,272, respectively.
Two questions:
1) I know the values are close between the query results and the actual file system, but is there a good explanation for why they're still somewhat different?
2) Is it surprising that the ndf file is over 7x larger than the data file?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 22, 2016 at 6:00 am
sys.master_files is, iirc, as-of the time that SQL last started, so if the DB's grown since then it'll be out of date.
2) Is it surprising that the ndf file is over 7x larger than the data file?
Why would it be?
ndf is also data file, a secondary data file. The size of a data file is dependant on what's in it, so if you've set the DB up with two filegroups and most tables are on the second filegroup (which will have the ndf file in it), then the secondary data file will be a lot larger than the primary data file. Completely depends on what's in what filegroup
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2016 at 6:11 am
GilaMonster (9/22/2016)
sys.master_files is, iirc, as-of the time that SQL last started, so if the DB's grown since then it'll be out of date.2) Is it surprising that the ndf file is over 7x larger than the data file?
Why would it be?
ndf is also data file, a secondary data file. The size of a data file is dependant on what's in it, so if you've set the DB up with two filegroups and most tables are on the second filegroup (which will have the ndf file in it), then the secondary data file will be a lot larger than the primary data file. Completely depends on what's in what filegroup
Thanks for the response.
Is it true that indexes are always stored in the ndf, or could they be stored in the mdf too, and both the mdf and ndf are equivalent from SQL's perspective (except that one is primary and one is secondary)?
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 22, 2016 at 6:19 am
Mike Scalise (9/22/2016)
Is it true that indexes are always stored in the ndf, or could they be stored in the mdf too, and both the mdf and ndf are equivalent from SQL's perspective (except that one is primary and one is secondary)
Indexes are stored where their create statement specifies.
SQL's filegroups have one or more files each. By convention (and only convention), the first file in the Primary filegroup has the .mdf extension, all other files have the .ndf extension. So, if you have two filegroups, primary and one we'll call f2 and you create an index and specify ON [PRIMARY], the index goes into the primary filegroup, into one of the files in primary (if there's more than one, the index will likely be written partially to each of them). If you create an index and specify ON [f2], then the index goes into the secondary filegroup.
Does that help?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2016 at 6:25 am
Yes, I get it now, and that makes perfect sense. Thanks for the clarification--it really brings everything together for me. I'm looking forward to your presentation(s) at the PASS Summit!
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply