Last active
October 20, 2015 17:51
-
-
Save robsonalves/931eb5f0ac42c148b758 to your computer and use it in GitHub Desktop.
Rotinas de sql para aprimorar performance na VM Azure
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--==================================================================== | |
-- DAC Connection em caso de emergência e o banco ficar travado, o usuário Admin não trava -- | |
--==================================================================== | |
/* | |
sp_configure 'remote admin connections', 1; | |
GO | |
RECONFIGURE; | |
GO | |
*/ | |
--==================================================================== | |
-- #### Select Collations BD ### --- | |
-- Manter sempre a mesma collation do tempdb -- | |
--==================================================================== | |
/* | |
SELECT name, collation_name FROM sys.databases; | |
*/ | |
--==================================================================== | |
-- Retorna o valor de fill factory dos indixes existentes | |
--==================================================================== | |
/* | |
SELECT DB_NAME() AS DataBaseName, | |
schema_name(o.schema_id) as schemaName | |
, OBJECT_NAME(s.[object_id]) as TableName | |
, i.name as IndexName | |
, i.fill_factor | |
from sys.dm_db_index_usage_stats s | |
inner join sys.indexes i on s.[object_id] = i.[object_id] and s.index_id = i.index_id | |
inner join sys.objects o on i.object_id = o.object_id | |
where s.database_id = db_id() | |
and i.name is not null | |
and OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0 | |
order by fill_factor desc | |
*/ | |
--==================================================================== | |
-- Altera o caminho do tempdb para a pasta a seguir (SSD) | |
-- Existe uma rotina interna via power shell e schedule do windows | |
-- para recriar essa pasta nova sempre que o windows for reiniciado | |
-- pois o diretório D é temporário. | |
--==================================================================== | |
/* | |
alter database tempdb modify file (name=tempdev, filename='D:\SQLTEMP\tempdb.mdf') | |
alter database tempdb modify file (name=templog, filename='D:\SQLTEMP\templog.ldf') | |
ALTER SERVER CONFIGURATION | |
SET BUFFER POOL EXTENSION ON | |
( FILENAME = 'D:\SQLTEMP\ExtensionFile.BPE' , SIZE = 168GB ) | |
*/ | |
--sys.dm_io_virtual_file_stats(db_id(N'Master'),2); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment