Query to show Performance Counters that SQL Server exposes to Windows OS

T-SQL query that can quickly show some valuable performance counters from SQL Server. The values in this system view are actual, they get refreshed many times a second.

SELECT *

FROM master.sys.dm_os_performance_counters

WHERE

instance_name = 'MyDbName'

OR OBJECT_NAME = 'SQLServer:General Statistics'

OR counter_name = 'Batch Requests/sec'

Troubleshooting: if this query returns nothing then you may have Performance Counters disabled. Run this to verify, it should return at least one row : SELECT COUNT(*) from sys.dm_os_performance_counters;

SQL Server, query performance and Execution Plan panel

Noticed one interesting thing with sql management studio.

Was trying to pinpoint some performance issues with a query which had WHILE loop. I switched on the execution plan to analyse the query. It was strange – like if performance was degrading with each new iteration. After several rounds of optimizations there was no luck, same performance degradation.

I created very simple test shown below (Example 1). It does nothing but counts the rows in sys.objects table 500 times so it returns the same result each time. There are 1570 rows in  MyDatabase.sys.objects on my machine

Still no luck – average execution times were rising, total time was more than a minute

Switched off the execution plan and it complete in less than a second!

The conclusion is that Execution Plan panel should be used carefully when your queries have WHILE loops – use Profiler or use SET SHOWPLAN_ALL ON or SET SHOWPLAN_XML ON statements as alternative (see Example 2)

--EXAMPLE1:

USE MyDatabase

SET STATISTICS TIME OFF;

set nocount on;

DECLARE @indexRow INT = 0, @cnt int

SET STATISTICS TIME ON

WHILE @indexRow <= 500

BEGIN

       SELECT @cnt=COUNT(*), @indexRow = @indexRow + 1 FROM sys.objects

END          

SET STATISTICS TIME OFF

--EXAMPLE2:

SET SHOWPLAN_ALL ON;

GO

DECLARE @indexRow INT = 0, @cnt int

WHILE @indexRow <= 500

BEGIN

       SELECT @cnt=COUNT(*), @indexRow = @indexRow + 1 FROM sys.objects

END

GO

SET SHOWPLAN_ALL OFF;

GO

Alternative to sp_changedbowner which is being deprecated

SQL Server Books online suggest that will is being deprecated starting from 2008 (not even 2008 R2) and will be removed from future releases of SQL Server.
What is the alternative?
Meet much more flexible command - ALTER AUTHORIZATION - which is available from version 2005 onwards.

so instead of


USE MyDatabase
GO
EXEC sp_changedbowner 'sa'
GO

you may do


USE MyDatabase
GO
ALTER AUTHORIZATION ON DATABASE::MyDatabase TO sa;
GO

Dropping all stored procedures, functions, views and also system-generated constraints

The script below will drop all stored procedures, functions and views in the database. Very useful when doing upgrades. It will also drop all system-generated default constraints. Just make sure that from now on, in your DB upgrade script, all the constraints have predefined names.

Works in SQL Server 2008

DECLARE @schemaName varchar(100), @objName varchar(500), @objType varchar(2), @parentObjName varchar(500)
DECLARE cur CURSOR
FOR SELECT sa.name AS SchemaName, s.[name] AS ObjName, s.[type] AS ObjType, s2.[name] AS ParentObjName
    FROM sys.objects s
    INNER JOIN sys.schemas sa ON s.schema_id = sa.schema_id
    LEFT OUTER JOIN sys.objects s2 ON s2.object_id = s.parent_object_id   
    WHERE s.[name] NOT LIKE '%aspnet%' AND s.[type] in (N'P', N'PC', N'V', N'FN', N'TF') OR (s.[name] like '%[_][_]%' AND s.[name] NOT LIKE '%aspnet%')

--ignoring constraints on ASP.Net tables as they are not touched by the upgrade
OPEN cur

FETCH NEXT FROM cur INTO @schemaName, @objName, @objType, @parentObjName
WHILE @@fetch_status = 0
BEGIN
    PRINT 'Dropping ['+@objName+'] ...'
    IF @objType = N'V'
    BEGIN
        EXEC (N'DROP VIEW ['+@schemaName+'].['+@objName+']')
    END
    IF @objType IN (N'P', N'PC')
    BEGIN
        EXEC (N'DROP PROCEDURE ['+@schemaName+'].['+@objName+']')
    END
    IF @objType IN (N'FN', N'TF')
    BEGIN
        EXEC (N'DROP FUNCTION ['+@schemaName+'].['+@objName+']')
    END   
    IF @objType IN (N'D') AND @parentObjName IS NOT NULL
    BEGIN
        PRINT 'Will drop the '+ @objName +' constraint in ' + @parentObjName
        EXEC (N'ALTER TABLE ['+@schemaName+'].['+@parentObjName+'] DROP CONSTRAINT ['+@objName+']');
    END       
    FETCH NEXT FROM cur INTO @schemaName, @objName, @objType, @parentObjName
END
CLOSE cur
DEALLOCATE cur
GO