Ndf sql server что за файл
SQL Server использует MDF для файлов данных и LDF для файлов журналов, но что такое файлы NDF?
в чем преимущества этих файлов?
с архитектура файлов и файловых групп
вторичные файлы данных
вторичные файлы данных составляют все файлы данных, кроме первичного файла данных. Некоторые базы данных могут не иметь дополнительных файлов данных, в то время как другие имеют несколько дополнительных файлов данных. рекомендуемое расширение имени файла для вторичных файлов данных .НДФЛ.
и с расширение файла NDF-Microsoft SQL Server secondary файл данных
посмотреть понимание файлов и файловых групп
дополнительные файлы данных являются необязательными, определяемые пользователем, и сохранять пользовательские данные. Вторичные файлы могут быть использованы для распространения данные на несколько дисков, каждый файл на другом диске. Кроме того, если база данных превышает максимальный размер для одного окна файл, вы можете использовать вторичные файлы данных таким образом, база данных может продолжать расти.
в рекомендуемое расширение имени файла для вторичных файлов данных .НДФЛ.
например, три файла, Data1.ndf, Данных2.ndf, и Data3.НДФ, может быть создано на трех дисках, соответственно, и присваивается файловая fgroup1. Таблица может тогда быть создан специально для файловой группы fgroup1. Запросы на получение данных из стол будет разбросан по всей тройке диски; это позволит повысить производительность. Такое же улучшение производительности мочь быть выполнено с помощью одного файла созданный на RAID (избыточный массив независимые диски) набор полосок. Однако, файлы и файловые группы позволяют легко добавлять новые файлы на новые диски.
вторичные файлы данных являются необязательными, определяются пользователем и хранят пользовательские данные. Вторичные файлы можно использовать для распространения данных по нескольким дискам, помещая каждый файл на другой диск. Кроме того, если база данных превышает максимальный размер для одного файла Windows, можно использовать вторичные файлы данных, чтобы база данных могла продолжать расти.
рекомендуемое имя файла расширение для вторичных файлов данных .ndf , но это не является обязательным.
файл NDF-это определяемый пользователем файл базы данных-получателя Microsoft SQL Server с расширением .ndf, которые хранят пользовательские данные. Кроме того, когда размер файла базы данных растет автоматически от его указанного размера, вы можете использовать .файл ndf для дополнительного хранения и т. д.файл ndf может храниться на отдельном диске. Каждый файл NDF использует то же имя файла, что и соответствующий файл MDF. Мы не можем открыть .файл ndf в SQL Server без прикрепления его связанного .файл МДФ.
Каждая база данных SQL Server имеет как минимум два рабочих системных файла: файл данных и файл журнала. Файлы данных содержат данные и объекты, такие как таблицы, индексы, хранимые процедуры и представления. Файлы журнала содержат сведения, необходимые для восстановления всех транзакций в базе данных. Файлы данных могут быть объединены в файловые группы для удобства распределения и администрирования.
Планирование ресурсов для tempdb в SQL Server
Определение требуемого размера tempdb в рабочей среде SQL Server зависит от многих факторов. Как описано выше, эти факторы включают текущую рабочую нагрузку и используемые функции SQL Server. Рекомендуется проанализировать текущую рабочую нагрузку, выполнив следующие задачи в среде тестирования SQL Server:
- Включите автоувеличение для tempdb .
- Запускайте отдельные запросы или файлы трассировки рабочей нагрузки и следите за использованием диска базой данных tempdb .
- Выполняйте операции обслуживания индексов, например перестроение индексов, и следите за использованием диска базой данных tempdb .
- Используйте значения используемого пространства на диске из предыдущих шагов для прогнозирования общей рабочей нагрузки. Скорректируйте полученное значение с учетом предполагаемой параллельной обработки и задайте соответствующий размер tempdb .
Database Snapshot Files
The form of file that is used by a database snapshot to store its copy-on-write data depends on whether the snapshot is created by a user or used internally:
- A database snapshot that is created by a user stores its data in one or more sparse files. Sparse file technology is a feature of the NTFS file system. At first, a sparse file contains no user data, and disk space for user data hasn't been allocated to the sparse file. For general information about the use of sparse files in database snapshots and how database snapshots grow, see View the Size of the Sparse File of a Database Snapshot.
- Database snapshots are used internally by certain DBCC commands. These commands include DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, and DBCC CHECKFILEGROUP. An internal database snapshot uses sparse alternate data streams of the original database files. Like sparse files, alternate data streams are a feature of the NTFS file system. The use of sparse alternate data streams allows for multiple data allocations to be associated with a single file or folder without affecting the file size or volume statistics.
Ограничения оптимизированной для памяти базы данных tempdb
Включение и отключение функции не является динамическим. Из-за внутренних изменений, которые необходимо внести в структуру tempdb , для включения или отключения этой функции требуется перезапуск.
Отдельная транзакция не может обратиться к таблицам, оптимизированным для памяти, в более чем одной базе данных. Все транзакции, связанные с таблицей, оптимизированной для памяти, в пользовательской базе данных, не смогут обратиться к системным представлениям tempdb в той же транзакции. Если вы попытаетесь обратиться к системным представлениям tempdb в транзакции с участием таблицы, оптимизированной для памяти, в пользовательской базе данных, возникнет следующая ошибка:
Запросы к таблицам, оптимизированным для памяти, не поддерживают указания блокировки и изоляции, поэтому запросы к представлениям каталога оптимизированной для памяти tempdb не будут учитывать указания блокировки и изоляции. Как и в случае с другими системными представлениями каталога в SQL Server, все транзакции для системных представлений будут находиться в изоляции READ COMMITTED (или READ COMMITTED SNAPSHOT в нашем случае).
Если оптимизированные для памяти метаданные tempdb включены, индексы columnstore нельзя создавать во временных таблицах.
В связи с ограничением на индексы columnstore использование системной хранимой процедуры sp_estimate_data_compression_savings с параметром сжатия данных COLUMNSTORE или COLUMNSTORE_ARCHIVE не поддерживается, если включены оптимизированные для памяти метаданные tempdb .
Эти ограничения применяются только при создании ссылок на системные представления tempdb . При необходимости вы сможете создать временную таблицу в той же транзакции, где обращаетесь к таблице, оптимизированной для памяти, в пользовательской базе данных.
14 Answers 14
Contains a row per file of a database as stored in the master database. This is a single, system-wide view.
@M4N If you just want to get the database name you can also call DB_NAME(database_id) instead joining with sys.databases
If you want get location of Database you can check Get All DBs Location.
you can use sys.master_files for get location of db and sys.database to get db name
I am using script to get empty space in each file:
Size is expressed in KB.
What is Use [?] supposed to do? It gives an error that it cannot find that stored procedure. Removing it shows only multiple times the system databases.
I've created this query:
'By '+ CASE WHEN mf.is_percent_growth = 1 THEN CAST(mf.growth AS VARCHAR(10))+'%' ELSE CONVERT(VARCHAR(30), CAST((mf.growth * 8) / 1024.0 AS DECIMAL(18, 1))) +' MB' END AS [Autogrowth], CASE WHEN mf.max_size = 0 THEN 'No growth is allowed' ELSE CASE WHEN mf.max_size = -1 THEN 'Unlimited' ELSE CAST((CAST(mf.max_size AS BIGINT) * 8 ) / 1024 AS VARCHAR(30))+' MB' END END as [MaximumSize]
Executing following sql (It will only work when you don't have multiple mdf/ldf files for same database)
will return this output
and rest of the databases
If your TempDB's have multiple MDF's (like mine have), this script will fail. However, you can use
at the end and it will return all databases except system databases.
I realise it's a small dataset, but that's no reason to use correlated subqueries. They might be fine on Oracle but they are serious performance killers on SQL Server, because they cause row-by-row processing. Your script will query the sys.master_files table twice for every row in the sys.databases table.
In addition to Davos' comment. This script will also fail with errors if you have multiple datafiles or logfiles for any database. (e.g. Subquery returned more than 1 value.)
@Davos I know what you are saying but it depends how frequently you are executing this query otherwise it is pre-optimization which probably you don't need.
I generally agree that early optimization is bad, but what I am saying is that correlated subqueries are just a bad pattern that should never be used in the first place. There's always exceptions to 'never' rules, but this is not one of those cases. I know it's minor and it might really not matter here, but that's not the point. This is a public forum that newbies use to learn good practice, so you need to provide role model code.
The query will error if multiple data files are used in one of the databases. Here's a version of your query using joins instead. Cheers! SELECT db.name AS DBName, db.database_id, mfr.Physical_Name AS DataFile, mfl.Physical_Name AS LogFile FROM sys.databases db JOIN sys.master_files mfr ON db.database_id=mfr.database_id AND mfr.type_desc='ROWS' JOIN sys.master_files mfl ON db.database_id=mfl.database_id AND mfl.type_desc='LOG' ORDER BY db.database_id
You can also try this.
Using this script you can show all the databases name and files used (with exception of system dbs).
Below script can be used to get following information: 1. DB Size Info 2. FileSpaceInfo 3. AutoGrowth 4. Recovery Model 5. Log_reuse_backup information
This script lists most of what you are looking for and can hopefully be modified to you needs. Note that it is creating a permanent table in there - you might want to change it. It is a subset from a larger script that also summarises backup and job information on various servers.
An excellent script that can be copy/pasted and used as-is. One question though, I see that for LogVolumeFreeSpaceMB it always shows the same amount for all files, in my case 44756. Is it possible to get the actual free space? Or is that the number of max free space on the drive where the LDF is located?
Hi Abel - It has been a while since I wrote and I am not really sure of your issue. There is an alternative to XP_FIXEDDRIVES called sys.dm_os_volume_stats so you can try using that. If the problem is that the file sizes of the database are not changing over time then that is normal as SQL Server allocates a big blob of empty disk space for its use and then doesn't expand that file until it has been filled up. It will expand a file in jumps with the amount determined by the file settings for that particular database
File and Filegroup Example
The following example creates a database on an instance of SQL Server. The database has a primary data file, a user-defined filegroup, and a log file. The primary data file is in the primary filegroup and the user-defined filegroup has two secondary data files. An ALTER DATABASE statement makes the user-defined filegroup the default. A table is then created specifying the user-defined filegroup. (This example uses a generic path c:\Program Files\Microsoft SQL Server\MSSQL.1 to avoid specifying a version of SQL Server.)
The following illustration summarizes the results of the previous example (except for the Filestream data).
Увеличение производительности базы данных tempdb в SQL Server
Начиная с версии SQL Server 2016 (13.x);, производительность tempdb дополнительно оптимизирована следующим образом:
- Временные таблицы и табличные переменные кэшируются. Кэширование позволяет операциям удаления и создания временных объектов выполняться очень быстро. Кэширование также снижает вероятность возникновения состязаний, связанных с метаданными и выделением страниц.
- Усовершенствован протокол кратковременных блокировок выделения страниц для снижения количества используемых кратковременных блокировок UP (обновление).
- Снижены затраты ресурсов на ведение журнала tempdb — уменьшено потребление пропускной способности подсистемы ввода-вывода файлом журнала tempdb .
- Программа установки добавляет множество файлов данных tempdb при установке нового экземпляра. Эту задачу можно выполнить с помощью нового элемента управления для ввода в пользовательском интерфейсе в разделе Настройка ядра СУБД и параметра командной строки /SQLTEMPDBFILECOUNT . По умолчанию программа установки добавляет столько файлов данных tempdb , сколько имеется логических процессоров, но их может быть не больше восьми.
- При наличии множества файлов данных tempdb автоматическое увеличение выполняется для всех файлов в одно время и в равном объеме согласно параметрам увеличения. Флаг трассировки 1117 больше не требуется.
- Для всех операций распределения в tempdb используются единообразные экстенты. Флаг трассировки 1118 больше не требуется.
- Для первичной файловой группы свойство AUTOGROW_ALL_FILES включено и не может быть изменено.
Дополнительные сведения об улучшениях производительности в tempdb см. в статье блога TEMPDB — Files and Trace Flags and Updates, Oh My! (TEMPDB — файлы, флаги трассировки и обновления).
Logical and Physical File Names
SQL Server files have two file name types:
logical_file_name: The logical_file_name is the name used to refer to the physical file in all Transact-SQL statements. The logical file name must comply with the rules for SQL Server identifiers and must be unique among logical file names in the database.
os_file_name: The os_file_name is the name of the physical file including the directory path. It must follow the rules for the operating system file names.
For more information on the NAME and FILENAME argument, see ALTER DATABASE File and Filegroup Options (Transact-SQL).
SQL Server data and log files can be put on either FAT or NTFS file systems. On Windows systems, we recommend using the NTFS file system because the security aspects of NTFS.
Read/write data filegroups and log files are not supported on an NTFS compressed file system. Only read-only databases and read-only secondary filegroups are allowed to be put on an NTFS compressed file system. For space savings, it is highly recommended to use data compression instead of file system compression.
When multiple instances of SQL Server are running on a single computer, each instance receives a different default directory to hold the files for the databases created in the instance. For more information, see File Locations for Default and Named Instances of SQL Server.
Файлы моментального снимка базы данных
Вид файла, используемый для хранения копируемых во время записи данных моментального снимка базы данных, зависит от того, создается ли моментальный снимок пользователем или используется внутренними механизмами.
- Данные моментального снимка базы данных, созданного пользователем, хранятся в одном или нескольких разреженных файлах. Технология разреженных файлов является свойством файловой системы NTFS. Изначально разреженный файл не содержит данных пользователя, и место на диске под него не выделяется. Общие сведения об использовании разреженных файлов в моментальных снимках базы данных и о том, как растут моментальные снимки базы данных, см. в разделе Просмотр размера разреженного файла моментального снимка базы данных.
- Моментальные снимки базы данных могут использоваться внутренними механизмами при выполнении определенных команд DBCC. Эти команды включают DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC и DBCC CHECKFILEGROUP. Внутренним моментальным снимком базы данных используются разреженные дополнительные потоки данных исходных файлов базы данных. Подобно разреженным файлам, дополнительные потоки данных являются свойством файловой системы NTFS. Использование разреженных дополнительных потоков данных позволяет связать несколько расположений данных с одним файлом или папкой, не затрагивая при этом размер файла или статистику тома.
Filestream Filegroup
For more information on filestream filegroups, see FILESTREAM and Create a FILESTREAM-Enabled Database.
tempdb в Azure SQL
Поведение tempdb в Базе данных SQL Azure отличается от ее поведения в SQL Server, Управляемом экземпляре SQL Azure и в SQL Server на виртуальных машинах Azure.
Размер файла
Файлы SQL Server могут автоматически увеличиваться в размерах, превосходя первоначально заданные показатели. При определении файла пользователь может указывать требуемый шаг роста. Каждый раз при заполнении файла его размер увеличивается на указанный шаг роста. Если в файловой группе имеется несколько файлов, их автоматический рост начинается лишь по заполнении всех файлов.
Дополнительные сведения о страницах и их типах см. в разделе Руководство по архитектуре страниц и экстентов.
Кроме того, можно указать максимальный размер каждого файла. Если максимальный размер файла не указан, файл может продолжать увеличиваться в размерах, пока не займет все доступное место на диске. Эта функция особенно полезна в случаях, когда SQL Server используется в качестве базы данных, внедренной в приложение, где пользователь не имеет удобного доступа к системному администратору. По мере необходимости пользователь может предоставить файлам возможность увеличиваться в размерах автоматически, тем самым снимая с администратора часть забот по наблюдению за свободным пространством базы данных и по распределению дополнительного пространства вручную.
Дополнительные сведения об управлении файлами журнала транзакций см. в разделе Управление размером файла журнала транзакций.
Разрешения
Любой пользователь может создавать временные объекты в tempdb . Если не предоставлены дополнительные разрешения, пользователям доступны только принадлежащие им объекты. Можно отозвать разрешение на подключение к tempdb , чтобы запретить пользователю работать с tempdb . Но делать это не рекомендуется, так как tempdb требуется для выполнения некоторых стандартных операций.
Оптимизация производительности базы данных tempdb в SQL Server
Размер и физическое размещение базы данных tempdb может влиять на производительность системы. Например, если для базы данных tempdb установлен слишком малый размер, часть системной нагрузки может приходиться на автоувеличение tempdb до размера, требуемого для поддержки рабочей нагрузки при каждом перезапуске экземпляра SQL Server.
По возможности используйте мгновенную инициализацию файлов, чтобы повысить производительность операций увеличения файлов данных.
Заранее выделите место для всех файлов tempdb , установив для файла размер, достаточный для обеспечения обычной рабочей нагрузки в среде. Предварительное выделение позволяет избежать слишком частого расширения tempdb , способного повлиять на производительность. Следует установить автоувеличение для базы данных tempdb , чтобы увеличить место на диске для незапланированных исключений.
Файлы данных в каждой файловой группе должны иметь одинаковый размер, так как SQL Server использует алгоритм пропорционального заполнения, который повышает вероятность выделения памяти в файлах с большим объемом свободного пространства. Разделение tempdb на множество файлов данных равного размера обеспечивает эффективное выполнение использующих tempdb операций с высокой степенью параллелизма.
Установите приемлемое значение шага увеличения размера файла, чтобы оно не было слишком низким для файлов базы данных tempdb . Если увеличение размера файлов будет слишком малым по сравнению с объемом записываемых в tempdb данных, tempdb может постоянно требовать расширения. Это скажется на производительности.
Чтобы проверить текущий размер и параметры увеличения для tempdb , используйте следующий запрос:
Поместите базу данных tempdb в быструю подсистему ввода-вывода. Если имеется много непосредственно присоединенных дисков, то используйте чередование дисков. Отдельные файлы данных tempdb или их группы не обязательно должны располагаться на разных дисках или шпинделях, если только у вы не наблюдаете узкие места в подсистеме ввода-вывода.
Расположите базу данных tempdb на дисках, отличающихся от используемых пользовательскими базами данных.
Файловая группа данных, оптимизированных для памяти
Дополнительные сведения об оптимизированных для памяти файловых группах см. в разделе Оптимизированные для памяти файловые группы.
Оптимизированные для памяти метаданные tempdb
Состязание метаданных tempdb всегда было узким местом для масштабируемости многих рабочих нагрузок, выполняющихся в SQL Server. В SQL Server 2019 (15.x) появилась новая функция оптимизированных для памяти метаданных tempdb, входящая в семейство функций выполняющейся в памяти базы данных.
Она эффективно устраняет существующее узкое место и открывает новый уровень масштабируемости для рабочих нагрузок, активно использующих tempdb. В SQL Server 2019 (15.x) системные таблицы, связанные с управлением метаданными временных таблиц, можно переместить в неустойчивые таблицы без кратковременной блокировки, оптимизированные для памяти.
Сейчас функция оптимизированных для памяти метаданных tempdb недоступна для Базы данных SQL Azure и Управляемых экземпляров SQL Azure.
Просмотрите это 7-минутное видео, чтобы узнать, как и когда следует использовать метаданные tempdb, оптимизированные для памяти:
Memory Optimized Data Filegroup
For more information on memory-optimized filegroups, see Memory Optimized Filegroup.
Рекомендации
Рекомендации при работе с файлами и файловыми группами:
- Для большинства баз данных достаточно использовать один файл данных и один файл журнала транзакций.
- При использовании множества файлов данных создайте вторую файловую группу с дополнительным файлом и сделайте ее файловой группой по умолчанию. Тогда в первичном файле будут храниться только системные таблицы и объекты.
- Чтобы увеличить производительность, по возможности разнесите файлы и файловые группы по нескольким доступным дискам. Объекты, активно конкурирующие за свободное пространство, поместите в разные файловые группы.
- Используйте файловые группы для целенаправленного размещения объектов на конкретных физических дисках.
- Помещайте разные таблицы, использующиеся в одних и тех же запросах с соединениями, в разные файловые группы. Этот этап увеличит производительность, так как для поиска соединяемых данных можно будет использовать параллельный ввод-вывод.
- Часто используемые таблицы и некластеризованные индексы, относящиеся к ним, помещайте в разные файловые группы. Использование разных групп файлов увеличит производительность, так как можно будет использовать параллельный ввод и вывод, если файлы находятся на разных жестких дисках.
- Не помещайте файлы журнала транзакций на тот же физический диск, где находятся другие файлы и файловые группы.
- Если необходимо расширить том или раздел, в котором находятся файлы базы данных, с помощью таких средств, как Diskpart, следует сначала выполнить резервное копирование всех системных и пользовательских баз данных и остановить службы SQL Server. Кроме того, после успешного расширения томов дисков рекомендуется выполнить команду DBCC CHECKDB , чтобы обеспечить физическую целостность всех баз данных в томе.
Дополнительные рекомендации по управлению файлами журнала транзакций см. в разделе Управление размером файла журнала транзакций.
В этой статье описывается системная база данных tempdb . Она является глобальным ресурсом, доступным всем пользователям, которые подключены к экземпляру SQL Server, Базе данных SQL Azure или Управляемому экземпляру SQL Azure.
Правила проектирования файлов и файловых групп
Для файлов и файловых групп действуют следующие правила:
- файл или файловая группа не могут использоваться несколькими базами данных. Например, файлы sales.mdf и sales.ndf, содержащие данные и объекты базы данных sales, не могут использоваться никакой другой базой данных.
- файл может быть элементом только одной файловой группы;
- файлы журнала транзакций не могут входить ни в какие файловые группы.
Настройка и использование метаданных оптимизированной для памяти базы данных tempdb
Чтобы согласиться на применение этой новой функции, используйте следующий скрипт:
Чтобы это изменение конфигурации вступило в силу, нужно перезапустить службу.
Вы можете проверить, является ли tempdb оптимизированной для памяти, используя следующую команду T-SQL:
Если по какой-то причине не удается запустить сервер после включения оптимизированных для памяти метаданных tempdb , можно обойти эту функцию, запустив экземпляр SQL Server в минимальной конфигурации с помощью параметра запуска -f. После этого вы можете отключить функцию и перезапустить SQL Server в нормальном режиме.
Чтобы защитить сервер от потенциальных состояний нехватки памяти, можно привязать tempdb к пулу ресурсов. В этом случае вместо действий, которые обычно выполняются при привязке пула ресурсов к базе данных, следует использовать команду ALTER SERVER .
Кроме того, даже если метаданные оптимизированной для памяти базы данных tempdb уже включены, чтобы это изменение вступило в силу, требуется перезагрузка.
Rules for designing Files and Filegroups
The following rules pertain to files and filegroups:
- A file or filegroup cannot be used by more than one database. For example, file sales.mdf and sales.ndf, which contain data and objects from the sales database, can't be used by any other database.
- A file can be a member of only one filegroup.
- Transaction log files are never part of any filegroups.
Перемещение данных и файлов журналов базы данных tempdb в SQL Server
Сведения о перемещении файлов журналов и данных tempdb см. в статье Перемещение системных баз данных.
Стратегия заполнения файлов и файловых групп
В файловых группах для каждого файла используется стратегия пропорционального заполнения. При записи данных в файловую группу компонент Компонент SQL Server Database Engine записывает в каждый файл количество данных, пропорциональное свободному пространству этого файла, вместо записи всех данных в первый файл до его заполнения. Затем запись производится в следующий файл. Например, если в файле f1 свободно 100 МБ, а в файле f2 — 200 МБ, то в файл f1 записывается одна часть данных, а в файл f2 — две части, и так далее. Таким образом, оба файла будут заполнены примерно в одно и то же время, и достигается простейшее распределение данных между хранилищами.
Например, файловая группа состоит из трех файлов, для всех разрешено автоматическое увеличение. Когда свободное пространство во всех файлах группы закончится, будет расширен только первый файл. Когда заполнится первый файл и в файловую группу снова нельзя будет записывать новые данные, будет расширен второй файл. Когда заполнится второй файл и в файловую группу опять нельзя будет записывать новые данные, будет расширен третий файл. Когда заполнится третий файл и в файловую группу нельзя будет записывать новые данные, будет снова расширен первый файл и т. д.
Параметры базы данных для tempdb в SQL Server
В следующей таблице приводится список значений по умолчанию для каждого параметра базы данных tempdb , а также возможность его изменения. Чтобы просмотреть текущие настройки этих параметров, используйте представление каталога sys.databases .
Логические и физические имена файлов
Файлы SQL Server имеют два типа имен файлов.
logical_file_name: имя, используемое для ссылки на физический файл во всех инструкциях Transact-SQL. Логическое имя файла должно соответствовать правилам для идентификаторов SQL Server и быть уникальным среди логических имен файлов в соответствующей базе данных.
os_file_name: имя физического файла, включающее путь к каталогу. Оно должно соответствовать правилам для имен файлов операционной системы.
Дополнительные сведения об аргументах NAME и FILENAME см. в статье Параметры ALTER DATABASE ((Transact-SQL)) для файлов и файловых групп.
Файлы данных и файлы журналов SQL Server могут использоваться как в файловой системе FAT, так и в системе NTFS. В системах Windows рекомендуется использовать файловую систему NTFS по причинам ее большей безопасности.
Файловые группы, доступные как для чтения, так и для записи, а также файлы журналов не поддерживаются со сжатой файловой системой NTFS. В сжатую файловую систему NTFS разрешено помещать лишь доступные только для чтения базы данных и доступные только для чтения вторичные файловые группы. Для экономии места настоятельно рекомендуется использовать сжатие данных вместо сжатия файловой системы.
Если на одном компьютере запущено несколько экземпляров SQL Server, каждый экземпляр получает отдельный каталог по умолчанию для хранения файлов баз данных, созданных в этом экземпляре. Дополнительные сведения см. в разделе Расположение файлов для экземпляра по умолчанию и именованных экземпляров SQL Server.
Файловые группы
- Эта файловая группа содержит первичный файл данных и все вторичные файлы, не входящие в другие файловые группы.
- Пользовательские файловые группы могут создаваться для удобства администрирования, распределения и размещения данных.
Например, Data1.ndf , Data2.ndf и Data3.ndf могут быть созданы на трех дисках соответственно и отнесены к файловой группе fgroup1 . В этом случае можно создать таблицу на основе файловой группы fgroup1 . Запросы данных из таблицы будут распределены по трем дискам, и это улучшит производительность. Подобного улучшения производительности можно достичь и с помощью одного файла, созданного на чередующемся наборе дискового массива RAID. Тем не менее файлы и файловые группы позволяют без труда добавлять новые файлы на новые диски.
Все файлы данных хранятся в файловых группах, перечисленных в следующей таблице.
Файловая группа | Описание |
---|---|
Первичная | Файловая группа, содержащая первичный файл. Все системные таблицы являются частью первичной файловой группы. |
Данные, оптимизированные для памяти | В основе оптимизированной для памяти файловой группы лежит файловая группа файлового потока. |
Файловый поток | |
Определяемые пользователем маршруты | Любая файловая группа, созданная пользователем при создании или изменении базы данных. |
Ограничения
С базой данных tempdb нельзя выполнять следующие операции:
- Добавление файловых групп.
- Резервное копирование и восстановление из копии.
- Изменение параметров сортировки. Параметрами сортировки по умолчанию являются параметры сортировки сервера.
- Изменение владельца базы данных. Владельцем tempdb является sa
- Создание моментального снимка базы данных.
- Удаление базы данных.
- Удаление пользователя guest из базы данных.
- Включение отслеживания измененных данных.
- Участие в зеркальном отображении базы данных.
- Удаление первичной файловой группы, первичного файла данных или файла журнала.
- Переименование базы данных или первичной файловой группы.
- Выполнение DBCC CHECKALLOC .
- Выполнение DBCC CHECKCATALOG .
- Перевод базы данных в режим OFFLINE .
- Перевод базы данных или первичной файловой группы в режим READ_ONLY .
Data File Pages
Pages in a SQL Server data file are numbered sequentially, starting with zero (0) for the first page in the file. Each file in a database has a unique file ID number. To uniquely identify a page in a database, both the file ID and the page number are required. The following example shows the page numbers in a database that has a 4-MB primary data file and a 1-MB secondary data file.
A file header page is the first page that contains information about the attributes of the file. Several of the other pages at the start of the file also contain system information, such as allocation maps. One of the system pages stored in both the primary data file and the first log file is a database boot page that contains information about the attributes of the database.
Файлы базы данных
SQL Server имеют три типа файлов.
Файл | Описание |
---|---|
Первичная | Содержит сведения, необходимые для запуска базы данных, и ссылки на другие файлы в базе данных. В каждой базе данных имеется один первичный файл данных. Для имени первичного файла данных рекомендуется расширение MDF. |
Вторичная | Необязательные определяемые пользователем файлы данных. Данные могут быть распределены на несколько дисков, в этом случае каждый файл записывается на отдельный диск. Для имени вторичного файла данных рекомендуется расширение NDF. |
Журнал транзакций | Журнал содержит информацию для восстановления базы данных. Для каждой базы данных должен существовать хотя бы один файл журнала. Для файлов журнала транзакций рекомендуется расширение LDF. |
Например, простая база данных с именем Sales включает один первичный файл, содержащий все данные и объекты, и один файл журнала, содержащий сведения журнала транзакций. Более сложная база данных с именем Orders может содержать один первичный файл и пять вторичных файлов. Данные и объекты внутри базы данных распределяются по всем шести файлам, а четыре файла журнала содержат сведения журнала транзакций.
По умолчанию и данные, и журналы транзакций помещаются на один и тот же диск и имеют один и тот же путь для обработки однодисковых систем. Для производственных сред это может быть неоптимальным решением. Рекомендуется помещать данные и файлы журнала на разные диски.
File Size
SQL Server files can grow automatically from their originally specified size. When you define a file, you can specify a specific growth increment. Every time the file is filled, it increases its size by the growth increment. If there are multiple files in a filegroup, they won't autogrow until all the files are full.
For more information about pages and page types, see Pages and Extents Architecture Guide.
Each file can also have a maximum size specified. If a maximum size isn't specified, the file can continue to grow until it has used all available space on the disk. This feature is especially useful when SQL Server is used as a database embedded in an application where the user doesn't have convenient access to a system administrator. The user can let the files autogrow as required to reduce the administrative burden of monitoring free space in the database and manually allocating additional space.
For more information on transaction log file management, see Manage the size of the transaction log file.
File and Filegroup Fill Strategy
Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. It then writes to the next file. For example, if file f1 has 100 MB free and file f2 has 200 MB free, one extent is given from file f1, two extents from file f2, and so on. In this way, both files become full at about the same time, and simple striping is achieved.
For example, a filegroup is made up of three files, all set to automatically grow. When space in all the files in the filegroup is exhausted, only the first file is expanded. When the first file is full and no more data can be written to the filegroup, the second file is expanded. When the second file is full and no more data can be written to the filegroup, the third file is expanded. If the third file becomes full and no more data can be written to the filegroup, the first file is expanded again, and so on.
Recommendations
Recommendations when working with files and filegroups:
- Most databases will work well with a single data file and a single transaction log file.
- If you use multiple data files, create a second filegroup for the additional file and make that filegroup the default filegroup. In this way, the primary file will contain only system tables and objects.
- To maximize performance, create files or filegroups on different available disks as possible. Put objects that compete heavily for space in different filegroups.
- Use filegroups to enable placement of objects on specific physical disks.
- Put different tables used in the same join queries in different filegroups. This step will improve performance, because of parallel disk I/O searching for joined data.
- Put heavily accessed tables and the nonclustered indexes that belong to those tables on different filegroups. Using different filegroups will improve performance, because of parallel I/O if the files are located on different physical disks.
- Don't put the transaction log file(s) on the same physical disk that has the other files and filegroups.
- If you need to extend a volume or partition on which database files reside using tools like Diskpart, you should back up all system and user databases and stop SQL Server services first. Also, once disk volumes are extended successfully, you should consider running DBCC CHECKDB command to ensure the physical integrity of all databases residing on the volume.
For more information on transaction log file management recommendations, see Manage the size of the transaction log file.
Is it possible to list information about the files (MDF/LDF) of all databases on an SQL Server?
I'd like to get a list showing which database is using what files on the local disk.
- exec sp_databases all databases
- select * from sys.databases shows a lot of information about each database - but unfortunately it doesn't show the files used by each database.
- select * from sys.database_files shows the mdf/ldf files of the master database - but not the other databases
Filegroups
- The filegroup contains the primary data file and any secondary files that aren't put into other filegroups.
- User-defined filegroups can be created to group data files together for administrative, data allocation, and placement purposes.
For example: Data1.ndf , Data2.ndf , and Data3.ndf , can be created on three disk drives, respectively, and assigned to the filegroup fgroup1 . A table can then be created specifically on the filegroup fgroup1 . Queries for data from the table will be spread across the three disks; it will improve performance. The same performance improvement can be accomplished by using a single file created on a RAID (redundant array of independent disks) stripe set. However, files and filegroups let you easily add new files to new disks.
All data files are stored in the filegroups listed in the following table.
Filegroup | Description |
---|---|
Primary | The filegroup that contains the primary file. All system tables are part of the primary filegroup. |
Memory Optimized Data | A memory-optimized filegroup is based on filestream filegroup |
Filestream | |
User-defined | Any filegroup that is created by the user when the user first creates or later modifies the database. |
Файловая группа по умолчанию (первичная)
Если в базе данных создаются объекты без указания файловой группы, к которой они относятся, они назначаются файловой группе по умолчанию. В любом случае только одна файловая группа создается как файловая группа по умолчанию. Файлы в файловой группе по умолчанию должны быть достаточно большими, чтобы вмещать новые объекты, не назначенные другим файловым группам.
Файловая группа PRIMARY является группой по умолчанию, если только она не была изменена инструкцией ALTER DATABASE. Системные объекты и таблицы распределяются внутри первичной файловой группы, а не новой файловой группой по умолчанию.
Физические свойства tempdb в SQL Server
В следующей таблице приводятся исходные значения конфигурации для файлов данных и журналов tempdb в SQL Server. Значения основаны на значениях по умолчанию для базы данных model . Размеры этих файлов могут немного отличаться в зависимости от выпуска SQL Server.
Количество вторичных файлов данных зависит от числа логических процессоров на компьютере. Как правило, если число логических процессоров меньше или равно восьми, используйте равное ему число файлов данных. Если число логических процессоров больше восьми, используйте восемь файлов данных. Если состязание сохраняется, увеличьте число файлов данных на значение, кратное четырем, пока состязание не снизится до приемлемого уровня, или внесите изменения в рабочую нагрузку или код.
Количество файлов данных по умолчанию основано на общих рекомендациях, приведенных в статье KB 2154845.
Чтобы проверить текущий размер и параметры увеличения для tempdb , выполнить запрос к представлению tempdb.sys.database_files .
Default (Primary) Filegroup
When objects are created in the database without specifying which filegroup they belong to, they are assigned to the default filegroup. At any time, exactly one filegroup is designated as the default filegroup. The files in the default filegroup must be large enough to hold any new objects not allocated to other filegroups.
The PRIMARY filegroup is the default filegroup unless it is changed by using the ALTER DATABASE statement. Allocation for the system objects and tables remains within the PRIMARY filegroup, not the new default filegroup.
Мониторинг использования tempdb
Нехватка места на диске для tempdb может привести к значительным сбоям рабочей среды SQL Server, а также помешать работающим приложениям завершить операции. Для отслеживания места на диске, используемого в файлах tempdb , можно применять динамическое административное представление sys.dm_db_file_space_usage:
Для мониторинга действий выделения и освобождения страниц в tempdb на уровне сеансов или задач можно использовать динамические административные представления sys.dm_db_session_space_usage и sys.dm_db_task_space_usage. Эти представления позволяют выявлять большие запросы, временные таблицы или табличные переменные, которые используют много места на диске для tempdb . Кроме того, вы можете использовать несколько счетчиков для мониторинга свободного места в базе данных tempdb и ресурсов, использующих tempdb .
At a minimum, every SQL Server database has two operating system files: a data file and a log file. Data files contain data and objects such as tables, indexes, stored procedures, and views. Log files contain the information that is required to recover all transactions in the database. Data files can be grouped together in filegroups for allocation and administration purposes.
Пример файлов и файловых групп
В следующем примере создается база данных на основе экземпляра SQL Server. База данных содержит первичный файл данных, пользовательскую файловую группу и файл журнала. Первичный файл данных входит в состав первичной файловой группы, а пользовательская файловая группа состоит из двух вторичных файлов данных. Инструкция ALTER DATABASE придает пользовательской файловой группе статус файловой группы по умолчанию. Затем создается таблица, определяющая пользовательскую файловую группу. (В этом примере используется универсальный путь к c:\Program Files\Microsoft SQL Server\MSSQL.1 , чтобы не указывать версию SQL Server.)
Данная иллюстрация обобщает все вышесказанное (кроме данных файлового потока).
Обзор
Системная база данных tempdb — это глобальный ресурс, содержащий следующее:
Временные пользовательские объекты, созданные явно. К ним относятся глобальные или локальные временные таблицы и индексы, временные хранимые процедуры, табличные переменные, возвращаемые функциями с табличными значениями таблицы и курсоры.
Внутренние объекты, создаваемые ядром СУБД. К ним относятся следующие:
- Рабочие таблицы, хранящие промежуточные результаты буферов, курсоры, сортировки и временное хранилище больших объектов (LOB).
- рабочие файлы для операций хэш-соединения или статистических хэш-выражений;
- промежуточные результаты сортировки для таких операций, как создание или перестроение индексов (если указать SORT_IN_TEMPDB ), либо определенных запросов GROUP BY , ORDER BY или UNION .
Хранилища версий. Это коллекции страниц данных со строками данных, которые поддерживают функции управления версиями строк. Существует два типа хранилищ: общее хранилище версий и хранилище версий для построения индекса в подключенном режиме. Хранилища версий содержат следующее:
- версии строк, создаваемые транзакциями изменения данных в базе данных, которая использует READ COMMITTED через транзакции изоляции моментальных снимков и транзакции изоляции управления версиями строк;
- версии строк, создаваемые транзакциями изменения данных для таких функций, как операции с индексами в подключенном режиме, функции MARS (множественный активный результирующий набор) и триггеры AFTER .
Операции в tempdb в минимальном объеме записываются в журнал, что позволяет откатывать транзакции. tempdb создается заново при каждом запуске SQL Server, чтобы система всегда запускалась с чистой копией базы данных. Временные таблицы и хранимые процедуры удаляются автоматически при отключении, и при выключении системы нет активных соединений.
tempdb не требует сохранения каких-либо данных между сеансами SQL Server. Операции резервного копирования и восстановления для tempdb недопустимы.
Файловая группа файлового потока
Дополнительные сведения о файловых группах файлового потока см. в статьях FILESTREAM и Создание базы данных с поддержкой FILESTREAM.
tempdb в Базе данных SQL
Отдельные базы данных и базы данных в пуле в службе База данных SQL Azure поддерживают глобальные временные таблицы и глобальные временные хранимые процедуры, которые хранятся в tempdb и имеют область действия на уровне базы данных. Глобальные временные таблицы и глобальные временные хранимые процедуры являются общими для всех сеансов пользователей в рамках одной базы данных. Сеансы пользователей, связанные с другими базами данных, не имеют доступа к глобальным временным таблицам. Дополнительные сведения см. в разделе Глобальные временные таблицы (база данных SQL Azure) в области базы данных.
Для отдельной базы данных или баз данных в пуле в службе "База данных SQL Azure" из всех системных баз данных доступны только база данных master и tempdb . Дополнительные сведения см. в статье Что собой представляет логический сервер в Azure.
Дополнительные сведения о размерах tempdb в Базе данных SQL Azure см. в следующих статьях:
- модель приобретения на основе виртуальных ядер: отдельные базы данных, базы данных в пуле;
- модель приобретения на основе DTU: отдельные базы данных, базы данных в пуле.
Database Files
SQL Server databases have three types of files, as shown in the following table.
File | Description |
---|---|
Primary | Contains startup information for the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension for primary data files is .mdf. |
Secondary | Optional user-defined data files. Data can be spread across multiple disks by putting each file on a different disk drive. The recommended file name extension for secondary data files is .ndf. |
Transaction Log | The log holds information used to recover the database. There must be at least one log file for each database. The recommended file name extension for transaction logs is .ldf. |
For example, a simple database named Sales has one primary file that contains all data and objects and a log file that contains the transaction log information. A more complex database named Orders can be created that includes one primary file and five secondary files. The data and objects within the database spread across all six files, and the four log files contain the transaction log information.
By default, the data and transaction logs are put on the same drive and path to handle single-disk systems. This choice may not be optimal for production environments. We recommend that you put data and log files on separate disks.
tempdb в Управляемом экземпляре SQL
Управляемый экземпляр SQL Azure поддерживает временные объекты так же, как и SQL Server, где все глобальные временные таблицы и глобальные временные хранимые процедуры доступны всем пользовательским сеансам в пределах одного управляемого экземпляра. Аналогично доступны все системные базы данных.
Дополнительные сведения о размерах tempdb в Управляемом экземпляре SQL Azure см. в статье Ограничения ресурсов.
Читайте также: