Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 системныйе виды/функции для обычного пользователя.  [new]
Alexander Us
Member

Откуда:
Сообщений: 1095
Есть вид, для сбора статистики по таблицам, использующий системные виды/функции.
Есть ли возможность наделить обычного пользователя правами выборки из этого вида,
не давая ему GRANT VIEW SERVER STATE?

если кому надо:
+

Картинка с другого сайта.
use master
go
 
create function fn_exec_text()
returns nvarchar(4000)
begin
declare @ret nvarchar(4000)
set @ret= N' 
alter view v_statistics_spaceusage_tables

as

SELECT 

a3.name AS [schema_name],

a2.name AS [table_name],

a1.rows as row_count,

(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved_kb,

a1.data * 8 AS data_kb,

(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size_kb,

cast(case when (a1.reserved + ISNULL(a4.reserved,0))=0 then NULL else ((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END)*100.0) / (a1.reserved + ISNULL(a4.reserved,0)) end as decimal(5,2)) as index_pct,

(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused_kb,

cast(case when (a1.reserved + ISNULL(a4.reserved,0)) = 0 then NULL else ((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END)*100.0 / (a1.reserved + ISNULL(a4.reserved,0))) end as decimal(5,2)) AS unused_pct,

IDENT_CURRENT(''['' + db_name() + ''].[''+ a3.name + ''].['' + a2.name + '']'') last_identity,

(select count(*) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc where tc.TABLE_SCHEMA = a3.name and tc.TABLE_NAME=a2.name and CONSTRAINT_TYPE = ''primary key'' ) has_pk,

(select count(*) from INFORMATION_SCHEMA.COLUMNS           tc where tc.TABLE_SCHEMA = a3.name and tc.TABLE_NAME=a2.name and tc.DATA_TYPE in(''timestamp'')) has_tstmp,

st.is_published,

--case st.lob_data_space_id when 0 then ''N'' else ''J'' end has_lobs,
(select count(*) from sys.columns sc where sc.object_id=st.object_id) cols,
(select count(*) from sys.columns sc where sc.object_id=st.object_id and (sc.system_type_id in(35,99,34) or (sc.system_type_id in(167,231,165,241) and max_length=-1))) lobs,
st.large_value_types_out_of_row as is_oor,

cast(case when a1.rows=0 then null else a1.data * 8.0 / a1.rows end as decimal(16,4)) data_kb_per_row,
cast(case when a1.rows=0 then null else ((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8) * 8.0 / a1.rows end as decimal(16,4)) index_kb_per_row
 
FROM 

(SELECT 
		ps.object_id
	, SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows]
	, SUM (ps.reserved_page_count) AS reserved
	, SUM 
	  (CASE
		WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
		ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
		END
	   ) AS data
	   	
	,SUM (ps.used_page_count) AS used

	FROM sys.dm_db_partition_stats ps
	GROUP BY ps.object_id
) AS a1
LEFT OUTER JOIN
(SELECT 
	it.parent_id
	,SUM(ps.reserved_page_count) AS reserved
	,SUM(ps.used_page_count) AS used
	FROM 
			   sys.dm_db_partition_stats ps
	INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
	WHERE it.internal_type IN (202,204)
	GROUP BY it.parent_id
) AS						a4 ON (a4.parent_id = a1.object_id)

INNER JOIN sys.all_objects	a2  ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas		a3 ON (a2.schema_id = a3.schema_id)
INNER JOIN sys.tables       st on st.object_id=a1.object_id
WHERE a2.type <> N''S'' and a2.type <> N''IT''
and st.is_ms_shipped=0
'

return @ret
end
GO

exec master..sp_MSforeachdb N'
use [?]
if exists(select * from sys.databases	where database_id  = db_id() and is_read_only = 0)
begin
	declare @sql nvarchar(4000)
	select @sql = master.dbo.fn_exec_text()
	exec sp_executesql @sql
end
'

go
drop function fn_exec_text



К сообщению приложен файл. Размер - 15Kb
22 фев 19, 13:26    [21817256]     Ответить | Цитировать Сообщить модератору
 Re: системныйе виды/функции для обычного пользователя.  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
Alexander Us,

смотрите в сторону certificate signing
22 фев 19, 13:31    [21817259]     Ответить | Цитировать Сообщить модератору
 Re: системныйе виды/функции для обычного пользователя.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Alexander Us,

а что там требует SERVER STATE?

"вид" это представление что ли?
22 фев 19, 13:32    [21817261]     Ответить | Цитировать Сообщить модератору
 Re: системныйе виды/функции для обычного пользователя.  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
TaPaK,

там у него sys.dm_db_partition_stats требует view database state, но в купе с ms_foreachdb я так полагаю они решили не запариваться с отдельным правом на каждую бд а смотреть в сторону view server state
22 фев 19, 13:38    [21817266]     Ответить | Цитировать Сообщить модератору
 Re: системныйе виды/функции для обычного пользователя.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
я не сильно понимаю зачем эта информация "онлайн". Храните, обновляйте, хотр раз в минуту.... сделйте виды - раздайте в народ...
22 фев 19, 13:40    [21817268]     Ответить | Цитировать Сообщить модератору
 Re: системныйе виды/функции для обычного пользователя.  [new]
Alexander Us
Member

Откуда:
Сообщений: 1095
На прозвучавшие вопрсы:

  • да, "видом" я назвал "представление"
  • ms_foreachdb используется для создания этого представления на каждой базе сервера
  • онлайн удобно в некоторых случаях. Например, когда надо посмотреть скорость вставок

    Хотя, TaPaK Вы правы - можно собирать статистику в таблицу, и на неё раздать права просмотра.
  • 22 фев 19, 13:56    [21817277]     Ответить | Цитировать Сообщить модератору
     Re: системныйе виды/функции для обычного пользователя.  [new]
    TaPaK
    Member

    Откуда: Kiev
    Сообщений: 6794
    Alexander Us,

    а что в приведённом показывает "скорость вставок "
    22 фев 19, 13:59    [21817281]     Ответить | Цитировать Сообщить модератору
     Re: системныйе виды/функции для обычного пользователя.  [new]
    Alexander Us
    Member

    Откуда:
    Сообщений: 1095
    TaPaK
    Alexander Us,
    а что в приведённом показывает "скорость вставок "


    declare @cnt1 int, @cnt2 int
    select @cnt1 = row_count from v_statistics_spaceusage_tables where table_name = 'XXL'
    waitfor delay '00:00:03'
    select @cnt2 = row_count from v_statistics_spaceusage_tables where table_name = 'XXL'
    
    --скорость вставок:
    select (@cnt2-@cnt1)/3.0
    
    22 фев 19, 14:03    [21817290]     Ответить | Цитировать Сообщить модератору
     Re: системныйе виды/функции для обычного пользователя.  [new]
    TaPaK
    Member

    Откуда: Kiev
    Сообщений: 6794
    Alexander Us
    TaPaK
    Alexander Us,
    а что в приведённом показывает "скорость вставок "


    declare @cnt1 int, @cnt2 int
    select @cnt1 = row_count from v_statistics_spaceusage_tables where table_name = 'XXL'
    waitfor delay '00:00:03'
    select @cnt2 = row_count from v_statistics_spaceusage_tables where table_name = 'XXL'
    
    --скорость вставок:
    select (@cnt2-@cnt1)/3.0
    

    умора!
    22 фев 19, 14:05    [21817292]     Ответить | Цитировать Сообщить модератору
     Re: системныйе виды/функции для обычного пользователя.  [new]
    Alexander Us
    Member

    Откуда:
    Сообщений: 1095
    Исправление:

    в скрипте создания вида надо заменить
    alter view v_statistics_spaceusage_tables  на
    create view v_statistics_spaceusage_tables
    
    22 фев 19, 14:07    [21817296]     Ответить | Цитировать Сообщить модератору
     Re: системныйе виды/функции для обычного пользователя.  [new]
    Alexander Us
    Member

    Откуда:
    Сообщений: 1095
    TaPaK
    умора!
    А как Вы измеряете количество вставок в секунду на больших таблицах?

    select count(*) ? ;))
    22 фев 19, 14:10    [21817297]     Ответить | Цитировать Сообщить модератору
     Re: системныйе виды/функции для обычного пользователя.  [new]
    TaPaK
    Member

    Откуда: Kiev
    Сообщений: 6794
    Alexander Us
    TaPaK
    умора!
    А как Вы измеряете количество вставок в секунду на больших таблицах?

    select count(*) ? ;))

    а что мне с этой информацией делать потом?
    22 фев 19, 14:17    [21817306]     Ответить | Цитировать Сообщить модератору
     Re: системныйе виды/функции для обычного пользователя.  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1172
    Alexander Us,

    откройте для себя sys.dm_db_index_operational_stats

    у вас "измерение количества вставок" вашим методом шикарно даст отрицательную величину. пример нужен или догадаетесь?
    22 фев 19, 14:20    [21817312]     Ответить | Цитировать Сообщить модератору
     Re: системныйе виды/функции для обычного пользователя.  [new]
    Alexander Us
    Member

    Откуда:
    Сообщений: 1095
    TaPaK
    а что мне с этой информацией делать потом?

    Это уже совсем другой вопрос.

    В некоторых случаях для этой информации есть практическое применение как:
    индикатор нагрузки приложенией
    индикатор скорости/эффективности при массовом копировании
    индткатор отката транзакции (результат со знаком минус)

    Но Вы пока не ответили на первый.
    22 фев 19, 14:25    [21817319]     Ответить | Цитировать Сообщить модератору
     Re: системныйе виды/функции для обычного пользователя.  [new]
    TaPaK
    Member

    Откуда: Kiev
    Сообщений: 6794
    Alexander Us
    TaPaK
    а что мне с этой информацией делать потом?

    Это уже совсем другой вопрос.

    В некоторых случаях для этой информации есть практическое применение как:
    идникатор нагрузки приложенией
    индикатор скорости/эффективности при массовом копировании
    индткатор отката транзакции (результат со знаком минус)

    Но Вы пока не ответили на первый.

    мне достаточно dm_io_virtual_file_stats или как говорили dm_db_index_operational_stats

    автор
    индткатор отката транзакции (результат со знаком минус)

    всё веселее и веселее

    автор
    индикатор скорости/эффективности при массовом копировании

    у вас есть доска почёта судя по всему
    22 фев 19, 14:30    [21817325]     Ответить | Цитировать Сообщить модератору
     Re: системныйе виды/функции для обычного пользователя.  [new]
    Alexander Us
    Member

    Откуда:
    Сообщений: 1095
    felix_ff
    Alexander Us,
    откройте для себя sys.dm_db_index_operational_stats
    у вас "измерение количества вставок" вашим методом шикарно даст отрицательную величину. пример нужен или догадаетесь?


    Насчёт sys.dm_db_index_operational_stats почитаю, спасибо.
    Насчёт отрицательной величины пока не догадался, если Вам не влом, буду признателен за пример.
    Разве что вы имеете ввиду откат транзакции - тогда отрицательная величина то, что ожидается.

    Своё представление, я "списал" частично со стандартного отчёта SSMS "Disk Usage by Table/Top Tables".
    Если Вы вызовите этот отчёт 2 раза с интервалом Х секунд, вы получите ту же разницу.
    22 фев 19, 14:38    [21817338]     Ответить | Цитировать Сообщить модератору
     Re: системныйе виды/функции для обычного пользователя.  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1172
    Alexander Us,

    никакого отката и в помине нет, все закомичено, но у вас не учтитываются инструкции удаления из таблицы

    drop table if exists t_stat;
    create table t_stat( id int);
    

    две сессии
    waitfor time '14:47:50'
    
    insert into t_stat 
    select row_number() over (order by 1/0) from string_split(replicate(cast(';' as varchar(max)), 9999), ';') 
    waitfor delay '00:00:02'
    delete from t_stat
    


    waitfor time '14:47:51'
    declare @cnt1 int = 0, @cnt2 int = 0
    select @cnt1 = row_count from v_statistics_spaceusage_tables where table_name = 't_stat'
    waitfor delay '00:00:03'
    select @cnt2 = row_count from v_statistics_spaceusage_tables where table_name = 't_stat'
    
    print @cnt1
    print @cnt2
    --скорость вставок:
    select (@cnt2-@cnt1)/3.0
    


    waitfor time в двух поправить немного что бы почти одновременно запустились.
    22 фев 19, 14:45    [21817350]     Ответить | Цитировать Сообщить модератору
     Re: системныйе виды/функции для обычного пользователя.  [new]
    TaPaK
    Member

    Откуда: Kiev
    Сообщений: 6794
    Веслее как же всё таки интерпретировать эту величину... Абастрактный 0 это нормально? Если транзация не влезла в ваш таймаут, при этом сожрала всё до чего смогла дотянуться? Какая нагрузка на сервер была пока вы меряли "скорость вставки"... Так цифирь для доски почёта.
    Если это тестовое окружение, то мерять "так" нет необходимости.
    22 фев 19, 14:58    [21817367]     Ответить | Цитировать Сообщить модератору
     Re: системныйе виды/функции для обычного пользователя.  [new]
    Alexander Us
    Member

    Откуда:
    Сообщений: 1095
    TaPaK,

    Представление сделано из стандартного отчёта SSMS "Disk Usage by Table/Top Tables".
    Это (почти) то же, но в форме таблицы(представления).
    22 фев 19, 15:13    [21817377]     Ответить | Цитировать Сообщить модератору
     Re: системныйе виды/функции для обычного пользователя.  [new]
    Alexander Us
    Member

    Откуда:
    Сообщений: 1095
    felix_ff,

    спасибо за разъяснения.
    Это не делает представление ложным.
    Просто надо учитывать, что Вы написали.
    22 фев 19, 15:16    [21817380]     Ответить | Цитировать Сообщить модератору
     Re: системныйе виды/функции для обычного пользователя.  [new]
    TaPaK
    Member

    Откуда: Kiev
    Сообщений: 6794
    Alexander Us
    TaPaK,

    Представление сделано из стандартного отчёта SSMS "Disk Usage by Table/Top Tables".
    Это (почти) то же, но в форме таблицы(представления).


    Disk Usage by Table вы интерпретируете как "скорость вставки"? Успехов
    22 фев 19, 15:22    [21817391]     Ответить | Цитировать Сообщить модератору
     Re: системныйе виды/функции для обычного пользователя.  [new]
    Alexander Us
    Member

    Откуда:
    Сообщений: 1095
    TaPaK,
    Вы занимаетесь ерундой.

    Если Вы знаете как правильно/лучше, то приведите работающий пример и объяснения.
    Как это сделал felix_ff.
    22 фев 19, 15:30    [21817404]     Ответить | Цитировать Сообщить модератору
     Re: системныйе виды/функции для обычного пользователя.  [new]
    Владислав Колосов
    Member

    Откуда:
    Сообщений: 7406
    Alexander Us,

    Какой практический смысл имеет "скорость вставки", даже если бы и был такой счетчик? Какие выводы Вы сможете сделать по его показаниям?
    22 фев 19, 18:11    [21817557]     Ответить | Цитировать Сообщить модератору
     Re: системныйе виды/функции для обычного пользователя.  [new]
    Alexander Us
    Member

    Откуда:
    Сообщений: 1095
    Владислав Колосов,

    я использую:

    - как косвенный показатель нагрузки от приложений
    - для проверки эффективности и оценки времени окончания массового импорта


    ЗЫ:
    Учитывая замечания от felix_ff оговорюсь, что я делаю массовый импорт самописным тулом на основе SqlBulkCopy.
    Он вставляет порциями.

    ЗЗЫ:
    Вообще то для для этой цели лучше использовать sp:

    +

    CREATE PROCEDURE [sp_growing_perf] 
    
    	@database_name sysname, @shema_name sysname, @table_name sysname, @messure_duration_sec decimal(16,4)
    
    AS
    BEGIN	
    	SET NOCOUNT ON;
    
        declare @rows1 bigint, @rows2 bigint, @waitto datetime, @sql nvarchar(4000)
    
    	set @shema_name=isnull(@shema_name,'dbo');
    	set @waitto=dateadd(ss,@messure_duration_sec,getdate());	
    	@shema_name + '.' + @table_name + ''')  and indid in (0,1)'
    
    	--SQL-Versionen: https://sqlserverbuilds.blogspot.de/
    	
        if cast(substring(cast(SERVERPROPERTY('productversion') as varchar(20)),1,2) as int)<=10 --bis 2008 R2:
    	set @sql = 'SELECT @rows=SUM(CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) FROM ' + @database_name + '.sys.dm_db_partition_stats ps WHERE ps.object_id = object_id(''' + @database_name + '.' + @shema_name + '.' + @table_name + ''')'	
    	  else
        set @sql = 'SELECT @rows=SUM(CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) FROM ' + @database_name + '.sys.dm_db_partition_stats ps WHERE ps.object_id NOT IN (SELECT object_id FROM ' + @database_name + '.sys.tables WHERE is_memory_optimized = 1)and ps.object_id = object_id(''' + @database_name + '.' + @shema_name + '.' + @table_name + ''')'
    	
    	exec sp_executesql @sql, N'@rows bigint OUTPUT', @rows=@rows1 OUTPUT
    	waitfor time @waitto;
    	exec sp_executesql @sql, N'@rows bigint OUTPUT', @rows=@rows2 OUTPUT
    
    	select @rows1 rows_from, @rows2 rows_to, cast((@rows2-@rows1) / @messure_duration_sec as decimal(12,1)) rows_sec
    		
    END
    

    22 фев 19, 18:27    [21817565]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить