SQLDBA - finding out dated statics

-

 -- Script - Find Details for Statistics of Whole Database

SELECT DISTINCT

OBJECT_NAME(s.[object_id]) AS TableName,

c.name AS ColumnName,

s.name AS StatName,

STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated,

DATEDIFF(d,STATS_DATE(s.[object_id], s.stats_id),getdate()) DaysOld,

dsp.modification_counter,

s.auto_created,

s.user_created,

s.no_recompute,

s.[object_id],

s.stats_id,

sc.stats_column_id,

sc.column_id

FROM sys.stats s

JOIN sys.stats_columns sc

ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id

JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id

JOIN sys.partitions par ON par.[object_id] = s.[object_id]

JOIN sys.objects obj ON par.[object_id] = obj.[object_id]

CROSS APPLY sys.dm_db_stats_properties(sc.[object_id], s.stats_id) AS dsp

WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1

AND (s.auto_created = 1 OR s.user_created = 1)

ORDER BY DaysOld;



--Script 2: Update Statistics for Database


--EXEC sp_updatestats;

--GO

Comments

Post a Comment

Popular posts from this blog

DBA- MAXDOP

-
Image
  Have your SQL queries been eating up CPU like it's a buffet? Then it’s time to talk about the most underrated setting in SQL Server:  MAXDOP . Let’s break it down in simple, real-world terms — with best practices, optimized queries, and use cases that matter. What is MAXDOP? MAXDOP stands for Maximum Degree of Parallelism — it controls how many CPU cores SQL Server uses to run a single query in parallel. Sounds powerful? It is — but misconfigured, it can hurt more than help. Too low? Queries run slower, underutilizing hardware. Too high? CPU gets overwhelmed, blocking other tasks. 🔧  Where to Configure MAXDOP : Instance Level (Recommended for Most) EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'max degree of parallelism', 4; RECONFIGURE; Database Level (Overrides Instance Setting) ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2; Query Level (...
Read more

DBA - Check health and status of Always On or Availability Group using DMVs

-
 -- Health and status of WSFC cluster. These two queries work only if the WSFC has quorum SELECT * FROM sys.dm_hadr_cluster SELECT * FROM sys.dm_hadr_cluster_members -- Health of the AGs SELECT ag.name agname, ags.* FROM sys.dm_hadr_availability_group_states ags  INNER JOIN sys.availability_groups ag ON ag.group_id = ags.group_id -- Health and status of AG replics from the WsFC perspective SELECT ar.replica_server_name,harc.* FROM sys.dm_hadr_availability_replica_cluster_states harc  INNER JOIN sys.availability_replicas ar ON ar.replica_id = harc.replica_id -- Health and status of AG replicas, run this on the primary replica.  -- On secondary this will only show info for that instance SELECT * FROM sys.dm_hadr_availability_replica_states  -- Health and status of AG databases from the WSFC perspective SELECT * FROM sys.dm_hadr_database_replica_cluster_states  -- Health and status of AG databases, run this on the primary replica.  -- On secondary this wi...
Read more

DBA - Script out the Linked server objects.

-
  Script out the Linked server objects.        CREATE  PROCEDURE [dbo].[win_linked_server_objects_extract_select]     AS       /***********************************************************************************************************    --Purporse: To extract Linkded server connection details      --Created By: kovvuru subbareddy        Purpose:    TEST:        EXEC [dbo].[win_linked_server_objects_extract_select]       ***********************************************************************************************************/     BEGIN   SET NOCOUNT ON      CREATE TABLE #temp_linkedserver_detail   ( ID INTEGER identity(1,1) primary key NOT NULL,   create_linked_server_command VARCHAR(5000),   linked_server_name varchar(100) ...
Read more