Base de Données SQL Server
Modification des options et reconfiguration forcée du serveur
EXEC sp_configure 'show advanced options', 1RECONFIGURE WITH OVERRIDEEXEC sp_configure 'xp_cmdshell', 1RECONFIGURE WITH OVERRIDE
Detection des verrous
{{tag>SQL Langage Requete DB Base_de_Donnees}}
====== Data Compression ======
===== Liste des tables d'une base, avec compression et filegroup ====
<code>SELECT
st.object_id,
st.name,
CASE
WHEN data_compression <> 0 THEN 'Compressée'
ELSE 'Brute'
END as 'Stockage',
sp.data_compression_desc,
-- sp.partition_id,
-- sp.partition_number,
--sp.data_compression,
FG.name
FROM sys.partitions SP
INNER JOIN sys.tables ST ON
st.object_id = sp.object_id
INNER JOIN sys.indexes SI ON
si.object_id = sp.object_id
INNER JOIN sys.filegroups FG ON FG.data_space_id = SI.data_space_id
order by 2
</code>
===== Liste des tables compressées d'une base =====
<code>
SELECT
st.object_id,
st.name,
CASE
WHEN data_compression <> 0 THEN 'Compressée'
ELSE 'Brute'
END as 'Stockage',
sp.data_compression_desc,
-- sp.partition_id,
-- sp.partition_number,
sp.data_compression
FROM sys.partitions SP
INNER JOIN sys.tables ST ON
st.object_id = sp.object_id
order by 2
</code>
===== Estimation de la compression sur les tables d'une base =====
KO
<code>
SET NOCOUNT ON
DECLARE @TableName varchar(128)
DECLARE TablesCursor CURSOR FOR
SELECT
name
FROM
sysobjects
WHERE
type='U'
AND
name LIKE '%'
ORDER BY
name ASC
OPEN TablesCursor
FETCH NEXT FROM TablesCursor
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
exec sp_estimate_data_compression_savings 'dbo', @TableName, NULL, NULL, 'PAGE'
FETCH NEXT FROM TablesCursor
INTO @TableName
END
CLOSE TablesCursor
DEALLOCATE TablesCursor
--exec sp_estimate_data_compression_savings 'dbo', HISTO_GPPARC_PCA_TOUS_JOURS, NULL, NULL, 'PAGE'
</code>
===== Liens =====
* [[http://msdn.microsoft.com/fr-fr/library/cc280574.aspx|Estimation du gain lié à la compression]]
* [[http://msdn.microsoft.com/en-us/library/dd894051%28v=sql.100%29.aspx|Data Compression: Strategy, Capacity Planning and Best Practices]]
====== SQL Data Usage ======
===== Liste de la taille des tables d'une base de données =====
<code>
SET NOCOUNT ON
DECLARE @TableId INT,
@pages bigint -- Working variable for size calc.
,@dbname sysname
,@dbsize bigint
,@logsize bigint
,@reservedpages bigint
,@usedpages bigint
,@rowCount bigint
DECLARE @TablesInfo TABLE
(
TableName nvarchar(128), -- Name of the object for which space usage information was requested.
NbRows char(11), -- Number of rows existing in the table. If the object specified is a Service Broker queue, this column indicates the number of messages in the queue.
ReservedSpace varchar(18), -- Total amount of reserved space for objname.
TotalSpace varchar(18), -- Total amount of space used by data in objname.
IndexSize varchar(18), -- Total amount of space used by indexes in objname.
UnusedSpace varchar(18) -- Total amount of space reserved for objname but not yet used.
)
DECLARE TablesCursor CURSOR FOR
SELECT
id
FROM
sysobjects
WHERE
type='U'
AND
name LIKE '%'
ORDER BY
name ASC
OPEN TablesCursor
FETCH NEXT FROM TablesCursor
INTO @TableId
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT
@reservedpages = SUM (reserved_page_count),
@usedpages = SUM (used_page_count),
@pages = SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
),
@rowCount = SUM (
CASE
WHEN (index_id < 2) THEN row_count
ELSE 0
END
)
FROM sys.dm_db_partition_stats
WHERE object_id = @TableId;
IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @TableId AND internal_type IN (202,204,211,212,213,214,215,216)) > 0
BEGIN
/*
** Now calculate the summary data. Row counts in these internal tables don't
** contribute towards row count of original table.
*/
SELECT
@reservedpages = @reservedpages + sum(reserved_page_count),
@usedpages = @usedpages + sum(used_page_count)
FROM sys.dm_db_partition_stats p, sys.internal_tables it
WHERE it.parent_id = @TableId AND it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id;
END
INSERT INTO
@TablesInfo
SELECT
OBJECT_NAME(@TableId),
rows = convert (char(11), @rowCount),
reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'),
data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'),
index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),
unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')
FETCH NEXT FROM TablesCursor
INTO @TableId
END
CLOSE TablesCursor
DEALLOCATE TablesCursor
SELECT * FROM @TablesInfo
</code>
Remarks
database_size will always be larger than the sum of reserved + unallocated space because it includes the size of log files, but reserved and unallocated_space consider only data pages.
Pages that are used by XML indexes and full-text indexes are included in index_size for both result sets. When objname is specified, the pages for the XML indexes and full-text indexes for the object are also counted in the total reserved and index_size results.
If space usage is calculated for a database or an object that has a spatial index, the space-size columns, such as database_size, reserved, and index_size, include the size of the spatial index.
When updateusage is specified, the SQL Server Database Engine scans the data pages in the database and makes any required corrections to the sys.allocation_units and sys.partitions catalog views regarding the storage space used by each table. There are some situations, for example, after an index is dropped, when the space information for the table may not be current. updateusage can take some time to run on large tables or databases. Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.
===== Liste de la taille des index d'une base =====
<code>
SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM
sys.indexes AS i
JOIN
sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN
sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY
i.OBJECT_ID,i.index_id,i.name
order by 4 desc
--ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id
</code
====== Usage ==
===== Fonctionnement correct des vues =====
<code>
SET NOCOUNT ON
DECLARE @name varchar(255), @exist bit
DECLARE vue_cursor CURSOR FOR
SELECT
name
FROM
sysobjects
WHERE
type='V'
ORDER BY
name
OPEN vue_cursor
FETCH NEXT FROM vue_cursor
INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Test pour la vue ' + @name
exec('select top 1 ' + @name + ' , * from ' + @name)
FETCH NEXT FROM vue_cursor
INTO @name
END
CLOSE vue_cursor
DEALLOCATE vue_cursor
</code>
===== Percent of Update Operations on the Object =====
<code>
/*
U: Percent of Update Operations on the Object
The percentage of update operations on a specific table, index, or partition, relative to total operations on that object.
The lower the value of U (that is, the table, index, or partition is infrequently updated), the better candidate it is for page compression.
*/
SELECT o.name AS [Table_Name], x.name AS [Index_Name],
i.partition_number AS [Partition],
i.index_id AS [Index_ID], x.type_desc AS [Index_Type],
i.leaf_update_count * 100.0 /
(i.range_scan_count + i.leaf_insert_count
+ i.leaf_delete_count + i.leaf_update_count
+ i.leaf_page_merge_count + i.singleton_lookup_count
) AS [Percent_Update]
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i
JOIN sys.objects o ON o.object_id = i.object_id
JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id
WHERE (i.range_scan_count + i.leaf_insert_count
+ i.leaf_delete_count + leaf_update_count
+ i.leaf_page_merge_count + i.singleton_lookup_count) != 0
AND objectproperty(i.object_id,'IsUserTable') = 1
ORDER BY [Percent_Update] ASC
</code>
===== Percent of Scan Operations on the Object =====
<code>
/*
S: Percent of Scan Operations on the Object
To compute S, use the statistics in the DMV sys.dm_db_index_operational_stats13. S is the ratio (expressed in percent)
of scans performed on a table or index to the sum of all operations (scans + DMLs + lookups) on that table or index.
In other words, S represents how heavily the table or index is scanned.
The following query reports S for each table, index, and partition in the database.
*/
SELECT o.name AS [Table_Name], x.name AS [Index_Name],
i.partition_number AS [Partition],
i.index_id AS [Index_ID], x.type_desc AS [Index_Type],
i.range_scan_count * 100.0 /
(i.range_scan_count + i.leaf_insert_count
+ i.leaf_delete_count + i.leaf_update_count
+ i.leaf_page_merge_count + i.singleton_lookup_count
) AS [Percent_Scan]
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i
JOIN sys.objects o ON o.object_id = i.object_id
JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id
WHERE (i.range_scan_count + i.leaf_insert_count
+ i.leaf_delete_count + leaf_update_count
+ i.leaf_page_merge_count + i.singleton_lookup_count) != 0
AND objectproperty(i.object_id,'IsUserTable') = 1
ORDER BY [Percent_Scan] DESC
</code>
Commentaires
Enregistrer un commentaire