How to: Verifying CPU Pressure via Signal Wait Time

Use the following query to verify the CPU pressure via signal wait time through MS SQL Server Management Studio.

USE master
GO
SELECT SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,
( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))
/ SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 )
AS PercentageSignalWaitsOfTotalTime
FROM sys.dm_os_wait_stats

References

How to: Find a Table on a SQL Server across all Databases

Some times it is difficult to find a table name from a SQL instance that hosted tremendous database.  “Which database I suppose to search for?” is the question that often pop-up in DBA’s mind and they hope the user can give them the database name as well.  Sadly speaking in most cases user do not know which database their application connect to.

To find a table in the SQL across all databases you can use undocumented stored procedure sp_MSForEachDB.

sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''%your_table_name%'''

Script to Check TempDB Speed

The following script adopted from Brent Ozar’s blog on his TempDB Performance and Configuration topic. I strongly encourage you to pay a visit to his site.

This query hits the dynamic management function (DMF) sys.dm_io_virtual_file_stats for all of the TempDB data files and lists out how fast they’re responding to write (and read) requests:

SELECT files.physical_name, files.name,
    stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,
    stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
INNER JOIN master.sys.master_files AS files
    ON stats.database_id = files.database_id
    AND stats.file_id = files.file_id
WHERE files.type_desc = 'ROWS'

Brent Ozar suggested to look for two things from the result generate from the script:

  • Are writes being evenly distributed between data files?
  • Are writes finishing in 20ms or less?

If either of those question is no, then we have some performance tuning work need to kick in.

How to: Check If xp_cmdshell is Enabled

“xp_cmdshell” allow us to run windows commands by using SQL Server but first we need to enable it. We can check whether the “xp_cmdshell” configuration is enabled from the system table sys.configurations which contains server-wide configuration option value in the system.

Execute the following T-SQL will allow you to check if “xp_cmdshell” is enabled on a server. You may need to manually enable the “xp_cmdshell” function if the return result is not “1”.

SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value
FROM  sys.configurations
WHERE  name = N'xp_cmdshell' ;

Enable “xp_cmdshell”

Execute the following T-SQL to enable the “xp_cmdshell” configuration.

--Enable Change in Advanced Options
EXEC SP_CONFIGURE N'show advanced options', 1
GO
RECONFIGURE
GO
--Enable XP_cmdshell
EXEC SP_CONFIGURE N'xp_cmdshell', 1
GO
RECONFIGURE
GO
--Disable Changes in Advanced Options
EXEC SP_CONFIGURE N'show advanced options', 0
GO
RECONFIGURE
GO

How to: Find Who Deleted the Database or Table

Found a very useful script from SQLServerCentral Forums that it will help you to easily identify who is the person that had deleted your database or table.

With cteObjectTypes AS
    (
    SELECT
        TSV.trace_event_id,
        TSV.subclass_name,
        TSV.subclass_value
    FROM
        sys.trace_subclass_values AS TSV JOIN
        sys.trace_columns AS TC ON
            TSV.trace_column_id = TC.trace_column_id 
    WHERE
        TC.[name] = 'ObjectType'
    ),
    cteEventSubClasses AS
    (
    SELECT
        TSV.trace_event_id,
        TSV.subclass_name,
        TSV.subclass_value
    FROM
        sys.trace_subclass_values AS TSV JOIN
        sys.trace_columns AS TC ON
            TSV.trace_column_id = TC.trace_column_id 
    WHERE
        TC.[name] = 'EventSubClass'
    )
SELECT
    TE.[name],
    I.ApplicationName,
    I.BigintData1,
    I.ClientProcessID,
    I.ColumnPermissions,
    I.DatabaseID,
    I.DatabaseName,
    I.DBUserName,
    I.Duration,
    I.EndTime,
    I.Error,
    I.EventSequence,
    Convert(nvarchar(10), I.EventSubClass) + N'-' + ESC.subclass_name as EventSubClass,
    I.FileName,
    I.HostName,
    I.IndexID,
    I.IntegerData,
    I.IsSystem,
    I.LineNumber,
    I.LoginName,
    I.LoginSid,
    I.NestLevel,
    I.NTDomainName,
    I.NTUserName,
    I.ObjectID,
    I.ObjectID2,
    I.ObjectName,
    Convert(nvarchar(10), I.ObjectType) + N'-' + OT.subclass_name as ObjectType,
    I.OwnerName,
    I.ParentName,
    I.Permissions,
    I.RequestID,
    I.RoleName,
    I.ServerName,
    I.SessionLoginName,
    I.Severity,
    I.SPID,
    I.StartTime,
    I.State,
    I.Success,
    I.TargetLoginName,
    I.TargetLoginSid,
    I.TargetUserName,
    I.TextData,
    I.TransactionID,
    I.Type,
    I.XactSequence
FROM
    sys.traces T CROSS Apply 
    sys.fn_trace_gettable((SELECT CONVERT(varchar(100), value)
             FROM sys.fn_trace_getinfo(default)
             WHERE property=2), default) I JOIN
    sys.trace_events AS TE ON 
        I.EventClass = TE.trace_event_id LEFT JOIN
    cteEventSubClasses AS ESC ON
        TE.trace_event_id = ESC.trace_event_id And
        I.EventSubClass = ESC.subclass_value LEFT JOIN
    cteObjectTypes AS OT ON 
        TE.trace_event_id = OT.trace_event_id AND
        I.ObjectType = OT.subclass_value
WHERE
    T.is_default = 1 AND
    TE.NAME = 'Object:Deleted'

Source from SQLServerCentral – Identify who deleted your database or table

How to: Get the Default Backup Directory

The following query will provide you the default backup directory of your SQL Server instance.

-- get the default backup directory
DECLARE    @BackupDirectory varchar(1000)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',@BackupDirectory OUTPUT;

SELECT  @BackupDirectory;

How to: Hide SQL Server User Databases in SSMS

Hiding all user databases for all logins

Suppose you want to hide all databases for all logins. Generally we hide our databases for security purposes. We can run the below statements to hide all databases for all logins. The databases will then only be visible to sysadmin logins or owners of the database.

USE MASTER
GO
DENY VIEW ANY DATABASE TO PUBLIC
GO

Once you run the above statement, you will not be able to see any databases in SQL Server Management Studio unless you are a sysadmin or your login is the owner of a database(s).

References

How to: Start SQL Server Agent When Agent XPs show Disabled

Problem
The SQL Server Agent was found stopped and the status shows “(Agent XPs disabled)” besides the SQL Server Agent, the funny thing is the service was running according to the services console. Tried to start the service from SQL Server Management Studio, but it didn’t work.

This issue appears when ‘Agent XPs’ advance configuration option is disabled and set to 0 for the SQL Server configuration..

Agent XPs is an advanced configuration option which enables the SQL Server Agent extended stored procedures on the server. When this is not enable SQL Server Agent will not be active in SSMS. Most of the time when you start the SQL Server services it automatically enables ‘Agent XPs’, but sometime it fails to enable or sets the value to 0 and then this issue will appear.

Solution
To fix this issue we should first set the ‘Agent XPs’ to 1 and then run RECONFIGURE to bring it into effect. Execute the following query and try to restart the agent service. The SQL Server agent should be online now.

sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Agent XPs',1
go
reconfigure with override
go
sp_configure 'show advanced options',0
go
reconfigure with override
go

This will be the possible output

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

References

http://sqlserverzest.com/2012/09/19/agent-xps-server-configuration-option/