Delete large number of rows
SELECT ‘Starting’ –sets @@ROWCOUNT WHILE @@ROWCOUNT ; 0 DELETE TOP (xxx) MyTable
Entries by alexander
Table size information
SELECT t.NAME AS TableName, i.name as indexName, sum(p.rows) as RowCounts, sum(a.total_pages) as TotalPages, sum(a.used_pages) as UsedPages, sum(a.data_pages) as DataPages, (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, (sum(a.data_pages) * 8) / 1024 as DataSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p […]
Erland Sommarskogs wait_stats_monitor
CREATE PROCEDURE wait_stats_monitor AS SET NOCOUNT ON DECLARE @latest datetime, @now datetime SELECT @now = getdate() IF object_id(‘waitstats’) IS NULL BEGIN CREATE TABLE waitstats ( sample_time datetime NOT NULL, wait_type nvarchar(60) NOT NULL, sample_length_sec int NULL, delta_tasks_count bigint NULL, delta_wait_time bigint […]
Get statements from waiter list
if exists (select * from sys.objects where object_id = object_id(N'[dbo].[get_statements_from_waiter_list]’) and OBJECTPROPERTY(object_id, N’IsProcedure’) = 1) drop procedure [dbo].[get_statements_from_waiter_list] go create proc get_statements_from_waiter_list (@wait_type nvarchar(60)=NULL) as select r.wait_type ,r.wait_time ,SUBSTRING(qt.text,r.statement_start_offset/2, (case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else r.statement_end_offset end -r.statement_start_offset)/2) as query_text ,qt.dbid, dbname=db_name(qt.dbid) ,qt.objectid ,r.sql_handle ,(SELECT query_plan FROM sys.dm_exec_query_plan (r.plan_handle)) as […]
Dude, where’s my disk?
Today I took a look at a clients 10g Oracle RAC environment where one node apparently had started misbehaving quite some time ago. The scenario was that both nodes were up, cluster ready services was up, nodeapps were up, ASM was up but the instance was down. Starting from the top with the database alert […]
Basic settings for SQL Server 2012/2014, part 1
Whenever I set up an SQL Server I usually do the same things. I’ve collected a list of best practice settings over the years (and most often put them in a script, as most of my friends are scripts). Throughout a few blog posts I’ll go over the settings I do use and discuss a […]
Failover on Oracle Standard Edition?
We have all heard (and gnashed our teeth over) that in order to get a proper automatic failover environment for Oracle (i.e Data Guard) we need Enterprise Edition. Oracle Enterprise Edition (or as Brent Ozar so succinctly put it: Expensive Edition) is hideously expensive, especially if you’re only ever going to use the Data Guard […]
Hello world!
So here we are. Again. The blog is live. Hopefully better, stronger and tastier. You be the judge 🙂