Например у нас есть хранимая процедура которую нужно оптимизировать.
Сначала смотрим сколько времени занимают отдельные запросы присутствующие в пакете.
Потом смотрим план запросов. Здесь основным параметром является стоимость запроса. Запросы с наибольшей стоимостью не всегда имеют наибольшее время выполнения.
What's this cost?
Анализируя данные профайлера нужно внимательно смотреть на единицы измерения и не перепутать миллисекунды с микросекундами.
Viewing and Analyzing Traces with SQL Server Profiler
http://www.sql-server-performance.com/nb_execution_plan_statistics.asp
http://www.sql-server-performance.com/query_execution_plan_analysis.asp
http://www.codeproject.com/cs/database/sql-tuning-tutorial-1.asp
http://www.sql-server-performance.com/jc_parallel_execution_plans.asp
http://www.sql-server-performance.com/jc_sql_server_quantative_analysis1.asp
Показаны сообщения с ярлыком sql server dba. Показать все сообщения
Показаны сообщения с ярлыком sql server dba. Показать все сообщения
четверг, 17 февраля 2011 г.
четверг, 23 декабря 2010 г.
Партиционирование. Скользящее окно.
Известно, что партиционирование применяется тогда когда идет речь о работе с большими объемами данных. Зачастую большие объемы возникают там где требуется хранить какую либо архивную информацию, например за несколько последних месяцев или лет.
Но время идет в систему добавляются новые данные, в месте с тем часть данных устаревает на столько что хранить их больше не требуется. В таких случаях архивные данные должны быть организованны в виде скользящего окна.
Ксожалению , в текущей версии Sql Server нет встроенное реализации этой задачи, однако в интернете есть множество ресурсов в которых описаны различные варианты ее решения.
http://www.sqlservercentral.com/articles/Partitioning/71655/
http://www.sqlservercentral.com/articles/Partitioning/71656/
http://msdn.microsoft.com/en-us/library/aa964122(v=sql.90).aspx
Но время идет в систему добавляются новые данные, в месте с тем часть данных устаревает на столько что хранить их больше не требуется. В таких случаях архивные данные должны быть организованны в виде скользящего окна.
Ксожалению , в текущей версии Sql Server нет встроенное реализации этой задачи, однако в интернете есть множество ресурсов в которых описаны различные варианты ее решения.
http://www.sqlservercentral.com/articles/Partitioning/71655/
http://www.sqlservercentral.com/articles/Partitioning/71656/
http://msdn.microsoft.com/en-us/library/aa964122(v=sql.90).aspx
четверг, 16 декабря 2010 г.
Использование кеша данных
Когда требуется узнать как сервер использует выделенную память и вчастности буфер данных, может помоч системное представление sys.dm_os_buffer_descriptors
SELECT count(*)*8/1024 AS 'Cached Size (MB)'
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS 'Database'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY 'Cached Size (MB)' DESC
Более детально, использование кеша данных обсуждается тут
SELECT count(*)*8/1024 AS 'Cached Size (MB)'
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS 'Database'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY 'Cached Size (MB)' DESC
Более детально, использование кеша данных обсуждается тут
понедельник, 6 декабря 2010 г.
Наиболее используемые индексы и таблицы
Всегда полезно анализировать нагрузку на сервер баз данных. В частность важно знать какие таблицы используются наиболее часто. Это важно, например, при разнесении БД по нескольким файловым группам расположенным на разных дисках.
--get most used tables
SELECT
db_name(ius.database_id) AS DatabaseName,
t.NAME AS TableName,
SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS NbrTimesAccessed
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.tables t ON t.OBJECT_ID = ius.object_id
WHERE database_id = DB_ID('MyDb')
GROUP BY database_id, t.name
ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC
--get most used indexes
SELECT
db_name(ius.database_id) AS DatabaseName,
t.NAME AS TableName,
i.NAME AS IndexName,
i.type_desc AS IndexType,
ius.user_seeks + ius.user_scans + ius.user_lookups AS NbrTimesAccessed
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i ON i.OBJECT_ID = ius.OBJECT_ID AND i.index_id = ius.index_id
INNER JOIN sys.tables t ON t.OBJECT_ID = i.object_id
WHERE database_id = DB_ID('MyDb')
ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC
источник
declare @dbid int
--To get Datbase ID
set @dbid = db_id()
select
db_name(d.database_id) database_name
,object_name(d.object_id) object_name
,s.name index_name,
c.index_columns
,d.*
from sys.dm_db_index_usage_stats d
inner join sys.indexes s
on d.object_id = s.object_id
and d.index_id = s.index_id
left outer join
(select distinct object_id, index_id,
stuff((SELECT ','+col_name(object_id,column_id ) as 'data()' FROM sys.index_columns t2 where t1.object_id =t2.object_id and t1.index_id = t2.index_id FOR XML PATH ('')),1,1,'')
as 'index_columns' FROM sys.index_columns t1 ) c on
c.index_id = s.index_id and c.object_id = s.object_id
where database_id = @dbid
and s.type_desc = 'NONCLUSTERED'
and objectproperty(d.object_id, 'IsIndexable') = 1
order by
(user_seeks+user_scans+user_lookups+system_seeks+system_scans+system_lookups) desc
источник
--get most used tables
SELECT
db_name(ius.database_id) AS DatabaseName,
t.NAME AS TableName,
SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS NbrTimesAccessed
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.tables t ON t.OBJECT_ID = ius.object_id
WHERE database_id = DB_ID('MyDb')
GROUP BY database_id, t.name
ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC
--get most used indexes
SELECT
db_name(ius.database_id) AS DatabaseName,
t.NAME AS TableName,
i.NAME AS IndexName,
i.type_desc AS IndexType,
ius.user_seeks + ius.user_scans + ius.user_lookups AS NbrTimesAccessed
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i ON i.OBJECT_ID = ius.OBJECT_ID AND i.index_id = ius.index_id
INNER JOIN sys.tables t ON t.OBJECT_ID = i.object_id
WHERE database_id = DB_ID('MyDb')
ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC
источник
declare @dbid int
--To get Datbase ID
set @dbid = db_id()
select
db_name(d.database_id) database_name
,object_name(d.object_id) object_name
,s.name index_name,
c.index_columns
,d.*
from sys.dm_db_index_usage_stats d
inner join sys.indexes s
on d.object_id = s.object_id
and d.index_id = s.index_id
left outer join
(select distinct object_id, index_id,
stuff((SELECT ','+col_name(object_id,column_id ) as 'data()' FROM sys.index_columns t2 where t1.object_id =t2.object_id and t1.index_id = t2.index_id FOR XML PATH ('')),1,1,'')
as 'index_columns' FROM sys.index_columns t1 ) c on
c.index_id = s.index_id and c.object_id = s.object_id
where database_id = @dbid
and s.type_desc = 'NONCLUSTERED'
and objectproperty(d.object_id, 'IsIndexable') = 1
order by
(user_seeks+user_scans+user_lookups+system_seeks+system_scans+system_lookups) desc
источник
Мониторинг памяти в SQL Server
- Текущее состояние памяти можно увидеть коммандой DBCC MEMORYSTATUS расшифровка результатов тут
- Два запроса позволяющих определить данные из каких таблиц находятся в кеше
1).
;WITH memusage_CTE AS (SELECT bd.database_id, bd.file_id, bd.page_id, bd.page_type
, COALESCE(p1.object_id, p2.object_id) AS object_id
, COALESCE(p1.index_id, p2.index_id) AS index_id
, bd.row_count, bd.free_space_in_bytes, CONVERT(TINYINT,bd.is_modified) AS 'DirtyPage'
FROM sys.dm_os_buffer_descriptors AS bd
JOIN sys.allocation_units AS au
ON au.allocation_unit_id = bd.allocation_unit_id
OUTER APPLY (
SELECT TOP(1) p.object_id, p.index_id
FROM sys.partitions AS p
WHERE p.hobt_id = au.container_id AND au.type IN (1, 3)
) AS p1
OUTER APPLY (
SELECT TOP(1) p.object_id, p.index_id
FROM sys.partitions AS p
WHERE p.partition_id = au.container_id AND au.type = 2
) AS p2
WHERE bd.database_id = DB_ID() AND
bd.page_type IN ('DATA_PAGE', 'INDEX_PAGE','TEXT_MIX_PAGE') )
SELECT TOP 20 DB_NAME(database_id) AS 'Database',OBJECT_NAME(object_id,database_id) AS 'Table Name', index_id,COUNT(*) AS 'Pages in Cache', SUM(dirtyPage) AS 'Dirty Pages'
FROM memusage_CTE
GROUP BY database_id, object_id, index_id
ORDER BY COUNT(*) DESC
2).
SELECT top 20 obj.[name]as "Table Name" ,obj.index_id ,si.name,convert(numeric(10,2),(count(*)*8)/1024.0) AS "cached size (mb)"
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
join sys.indexes si on si.index_id = obj.index_id and si.[object_id] = object_id(obj.name)
WHERE bd.database_id = db_id()
GROUP BY obj.name, obj.index_id,si.name
ORDER BY "cached size (mb)" DESC
- Два запроса позволяющих определить данные из каких таблиц находятся в кеше
1).
;WITH memusage_CTE AS (SELECT bd.database_id, bd.file_id, bd.page_id, bd.page_type
, COALESCE(p1.object_id, p2.object_id) AS object_id
, COALESCE(p1.index_id, p2.index_id) AS index_id
, bd.row_count, bd.free_space_in_bytes, CONVERT(TINYINT,bd.is_modified) AS 'DirtyPage'
FROM sys.dm_os_buffer_descriptors AS bd
JOIN sys.allocation_units AS au
ON au.allocation_unit_id = bd.allocation_unit_id
OUTER APPLY (
SELECT TOP(1) p.object_id, p.index_id
FROM sys.partitions AS p
WHERE p.hobt_id = au.container_id AND au.type IN (1, 3)
) AS p1
OUTER APPLY (
SELECT TOP(1) p.object_id, p.index_id
FROM sys.partitions AS p
WHERE p.partition_id = au.container_id AND au.type = 2
) AS p2
WHERE bd.database_id = DB_ID() AND
bd.page_type IN ('DATA_PAGE', 'INDEX_PAGE','TEXT_MIX_PAGE') )
SELECT TOP 20 DB_NAME(database_id) AS 'Database',OBJECT_NAME(object_id,database_id) AS 'Table Name', index_id,COUNT(*) AS 'Pages in Cache', SUM(dirtyPage) AS 'Dirty Pages'
FROM memusage_CTE
GROUP BY database_id, object_id, index_id
ORDER BY COUNT(*) DESC
2).
SELECT top 20 obj.[name]as "Table Name" ,obj.index_id ,si.name,convert(numeric(10,2),(count(*)*8)/1024.0) AS "cached size (mb)"
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
join sys.indexes si on si.index_id = obj.index_id and si.[object_id] = object_id(obj.name)
WHERE bd.database_id = db_id()
GROUP BY obj.name, obj.index_id,si.name
ORDER BY "cached size (mb)" DESC
воскресенье, 21 ноября 2010 г.
Официальные источники статей по Sql Server
Sql server 2005
http://technet.microsoft.com/en-us/library/ff928326(SQL.10).aspx
Sql server 2008 Community Articles
http://technet.microsoft.com/en-us/library/cc872864(SQL.100).aspx
Sql server 2008 SQLCAT Articles
http://technet.microsoft.com/en-us/library/dd334464(SQL.100).aspx
Sql server 2008 Microsoft White Papers
http://technet.microsoft.com/en-us/library/ee229554(SQL.10).aspx
Sql server 2008 R2 Microsoft White Papers
http://technet.microsoft.com/en-us/library/ee410014.aspx
Sql server 2008 R2 SQLCAT Articles
http://technet.microsoft.com/en-us/library/ff645399.aspx
http://technet.microsoft.com/en-us/library/ff928326(SQL.10).aspx
Sql server 2008 Community Articles
http://technet.microsoft.com/en-us/library/cc872864(SQL.100).aspx
Sql server 2008 SQLCAT Articles
http://technet.microsoft.com/en-us/library/dd334464(SQL.100).aspx
Sql server 2008 Microsoft White Papers
http://technet.microsoft.com/en-us/library/ee229554(SQL.10).aspx
Sql server 2008 R2 Microsoft White Papers
http://technet.microsoft.com/en-us/library/ee410014.aspx
Sql server 2008 R2 SQLCAT Articles
http://technet.microsoft.com/en-us/library/ff645399.aspx
пятница, 5 ноября 2010 г.
среда, 18 августа 2010 г.
Список прав на объекты в текущей БД
DECLARE @Principal sysname
SET @Principal = NULL -- set this to a specific user or role name if desired.
SELECT
prin.name AS PrincipalName,
prin.type_desc AS PrincipalType,
CASE
WHEN perm.class=1 and perm.minor_id = 0 THEN 'OBJECT'
WHEN perm.class=1 and perm.minor_id = 0 THEN 'COLUMN'
ELSE perm.class_desc
END AS SecurableType,
sch.name AS SchemaName,
obj.name AS ObjectName,
IsNull(col.name,'') AS ColumnName,
state_desc AS PermissionState,
permission_name AS Permission
--,*
FROM sys.database_principals AS prin
JOIN sys.database_permissions AS perm
ON prin.principal_ID = perm.grantee_principal_ID
JOIN sys.objects AS obj
ON perm.major_id = obj.object_id
AND perm.minor_id = 0
LEFT JOIN sys.columns AS col
ON perm.major_id = col.object_id
AND col.column_id = perm.minor_id
JOIN sys.schemas AS sch
ON obj.schema_id = sch.schema_id
WHERE @Principal IS NULL OR prin.name=@Principal
Ссылка
SET @Principal = NULL -- set this to a specific user or role name if desired.
SELECT
prin.name AS PrincipalName,
prin.type_desc AS PrincipalType,
CASE
WHEN perm.class=1 and perm.minor_id = 0 THEN 'OBJECT'
WHEN perm.class=1 and perm.minor_id = 0 THEN 'COLUMN'
ELSE perm.class_desc
END AS SecurableType,
sch.name AS SchemaName,
obj.name AS ObjectName,
IsNull(col.name,'') AS ColumnName,
state_desc AS PermissionState,
permission_name AS Permission
--,*
FROM sys.database_principals AS prin
JOIN sys.database_permissions AS perm
ON prin.principal_ID = perm.grantee_principal_ID
JOIN sys.objects AS obj
ON perm.major_id = obj.object_id
AND perm.minor_id = 0
LEFT JOIN sys.columns AS col
ON perm.major_id = col.object_id
AND col.column_id = perm.minor_id
JOIN sys.schemas AS sch
ON obj.schema_id = sch.schema_id
WHERE @Principal IS NULL OR prin.name=@Principal
Ссылка
В каких объектах используются колонки таблицы
-- =============================================================================
-- Title: SQL Server 2005 Column Usage
-- Author: Bret Stateham
-- bret@pingit.biz
-- Created: 08/01/07
-- Description: Sample script to show table column usage by server objects
-- =============================================================================
SET NOCOUNT ON
IF OBJECT_ID('TempDB..#Dependencies') IS NOT NULL
DROP TABLE #Dependencies
CREATE TABLE #Dependencies
(
ReferencingObject nvarchar(256),
ReferencingColumn nvarchar(256),
ReferencedObject nvarchar(256),
ReferencedColumn nvarchar(256),
Usage nchar(256)
)
INSERT INTO #Dependencies (ReferencingObject,ReferencingColumn,ReferencedObject,ReferencedColumn,Usage)
SELECT
object_name(object_id) AS ReferencingObject
,IsNull
(
(
SELECT name
FROM sys.columns AS c
WHERE c.object_id = d.object_id
AND c.column_id = d.column_id
)
,''
) AS ReferencingColumn
,OBJECT_NAME(referenced_major_id) AS ReferencedObject
,ISNULL
(
(
SELECT name
FROM sys.columns AS c
WHERE c.object_id = d.referenced_major_id
AND c.column_id = d.referenced_minor_id
)
,''
) AS ReferencedColumn
,CASE
WHEN is_selected = 1 and is_updated = 1 THEN 'SU'
WHEN is_selected = 1 and is_updated = 0 THEN 'S'
WHEN is_selected = 0 and is_updated = 1 THEN 'U'
WHEN is_selected = 0 and is_updated = 0 THEN ''
END AS Usage
FROM sys.sql_dependencies AS d
DECLARE @PivotStatement nvarchar(max)
DECLARE @PivotColumns nvarchar(max)
DECLARE @SelectColumns nvarchar(max)
SET @PivotStatement = ''
SET @PivotColumns = NULL
SET @SelectColumns = ''
SELECT @PivotColumns =
COALESCE(@PivotColumns + ',[' + Referencing + ']','[' + Referencing + ']')
FROM
(
SELECT DISTINCT
CASE
WHEN ReferencingColumn <> '' THEN
ReferencingObject + '.' + ReferencingColumn
ELSE
ReferencingObject
END AS Referencing
FROM #Dependencies
) AS DistinctReferencing
SELECT
@SelectColumns = ISNULL(@SelectColumns,'') +
' ,ISNULL([' + Referencing + '],'''') AS [' + Referencing + ']' + CHAR(13) + CHAR(10)
FROM
(
SELECT DISTINCT
CASE
WHEN ReferencingColumn <> '' THEN
ReferencingObject + '.' + ReferencingColumn
ELSE
ReferencingObject
END AS Referencing
FROM #Dependencies
) AS DistinctReferencing
SET @PivotStatement = '
SELECT
ReferencedObject
,ReferencedColumn
' + @SelectColumns + '
FROM
(
SELECT
ReferencedObject
,ReferencedColumn
,CASE
WHEN ReferencingColumn <> '''' THEN
ReferencingObject + ''.'' + ReferencingColumn
ELSE
ReferencingObject
END AS Referencing
,Usage
FROM #Dependencies
) AS d
PIVOT
(
MAX(Usage)
FOR Referencing IN (' + @PivotColumns + ')
) AS PivotedDependencies
ORDER BY ReferencedObject, ReferencedColumn
'
EXEC (@PivotStatement)
DROP TABLE #Dependencies
SET NOCOUNT OFF
Ссылка
-- Title: SQL Server 2005 Column Usage
-- Author: Bret Stateham
-- bret@pingit.biz
-- Created: 08/01/07
-- Description: Sample script to show table column usage by server objects
-- =============================================================================
SET NOCOUNT ON
IF OBJECT_ID('TempDB..#Dependencies') IS NOT NULL
DROP TABLE #Dependencies
CREATE TABLE #Dependencies
(
ReferencingObject nvarchar(256),
ReferencingColumn nvarchar(256),
ReferencedObject nvarchar(256),
ReferencedColumn nvarchar(256),
Usage nchar(256)
)
INSERT INTO #Dependencies (ReferencingObject,ReferencingColumn,ReferencedObject,ReferencedColumn,Usage)
SELECT
object_name(object_id) AS ReferencingObject
,IsNull
(
(
SELECT name
FROM sys.columns AS c
WHERE c.object_id = d.object_id
AND c.column_id = d.column_id
)
,''
) AS ReferencingColumn
,OBJECT_NAME(referenced_major_id) AS ReferencedObject
,ISNULL
(
(
SELECT name
FROM sys.columns AS c
WHERE c.object_id = d.referenced_major_id
AND c.column_id = d.referenced_minor_id
)
,''
) AS ReferencedColumn
,CASE
WHEN is_selected = 1 and is_updated = 1 THEN 'SU'
WHEN is_selected = 1 and is_updated = 0 THEN 'S'
WHEN is_selected = 0 and is_updated = 1 THEN 'U'
WHEN is_selected = 0 and is_updated = 0 THEN ''
END AS Usage
FROM sys.sql_dependencies AS d
DECLARE @PivotStatement nvarchar(max)
DECLARE @PivotColumns nvarchar(max)
DECLARE @SelectColumns nvarchar(max)
SET @PivotStatement = ''
SET @PivotColumns = NULL
SET @SelectColumns = ''
SELECT @PivotColumns =
COALESCE(@PivotColumns + ',[' + Referencing + ']','[' + Referencing + ']')
FROM
(
SELECT DISTINCT
CASE
WHEN ReferencingColumn <> '' THEN
ReferencingObject + '.' + ReferencingColumn
ELSE
ReferencingObject
END AS Referencing
FROM #Dependencies
) AS DistinctReferencing
SELECT
@SelectColumns = ISNULL(@SelectColumns,'') +
' ,ISNULL([' + Referencing + '],'''') AS [' + Referencing + ']' + CHAR(13) + CHAR(10)
FROM
(
SELECT DISTINCT
CASE
WHEN ReferencingColumn <> '' THEN
ReferencingObject + '.' + ReferencingColumn
ELSE
ReferencingObject
END AS Referencing
FROM #Dependencies
) AS DistinctReferencing
SET @PivotStatement = '
SELECT
ReferencedObject
,ReferencedColumn
' + @SelectColumns + '
FROM
(
SELECT
ReferencedObject
,ReferencedColumn
,CASE
WHEN ReferencingColumn <> '''' THEN
ReferencingObject + ''.'' + ReferencingColumn
ELSE
ReferencingObject
END AS Referencing
,Usage
FROM #Dependencies
) AS d
PIVOT
(
MAX(Usage)
FOR Referencing IN (' + @PivotColumns + ')
) AS PivotedDependencies
ORDER BY ReferencedObject, ReferencedColumn
'
EXEC (@PivotStatement)
DROP TABLE #Dependencies
SET NOCOUNT OFF
Ссылка
пятница, 2 июля 2010 г.
пятница, 25 июня 2010 г.
Флаги трассировки SQLServer
Флаги трассировки служат для изменения стандартного поведения сервера.
Существуют глобальные флаги трассировки которые действуют на уровне сервера и оказывают влияние на все соединения и флаги трассировки действия которых распространяется на уровне сессии.
Существуют глобальные флаги трассировки которые действуют на уровне сервера и оказывают влияние на все соединения и флаги трассировки действия которых распространяется на уровне сессии.
Проверить фрагментированность индексов
(a)
USE database;
GO
DBCC SHOWCONTIG (table);
GO
(b)
SELECT type_desc, ind.name AS Name_of_the_Index,OBJECT_NAME(ind.object_id)
AS Which_Table_has_this_index, phystat.avg_fragmentation_in_percent as Fragmentation_in_Percent
FROM sys.dm_db_index_physical_stats(DB_ID('database'), OBJECT_ID('table'), NULL, NULL, 'DETAILED')
phystat inner JOIN sys.indexes ind ON ind.object_id = phystat.object_id
AND ind.index_id = phystat.index_id
USE database;
GO
DBCC SHOWCONTIG (table);
GO
(b)
SELECT type_desc, ind.name AS Name_of_the_Index,OBJECT_NAME(ind.object_id)
AS Which_Table_has_this_index, phystat.avg_fragmentation_in_percent as Fragmentation_in_Percent
FROM sys.dm_db_index_physical_stats(DB_ID('database'), OBJECT_ID('table'), NULL, NULL, 'DETAILED')
phystat inner JOIN sys.indexes ind ON ind.object_id = phystat.object_id
AND ind.index_id = phystat.index_id
Transaction Log
Основные причины роста:
- незавершенные транзакции
- очень длинный транзакции
- операции DBCC DBREINDEX и CREATE INDEX
- восстановление из Transaction Log Backups
- незавершенные транзакции
- очень длинный транзакции
- операции DBCC DBREINDEX и CREATE INDEX
- восстановление из Transaction Log Backups
четверг, 24 июня 2010 г.
Полнотекстовые индексы
Microsoft SQL Server 2005 Full-Text Search (FTS) ramblings…
Lucene.NET vs SQL Server Full-text – Generating a million records and a full-text index
Оптимизация производительности
Performance Tuning and Optimization of Full-Text Queries
SQL 2008 Full-Text Search Problems
Improving SQL Server full-text search performance
SQL Server 2008 Full Text slowness
SQL Server Full-Text Search Performance Tuning and Optimization
Performance Tuning and Optimization of Full-Text Indexes
Best Practices for Integrated Full Text Search (iFTS) in SQL 2008
Lucene.NET vs SQL Server Full-text – Generating a million records and a full-text index
Оптимизация производительности
Performance Tuning and Optimization of Full-Text Queries
SQL 2008 Full-Text Search Problems
Improving SQL Server full-text search performance
SQL Server 2008 Full Text slowness
SQL Server Full-Text Search Performance Tuning and Optimization
Performance Tuning and Optimization of Full-Text Indexes
Best Practices for Integrated Full Text Search (iFTS) in SQL 2008
Анализ счетчиков производительности
Счетчики производительности - это важнейший источник информации о состоянии сервера. Можно наяти множество рекомендаций по интерпретации их показаний. Здесь мне хочется остановиться на отношениях между показаниями различных счетчиков.
1. (Page lookups/sec) / (Batch Requests/sec) < 100
привышение этого соотношения свидетельствует о слишком частых обращениях к BuferPool и высоких значения логического ввода/вывода. Происходит из-за не эфективных запросов.
2. (Page Splits/sec) / (Batch Requests/sec) < 0.2
большое значение этого соотношения свидетельствует о слишком частых переполнениях индексных страниц. Исправить ситуацию можно увеличением fillfactor.
3. (Index Searches/sec)/(Full Scans/sec) > 1000
если маленикие значения этого показателя не сопровождаются нагрузкой на CPU это может свидетельствовать о том что Full Scans происходят на маленьких таблицах, что вполне допустимо.
Частые сканирования больших таблиц происходят из-за недостающих индексов и слишком большом количестве запрашиваемых данных.
4. (Total Latch Wait Time) / (Latch Waits/Sec) < 10
средняя продолжительность ожидания Latch
5. (SQL Compilations/sec) / (Batch Requests/sec) < 0.2
большая величина этого отношения свидетельствует о частых adhoc запросах, что может приводить к перегрузке CPU. Улучшение этого показателя достигается использование хранимых процедур и правильным использованием sp_executeSQL.
6. (Forwarded requests/sec) / (Batch Requests/sec) < 0.1
SQL Server - Performance Counter Guidance
Understanding SQL Performance Counters
Finding performance bottlenecks and their resolutions in windows services
How to troubleshoot SQL Server performance problems by using Perfmon
SQL Server Performance Assessment and Optimization Techniques
Performance Counters - Analysis
http://weblogs.sqlteam.com/jenm/
1. (Page lookups/sec) / (Batch Requests/sec) < 100
привышение этого соотношения свидетельствует о слишком частых обращениях к BuferPool и высоких значения логического ввода/вывода. Происходит из-за не эфективных запросов.
2. (Page Splits/sec) / (Batch Requests/sec) < 0.2
большое значение этого соотношения свидетельствует о слишком частых переполнениях индексных страниц. Исправить ситуацию можно увеличением fillfactor.
3. (Index Searches/sec)/(Full Scans/sec) > 1000
если маленикие значения этого показателя не сопровождаются нагрузкой на CPU это может свидетельствовать о том что Full Scans происходят на маленьких таблицах, что вполне допустимо.
Частые сканирования больших таблиц происходят из-за недостающих индексов и слишком большом количестве запрашиваемых данных.
4. (Total Latch Wait Time) / (Latch Waits/Sec) < 10
средняя продолжительность ожидания Latch
5. (SQL Compilations/sec) / (Batch Requests/sec) < 0.2
большая величина этого отношения свидетельствует о частых adhoc запросах, что может приводить к перегрузке CPU. Улучшение этого показателя достигается использование хранимых процедур и правильным использованием sp_executeSQL.
6. (Forwarded requests/sec) / (Batch Requests/sec) < 0.1
SQL Server - Performance Counter Guidance
Understanding SQL Performance Counters
Finding performance bottlenecks and their resolutions in windows services
How to troubleshoot SQL Server performance problems by using Perfmon
SQL Server Performance Assessment and Optimization Techniques
Performance Counters - Analysis
http://weblogs.sqlteam.com/jenm/
понедельник, 21 июня 2010 г.
Чем отличается композитный индекс от индекса с включенными столбцами (included columns)
Composite Index vs. INCLUDE Covering Index
Выводы из статьи:
- Индекс с включенными столбцами занимает меньше места (Т.к. содержит данные дополнительных столбцов только на листовом уровне).
- На всключенные столбцы не распространяется ограничение на суммарный размер (для композитных индексов максимальный сумарный размер полей 900 байт)
- Обновление "включенных" полей не приводит к фрагментации индекса
-
Выводы из статьи:
- Индекс с включенными столбцами занимает меньше места (Т.к. содержит данные дополнительных столбцов только на листовом уровне).
- На всключенные столбцы не распространяется ограничение на суммарный размер (для композитных индексов максимальный сумарный размер полей 900 байт)
- Обновление "включенных" полей не приводит к фрагментации индекса
-
пятница, 18 июня 2010 г.
Extended Events
(sqlteam.com) Introduction to SQL Server 2008 Extended Events
(technet.microsoft.com) Advanced Troubleshooting with Extended Events
(msdn.microsoft.com) Using SQL Server 2008 Extended Events
(blog.sqlauthority.com) SQL SERVER – Introduction to Extended Events – Finding Long Running Queries
(mssqltips.com) SQL Server Tempdb Usage and Bottlenecks tracked with Extended Events
(sql-server-performance.com) Light Weight Monitoring using Extended Events
(technet.microsoft.com) Advanced Troubleshooting with Extended Events
(msdn.microsoft.com) Using SQL Server 2008 Extended Events
(blog.sqlauthority.com) SQL SERVER – Introduction to Extended Events – Finding Long Running Queries
(mssqltips.com) SQL Server Tempdb Usage and Bottlenecks tracked with Extended Events
(sql-server-performance.com) Light Weight Monitoring using Extended Events
воскресенье, 13 июня 2010 г.
Большие базы данных
Большими базами данных называют БД которые имеют большое число записей (порядка нескольких миллиардов) или занимают большое количество дискового пространства (более одного терабайта).
Рассмотрим некоторые особенности которые характерны для таких БД.
- из-за большого размера БД процедуры контроля целостности (DBCC) выполняются очень долго, поэтому их нельзя использовать ежедневно.
- для экономии дискового пространства следует использовать бекапы отдельных файловых групп
- если в запросе осуществляется соединение (join) нескольких больших таблиц, эти таблицы должны располагаться на разных физических дисках.
- опции "automatically grow file" и "auto shrink" должны быть отключены т.к. их выполнение приводит к высокой нагрузке на диски.
- для уменьшения количества соединений больших таблиц, используется денормализация данных (поля таблиц реазующие денормализацию могут заполняться периодически запускаемыми заданиями (jobs))
- использование партиционирования
Ссылки по теме:
Quick list of VLDB maintenance best practices
VLDB Tips
Some VLDB Availability Tidbits
Partial Database Availability
VLDB Performance Tuning and Optimization
SQL Server and the VLDB: Playing with the Big Boys
Example corrupt database to play with and some backup/restore things to try
Рассмотрим некоторые особенности которые характерны для таких БД.
- из-за большого размера БД процедуры контроля целостности (DBCC) выполняются очень долго, поэтому их нельзя использовать ежедневно.
- для экономии дискового пространства следует использовать бекапы отдельных файловых групп
- если в запросе осуществляется соединение (join) нескольких больших таблиц, эти таблицы должны располагаться на разных физических дисках.
- опции "automatically grow file" и "auto shrink" должны быть отключены т.к. их выполнение приводит к высокой нагрузке на диски.
- для уменьшения количества соединений больших таблиц, используется денормализация данных (поля таблиц реазующие денормализацию могут заполняться периодически запускаемыми заданиями (jobs))
- использование партиционирования
Ссылки по теме:
Quick list of VLDB maintenance best practices
VLDB Tips
Some VLDB Availability Tidbits
Partial Database Availability
VLDB Performance Tuning and Optimization
SQL Server and the VLDB: Playing with the Big Boys
Example corrupt database to play with and some backup/restore things to try
среда, 9 июня 2010 г.
Подписаться на:
Сообщения (Atom)
