Лабораторна робота № 2
Адміністрування БД
Мета роботи: навчитися досліджувати БД за допомогою T-SQL
Програмне забезпечення: OC Windows 7.0 та вище, SQL Server 2012 та вище.
Хід роботи
На прикладі власного серверу та бази даних:
Дослідити сервер.
Дослідити базу даних.
Дослідити таблиці.
Дослідити відображення.
Дослідити процедури, що зберігаються.
Дослідити функції.
Дослідити тригери.
Дослідити check – обмеження.
Дослідити модель даних.
Зразок виконання роботи
Досліджуємо сервер
Почнемо з запитів, які надають інформацію про ваші сервера.
-- Імена сервера і примірника
Select @@SERVERNAME as [Server\Instance];
-- версія SQL Server
Select @@VERSION as SQLServerVersion;
-- примірник SQL Server
Select @@ServiceName AS ServiceInstance;
-- Поточна БД (БД, в контексті якої виконується запит)
Select DB_NAME() AS CurrentDB_Name;
Як довго ваш SQL Server працює після останнього перезапуску? Пам'ятайте, що системна база даних tempdb заново створюється при кожному перезапуску SQL Server. Ось один з методів визначення часу останнього перезапуску сервера.
SELECT @@Servername AS ServerName ,
create_date AS ServerStarted ,
DATEDIFF(s, create_date, GETDATE()) / 86400.0 AS DaysRunning ,
DATEDIFF(s, create_date, GETDATE()) AS SecondsRunnig
FROM sys.databases
WHERE name = 'tempdb';
Пов'язані сервера - це з'єднання, що дозволяють SQL Serverу звертатися до інших серверів з даними. Розподілені запити можуть бути запущені на різних пов'язаних серверах. Корисно знати - чи є ваш сервер баз даних ізольованим від інших, або він пов'язаний з іншими серверами.
EXEC sp_helpserver;
--OR
EXEC sp_linkedservers;
--OR
SELECT @@SERVERNAME AS Server ,
Server_Id AS LinkedServerID ,
name AS LinkedServer ,
Product ,
Provider ,
Data_Source ,
Modify_Date
FROM sys.servers
ORDER BY name;
Список всіх баз даних. По-перше, отримаємо список всіх баз даних на сервері. Пам'ятайте, що на будь-якому сервері є чотири або п'ять системних баз даних (master, model, msdb, tempdb і distribution, якщо ви користуєтеся реплікацією). Є кілька шляхів для отримання списку всіх БД на T-SQL і нижче ви побачите деякі з них. Кожен метод повертає схожий результат, але з деякими відмінностями.
EXEC sp_helpdb;
--OR
EXEC sp_Databases;
--OR
SELECT @@SERVERNAME AS Server ,
name AS DBName ,
recovery_model_Desc AS RecoveryModel ,
Compatibility_level AS CompatiblityLevel ,
create_date ,
state_desc
FROM sys.databases
ORDER BY Name;
--OR
SELECT @@SERVERNAME AS Server ,
d.name AS DBName ,
create_date ,
compatibility_level ,
m.physical_name AS FileName
FROM sys.databases d
JOIN sys.master_files m ON d.database_id = m.database_id
WHERE m.[type] = 0 -- data files only
ORDER BY d.name;
Вивчаємо бази даних
Як ми можемо зібрати інформацію про об'єкти у всіх ваших БД, використовуючи різні відображення каталогу і dmv. Більшість із запитів, поданих в цьому розділі, працюють «всередині» лише однієї БД, тому не забувайте вибирати потрібну БД в SSMS або за допомогою команди use database. Також пам'ятайте, що ви завжди можете подивитися в контексті якої БД буде виконаний запит, за допомогою select db_name (). Системна таблиця sys.objects одна з ключових для збору інформації про об'єкти, що становлять вашу модель даних.
USE MyDatabase;
SELECT *
FROM sys.objects
WHERE type = 'U';
Спробуйте підставити інші значення type в WHERE.
Розташування файлів баз даних. Фізичне розташування обраної БД, включаючи основний файл даних (mdf), і файл журналу транзакцій (ldf), можуть бути отримані за допомогою цих запитів.
EXEC sp_Helpfile;
--OR
SELECT @@Servername AS Server ,
DB_NAME() AS DB_Name ,
File_id ,
Type_desc ,
Name ,
LEFT(Physical_Name, 1) AS Drive ,
Physical_Name ,
RIGHT(physical_name, 3) AS Ext ,
Size ,
Growth
FROM sys.database_files
ORDER BY File_id;
Досліджуємо таблиці.
Object Explorer в SSMS показує повний список таблиць в обраній БД, але частину інформації за допомогою GUI отримати складніше, ніж за допомогою скриптів. Стандарт ANSI передбачає звернення до відображення INFORMATION_SCHEMA, але вони не нададуть інформацію про об'єкти, які не є частиною стандарту (такі як тригери, extended procedures і т.д.), тому краще використовувати відображення каталогу SQL Server.
EXEC sp_tables; -- Пам'ятайте, що цей метод поверне і таблиці, і відображення
--OR
SELECT @@Servername AS ServerName ,
TABLE_CATALOG ,
TABLE_SCHEMA ,
TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME ;
--OR
SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
o.name AS 'TableName' ,
o.[Type] ,
o.create_date
FROM sys.objects o
WHERE o.Type = 'U' -- User table
ORDER BY o.name;
--OR
SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
t.Name AS TableName,
t.[Type],
t.create_date
FROM sys.tables t
ORDER BY t.Name;
Кількість записів в таблиці. Якщо ви нічого не знаєте про таблиці, то все таблиці однаково важливі. Чим більше ви дізнаєтеся про таблиці, тим більше ви їх поділяєте на умовно важливіші і умовно менш важливі. В цілому, таблиці з величезною кількістю записів частіше роблять серйозний вплив на продуктивність. У SSMS ми можемо натиснути правою кнопкою миші на будь-яку таблицю, відкрити властивості на вкладці Storage і побачити кількість записів в таблиці (рис.1)
Рис.1. Кількість записів у таблиці
Набагато зручніше використовувати T-SQL для генерування скрипта. Скрипт, наведений нижче, згенерує набір інструкцій T-SQL для отримання кількості рядків в кожній таблиці поточної бази даних. Просто виконайте його, скопіюйте результат в нове вікно і запустіть.
SELECT 'Select ''' + DB_NAME() + '.' + SCHEMA_NAME(SCHEMA_ID) + '.'
+ LEFT(o.name, 128) + ''' as DBName, count(*) as Count From ' + SCHEMA_NAME(SCHEMA_ID) + '.' + o.name
+ ';' AS ' Script generator to get counts for all tables'
FROM sys.objects o
WHERE o.[type] = 'U'
ORDER BY o.name;
Результат виконання скрипта наведено на рисунку 2.
Рис.2. Кількість записів у таблицях поточної бази даних
Розбираємося з активністю в таблиці.
При роботах по оптимізації продуктивності, дуже важливо знати які таблиці активно читаються, а в які йде активний запис. Раніше ми дізналися скільки записів в наших таблицях, зараз подивимося як часто в них пишуть і читають. Пам'ятайте, що ця інформація з dmv, очищається при кожному перезапуску SQL Server. Чим довше сервер працює, тим надійніша статистика. Я відчуваю себе набагато впевненіше зі статистикою, зібраною за 30 днів, ніж зі статистикою, зібраною за тиждень.
SELECT @@ServerName AS ServerName ,
DB_NAME() AS DBName ,
OBJECT_NAME(ddius.object_id) AS TableName ,
SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups)
AS Reads ,
SUM(ddius.user_updates) AS Writes ,
SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups
+ ddius.user_updates) AS [Reads&Writes] ,
( SELECT DATEDIFF(s, create_date, GETDATE()) / 86400.0
FROM master.sys.databases
WHERE name = 'tempdb'
) AS SampleDays ,
( SELECT DATEDIFF(s, create_date, GETDATE()) AS SecoundsRunnig
FROM master.sys.databases
WHERE name = 'tempdb'
) AS SampleSeconds
FROM sys.dm_db_index_usage_stats ddius
INNER JOIN sys.indexes i ON ddius.object_id = i.object_id
AND i.index_id = ddius.index_id
WHERE OBJECTPROPERTY(ddius.object_id, 'IsUserTable') = 1
AND ddius.database_id = DB_ID()
GROUP BY OBJECT_NAME(ddius.object_id)
ORDER BY [Reads&Writes] DESC;
Набагато більш просунута версія цього запиту представлена курсором, який збирає інформацію по всіх таблицях всіх баз даних на сервері. Взагалі, я не фанат курсорів через їхню невисоку продуктивність, але переміщення по різних базах даних - це відмінне застосування для них.
DECLARE DBNameCursor CURSOR
FOR
SELECT Name
FROM sys.databases
WHERE Name NOT IN ( 'master', 'model', 'msdb', 'tempdb',
'distribution' )
ORDER BY Name;
DECLARE @DBName NVARCHAR(128)
DECLARE @cmd VARCHAR(4000)
IF OBJECT_ID(N'tempdb..TempResults') IS NOT NULL
BEGIN
DROP TABLE tempdb..TempResults
END
CREATE TABLE tempdb..TempResults
(
ServerName NVARCHAR(128) ,
DBName NVARCHAR(128) ,
TableName NVARCHAR(128) ,
Reads INT ,
Writes INT ,
ReadsWrites INT ,
SampleDays DECIMAL(18, 8) ,
SampleSeconds INT
)
OPEN DBNameCursor
FETCH NEXT FROM DBNameCursor INTO @DBName
WHILE @@fetch_status = 0
BEGIN
SELECT @cmd = 'Use ' + @DBName + '; '
SELECT @cmd = @cmd + ' Insert Into tempdb..TempResults
SELECT @@ServerName AS ServerName,
DB_NAME() AS DBName,
object_name(ddius.object_id) AS TableName ,
SUM(ddius.user_seeks
+ ddius.user_scans
+ ddius.user_lookups) AS Reads,
SUM(ddius.user_updates) as Writes,
SUM(ddius.user_seeks
+ ddius.user_scans
+ ddius.user_lookups
+ ddius.user_updates) as ReadsWrites,
(SELECT datediff(s,create_date, GETDATE()) / 86400.0
FROM sys.databases WHERE name = ''tempdb'') AS SampleDays,
(SELECT datediff(s,create_date, GETDATE())
FROM sys.databases WHERE name = ''tempdb'') as SampleSeconds
FROM sys.dm_db_index_usage_stats ddius
INNER JOIN sys.indexes i
ON ddius.object_id = i.object_id
AND i.index_id = ddius.index_id
WHERE objectproperty(ddius.object_id,''IsUserTable'') = 1 --True
AND ddius.database_id = db_id()
GROUP BY object_name(ddius.object_id)
ORDER BY ReadsWrites DESC;'
EXECUTE (@cmd)
FETCH NEXT FROM DBNameCursor INTO @DBName
END
CLOSE DBNameCursor
DEALLOCATE DBNameCursor
SELECT *
FROM tempdb..TempResults
ORDER BY DBName ,
TableName;
Досліджуємо відображення.
Відображення - це, умовно кажучи, запити, що зберігаються в БД. Ви можете думати про них, як про віртуальні таблиці. Дані не зберігаються в відображеннях, але в наших запитах ми посилаємося на них точно так само, як і на таблиці.
У SQL Server, в деяких випадках, ми можемо оновлювати дані з використанням відображень. Щоб отримати відображення «тільки для читання», можна використовувати SELECT DISTINCT при його створенні. Дані «через» відображення можна змінювати тільки в тому випадку, якщо кожному рядку відображення відповідає тільки один рядок в «базовій» таблиці. Будь-яке відображення, що не відповідає цьому критерію, тобто побудоване на кількох таблицях, або з використанням угруповань, агрегатних функцій і обчислень, буде доступно тільки для читання.
SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
o.name AS ViewName ,
o.[Type] ,
o.create_date
FROM sys.objects o
WHERE o.[Type] = 'V' -- View
ORDER BY o.NAME
--OR
SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
Name AS ViewName ,
create_date
FROM sys.Views
ORDER BY Name
--OR
SELECT @@Servername AS ServerName ,
TABLE_CATALOG ,
TABLE_SCHEMA ,
TABLE_NAME ,
TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
ORDER BY TABLE_NAME
--OR
-- CREATE VIEW Code
SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
o.name AS 'ViewName' ,
o.Type ,
o.create_date ,
sm.[DEFINITION] AS 'View script'
FROM sys.objects o
INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE o.Type = 'V' -- View
ORDER BY o.NAME;
Дослідити процедури, що зберігаються.
Для цього можемо використовувати відображення каталогу, щоб визначити які ЗП створені, які дії вони виконують і над якими таблицями.
SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
o.name AS StoredProcedureName ,
o.[Type] ,
o.create_date
FROM sys.objects o
WHERE o.[Type] = 'P' -- Stored Procedures
ORDER BY o.name
--OR
-- Додаткова інформація про ЗП
SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
o.name AS 'ViewName' ,
o.[type] ,
o.Create_date ,
sm.[definition] AS 'Stored Procedure script'
FROM sys.objects o
INNER JOIN sys.sql_modules sm ON o.object_id = sm.object_id
WHERE o.[type] = 'P' -- Stored Procedures
-- AND sm.[definition] LIKE '%insert%'
-- AND sm.[definition] LIKE '%update%'
-- AND sm.[definition] LIKE '%delete%'
-- AND sm.[definition] LIKE '%tablename%'
ORDER BY o.name;
Додавши просту умову в WHERE ми можемо отримати інформацію тільки про ті процедури, які, наприклад, виконують операції INSERT.
WHERE o.[type] = 'P' -- Stored Procedures
AND sm.definition LIKE '%insert%'
ORDER BY o.name
Дослідити функції.
Функції зберігаються в SQL Server, приймають будь-які параметри і виконують певні дії, або обчислення, після чого повертають результат.
SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
o.name AS 'Functions' ,
o.[Type] ,
o.create_date
FROM sys.objects o
WHERE o.Type = 'FN' -- Function
ORDER BY o.NAME;
--OR
-- Додаткова інформація про функції
SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
o.name AS 'FunctionName' ,
o.[type] ,
o.create_date ,
sm.[DEFINITION] AS 'Function script'
FROM sys.objects o
INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE o.[Type] = 'FN' -- Function
ORDER BY o.NAME;
Дослідити тригери.
Тригер - це щось на зразок збереженої процедури, яка виконується у відповідь на певні дії з тієї таблицею, до якої цей тригер належить. Наприклад, ми можемо створити INSERT, UPDATE і DELETE тригери.
SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
parent.name AS TableName ,
o.name AS TriggerName ,
o.[Type] ,
o.create_date
FROM sys.objects o
INNER JOIN sys.objects parent ON o.parent_object_id = parent.object_id
WHERE o.Type = 'TR' -- Triggers
ORDER BY parent.name ,
o.NAME
--OR
SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
Parent_id ,
name AS TriggerName ,
create_date
FROM sys.triggers
WHERE parent_class = 1
ORDER BY name;
--OR
-- Додаткова інформація по тригерах
SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
OBJECT_NAME(Parent_object_id) AS TableName ,
o.name AS 'TriggerName' ,
o.Type ,
o.create_date ,
sm.[DEFINITION] AS 'Trigger script'
FROM sys.objects o
INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE o.Type = 'TR' -- Triggers
ORDER BY o.NAME;
CHECK-обмеження.
CHECK-обмеження - це непоганий засіб для реалізації бізнес-логіки в базі даних. Наприклад, деякі поля повинні бути позитивними, або негативними, або дата в одному стовпці повинна бути більше дати в іншому.
SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
parent.name AS 'TableName' ,
o.name AS 'Constraints' ,
o.[Type] ,
o.create_date
FROM sys.objects o
INNER JOIN sys.objects parent
ON o.parent_object_id = parent.object_id
WHERE o.Type = 'C' -- Check Constraints
ORDER BY parent.name ,
o.name
--OR
--CHECK constriant definitions
SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName ,
OBJECT_NAME(parent_object_id) AS TableName ,
parent_column_id AS Column_NBR ,
Name AS CheckConstraintName ,
type ,
type_desc ,
create_date ,
OBJECT_DEFINITION(object_id) AS CheckConstraintDefinition
FROM sys.Check_constraints
ORDER BY TableName ,
SchemaName ,
Column_NBR
Модель даних.
Наступний скрипт описує таблиці і стовпчики з усієї бази даних. Результат цього запиту, можна скопіювати в Excel, де можна налаштувати фільтри і сортування і добре розібратися з типами даних, що використовуються в БД. Так само, зверніть увагу на стовпці з однаковими іменами, але різними типами даних.
SELECT @@Servername AS Server ,
DB_NAME() AS DBName ,
isc.Table_Name AS TableName ,
isc.Table_Schema AS SchemaName ,
Ordinal_Position AS Ord ,
Column_Name ,
Data_Type ,
Numeric_Precision AS Prec ,
Numeric_Scale AS Scale ,
Character_Maximum_Length AS LEN , -- -1 means MAX like Varchar(MAX)
Is_Nullable ,
Column_Default ,
Table_Type
FROM INFORMATION_SCHEMA.COLUMNS isc
INNER JOIN information_schema.tables ist
ON isc.table_name = ist.table_name
-- WHERE Table_Type = 'BASE TABLE' -- 'Base Table' or 'View'
ORDER BY DBName ,
TableName ,
SchemaName ,
Ordinal_position;
-- Назви стовпців та кількість повторів
-- Використовується для пошуку одноіменних стовпців з різними типами даних
SELECT @@Servername AS Server ,
DB_NAME() AS DBName ,
Column_Name ,
Data_Type ,
Numeric_Precision AS Prec ,
Numeric_Scale AS Scale ,
Character_Maximum_Length ,
COUNT(*) AS Count
FROM information_schema.columns isc
INNER JOIN information_schema.tables ist
ON isc.table_name = ist.table_name
WHERE Table_type = 'BASE TABLE'
GROUP BY Column_Name ,
Data_Type ,
Numeric_Precision ,
Numeric_Scale ,
Character_Maximum_Length;
--Інформація по типам даних, що використовуються
SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
Data_Type ,
Numeric_Precision AS Prec ,
Numeric_Scale AS Scale ,
Character_Maximum_Length AS [Length] ,
COUNT(*) AS COUNT
FROM information_schema.columns isc
INNER JOIN information_schema.tables ist
ON isc.table_name = ist.table_name
WHERE Table_type = 'BASE TABLE'
GROUP BY Data_Type ,
Numeric_Precision ,
Numeric_Scale ,
Character_Maximum_Length
ORDER BY Data_Type ,
Numeric_Precision ,
Numeric_Scale ,
Character_Maximum_Length
-- Пам'ятайте, що індекси по цих таблиць не можуть бути перебудовані в режимі "online"
SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
isc.Table_Name ,
Ordinal_Position AS Ord ,
Column_Name ,
Data_Type AS BLOB_Data_Type ,
Numeric_Precision AS Prec ,
Numeric_Scale AS Scale ,
Character_Maximum_Length AS [Length]
FROM information_schema.columns isc
INNER JOIN information_schema.tables ist
ON isc.table_name = ist.table_name
WHERE Table_type = 'BASE TABLE'
AND ( Data_Type IN ( 'text', 'ntext', 'image', 'XML' )
OR ( Data_Type IN ( 'varchar', 'nvarchar', 'varbinary' )
AND Character_Maximum_Length = -1
)
) -- varchar(max), nvarchar(max), varbinary(max)
ORDER BY isc.Table_Name ,
Ordinal_position;
Обчислювані стовпці - це стовпці, значення в яких обчислюються на підставі, як правило, значень в інших стовпцях таблиці.
SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
OBJECT_SCHEMA_NAME(object_id) AS SchemaName ,
OBJECT_NAME(object_id) AS Tablename ,
Column_id ,
Name AS Computed_Column ,
[Definition] ,
is_persisted
FROM sys.computed_columns
ORDER BY SchemaName ,
Tablename ,
[Definition];
--Or
-- Computed Columns
SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName,
t.Name AS TableName ,
c.Column_ID AS Ord ,
c.Name AS Computed_Column
FROM sys.Tables t
INNER JOIN sys.Columns c ON t.object_id = c.object_id
WHERE is_computed = 1
ORDER BY t.Name ,
SchemaName ,
c.Column_ID
Вимоги до звітності:
Звіт містить скріпти самостійно створених запитів до власної БД та сервера, а також результати їх виконання.
Файл із скриптами та файл із скриптом бази даних.