T-SQL to Check SQL ErrorLog file location

USEmaster
GO
xp_readerrorlog
0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'

GO

Different ways to check the SQL Server Instance Port number

Problem: If there are multiple SQL instances running on the same computer, it is difficult to identify the instance port number. You can use the below solution to find the instance specific port numbers.

Solution: You can check the list of port number used by the SQL Server instances using one of the below way.

Soln 1# Using SQL Server Configuration Manager

■Go to SQL Server Configuration Manager
■Select Protocols for SQL2005/2008 under SQL server Network Configuration
■Right click on TCP/IP and select Properties
■Select the IP Addresses-tab
■In the section IP ALL, you can see the ports

Soln 2#From Registry Values

 SQL Server 2005
 Type the regedit command in Run window and check the below registry values.HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.#

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ MSSQL.#\ MSSQLServer\ SuperSocketNetLib\TCP\IPAll

SQL Server 2008
 Default instance
 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\TCP\IPAll

Named instance

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.(InstanceName)\MSSQLServer\SuperSocketNetLib\TCP\IPAll

Soln 3# Error Log

Query the error log as below to get the port number.

EXEC xp_readerrorlog 0,1,”Server is listening on”,Null

Enable Mixed Mode Authentication After Installation of MSDE

This step-by-step article describes how to establish a connection to Microsoft Desktop Engine (MSDE).

MSDE uses two authentication modes:

Windows Authentication Mode (Windows Authentication)
In Windows Authentication Mode, a user can connect through a Microsoft Windows NT 4.0, a Microsoft Windows 2000, or a Microsoft Windows XP user account.

Mixed Mode (Windows Authentication and SQL Server Authentication)

In Mixed Mode, users can use either Windows Authentication or SQL Server Authentication to connect to an instance of MSDE. Users who connect through a Windows NT 4.0, a Windows 2000, or a Windows XP user account can use trusted connections in either Windows Authentication Mode or Mixed Mode.

The default authentication mode for MSDE is Windows Authentication. MSDE installs with a built-in system administrator (SA) user account. However, because SQL Server Authentication is disabled by default, you cannot access the built-in account after a typical installation.

By default, the value of the LoginMode Windows registry subkey is set to 1 for Windows authentication. To enable Mixed Mode authentication after installation, you must change this value to 2.

Resolution :

The location of the LoginMode subkey depends on whether you installed MSDE as the default MSDE instance or as a named instance. If you installed MSDE as the default instance, the LoginMode subkey is located in the following registry subkey:

HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode

If you installed MSDE as a named instance, the LoginMode subkey is located in the following registry subkey:

HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode

To change the value of LoginMode to 2, follow these steps:
In Control Panel, open the Services tool to stop MSSQLSERVER and all other related services (such as SQLSERVERAgent)
To open Registry Editor, click Start, click Run, type regedit, and then click OK.

Locate either of the following subkeys (depending on whether you installed MSDE as the default MSDE instance or as a named instance:

HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer\
-or-
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\
In the right pane, double-click the LoginMode subkey.

In the DWORD Editor dialog box, set the value of this subkey to 2, make sure that the Hex option is selected, and then click OK.

Restart the MSSQLSERVER and the SQLSERVERAgent services for this change to take effect.

How to Change Server Authentication Mode

In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.


On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.

To restart SQL Server from SQL Server Management Studio
In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.
To enable the sa login by using Management Studio
In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
On the General page, you might have to create and confirm a password for the sa login.
On the Status page, in the Login section, click Enabled, and then click OK.

To enable the sa login by using Transact-SQL
Execute the following statements to enable the sa password and assign a password.

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '' ;
GO

Last execution time of a stored Procedure

Select DB_Name(dest.[dbid]) As 'databaseName'
    , Object_Name(dest.objectid, dest.[dbid]) As 'procName'
    , Max(deqs.last_execution_time) As 'last_execution'
From sys.dm_exec_query_stats As deqs
Cross Apply sys.dm_exec_sql_text(deqs.sql_handle) As dest
Where dest.[text] Like '%SP_FAST_Style_Colors%' -- replace
    And dest.[dbid] Is Not Null  -- exclude ad-hocs
Group By db_name(dest.[dbid])
    , Object_Name(dest.objectid, dest.[dbid])
Order By databaseName
    , procName
Option (MaxDop 1);

Find lead blocker on a SQL Server Instance

We can use sp_who2 active, sys.dm_exec_requests , sysprocesses or sp_lock to get information about the locks. When there are large number of blocks on a server and one needs to figure out what is the root SPID that is causing all this – We are looking for a lead blocker.

select a.spid, a.blocked, b.spid, b.blocked from sys.sysprocesses a inner join sys.sysprocesses b on b.spid= a.blocked and b.blocked = 0 and a.spid<> a.blocked

Once you have the lead blocker SPID , you can see the statement using DBCC INPUTBUFFER (SPID) or use the below script to get the SQL Text of lead blocker in a single go.

SELECT

  

    spid 

  

    ,sp.status 

  

    ,loginame   = SUBSTRING(loginame, 1, 12) 

  

    ,hostname   = SUBSTRING(hostname, 1, 12) 

  

    ,blk        = CONVERT(char(3), blocked) 

  

    ,open_tran 

  

    ,dbname     = SUBSTRING(DB_NAME(sp.dbid),1,10) 

  

    ,cmd 

  

    ,waittype 

  

    ,waittime 

  

    ,last_batch 

  

    ,SQLStatement       = 

  

        SUBSTRING

  

        ( 

  

            qt.text, 

  

            er.statement_start_offset/2, 

  

            (CASE WHEN er.statement_end_offset = -1 

  

                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 

  

                ELSE er.statement_end_offset 

  

                END - er.statement_start_offset)/2 

  

        ) 

  

FROM master.dbo.sysprocesses sp 

  

LEFT JOIN sys.dm_exec_requests er 

  

    ON er.session_id = sp.spid 

  

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) as qt 

  

WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses) 

  

AND blocked = 0

Identifying Queries with Lowest Plan Reuse

Lowest Plan Reuse

One of the advantages of using stored procedures is that the query plan is cached and can be reused without compiling the query. This saves time, resources, and improves performance. You can identify the query plans that have the lowest reuse to further investigate why the plans are not being reused. You may find that some can be rewritten to optimize reuse.

SELECT TOP 10


[Plan usage] = cp.usecounts

,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

(CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX),

qt.text)) * 2 ELSE qs.statement_end_offset END -

qs.statement_start_offset)/2)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

,cp.cacheobjtype

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

INNER JOIN sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle

WHERE cp.plan_handle=qs.plan_handle

ORDER BY [Plan usage] ASC;

Identifying Queries Most Often Blocked

SELECT TOP 10


[Average Time Blocked] = (total_elapsed_time - total_worker_time) / qs.execution_count

,[Total Time Blocked] = total_elapsed_time - total_worker_time

,[Execution count] = qs.execution_count

,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

(CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Average Time Blocked] DESC;

Identifying Most Costly CLR Queries

SELECT TOP 10


[Average CLR Time] = total_clr_time / execution_count

,[Total CLR Time] = total_clr_time

,[Execution count] = qs.execution_count

,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

(CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats as qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

WHERE total_clr_time <> 0

ORDER BY [Average CLR Time] DESC;

Identifying Most Costly Queries by CPU

SELECT TOP 10


[Average CPU used] = total_worker_time / qs.execution_count

,[Total CPU used] = total_worker_time

,[Execution count] = qs.execution_count

,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

(CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE qs.statement_end_offset END -

qs.statement_start_offset)/2)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Average CPU used] DESC;

Identifying Most Costly Queries by I/O

SELECT TOP 10


[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count

,[Total IO] = (total_logical_reads + total_logical_writes)

,[Execution count] = qs.execution_count

,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

(CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Average IO] DESC;

Identifying Logically Fragmented Indexes

-- Create required table structure only.


-- Note: this SQL must be the same as in the Database loop given in the -- following step.

SELECT TOP 1

DatbaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)

INTO #TempFragmentation

FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.[object_id] = -999 -- Dummy value just to get table structure.

;



-- Loop around all the databases on the server.

EXEC sp_MSForEachDB 'USE [?];

-- Table already exists.

INSERT INTO #TempFragmentation

SELECT TOP 10

DatbaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)

FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.database_id = DB_ID()

AND i.name IS NOT NULL -- Ignore HEAP indexes.

AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0

ORDER BY [Fragmentation %] DESC

;

'



-- Select records.

SELECT TOP 10 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC

-- Tidy up.

DROP TABLE #TempFragmentation

Identifying the Most Costly Indexes

-- Create required table structure only.


-- Note: this SQL must be the same as in the Database loop given in the following step.

SELECT TOP 1

[Maintenance cost] = (user_updates + system_updates)

,[Retrieval usage] = (user_seeks + user_scans + user_lookups)

,DatabaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

INTO #TempMaintenanceCost

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.database_id = DB_ID()

AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0

AND (user_updates + system_updates) > 0 -- Only report on active rows.

AND s.[object_id] = -999 -- Dummy value to get table structure.

;



-- Loop around all the databases on the server.

EXEC sp_MSForEachDB 'USE [?];

-- Table already exists.

INSERT INTO #TempMaintenanceCost

SELECT TOP 10

[Maintenance cost] = (user_updates + system_updates)

,[Retrieval usage] = (user_seeks + user_scans + user_lookups)

,DatabaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.database_id = DB_ID()

AND i.name IS NOT NULL -- Ignore HEAP indexes.

AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0

AND (user_updates + system_updates) > 0 -- Only report on active rows.

ORDER BY [Maintenance cost] DESC

;

'



-- Select records.

SELECT TOP 10 * FROM #TempMaintenanceCost

ORDER BY [Maintenance cost] DESC

-- Tidy up.

DROP TABLE #TempMaintenanceCost

Find out the Unused Indexes

-- Create required table structure only.


-- Note: this SQL must be the same as in the Database loop given in the following step.

SELECT TOP 1

DatabaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

,user_updates

,system_updates

-- Useful fields below:

--, *

INTO #TempUnusedIndexes

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.database_id = DB_ID()

AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0

AND user_seeks = 0

AND user_scans = 0

AND user_lookups = 0

AND s.[object_id] = -999 -- Dummy value to get table structure.

;



-- Loop around all the databases on the server.

EXEC sp_MSForEachDB 'USE [?];

-- Table already exists.

INSERT INTO #TempUnusedIndexes

SELECT TOP 10

DatabaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

,user_updates

,system_updates

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.database_id = DB_ID()

AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0

AND user_seeks = 0

AND user_scans = 0

AND user_lookups = 0

AND i.name IS NOT NULL -- Ignore HEAP indexes.

ORDER BY user_updates DESC

;

'



-- Select records.

SELECT TOP 10 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC

-- Tidy up.

DROP TABLE #TempUnusedIndexes

Find out Costly Missing Indexes

SELECT TOP 10


[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)

, avg_user_impact

, TableName = statement

, [EqualityUsage] = equality_columns

, [InequalityUsage] = inequality_columns

, [Include Cloumns] = included_columns

FROM sys.dm_db_missing_index_groups g

INNER JOIN sys.dm_db_missing_index_group_stats s

ON s.group_handle = g.index_group_handle

INNER JOIN sys.dm_db_missing_index_details d

ON d.index_handle = g.index_handle

ORDER BY [Total Cost] DESC;

Missing Indexes by Database

SELECT


DatabaseName = DB_NAME(database_id)

,[Number Indexes Missing] = count(*)

FROM sys.dm_db_missing_index_details

GROUP BY DB_NAME(database_id)

ORDER BY 2 DESC;

Identifying the Most Reads and Writes

SELECT TOP 10


[Total Reads] = SUM(total_logical_reads)

,[Execution count] = SUM(qs.execution_count)

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

GROUP BY DB_NAME(qt.dbid)

ORDER BY [Total Reads] DESC;



SELECT TOP 10

[Total Writes] = SUM(total_logical_writes)

,[Execution count] = SUM(qs.execution_count)

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

GROUP BY DB_NAME(qt.dbid)

ORDER BY [Total Writes] DESC;

SQL Query Records Causes of Wait Times

SELECT TOP 10


[Wait type] = wait_type,

[Wait time (s)] = wait_time_ms / 1000,

[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0

/ SUM(wait_time_ms) OVER())

FROM sys.dm_os_wait_stats

WHERE wait_type NOT LIKE '%SLEEP%'

ORDER BY wait_time_ms DESC;

Extract Object level permission

SET NOCOUNT ON


SELECT Scripts

FROM

(

SELECT getdate() AS ScriptDateTime,'CREATE USER ['+DP.name+'] FOR LOGIN ['+SP.name+']'

+ CASE WHEN DP.type_desc != 'WINDOWS_GROUP'

THEN ' WITH DEFAULT_SCHEMA = ['+ISNULL(DP.default_schema_name,'dbo')+']'--+ CHAR(13)+CHAR(10)+'GO'

ELSE ''--+ CHAR(13)+CHAR(10)+'GO'

END AS Scripts

FROM SYS.DATABASE_PRINCIPALS DP,SYS.SERVER_PRINCIPALS SP

WHERE SP.SID = DP.SID

AND DP.name NOT IN ('DBO','GUEST','INFORMATION_SCHEMA','SYS','PUBLIC','DB_OWNER','DB_ACCESSADMIN','DB_SECURITYADMIN','DB_DDLADMIN',

'DB_BACKUPOPERATOR','DB_DATAREADER','DB_DATAWRITER','DB_DENYDATAREADER','DB_DENYDATAWRITER','DB_X')

UNION

--Extracting role membership

SELECT getdate() AS ScriptDateTime,'EXEC sp_addrolemember @rolename ='

+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername ='

+ SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''')

--+ CHAR(13)+CHAR(10)+'GO'

AS '--Role Memberships'

FROM sys.database_role_members AS rm

WHERE USER_NAME(rm.role_principal_id)+USER_NAME(rm.member_principal_id) != 'DB_OWNERDBO'

--ORDER BY rm.role_principal_id ASC

UNION

--Extracting object level permissions

SELECT getdate() AS ScriptDateTime,CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END

+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)

+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END

+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default

+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END

--+ CHAR(13)+CHAR(10)+'GO'

AS '--Object Level Permissions'

FROM sys.database_permissions AS perm

INNER JOIN

sys.objects AS obj

ON perm.major_id = obj.[object_id]

INNER JOIN

sys.database_principals AS usr

ON perm.grantee_principal_id = usr.principal_id

LEFT JOIN

sys.columns AS cl

ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id



--ORDER BY perm.permission_name ASC, perm.state_desc ASC

UNION

--Extracting database level permissions

SELECT getdate() AS ScriptDateTime,CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END

+ SPACE(1) + perm.permission_name + SPACE(1)

+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default

+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END

--+ CHAR(13)+CHAR(10)+'GO'

AS '--Database Level Permissions'

FROM sys.database_permissions AS perm

INNER JOIN

sys.database_principals AS usr

ON perm.grantee_principal_id = usr.principal_id

WHERE perm.major_id = 0

AND (permission_name+USER_NAME(usr.principal_id) != 'CONNECTDBO')

--ORDER BY perm.permission_name ASC, perm.state_desc ASC

) AS UserScripts

ORDER BY Scripts



USE [master]

Working with tempdb

DBCC FREEPROCCACHE

GO
DBCC DROPCLEANBUFFERS
go
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO

select * from sys.dm_db_file_space_usage

select * from sys.dm_db_session_space_usage

select* from sys.dm_db_task_space_usage