Performance
Top 10 Tips for Snowflake Query Optimization
The Snowflake query optimizer implements many advanced query-tuning techniques. However, Snowflake Indexes are not supported on default...
Analyze SQL 2016 new configuration that improves in 40% the performance and index fragmentation analyses to identify poor maintenance
sp_configure
go
SELECT name,compatibility_level FROM sys.databases
go
SELECT @@VERSION
go
EXEC sp_MSforeachdb 'USE [?];SELECT DatabaseName=DB_NAME(), Configuration = name, value FROM sys.database_scoped_configurations'
go
SELECT ...
SQL Server Profile - Collect Triggers
Through the SQL Profile, enable the indicators:
Stored procedures: - SP:StmtStarting - SP:StmtCompleted
SQL Scripts - Best Practices from Microsoft - Performance Analyse
--Collection of Useful T-SQL Scripts for Database Administrators --Most ExecutedSELECT TOP 5 execution_count, Plan_handle, query_plan, TEXTFROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle)ORDER BY...
SQL SERVER – Find Most Expensive Queries Using DMV
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads,&nb
SQL SERVER – Index Levels and Delete Operations – Page Level Observation
USE tempdb
GO
-- Create Table FragTable
CREATE TABLE FragTable (ID CHAR(800),
FirstName CHAR(2000),
LastName CHAR(3000),
City CHAR(2200))
GO
-- Create Clustered Index
CREATE CLUSTERED...
SQL SEVER – Finding Memory Pressure – External and Internal
SELECT TYPE, SUM(single_pages_kb) InternalPressure,SUM(multi_pages_kb) ExtermalPressure
FROM sys.dm_os_memory_clerks
GROUP BY TYPE
ORDER BY SUM(single_pages_kb) DESC, SUM(multi_pages_kb) DESC
GO
Ganhe até 30% de performance dos discos do servidor de banco de dados
Nesse post irei comentar sobre a importância de se fazer o alinhamento dos discos para obter melhor performance com o volume de dados no SQL Server.
Antes de falar sobre a importância do alinhamento de discos, precisamos antes saber algumas nomenclaturas importantes.
Abaixo adicionei...
A DBA guide to SQL Server performance troubleshooting – Part 2 – Monitoring utilities
In this article, we will present native Windows and SQL Server tools commonly used for troubleshooting SQL Server performance issues. Selecting the right performance monitoring utility depends on your monitoring goals and coding knowledge, as the tools provide different information and have...
A DBA guide to SQL Server performance troubleshooting – Part 1 – Problems and performance metrics
Monitoring SQL Server performance is a complex task, as performance depends on many parameters, both hardware and software. It’s recommended to monitor these parameters proactively in order to prevent any potential performance degradation. However, this is not always the case. If the...