Muller Jannie

About myself and everyone else.

Category Archives: SQL Server

DBA Ramblings from 2013

DBA In training My IT career started when I joined a small IT firm that developed web based applications. The applications were written like most other applications in at the … Continue reading

August 17, 2018 · Leave a comment

Get out of date stats

SELECT DISTINCT SCHEMA_NAME(so.schema_id) AS ‘SchemaName’, OBJECT_NAME(so.object_id) AS ‘TableName’,so.object_id AS ‘object_id’,CASE OBJECTPROPERTY(MAX(so.object_id), ‘TableHasClustIndex’) WHEN 1 THEN ‘Clustered’ WHEN 0 THEN ‘Heap’ ELSE ‘Indexed View’ END AS ‘ClusteredHeap’,CASE objectproperty(max(so.object_id), ‘TableHasClustIndex’) WHEN 0 … Continue reading

August 17, 2018 · Leave a comment

Disk Usage with HADR

if object_id(‘tempdb..##DiskUsage’) is not null drop table tempdb..##DiskUsage Create table ##DiskUsage ( databasename varchar(256), filegroupname varchar(56), physical_name varchar(256), [filename] varchar(256), datafilesizemb int, datausedsizemb int, datafilefreemb int, diskcapacitymb int, diskfreespacemb int, … Continue reading

August 17, 2018 · Leave a comment

Index Operational Info

Select @@servername as servername, db_name() as databasename, object_name(b.object_id,db_id()) as objectname, object_schema_name(b.object_id,db_id()) as schemaname, isnull(b.name,’HEAP’) as indexname, isnull(user_seeks,0) as user_seeks, isnull(user_scans,0) as user_scans, isnull(user_lookups,0) as user_lookups, isnull(user_updates,0) as user_updates, d.rows, d.data_compression_desc, … Continue reading

August 17, 2018 · Leave a comment

Database FileSize

with recent_DatabaseFileSize as ( select rank() over(partition by databaseid order by snapshotdate desc ) as recent,* from snapshot.DatabaseFileSize WITH (NOLOCK)), Growth_On_Disk as ( select a.name, a.label, min(a.used – b.used) as … Continue reading

August 17, 2018 · Leave a comment

Disk space report

with recent_DatabaseFileSize as ( select rank() over(partition by databaseid order by snapshotdate desc ) as recent,* from snapshot.DatabaseFileSize WITH (NOLOCK)), Growth_On_Disk as ( select a.name, a.label, min(a.used – b.used) as … Continue reading

August 17, 2018 · Leave a comment

Get IO Rank

SELECT db_name(a.database_id) as dbname, b.physical_name, –Server stats rank() over (partition by @@servername order by num_of_bytes_read desc ) as rank_num_of_bytes_read, rank() over (partition by @@servername order by num_of_bytes_written desc ) as … Continue reading

August 17, 2018 · Leave a comment

What is using my index

select        convert(varchar(max),query_plan),        object_name(object_id),        *        from sys.dm_exec_procedure_stats a        cross apply sys.dm_exec_query_plan(a.plan_handle)        where object_name(object_id) like ‘test%’        and convert(varchar(max),query_plan) like ‘%IndexScan Lookup=”1″%Index=”[[]PK__test__357D0D3E096A3B7C]”%’

August 17, 2018 · Leave a comment

Data Churn

select @@servername as servername,               db_name(database_id) as database_name,               object_name(a.object_id,a.database_id)  as object_name,               b.rows,               a.leaf_insert_count As inserts_since_restart,               a.leaf_update_count As updates_since_restart,               a.leaf_delete_count + a.leaf_ghost_count as delete_since_restart,               convert(money,((leaf_insert_count)  … Continue reading

August 17, 2018 · Leave a comment

Get Parallel Plans

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; WITH XMLNAMESPACES (DEFAULT ‘http://schemas.microsoft.com/sqlserver/2004/07/showplan’) SELECT query_plan AS CompleteQueryPlan, n.value(‘(@StatementText)[1]’, ‘VARCHAR(4000)’) AS StatementText, n.value(‘(@StatementOptmLevel)[1]’, ‘VARCHAR(25)’) AS StatementOptimizationLevel, n.value(‘(@StatementSubTreeCost)[1]’, ‘VARCHAR(128)’) AS StatementSubTreeCost, n.query(‘.’) AS ParallelSubTreeXML, ecp.usecounts, ecp.size_in_bytes FROM sys.dm_exec_cached_plans AS ecp CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp CROSS APPLY query_plan.nodes(‘/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple’) AS qn(n) WHERE  n.query(‘.’).exist(‘//RelOp[@PhysicalOp=”Parallelism”]’) = 1 

August 17, 2018 · Leave a comment
Advertisements