Архив

Archive for the ‘SQL Server’ Category

SQL Server: Наблюдение за дисковым пространством

В этой заметке представлен способ наблюдения за дисковым пространством на сервере с SQL Server для бедных, для случая если у вас нет каких-либо других централизованных систем мониторинга за инфраструктурой.

T-SQL скрипт использует утилиту fsutil.exe операционной системы, получает от неё данные и в случае если свободное пространство хотя бы на одном диске менее 20%, то выполняет оповещение по email (естественно компонент DatabaseMail на SQL Server должен быть уже настроен и работоспособен).

Скрипт можно выполнять как вручную, так и настроив job в SQL Agent, результаты выполнения будут такими:

— в окне SQL Server Management Studio
DiskSpaceStatus1

— в Outlook
DiskSpaceStatus2

Настройка безопасности:
Для выполнения утилиты в командной строке Windows серверу SQL Server требуются права администратора.
Job с данным скриптом выполняется в контексте учётной записи Windows, из под которой запущен SQL Agent, поэтому у соответствующей учетной записи должны быть соответствующие права.

Скачать T-SQL скрипт: FreeDiskSpaceStatus_v03042013_forblog.sql

Скрипт протестирован на:
— SQL Server 2008 R2, 2012
— ОС Windows Server 2008, 2008R2

Дополнительные материалы:
Объём дисков на T-SQL
Monitoring SQL Server Disk Space

Реклама
Рубрики:SQL Server

Index Defrag Script

IC322Замечательный на мой взгляд скрипт для решения задачи обслуживания индексов баз данных MS SQL Server® опубликовал Michelle Ufford в своем блогеIndex Defrag Script, v4.0.
Скрипт достаточно интеллектуален, автоматически определяет необходимость перестроения или дефрагментации индексов, имеет множество параметров и может быть достаточно тонко настроен для дефрагментации одного или более индексов в одной или более баз данных.

Скачать скрипт.

Применимо: Microsoft SQL Server 2005 SP2 и выше.

Рубрики:SQL Server

Электронная книга Introducing Microsoft SQL Server 2008

Доступна для свободного скачивания электронная книгаms_sql_srv_cvr Introducing Microsoft SQL Server 2008

by Peter DeBetta, Greg Low, and Mark Whitehorn
ISBN: 9780735625587

Learn about major new features in SQL Server 2008 including security, administration, and performance.

Introducing Microsoft SQL Server 2008:
Chapter 1: Security and Administration
Chapter 2: Performance
Chapter 3: Type System
Chapter 4: Programmability
Chapter 5: Storage
Chapter 6: Enhancements for High Availability
Chapter 7: Business Intelligence Enhancements

Скачать

Рубрики:SQL Server

Список версий SQL Server от SQL Server 6.5 до наших дней

Рубрики:SQL Server

Снимок учетных записей в SQL Server

SQL%20Server%20generic%20brand%20Grid%20h Как заскриптовать все логины на SQL Server, их серверные роли + пользователей с правами в базе данных?

SQL Server MVP Алексей Князев попробовал решить эту задачу и его заявка на победу 🙂 тут — Снимок УЗ в SQL Server , в его блоге T-SQL.RU.

За основу взят скрипт от microsoft (http://support.microsoft.com/kb/918992/ru), плюс немного интеллектуального труда и вот цитата T-SQL кодом из его блога:
———————————————
USE master

GO

IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL

  DROP PROCEDURE sp_hexadecimal

GO

CREATE PROCEDURE sp_hexadecimal

    @binvalue varbinary(256),

    @hexvalue varchar (514) OUTPUT

AS

DECLARE @charvalue varchar (514)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = ‘0x’

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = ‘0123456789ABCDEF’

WHILE (@i <= @length)

BEGIN

  DECLARE @tempint int

  DECLARE @firstint int

  DECLARE @secondint int

  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

  SELECT @firstint = FLOOR(@tempint/16)

  SELECT @secondint = @tempint — (@firstint*16)

  SELECT @charvalue = @charvalue +

    SUBSTRING(@hexstring, @firstint+1, 1) +

    SUBSTRING(@hexstring, @secondint+1, 1)

  SELECT @i = @i + 1

END

 

SELECT @hexvalue = @charvalue

GO

 

IF OBJECT_ID (‘sp_help_revlogin_with_roles’) IS NOT NULL

  DROP PROCEDURE sp_help_revlogin_with_roles

GO

CREATE PROCEDURE sp_help_revlogin_with_roles @login_name sysname = NULL AS

DECLARE @name sysname

DECLARE @type varchar (1)

DECLARE @hasaccess int

DECLARE @denylogin int

DECLARE @is_disabled int

DECLARE @PWD_varbinary  varbinary (256)

DECLARE @PWD_string  varchar (514)

DECLARE @SID_varbinary varbinary (85)

DECLARE @SID_string varchar (514)

DECLARE @tmpstr  varchar (1024)

DECLARE @is_policy_checked varchar (3)

DECLARE @is_expiration_checked varchar (3)

 

DECLARE @defaultdb sysname

 

DECLARE @srvrolemember sysname

DECLARE @str varchar(max)

 

IF (@login_name IS NULL)

  DECLARE login_curs CURSOR FOR

 

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l

      ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name <> ‘sa’

ELSE

  DECLARE login_curs CURSOR FOR

 

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l

      ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name = @login_name

OPEN login_curs

 

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

IF (@@fetch_status = -1)

BEGIN

  PRINT ‘Имена не найдены.’

  CLOSE login_curs

  DEALLOCATE login_curs

  RETURN -1

END

SET @tmpstr = ‘/* sp_help_revlogin script ‘

PRINT @tmpstr

SET @tmpstr = ‘** Generated ‘ + CONVERT (varchar, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */’

PRINT @tmpstr

PRINT »

WHILE (@@fetch_status <> -1)

BEGIN

  IF (@@fetch_status <> -2)

  BEGIN

    PRINT »

    SET @tmpstr = ‘— Login: ‘ + @name

    PRINT @tmpstr

 

    SET @tmpstr = ‘IF EXISTS (SELECT * FROM sys.server_principals WHERE name= ‘ + QUOTENAME( @name , »») + ‘ ) DROP LOGIN  ‘ + QUOTENAME( @name ) +’;’

    PRINT @tmpstr

 

    IF (@type IN ( ‘G’, ‘U’))

    BEGIN — NT authenticated account/group

 

      SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ FROM WINDOWS WITH DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’

    END

    ELSE BEGIN — SQL Server authentication

        — obtain password and sid

            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, ‘PasswordHash’ ) AS varbinary (256) )

        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT

        EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT

 

        — obtain password policy state

        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name

        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name

 

            SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ WITH PASSWORD = ‘ + @PWD_string + ‘ HASHED, SID = ‘ + @SID_string + ‘, DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’

 

        IF ( @is_policy_checked IS NOT NULL )

        BEGIN

          SET @tmpstr = @tmpstr + ‘, CHECK_POLICY = ‘ + @is_policy_checked

        END

        IF ( @is_expiration_checked IS NOT NULL )

        BEGIN

          SET @tmpstr = @tmpstr + ‘, CHECK_EXPIRATION = ‘ + @is_expiration_checked

        END

    END

    IF (@denylogin = 1)

    BEGIN — login is denied access

      SET @tmpstr = @tmpstr + ‘; DENY CONNECT SQL TO ‘ + QUOTENAME( @name )

    END

    ELSE IF (@hasaccess = 0)

    BEGIN — login exists but does not have access

      SET @tmpstr = @tmpstr + ‘; REVOKE CONNECT SQL TO ‘ + QUOTENAME( @name )

    END

    IF (@is_disabled = 1)

    BEGIN — login is disabled

      SET @tmpstr = @tmpstr + ‘; ALTER LOGIN ‘ + QUOTENAME( @name ) + ‘ DISABLE;’

    END

    PRINT @tmpstr

  END

 

            —sp_addsrvrolemember

            DECLARE srvrolemember_curs CURSOR FOR

                             SELECT r.name FROM sys.server_role_members rm

                             INNER JOIN

                             sys.server_principals r ON rm.role_principal_id=r.principal_id

                             INNER JOIN

                             sys.server_principals p ON rm.member_principal_id=p.principal_id

                             WHERE p.name=@name

            OPEN srvrolemember_curs

            FETCH NEXT FROM srvrolemember_curs INTO @srvrolemember

            WHILE (@@fetch_status <> -1)

            BEGIN

            IF (@@fetch_status <> -2)

            BEGIN

            SET @tmpstr = ‘EXEC sp_addsrvrolemember ‘ + QUOTENAME( @name ) + ‘, ‘+ QUOTENAME( @srvrolemember ) + ‘;’

            PRINT @tmpstr

            END

            FETCH NEXT FROM srvrolemember_curs INTO @srvrolemember

            END

            CLOSE srvrolemember_curs

            DEALLOCATE srvrolemember_curs

 

            —CREATE USERS

            set @str=’USE ?

            IF EXISTS (SELECT * FROM sys.database_principals WHERE sid=’+

            CONVERT (VARCHAR(514), @SID_varbinary, 1)+’)

            BEGIN

            DECLARE @name sysname, @schema sysname

            SELECT @name=name, @schema=default_schema_name FROM sys.database_principals WHERE sid=’+

            CONVERT (VARCHAR(514), @SID_varbinary, 1)+

            ‘print "USE ?;

            IF EXISTS (SELECT * FROM sys.database_principals WHERE name=»"+@name+"»)

            DROP USER "+QUOTENAME(@name)+";

            CREATE USER "+QUOTENAME(@name)+" FOR LOGIN ‘+ QUOTENAME( @name ) +’ WITH DEFAULT_SCHEMA = "+QUOTENAME(@schema)+";"

 

                  DECLARE @dbrolemember sysname

                  DECLARE dbrolemember_curs CURSOR FOR

                  SELECT r.name FROM sys.database_role_members rm

                  INNER JOIN

                  sys.database_principals r ON rm.role_principal_id=r.principal_id

                  INNER JOIN

                  sys.database_principals p ON rm.member_principal_id=p.principal_id

                  WHERE p.name=@name

                  OPEN dbrolemember_curs

                  FETCH NEXT FROM dbrolemember_curs INTO @dbrolemember

                  WHILE (@@fetch_status <> -1)

                  BEGIN

                  IF (@@fetch_status <> -2)

                  BEGIN

                  PRINT "EXEC sp_addrolemember "+QUOTENAME(@dbrolemember)+", "+QUOTENAME(@name)+";"

                  END

                  FETCH NEXT FROM dbrolemember_curs INTO @dbrolemember

                  END

                  CLOSE dbrolemember_curs

                  DEALLOCATE dbrolemember_curs

            END

            ‘

            EXECUTE sp_MSforeachdb @str

 

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

   END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

GO
———————————————

Сценарий создает в базе данных master две хранимых процедуры — sp_hexadecimal и sp_help_revlogin_with_roles.
Далее вызываем процедуру и получаем скрипт-снимок состояния УЗ на сервере БД:

———————————————
EXEC
master..sp_help_revlogin_with_roles
———————————————

 
Применимо:
SQL Server 2005 и выше

Рубрики:SQL Server

MCP-Club Самара. SQL Server 2008. Планирование установки, настройка для наилучшей производительности.

j0439328 В июле проходила очередная встреча Самарского MCP-Клуба. Я читал доклад на тему “SQL Server 2008. Планирование установки, настройка для наилучшей производительности”.

Презентацию в формате PowerPoint можно скачать здесь.

Рубрики:SQL Server

SQL Server 2005, 2008: Создание недостающих индексов

speedometer-thumbВ SQL Server начиная с версии 2005 есть функция отсутствующих индексов, которая использует объекты DMO и классы событий Showplan для получения сведений об отсутствующих индексах, благодаря которым можно повысить производительность обработки запросов в SQL Server.

А как получить эти сведения, обработать и принять решение о необходимости создания тех или иных индексов?

Интересная методика представлена в Учебном курсе Microsoft “SQL Server 2005 Реализация и обслуживание”.
Решение о эффективности индекса предлагается принять из расчета некоторого значения по формуле user_seeks * avg_total_user_cost * (avg_user_impact * 0.01). Исходные данные для расчета берутся из представлений sys.dm_db_missing_index*.
Значение выше 5000 в промышленных системах означает, что следует рассмотреть возможность создания этих индексов.
Если же значение превышает 10000, это обычно означает, что индекс может обеспечить значительное повышение производительности для операций чтения.
Немного творчества и получаем вот такой скрипт:
———————————————————————————————
— Создание недостающих индексов баз данных на SQL Server 2005, 2008

— Скрипт анализирует статистику, собранную сервером баз данных об отсутствующих индексах в базах данных и
— предлагает создать индексы, которые могут обеспечить значительное повышение производительности. 

SET NOCOUNT ON

DECLARE @dbid int

 

IF (object_id(‘tempdb..##IndexAdvantage’) IS NOT NULL) DROP TABLE ##IndexAdvantage

CREATE TABLE ##IndexAdvantage ([Преимущество индекса] float, [База данных] varchar(64), [Transact SQL код для создания индекса] varchar(512),

[Число компиляций] int, [Количество операций поиска] int, [Количество операций просмотра] int,

[Средняя стоимость ] int, [Средний процент выигрыша] int );

 

DECLARE DBases CURSOR FOR

SELECT database_id FROM sys.master_files Получаем список ID баз данных 

WHERE state = 0 AND — ONLINE

has_dbaccess(db_name(database_id)) = 1 — Only look at databases to which we have access

GROUP BY database_id

 

OPEN DBases

FETCH NEXT FROM DBases

INTO @dbid

 

WHILE @@FETCH_STATUS = 0

BEGIN — Выполняем для каждой базы данных —————————————————

 

INSERT INTO ##IndexAdvantage

SELECT [Преимущество индекса] = user_seeks * avg_total_user_cost * (avg_user_impact * 0.01),

      [База данных] = DB_NAME(mid.database_id),

      [Transact SQL код для создания индекса] = ‘CREATE INDEX [IX_’ + OBJECT_NAME(mid.object_id,@dbid) + ‘_’ +

      CAST(mid.index_handle AS nvarchar) + ‘] ON ‘ +

      mid.statement + ‘ (‘ + ISNULL(mid.equality_columns,») +

      (CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ‘, ‘ ELSE » END) +

      (CASE WHEN mid.inequality_columns IS NOT NULL THEN + mid.inequality_columns ELSE » END) +

      ‘)’ +

      (CASE WHEN mid.included_columns IS NOT NULL THEN ‘ INCLUDE (‘ + mid.included_columns + ‘)’ ELSE » END) +

      ‘;’,

      [Число компиляций] = migs.unique_compiles,

      [Количество операций поиска] = migs.user_seeks,

      [Количество операций просмотра] = migs.user_scans,

      [Средняя стоимость ] = CAST(migs.avg_total_user_cost AS int),

      [Средний процент выигрыша] = CAST(migs.avg_user_impact AS int)

FROM  sys.dm_db_missing_index_groups mig

JOIN  sys.dm_db_missing_index_group_stats migs

ON    migs.group_handle = mig.index_group_handle

JOIN  sys.dm_db_missing_index_details mid

ON    mig.index_handle = mid.index_handle

AND   mid.database_id = @dbid

 

    FETCH NEXT FROM DBases

    INTO @dbid

END —————————————————————————————-

CLOSE DBases

DEALLOCATE DBases

GO

 

SELECT * FROM ##IndexAdvantage ORDER BY 1 DESC

— Значение »Преимущество индекса» выше 5000 в промышленных системах означает, что следует рассмотреть возможность создания этих индексов.

— Если же значение превышает 10000, это обычно означает, что индекс может обеспечить значительное повышение производительности для операций чтения.

———————————————————————————————

— Отправляем email с рекомендацией создать индекс

IF (object_id(‘tempdb..##IndexAdvantage2’) IS NOT NULL) DROP TABLE ##IndexAdvantage2

SELECT * INTO ##IndexAdvantage2 FROM ##IndexAdvantage WHERE [Преимущество индекса] >= 5000 ORDER BY 1 DESC

 

IF ((SELECT COUNT(*) FROM ##IndexAdvantage2) >= 1) BEGIN

DECLARE @subject_str varchar(255),

@message_str varchar(1024),

@separator_str varchar(1),

@email varchar(128)

 

SET @separator_str=CHAR(9) — Символ табуляции

SET @email = ’email_address@webzavod.ru’

 

— Подготовим текст сообщения

SET @subject_str = ‘SQL Server ‘+@@SERVERNAME+‘: Предложение создать индексы в базе данных.’

SET @message_str = ‘Сервер ‘+@@SERVERNAME + ‘. Выявлена необходимость создать индексы в базе данных!

Во вложении — таблица с кодом предлагаемых индексов.

Значение «Преимущество индекса» выше 5000 в промышленных системах означает, что следует рассмотреть возможность создания этих индексов.

Если же значение превышает 10000, это обычно означает, что индекс может обеспечить значительное повышение производительности для операций чтения.

Динамические административные представления, которые помогли нам получить информацию об отсутствующих индексах, не являются заменой помощника по настройке ядра СУБД, который также рассматривает индексированные представления и секции и обеспечивает более всесторонний анализ индексов, но они могут быть очень эффективны на начальном уровне анализа.’

 

Отправляем email

EXEC msdb.dbo.sp_send_dbmail

@recipients = @email,

@query = ‘SELECT * FROM ##IndexAdvantage2’,

@subject = @subject_str,

@body = @message_str,

@attach_query_result_as_file = 1,

@query_result_separator = @separator_str,

@query_result_width = 7000

END

 

— Удаляем временную таблицу

IF (object_id(‘tempdb..##IndexAdvantage’) IS NOT NULL) DROP TABLE ##IndexAdvantage

IF (object_id(‘tempdb..##IndexAdvantage2’) IS NOT NULL) DROP TABLE ##IndexAdvantage2
———————————————————————————————

Как использовать данный скрипт:
Можно создать JOB на основе этого скрипта и выполнять его периодически.
Если скрипт выявит необходимость создать какой либо индекс, то он сообщит Вам об этом, послав электронное письмо (компонент Database Mail должен быть включен и настроен).

Отмечу также, что если приложение само управляет объектами базы данных, то и индексы следует создавать через функционал данного приложения.
Пример такого приложения –
Microsoft Dynamics AX.

Пример результата:

В прикрепленном файле образец предложения индекса данным скриптом. База данных от Microsoft Dynamics CRM 3.0
QueryResults897901.txt

Рубрики:SQL Server