MSSQL: Maintaining SQL DB Statistics.

MSSQL: Maintaining SQL DB Statistics.
May 22, 2019.

**Notes: All queries have been run and validated in MSSQL 2008/2012/2017.

Statistics are a description of the distribution of data in a column or index.

The query optimizer relies heavily on statistics, so if the stats do not represent what you are looking for very well then the optimizer
can create an inefficient query plan. The optimizer’s query plan is determined before execution, and once the plan is made the execution must either complete or be cancelled. (In other words, there is no mechanism by which after running for a while SQL can see that the plan it made is based on bad statistics and start over.)

Knowing how to evaluate the freshness of your stats sure makes troubleshooting easier when problems occur.

The query below will look at all statistics in the current database and for each one show you the time at which statistics were last updated, as well as the approximate number of rows that have been changed in that statistic since the last update.

SELECT DISTINCT
tablename=object_name(i.object_id)
, o.type_desc
,index_name=i.[name]
, statistics_update_date = STATS_DATE(i.object_id, i.index_id)
, si.rowmodctr
FROM sys.indexes i (nolock)
JOIN sys.objects o (nolock) on
i.object_id=o.object_id
JOIN sys.sysindexes si (nolock) on
i.object_id=si.id
and i.index_id=si.indid
where
o.type = ‘S’
–ignore system objects
and STATS_DATE(i.object_id, i.index_id) is not null
order by si.rowmodctr desc

Updating Stats Using T-SQL:

Update statistics for an index
USE AdventureWorks;
GO
UPDATE STATISTICS Adventureworks. ;
GO

Update statistics for a table
USE AdventureWorks;
GO
UPDATE STATISTICS Adventureworks.;
GO

Update all statistics
USE AdventureWorks
GO
EXEC sp_updatestats;
GO
==================================================
Prakash

MSSQL ERROR 15141: The server principal owns an events notification and cannot be dropped.(Microsoft Sql Server)

MSSQL ERROR 15141: The server principal owns an events notification and cannot be dropped.(Microsoft Sql Server,
Error : 15141)
May 18 2019.

Problem: As a SQl admin you are trying to drop a user from instance logins.
SQL Server Instance—Security–Login–Highlight a user to drop—delete.

The SQL Server errors out with 15141 code.

Solution:
The error just means that the particular user, you are trying to drop, has event notifications enabled/active.
Run this query:

SELECT
s.name AS ‘EventNotificationName’
, p.name AS ‘PrincipalName’
, ‘DROP EVENT NOTIFICATION ‘+s.name+’ ON SERVER;’ AS ‘DropEventNotificationQuery’
FROM sys.server_event_notifications s
LEFT JOIN sys.server_principals p
ON s.principal_id = p.principal_rror
WHERE p.name = ‘domain\user’;

replace domain\user with your domain and user.

Should give you the broker processes the user has notifications enabled and the last column will give you the SQL statement to drop the notifications.
Once that is done you should be able to Drop the login.

Prakash.

MSSQL: Finding out TCP/IP ports a MSSQL Server Instance is running under.

Finding out what ports a MSSQL Server Instance is Configured.
May 5, 2019

1: SELECT DISTINCT local_net_address, local_tcp_port
FROM sys.dm_exec_connections
WHERE local_net_address IS NOT NULL

2: Execute below script if SQL Server is configured with dynamic ports.
SELECT local_tcp_port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID


3: Execute below script if SQL Server is configured with static port number

DECLARE       @portNo   NVARCHAR(10)

EXEC   xp_instance_regread
  @rootkey    = ‘HKEY_LOCAL_MACHINE’,
  @key        =
‘Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll’,
  @value_name = ‘TcpPort’,
  @value      = @portNo OUTPUT

SELECT [PortNumber] = @portNo

Any one of the above will get you the port information.

Prakash