Finding SYSADMINS on your SQL Server

SELECT a.[name]

      ,CASE WHEN (IS_SRVROLEMEMBER('sysadmin', a.[name])) = 1 THEN 'YES'  

         ELSE 'NO' END AS [is_sysadmin]

      ,CASE WHEN (IS_SRVROLEMEMBER('securityadmin', a.[name])) = 1 THEN 'YES' 

         ELSE 'NO' END AS [is_securityadmin]    

      ,a.[type_desc]

      ,a.[is_disabled]

      ,a.[default_database_name]

      ,b.[createdate]

FROM [master].[sys].[server_principals] a

INNER JOIN [master].[sys].[syslogins] b

ON a.[name] = b.[name]

WHERE a.[type] NOT IN ('R','C')

AND a.[is_disabled] = 0

ORDER BY [is_sysadmin] DESC, [type_desc], [name]

;

Add Primary Key To All Tables Using The Index Keys From Clustered Unique Indexes

I needed to add a Primary Key to each of my tables within my Coda Financials Database. This was required so I could institute replication...

Fortunately each of the tables I required for replication have Unique Clustered Indexes on them. This obviously meant I could add a Primary Key to each of these tables using the Index Key Columns from these Unique Clustered Indexes.

I wanted to go down this route because the application checksums all rows in each table, so if I added an extra Primary Key column it would mean having to run Coda's FixCheck application over all the tables. This way I don't need to do this.

I came up with the below script to save me writing each individual ALTER Table statement:

DECLARE @IndexInfoTemp TABLE

(

index_name VARCHAR(250)

,index_description VARCHAR(250)

,index_keys VARCHAR(250)

)

DECLARE @IndexInfo TABLE

(

table_name SYSNAME

,index_name VARCHAR(250)

,index_description VARCHAR(250)

,index_keys VARCHAR(250)

)

DECLARE @Tables TABLE

(

rowid INT NOT NULL IDENTITY(1, 1)

,tablename SYSNAME

)

DECLARE @MaxRow INT

DECLARE @CurrentRow INT

DECLARE @CurrentTable SYSNAME

INSERT INTO @Tables

SELECT DISTINCT t.name

FROM sys.indexes i

INNER JOIN sys.tables t

ON i.object_id = t.object_id

WHERE i.name IS NOT NULL

SELECT @MaxRow = @@ROWCOUNT

,@CurrentRow = 1

WHILE @CurrentRow <= @MaxRow

BEGIN

SELECT @CurrentTable = tablename

FROM @Tables

WHERE rowid = @CurrentRow

INSERT INTO @IndexInfoTemp

EXEC Sp_helpindex

@CurrentTable

INSERT INTO @IndexInfo

(table_name

,index_name

,index_description

,index_keys)

SELECT @CurrentTable

,index_name

,index_description

,index_keys

FROM @IndexInfoTemp

DELETE FROM @IndexInfoTemp

SET @CurrentRow=@CurrentRow + 1

END --WHILE

SELECT table_name

,index_name

,index_description

,index_keys

,'ALTER TABLE ' + table_name

+ ' ADD CONSTRAINT pk_' + table_name

+ ' PRIMARY KEY(' + index_keys + ');' AS [Create Primary Key]

FROM @IndexInfo

WHERE index_description LIKE 'clustered, unique%'

AND table_name <> 'bi_params_transitionloading'

ORDER BY table_name;

Processor Queue Length on SQL2005 and above...

The processor queue length is becoming less important as a SQL Server performance metric since SQL 2005.

In SQL 2005 and above the SQL server does not queue its work to the Windows Op System, it now has a user mode scheduler which is where it queues tasks that are waiting.

The best way to view the number of tasks queued in SQL Server is to use the DMV sys.dm_os_schedulers:

SELECT AVG([runnable_tasks_count])
FROM [sys].[dm_os_schedulers]
WHERE [status] = 'VISIBLE ONLINE';

This should return zero, CPU pressure would show if this is NOT zero consistently.

Last Running Query Based on SPID

Got this from Pinal Dave's Blog. Very useful Blog:

http://blog.sqlauthority.com

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = [SPID ID you wish to query]
SELECT TEXT, USER
FROM sys.dm_exec_sql_text(@sqltext);

In the future sys.sysprocesses is going to depreciated by Microsoft in favour of the System DMV's. 

Unfortunately unlike sys.sysprocesses, sys.dm_exec_requests only holds sql handles for active sessions. Microsoft have advised that deprecation of sys.sysprocesses will not happen until ALL functionality can be replaced by the DMV's, and clearly there is still some way to go....

Once the sys.dm_exec_requests DMV is fixed so it holds the handles for all sessions I would say that the above could be changed to:

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.dm_exec_requests
WHERE session_id = [SPID ID you wish to query]
SELECT TEXT, USER
FROM sys.dm_exec_sql_text(@sqltext);

Useful Stored Procedures for Linked Servers

sp_tables_ex
Parameters

• @table_server
• @table_name
• @table_schema
• @table_catalog
• @table_type

sp_columns_ex
Parameters

• @table_server
• @table_name
• @table_schema
• @table_catalogue
• @column_name

sp_help is also a very useful system stored procedure
Parameters

•  @objname